Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 41 ยท Projects and Practice

Project: Testing and Quality Gates

Plan a test strategy for a dbt project: test pyramid, coverage report, custom tests, severity rules, freshness and CI gates, with runnable SQL checks on the shop data.

A project with 200 models and no tests is a project nobody trusts. A project with 2,000 tests nobody reads is not much better. In this project you design a test strategy: which tests go where, which ones block deploys, and how you measure coverage. The dbt YAML is read-only; the checks themselves run here as SQL against the sample shop data.

The test pyramid for data

flowchart TD
  A["Business reconciliation tests: few, high value"] --> B["Relationships and accepted_values: per foreign key and status column"]
  B --> C["unique and not_null: every model, every key"]
  C --> D["Source freshness and schema checks: every source"]

Many cheap structural tests at the base, a few expensive business tests at the top.

Layer Tests Severity
Sources unique/not_null on ids, freshness warn for freshness first, error when stable
Staging unique, not_null, accepted_values error
Marts all of the above plus relationships, ranges error
Business reconciliation, row-count comparisons error for finance, warn elsewhere

Step 1: a pack of structural checks

Run a whole suite on the raw tables in one query. In dbt each line is a test; here they are collected into a report with one row per check:

SQL A test suite as one report ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

All zeros means the whole suite passes. You can see why this is cheap insurance: seven checks, one scan each.

Step 2: business tests

Structural tests cannot know that revenue should match accounting. Write a few singular tests for rules the business cares about. For example, a delivered order must have at least one item:

SQL Business rule: delivered orders have items ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Zero rows means the rule holds. In dbt this query would live in tests/assert_delivered_orders_have_items.sql. Keep a short list of such rules agreed with the data owners, and store them in the repository so they are reviewed.

Step 3: measure coverage

How many models have a primary-key test? How many columns are documented? You can compute this from manifest.json (dbt Core writes a nodes map with tests and columns). With the dbt_project_evaluator package, it is a dbt build --select package:dbt_project_evaluator away. The measurement idea as a stand-in:

SQL Stand-in: which models have a unique and not_null test?

Press Run (or Ctrl + Enter) to see the result here.

fct_orders lacks not_null and stg_payments has no tests at all. These are the models a quality gate would flag.

Step 4: make failures visible

  • Set store_failures: true for marts so the failing rows are inspectable (see severity and selection).
  • Add warn_if / error_if thresholds for noisy rules instead of deleting them.
  • Tag tests critical and run them after every deploy: dbt test --select tag:critical.
  • Send job failures to Slack or email, and assign an owner in each model's meta so alerts reach a person.

Step 5: gates in CI

A pull request should not merge if:

  1. dbt parse or dbt compile fails.
  2. dbt build --select state:modified+ fails (see slim CI).
  3. A changed mart has no description or no primary-key test (governance check).
  4. SQLFluff finds style errors.
YAML
# example governance gate: fail if the evaluator finds missing tests
- run: dbt build --select package:dbt_project_evaluator --target ci

Gates should be strict about structure and lenient about noise: a gate that fails every day teaches people to ignore it.

Step 6: review the strategy

Every quarter, look at the tests that fail often and the incidents that tests missed. A recurring incident without a test is a missing test; a test that always fails and gets ignored should be fixed, relaxed to warn, or removed. Tests are part of the product, so they need maintenance.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which tests belong at the base of the data test pyramid?

    Structural tests are cheap, apply everywhere and catch most problems.
  2. 2. Why use tags like critical on tests?

    dbt test --select tag:critical runs only those tests.
  3. 3. What is a sensible response to a noisy test that fails daily and is ignored?

    Alerts nobody trusts are worse than none; keep the suite credible.
  4. 4. What should every model with a primary key have?

    Together these two tests state that the key identifies a row.

Summary

  • Plan tests as a pyramid: many structural checks, fewer business checks.
  • Business rules become singular tests agreed with data owners.
  • Measure coverage and gate pull requests on it.
  • Make failures visible with store_failures, tags and alerts, and maintain the suite.

Next: dbt Practice Exercises