Technical note

dbt Tests as Risk Mitigation Controls

7 min read · Published 17 June 2026

• Analytics Engineering
• dbt
• Data Quality
• Reporting Trust

dbt tests are often introduced as a development feature.

They check that a column is not null. They check that a key is unique. They check that a value is accepted. They check that relationships exist between models.

That is useful, but it undersells their role.

For important reporting, dbt tests are risk mitigation controls.

They help stop a weak number from reaching a decision workflow. They move the discovery point from “the CEO noticed in the meeting” to “the system failed before publication”.

That shift matters for Reporting Trust.

The symptom: the dashboard breaks silently

A critical dashboard refreshes overnight.

No alert fires. The chart still loads. The number looks normal enough to pass a quick glance.

Then someone in the Brightside Bikes weekly operating meeting notices that Shopify Net Revenue no longer reconciles to the finance revenue bridge. The team pauses the meeting, opens spreadsheets, checks source systems, and asks the data team to explain what happened.

The problem may be simple:

• A required customer ID is now null.
• A source system changed a status value.
• A dimension table has duplicate customer records.
• A relationship between invoice and customer broke.
• Freshness failed upstream, but the final model still rebuilt.

Without tests, these issues become human discovery tasks.

That is part of the Invisible Data Tax.

Tests should map to business risk

The weakest way to use dbt tests is to add generic checks because they are easy.

The stronger way is to ask:

What would make this model unsafe for the decision it supports?

For Brightside’s weekly operating models, risks might include:

• A paid Shopify order is missing.
• A refunded order is included incorrectly.
• A Stripe settlement has no matching Shopify order.
• A SKU has no approved cost in the product cost map.
• A campaign name is unmapped, so CAC is understated.
• The final weekly grain has more than one row per metric per week.

Those risks should drive the tests.

The test is not just a technical assertion. It is a trust control attached to a business consequence.

Common tests as trust signals

A not_null test can mean: this field is required for the metric to be interpretable.

A unique test can mean: this model is expected to have one row per entity at this grain.

A relationships test can mean: every fact record should connect to a valid business entity.

An accepted_values test can mean: the model recognises all valid business states and should fail if a new unmapped state appears.

A freshness check can mean: the dashboard is not safe for the morning meeting if the source did not land.

That is the language that connects tests to the core book message. Tests are not just code quality. They are trust signals that tell the business whether a number is safe enough to use.

A simple dbt test example

In dbt, a model can declare tests in YAML:

models:
  - name: fct_weekly_trading_revenue
    description: "Weekly Shopify trading revenue for Brightside Bikes."
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: trading_week
        tests:
          - not_null
      - name: shopify_net_revenue
        tests:
          - not_null

This is a start.

But for a trusted reporting model, the important part is not the syntax. It is the link between the test and the reporting contract.

If order_id is unique, that means the model expects one row per Shopify order. If duplicates appear, the model is no longer safe for a dashboard that sums weekly trading revenue.

The test protects the grain.

Custom tests for business logic

Generic tests catch common structural problems. Important metrics often need custom checks.

For example, if Brightside’s weekly Shopify trading revenue should reconcile to the finance revenue bridge within an agreed tolerance, write that expectation down as a test or reconciliation query.

with shopify_revenue as (
  select
    trading_week,
    sum(shopify_net_revenue) as revenue_amount
  from {{ ref('fct_weekly_trading_revenue') }}
  group by 1
),

finance_revenue as (
  select
    trading_week,
    sum(finance_bridge_revenue) as revenue_amount
  from {{ ref('mart_weekly_revenue_bridge') }}
  group by 1
),

comparison as (
  select
    s.trading_week,
    s.revenue_amount as shopify_amount,
    f.revenue_amount as finance_amount,
    abs(s.revenue_amount - f.revenue_amount) as variance_amount
  from shopify_revenue s
  join finance_revenue f
    on s.trading_week = f.trading_week
)

select *
from comparison
where variance_amount > 100

This test says the quiet part out loud:

The model is allowed to differ only within an agreed tolerance.

That is tolerable divergence expressed as a control.

Tests need ownership

A failing test is not useful if nobody knows what it means or who should respond.

Each important test should have an owner or response path.

Ask:

• Is this a technical failure or a business rule change?
• Who decides whether the rule is still valid?
• Should the dashboard be blocked, warned, or published with a caveat?
• Is the failure urgent for today’s decision cycle?
• Does the reporting contract need updating?

Without ownership, tests can become alert noise.

With ownership, tests become part of the operating system for Reporting Trust.

Where dbt tests fit in the wider control system

dbt tests are not the whole QA process.

They should sit alongside:

• Source freshness checks
• Pull request review
• CI checks before merge
• Reconciliation to finance or operational systems
• Business review of new definitions
• Monitoring after deployment
• Documentation of known caveats

The point is to move quality from memory into process.

If a metric is important enough to use in a board pack, forecast, pricing decision, investor update, or AI workflow, it is important enough to have explicit controls.

What to inspect first

Pick one model that feeds a disputed metric.

Then ask:

• What business decision uses this model?
• What would make the number unsafe?
• Are those failure modes tested?
• Are tests checking grain, keys, relationships, accepted values, and freshness?
• Are there reconciliation checks against the source of truth?
• Does anyone review test failures in business context?
• Are failures visible before users find them?

If the answer is no, the team may have tests, but not yet a trust control system.

What good looks like

Good dbt testing is boring in the right way.

The important checks run automatically. Failures are understandable. The response path is clear. The tests match business risk. The documentation explains why the check exists.

That is how tests reduce the Invisible Data Tax.

They do not remove the need for judgement. They make sure the same preventable errors are not rediscovered in meetings, spreadsheets, and urgent Slack threads.

For analytics engineering, that is the real value: fewer surprises reaching the people trying to make decisions.