Generic tests: `accepted_values` and `relationships`

Description

unique and not_null, from the previous lesson, check properties of a column on its own — they need to know nothing about any other table in the project. This lesson's two tests are different: each one compares a fact_orders column against a set of values that lives somewhere else. accepted_values compares product_id against a fixed list, hand-written right in the YAML. relationships compares store_id against dim_store.store_id's real values, with you never having to write that list yourself — dbt runs the query against the complete table for you.

With these two, you complete the out-of-the-box generic test quartet dbt-core ships with. And, along the way, you're going to discover something that isn't obvious until you actually test it: one of the two tests you declare in this lesson is never going to fail on fact_orders, no matter what broken data you feed it — and you're going to understand exactly why, with a real demonstration.

Connection to the module. Lesson 3 protected fact_orders's primary key. This lesson protects two columns that point outward from the row: product_id, which should belong to Kiosko's closed four-product catalog, and store_id, which should reference a real store in dim_store. With all four columns covered — order_id (lesson 3), product_id and store_id (this lesson) — fact_orders completes the minimum coverage of the factory quartet lesson 1 announced.

Worked example: accepted_values on product_id

Kiosko sells exactly four products — P001 through P004 — since data-engineering-foundations-guide defined the catalog. product_id is a categorical column with a closed, known set of valid values: the exact definition of what accepted_values checks.

Extend models/marts/_models.yml, adding product_id to fact_orders's column list:

# models/marts/_models.yml (fragment, inside fact_orders: columns:)
      - name: product_id
        data_tests:
          - accepted_values:
              arguments:
                values: ['P001', 'P002', 'P003', 'P004']

Notice this block's shape, different from unique/not_null: instead of a standalone word in the list, accepted_values is a key that wraps a second key, arguments:, with the parameter this test needs — the list of allowed values. This nesting under arguments: is dbt-core's current standard for passing parameters to any generic test, whether out-of-the-box or custom; declaring the same values without that level (values: [...] directly under accepted_values:, no arguments:) still works today for backward compatibility, but dbt emits a deprecation warning (MissingArgumentsPropertyInGenericTestDeprecation) while parsing the project — this guide always uses the arguments: form, the only one with no warnings.

Worked example (continued): relationships on store_id

store_id is different: it has no fixed catalog hand-written anywhere — its source of truth is dim_store, the dimension you already built in module 3. relationships checks referential integrity: that every value in a "child" column (fact_orders.store_id) also exists in the corresponding column of a "parent" table (dim_store.store_id).

# models/marts/_models.yml (fragment, inside fact_orders: columns:)
      - name: store_id
        data_tests:
          - relationships:
              arguments:
                to: ref('dim_store')
                field: store_id

to: ref('dim_store') — notice ref() shows up inside the YAML, not only inside a .sql file; dbt evaluates that call exactly as it would in any model, so this test also gets registered as a real dependency on dim_store inside the project's DAG. field: store_id is the column's name on the "parent" side you compare against — in this case, the same column, but it wouldn't have to be: relationships compares column names you choose on each side, it doesn't require them to match.

The complete _models.yml, with the four fact_orders columns that have data_tests so far (order_id from lesson 3, plus these two):

# models/marts/_models.yml
version: 2

models:
  - name: dim_store
    description: "Store dimension, a dbt model over stg_stores via ref()."

  - name: dim_date
    description: "Date dimension, fixed August 2026 range generated with a deterministic recursive CTE."

  - name: fact_orders
    description: "Sales fact, order-line grain. Joins stg_orders with dim_store and dim_date via ref()."
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: product_id
        data_tests:
          - accepted_values:
              arguments:
                values: ['P001', 'P002', 'P003', 'P004']
      - name: store_id
        data_tests:
          - relationships:
              arguments:
                to: ref('dim_store')
                field: store_id

Running the two new tests

dbt test --select fact_orders

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 12 data tests, 4 sources, 500 macros

Concurrency: 4 threads (target='dev')

1 of 4 START test accepted_values_fact_orders_product_id__P001__P002__P003__P004  [RUN]
2 of 4 START test not_null_fact_orders_order_id ................................ [RUN]
3 of 4 START test relationships_fact_orders_store_id__store_id__ref_dim_store_ . [RUN]
4 of 4 START test unique_fact_orders_order_id .................................. [RUN]
1 of 4 PASS accepted_values_fact_orders_product_id__P001__P002__P003__P004 ..... [PASS in 0.05s]
2 of 4 PASS not_null_fact_orders_order_id ...................................... [PASS in 0.05s]
3 of 4 PASS relationships_fact_orders_store_id__store_id__ref_dim_store_ ....... [PASS in 0.05s]
4 of 4 PASS unique_fact_orders_order_id ........................................ [PASS in 0.05s]

