Tests That Keep a Star a Star
A star schema degrades silently — duplicated grain, orphaned keys, overlapping SCD windows — and the layered dbt test strategy that catches each failure before a dashboard does.
A star schema doesn’t fail loudly. When an app database breaks, something throws — a constraint fires, a request 500s, a pager goes off. When a star breaks, every query keeps returning numbers. A duplicated dimension row fans out a join and revenue doubles — plausibly, by segment, in a way nobody eyeballs. An orphaned foreign key drops rows from an inner join and the quarter looks slightly soft. Two overlapping SCD windows double-count one customer’s history. The dashboards render, the numbers are wrong, and the first person to notice is a CFO with a different spreadsheet.
That’s the case for testing dimensional models specifically, over and above testing your data generally. The dbt mechanics are the ones that series taught — schema tests in YAML, singular tests in tests/, generic tests you write once and attach everywhere. What’s new here is the what: a star schema makes precise structural promises, which means every one of them is testable. Grain promises one row per declared thing. Foreign keys promise to resolve. SCD windows promise to tile time without gap or overlap. Measures promise to reconcile to the source. This chapter turns each promise into a test, layer by layer, until the bookshop’s star can’t quietly stop being a star.
Layer 1: grain
Grain is the first promise every table made — chapter 3 called declaring it the non-negotiable step — and grain violations are the most destructive failure a star has, because a duplicated row doesn’t just corrupt its own table. It fans out every join that touches it. Two rows in dim_book for the same book means every order line for that book joins twice, and sum(extended_price) inflates by exactly the sales of your most popular duplicated titles — an error with a pattern, which is worse than a random one, because it survives sanity checks.
Every dimension gets unique and not_null on its surrogate key. Every fact gets the same on its grain key. No exceptions, no judgement calls — this is the floor:
models:
- name: dim_book
columns:
- name: book_sk
data_tests: [unique, not_null]
- name: fct_order_items
columns:
- name: order_item_sk
data_tests: [unique, not_null]But the surrogate key test has a blind spot. order_item_sk is a hash of order_id and line_number — so unique on it really tests “the hash inputs are unique,” which is the grain only as long as the hash inputs are the grain. Someone edits the model, adds a column to the generate_surrogate_key call to “fix” a duplicate, and the unique test follows along happily — the hash is still unique, and the declared grain just silently changed from underneath every consumer. So the grain gets pinned twice: once on the key, and once on the named natural columns with dbt_utils.unique_combination_of_columns:
- name: fct_order_items
data_tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns: [order_id, line_number]
columns:
- name: order_item_sk
data_tests: [unique, not_null]
- name: fct_inventory_daily
data_tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns: [book_sk, snapshot_date]
- name: fct_orders
columns:
- name: order_id
data_tests: [unique, not_null]Read those as documentation, because they are: the test block is the grain declaration, executable. fct_order_items is one row per order line. fct_inventory_daily is one row per book per day. fct_orders — the accumulating snapshot — is one row per order, full stop, which is the promise that makes update-in-place milestones sane. The combination test also catches the failure unique-on-a-hash can’t: an actual MD5 collision would make two different grain combinations pass the key test while being two rows of the same key — vanishingly rare, but the combination test closes the gap for free.
Two grains deserve special attention. The Type 2 dim_customer_history from the SCD chapters is not one row per customer — it’s one row per customer version, so its combination test is [customer_id, valid_from], and unique on customer_id would fail by design. (Its current-only sibling dim_customer — the view that keeps just the open version — is one row per customer, so that one takes the ordinary unique + not_null on customer_id. Same entity, two grains, two tests.) And the bridge table is one row per book-author pairing, [book_sk, author_sk] — plus the test that guards its allocation math, which we’ll get to in the reconciliation layer.
Layer 2: referential integrity
The second promise: every foreign key on every fact resolves to a dimension row. The warehouse won’t enforce this for you — Snowflake accepts FOREIGN KEY constraints but doesn’t enforce them (they’re metadata for tools and the optimizer, a fact worth double-checking against current docs but long-standing) — so the relationships test is the only referee. One per foreign key, every fact, including every role:
- name: fct_order_items
columns:
- name: book_sk
data_tests:
- relationships:
to: ref('dim_book')
field: book_sk
- name: customer_sk
data_tests:
- relationships:
to: ref('dim_customer')
field: customer_sk
- name: customer_profile_sk
data_tests:
- relationships:
to: ref('dim_customer_profile')
field: customer_profile_sk
- name: date_sk
data_tests:
- relationships:
to: ref('dim_date')
field: date_sk
- name: fct_orders
columns:
- name: order_date_sk
data_tests:
- relationships: {to: ref('dim_date'), field: date_sk}
- name: ship_date_sk
data_tests:
- relationships: {to: ref('dim_date'), field: date_sk}
- name: delivery_date_sk
data_tests:
- relationships: {to: ref('dim_date'), field: date_sk}The role-playing keys on fct_orders all point at the physical dim_date — the role views are renamings, and testing against the base table tests all three roles at once. Under the hood each test compiles to roughly select child keys ... left join parent ... where parent key is null — it returns the orphans, and any orphan is a failure.
Here’s the part that separates a test suite that’s honest from one that’s theater: the unknown member is what lets these tests pass truthfully. Think about what an orphaned key means — a fact row whose customer isn’t in dim_customer. Without the unknown-member discipline, you have three bad options: let the test fail perpetually (and train everyone to ignore it), filter the orphans out of the fact (silently losing revenue from the report), or drop the test. The discipline this series built instead — coalesce nulls to defaults before hashing, route unmatched lookups to a materialized unknown/inferred member, never emit a null or dangling key — means every key the fact mints has a row waiting for it by construction. delivery_date_sk on an undelivered order points at the calendar’s not-yet member, which exists, so the test passes. A late-arriving fact for a brand-new customer points at an inferred member, which the load created, so the test passes. The relationships test then guards exactly what it should: new code paths that mint keys without routing them. It’s a tripwire for regressions in the load discipline, not a bill for data you can’t control.
There’s a temptation to say the unknown member makes RI tests pointless — everything resolves, so what’s tested? What’s tested is the machinery. The day someone adds a new lookup to the fact build and forgets the coalesce, or builds a junk-dimension variant whose observed combinations lag the fact, the orphans appear and the test catches them at build time — before the inner join in a BI tool quietly eats the rows.
Layer 3: domains
Structural tests don’t know that order_status has four legal values or that nobody sells negative books. Domain tests encode the small facts:
- name: fct_orders
columns:
- name: order_status
data_tests:
- accepted_values:
values: ['placed', 'shipped', 'delivered', 'cancelled']
- name: fct_order_items
columns:
- name: quantity
data_tests:
- dbt_utils.accepted_range:
min_value: 1
inclusive: true
- name: dim_order_flags
columns:
- name: channel
data_tests:
- accepted_values:
values: ['web', 'store', 'phone', 'unknown']accepted_values on status columns earns its keep the day the source system adds a status — returned, say — and your accumulating snapshot’s milestone logic doesn’t know it exists. Without the test, returned orders sit in whatever state the case expression’s else branch dumps them; with it, the build fails and tells you the business changed. That’s the right way to learn.
The domain layer is also where computed columns get their internal consistency checked. extended_price was defined as quantity times unit price — so assert it:
- name: fct_order_items
data_tests:
- dbt_utils.expression_is_true:
expression: "extended_price = quantity * unit_price"This looks like testing your own arithmetic, and early on it is. Its value compounds later, when the model is three years old, the expression has been refactored twice, and a new discount column was supposed to flow into extended_price but only made it into half the branches of a case. Definitional assertions are how a measure’s meaning survives its maintainers.
Layer 4: SCD windows
Type 2 dimensions make the most intricate promise in the star: for every customer, the version windows tile time perfectly — no overlaps, no gaps, exactly one current row. Each failure mode has a distinct symptom. Overlapping windows double-count: an order date falling inside two windows joins twice, and the fan-out corrupts facts downstream. Gaps drop: an order dated inside a gap matches no version, and the point-in-time join loses it. Two is_current rows make “current customers” reports double-count; zero make the customer vanish.
Before writing a line of it, be precise about which table gets this test. The windows live on dim_customer_history — the Type 2 versioned table from chapter 9, the one with valid_from, valid_to, and is_current. They do not live on dim_customer, which is the current-only view: it has exactly one row per customer and no validity columns to check, so pointing a window test at it is a category error that either fails on a missing column or, worse, passes vacuously. Test the table that has windows.
dbt_utils.mutually_exclusive_ranges tests the tiling directly — per customer, each row’s window must end exactly where the next begins:
- name: dim_customer_history
data_tests:
- dbt_utils.mutually_exclusive_ranges:
lower_bound_column: valid_from
upper_bound_column: valid_to
partition_by: customer_id
gaps: not_allowed
zero_length_range_allowed: falsepartition_by scopes the check to each customer’s history; gaps: not_allowed upgrades it from “no overlaps” to “perfectly contiguous”; zero_length_range_allowed: false rejects degenerate rows where valid_from = valid_to. The one wrinkle is the open row, and whether it bites you depends on a config from the snapshot chapter. By default a dbt snapshot leaves valid_to null on the current version, and a null upper bound has no place on a number line, so the check would either error or silently skip the newest window. Our snapshots set dbt_valid_to_current: "to_date('9999-12-31')" (and rename the meta-columns to valid_from/valid_to), which stores that far-future sentinel instead of a null — so the current row is already just another closed range and the test needs nothing special. If you inherit a snapshot without that config, its open rows carry nulls and mutually_exclusive_ranges will either error or silently skip the newest window; wrap the upper bound in a coalesce to a far-future date and it works either way.
The packaged test is convenient, but the window check is worth writing out once by hand — the mechanism is the lesson, a singular test survives the day you drop dbt_utils, and it fails with the evidence already attached. The idea: line each customer’s versions up in order, look one row ahead with lead, and demand that every window’s close lands exactly on the next window’s open. A close before the next open is a gap; a close after it is an overlap; equality is the only pass:
-- tests/assert_scd_windows_tile_time.sql
-- Every customer's Type-2 history must tile time: no gaps, no overlaps.
with windows as (
select
customer_id,
valid_from,
coalesce(valid_to, timestamp '9999-12-31 00:00:00') as valid_to,
lead(valid_from) over (
partition by customer_id
order by valid_from
) as next_valid_from
from {{ ref('dim_customer_history') }}
)
select
customer_id,
valid_from,
valid_to,
next_valid_from,
case
when valid_to < next_valid_from then 'gap'
when valid_to > next_valid_from then 'overlap'
end as defect
from windows
where next_valid_from is not null -- the last row per customer is the open one; nothing follows it
and valid_to <> next_valid_from -- exact hand-off is the only acceptable stateZero rows means every hand-off is exact. When it isn’t, the query hands you the diagnosis pre-computed: the defect column labels each offending row gap or overlap, and customer_id tells you whose history to go re-window. That’s the edge a singular test has over the packaged one — it fails with the culprit rows, no compiled-SQL detour into target/. The next_valid_from is not null filter is load-bearing: it excludes each customer’s final, open window, which has nothing after it to abut and would otherwise register as a false gap.
Exactly-one-current is the third leg of the contract, has no packaged test, and makes a perfect singular test — a query that returns the customers violating the rule. It, too, reads from dim_customer_history, because is_current is a column on the versioned table (the dim_customer view is filtered to is_current = true, so counting current rows there would tautologically return one and test nothing):
-- tests/assert_one_current_row_per_customer.sql
select
customer_id,
count_if(is_current) as current_rows
from {{ ref('dim_customer_history') }}
group by customer_id
having count_if(is_current) != 1Zero rows back means every customer has exactly one open version. (count_if is Snowflake’s conditional count; sum(case when is_current then 1 else 0 end) is the portable spelling.) Together the two tests cover the whole Type 2 contract — and they’re also your early-warning system for the failure the late-arriving chapter dissects: a back-dated source change that re-windows history is exactly the kind of surgery that leaves an overlap or a gap behind, and this pair of tests is how you find out at build time instead of at reconciliation time.
Layer 5: reconciliation
Everything so far tests the star’s internal consistency. Reconciliation tests its external honesty: did anything get lost or invented between staging and the mart? A star that’s internally perfect but dropped 2% of order lines in a join is still lying — just neatly.
Row counts first. fct_order_items is one row per order line and staging is one row per order line, so the counts must match exactly:
- name: fct_order_items
data_tests:
- dbt_utils.equal_rowcount:
compare_model: ref('stg_order_items')This catches both directions of failure. Fewer rows in the mart means a join dropped lines — an inner join to stg_orders that should have been a left join, an order-line orphan that fell through. More rows means fan-out — a duplicate in stg_orders doubling lines through the join. If your fact legitimately filters (a mart that excludes cancelled orders, say), don’t abandon the test — point compare_model at an intermediate model with the same filter, so the comparison stays exact. A reconciliation test with a fudge factor reconciles nothing.
Sums second, because row counts can match while values corrupt. A singular test, comparing the mart’s revenue to the same computation done directly against staging:
-- tests/assert_revenue_reconciles_to_staging.sql
with mart as (
select sum(extended_price) as revenue
from {{ ref('fct_order_items') }}
),
staging as (
select sum(quantity * unit_price) as revenue
from {{ ref('stg_order_items') }}
)
select
mart.revenue as mart_revenue,
staging.revenue as staging_revenue
from mart
cross join staging
where abs(mart.revenue - staging.revenue) > 0.01The tolerance is for floating-point noise, not for missing money — a penny, not a percent. When this fires, the debugging move is to re-run it with a group by on order date to localize which day diverged; equal_rowcount accepts group_by_columns for the same trick on counts. And the same pattern guards the bridge table’s allocation math from two chapters ago — the weights must sum to one per book, or allocated revenue silently leaks:
-- tests/assert_bridge_allocation_sums_to_one.sql
select
book_sk,
sum(allocation_factor) as total_allocation
from {{ ref('bridge_book_author') }}
group by book_sk
having abs(sum(allocation_factor) - 1.0) > 0.001Layer 6: audits on the unknown member
The unknown-member discipline made the RI tests honest; now audit the discipline itself. Facts pointing at the unknown member are supposed to exist — that’s the design — but their volume is a health metric. A steady trickle of orders on the unknown customer profile means history predating the profile snapshot: expected. A sudden spike means an upstream feed broke and the coalesce defaults are absorbing the evidence. Structurally fine, operationally on fire.
This wants a warning, not an error, with a threshold:
-- tests/audit_unknown_member_share.sql
{{ config(severity='warn', warn_if='>0', error_if='>1') }}
with facts as (
select
count(*) as total_rows,
count_if(c.customer_id = '-1') as unknown_rows
from {{ ref('fct_order_items') }} f
join {{ ref('dim_customer') }} c on f.customer_sk = c.customer_sk
)
select *
from facts
where unknown_rows / nullif(total_rows, 0) > 0.02The query returns a row only when unknown-member share crosses 2% — below that, silence; above it, a warning; and the error_if escalation is there if you want a hard ceiling. The same audit pattern applies to inferred members from the late-arriving pipeline: a singular test that warns when an inferred customer row has sat unenriched for more than a few days, because inferred members are IOUs and IOUs age badly.
For distribution drift beyond simple thresholds, the dbt_expectations package carries the statistical vocabulary — expect_column_values_to_be_between, expect_column_mean_to_be_between, expect_table_row_count_to_be_between — useful as warn-severity canaries on measures (“mean order line value stays between $5 and $200”) that catch a currency-conversion bug no structural test would ever see. Treat these as smoke detectors, not assertions: they encode expectations about the business, and the business is allowed to change.
Severity: what stops the build and what doesn’t
Every test so far has an implicit judgement attached — is a failure here a broken star, or a question worth asking? — and dbt’s severity config is where the judgement gets written down. The rule that falls out of the layers:
Error — structural promises. Grain, referential integrity, SCD windows, exactly-one-current, reconciliation, allocation sums. A failure means the star’s geometry is wrong, every downstream number is suspect, and continuing the build propagates the corruption. These stop the world, and they should.
Warn — health and drift. Unknown-member share, inferred-member age, distribution canaries, “usually fewer than N returns per day.” A failure means look at this today, not the marts are wrong. Warnings that stop deploys get deleted by the first on-call engineer they annoy; warnings that page nobody but appear in the log get triaged — which is exactly their job.
The graduated form covers the middle ground — thresholds where a few failures are tolerable and many are not:
- accepted_values:
values: ['placed', 'shipped', 'delivered', 'cancelled']
config:
severity: error
warn_if: ">0"
error_if: ">100"A handful of rows with a weird status warns; a flood errors. Use this sparingly — most structural tests should have no tolerance at all, because “a little bit duplicated” is not a state a grain can be in.
Running it: dbt build and the failing star
Wiring is the easy part, and it’s the payoff for putting everything in one DAG. dbt build runs models and their tests interleaved — each model’s tests execute immediately after the model materializes, and here’s the operational teeth: if an error-severity test fails, everything downstream of that model is skipped. A duplicated dim_book fails its unique test, and fct_order_items — which would have fanned out against it — never builds. The corruption stops at its source, and yesterday’s correct tables stay in place until you fix the problem. That skip behavior is the single best argument for testing dimensions before facts reference them, which the DAG does for free.
What it looks like when a star fails, concretely:
12:04:11 1 of 42 OK created sql table model marts.dim_book ................. [SUCCESS]
12:04:13 2 of 42 FAIL 3 unique_dim_book_book_sk ............................ [FAIL 3]
12:04:13 3 of 42 SKIP relation marts.fct_order_items ....................... [SKIP]
12:04:13 4 of 42 SKIP test relationships_fct_order_items_book_sk .......... [SKIP]Three rows violated uniqueness; the fact and its tests never ran. The triage loop: dbt build prints the path to the compiled test SQL under target/compiled/ — run it in a worksheet and you’re looking at the three duplicate keys themselves. Better, configure store_failures on the structural tests and dbt writes the failing rows to an audit schema table you can query and share, which turns “the build is red” into “these three books have two source rows each, here’s the ticket.” Once fixed, dbt retry re-runs from the point of failure instead of rebuilding the whole star.
Day to day, the discipline that keeps the suite alive is boring and worth stating: every new model lands with its grain test and its FK tests in the same PR, no exceptions — codify it in a PR checklist or let dbt_project_evaluator nag about untested models mechanically. A star’s test suite is like the star itself: cheap to maintain continuously, brutal to retrofit.
One honesty note before the close, in keeping with the series’ rules: the YAML shapes and package tests here are docs-correct for dbt_utils and dbt_expectations on current dbt, and the SQL is standard Snowflake — but this series has no live Snowflake to run against, so treat the snippets as verified-by-reading, not by execution.
Final thoughts
There’s a school of thought that says testing analytics code is over-engineering — it’s just reporting, nobody dies, ship the dashboard. The mistake in that argument isn’t the stakes; it’s the physics. Application bugs announce themselves — they throw, they crash, they page. Dimensional bugs compound in silence: the fan-out that doubles revenue produces a prettier chart, not an error, and every week it goes uncaught is a week of decisions made on it. Tests are how a star schema gets the loud failures nature didn’t give it. And notice what made the testing tractable: the modeling. Grain, surrogate keys, the unknown member, windowed SCDs — every discipline the series taught turned out to be a testable assertion in disguise. A well-modeled star is one where “correct” has a definition precise enough to execute nightly. That’s not a coincidence; it’s most of the reason to model well in the first place.
Comments