Why dbt Tests Alone Cannot Catch Freshness, Volume, or Schema Drift

dbt tests are the best thing to happen to data reliability in the last decade. They are also, quietly, the reason most data teams still get paged by stakeholders instead of by their own systems. The tests catch what they were designed to catch. They cannot catch the failure modes that never enter a dbt run.

This is why teams that ship 400 dbt tests still find out about broken data from their CEO.

What are dbt tests actually built to catch?

dbt tests are boolean SQL assertions executed against a model's output at run time. Four generic tests cover roughly 80% of usage.

• unique. No duplicate values in a column.
• not_null. No null values.
• accepted_values. All values in a fixed set.
• relationships. Foreign key hits an actual primary key.

Custom generic tests extend the pattern (row count comparisons, threshold checks) but keep the same primitive: at run time, execute a SQL query, expect zero rows returned, fail if not.

That primitive is genuinely useful. It catches roughly 40% of data incidents on a typical warehouse: broken joins, deduplication failures, referential drift in mart tables, business logic violations you thought to encode.

The remaining 60% lives outside the primitive.

Why does the run cadence limit what tests can catch?

dbt tests execute during dbt runs. Between runs, the warehouse changes and nothing checks it.

• Your source table stops loading at 8 PM.
• Your nightly dbt run kicks off at 6 AM.
• The source freshness test fires at 6:07 AM, 10 hours after the incident.
• Business users start checking dashboards at 8 AM.

The best case is your tests catch it before the business does. The typical case is the business catches it first, because 10 hours of staleness is enough time for the first CFO to look at yesterday's data.

Continuous monitoring on information_schema and warehouse metadata queries at 5- to 15-minute intervals collapses the detection gap from hours to minutes. Tests cannot do that because they are gated on the transformation cycle.

What kinds of failures happen outside a dbt run?

Five categories, none of which the dbt run knows about at the moment they occur.

• Ingestion pauses. Fivetran connector broke; no data loads for 6 hours. No dbt input changes; no test fails at the next run.
• Schema changes at the source. A producer renamed a column upstream. The next dbt run will fail loudly, but you learn the schema drifted only at run time, not when the change happened.
• Silent zero-row loads. Ingestion job succeeded, wrote zero rows. not_null still passes; unique still passes; row_count > 0 might not exist as a test.
• Distribution shifts. Currency field changed units. All tests pass because they check nulls and referential integrity, not the shape of the values.
• Backfills and reprocessing. A backfill silently duplicates rows. unique might catch it later if you tested the right column; often teams do not.

Each of these is invisible to a test-only reliability posture until either the run happens or, more often, the business notices.

How do learned baselines change the equation?

The core limit of dbt tests is that you have to know the threshold. row_count > 1000 is a rule. Whose row count? On which day? The rule that works on Monday breaks on Sunday. The rule that works this quarter fails at scale next quarter.

Learned baselines flip the model. Instead of you picking the threshold, the system observes the table over 30 to 60 days and computes what "normal" is for each hour of each day. Anomalies get defined relative to the learned pattern, so weekday and weekend, month-end and mid-month, are all handled without new code.

You cannot express "the p95 load interval for this table is 65 minutes, and today's load has been idle for 4 hours, which is 3.7x the p95" as a dbt test without writing custom SQL that queries account_usage or its equivalent. And at that point, you are building a monitoring system inside dbt, poorly.

Where do dbt tests still win?

Two places, unambiguously.

• Business logic on mart tables. Only you know that revenue_arr = mrr * 12 within rounding, or that order_status must be in ('paid', 'refunded', 'pending', 'chargeback'). No tool can infer that. dbt tests are the right primitive.
• Referential integrity between models. If model B joins to model A on customer_id, a relationships test is exactly the right check. It runs at build time, before B ships. Observability cannot enforce this because it operates on states, not transformations.

The right posture is: tests own business logic on models you own; observability owns everything else. Neither layer is a substitute for the other.

What is a rough coverage rubric?

Use this to check your current stack.

If your current reliability stack has only the left column filled, you are catching business logic bugs. Everything in the right column is what your CEO catches for you.

What is the migration path for a test-heavy team?

Do not delete tests. Add observability alongside, in a specific order.

1. Freshness monitoring first. Highest ROI, shortest rollout (see the one-week Snowflake playbook). Covers the failure mode most likely to hit a dashboard.
2. Schema drift monitoring next. Cheap to run (daily diff of information_schema.columns). Catches producer-side breakage before your next dbt run fails.
3. Volume monitoring with learned baselines. Replaces the 40 hand-tuned volume tests you wrote and mute half of. Higher precision, less maintenance.
4. Distribution monitoring last. Highest complexity, but the only way to catch silent corruption.

Keep dbt tests focused on the mart layer where business logic lives. Prune the volume and freshness tests you wrote as workarounds; they will be handled by the new layer with better precision.

The mistake to avoid

The dbt community sometimes frames tests as sufficient for data reliability. They are not. Tests validate transformations against rules you wrote. Nothing about that primitive scales to freshness, schema, or distribution drift, and no amount of custom generic tests closes the gap. Add a monitoring layer that runs continuously against warehouse metadata, keep your tests on business logic, and stop being surprised when stakeholders find breakage before your CI does. Reliability comes from covering both layers, not from adding a 401st test.