How to Detect Silent Data Corruption Without Writing Custom SQL for Every Table

Every data team has been through this. The pipeline was green all week. The dbt tests all passed. Then a stakeholder pinged: "the revenue number looks off." Three days of digging later, you find that a currency field started returning values in cents instead of dollars two weeks ago, and every downstream calculation has been divided by 100 too many times.

That is silent data corruption. It is the hardest class of failure to catch because there is nothing failing. This is how to catch it anyway, without writing custom SQL for every table.

What signals catch corruption when nothing has technically broken?

Corruption produces statistical fingerprints. Three signals catch nearly all of it.

• Null rate. A column that has been 2% null for months suddenly at 20% null. The pipeline succeeded; the join or the source degraded.
• Cardinality. A status column with 5 known values suddenly has 6. Or a country_code column dropped from 42 to 38 distinct values because a country code silently got remapped.
• Percentile drift. A revenue column's p95 that has hovered near $1,200 for 90 days is now at $12,000 because somebody accidentally shipped an order of magnitude bug in a producer app.

None of these require you to know what a "correct" value looks like. They only require you to know what the column normally does.

Which columns actually deserve monitoring?

Watching every column in a 3,000-table warehouse means alert fatigue and no upside. Filter with a simple rule.

Include:

1. Any column referenced in a dbt model tagged P0 or P1.
2. Any column that is a primary key, foreign key, or join key.
3. Any low-cardinality categorical column (distinct count under 100) used in filters or grouping.
4. Any numeric column used in aggregations that feed a dashboard.

Exclude:

• Raw event payload columns nobody reads.
• Timestamp columns already covered by freshness monitoring.
• High-cardinality string columns (names, emails, free-text notes) where statistics are meaningless.

That filter typically leaves 20 to 30% of columns as monitored. Enough to catch the corruption; small enough to keep alerts scanable.

What statistics should you compute per column?

A short but non-redundant set. Compute daily on a sample of the table (10K rows or the full table if smaller) and store the results in a time-series table.

Ship the statistics into monitoring.column_baseline with columns for date, table, column, statistic, and value. Now you have a time series to detect against.

How do you build a baseline that does not false-positive?

Use robust statistics, not naive ones.

• Compute the trailing 30-day median and IQR (interquartile range) per column per statistic.
• Alert when today's value falls outside median +/- 2.5 * IQR for any tracked statistic.
• Suppress alerts when the same table has an open schema change incident (a new column can shift null rates legitimately).

The naive version uses mean and standard deviation. That version breaks on the first legitimate spike (a Black Friday sale, a marketing campaign, a batch backfill), because those shift the mean and inflate the standard deviation. Robust statistics tolerate legitimate anomalies without letting them poison the baseline.

Precision at 3-sigma with robust statistics runs 85 to 90% at scale. Naive Gaussian thresholding runs 30 to 50%, which is unshippable.

How do you handle seasonality?

Some columns have real weekday effects. Order volumes rise Monday through Thursday and drop over weekends. Ignoring seasonality means every Saturday triggers alerts on volume-derived percentiles.

Two ways to handle it, in order of complexity.

• Bucketed baseline. Compute baselines separately for weekdays vs. weekends, or by day-of-week if the table has 90 days of history. Cheap, works for 80% of cases.
• Decomposed time series. For P0 tables with strong seasonal patterns, use a lightweight decomposition (STL or Prophet-style additive model) to remove trend and seasonality before checking anomaly. More expensive, but recovers the remaining 20%.

Do not skip seasonality. It is the number-one source of false positives in distribution monitoring.

What corruption patterns show up in the wild?

Six patterns account for the vast majority of silent corruption incidents.

1. Type coercion drift. A column that was INTEGER starts arriving as VARCHAR because a producer changed serialization. Cardinality on the column can look normal; the downstream cast fails silently.
2. Timezone shift. Timestamps arrive shifted by hours because a producer service redeployed with a different default timezone. Percentiles on event_time - server_time catch it.
3. Enum expansion. A status field gains a new value that no downstream mapping handles. Cardinality alert on the column catches it same-day.
4. Silent dedup failure. A primary key that was previously unique now has duplicates because an upsert became an insert. Uniqueness ratio on the key catches it.
5. Unit change. A currency column moves from dollars to cents, or a distance column from kilometers to meters. Percentile drift catches it, usually with a 100x jump.
6. Join degradation. A left join that used to hit 98% of rows now hits 60% because the join key drifted. Null rate on the joined column catches it.

You will find all six inside the first quarter of running distribution monitoring on your P0 tables. Half of them will be incidents that had been running for weeks.

How do you connect corruption alerts to lineage?

An alert on a raw column matters more when you can point at what breaks. Column-level lineage from query logs plus dbt manifests tells you, for a given corrupted column, which downstream models reference it and which BI dashboards consume those models.

Alert format that works:

• Severity and table
• Which column, which statistic, expected range, observed value
• Downstream: {n} dbt models, {n} dashboards affected
• Suggested runbook link

Without downstream impact, corruption alerts get de-prioritized because they look abstract. "Null rate on raw.stripe.invoices.currency moved from 1% to 18%" reads as low-stakes. "18% null rate on a column referenced in 12 dbt models and 8 finance dashboards, one of which is on the board deck this Friday" reads as a fire.

What is the operational cost of doing this?

Compute cost is modest if you sample. On a 3,000-table warehouse with 30% column coverage (roughly 40 columns per governed table), daily statistics on 10K-row samples run in under 30 minutes on an XS Snowflake warehouse. That is $5 to $15 per month in credits.

Storage for the baseline table is negligible: one row per column per statistic per day equals under 1M rows per month for most teams.

The real cost is the ownership overhead of tuning: three engineer hours per week for the first month, one hour per week after. Amortized against catching even one silent revenue incident, the ROI is positive inside the first quarter.

The mistake to avoid

Data teams treat corruption as "the class of bugs we cannot catch," then keep finding out from stakeholders. Corruption is catchable. It just does not respond to the same tools freshness does. Instrument three statistics per monitored column, use robust baselines, connect the alerts to downstream lineage, and correlate with schema changes to kill false positives. You will find corruption that has been running for weeks in your warehouse right now. The wrong number your CEO caught last quarter had a statistical fingerprint you were not watching for.