Data Quality Testing with dbt — Schema Tests to CI/CD

Pipelines succeeding is not the same as data being correct. A pipeline can run flawlessly while producing garbage. Data quality testing closes the gap.

What can go wrong

1. Source schema changed — column dropped, type changed, semantics shifted.
2. Source data corrupted — vendor sent bad file, upstream system buggy.
3. Transformation logic broken — your dbt model has a bug.
4. Joins exploded — many-to-many join when you expected many-to-one.
5. Duplicates — at-least-once delivery delivered twice.
6. Late-arriving data — events for yesterday arrived after the daily cutoff.
7. Volume drop — 90% of expected rows missing; pipeline didn't notice.

Without checks, these silently corrupt downstream. Reports show "wrong" numbers; nobody knows why.

The four levels of data quality

Level 1 — Schema validation

Does the data have the columns you expect? Right types?

# dbt test
columns:
  - name: customer_id
    tests: [not_null, unique]
  - name: order_total
    tests:
      - not_null
      - dbt_utils.expression_is_true:
          expression: ">= 0"

Level 2 — Row-level validation

Are individual rows valid?

- name: status
  tests:
    - accepted_values:
        values: ['pending', 'paid', 'shipped', 'cancelled']

Level 3 — Cross-row / aggregate validation

Do totals match? Is the sum across rows what you expect?

-- Custom test: today's revenue is within 50% of yesterday's
WITH today AS (SELECT SUM(amount) AS s FROM marts.orders WHERE date = current_date),
     yest  AS (SELECT SUM(amount) AS s FROM marts.orders WHERE date = current_date - 1)
SELECT 1
WHERE (SELECT s FROM today) < (SELECT s FROM yest) * 0.5
   OR (SELECT s FROM today) > (SELECT s FROM yest) * 1.5

Level 4 — Business-rule validation

Does the data conform to business invariants?

-- A customer should not appear with two different account_creation_date values
SELECT customer_id, COUNT(DISTINCT account_creation_date)
FROM marts.customers
GROUP BY customer_id
HAVING COUNT(DISTINCT account_creation_date) > 1

Two main tools

dbt tests

Built into dbt. Tests run after models.

# models/marts/dim_customer.yml
version: 2
models:
  - name: dim_customer
    columns:
      - name: customer_id
        tests:
          - not_null
          - unique
      - name: lifetime_value
        tests:
          - not_null

dbt test --select dim_customer

Pros: free, integrated with dbt, simple. Cons: only post-transformation; not great for source data.

Great Expectations

Standalone framework. Define "expectations" about data; run them in pipelines.

import great_expectations as gx

df = gx.read_csv("data.csv")
df.expect_column_values_to_not_be_null("customer_id")
df.expect_column_values_to_be_unique("order_id")
df.expect_column_value_lengths_to_be_between("phone", min=10, max=15)

validation_result = df.validate()
if not validation_result["success"]:
    raise ValueError("Data quality check failed")

Pros: richer expectations (statistical, custom), works on any data (not just SQL), can profile data automatically. Cons: more complex, separate framework to learn.

When to use which

• dbt tests for warehouse-side validation (after your dbt models).
• Great Expectations for source-side / pre-load validation, or when dbt is not in your stack.

Most production teams use dbt tests as the default and add Great Expectations for source-side checks or non-dbt pipelines.

Where to put tests in the pipeline

Source → [Ingestion] → Raw → [dbt staging] → Staging → [dbt marts] → Marts → Downstream

Tests:
  After ingestion (raw): expected schema; row count reasonable
  After staging: no duplicates; required fields present
  After marts: business invariants hold; cross-table joins clean

Three places to test. Three different scopes.

What to actually test (the 80/20)

For every important table, test:

1. Primary key: not_null + unique.
2. Required columns: not_null on critical fields.
3. Allowed values: status, type, category fields.
4. Referential integrity: FK columns exist in parent table.
5. Row count: row count > 0 and within expected range.

For aggregates / facts:

6. Sum invariants: today's total within 50% of yesterday's.
7. Distinct counts: number of distinct customers matches expected.
8. Date freshness: latest row's date is recent (within last 24h).

For raw / source data:

9. Schema match: columns and types match contract.
10. No new columns: alert if source added unexpected columns.

Anomaly detection vs static tests

Static tests check fixed conditions ("count > 0"). Anomaly detection catches deviations ("today's count is 80% lower than usual").

Tools for anomaly detection:

• Monte Carlo / Soda — commercial observability platforms.
• OpenLineage + custom logic — DIY.
• Great Expectations profiler — automated baselines from past data.

Many teams use both: static tests for known invariants, anomaly detection for "something is weird and I don't have a specific rule for it."

Test failure handling

When a test fails, options:

1. Fail the pipeline — block downstream from running on bad data.
2. Warn but continue — log the failure; let downstream run.
3. Quarantine — move bad rows to a side table; continue with the clean ones.

dbt severity options:

tests:
  - not_null:
      severity: error  # fail
  - unique:
      severity: warn   # log but continue

Critical tests = error. Soft checks = warn.

Alerting

Test failures should alert the right people. Patterns:

• Slack channel for data alerts (#data-quality-alerts).
• PagerDuty for critical failures (production marts).
• Email digest for warns (daily summary).

dbt has built-in callback support; CI tools have integrations.

Cost of data quality checks

For a daily pipeline with 100M rows:

• dbt tests on 50 columns: 5-15 minutes added to pipeline.
• Great Expectations on 30 expectations: 10-30 minutes.
• Anomaly detection (Monte Carlo): real-time, no pipeline overhead.

Worth it. Cost of catching a bad load before it propagates is 10-100x lower than fixing wrong reports later.

Common data-quality mistakes

1. No tests at all. Bad data ships silently.
2. Tests only on marts. Source-side bugs missed for days.
3. Tests too lax. "not_null" but no value-range check; junk passes.
4. Tests too strict. False alarms erode trust; tests get disabled.
5. No alerting on failures. Tests exist but no one notices.
6. Test failures don't block downstream. Bad data propagates anyway.

Takeaway

Test at every layer: source, staging, marts. Use dbt tests for warehouse, Great Expectations for source / pre-load. Cover PK uniqueness, not_null, allowed values, referential integrity, row counts. Add anomaly detection for "weird but I don't have a rule" cases. Failures alert; critical failures block downstream. Costs are minor; benefits are major.

---

📘 Companion Deep Dive: Data Contracts & Upstream Schema Drift

Data downtime is rarely caused by syntax bugs in SQL; it is caused by upstream application developers altering database columns (e.g. dropping user_id or changing an integer timestamp to an ISO string) without consulting data teams.

1. Defining Declarative Model Contracts in dbt

Modern dbt projects (v1.5+) enforce contracts at model build time. If the compiled SQL does not match the strict YAML specification, the build fails before materializing data in production:

version: 2

models:
  - name: stg_orders
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: integer
        constraints:
          - type: not_null
      - name: customer_id
        data_type: integer
        constraints:
          - type: not_null
      - name: order_status
        data_type: varchar
        constraints:
          - type: check
            expression: "order_status in ('pending', 'authorized', 'settled', 'refunded')"

2. Breaking Change Protocol

1. Never drop columns silently: Upstream software releases must mark columns @deprecated for one full release cycle before removal.
2. CI/CD Contract Checks: Run dbt compile against production state using Slim CI (dbt test --state state:modified+) in GitHub Actions pull requests to block breaking schema merges.