# dbt Tests for Marketing Data: 12 Checks With SQL and YAML Examples

Start dbt testing with the records and reports that drive a decision: unique business keys, required fields, valid categories, complete relationships and reconciled amounts. Then test freshness and missing populations. A passing query only proves the assertion you wrote. A useful test suite also names the affected decision, the person who fixes a failure and the condition for reopening downstream work.

Canonical URL: https://www.martechdevs.com/blog/dbt-tests

By [Jafrin Akter Alo](https://www.martechdevs.com/authors/jafrin-akter-alo)

Data Engineer and Co-founder at [Martechdevs](https://www.martechdevs.com) and [Growth and Analytics Partners](https://www.growthandanalytics.com)

[LinkedIn](https://linkedin.com/in/jafrinalo)

Last updated: 2026-10-25

Next step: [dbt Implementation Mistakes: Fix Stale Models, Incremental Errors and Fragile Deployments](https://www.martechdevs.com/blog/dbt-implementation-mistakes)

A duplicated spend row can inflate acquisition cost while every timestamp looks fresh. A missing campaign day can pass all row-level tests because there are no rows to fail. Those need different checks.

This guide builds on [the marketing data quality operating model](https://www.martechdevs.com/blog/marketing-data-quality) and the [ten marketing models in our dbt setup guide](https://www.martechdevs.com/blog/dbt-for-marketing). The examples use normalized fields you control, rather than assuming every connector exports the same schema.

## Which twelve dbt checks should a marketing team start with?

Start with checks that protect a named report or action, then expand when another failure would change a decision. The following is an illustrative starter suite for paid-media reporting joined to customer relationship management (CRM) and billing data. It contains twelve controls, not a requirement to install twelve tests on every model.

| Check | Model or input | Failure it should expose | Suggested response owner |
| --- | --- | --- | --- |
| 1. Spend grain is unique | `stg_ads__daily_spend` | Repeated platform, account, campaign and date | Ads ingestion owner |
| 2. Spend keys are present | `stg_ads__daily_spend` | Missing platform, account, campaign or date | Ads ingestion owner |
| 3. Complete days exist | Expected account-day spine versus spend | An entire load is missing | Pipeline owner |
| 4. Currency is present | `stg_billing__invoices` | Amount with no interpretable currency | Billing data owner |
| 5. Invoice key is unique | `stg_billing__invoices` | Duplicate invoice snapshots treated as new invoices | Billing data owner |
| 6. Lifecycle values are recognized | `stg_crm__contacts` | A new value falls outside the agreed mapping | RevOps owner |
| 7. Deal-to-company links resolve | `stg_crm__deals` | A company reference has no matching account | CRM data owner |
| 8. Paid outcomes have an occurrence key | Deduplicated purchase model | Purchases cannot be reconciled or deduplicated | Measurement owner |
| 9. Spend and impressions are plausible together | Mature account-day spend | Positive spend with unexpectedly zero impressions | Paid-media owner |
| 10. Billing amounts reconcile | Invoice source versus reporting fact | Missing invoices, duplicates or changed amounts | Finance and data owners |
| 11. Inputs meet their delivery deadline | Required sources | A consumer would use stale data | Pipeline owner |
| 12. Incremental and full results agree | A bounded historical interval | A correction never enters the incremental result | Analytics engineering owner |

Choose severity from consequence. An unresolved currency on a revenue model can block publication. A newly introduced lifecycle category can begin as a warning while the affected funnel is held for review. A table of warnings nobody acts on provides little protection.

At Martechdevs, we scope [dbt and warehouse implementation](https://www.martechdevs.com/data-warehouse-cdp) around those consumer decisions and acceptance records. Testing should help an operator decide whether to publish, investigate or pause a particular output.

## How do generic and singular dbt tests differ?

Generic tests reuse a parameterized assertion; singular tests use a SQL query for one business rule. dbt's built-in generic tests are `unique`, `not_null`, `accepted_values` and `relationships`. A data test passes when its query returns no failing records. The `data_tests` YAML key distinguishes these from unit tests; `tests` remains an alias. [dbt data tests](https://docs.getdbt.com/docs/build/data-tests).

Use generic tests for repeatable structural requirements. Use a singular test when investigating the failure requires several fields or when the rule spans sources, periods or accounting assumptions. Avoid a complex macro when a short, inspectable query explains the business rule more clearly.

The YAML below uses `arguments`, available from dbt 1.10.5 and required in dbt v2 for generic-test inputs. Framework options belong under `config`. Check the engine and adapter used by your deployment before changing an older project. [Data-test arguments](https://docs.getdbt.com/docs/build/data-tests).

## How do you test the business key and required values?

Test the complete grain of the model and test required components separately. Campaign ID alone is insufficient for a table containing several platforms, accounts or reporting dates.

After installing a compatible, pinned `dbt_utils` package, adapt this to your normalized field names in `models/staging/marketing_tests.yml`. The illustrative lifecycle values belong to a normalized business vocabulary; map your CRM's actual values before applying them.

```yaml
version: 2
models:
  - name: stg_ads__daily_spend
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns:
              - platform
              - account_id
              - campaign_id
              - spend_date
    columns:
      - name: platform
        data_tests: [not_null]
      - name: account_id
        data_tests: [not_null]
      - name: campaign_id
        data_tests: [not_null]
      - name: spend_date
        data_tests: [not_null]

  - name: stg_billing__invoices
    columns:
      - name: invoice_id
        data_tests: [unique, not_null]
      - name: currency
        data_tests: [not_null]

  - name: stg_crm__contacts
    columns:
      - name: lifecycle_stage
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: [lead, qualified, customer, inactive]
              config:
                severity: warn

  - name: stg_crm__deals
    columns:
      - name: company_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_crm__companies')
                field: company_id
```

The `dbt_utils.unique_combination_of_columns` test checks the combined columns without requiring a manually concatenated string key. The package also provides `accepted_range`, `expression_is_true`, `not_null_proportion`, `relationships_where`, `equal_rowcount` and `recency` for more specific assertions. Pick the test that describes the failure you need to investigate. [dbt-utils test reference](https://github.com/dbt-labs/dbt-utils).

A foreign-key test does not establish that a required key is populated. dbt's `relationships` test excludes null values, while `accepted_values` checks non-null values against the list. Pair either with `not_null` when absence is invalid. [Built-in test properties](https://docs.getdbt.com/reference/resource-properties/data-tests).

For GA4 purchases, test transaction keys in the deduplicated purchase model rather than declaring every raw event unique. First define the eligible purchase population, retry behavior and missing-ID policy. The [dbt implementation mistakes guide](https://www.martechdevs.com/blog/dbt-implementation-mistakes) explains why a correct test attached to the wrong model or omitted selection cannot protect the final report.

## How do you catch a missing day when every existing row passes?

Compare observed data with an independently defined set of expected account-days. A `not_null` test cannot fail on an account-day that never arrived.

Create `expected_ad_account_days` from active account coverage and an agreed completeness cutoff. It should contain one row per `platform`, `account_id` and `spend_date` that must be available. Exclude planned shutdowns deliberately. Some APIs omit zero-activity rows, so choose whether a completed load manifest or an explicit zero-filled model supplies evidence for a quiet day.

Save this singular test as `tests/assert_expected_ad_days.sql`:

```sql
select
    e.platform,
    e.account_id,
    e.spend_date
from {{ ref('expected_ad_account_days') }} as e
left join (
    select distinct platform, account_id, spend_date
    from {{ ref('stg_ads__daily_spend') }}
) as a
    on e.platform = a.platform
    and e.account_id = a.account_id
    and e.spend_date = a.spend_date
where a.account_id is null
```

A missing day appears as a failing row even when the spend table contains no records for it. The expected spine must be governed separately: deriving it from the same incomplete spend table would make the test incapable of detecting the missing population.

Use `dbt test --select assert_expected_ad_days` against a built development model. `dbt test` evaluates existing resources; it does not build them first. [dbt test command](https://docs.getdbt.com/reference/commands/test).

## Which singular tests protect marketing and revenue meaning?

Write a query that returns the specific records that violate an agreed business rule. Include the identifiers and values needed for repair, then make the rule's exceptions explicit.

For positive spend with zero impressions, use completed periods and the platform's reporting lag. Credits, adjustments or a delayed impressions field may justify an exception. An unconditional `spend > 0 and impressions = 0` rule is an investigation signal, not proof of fraud or wasted media.

For billing, reconcile the same invoice population and currency before comparing totals. A CRM deal and a Stripe invoice are different objects; equality is valid only after the [billing-to-CRM reconciliation model](https://www.martechdevs.com/blog/reconcile-stripe-crm-revenue) defines their relationship.

The following test compares original integer minor-unit amounts without converting currencies. `expected_invoice_amounts` is an independently reconciled invoice snapshot; `fct_invoice_amounts` is the reporting result. Each must first pass uniqueness on `(invoice_id, currency)` and non-null tests on those fields and `amount_minor`. Save it as `tests/assert_invoice_amounts_match.sql`:

```sql
with invoice_rows as (
    select invoice_id, currency
    from {{ ref('expected_invoice_amounts') }}
    union all
    select invoice_id, currency
    from {{ ref('fct_invoice_amounts') }}
), invoice_keys as (
    select distinct invoice_id, currency
    from invoice_rows
)
select
    k.invoice_id,
    k.currency,
    e.amount_minor as expected_amount_minor,
    a.amount_minor as actual_amount_minor
from invoice_keys as k
left join {{ ref('expected_invoice_amounts') }} as e
    on k.invoice_id = e.invoice_id and k.currency = e.currency
left join {{ ref('fct_invoice_amounts') }} as a
    on k.invoice_id = a.invoice_id and k.currency = a.currency
where e.invoice_id is null
   or a.invoice_id is null
   or e.amount_minor <> a.amount_minor
```

The union catches unexpected invoices as well as missing ones. A currency change produces a missing and an unexpected key, which is useful evidence rather than a netted amount. If your report intentionally rounds or converts amounts, create a separate tolerance policy for that derived measure. Do not hide differences in source invoice amounts with a blanket percentage threshold.

For leads, test the population that should have acquisition evidence after a release. Keep legitimate unknown or direct sources explicit. Rejecting every unattributed contact can remove real business outcomes from the denominator.

## How should severity, failure storage and publication gates work?

Use errors to block an unsafe result and warnings for a bounded review process with a named owner. `severity: error` evaluates `error_if` first and then `warn_if`; `severity: warn` skips the error condition. `--warn-error` can promote warnings to errors. Count-based thresholds refer to the test's failure calculation, which is usually returned rows rather than a business percentage. [Severity behavior](https://docs.getdbt.com/reference/resource-configs/severity).

This is an illustrative tolerance for a noncritical lookup test, not a recommended allowance for missing revenue:

```yaml
config:
  severity: error
  error_if: ">10"
  warn_if: ">0"
  store_failures: true
```

`store_failures` saves failing records in a test table, normally under `<profile_schema>_dbt_test__audit`. Each run replaces the previous failure set, including a clean run. Archive the incident evidence separately if you need history. Restrict access because failed rows can contain customer information. [Stored-failure behavior](https://docs.getdbt.com/reference/resource-configs/store_failures).

During `dbt build`, failed upstream error tests can skip selected downstream resources. Multi-parent tests have dependency-specific behavior, so verify the actual selected graph. A warning alone does not provide that block. [Build failure behavior](https://docs.getdbt.com/reference/commands/build).

A separate reverse ETL job can still read yesterday's table or run after a failed dbt job. Require the publication workflow to check the expected model version, successful run and data cutoff before starting that sync. A dbt test is one component of that gate, not a universal switch for every downstream tool.

## How do freshness and anomaly tests complement data tests?

Freshness asks whether the data arrived in time; row assertions ask whether the available data satisfies a rule. Neither replaces a completeness check against the expected population.

For dbt v2, `dbt freshness` checks configured sources and models. The legacy `dbt source freshness` checks sources only. Set one or both of `warn_after` and `error_after`, with a count and period, and explicitly schedule the appropriate command. [Freshness configuration](https://docs.getdbt.com/reference/resource-configs/freshness). Choose [thresholds from the consumer's deadline](https://www.martechdevs.com/blog/marketing-data-freshness-sla), allowing enough time to repair the pipeline.

A recent maximum load timestamp can coexist with one missing account. Pair it with the account-day check above rather than making the timestamp represent complete delivery.

Elementary's `volume_anomalies` monitors row counts over time. Its defaults use 14 training days and two detection days, with completed buckets only; a partial current day is not a completed daily bucket. Set the window to the actual reporting cadence. [Elementary volume anomalies](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/volume-anomalies).

A product launch or planned traffic stop can be a genuine business change. Investigate the difference before retraining a baseline or treating it as broken ingestion. Anomaly detection estimates unusual behavior; source-to-report reconciliation proves a different claim.

## Which packages and unit tests are worth adding?

Add a package when its assertion replaces repeated code or detects a failure the initial suite misses. Pin a compatible release and inspect the compiled query before using it on a large table.

Metaplane maintains a fork of `dbt-expectations` supporting dbt 1.7 and later, with tests for table shape, column values and aggregate comparisons. Check its supported adapters and timezone configuration for the package version you install. [Maintained dbt-expectations package](https://github.com/metaplane/dbt-expectations).

Elementary's `schema_changes` detects added, deleted or retyped columns and deleted tables. It requires Unity Catalog on Databricks and does not support Athena or Trino. [Schema-test support](https://docs.elementary-data.com/data-tests/schema-tests/schema-changes). The [schema drift guide](https://www.martechdevs.com/blog/schema-drift) separates those structural changes from new categories and changed business meaning. Compare [observability tools](https://www.martechdevs.com/blog/data-observability-tools) when you also need richer alerting and investigation.

Unit tests exercise SQL logic against static input and expected output; data tests inspect actual model results. dbt supports unit tests from v1.8 and recommends them in development or continuous integration. [Unit-test guidance](https://docs.getdbt.com/docs/build/unit-tests). Use fixtures for attribution-window boundaries, refunds, missing identities and ties, based on [metric-contract acceptance cases](https://www.martechdevs.com/blog/marketing-metric-contracts).

In dbt v2, project `checks` are another mechanism: they query the dbt Information Schema to enforce project standards before models run. They do not replace tests on business data. [Build checks](https://docs.getdbt.com/reference/commands/build).

## How do you route failures and prove the suite works?

Route the failure to the person who can change the failing system, and include enough context to make the first action clear. Send the model, test, observed cutoff, failed-row location, affected consumer and run link.

dbt platform job notifications support selected success, warning, failure and cancellation events. Slack integration supports public channels; use an appropriate destination for the information included. [Job notifications](https://docs.getdbt.com/docs/deploy/job-notifications). Model notifications can route model and test results through assigned groups when the account setting and release-track prerequisites are met. [Model notifications](https://docs.getdbt.com/docs/deploy/model-notifications).

A serious failure should enter the [data incident runbook](https://www.martechdevs.com/blog/data-incident-runbook), with its affected syncs and reports identified. A routine warning needs an owner and resolution deadline. Do not send both to an unowned channel and assume delivery proves response.

Run a controlled failure exercise in development:

1. Establish a clean fixture and confirm the expected tests pass.
2. Duplicate a campaign-date row and confirm the grain test fails.
3. Remove an expected account-day and confirm the spine test returns it.
4. Null an invoice currency and confirm the required-field test fails.
5. Change one invoice amount by one minor unit and confirm reconciliation detects it.
6. Insert an unexpected invoice and confirm the union-based test detects it.
7. Repair the fixture and confirm failures clear, while the incident evidence remains saved.

Also [compare incremental and full results](https://www.martechdevs.com/blog/dbt-incremental-backfills) over the same bounded history. A passing current-day test cannot establish that a late correction reached last month's report.

![A consumer decision defines the contract, freshness and coverage checks; blocking failures hold publication for repair, while successful checks release the verified data version and cutoff.](https://www.martechdevs.com/assets/blog/dbt-tests.svg)

Diagram connections:

- Name consumer decision and data contract to Check source freshness and expected coverage. 
- Check source freshness and expected coverage to Run key category relationship and reconciliation tests. 
- Run key category relationship and reconciliation tests to Blocking checks pass. 
- Blocking checks pass to Hold affected publication and assign repair: No. 
- Hold affected publication and assign repair to Check source freshness and expected coverage. 
- Blocking checks pass to Publish the verified version and cutoff: Yes. 
- Publish the verified version and cutoff to Review warnings and rehearse failures

## What should be in the dbt test acceptance checklist?

Accept the suite only when it detects representative failures and changes the release decision correctly.

## Checklist

- [ ] Every critical test names a consumer, failure consequence and repair owner.
- [ ] Business keys match the full grain and required fields have separate null checks.
- [ ] Missing populations are compared with an independent expectation.
- [ ] Reconciliation uses matching objects, periods, currencies and amount definitions.
- [ ] Freshness commands and thresholds match the actual engine and schedule.
- [ ] Failure storage has access controls and incident snapshots survive reruns.
- [ ] Downstream publication checks the intended successful run and cutoff.
- [ ] Known-bad fixtures fail, clean fixtures pass and alerts reach an accountable owner.

Bring the failed query and affected report to the [$99 martech audit](https://www.martechdevs.com/pricing). For a defined repair, [request a detailed dbt implementation scope](https://www.martechdevs.com/dbt-implementation#offer) so the handover includes a working release gate.

## Frequently asked questions

### How should tests work when historical source data is unavailable?

State the available-history boundary and test what can actually be established within it. Keep preserved extracts or accepted report totals as separate evidence when they exist. Do not make a current source table impersonate an earlier snapshot. For unrecoverable periods, record the limitation beside the affected report and prioritize preventing the same evidence gap in future loads.

### Should a failing test automatically delete the offending rows?

Keep detection and correction separate unless an approved rule makes the correction unambiguous and reversible. Preserve the original values and count the excluded population. Automatically deleting a duplicate-looking customer can damage legitimate relationships, while dropping an unmatched payment can hide revenue. Require the repair to pass reconciliation before the cleaned model is published to reports or operational destinations.

### How can a renamed dbt test keep its incident history?

Maintain a stable business-control identifier outside the generated test name and map old and new names to it. Record why the assertion changed, which model versions it applies to and when its results became comparable again. A renamed test should not appear to erase recurring failures or create a new control with no history in the operating review.

### What should reviewers check in an AI-generated dbt test?

Ask which bad fixture the test detects and which legitimate exception it preserves. Read the compiled SQL, especially joins, null handling, filters and failure thresholds. Confirm the expectation comes from a business contract rather than the current data alone. Have the generator explain its prerequisites, then keep the fixture and review evidence with the model so future changes remain inspectable.

### How should testing costs be allocated across shared reports?

Attribute the test to the shared model or control that incurs the query cost, then allocate that cost once under the agreed reporting policy. Measure scanned data, runtime and frequency before reducing coverage. In dbt v2, [batching uniqueness and null tests](https://docs.getdbt.com/docs/build/data-tests) can reduce queries while retaining individual results. A cheaper schedule still needs to detect failure before the consumer acts.

## Sources

1. [dbt Labs: Data tests and arguments](https://docs.getdbt.com/docs/build/data-tests)

2. [dbt Labs: Built-in data-test properties](https://docs.getdbt.com/reference/resource-properties/data-tests)

3. [dbt Labs: Test command](https://docs.getdbt.com/reference/commands/test)

4. [dbt Labs: Severity and thresholds](https://docs.getdbt.com/reference/resource-configs/severity)

5. [dbt Labs: Stored failures](https://docs.getdbt.com/reference/resource-configs/store_failures)

6. [dbt Labs: Build behavior and checks](https://docs.getdbt.com/reference/commands/build)

7. [dbt Labs: Freshness configuration](https://docs.getdbt.com/reference/resource-configs/freshness)

8. [dbt Labs: Unit tests](https://docs.getdbt.com/docs/build/unit-tests)

9. [dbt Labs: Job notifications](https://docs.getdbt.com/docs/deploy/job-notifications)

10. [dbt Labs: Model notifications](https://docs.getdbt.com/docs/deploy/model-notifications)

11. [dbt Labs: dbt-utils](https://github.com/dbt-labs/dbt-utils)

12. [Metaplane: dbt-expectations](https://github.com/metaplane/dbt-expectations)

13. [Elementary: Volume anomalies](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/volume-anomalies)

14. [Elementary: Schema changes](https://docs.elementary-data.com/data-tests/schema-tests/schema-changes)