A model that builds is not a model that is right. Keys can duplicate after a bad join, a source can stop sending data without any error, and a calculation can drift from the source system's numbers. dbt lets you write those expectations down as tests that run with every build. In this part we add 20 tests to the project, watch one of them catch a real discrepancy, check that raw data is fresh, and publish documentation with a lineage graph.
Code for this part: _tpch__models.yml, _core__models.yml, tests/. Full project: tpch_analytics on GitHub.
Generic tests: one line per rule
dbt ships four generic tests: unique, not_null, accepted_values and relationships. You attach them to columns in a YAML file next to the models. Each test compiles to a query that returns the rows breaking the rule; zero rows means pass.
models/staging/tpch/_tpch__models.ymlversion: 2
models:
- name: stg_tpch__orders
description: One row per order, renamed and typed.
columns:
- name: order_key
data_tests: [unique, not_null]
- name: order_status
data_tests:
- accepted_values:
arguments:
values: ['fulfilled', 'open', 'partially_fulfilled']
- name: customer_key
data_tests:
- not_null
- relationships:
arguments:
to: ref('stg_tpch__customers')
field: customer_key
- name: stg_tpch__line_items
description: One row per order line.
columns:
- name: order_item_key
data_tests: [unique, not_null]
- name: discount_rate
data_tests:
- dbt_utils.accepted_range:
arguments:
min_value: 0
max_value: 0.1
- name: stg_tpch__customers
columns:
- name: customer_key
data_tests: [unique, not_null]Recent dbt versions expect test parameters under an arguments: key, as above; older tutorials put them directly under the test name, which now produces a deprecation warning. dbt_utils.accepted_range comes from the package installed in part 4 and catches impossible values, here a discount outside 0 to 10%.
The marts get the same treatment, plus a rule expressed as SQL:
models/marts/core/_core__models.ymlversion: 2
models:
- name: fct_order_items
description: One row per order line with amounts. Incremental on _loaded_at.
columns:
- name: order_item_key
data_tests: [unique, not_null]
- name: net_amount
data_tests:
- dbt_utils.expression_is_true:
arguments:
expression: ">= 0"
- name: fct_orders
description: One row per order with totals rolled up from order items.
columns:
- name: order_key
data_tests: [unique, not_null]
- name: customer_key
data_tests:
- relationships:
arguments:
to: ref('dim_customers')
field: customer_key
- name: dim_customers
description: One row per customer with geography and order history.
columns:
- name: customer_key
data_tests: [unique, not_null]
- name: region_name
data_tests: [not_null]Singular tests: any rule you can write in SQL
Some rules involve several columns or tables. A singular test is a SQL file in tests/ that returns the failing rows. Here is a reconciliation: the order header's total should equal the sum of its lines.
tests/assert_order_total_matches_line_items.sql (first version)-- The order header total should equal the sum of its line items.
-- Returns the orders where they differ by more than one cent.
select
order_key,
total_price,
total_amount
from {{ ref('fct_orders') }}
where abs(total_price - total_amount) > 0.01When I first ran the full build, this test failed:
30 of 34 FAIL 65510 assert_order_total_matches_line_items ...................... [FAIL 65510]
...
Done. PASS=32 WARN=0 ERROR=1 SKIP=1 NO-OP=0 REUSED=0 TOTAL=34Two things to notice. First, SKIP=1: dbt did not build agg_monthly_revenue, because a test upstream of it failed, so a questionable number never reached the dashboard. Second, the failure is a finding, not a bug in the test. Querying the differences showed they are tiny: a median of 3 cents and at most about 8 cents per order, because the source system rounds every line to the cent before adding them up. The right fix is to encode the real business tolerance:
tests/assert_order_total_matches_line_items.sql-- The order header total should equal the sum of its line items.
-- Source systems round each line, so allow up to $1 of rounding difference.
select
order_key,
total_price,
total_amount,
total_price - total_amount as difference
from {{ ref('fct_orders') }}
where abs(total_price - total_amount) > 1.00Done. PASS=34 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=34The outputs above are from the local DuckDB build of the project at a small scale factor; on Snowflake the same test behaves the same way with larger counts. If a rule should be watched but not block the pipeline, lower its severity instead of deleting it: add config: {severity: warn} to the test.
Source freshness: catch silent failures
The worst pipeline failure is the one that throws no error: the source stops sending files, every model rebuilds on yesterday's data, and the dashboard looks fine. The loaded_at_field and freshness settings from part 4 turn that into an alert, using the _LOADED_AT audit column from part 3:
bashdbt source freshness1 of 2 START freshness of tpch.lineitem ........................................ [RUN]
1 of 2 PASS freshness of tpch.lineitem ......................................... [PASS in 0.01s]
2 of 2 START freshness of tpch.orders .......................................... [RUN]
2 of 2 PASS freshness of tpch.orders ........................................... [PASS in 0.01s]If the newest _LOADED_AT is more than 24 hours old the check warns; after 48 hours it errors. Part 8 runs it before every daily build.
What to test: a practical checklist
| Where | Tests | Why |
|---|---|---|
| Every primary key | unique + not_null | Duplicates silently double revenue |
| Every foreign key in a fact | relationships | Orphan rows disappear from joined reports |
| Status and category columns | accepted_values | New codes from the source get noticed |
| Amounts and rates | accepted_range / expression_is_true | Impossible values point to upstream bugs |
| Totals that exist twice | singular reconciliation test | Proves your logic matches the source |
| Every loaded source | freshness | Detects a pipeline that stopped quietly |
Start there. More tests are not automatically better: every test costs warehouse time, and a suite full of noisy warnings trains people to ignore it.
Documentation and lineage
The description fields in the YAML files are not just comments. dbt turns them, together with every model's SQL, columns and tests, into a documentation website:
bashdbt docs generate
dbt docs serve # opens http://localhost:8080The site's lineage graph shows each model's upstream and downstream dependencies, the same graph as the diagram in part 5, generated from your ref() and source() calls. Host the generated target/ folder on any static web host to share it with analysts.
Publishing marts to report users
Part 2 gave the REPORTER role access to the ANALYTICS database but no tables. On Snowflake, dbt can grant SELECT on each model as it builds it, so access follows the code. Add this to dbt_project.yml to let report users read the marts and nothing else:
dbt_project.yml (Snowflake)models:
tpch_analytics:
marts:
+materialized: table
+schema: marts
+grants:
select: ['REPORTER']Report users also need USAGE on the MARTS schema, granted once by an admin.
Next
Every model is now rebuilt in full on every run. That is fine for small tables but wasteful for order lines that only ever grow. In part 7 we make fct_order_items incremental, process only new rows, and keep a history of customer changes with a snapshot.
The series
- The stack and what we will build
- Setting up Snowflake for dbt
- Loading raw data into Snowflake
- Your first dbt project on Snowflake
- Modeling with dbt: staging to marts
- Testing and documenting with dbt (this post)
- Incremental models and snapshots
- Orchestrating dbt with Airflow
- CI/CD for dbt with GitHub Actions
- Running the pipeline in production
All parts: Data Pipeline Series. Every file shown in this series is in the companion project tpch_analytics on GitHub; clone it to follow along, or run it locally on DuckDB without a Snowflake account.
Comments
Post a Comment