dbt Tests Explained: Types, Business vs. Technical Data Quality (BDQ & TDQ)

Tests are the reason dbt is trusted in production at all. Here's every test type dbt supports, why the business actually cares, and how they split cleanly into Technical Data Quality and Business Data Quality — with code for each one.

By Rakesh Madala — 10 years in the data field

Every analytics engineer has a story about a dashboard number that was wrong for weeks before anyone noticed. Maybe a join silently fan-out duplicated rows, maybe a source system started sending nulls where it never had before, maybe someone changed a currency field's definition upstream without telling anyone downstream. The number looked plausible enough that nobody double-checked it, and by the time finance caught the discrepancy, three board reports had already gone out with it baked in.

dbt tests exist to catch exactly that class of failure before it reaches a human. They're one of the smallest features in dbt to describe and one of the largest in terms of the trust they buy — turning "I'm fairly confident this number is right" into "this number passed thirty-eight automated checks before it was allowed anywhere near a dashboard." This article goes deep on what dbt tests actually are, the different types available, why a business — not just a data team — should care about each of them, and how they map onto two useful umbrella categories: Technical Data Quality (TDQ) and Business Data Quality (BDQ). Every pattern below comes with a working code example.

What dbt Tests Actually Are

A dbt test is, underneath everything, just a SQL query that's expected to return zero rows. If it returns rows, those rows represent the records that failed the check — a duplicate primary key, a null in a column that should never be null, an order total that doesn't match the sum of its line items. Run dbt test (or dbt build, which runs models and tests together in dependency order), and dbt executes every test defined across your project, reports which ones passed, and — critically — tells you exactly which rows failed the ones that didn't.

That "zero rows means pass" convention is the entire mechanism. There's no special test engine, no separate DSL to learn, no external service to configure. It's SQL, compiled and executed against your warehouse the same way a model is, which is exactly why it fits so naturally into a workflow analytics engineers already understand.

dbt tests live alongside the models they check, in the same Git repository, reviewed in the same pull requests. That proximity matters more than it sounds: a test isn't a separate QA activity bolted on after the fact, it's part of the definition of the model itself. If you can't state what "correct" looks like for a table in a testable way, you probably haven't finished specifying what that table is supposed to do.

Why the Business Needs This, Not Just the Data Team

It's tempting to file data testing under "engineering hygiene" and move on, but the actual cost of untested transformations lands squarely on the business side, not the data team. A finance team that reforecasts revenue off a number quietly inflated by duplicate rows makes a real decision on bad information. A marketing team that targets a customer segment built on a broken join wastes real budget. A product team that ships a feature based on an engagement metric that double-counted sessions draws the wrong conclusion about what users actually want.

None of these failures announce themselves. A broken pipeline that throws an error is, in a strange way, the easy case — someone notices immediately because nothing loads. The dangerous failures are the ones that complete successfully and produce a number that's simply wrong, silently, indefinitely, until someone downstream happens to sanity-check it against another source and finds a gap. By that point the bad number may have already influenced a budget, a hiring decision, or an investor update.

Before systematic testing became normal, catching these issues depended entirely on someone's vigilance — an analyst who happened to eyeball last week's numbers, a stakeholder who happened to notice something felt off. That's not a data quality strategy, that's luck. dbt tests replace luck with a standing, automated contract: every time the warehouse rebuilds, every rule gets re-checked, and nothing ships downstream without passing them first.

This is also why the split between Technical Data Quality and Business Data Quality, covered in detail further down, matters so much to how a business should think about testing investment. TDQ failures break pipelines and corrupt structure — they're usually obvious once you're looking. BDQ failures produce numbers that are structurally perfect and substantively wrong — a report that runs cleanly, joins correctly, has no nulls, and still tells the CFO the wrong thing because a business rule was violated somewhere in the transformation logic. A mature testing strategy needs both, and most teams that get burned badly by data quality issues got burned by BDQ gaps, not TDQ ones.

How dbt Tests Work Under the Hood

Every dbt test compiles down to a SQL SELECT statement dbt wraps and executes for you. Whether you're using a one-line built-in test or hand-writing custom SQL, the underlying contract is identical: return the offending rows, or return nothing.

Take the simplest possible example — checking that a column has no duplicate values. Conceptually, that's just:

SELECT order_id, COUNT(*) AS n
FROM {{ ref('fct_orders') }}
GROUP BY order_id
HAVING COUNT(*) > 1

