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.

Jafrin Akter AloLast updated 25 October 202612 min read

On this page

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 and the ten marketing models in our dbt setup guide. 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.

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 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.

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.

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.

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.

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.

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 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:

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.

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 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:

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.

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

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.

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.

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. Choose thresholds from the consumer's deadline, 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.

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.

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. The schema drift guide separates those structural changes from new categories and changed business meaning. Compare 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. Use fixtures for attribution-window boundaries, refunds, missing identities and ties, based on metric-contract acceptance cases.

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.

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. Model notifications can route model and test results through assigned groups when the account setting and release-track prerequisites are met. Model notifications.

A serious failure should enter the 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 over the same bounded history. A passing current-day test cannot establish that a late correction reached last month's report.

Read diagram as text

• 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

Open full diagram

What should be in the dbt test acceptance checklist?

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

Bring the failed query and affected report to the $99 martech audit. For a defined repair, request a detailed dbt implementation scope so the handover includes a working release gate.

Written by

Jafrin Akter Alo

Data Engineer and Co-founder at Martechdevs and Growth and Analytics Partners

Jafrin writes practical guides to marketing technology, customer data, analytics and AI workflows. Martechdevs is a Growth and Analytics Partners company.

View LinkedIn profile