Styxis
Company
Knowledge

Checking inconsistent DuckDB NULL counts with a conservation rule

Before trusting a NULL ratio, verify that total, non-null and null counts partition the same input.

The quality metric needs a check

NULL metrics are themselves data that need validation. Total rows must equal the non-null and null partitions. Truthound belongs at the audit table containing these counts, rather than inside the database implementation. A single reported missing-value ratio cannot reveal disagreement between its counting paths. [1, 2, 3]

One column, different NULL counts

The public DuckDB report describes incorrect NULL FILTER aggregation over a C++ table function returning a single column. Ordinary count, a WHERE path and a materialized table are described as successful controls. Preserve the function's output shape and the aggregation path; the report does not establish that every NULL aggregate is affected. [1]

Keep the numerator and denominator together

Store total, non-null and null counts from the same extraction. If their sum disagrees, hold the quality-dashboard update and compare a materialized path. Keep the execution identity and input snapshot alongside them. Counts taken at different times can make legitimate concurrent ingestion look inconsistent. [1, 3]

A worked passing and failing fixture

Define an illustrative input with 99 total rows, 99 non-null rows and 0 null rows. It satisfies 99+0=99. If the same input reports 37 null rows, its partition sum becomes 136 and disagrees with the total of 99. Adopting 136 as a replacement denominator hides the contradiction. The expected total comes from the input definition, not from copying the observed metric. [1, 3]

The Truthound boundary

Truthound Core's ExpressionValidator can check that the non-null and null counts sum to the total in a prepared table. The independently extracted values must first be linked to the same batch. Passing this rule does not prove that each row's NULL position is correct, and it does not repair a C++ validity mask or the DuckDB executor. For a stronger check, compare row identities and NULL states in the materialized result. [1, 2, 3]

Application conditions

Obtain total_count, nonnull_count and null_count from the same extraction through the source or independent aggregation, and link them by batch identity.

Alternatives

Materialize the complete result first and recalculate the same NULL partition with WHERE.

Limitations

  • Truthound does not repair a C++ validity mask. Passing the conservation rule does not establish the correct NULL position of every row.

Evidence

Event checked
Subject checked
Modified