If that query returns any rows, order_id isn't actually unique, and the test fails with those exact rows as evidence. dbt ships this pattern as the built-in unique test so nobody has to hand-write it, but it's worth seeing the SQL underneath at least once — it demystifies the whole feature. Every other test type in this article, no matter how it's declared, ultimately reduces to the same "rows returned equal rows failed" mechanic.

Run tests with dbt test on their own, target a single model with dbt test --select fct_orders, or run models and their tests together with dbt build, which stops downstream models from building on top of data that just failed a test upstream — a detail that matters more than it might first appear, since it's what actually prevents a bad batch of source data from quietly propagating through your entire mart layer.

Type 1 — Generic (Schema) Tests

Generic tests, sometimes called schema tests, are the tests most people mean when they first hear "dbt has built-in tests." They're reusable, parameterized checks you attach to a column or model with a few lines of YAML — no SQL required. dbt ships four out of the box, and they cover a surprising share of what most tables actually need.

models:
  - name: fct_orders
    description: "One row per order, grain: order_id."
    columns:
      - name: order_id
        description: "Primary key."
        tests:
          - unique
          - not_null
      - name: order_status
        tests:
          - not_null
          - accepted_values:
              values: ['placed', 'shipped', 'delivered', 'returned', 'cancelled']
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

Between them, these four built-ins — unique, not_null, accepted_values, and relationships — catch the majority of structural mistakes: duplicate keys, missing required fields, values that fall outside a known enum, and foreign keys pointing at rows that don't exist. Run dbt test and every one of these gets checked across the whole project in a single command, with zero SQL written by hand.

What makes generic tests worth the name is that they're not limited to the four built-ins. Any test — including your own custom ones, covered below — is "generic" in dbt's terms as long as it's written once as a reusable macro and then applied declaratively wherever it's needed, on any column, in any model, without rewriting the logic each time.

Type 2 — Singular Tests

Not every business rule fits neatly into "is this unique" or "is this value in a list." Singular tests are for everything else — a standalone .sql file in your project's tests/ directory, written as a query that should return zero rows. There's no YAML, no parameters, just SQL that expresses whatever specific condition you need checked.

-- fails if any order's stored total drifts from the sum of its line items by more than a cent
WITH order_totals AS (
    SELECT
        order_id,
        order_total
    FROM {{ ref('fct_orders') }}
),

line_item_sums AS (
    SELECT
        order_id,
        SUM(line_amount) AS computed_total
    FROM {{ ref('fct_order_lines') }}
    GROUP BY order_id
)

SELECT
    order_totals.order_id,
    order_totals.order_total,
    line_item_sums.computed_total
FROM order_totals
JOIN line_item_sums USING (order_id)
WHERE ABS(order_totals.order_total - line_item_sums.computed_total) > 0.01

This is where dbt testing starts doing real work for the business, rather than just structural housekeeping. A test like this one has nothing to do with nulls or duplicates — it encodes an actual accounting rule specific to how this company defines a correct order total, and it will keep enforcing that rule automatically every single time the pipeline runs, without anyone having to remember to check it manually again.

Singular tests are the natural home for anything that's a one-off business rule rather than a reusable pattern: "refund amounts should never exceed the original order total," "a subscription's end date should never be before its start date," "no invoice should be marked paid with a payment date before its issue date." Each of these is a real rule a real business cares about, and each one takes about ten lines of SQL to enforce permanently.

Type 3 — Custom Generic Tests

Singular tests solve a one-off problem in one place. Custom generic tests solve the same shape of problem everywhere it recurs, by turning a singular test's logic into a reusable, parameterized macro — the same mechanism the built-in unique and not_null tests are actually built on internally.

Say a company needs to validate email format across several tables — customers, leads, support tickets — not just once. Writing that regex logic three separate times is exactly the kind of duplication dbt otherwise pushes hard against. A custom generic test fixes that:

{% test is_valid_email(model, column_name) %}

SELECT *
FROM {{ model }}
WHERE {{ column_name }} IS NOT NULL
  AND {{ column_name }} NOT REGEXP_LIKE(
        {{ column_name }},
        '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
      )

{% endtest %}

Once that macro exists, applying it anywhere is one line of YAML, exactly like a built-in test:

