My data drift detector hit 55/56 on a fault-injection benchmark, but failed the one

I built this tool to catch "silent" data failures—things like a vendor changing units or an undocumented enum appearing—where the pipeline stays green but the meaning of the data shifts. To prove it actually worked, I seeded 56 defects across different pipeline layers and magnitudes. The tool's job was to profile the data, detect the drift, and trace the lineage back to the broken node. It nailed 55 of them.

Why negative controls matter more than success rates

A benchmark consisting only of faults only tells you if the detector fires; it doesn't tell you if it fires too often. A tool that alerts on every single run would score 100% on a fault-only benchmark but is useless in production because of alert fatigue.

I added four negative controls where the expected result was total silence:

• control-null: A rebuild and reprofile with zero changes.
• control-subthreshold-tip: A 3% increase (below my 5% threshold).
• control-subthreshold-extra: A 2% increase.
• control-subthreshold-tip-near-limit: A 4.5% increase, pushing right up against the limit.

The control-null failed immediately. I got three high-severity signals on a run where the data hadn't changed a single bit.

The culprits were HyperLogLog and floating-point non-determinism

The false positives came from two compounding sources of noise.

First, I was using approx_count_distinct for speed. Since HyperLogLog (HLL) is a probabilistic sketch, the estimates aren't stable across runs. I found variance up to 30% between identical runs on the same dataset. Since my detection threshold was 10%, the estimator's inherent noise was three times louder than the signal I was looking for.

Second, I hit a classic DuckDB parallelism issue. Because DuckDB parallelizes aggregates, sum() and avg() accumulate in non-deterministic orders across threads. Since floating-point addition isn't associative, (a + b) + c doesn't always equal a + (b + c) at the 15th decimal place. This tiny variance was enough to change which values were counted as distinct, triggering the drift alert.

How I stabilized the measurements

The fix was to stop approximating and start rounding. I had to force exact counts and truncate floats before they hit the counting or recording logic.

def _distinct_expr(col: str, data_type: str) -> str:
    if _is_float(data_type):
        return f"count(distinct round({col}, 6))"
    return f"count(distinct {col})"

I also had to apply this to min and max values that I was storing as strings. Without rounding, a value of 22575.66999999999 in run A and 22575.669999999995 in run B looked like a data change.

def _bound_expr(fn: str, col: str, data_type: str) -> str:
    if _is_float(data_type):
        return f"round({fn}({col}), 6)::varchar"
    return f"{fn}({col})::varchar"

After implementing these, two identical runs finally produced zero signals.

Determinism is a requirement, not a feature

The technical takeaway is simple: use exact counts. The architectural takeaway is that determinism is a hard precondition for detection. If your measurement process has its own variance, you haven't built a detector; you've built a random number generator with a threshold.

The most striking part is the asymmetry of the testing. Fifty-six tests that expected a failure missed the problem entirely. One single test that expected nothing to happen exposed the entire flaw in the measurement logic.