Data Quality & Testing

Three layers of data quality

1. Schema / structural — does the data have the right shape? (column existence, types, nullability)
2. Logical / business rules — does the data satisfy expected invariants? (primary keys unique, foreign keys resolve, values in expected set, business arithmetic holds)
3. Statistical / distributional — does the data look right? (row counts in expected range, column distributions stable, no anomalous spikes/drops)

Different tools handle each. dbt tests cover layers 1 and 2 well. Layer 3 needs observability tooling — Monte Carlo, Anomalo, Elementary, or custom anomaly checks.

Tests — dbt and beyond

The dbt built-ins

• unique — column has no duplicates.
• not_null — column has no nulls.
• accepted_values — column values are within a list.
• relationships — every value in column A exists in column B of another table (foreign key check).

dbt-utils additions

• unique_combination_of_columns — composite unique key.
• not_null_proportion — at least X% of rows have non-null.
• recency — most recent row is within X hours.
• at_least_one — table has at least one row.
• expression_is_true — arbitrary boolean expression.

dbt-expectations (Great Expectations port)

Distribution tests, regex tests, statistical bounds. Useful for layer 3 checks within dbt.

Singular tests

Custom SQL in tests/ that returns failing rows. Empty result = pass. Use for business-logic checks no built-in covers:

SELECT
  o.order_id,
  o.revenue AS order_revenue,
  SUM(oi.line_revenue) AS line_revenue_sum
FROM {{ ref('fct_orders') }} o
JOIN {{ ref('fct_order_items') }} oi USING (order_id)
GROUP BY o.order_id, o.revenue
HAVING ABS(o.revenue - SUM(oi.line_revenue)) > 0.01

Freshness

Data that's stale lies. Worse, it lies silently — the dashboard looks the same, the numbers just stop moving.

dbt source freshness

sources:
  - name: raw_app
    loaded_at_field: _ingested_at
    freshness:
      warn_after:  {count: 2, period: hour}
      error_after: {count: 6, period: hour}
    tables:
      - name: orders

Run dbt source freshness in CI / scheduled job. Page or alert on errors.

Model freshness

Test that downstream models have recent data:

models:
  - name: fct_orders
    tests:
      - dbt_utils.recency:
          datepart: hour
          field: ordered_at
          interval: 2

Anomaly detection

Tests catch boolean invariants. They don't catch "revenue dropped 60% today and no one noticed." That's anomaly territory.

Built-in patterns

• Row count anomalies — today's row count is N standard deviations from the trailing average.
• Schema anomalies — column added / removed / type changed unexpectedly.
• Distribution anomalies — column's distribution shifted (mean, p50, p95).
• NULL anomalies — null proportion changed.

Tools

• Elementary — open-source, dbt-native. Stores test results + anomaly detection in your warehouse.
• Monte Carlo, Anomalo, Bigeye — commercial, ML-driven, less config.
• Roll your own — a few dbt-utils tests + scheduled queries can do a lot.

Data contracts

An explicit, versioned agreement between data producer and consumer. The producer commits to: these columns, these types, this freshness, these uniqueness guarantees. The consumer commits to: only relying on what's documented.

Implementation:

• dbt model contracts — declare expected columns + types in YAML; CI fails if the model output doesn't match.
• Schema registry for streaming events (Confluent, Glue Schema Registry).
• Backstage / data catalogs for org-wide visibility.

The cultural piece matters as much as the technical piece. Contracts shift "data team gets blindsided when engineer drops a column" to "producer can't ship without breaking the build."

Severity strategy

If every test is "error severity, page on-call," tests become noise and get ignored. Smart severity:

• Severity error — true contracts. Page if violated. Examples: PK uniqueness, freshness on source data, business rules where wrong data = wrong decisions.
• Severity warn — signal, not gate. Investigate when convenient. Examples: distribution shifts, low-importance accepted-values lists, soft thresholds.
• Threshold-based errors — allow some failures. Example: error_if: >100 means warn at 1-100 failing rows, error at 101+.

