Creating a Data Model w/ dbt: Tests (Part 3/3)

modeling Jul 16, 2025

Building the tables is only the first step, because data and the custom logic you write on top of it are rarely perfect. The cheapest useful safety net in dbt is a pair of generic tests, unique and not_null, declared on the surrogate key of every warehouse model. You write them in the schema YAML file that already sits beside your models, run dbt test, and dbt compiles a small SQL query per test, sends it to the warehouse, and tells you what failed. Once that is in place, dbt build replaces running dbt run and dbt test separately by deploying and testing in one pass. That is enough to catch the obvious problems a new star schema introduces without overcomplicating things on day one.

Key takeaways

Test the keys first. unique and not_null on every surrogate key catch most of what goes wrong in a new model.
Tests live in the schema YAML beside the models, not in separate files. That is what those YAML files were waiting for.
You do not need to list every column. Listing one key column per model is more useful than exhaustive coverage nobody reads.
A generic test compiles to a SQL query that counts the rows breaking the rule. Zero rows passes, anything else fails.
dbt build runs and tests in one command, in dependency order, instead of two separate passes.
A failing test stops the run and names the test and the model, which is what makes this worth wiring into automation.

Why tests before you push the warehouse models

This is the last of three parts. Part 1 created the dimensions and Part 2 created the fact table and the joins between them.

Those joins are custom logic we wrote by hand. Custom logic is exactly where duplicates and missing keys sneak in, and nobody notices until a number looks wrong in a report.

So one more step before the branch gets merged. A sanity check on the primary keys, which here means the surrogate keys generated in the last two parts.

dbt has this built in. If you use a different tool, the idea travels: write a query that returns the bad rows and fail when it returns any.

The risk is quiet, not loud

A broken join rarely errors. It returns rows, just the wrong number of them, and the model builds green.

With a small practice dataset you can scroll through the output and see that. With real volumes you cannot, so the check has to be something the pipeline performs for you.

The built-in generic tests

A generic test is a reusable test you declare by name rather than write as SQL. dbt ships with four of them out of the box:

unique: no duplicate values in the column.
not_null: no missing values in the column.
accepted_values: every value comes from a list you supply.
relationships: every value exists in a column of another model.

The first two are what I care about here. Together they assert that a surrogate key is a real primary key, which is the single most valuable claim you can make about a warehouse table.

You declare them by adding a tests block to a column. dbt reads that, compiles a small piece of SQL for each entry, and runs it against your database.

No test framework to install and nothing to configure. The whole thing is a few lines of YAML in a file you already have.

Where the YAML goes

Back in Part 1 we created schema YAML files alongside the models and left them mostly empty. This is what they were for.

Add one per subfolder, so the tests sit next to the models they describe:

models/warehouse/dimensions/_dimensions__models.yml
models/warehouse/facts/_facts__models.yml

We are not testing the staging models here. You could, and on a larger project you probably should, but the keys we just invented are the higher risk.

What the file looks like

Same shape as before, with column level detail added underneath each model.

version: 2

models:
  - name: dim_teams
    description: One row per team.
    columns:
      - name: team_sk
        description: Surrogate key for a team record.
        tests:
          - unique
          - not_null

  - name: dim_games
    columns:
      - name: game_sk
        tests:
          - unique
          - not_null

  - name: dim_calendar_dates
    columns:
      - name: calendar_date_sk
        tests:
          - unique
          - not_null

You could technically list every column and attach tests everywhere. It is not necessary right now and probably not helpful, so the only column listed is the surrogate key.

The fact table file

The fact model gets the same treatment, pointed at the key we generated in Part 2.

version: 2

models:
  - name: fct_games_played
    description: One row per game played.
    columns:
      - name: game_played_sk
        tests:
          - unique
          - not_null

Two tests, one column, one model. That is the baseline, and it is deliberately small so it actually gets written.

Run the tests

dbt test

dbt looks for any YAML with a tests block and runs what it finds. It is fast, and the output is one line per test with a pass or fail.

