Choose a transformation with meaningful edge cases

Start with a model that contains a join, conditional classification, or date boundary. Supply a small synthetic input that distinguishes the intended output from a plausible mistake. For a left join, include an unmatched row so the test can reveal whether a later filter accidentally removes records that should remain in the result.

Keep expected rows explicit and minimal. Repeating the model's SQL in the expectation can reproduce the same error instead of challenging it. Include nulls or duplicate keys when the business rule defines their handling, and describe the intended row grain so a changed join cannot silently multiply the output without detection.

Define inputs and expected outputs clearly

Use the model name and given input references in the documented YAML structure. Provide the relevant columns for each fixture and the expected output rows. Check the installed dbt version and adapter documentation because unit testing support and fixture requirements can differ across model types and execution environments in existing projects.

Where the model depends on incremental behavior, specify the relevant macro override and expected branch deliberately. The test should state whether it represents a full-refresh or incremental scenario. Do not assume that one fixture exercises both paths, because a correct result in one branch does not establish the other branch's filtering or merge-related logic.

Separate logic tests from live-data evidence

A unit fixture can establish how supplied rows transform, but it does not prove that production data contains valid keys or that upstream sources arrive on time. Keep uniqueness, non-null, relationship, and freshness checks in their appropriate data-quality workflows. These checks answer different questions and should not be replaced by a small synthetic example alone.

Run unit tests through the project's selected dbt workflow and inspect unexpected output rows carefully. A failing join case may reveal a grain assumption rather than a syntax error. Add adapter-specific integration evidence where necessary, and document unsupported model or fixture patterns instead of reshaping the test until it makes a broader claim than the environment supports.

Working sheet

dbt unit fixture cases
Transformation riskInput exampleExpected behavior
Left join filteringUnmatched source rowRow retained with null match
Classification boundaryValue at thresholdDefined category
Null handlingMissing optional valueDefined fallback
Duplicate join keyRepeated lookup keyIntended output grain
Incremental branchExplicit macro overrideExpected selected rows
Download this worksheet (CSV)

Questions about test dbt model logic with controlled input rows

Do dbt unit tests replace data tests?

No. Unit tests check transformation logic on controlled rows; data tests check properties of actual datasets.

Can every dbt model use the same fixture setup?

Check model type, adapter, and dbt version requirements. Some inputs and patterns need specific fixture formats or setup.

How should I use the working sheet?

Download the CSV and replace the sample rows with evidence from your repository. Record a clear acceptance check and an owner for each item. Keep unresolved assumptions visible rather than treating the example as a completed assessment.

Sources and further reading

Examples are illustrative. They are not customer results, security guarantees, or a substitute for checking the documentation for your installed version.

Make the next change verifiable.

Getzilla is an open source workflow for changes to existing repositories. Inspect the project, define acceptance checks, and keep independent review in the loop.

See the repository workflow See current access and pricing