Finished running 4 data tests in 0 hours 0 minutes and 0.11 seconds (0.11s).

Completed successfully

Done. PASS=4 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=4

Four tests, four PASS — and notice each one's autogenerated name: accepted_values_fact_orders_product_id__P001__P002__P003__P004 literally includes the four allowed values right in the test's name; relationships_fact_orders_store_id__store_id__ref_dim_store_ includes the source column, the destination column, and the referenced model. They're long names, but completely self-explanatory — you can read, with no file open, exactly what rule each one protects.

Going deeper: relationships is never going to fail here, and that's real information

Before continuing, it's worth confirming something with a direct test, because it's counterintuitive. Temporarily add two problem rows to a new file, raw_data/kiosko/orders_2026-08-12.csv:

-- raw_data/kiosko/orders_2026-08-12.csv (temporary, only for this demonstration)
order_id,store_id,product_id,quantity,unit_price,order_ts
ORD-9101,S01,P099,1,1.00,2026-08-12T08:00:00
ORD-9102,S99,P001,1,0.55,2026-08-12T08:10:00

ORD-9101 has a made-up product_id (P099, which doesn't exist in the catalog). ORD-9102 has a made-up store_id (S99, which doesn't exist in dim_store). Since orders's external_location uses a glob (orders_*.csv), this new file automatically gets added to the read:

dbt run --select fact_orders
dbt test --select fact_orders

What to expect.

1 of 4 PASS not_null_fact_orders_order_id ...................................... [PASS in 0.06s]
2 of 4 PASS unique_fact_orders_order_id ........................................ [PASS in 0.06s]
3 of 4 PASS relationships_fact_orders_store_id__store_id__ref_dim_store_ ....... [PASS in 0.06s]
4 of 4 FAIL 1 accepted_values_fact_orders_product_id__P001__P002__P003__P004 ... [FAIL 1 in 0.07s]

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in test accepted_values_fact_orders_product_id__P001__P002__P003__P004 (models/marts/_models.yml)
  Got 1 result, configured to fail if != 0

Done. PASS=3 WARN=0 ERROR=1 SKIP=0 NO-OP=0 REUSED=0 TOTAL=4

accepted_values fails, exactly as you expected: ORD-9101 with product_id = 'P099' is the only row violating the allowed list. But relationships still shows PASS, even though you just introduced a made-up store_id on purpose. Confirm why, by counting fact_orders's rows:

dbt show --inline "select count(*) as n from {{ ref('fact_orders') }}"

What to expect.

Previewing inline node:
|  n |
| -- |
| 41 |

41 rows — 40 original plus ORD-9101, but without ORD-9102. Remember fact_orders.sql, from module 3: INNER JOIN {{ ref('dim_store') }} as ds on o.store_id = ds.store_id. A store_id that doesn't exist in dim_store makes that INNER JOIN find no match — ORD-9102's row never even gets to exist inside fact_orders, so the relationships test, which can only check rows that are in the table, has nothing to report. product_id, on the other hand, takes part in no JOIN inside fact_orders.sql at all — there's no product dimension yet in this project — so a made-up product_id does survive all the way to the final table, and that's where accepted_values catches it.

flowchart TD
    A["ORD-9101\nproduct_id = P099 (invalid)"] --> B["INNER JOIN dim_store: OK\n(valid store_id)"]
    B --> C["INNER JOIN dim_date: OK"]
    C --> D["reaches fact_orders\naccepted_values catches it"]
    E["ORD-9102\nstore_id = S99 (invalid)"] --> F["INNER JOIN dim_store:\nno match"]
    F -.->|"silently dropped"| G["never reaches fact_orders\nrelationships has nothing to check"]

This doesn't make the relationships test useless — quite the opposite. It documents, explicitly and legibly in _models.yml, a guarantee that today depends solely on fact_orders.sql continuing to use INNER JOIN — if someday someone changed it to LEFT JOIN (the exact mistake module 3's lesson 5 already warned about), the invalid store_id would reach the table, and at that point the relationships test would start failing, catching exactly the problem that JOIN change introduced. The test isn't unnecessary because it never fails today — it's there for the day the implementation changes and the guarantee stops holding on its own.

Undo the demonstration before continuing:

rm raw_data/kiosko/orders_2026-08-12.csv
dbt run --select fact_orders
dbt test --select fact_orders

What to expect. Back to PASS=4 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=4, and fact_orders back to 40 rows.

Common mistakes