You can scope it the same way you scope a run, which is handy once the project grows.

dbt test -s warehouse.facts

Read the compiled SQL

Each test becomes a real query. Look under target/compiled/ and you can open the exact SQL that was sent to the database.

select team_sk as unique_field,
       count(*) as n_records
from analytics.dbt_dev.dim_teams
where team_sk is not null
group by team_sk
having count(*) > 1

That is the whole mechanism. The test counts records that fit the failure condition, and the result decides pass or fail.

Worth opening once. Seeing that a test is just a query removes the mystery and makes it obvious how to write your own later.

Use dbt build instead of two commands

Most of the time you want to deploy and then immediately test. Running dbt run and then dbt test every time gets old.

dbt build -s warehouse

One command, both jobs. dbt deploys each model and tests it, in dependency order, so a model whose tests fail does not quietly feed the things downstream of it.

For the warehouse folder that means three dimensions and one fact built and checked in a single pass.

It is also the command I point at a schedule. One entry point, one exit code, and the orchestration layer does not need to know how many models are behind it.

What a failure looks like

It is worth seeing a red result on purpose. Hard code one of the key columns to null, rebuild, and the not_null test fails on every row.

dbt build -s fct_games_played

The output names the failing test and the model it belongs to, and points you at the query so you can inspect the offending rows.

Undo the change, rebuild, and it is green again. The reason to do this once is automation: in a scheduled run that failure is how you find out early, instead of hearing it from a stakeholder.

There is a second reason to break it on purpose. You learn where the failure shows up in the output, which is the thing you will be hunting for at eight in the morning.

Commit and merge

One more commit, something like add data quality tests, and the branch is ready.

What is in the development environment now is a complete first pass. Staging models as views, a star schema of three dimensions and one fact table, and a quality check on every key in it.

From here the marts layer gets built on top, and it inherits both the joins and the guarantees underneath them.

Key terms

Generic test

A reusable dbt test you attach to a column by name in YAML, which dbt expands into a SQL query at run time.

unique test

A generic test that fails if a column contains any duplicate value, used to assert that a surrogate key identifies exactly one row.

not_null test

A generic test that fails if a column contains any null, used alongside unique to make a column behave as a primary key.

Compiled test SQL

The query dbt writes to the target directory for each test, which counts the rows that break the rule and is what actually runs in the warehouse.

dbt build

The command that materializes a model and runs its tests in one pass, in dependency order, instead of a separate dbt run and dbt test.

Common questions

What tests should I add to a new dbt model?

Start with unique and not_null on the primary or surrogate key. That one pair catches duplicate rows and missing keys, which is most of what goes wrong when you first write joins.

Where do dbt tests go?

In the schema YAML file that sits beside the models, under a tests block on the column. One YAML per model subfolder keeps the tests next to what they describe.

What is the difference between dbt test and dbt build?

dbt test only runs tests against models that already exist. dbt build materializes each model and tests it in the same pass, following dependency order, so a failure stops the chain early.

Do I need to test every column?

No, and trying to usually produces noise nobody reads. Test the keys, then add targeted tests where a specific assumption matters, such as an accepted list of status values.

What happens when a dbt test fails?

The run reports a failure naming the test and the model, and the compiled query is available so you can pull the offending rows. In an automated schedule that is the signal that something upstream changed.

Should I test staging models too?

You can. On a small project the warehouse keys are the higher risk because you generated them yourself. As the project grows, source and staging tests are worth adding so bad data is caught earlier.

Final takeaway

Two tests on one column per model is a small amount of work for the amount of trust it buys. Every warehouse layer I help a team stand up gets these before the branch merges, because the first duplicate key is always cheaper to find in a test than in a dashboard.

Additional Free Resources

Starter Guides & Checklists

Explore additional free resources built on the same patterns I use with real clients so you can build your own with structure and confidence. Topics include data architecture, modeling and more specifically for small data teams.

Browse Resources