models:
  - name: stg_customers
    columns:
      - name: email
        tests:
          - is_valid_email
  - name: stg_leads
    columns:
      - name: contact_email
        tests:
          - is_valid_email

This is where a data team's accumulated knowledge of "what correct looks like for our business" starts compounding instead of being rewritten from scratch on every new table. Custom generic tests are also how most of the community's shared testing packages, covered later in this article, are built and distributed in the first place — a well-designed custom test in one project is often generic enough to become a package used across hundreds of others.

Type 4 — Unit Tests

Every test type covered so far checks data that's already been built — real rows, sitting in a real warehouse, evaluated after a model has run. Unit tests work differently, and deliberately so: they check a model's logic against small, hand-crafted, fictional input rows, independent of whatever is actually sitting in your warehouse right now.

This matters most for models with meaningful business logic — a case statement calculating a discount tier, a currency conversion, a complex deduplication rule — where you want to know with certainty "does this SQL produce the right output for this exact input," rather than "does today's real data happen to look fine." A unit test can assert that behaviour before the model ever touches production data.

unit_tests:
  - name: test_discount_tier_logic
    description: "Orders over 1000 get a 10% tier; under, 0%."
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 'A1', order_total: 1500, order_status: 'placed'}
          - {order_id: 'A2', order_total: 400,  order_status: 'placed'}
    expect:
      rows:
        - {order_id: 'A1', discount_tier: 'high', discount_pct: 0.10}
        - {order_id: 'A2', discount_tier: 'standard', discount_pct: 0.00}

The distinction is worth being precise about, because it changes what each test type can actually catch. A generic or singular test run against real production data can only tell you a rule was violated somewhere in whatever rows happen to exist today — if no order currently triggers the edge case, the bug stays invisible. A unit test constructs the edge case on purpose, on fictional data, so the logic gets verified regardless of what today's warehouse contains. The two approaches are complementary, not competing — most mature dbt projects lean on data tests for ongoing production monitoring and unit tests for verifying tricky logic before it ships.

Type 5 — Source Freshness Tests

Every test type above checks whether data is correct. Freshness tests check something different and equally important: whether data has arrived at all, on schedule. A perfectly accurate table built from yesterday's stale extract will pass every other test in this article while still being wrong for entirely different reasons — it's simply out of date.

sources:
  - name: raw_ecommerce
    database: raw
    schema: ecommerce
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 6, period: hour}
          error_after: {count: 24, period: hour}

Running dbt source freshness checks the most recent timestamp in _loaded_at against the current time, and flags a warning or a hard error depending on how stale the data has become. For a business dashboard that's supposed to reflect "today," a freshness failure is often the single most business-critical alert in the whole testing suite — it's the difference between "the numbers are wrong" and "the numbers stopped updating three days ago and nobody told the people relying on them."

Technical Data Quality (TDQ)

Technical Data Quality is the umbrella for everything that checks whether data is structurally sound — correctly shaped, correctly typed, correctly connected — independent of whether it makes business sense. TDQ failures are usually the result of pipeline, schema, or engineering issues: a source system changed its schema, a load job partially failed, a join produced a fan-out. They're the checks a data engineer thinks of first, and for good reason — a table that fails TDQ checks generally shouldn't be trusted with anything downstream at all, business logic included.

TDQ typically covers five overlapping dimensions. Each one maps cleanly onto a specific dbt test pattern.

Completeness — are required fields actually populated?

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [not_null]
      - name: customer_id
        tests: [not_null]
      - name: order_placed_at
        tests: [not_null]

Uniqueness — is the declared grain of the table actually respected?

models:
  - name: fct_order_lines
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
            - line_number

Validity — do values fall within an allowed domain or format?

models:
  - name: stg_orders
    columns:
      - name: currency_code
        tests:
          - accepted_values:
              values: ['USD', 'EUR', 'GBP', 'INR']

Referential integrity — do foreign keys point at rows that actually exist?

models:
  - name: fct_order_lines
    columns:
      - name: order_id
        tests:
          - relationships:
              to: ref('fct_orders')
              field: order_id
      - name: product_id
        tests:
          - relationships:
              to: ref('dim_products')
              field: product_id

Schema stability — did an upstream source change shape without warning?

-- fails (returns a row) if a new, unmapped column shows up in the raw orders source
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'raw_ecommerce'
  AND table_name = 'orders'
  AND column_name NOT IN (
    'order_id', 'customer_id', 'order_status',
    'order_total', 'currency_code', '_loaded_at'
  )