Writing values: directly under accepted_values:, without arguments:. What happens: someone, copying an old example or an outdated tutorial, writes accepted_values: values: [...] with no arguments: level in between. Why it happens: that form is still valid today — dbt-core 1.12.2 accepts it with no error — so the project runs the same, with no obvious sign anything is outdated. How to spot it: dbt parse (or any command that parses the project) shows a [WARNING][MissingArgumentsPropertyInGenericTestDeprecation] warning, pointing exactly at which file and which test use the old form. How to fix it: always nest a test's parameters under arguments:, as this lesson's worked example does — it's the form with no warnings, and the one you're also going to need in lesson 5, when you declare your own custom generic test's parameters.

Assuming relationships on store_id also protects product_id, "because they're similar." What happens: someone, after seeing relationships working on store_id, assumes it somehow also covers product_id, since both are "reference" columns inside an order line. Why it happens: both columns fill a conceptually similar role (they point at an entity outside the row), so it's easy to generalize protection from one to the other. How to spot it: this lesson's demonstration makes it explicit — product_id with a made-up value does survive all the way to fact_orders, precisely because there's no relationships test declared on that column (no product dimension exists yet to compare it against). How to fix it: every test protects exactly the column where it's declared, and only that one — accepted_values on product_id is the right choice here, precisely because there's no product dimension in the project until module 8, when the capstone adds it.

Being surprised relationships never reports a FAIL in this project, and concluding it's "useless." What happens: someone tries breaking store_id several ways, always sees PASS, and decides the test is useless and deletes it. Why it happens: without having read this lesson's Going deeper section, the behavior looks like a broken test instead of a test documenting a real guarantee. How to spot it: this lesson's demonstration explains exactly why it happens — fact_orders.sql's INNER JOIN already drops any invalid store_id before the row exists — and what future change would actually make it fail. How to fix it: don't delete a test just because it always passes today — a test that documents a real guarantee, even if that guarantee is currently held up by another mechanism (the JOIN), stays valuable as a safety net for the day that other mechanism changes.

Exercises

Exercise 1 — Test accepted_values with a value that does exist. Without modifying any file, use dbt show --inline to confirm fact_orders's four real product_id values (P001 through P004) are, indeed, contained in the list you declared. What query would you write?

dbt show --inline "select distinct product_id from {{ ref('fact_orders') }} order by product_id"

Previewing inline node:
| product_id |
| ---------- |
| P001       |
| P002       |
| P003       |
| P004       |

Exercise 2 — Add a fifth "allowed" value Kiosko never sold. Temporarily modify values: to include 'P005' (a product that doesn't exist in any real Kiosko data), run dbt test --select fact_orders, and confirm the test still shows PASS. Explain why.

values: ['P001', 'P002', 'P003', 'P004', 'P005']

Exercise 3 — Argue, in your own words, why relationships is more robust than "copying and pasting" dim_store's values by hand. In 2-3 sentences, compare declaring relationships: to: ref('dim_store'), field: store_id against instead writing accepted_values: values: ['S01', 'S02', 'S03'] for the same column. What future scenario would break the second option without breaking the first?

Summary and next step

In this lesson you completed the out-of-the-box generic test quartet on fact_orders: accepted_values on product_id (against a fixed list of four values) and relationships on store_id (against dim_store's real values, via ref() right inside the YAML). You confirmed, with a real demonstration, that relationships is never going to fail in this project — because fact_orders.sql's INNER JOIN already drops any invalid store_id before the row exists — and that this doesn't make it useless: it documents a real guarantee, today held up by the JOIN, tomorrow by the test if the JOIN ever changed.

Before moving on you should be able to: write accepted_values's and relationships's arguments: syntax from memory; and explain, with a concrete demonstration if needed, why a made-up product_id survives all the way to fact_orders but a made-up store_id doesn't.

With all four factory tests complete, lesson 5 teaches you to write your own: test_is_positive, a reusable range rule you're going to apply to quantity and unit_price — two columns no out-of-the-box generic test knows how to check, because "greater than zero" isn't a universal property like uniqueness or list membership, it's a business rule expressed as a numeric range.

Resources

• dbt Developer Hub — "Add data tests to your DAG," the accepted_values and relationships section, with the complete arguments: syntax for each. docs.getdbt.com/docs/build/data-tests. In English.
• dbt Developer Hub — "Upgrading to v1.8," which documents the arguments: standard for passing parameters to a generic test and the deprecation of the unnested form. docs.getdbt.com/docs/dbt-versions/core-upgrade/upgrading-to-v1.8. In English.
• dbt Developer Hub — "ref()," again module 3's central reference, this time used inside a YAML file instead of a .sql file. docs.getdbt.com/reference/dbt-jinja-functions/ref. In English.