I was building a tool that detects when data quietly changes meaning — a vendor switching units, a source dropping a field, an undocumented enum appearing. The kind of failure where every test passes and every job is green.
Claims about detection are cheap, so I built a benchmark. 56 seeded defects across fault type, magnitude, time window and pipeline layer. Each one has a known root cause. The tool profiles the pipeline, detects drift, walks the lineage graph, and names the node where the problem started. Score it against the node I actually broke.
It scored 55/56. I was pleased with myself for about a day.
Then I added the controls
A benchmark made only of faults can only tell you one thing: does the detector fire? It cannot tell you whether it fires too much. A detector that screams on every run scores 100% on that benchmark and is completely useless in production, because nobody reads an alert channel that cries wolf.
So I added four negative controls. Scenarios where the correct answer is silence:
-
control-null— rebuild and reprofile with no change at all -
control-subthreshold-tip— tips up 3%, under the 5% threshold -
control-subthreshold-extra— extras up 2% -
control-subthreshold-tip-near-limit— tips up 4.5%, just under the line
A control passes only if it raises no high or critical signal.
control-null failed. Three high-severity signals on a run where nothing had changed.
What was actually happening
Two separate causes, compounding.
HyperLogLog. I was computing distinct counts with approx_count_distinct. It is fast, and for dashboards the approximation is fine. But HLL is a probabilistic sketch, and its estimate is not stable across runs — I measured variation up to 30% between two identical runs on the same data. My distinct-count threshold was 10%. The estimator's own noise was three times louder than the signal I was trying to detect.
Floating point. The second cause is the one I would not have guessed. DuckDB parallelises aggregates, so sum() and avg() accumulate in a non-deterministic order across threads. Floating-point addition is not associative: (a + b) + c and a + (b + c) can differ in the last bits. Two mathematically identical groups could therefore produce values that differed at the fifteenth decimal place — and that was enough to change which values counted as distinct.
The fix
Count exactly, and round floats before counting:
python
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})"
The same reasoning later applied to min and max, which I was recording as text:
python
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"
Without that second one, a float min of 22575.66999999999 in one run and 22575.669999999995 in the next reads as a changed value. It is not a changed value. It is the same number, added up in a different order.
Two identical runs now produce zero signals.
What I actually took away
The obvious lesson is "use exact counts". That is not the interesting one.
The interesting one is that determinism is a precondition for detection, not a nice-to-have. A drift detector compares two measurements and calls the difference a signal. If your measurement process has its own variance, you have built a random number generator with a threshold on it. Every false positive it produces is indistinguishable from a real finding — and you will only find out after someone has stopped trusting the alerts.
And the second: fifty-six tests that expected something to happen never caught this. One test that expected nothing to happen caught it immediately.
That asymmetry generalises well beyond data tools. Most test suites are built entirely out of "given this input, assert this output". Very few contain "given no change, assert no output". The second kind is cheap to write and catches a category of bug the first kind is structurally blind to — anything where your system is noisy rather than wrong.
If you are building anything that detects, classifies, or alerts, write the test that expects silence. It will not pass on the first try.
The tool is Upstrace — column-level drift detection and lineage-based root-cause analysis for dbt projects. pip install upstrace; MIT-licensed. The full benchmark, including the one scenario that still fails, is in the repo.
