Tests & documentation

A test looks for a broken promise

Suppose a register promises one row per included person. You can inspect a sample today, but next week's input may contain a duplicate you have never seen. A data test records the rule so it can be checked again.

An assertion states what must hold. Here, the person key must be present and unique. In YAML, a dash starts a list item and indentation places that item under its parent. The two test names below belong to person_id.

columns:
  - name: person_id
    data_tests:
      - unique
      - not_null

dbt turns these assertions into queries that look for counterexamples:

select person_id
from fct_person_example_register
where person_id is not null
group by person_id
having count(*) > 1

group by person_id collects records with the same identifier. having count(*) > 1 keeps groups with more than one row. The query returns the identifiers that disprove the uniqueness claim. The separate not_null test looks for missing identifiers.

Zero failing rows means this assertion holds for the data examined. In the first result, the test detects a duplicated identifier. It does not decide which row is correct. If an upstream join caused the duplication, repair that join and rerun the build rather than discard a row arbitrarily.

dbt build builds selected resources and runs the selected tests. dbt run runs models without their tests; dbt testcan test existing data without rebuilding the model. In this project's PR workflow, updates compile first and runtime validation occurs in the merge queue. The build chapter explains what each command establishes.

Write the model's meaning beside its SQL

The properties file gives the model a description, identifies its owner and attaches tests. Keeping it beside the SQL helps reviewers see whether the implementation and the stated meaning change together. The name below is a fictional register used to demonstrate the structure.

models:
  - name: fct_person_example_register
    description: >
      Current example-register population. One row per included person
      aged 18 or over who meets the documented clinical criteria,
      evaluated at the current build date.
    config:
      meta:
        owner:
          name: Your Name
    columns:
      - name: person_id
        description: Unique identifier for the included person
        data_tests:
          - unique
          - not_null
      - name: is_on_register
        description: True for every row because non-members are excluded
        data_tests:
          - accepted_values:
              arguments:
                values: [true]

The description states population, time and grain. The key tests enforce one row per included person. The flag description explains something that would otherwise be surprising: false rows do not exist in this model. The owner identifies where questions and proposed definition changes should go.

YAML syntax is simple but exact. A colon names a property, a dash begins an item in a list and indentation establishes ownership. This project uses two spaces for each level and spaces rather than tabs. A parser error usually points near the first place that the intended structure became ambiguous.

Good descriptions record decisions

A description should add information that the name and data type cannot provide. "Person identifier" does not help much if the column is already called person_id. "OLIDS person identifier retained at the model's one-row-per-person grain; never null" tells a consumer which identity system and row contract apply.

Model descriptions should normally settle the subject, population, reference time and grain. When filters or clinical rules define the population, the model description should explain the inclusion and exclusion rules, thresholds and date windows, and name the code list, value set or upstream definition used. An analyst should be able to tell who or what is selected without reading the SQL. Refer to a managed code list rather than copying a long list of codes into prose.

Column descriptions earn their place when they explain units, code systems, effective dates, null meaning, derivation or a non-obvious relationship. Repeating every column name in a sentence creates coverage without understanding.

Documentation should also distinguish an agreed definition from an implementation detail. A consumer needs to know that the model selects the latest qualifying blood pressure by clinical-effective date and how ties are resolved. They rarely need a prose transcription of the window function used to implement that rule.

Choose assertions from the contract

Start with a promise that could be broken, then choose a test capable of detecting that failure. Different tests protect different parts of the model.

The right test follows from a decision in the model contract. Adding every available generic test does not create quality. A not_nulltest is valuable when null would contradict the model's meaning; it is harmful when unknown is a legitimate clinical state and the test pressures developers to coalesce it into false or zero.

Here are examples of vocabulary and relationship assertions:

columns:
  - name: attendance_status
    description: Deliberately finite; a new value means the feed changed
    data_tests:
      - accepted_values:
          arguments:
            values: ['attended', 'did_not_attend', 'cancelled']
  - name: sk_patient_id
    data_tests:
      - relationships:
          arguments:
            to: ref('dim_person_demographics_basic')
            field: sk_patient_id