Notice what all five of these have in common: none of them require knowing anything about the business. A machine could verify every one of these rules purely from the schema and the raw values, without ever being told what an "order" or a "customer" means in the context of this particular company. That's the defining trait of TDQ — it's necessary, it's largely automatable straight out of the box with dbt's built-ins and a package like dbt_utils, and it's the same regardless of which company or industry the data belongs to.

Business Data Quality (BDQ)

Business Data Quality is the umbrella for checks that require actual domain knowledge to write — rules that only make sense once someone understands how this specific business operates. A table can pass every TDQ check in the previous section — no nulls, no duplicates, valid foreign keys, stable schema — and still be wrong in a way that only someone who understands the business would catch. BDQ is where that understanding gets encoded into SQL.

Accuracy — does a derived or aggregated value match what it should, by the business's own definition?

This is the order-total-versus-line-items example revisited from the singular tests section — but it's worth naming explicitly as a BDQ concern, because the rule ("an order total must equal the sum of its line items, allowing for a one-cent rounding tolerance") isn't something dbt or any generic tool could infer. Someone who understands this company's checkout and refund logic had to define it.

-- fails if any cancelled order is still counted inside recognized revenue
SELECT
    order_id,
    order_status,
    recognized_revenue
FROM {{ ref('fct_revenue') }}
WHERE order_status = 'cancelled'
  AND recognized_revenue > 0

Business rule compliance — are company-specific policies actually enforced by the data?

-- fails if a refund amount ever exceeds what was actually paid on the order
SELECT
    refunds.refund_id,
    refunds.order_id,
    refunds.refund_amount,
    orders.order_total
FROM {{ ref('fct_refunds') }} AS refunds
JOIN {{ ref('fct_orders') }} AS orders USING (order_id)
WHERE refunds.refund_amount > orders.order_total

Reasonableness — does a value fall within a range the business considers plausible?

Not every problem shows up as a clean rule violation — sometimes a value is technically valid but wildly implausible, and only someone who knows the normal range for this business would recognize it as suspicious. This is a good use case for dbt_expectations, a popular community package that adds range- and distribution-style tests on top of dbt's built-ins:

models:
  - name: fct_orders
    columns:
      - name: order_total
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 50000
              row_condition: "order_status != 'cancelled'"

Fifty thousand isn't a number dbt could ever guess on its own — it's a threshold that comes from someone on the business side saying, in effect, "an individual consumer order over that amount is unusual enough for our catalog that it's worth a human glancing at it before it flows into a revenue report untouched."

Timeliness — does the data reflect the period the business expects it to?

Freshness tests, covered as a technical test type earlier, actually straddle both categories depending on how the threshold is set. A generic "warn after 6 hours" rule is a TDQ concern about pipeline health. But a rule like the one below is a BDQ concern — it encodes a specific business expectation about the reporting calendar:

-- fails if last month has fewer orders loaded than any of the prior three months —
-- a strong signal the month-end feed didn't fully land before the close deadline
WITH monthly_counts AS (
    SELECT
        DATE_TRUNC('month', order_placed_at) AS order_month,
        COUNT(*) AS n_orders
    FROM {{ ref('fct_orders') }}
    GROUP BY 1
)

SELECT *
FROM monthly_counts
WHERE order_month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND n_orders < (
        SELECT MIN(n_orders)
        FROM monthly_counts
        WHERE order_month >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '4 months'
          AND order_month < DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
      )

Consistency — do two supposedly related numbers actually agree?

-- fails if the mart's total revenue for a day drifts more than 0.5% from the
-- finance team's independently loaded general ledger extract for the same day
WITH mart_daily AS (
    SELECT
        order_date,
        SUM(order_total) AS mart_revenue
    FROM {{ ref('fct_orders') }}
    GROUP BY 1
),

gl_daily AS (
    SELECT
        posting_date AS order_date,
        SUM(amount) AS gl_revenue
    FROM {{ source('finance', 'general_ledger') }}
    WHERE account = 'revenue'
    GROUP BY 1
)

SELECT
    mart_daily.order_date,
    mart_daily.mart_revenue,
    gl_daily.gl_revenue
FROM mart_daily
JOIN gl_daily USING (order_date)
WHERE ABS(mart_daily.mart_revenue - gl_daily.gl_revenue) / NULLIF(gl_daily.gl_revenue, 0) > 0.005

