The break-a-model lab

• unique and not_null on every key. Together they are the grain claim, enforced. If order_item_sk is not unique, every SUM in the project is wrong and nothing else you test matters.
• relationships on every foreign key. This is the test that catches a dimension lookup that stopped resolving - the failure that makes inner-join reports quietly lose rows.
• accepted_values on every enum-ish column. Statuses, tiers, categories, bands. A new value appearing upstream is normal; a new value appearing upstream while every dashboard filters on the old list is a silent exclusion.

Two more are written into this project as custom generic tests, because they came up more than twice: dbt_positive_value for quantities and prices, and unique_combination for composite grain like (order_id, line_no). Both live in macros/ and are one line each to use.

Every singular test in tests/ started as an incident somewhere, and each one is a query you could have written by hand at any point in the last twenty years. What dbt adds is that it now runs on every build:

• assert_star_reconciles_to_source.sql - does settled revenue in the fact still equal the source, to the cent? The single most valuable assertion in the project, and the one auditors ask for.
• assert_net_amount_equals_its_parts.sql - a derived measure that drifted from its own definition means the model is internally inconsistent.
• assert_mart_agrees_with_fact.sql - a mart that disagrees with the fact it aggregates is a second source of truth, which is worse than no mart.
• assert_no_fact_rows_lost_to_unknown_members.sql - unknown members keep broken lookups countable; this fires if their share climbs past 1%.
• assert_payments_never_fan_out_revenue.sql - joining the payments model must not change revenue. The fan-out guard, as a test.

This is tests/assert_star_reconciles_to_source.sql, exactly as dbt compiled it. Zero rows means pass. Uncomment the DELETE to make it fail, and notice the test needs no change to catch it.

-- DELETE FROM raw.order_items WHERE order_id < 30;

with fact as (
    select round(sum(net_amount), 2) as amount from main_marts.fct_order_items
),
source as (
    select round(sum(quantity * unit_price - discount_amt), 2) as amount
    from raw.order_items
)
select fact.amount as fact_amount, source.amount as source_amount
from fact cross join source
where abs(fact.amount - source.amount) > 0.01;

Nothing currently asserts that a cart's conversion flag agrees with whether it has an order id. Write it: select the rows that should not exist.

select cart_id, order_id, is_converted
from main_marts.fct_carts
where (order_id is null and is_converted)
   or (order_id is not null and not is_converted);

Zero rows, so the model is consistent today. Saved as a file in tests/, it stays consistent tomorrow - which is the only difference between a query and a test.

No SQL. A hundred tests on twenty-three models sounds thorough, and some of them are noise: a not_null on a column that is part of a unique key, an accepted_values on a column the business changes monthly, a relationships test between two models one owner controls end to end.

Noisy tests are worse than missing ones, because a suite that cries wolf gets skipped, and a skipped suite is a suite you do not have. The question to ask of each test: if this fails at 3am, would anybody act on it? If not, delete it - and if the answer is "we would investigate but probably ignore it", make it a warning rather than an error (severity: warn in the config), which is exactly what that setting is for.