Read as assertions, both say something falsifiable. The first: this column's vocabulary is closed, and a value outside it is news, not noise. The second: every non-null person key in this model resolves to the chosen person spine. Whether each statement should be true is a design decision. The next sections examine cases where the choice needs care.

The grain test protects every downstream measure

Uniqueness and not-null tests do not prove that every expected person is present. An empty table can pass both. Population completeness needs separate evidence, such as reconciliation with the expected input population and checks of records excluded by the model.

The highest-value structural assertion is usually the one that enforces the model's grain. If a model promises one row per person, its person key should be unique and not null. If it promises one row per person, pathway and snapshot week, the combination is unique. Each individual column may repeat.

models:
  - name: example_pathway_history
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns:
              - person_id
              - pathway_id
              - snapshot_week

Separate uniqueness tests on those three columns would describe the wrong model: one person can have several pathways and every snapshot week contains many people. A row-count test would also miss many fan-outs because it cannot identify which rows are duplicated or whether the new total is legitimate. The composite key expresses the actual contract.

This is why grain documentation and grain testing belong together. The prose tells a reader what one row means; the assertion gives dbt a way to detect when an implementation change makes that statement false.

Relationship tests require domain judgement

A relationship test checks whether non-null child keys have matching parent keys. It does not establish that the parent key is unique. Test parent uniqueness separately if the join relies on one match, and add a not-null test only when missing child keys are invalid.

A relationship test asks whether values in one model exist in another. That sounds universally desirable, but it is only correct when both populations are meant to align. An events model may legitimately contain people outside a current active-patient dimension. A point-in-time fact should not necessarily resolve to a dimension that only represents today.

Before adding the test, state the intended relationship: every current medication fact belongs to a person in the current person dimension, or every organisation identifier resolves to the organisation reference model. If exceptions are valid, either select the appropriate parent population or encode the accepted exception. Do not weaken a failing test until it passes without first deciding what the relationship should mean.

Tests belong where the promise is made

A useful assertion is attached to the model that owns the corresponding promise. Staging can test the prepared source grain, required source keys and the finite vocabularies guaranteed by the feed contract. It should not normally assert a programme's definition of eligibility, because staging does not own that meaning.

Modelling models can test the reusable evidence or relationship they establish: one latest qualifying observation per person, no overlapping effective intervals, or a resolved code occurring only after a diagnosis. Reporting models protect business-ready populations, facts and dimensions. Their tests should make it difficult for a model to keep its name while silently losing its grain or clinical meaning.

Published models test the delivery contract they own. A dashboard base may require one row per person and practice, a fixed set of output statuses or the application of an audience-specific exclusion. Those checks do not belong in the shared facts merely because the product consumes them.

The same logical condition should not be copied onto every descendant. If a shared register fact guarantees unique people, a downstream product does not need to reimplement the register's clinical tests. It does need a grain test after joining that register to other models, because the composition introduces a new way for duplicates to appear.

This placement makes failures informative. A source-contract failure points to ingestion or source preparation. A register-population failure points to clinical definition logic. A published-grain failure points to product composition. Repeating broad checks everywhere produces noise; testing each layer's own promise narrows the search.

Semantic rules need semantic tests

Generic tests cover patterns that recur across many models. Domain rules are often more specific. A waiting-list decision date must not precede its referral date. A register reconstructed for a reference date must not use evidence recorded after that date. A clinical range may be impossible even though the value is non-null and correctly typed.

A singular data test expresses one of those rules directly as SQL in the tests/ directory. Like every data test, it returns the violating records:

select
    person_id,
    pathway_id,
    referral_request_received_date,
    decision_date
from {{ ref('example_pathway_history') }}
where decision_date < referral_request_received_date

This fictional example uses ref() so dbt records its dependency. The test runs when included by the command and test-selection rules. Returning the identifying columns also makes a failure easier to investigate than returning only a count.

Data tests and unit tests answer different questions

dbt distinguishes data tests from unit tests. Data tests examine the rows produced from real warehouse inputs and ask whether the resulting dataset obeys its assertions. Unit tests provide small, controlled input rows and compare the model's output with an expected result. They are useful for isolated, branch-heavy logic where important edge cases may not happen to exist in development data.

