dbt / Basic: project and model foundations / Lesson 6
Data tests and failing records
Turn loan invariants into explicit failing-row queries without confusing tests with continuously enforced database constraints.
On this page
Lesson goals
- Distinguish singular and generic data tests.
- Interpret zero failures within a stated population and severity policy.
A data test looks for counterexamples
A typical data test is a select query returning records that violate an assertion. Under ordinary default failure rules, zero failing records means the test passes for the evaluated population at that time. That is not a continuously enforced primary key or a guarantee about tomorrow’s import. Scope filters, source completeness and test severity still matter. A uniqueness test on an empty model can pass while the feed is missing. Define what assertion is being checked, which rows are eligible and what operational response follows a failure. Do not call every successful test proof of complete business correctness.
Reuse a parameterized assertion with explicit arguments
The YAML example attaches unique and not_null to loan_id, and accepted_values to a controlled loan_state. Its nested arguments structure is selected for the v1.12 baseline; older examples may place generic-test arguments differently. accepted_values does not replace a required-value test, because ordinary NULL handling can exclude missing values. The test declarations describe intended warehouse queries but are not executed here. A failed unique assertion requires distinguishing true source duplication from an incorrect join or revision-selection rule before deleting any records.
version: 2
models:
- name: fct_loans
columns:
- name: loan_id
data_tests: [unique, not_null]
- name: loan_state
data_tests:
- accepted_values:
arguments:
values: ['open', 'returned']Illustrative v1.12 generic-test properties. The source relation must already exist in an authorized test environment; no tests or constraints are created here.
Write a focused query for a domain violation
The singular test finds loans whose known return time precedes their borrow time. NULL return values do not qualify under the comparison, so missingness needs a separate assertion if the domain requires it. Returning the loan identity and timestamps makes diagnosis concrete without selecting every unrelated field. A test file should express the failing population, not count valid rows and expect dbt to infer the opposite meaning. Database timestamp comparison semantics still apply. Preserve source provenance so the team can repair a producer defect rather than merely suppress the failing record.
select loan_id, borrowed_at, returned_at
from {{ ref('fct_loans') }}
where returned_at < borrowed_atA singular failing-row test fragment. A zero-row result would pass this assertion under ordinary defaults, but no warehouse query is run.
Severity and stored failures change operational behavior
Tests can use severity and configured failure thresholds, so a warning is not the same outcome as an error. Review build and orchestration behavior for downstream resources rather than assuming every failed assertion atomically rolls back all models. Storing failures can create or replace audit relations and expose sensitive loan records; it is a database write requiring approved scope and permissions. Treat failure data as protected operational evidence. No store_failures, build or test command is invoked by this course. A textual YAML review cannot establish either permission or database execution success.
Test the assertion’s blind spots deliberately
Keep an exact fixture with a valid return, a reversed return, a NULL return, duplicate identity and an empty input. Predict which assertion should fail for each case. Add expected-population and relationship checks where business completeness demands them, while avoiding redundant tests that merely repeat the implementation. Generic tests are reusable query contracts; unit tests later isolate transformation logic with fixtures. The two forms complement each other and neither eliminates integration or recovery checks. Validation here uses only tiny local models of failing-row selection, with no dbt runner or database constraint test claimed.
Common errors and fixes
A passing unique test is called an enforced primary key
- Why it happens
- Point-in-time query validation was confused with database enforcement.
- How to fix it
- State the evaluated population and verify actual constraint support separately.
- Prevent it next time
- Keep data tests and database constraints distinct in documentation.
A test returns valid records and fails unexpectedly
- Why it happens
- The query’s failing-row convention was inverted.
- How to fix it
- Return counterexamples to the assertion.
- Prevent it next time
- Check a known-good and a known-bad fixture by hand.
Stored test failures expose sensitive records
- Why it happens
- An audit relation was treated as harmless console output.
- How to fix it
- Review storage, access, retention and selected fields before enabling it.
- Prevent it next time
- Treat test failure datasets as governed data assets.
Check your understanding
1. Does the reversed-return test fail a row whose returned_at is NULL?
Show worked solution
Not under the ordinary comparison shown: the predicate is unknown and the row is not selected. Add a requiredness or completeness assertion only if the business rule demands it. Keep legitimate open loans separate from malformed timestamps that became NULL.
2. Why can an empty fact model pass uniqueness while still being unacceptable?
Show worked solution
There are no duplicate counterexamples, so the uniqueness assertion can pass. Expected source population and delivery are different requirements. Reconcile counts and source identity or freshness evidence before publishing an empty report as a valid business result.
Key takeaways
- A data test’s failing population and severity define its meaning.
- Passing assertions do not automatically prove completeness or continuous enforcement.