This last example is a good stopping point for the section, because it shows BDQ at its most valuable: it's not checking dbt's own output against itself, it's checking dbt's output against an independent, business-owned source of truth. That's the kind of test that actually catches the scenario from the opening of this article — a number that's internally consistent, passes every structural check, and is still wrong.

TDQ vs. BDQ, Side by Side

Both categories matter, and neither substitutes for the other — a table can be technically flawless and still tell the business the wrong story, and a table that encodes perfect business logic on top of a structurally broken join will still produce garbage. Here's the practical breakdown teams tend to find useful when deciding where to invest testing effort:

A useful rule of thumb: if you could write the test correctly without ever talking to a stakeholder outside the data team, it's almost certainly TDQ. If writing it correctly required a conversation with finance, ops, or a domain expert to even know what "correct" means, it's BDQ. Most mature dbt projects end up with TDQ coverage close to 100% of models — it's cheap and mechanical — while BDQ coverage stays intentionally concentrated on the handful of tables that actually drive business decisions, since each BDQ rule takes real domain conversation to get right.

Severity — Warn vs. Error

Not every failed test should block a pipeline. dbt lets you configure a test's severity as either error, the default, which fails the run and stops downstream models from building on top of the bad data, or warn, which logs the failure clearly without blocking anything.

models:
  - name: fct_orders
    columns:
      - name: order_total
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 50000
              config:
                severity: warn
                warn_if: ">10"
                error_if: ">100"

That last pattern — warn_if and error_if with row-count thresholds — is worth calling out specifically, because it solves a real business problem: a handful of unusually large orders during a legitimate flash sale shouldn't halt the entire pipeline, but a hundred of them in one run probably signals something actually broke. Setting the right thresholds is itself a BDQ judgment call, not a technical one — it requires knowing what "a little unusual" versus "clearly wrong" looks like for this specific business.

Running Tests Inside CI/CD

Tests that only run manually, when someone remembers to run them, provide a fraction of the value tests running automatically on every change do. Most dbt projects wire dbt build — models and tests together — into a CI pipeline that runs on every pull request, so a proposed change can't merge if it breaks a test.

name: dbt CI
on:
  pull_request:
    branches: [main]

jobs:
  dbt-build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Install dbt
        run: pip install dbt-snowflake
      - name: dbt deps
        run: dbt deps
      - name: dbt build (models + tests)
        run: dbt build --target ci
        env:
          DBT_PROFILES_DIR: ./ci

The practical effect of this setup is that a broken business rule or a duplicate key becomes a failed check on a pull request, visible to a reviewer before the change ever reaches production — the exact same review discipline software engineering teams have relied on for decades, now applied to the SQL a business actually runs its reporting on.

Packages That Extend Testing

dbt's four built-in generic tests cover a lot of ground, but the open-source package ecosystem extends that coverage considerably, so teams rarely need to hand-write tests for common patterns from scratch.

Installing any of these is a few lines in packages.yml followed by dbt deps — the same low-friction pattern that makes dbt testing approachable in the first place. Most teams end up leaning on dbt_utils for everyday TDQ coverage and dbt_expectations for the more nuanced range and distribution checks that show up in BDQ work.

Getting Started With dbt Testing

If you already have a working dbt project with no tests in it, or only a handful, the path to meaningful coverage is shorter than it looks.

The Bottom Line

dbt tests are, at their core, a very small idea — a SQL query that should return nothing, run automatically, every time. What makes that idea powerful is how completely it can be stretched to cover both ends of data quality: the technical structure a table needs to be trustworthy at all, and the business rules that determine whether a structurally perfect table is actually telling the truth.

Technical Data Quality is the floor — cheap, mostly mechanical, and something every model in a project should have close to full coverage on. Business Data Quality is where the real value sits, because it's the layer that actually protects the decisions a business makes off its own data — and it's also the layer that takes real conversations with the people who understand what "correct" means for this specific company. Neither one replaces the other. A testing strategy that only covers TDQ will still let a wrong-but-clean number reach a board deck; a strategy that only covers BDQ is standing on a foundation that could be silently corrupted underneath it. Together, they're what turns "I think this number is right" into something you can actually stand behind.

— This article is part of an ongoing tooling series on techedge.in. Building out your own TDQ/BDQ split and want a second opinion on a specific test? Drop a comment, I read every one.