Your DBT pipeline ran, but Your Data Was Wrong. Here’s the Fix. [DBT Series #4]

A practical guide to DBT generic tests, singular tests, and model documentation that catches silent failures before your stakeholders do.

You can read from here if you are not a Medium member.

In one of the projects I built, an upstream team renamed a column in one of their source tables, total_price became order_value. The staging model didn’t break. It compiled cleanly, ran cleanly, but produced a result: a column full of NULLs that landed in a revenue dashboard. No alert fired. No job failed.

Tests are what close that gap.

They’re the mechanism that catches a silent wrong answer before a stakeholder messages you saying the dashboard numbers were wrong. But tests alone aren’t enough — if the next person on the team doesn’t understand what your models promise, they can’t tell when something has changed.

That’s what documentation handles.

This article is in two parts. Part 1 covers how DBT tests work, the four generic tests, single-test business rules, severity levels, scoping, and how to write descriptions that actually communicate.

Part 2 applies all of it to the TPC-H project — we’ll create two schema.yml files from scratch, add tests and descriptions to both layers, and run the full test.

Part 1 — Core Concepts

How DBT Tests Work

A DBT test is a SQL query with one rule: it must return zero rows to pass. When you run dbt test, dbt compiles each test into a SELECT statement and executes it against your warehouse.

If any rows come back, the test fails. That’s the entire mechanism — no special test framework, no assertions library. Just SQL and a row count.

Tests live in two places. Generic tests are declared in schema.yml files alongside your models — you reference them by name, and dbt generates the SQL for you.

Singular tests are plain .sql files in a tests/folder that you write yourself for custom business rules.

We’ll cover both in this article, and in Part 2, we’ll see how they work together in the same layer.

The Four Generic Tests

DBT comes with four built-in generic tests. They work on any column in any model without writing custom SQL — you declare them in YAML and dbt handles the rest.

The four are:

• **unique** — checks that every value in the column is distinct. Use this on primary keys and any column that's supposed to identify a single record.
• **not_null** — checks that no value is NULL. Use this on columns that downstream models join on — a NULL foreign key means the join silently drops those rows from the result, and your aggregations come out short with no error to show for it.
• **accepted_values** — checks that every value in the column belongs to a defined list. Use this on status fields or any column with a fixed set of valid values, like a category or type field.
• **relationships** — checks referential integrity between two models: every value in a foreign key column must exist in the referenced column. Use this wherever a join between models is implied.

Think of these four as a checklist for trust. To make your data warehouse reliable, you want to know:

• Is this column unique where it should be?
• Is it populated?
• Does it contain only valid values?
• Does it point to something that exists?

If any answer is no, you want to find out from a test, not from a stakeholder query that returns unexpected zeros.

Generic tests are declared in a schema.yml file alongside your models. The structure is: model name, then columns, then tests under each column. We’ll write the full YAML in Part 2.

dbt test to improve data quality

Singular Tests

Generic tests cover structural guarantees — uniqueness, nullability, valid values, and referential integrity. However, sometimes you need to test a business rule: revenue should never be negative, or an order date cannot be in the future.

For those cases, DBT has singular tests: plain SQL files in a tests/ folder that contains one SELECT query.

The rule is the same as generic tests: the test passes if the query returns zero rows. A singular test for a future order date looks like this:

-- tests/assert_order_date_not_in_future.sql
-- Fails if any order has a date that hasn't happened yet.
-- A future date here usually means a data entry error or a timezone mismatch.

SELECT
    order_id,
    order_date
FROM {{ ref('stg_tpch__orders') }}
WHERE order_date > current_date

If stg_tpch__orders has even one row where order_date is in the future, this query returns that row, and the test fails. Singular tests are where you encode what your data is supposed to mean — not just what shape it has.

With the two test types in place, there’s one more thing worth knowing before we write any YAML:

What happens when a test fails, and how much control you have over it.

Test Severity: warn vs error

By default, a failing test stops the run with an error. That’s exactly what we want for primary keys — a duplicate order_id or a NULL customer_id would corrupt every join downstream, so halting the pipeline is the right call.

However, not every failure is that critical. For a status column where an unexpected value is worth investigating but doesn't make the numbers meaningless, you can set the severity to warn instead.

error is a hard stop — nothing moves forward until the problem is fixed. warn lets the run continue and surface the issue in the output for you to investigate.

• Set error on anything structural: null keys, duplicate primary keys, broken foreign key relationships.
• Set warn on quality signals you want to monitor without blocking every run: edge-case status values, coverage gaps, business rules that are aspirational rather than absolute.

Here is an example:

# Showing both severity levels side by side
columns:
  - name: order_id
    tests:
      - unique:
          severity: error    # duplicate order_id breaks downstream joins — stop the run
      - not_null:
          severity: error    # null primary key is unrecoverable

  - name: order_status
    tests:
      - accepted_values:
          values: ['F', 'O', 'P']
          severity: warn     # unexpected value is worth investigating, but not worth halting

When you’re writing tests on large tables, running every test against every row can get expensive. There’s a straightforward way to handle that:

scoping tests with a where clause

Scoping Tests with where

The where clause lets you scope a test to a subset of rows — typically the most recent data. This is especially useful for not_null and unique tests on tables with millions of historical rows you can’t modify, where the main concern is making sure new incoming data meets the expected shape.

Therefore, we can set a filter for the testing:

# Only test the last 30 days of order data
- name: order_id
  tests:
    - unique:
        where: "order_date >= current_date - interval '30 days'"
    - not_null:
        where: "order_date >= current_date - interval '30 days'"

However, the trade-off is real: scoping saves compute, but it means tests won’t catch problems in historical data, so use it when the table is genuinely large, and the risk is concentrated in new rows — not just to make test runs faster on a project where a full table scan is already quick.

Once tests are in place, the next question is whether the people using these models — or the future version of you — can understand what each column actually means. That’s where descriptions come in.

dbt document

Writing Descriptions That Actually Help

DBT lets you add descriptions to models and columns directly in YAML. Most teams treat this as a chore and write things like “This is the orders table” or “The order ID column.”

Those descriptions are worse than nothing — they take up space and communicate nothing a reader couldn’t infer from the name.

A useful description answers two questions:

• What does this column contain?
• What should a downstream user know before they use it?

For a column like **total_price**, a weak description is “The total price of the order.” A useful one is: “The total price of the order in USD, before discounts are applied. Does not include shipping costs.”

Here’s why it matters in practice. Three months after building stg_tpch__ordersmodel, you’re adding a new mart model, and you need to know: does total_price include shipping or not?

Without a description, you’re opening the staging SQL, tracing back through the source table, and hoping the column name in the raw data tells you something.

With one sentence in the YAML, your past self already answered the question. The description is your future self’s shortcut — and it’s equally useful for anyone else who builds on top of your models.

With the concepts covered, the next section applies everything to the TPC-H project. We’ll create both schema.yml files from scratch, add tests and descriptions layer by layer, and run the full test.

Part 2 — Apply to TPC-H

Up to this point, the TPC-H project has SQL files for each model, but no schema.yml files yet. The SQL defines what each model selects and transforms — but nothing tests the output or explains what any column means.

We’re going to create two files: models/staging/schema.yml and models/marts/schema.yml. Each one holds the tests and descriptions for models in that layer.

Setting Up: What the Files Look Like Before Tests

Here’s what both files look like before we add anything — just the model and column names.

# models/staging/schema.yml — starting state, no tests or descriptions yet

version: 2

models:
  - name: stg_tpch__orders
    columns:
      - name: order_id
      - name: customer_id
      - name: order_status
      - name: total_price
      - name: order_date

  - name: stg_tpch__customers
    columns:
      - name: customer_id
      - name: customer_name
      - name: nation_key

# models/marts/schema.yml — starting state, no tests or descriptions yet

version: 2

models:
  - name: fct_order_revenue
    columns:
      - name: customer_id
      - name: customer_name
      - name: total_revenue
      - name: order_count

One thing worth noting before we add tests: the column names here are the renamed names from your staging SQL — order_id, not o_orderkey; total_price, not o_totalprice.

DBT tests run against the model’s output, not the raw source.

dbt model schematic disgram

Adding Tests and Descriptions to Staging

Now we fill in models/staging/schema.yml. This is the complete file — generic tests, severity configuration, descriptions, and a reference to the singular test, all in one place.

# models/staging/schema.yml

version: 2

models:
  - name: stg_tpch__orders
    description: >
      Cleaned and renamed version of the raw TPC-H orders table.
      Each row represents a single customer order. total_price is in USD
      and reflects the pre-discount order total, excluding shipping.
    columns:
      - name: order_id
        description: Primary key — unique identifier for each order.
        tests:
          - unique:           # every order must have a distinct ID
              severity: error
          - not_null:         # a null here breaks every downstream join
              severity: error

      - name: customer_id
        description: Foreign key to stg_tpch__customers. Links each order to its customer.
        tests:
          - not_null:
              severity: error
          - relationships:
              to: ref('stg_tpch__customers')  # every customer_id must exist in the customers model
              field: customer_id
              severity: error

      - name: order_status
        description: >
          Current status of the order. Valid values are F (fulfilled),
          O (open/in progress), and P (partially fulfilled).
          Single-character codes from the TPC-H source — not human-readable strings.
        tests:
          - not_null:
              severity: error
          - accepted_values:
              values: ['F', 'O', 'P']  # TPC-H domain values — see friction note below
              severity: warn            # unexpected value is worth investigating, not worth halting

      - name: total_price
        description: >
          Total value of the order in USD, before discounts are applied.
          Does not include shipping costs.
        tests:
          - not_null:
              severity: error

      - name: order_date
        description: >
          Date the order was placed. Must not be a future date —
          enforced by the singular test assert_order_date_not_in_future.sql
          in the tests/ folder.
        tests:
          - not_null:
              severity: error

  - name: stg_tpch__customers
    description: >
      Cleaned version of the raw TPC-H customer table.
      Each row represents one customer account.
    columns:
      - name: customer_id
        description: Primary key — unique identifier for each customer.
        tests:
          - unique:
              severity: error
          - not_null:
              severity: error

      - name: customer_name
        description: Customer name as provided in the source system.
        tests:
          - not_null:
              severity: error

      - name: nation_key
        description: Foreign key to the nation dimension. Links each customer to their country.
        tests:
          - not_null:
              severity: error

The singular test for order_date isn't listed anywhere in this file — but dbt still runs it. Here's how that works.

DBT automatically discovers any .sql file placed in the tests/ folder at the root of your project. You don't register it, import it, or reference it from schema.yml. The folder structure looks like this:

your_dbt_project/
├── models/
│   ├── staging/
│   │   └── schema.yml
│   └── marts/
│       └── schema.yml
├── tests/
│   └── assert_order_date_not_in_future.sql  ← dbt finds this automatically
└── dbt_project.yml

When you run dbt test, dbt scans the tests/ folder and runs every .sql file it finds alongside the generic tests from schema.yml.

The assert_ prefix in the filename is a community convention — not a dbt requirement — but it’s worth adopting because it signals intent at a glance: this file asserts that something must be true about the data.

Adding Tests and Descriptions to the Mart Layer

The same pattern applies to the mart layer. fct_order_revenue is the model most likely to be queried by someone who wasn’t around when it was built, so the descriptions here matter as much as the tests.

# models/marts/schema.yml

version: 2

models:
  - name: fct_order_revenue
    description: >
      Aggregated order revenue by customer, calculated from stg_tpch__orders
      and stg_tpch__customers. Each row represents one customer's total
      order activity. Revenue figures are pre-discount and exclude shipping.
      Refresh cadence: daily.
    columns:
      - name: customer_id
        description: Foreign key to stg_tpch__customers. Unique per row in this model.
        tests:
          - unique:
              severity: error
          - not_null:
              severity: error

      - name: customer_name
        description: Customer name sourced from stg_tpch__customers.
        tests:
          - not_null:
              severity: error

      - name: total_revenue
        description: >
          Sum of total_price across all orders for this customer.
          Denominated in USD. Pre-discount, excludes shipping.
          Sourced from stg_tpch__orders — null-safety on total_price
          is enforced at the staging layer.
        tests:
          - not_null:
              severity: error

      - name: order_count
        description: Total number of orders placed by this customer in the dataset.
        tests:
          - not_null:
              severity: error

Notice the total_revenue description names its source model and points to where the null-safety is enforced. Anyone reading this description doesn’t have to trace back through the SQL to find out where that guarantee lives — it’s written down right here.

That’s the dependency between staging and mart made visible in plain text.

Running dbt test and reading the Output

Once both files are saved and the singular test is in the tests/ folder, run the full suite:

dbt test

When tests pass, the output looks like this:

dbt results

When a test fails, dbt tells you exactly which test, which model, and how many rows violated the condition:

18:43:11  Failure in test accepted_values_stg_tpch__orders_order_status__F__O__P (models/staging/schema.yml)
18:43:11  Got 1 result, configured to fail if != 0
18:43:11  compiled code at target/compiled/...sql

That “compiled code at” path is useful. dbt writes the actual SQL it ran into the target/ folder. Open that file when a test fails — it’s the fastest way to understand what the test was checking and to query the data yourself to see what came back.

Generating Documentation

With descriptions written, two commands build and serve the documentation site:

dbt docs generate  # builds the docs site from your YAML and model metadata
dbt docs serve     # opens it in your browser at localhost:8080

The lineage graph shows every model annotated with its description. Click any node to see its columns, their descriptions, and the tests applied.

For the TPC-H project at this point, the graph runs from the raw source tables through staging and intermediate to the mart layer — all labelled, all cross-referenced.

What We Built

We created two schema.yml files — one for staging, one for marts — and added tests and descriptions to both. If the DBT test returns all passes, what you have isn’t just a working project — it’s one with a defined and testable contract that anyone on the team can read.

In the next article, we will talk about adding freshness thresholds and loading a seed lookup table that the staging models can join against.

If you like this article and want to show some love:

• Clap 50 times — each one helps more than you think! 👏
• **Follow me**, so you won’t miss it when a new article is published
• You can buy m**e a Coffee** to support me further.
• Let’s connect with me at **LinkedIn or lhungen@gmail.com to chat more about data!**

I also want to share with you some great articles to help you explore the world of analytical engineering.

[embed]What Analytics Engineers Actually Do - and Why They All Use DBT [DBT Series #1] Learn what dbt is, why the analytics engineering role exists, and how dbt brings version control, testing, and…levelup.gitconnected.com

[embed]Setting Up dbt with DuckDB: From Zero to Your First dbt Model [DBT Series #2] for data analysts and analytics engineers starting with dbt. Learn how to install dbt Core, connect it to DuckDB, and…levelup.gitconnected.com

[embed]The dbt Architecture That Keeps Your SQL Manageable [DBT Series #3] A practical guide to staging, intermediate, and marts, as well as how ref() and source() wire it all togetherlevelup.gitconnected.com

[embed]Data Modeling for Data Engineers: OLTP, OLAP, Inmon, and Kimball Explained OLTP, OLAP, Inmon, and Kimball as separate topics. This article shows how they connect in a single data journey — from…blog.dataengineerthings.org