My Data Drift Detector Successfully Identified 55/56 Faults but Failed Negative Controls

I developed a tool designed to detect "silent" data failures, such as unit changes by vendors or undocumented enums, where pipelines remain green despite data meaning shifts. To validate its effectiveness, I introduced 56 defects across various pipeline layers and magnitudes. The tool's purpose was to profile the data, detect drift, and trace the lineage back to the faulty node. It successfully identified 55 of these defects.

Why Negative Controls Are More Important Than Success Rates

A benchmark that only includes faults indicates whether the detector triggers, but not whether it triggers too frequently. A tool that alerts on every run would achieve a 100% score on a fault-only benchmark but would be unusable in production due to alert fatigue. Therefore, I added four negative controls where the expected result was no alerts:

• control-null: a rebuild and reprofile with zero changes.
• control-subthreshold-tip: a 3% increase (below the 5% threshold).
• control-subthreshold-extra: a 2% increase.
• control-subthreshold-tip-near-limit: a 4.5% increase, close to the limit.

The control-null test failed immediately. I received three high-severity signals on a run where the data had not changed at all.

The Culprits Were HyperLogLog and Floating-Point Non-Determinism

Two factors contributed to the false positives.

First, I used approx_count_distinct for efficiency. Since HyperLogLog (HLL) is a probabilistic sketch, the estimates vary significantly between runs on the same dataset. I observed variance as high as 30% between identical runs. With a detection threshold of 10%, the estimator's inherent noise was three times louder than the signal I aimed to detect.

Second, I encountered a classic DuckDB parallelism issue. DuckDB parallelizes aggregates, so operations like sum() and avg() accumulate in non-deterministic orders across threads. Due to floating-point addition not being associative, (a + b) + c does not always equal a + (b + c) at the 15th decimal place. This minor variance was sufficient to alter which values were counted as distinct, triggering the drift alert.

How I Stabilized the Measurements

The solution was to stop approximating and start rounding. I implemented exact counts and truncated floats before they reached 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 applied this to min and max values stored as strings. Without rounding, a value of 22575.66999999999 in run A and 22575.669999999995 in run B appeared as 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 these changes, two identical runs finally produced no signals.