This project relies primarily on data tests, which run in development, CI and production. The distinction is still useful: a passing data test says no current row disproved the assertion. It does not prove that every branch of the SQL behaves correctly for inputs that are not present.

dbt's unit-test guide covers the controlled-input syntax. Use it where enumerating edge cases is clearer than waiting for production data to exercise them.

A passing suite cannot rescue the wrong contract

A passing data test means no current row disproved its assertion. A model can pass a uniqueness test while selecting the wrong clinical population. A generous row-count range can pass while a join doubles a small cohort. An accepted-values test can protect a vocabulary that should have allowed unknown.

Good tests therefore begin with design and review. First decide what the model means; then choose assertions capable of disproving important parts of that meaning. Data inspection, comparison with known totals, clinical review and reconciliation with existing models remain necessary evidence. The test suite preserves those decisions once they have been made.

After deployment, failures become operational evidence. The observing production lesson shows how test history, model results and downstream impact are investigated together.

Tests should make failure understandable

A test is operational documentation as well as a gate. Its name, arguments and returned columns should help the person responding to a failure understand which assumption broke. A singular test called assert_no_future_evidence_in_pit_register communicates more than assert_register_valid. Returning person, evidence and reference dates makes the violating relationship inspectable.

Broad plausibility tests require particular care. "The model must contain at least one row" catches a completely absent feed but says nothing about a 60% fall. A narrow expected range may create routine noise when activity is seasonal. Thresholds should reflect a known operational expectation, include an owner and change when the expectation changes. A failure needs investigation before any threshold is widened.

Stored failures can help investigation in development by materialising the records returned by a data test. They must be handled under the same data governance as the model itself. Failure rows are diagnostic data, not suitable material for a public PR or repository.

A failure changes what downstream users can trust

During dbt build, dbt builds a model and then runs its data tests. If an error-level test fails, the newly built relation normally remains in the warehouse. dbt records the test failure and skips affected selected downstream nodes. Existing downstream tables may therefore remain on their last successful version while direct consumers of the failed model can see its new rows.

A failed test is a trust signal and a control on further propagation, not a transaction rollback. Anyone investigating a production failure needs to know which relation failed, which descendants were skipped and whether existing outputs are stale.

Tests configured with severity: warn report a problem but do not block downstream execution. That is appropriate when a condition is informative or a source-quality issue is tolerated temporarily. It should not be used merely to make a red build green. Severity records the operational consequence the team has chosen for a broken assertion.

Debugging begins with the compiled test SQL. Running it shows the records that violated the rule. From there, determine whether the data changed, the model changed or the assertion encoded the wrong contract. Fixing the test is correct only in the third case.

Keep the description and evidence together

Return to the example register. Its description states the age and clinical criteria, whether it describes the current population or a past date, and whether non-members are absent. Its key tests protect uniqueness and presence. A rule about eligibility needs a separate assertion capable of finding an included person who does not meet that rule.

If a definition changes, update the SQL, description and relevant tests in the same change. Keep the dates and criterion fields that help explain why a person was included. The next reviewer should be able to compare the stated rule with the evidence, without reconstructing every transformation.

The YAML field guide covers generating a properties file and adding tests. Generation can supply column names and inherited descriptions; the author still needs to state the population, time, null meaning and decisions specific to this model.

A contract checklist

1. State the model's subject, population, reference time and grain.
2. Name an owner who can answer questions about the definition.
3. Explain population selection in the model description, including thresholds, date rules and the named code list or definition used.
4. Document units, codes and null meaning where they affect column interpretation.
5. Test the key or key combination that enforces the grain.
6. Add accepted-value and relationship assertions only where the domain contract makes them true.
7. Use singular tests for important business rules that generic tests cannot express.
8. Choose warning or error severity according to the intended operational response.
9. Treat generated YAML as a skeleton and review documentation whenever the model's meaning changes.

The official dbt data-test guide documents the available test forms and configuration. The earlier finding models lesson shows how a consumer uses these contracts to decide whether a model is safe to reuse.