Styxis
Company
Knowledge

Do not hide a DuckDB CSV TRY_CAST failure inside a missing-value ratio

Unparseable values, quarantined records and a failed execution are different outcomes.

An empty result is not a substitute for failure

Allowed parsing failures must not be counted as if they were the same as execution failure. Substituting an empty table when no table was produced makes a failed read indistinguishable from a genuinely empty batch. A Truthound completeness check becomes meaningful only after successful execution is established. [1, 2, 3]

When TRY_CAST becomes a strict scan

The public DuckDB report describes a CSV string TRY_CAST failing the entire scan instead of yielding NULL for an unconvertible value. A control with type_pushdown disabled behaves differently. The case calls for checking whether the SQL-level intention to tolerate conversion errors survives its translation into the scanner. [1]

Separate landing from conversion

Preserve raw strings and account separately for collection success, conversion success and quarantined records. Apply the missing-value budget only to a successfully produced batch; never approve an empty replacement for a failed execution. Retain original record identities and reasons in the quarantine inventory so the retry scope remains known. [1, 3]

Use a ratio only after a successful read

If an illustrative policy requires a minimum completeness ratio of 0.98, a ratio of 0.99 passes and 0.90 fails. The denominator is the successfully landed inspection scope. Files that could not be read must not silently disappear from it. A failed execution retains its failure state instead of receiving this numerical verdict. [1, 3]

The Truthound check and operational alternative

Truthound Core's CompletenessRatioValidator can apply the approved minimum to the parsed_value column that was actually produced. Landing raw strings first and converting them separately is an alternative that exposes the failure boundary. The check does not change the optimizer or recover missing CSV data. After a retry, reconcile the successful file inventory with the original collection plan as well. [1, 2, 3]

Application conditions

Link the actual landing table containing raw_value, parsed_value and conversion_status with successful and failed file inventories for the same collection run.

Alternatives

Land raw CSV strings first, then perform conversion and quarantine in a separate stage.

Limitations

  • Truthound neither changes the DuckDB optimizer nor recovers results from a failed CSV scan.

Evidence

Event checked
Subject checked
Modified