Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 41 ยท Tests and Documentation

Relationships and accepted_values Tests

Use dbt's relationships test to protect foreign keys and accepted_values to guard category columns. Includes the SQL behind each test, runnable in your browser.

Primary keys are only half of data integrity. The other half is that columns point at things that exist, and that categorical columns only contain values you expect. dbt covers both with two more built-in generic tests: relationships (referential integrity) and accepted_values (a fixed list of allowed values).

After unique and not_null these are the tests you will use most. A typical staging model carries all four.

accepted_values

Use it for status columns, country codes, plan names, anything with a short, known list:

YAML
models:
  - name: stg_orders
    columns:
      - name: status
        data_tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled']

dbt turns that into a query that finds every row whose value is outside the list:

SQL
select status as value_field, count(*) as n_records
from stg_orders
group by status
having status not in ('pending', 'shipped', 'delivered', 'cancelled')

Run the check on the sample orders, then try it with a deliberately incomplete list to see a failure:

SQL accepted_values in SQL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The second query shows what happens when a new status appears in the source (or the list is out of date): the failures count is above zero and dbt test fails. Treat that as a useful alarm. A new status usually needs a decision, such as how it counts in revenue.

Useful options:

  • quote: false for numeric or boolean values so they are not wrapped in quotes.
  • config: {severity: warn} if a new value should warn rather than block.
  • Case matters. 'Shipped' is not 'shipped'.

relationships

relationships verifies that every value in a column exists in another model's column, which is a foreign key check:

YAML
models:
  - name: stg_order_items
    columns:
      - name: order_id
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_orders')
                field: order_id

On dbt versions before the arguments: key, write to and field directly under relationships:. Both spellings exist in the wild, so check your version. The to value is a normal ref() (or source()), which also adds the lineage edge to the graph.

The compiled query is an anti-join: child rows with no matching parent.

SQL
select child.order_id
from stg_order_items as child
left join stg_orders as parent
  on child.order_id = parent.order_id
where child.order_id is not null
  and parent.order_id is null

Note the child.order_id is not null filter. Missing values are the job of not_null; relationships only complains about values that point nowhere.

SQL relationships as an anti-join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

flowchart LR
  I["stg_order_items.order_id"] -->|"must exist in"| O["stg_orders.order_id"]
  I2["stg_order_items.product_id"] -->|"must exist in"| P["stg_products.product_id"]

Each foreign key gets its own relationships test.

Orphans in practice

Orphans usually appear because data arrives out of order (an item row lands before its order), a parent was deleted in the source, or a filter in the parent model removed rows the child still references. Break the shop data on purpose to see the test catch it:

SQL Creating and finding an orphan

Press Run (or Ctrl + Enter) to see the result here.

Order 12 is the one failing row. dbt shows only a count, but with store_failures (see severity and selection) the row itself is saved to a table you can inspect.

Tips

  • Put relationships on the child column, pointing to the parent.
  • Test relationships between staging models, where keys are clean, rather than raw sources.
  • If the parent is large, the anti-join can be expensive; consider a where config limiting to recent data.
  • Prefer accepted_values over free-text status columns being handled with CASE logic downstream.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the relationships test verify?

    It is a foreign-key check: child values must exist in the parent column.
  2. 2. A new value 'refunded' appears in orders.status, which is not in the accepted_values list. What happens on dbt test?

    The query finds rows outside the list, so the failure count is above zero.
  3. 3. Which rows does the relationships test ignore?

    NULL child values are filtered out; use not_null if they should not occur.
  4. 4. How is a relationships test compiled?

    A left join with a parent-is-null filter returns the orphans.

Summary

  • accepted_values checks a column against a fixed list; case matters.
  • relationships is an anti-join that finds orphaned foreign keys.
  • NULLs are skipped by relationships, so combine it with not_null.
  • Test staging models and put the test on the child column.

Next: Singular and Custom Generic Tests