Chapter 11 · Operating It

Tests & Reconciliation

Two kinds of proof protect every number. dbt tests check that the model is internally sound: keys are unique, every fact points at a real dimension row. The reconciliation checks it against the outside world: the same counts and totals, computed in SQL Server and in Snowflake, must match exactly.

dbt tests

  - name: mart_fact_sales_line
    columns:
      - {name: sales_line_key, data_tests: [unique, not_null]}
      - {name: customer_key, data_tests: [{relationships: {arguments: {to: ref('mart_dim_customer'), field: customer_key}}}]}

Since dbt 1.10, a generic test's parameters go under arguments:. The older form still runs but prints a deprecation warning for every test.

-- returns a row (and fails) if the mart lost or duplicated invoice lines on the way from Silver
with silver as (select count(*) n, sum(extended_price) incl, sum(tax_amount) tax, sum(line_profit) profit
                from {{ ref('stg_sales__invoice_lines') }}),
     mart   as (select count(*) n, sum(extended_price) incl, sum(tax_amount) tax, sum(line_profit) profit
                from {{ ref('mart_fact_sales_line') }})
select * from silver cross join mart
where silver.n <> mart.n or silver.incl <> mart.incl or silver.tax <> mart.tax or silver.profit <> mart.profit

Which tests run with which layer

The first time dbt test --select silver ran, 62 tests passed and one errored: the totals test above. It reads a Silver model, so dbt's default eager selection picked it up, but the mart it also reads didn't exist yet. The fix is one flag:

dbt test --select silver --indirect-selection cautious   # only tests whose models are ALL in Silver
dbt test --select gold   --indirect-selection cautious
dbt test --select mart_sales                             # default (eager): includes the cross-layer test

With cautious, a test runs only when every model it reads is selected. Cross-layer tests run once, with the last layer they touch.

Reconciliation: Snowflake against the source

("sales lines -> MART_SALES.FACT_SALES_LINE",
 "SELECT COUNT(*), SUM(ExtendedPrice), SUM(LineProfit) FROM Sales.InvoiceLines",                         # SQL Server
 "SELECT COUNT(*), SUM(extended_price), SUM(line_profit) FROM WWI_ANALYTICS.MART_SALES.FACT_SALES_LINE"), # Snowflake

Each check runs one query on each side and compares the three numbers as exact decimals. Any difference prints MISMATCH and exits with code 1, which fails the Airflow task and stops the run. This is the real output from the scheduled run:

check                                                       source (rows | total 1 | total 2)          snowflake            result
sales lines -> GOLD.FACT_SALE                       228,265 | 198,043,439.45 | 85,729,180.90   228,265 | 198,043,439.45 | 85,729,180.90   OK
sales lines -> MART_SALES.FACT_SALES_LINE           228,265 | 198,043,439.45 | 85,729,180.90   228,265 | 198,043,439.45 | 85,729,180.90   OK
order lines -> GOLD.FACT_ORDER                      231,412 | 177,634,276.40 | 9,310,904       231,412 | 177,634,276.40 | 9,310,904       OK
purchase lines -> GOLD.FACT_PURCHASE                  8,367 | 10,310,535 | 10,284,586           8,367 | 10,310,535 | 10,284,586       OK
stock movements -> GOLD.FACT_MOVEMENT               236,667 | 130,467,680.000 | 148,369,082   236,667 | 130,467,680.000 | 148,369,082   OK
customer + supplier transactions -> FACT_TRANSACTION 99,585 | 2,988,689.65 | 2,988,689.65       99,585 | 2,988,689.65 | 2,988,689.65    OK
stock holdings -> GOLD.FACT_STOCK_HOLDING               227 | 30,343,296 | 5,107.92                227 | 30,343,296 | 5,107.92          OK
all reconciliation checks passed