Incident response

When a quality alert fires, the discipline is:

1. Triage — is it real, or a flaky test? Look at the failing rows.
2. Communicate — if downstream dashboards are affected, post in the data channel. Don't wait until stakeholders find it.
3. Fix the data — the immediate priority. Backfill, re-run, mark records, whatever.
4. Root-cause — what change introduced this? Was it a schema change, a logic bug, a data drift?
5. Prevent — add a test that would have caught this earlier. Document the gotcha in the model's yaml.
6. Postmortem for the painful ones. Mature data teams run them like SREs run incident reviews.

Talking points

✓ Check yourself

Answer from memory before opening each one.

1. Every dbt test passes, yet revenue fell 60% overnight and nobody noticed. Which layer of quality missed it, and why can tests not catch it?

   Show answer

   The statistical layer. Tests assert fixed invariants (unique, not null, in a set), and a 60% drop breaks none of them. It needs anomaly checks on row counts, distributions or null rates against a baseline. See the three layers and anomaly detection.

2. A source loader stalls and the raw table stops changing, but the dashboards look normal. What catches this, and what must run for it to fire?

   Show answer

   A source freshness check, configured with a loaded-at field and warn and error thresholds. It only fires if dbt source freshness runs on a schedule or in the pipeline before transformation. See freshness.

3. A test is configured with error_if: ">100" and the default warn threshold. What happens when 40 rows fail, and when 150 fail?

   Show answer

   Forty rows produce a warning and the run continues. One hundred fifty rows exceed the error threshold and fail the run. Thresholds let a known amount of noise through without paging anyone. See severity strategy.

4. A singular test file returns 12 rows when it runs. Does the build pass, and what are those rows?

   Show answer

   It fails. A singular test is a query that selects the violating rows, so any returned row is a failure and an empty result passes. The 12 rows are the offending records. See tests.

5. A team adds a test after every incident and now 40 alerts a day page the on-call engineer. What do you change?

   Show answer

   Tier them: error and page only for true contracts, warn for signals such as distribution drift. Give each test an owner and a runbook, and review weekly which alerts were noise. See severity.

6. The producing team plans to drop a column that your models read. How does a data contract change the outcome compared with relying on convention?

   Show answer

   With a contract the change is versioned and their build breaks before it ships, so the conversation happens first. With convention you find out when your dashboards break. See data contracts.

Takeaway

• Quality has three layers: structure, business rules and distribution. dbt tests cover the first two, and observability tooling covers the third.
• A dbt test passes when its query returns no rows, and singular tests express business rules that built-in tests cannot.
• Stale data fails silently, so check freshness at the source, the model and the dashboard against an agreed service level.
• Tests catch broken invariants; anomaly checks on row counts, schema, distributions and null rates catch "revenue fell 60%".
• Contracts move a schema break from the consumer's surprise to the producer's failing build.
• Severity tiers keep alerts meaningful: error for true contracts, warn for signals, and an owner and runbook on every test.
• Treat a quality alert as an incident: triage, communicate, fix the data, find the root cause, and add the test that would have caught it.

Tests run inside a warehouse, so the next chapter compares the platforms and how each one bills. → Warehouses & Lakehouses

Further reading

• Add data tests to your DAG — dbt Labs docs, accessed 2026. Generic and singular tests, severity, warn_if and error_if.
• Source freshness — dbt Labs docs, accessed 2026. Declaring thresholds and running the freshness command.
• Model contracts — dbt Labs docs, accessed 2026. Enforcing columns and types so a mismatch fails the build.
• dbt-utils — dbt Labs, accessed 2026. The recency, unique_combination_of_columns and expression_is_true tests.
• dbt-expectations — calogica, accessed 2026. A port of Great Expectations checks to dbt tests.
• Elementary documentation — Elementary, accessed 2026. Anomaly detection and test result storage for dbt projects.