Testing dbt Models: From not_null to Premium Reconciliation

The most expensive bugs in insurance reporting aren't the ones that crash a pipeline. They're the ones that quietly drop a few contract sections from an inner join, double-count a resubmitted transaction, or apply last month's exchange rate. The numbers look plausible, and nobody notices until the premium total doesn't match finance at quarter end.

dbt makes testing part of every run instead of something done by hand before go-live. This post covers the kinds of tests I find most useful on insurance data, from the built-in ones to reconciliation checks and dbt's unit tests.

1. Generic tests: the basics on every model

 1: # models/marts/_marts.yml
 2: version: 2
 3: 
 4: models:
 5:   - name: fct_premium
 6:     columns:
 7:       - name: premium_id
 8:         tests: [unique, not_null]
 9:       - name: contract_section_key
10:         tests:
11:           - not_null
12:           - relationships:
13:               to: ref('dim_section')
14:               field: contract_section_key
15:       - name: broker_key
16:         tests:
17:           - relationships:
18:               to: ref('dim_broker')
19:               field: broker_key
20:       - name: transaction_type
21:         tests:
22:           - accepted_values:
23:               values: ['NEW', 'RENEWAL', 'MTA', 'CANCELLATION']
24:       - name: original_ccy
25:         tests:
26:           - not_null
27:           - accepted_values:
28:               values: ['GBP', 'USD', 'EUR', 'CAD', 'AUD']
29:               config:
30:                 severity: warn

The relationships tests are the most valuable here. They catch orphaned transactions, rows whose contract section or broker doesn't exist in the dimension, before an inner join drops them from a report.

The currency test is set to warn. A new currency isn't necessarily wrong, but someone should look at it.

2. Singular tests: business rules in SQL

A singular test is a SQL file in the tests/ folder that returns the rows that break a rule. No rows means a pass.

1: -- tests/assert_expiry_after_inception.sql
2: select certificate_ref, umr, section_no, inception_date, expiry_date
3: from {{ ref('fct_risks') }}
4: where expiry_date < inception_date

1: -- tests/assert_cancellations_are_negative.sql
2: select premium_id, transaction_type, gross_premium
3: from {{ ref('fct_premium') }}
4: where transaction_type = 'CANCELLATION'
5:   and gross_premium > 0

1: -- tests/assert_risk_within_contract_period.sql
2: select r.certificate_ref, r.risk_inception_date, cs.inception_date, cs.expiry_date
3: from {{ ref('fct_risks') }} r
4: join {{ ref('dim_section') }} cs
5:   on cs.contract_section_key = r.contract_section_key
6: where r.risk_inception_date < cs.inception_date
7:    or r.risk_inception_date >  cs.expiry_date

That last one is a real data quality rule for delegated authority: a risk bound outside the binding authority's period is something the oversight team needs to know about.

3. Reconciliation tests: do the totals match the source?

Row-level tests don't catch a join that duplicates rows or a filter that drops a whole month. For that, compare totals between the source and the final model. Most of the migration and reporting work I've done ends with this kind of reconciliation, and it's worth automating rather than doing in a spreadsheet.

 1: -- tests/reconcile_premium_to_source.sql
 2: with source_totals as (
 3:     select year_of_account, bordereau_month,
 4:            count(*)            as row_count,
 5:            sum(gross_premium)  as gross_premium
 6:     from {{ ref('stg_premium') }}
 7:     group by 1, 2
 8: ),
 9: model_totals as (
10:     select cs.year_of_account, f.bordereau_month,
11:            count(*)              as row_count,
12:            sum(f.gross_premium)  as gross_premium
13:     from {{ ref('fct_premium') }} f
14:     join {{ ref('dim_section') }} cs
15:       on cs.contract_section_key = f.contract_section_key
16:     group by 1, 2
17: )
18: select
19:     coalesce(s.year_of_account, m.year_of_account) as year_of_account,
20:     coalesce(s.bordereau_month, m.bordereau_month) as bordereau_month,
21:     s.row_count     as source_rows,   m.row_count     as model_rows,
22:     s.gross_premium as source_gwp,    m.gross_premium as model_gwp
23: from source_totals s
24: full outer join model_totals m
25:   on  m.year_of_account = s.year_of_account
26:   and m.bordereau_month = s.bordereau_month
27: where coalesce(s.row_count, 0) <> coalesce(m.row_count, 0)
28:    or abs(coalesce(s.gross_premium, 0) - coalesce(m.gross_premium, 0)) > 0.01

Any row returned shows exactly which year of account and month is out, by how many rows, and by how much premium. Compare in the original currency, so currency conversion problems don't hide counting problems.

4. Unit tests: logic with fixed inputs (dbt 1.8+)

The tests above check real data. Unit tests check logic with small, fixed inputs, before the model ever runs against production data. They're ideal for calculations like signing cancellations or netting off commission and brokerage:

 1: # models/marts/_unit_tests.yml
 2: unit_tests:
 3:   - name: net_premium_signs_cancellations_and_deducts_deductions
 4:     model: fct_premium
 5:     given:
 6:       - input: ref('stg_premium')
 7:         rows:
 8:           - {certificate_ref: C1, umr: B1, section_no: S1, transaction_type: NEW,          gross_premium: 1000, commission_pct: 25, brokerage_pct: 5, original_ccy: GBP, bordereau_month: 2025-09-01}
 9:           - {certificate_ref: C2, umr: B1, section_no: S1, transaction_type: CANCELLATION, gross_premium: 400,  commission_pct: 25, brokerage_pct: 5, original_ccy: GBP, bordereau_month: 2025-09-01}
10:       - input: ref('dim_section')
11:         rows:
12:           - {contract_section_key: 10, umr: B1, section_no: S1, coverholder_key: 1, broker_key: 1}
13:       - input: ref('stg_fx_rates')
14:         rows:
15:           - {currency: GBP, roe_month: 2025-09-01, rate_to_gbp: 1}
16:     expect:
17:       rows:
18:         - {certificate_ref: C1, gross_premium: 1000, commission: 250,  brokerage: 50,  net_premium: 700}
19:         - {certificate_ref: C2, gross_premium: -400, commission: -100, brokerage: -20, net_premium: -280}

You only list the columns the test cares about. dbt fills the rest. Run unit tests in development and CI. They don't need to run in production, because the inputs never change.

5. Source freshness

A pipeline that runs perfectly on yesterday's data is still wrong. Freshness checks on sources catch a feed that has stopped arriving:

1: sources:
2:   - name: feed
3:     tables:
4:       - name: claims
5:         loaded_at_field: loaded_at
6:         freshness:
7:           warn_after:  {count: 1, period: day}
8:           error_after: {count: 3, period: day}

Making failures useful

• store_failures: true writes the failing rows to a table, so you can query them, or put them in a report for whoever needs to fix the data.
• Severity and thresholds: warn_if: ">0" and error_if: ">100" let a test warn on a handful of bad rows and fail only when something is badly wrong.
• dbt build runs tests straight after each model and skips downstream models when a test errors, so bad data doesn't reach reporting.

Where to start

If a project has no tests at all, I'd add them in this order: unique and not_null on every key, relationships from facts to dimensions, one reconciliation test per fact table, and source freshness. That's a small amount of YAML and SQL, and it catches most of the problems that would otherwise surface in a finance meeting.