Your dbt CI pipeline passes. All 47 tests are green. You promote the model to production. At 10 AM, the support team gets a ticket: “dashboard shows 0 revenue for yesterday.” The data loaded correctly; the queries compiled; the tests ran. But none of those tests checked what actually broke.

dbt tests check exactly what you tell them to check. They validate that a column has no NULLs (not_null), that values are unique (unique), that a foreign key exists in a reference table (relationships). They do not check that a column that was 90% populated yesterday is 100% NULL today. They do not check that a join key that matched 2 million rows yesterday matches 200 today. They do not check that a distribution shifted by six standard deviations.

These are the unknown unknowns — conditions that produce no test failure but corrupt your data silently.

The Four Silent Failure Modes

1. NULL Explosions

A source API starts returning NULL for a previously populated field. The column is not in your not_null test because it was never meant to be required. The NULLs propagate through every downstream model. Aggregations that skip NULLs return different numbers. JOINs on that column produce fewer matches.

Detection: Add row-level distribution profiles to your test suite.

-- dbt test: distribution check for non-null rate
WITH stats AS (
  SELECT
    COUNT(*) AS total,
    COUNT(device_id) AS non_null,
    ROUND(COUNT(device_id) * 100.0 / COUNT(*), 2) AS pct_non_null
  FROM {{ ref('raw_events') }}
)
SELECT * FROM stats WHERE pct_non_null < 90

Run this as a custom dbt data_test. It alerts when a column’s non-NULL rate drops below threshold. Threshold should be one standard deviation below the trailing 30-day mean.

2. Referential Orphans

A source system archives old records. Your relationships test still passes because the test only checks that every user_id in the fact table exists in the dimension table — it does not check that every user_id in the dimension table appears in the fact table. After the archive, your fact table has 40% fewer rows. The dashboard shows a “drop in activity” that is actually a data loss.

Detection: Add a referential integrity test that checks both directions.

-- Check orphans in the dimension table
SELECT COUNT(*) AS orphan_count
FROM {{ ref('dim_users') }} u
LEFT JOIN {{ ref('fct_events') }} f ON u.user_id = f.user_id
WHERE f.user_id IS NULL
-- Alert if orphan_count > expected_archive_threshold

Set thresholds based on your data retention policy. Not all orphans are bad — archived users are expected. But a sudden spike means something deleted records that should still exist.

3. Distribution Shifts

A column revenue historically ranges from $5 to $500 with a mean of $45. A code change in the source pipeline starts emitting values in cents instead of dollars. The mean drops to $0.45. Every not_null and unique test passes. The accepted_values test for currency still passes. But every revenue-based metric is wrong by a factor of 100.

Detection: Profile expected ranges and alert on statistical outliers.

-- dbt test: range sanity check
WITH stats AS (
  SELECT
    AVG(revenue) AS mean_revenue,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY revenue) AS p05,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY revenue) AS p95
  FROM {{ ref('fct_orders') }}
)
SELECT * FROM stats
WHERE mean_revenue < 1.0  -- cents instead of dollars
   OR mean_revenue > 1000 -- likely a unit error

This catches unit mismatches, decimal shifts, and currency conversions before they reach dashboards.

4. Row Count Drift

A scheduled job that feeds your source table fails silently. The data is 12 hours old when your dbt model runs, but the model compiles and tests pass because the data that exists is valid. The dashboard shows yesterday’s numbers as today’s.

Detection: Compare row counts against trailing averages.

-- dbt test: row count drift
WITH history AS (
  SELECT COUNT(*) AS row_count
  FROM {{ ref('source_table') }}
  WHERE ingestion_date = CURRENT_DATE
)
SELECT * FROM history
WHERE row_count < (SELECT AVG(row_count) * 0.5 FROM history)

Set the threshold to 50% of the trailing 7-day average. This catches pipeline failures, archive jobs, and connection drops that produce partial loads.

Building the Unknown-Unknown Test Suite

Standard dbt tests cover known failure modes. Custom data tests cover unknown ones. Here is the minimum set:

# tests/schema.yml
version: 2

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: user_id
        tests:
          - relationships:
              to: ref('dim_users')
              field: user_id
    tests:
      - distribution_check:
          column_name: revenue
          min: 1.0
          max: 10000.0
      - row_count_drift:
          threshold_pct: 50
      - null_rate_check:
          column_name: device_id
          min_pct: 90.0

Define the custom tests as SQL files in tests/:

-- tests/distribution_check.sql
{% test distribution_check(model, column_name, min, max) %}
SELECT {{ column_name }}
FROM {{ model }}
WHERE {{ column_name }} < {{ min }}
   OR {{ column_name }} > {{ max }}
LIMIT 10
{% endtest %}

-- tests/null_rate_check.sql
{% test null_rate_check(model, column_name, min_pct) %}
WITH stats AS (
  SELECT COUNT({{ column_name }}) * 100.0 / COUNT(*) AS pct
  FROM {{ model }}
)
SELECT * FROM stats WHERE pct < {{ min_pct }}
{% endtest %}

What This Costs

Test Type	Run Time (per billion rows)	Maintenance
not_null + unique	2-5 seconds	None — schema-driven
Distribution check	10-30 seconds	Set initial bounds per column
Row count drift	2-5 seconds	None — self-calibrating
Referential integrity	30-120 seconds	Update on archive events
NULL rate check	5-15 seconds	Set per-column threshold once

The total CI time increase is roughly 60 seconds per model. For most pipelines, this is acceptable. For tables with 10+ billion rows, run checks on a 1% sample and accept the statistical margin.

Trade-offs

Custom tests require maintenance every time a source schema changes. If a column’s valid range expands legitimately, the distribution test breaks. Budget 15 minutes per schema update to adjust thresholds.

Statistical checks produce false alarms on holidays, product launches, and data migrations. Suppress alerts for known events with a calendar-based exclusion list.

Row count drift tests assume stable ingestion volume. High-growth products will trigger false positives every week. Use trailing 7-day average with a growth factor multiplier instead of a fixed threshold.

Data Type	Recommended Checks	CI Impact
Revenue, billing	Distribution + referential + row count	~90s
User activity	Row count + NULL rate	~30s
Event logs	Row count only	~10s
Dimension tables	Referential + uniqueness	~20s

dbt tests are necessary but not sufficient. The tests that pass tell you nothing about the things you forgot to check. Profile distributions, track row counts, and audit NULL rates — or discover the gap when a dashboard goes dark.