Handling FLOAT / DOUBLE columns in Paridity¶
One-line answer: if a column carries money, rates, quantities, or anything an auditor treats as a decimal, model it as
NUMERIC/DECIMAL, notFLOAT. Paridity reconcilesNUMERICbyte-identically on every engine with native pushdown.FLOAT/DOUBLEis an approximate binary type; whether it can be compared in-warehouse (no egress) depends on the engine, and Paridity never trades accuracy for pushdown.
This note exists so an SE can answer, on the spot, three customer questions:
- "Why did Paridity pull my
FLOATcolumn out of the warehouse to compare it?" - "Can I keep the comparison in-warehouse for my
FLOATcolumns?" - "What should I do with the amount column my source stored as
FLOAT64?"
Every FLOAT compare mode below is exact — byte-for-byte identical between the
source engine, the target engine, and the DuckDB reference. A hash cannot express a
tolerance band, so none of these modes is an "approximately equal" compare; the only
question a FLOAT column raises for them is where that exact comparison runs (native
SQL pushdown vs the DuckDB reference), which is a cost/egress question, not an accuracy
question. The one intentional exception is numeric_tolerance (§4) — an explicit,
operator-configured ε-bucketing that is never a silent default and is applied
identically on both sides.
Why cross-engine FLOAT is hard (the 30-second version)¶
A FLOAT/DOUBLE stores an IEEE-754 binary approximation of the number you wrote. The
decimal 0.1 is not representable in binary; every engine stores the same nearest binary
value, but they render it back to text differently:
- Paridity's canonical reference renders the shortest round-trip decimal (
str(0.1)→"0.1") — the same rule Python, PostgreSQL ≥12, and Spark use. - Several engines can only render a fixed-digit expansion of the exact binary value
(
0.100000000000000006…) or a truncated 15-digit form.
round_N("0.1") and round_N(0.100000000000000006…) round two different real numbers, so
they can disagree in the last kept digit — a fail_hash on data that is equal. This is a
semantic mismatch between the engines, not a per-engine bug, and it is why Paridity
routes float comparison to the DuckDB reference on engines that lack a shortest-round-trip
float→string primitive. See docs/cross_engine_hash_audit.md Issue #18 (#679/#680/#695)
for the full live investigation.
Pushdown vs fallback — is one better?¶
Yes, pushdown is preferred — but the two are equally accurate. The difference is cost, never correctness:
- Native pushdown computes the hash inside the warehouse in SQL; only the tiny hash digest crosses the wire. No egress, and it meets Paridity's throughput floors. Preferred.
- DuckDB reference (fallback) streams the raw values out to Paridity's canonical reference and hashes them there. Guaranteed byte-identical (the same algorithm for every engine), but the customer pays egress to pull the values out — which for a proof-phase hash of a very large table can be material. That egress is the entire cost of a fallback; the answer is exactly as correct either way.
So a fallback is a correctness-preserving safety net, not an accuracy compromise.
Two things worth saying to a customer who sees their engine in the fallback column:
- This is FLOAT-specific, not a second-class engine. Paridity pushes down
everything else — integers,
NUMERIC/DECIMAL, strings, timestamps, booleans — on all 11 engines. Only theFLOAT/DOUBLEtype triggers the fallback below. - It is an engine-capability wall, not unfinished Paridity work. Pushing down FLOAT
requires the engine's SQL to render the shortest round-trip decimal of a binary
float. Five engines have that primitive; six do not, and no amount of Paridity
engineering adds it to their SQL (it was live-proven, not assumed — #680/#695). The two
ways to get in-warehouse FLOAT comparison on a fallback engine are to model the column
as
NUMERIC/DECIMAL(pushes down everywhere) or, for FLOAT columns that actually hold fixed-scale decimals, the opt-indecimal_in_float(N)mode below (SQL Server/Snowflake/BigQuery). General/measurement floats on those six engines stay fallback permanently.
Current behavior: exact FLOAT comparison, per engine¶
This is what ships today. Every cell is exact / byte-identical; the column tells you where the comparison runs.
| Engine | Exact FLOAT comparison | Why |
|---|---|---|
| PostgreSQL | Native pushdown | col::text is Ryu shortest-repr (PG ≥12) → cast to numeric(38,s) |
| MySQL / Aurora MySQL | Native pushdown | CAST(... AS DECIMAL(65,s)) matches the reference |
| Trino | Native pushdown | CAST(CAST(col AS DECIMAL(38,s)) AS VARCHAR) |
| SAP HANA | Native pushdown | TO_VARCHAR(DOUBLE) is HANA's true shortest-repr primitive |
| Databricks | Native pushdown | Spark Double.toString is Ryu shortest-repr → CAST … AS DECIMAL (#695) |
| Oracle | DuckDB reference (fallback) | TO_CHAR/TM9 give 17-digit exact expansion — no shortest-repr path |
| SQL Server | DuckDB reference (fallback) | Fixed-digit CONVERT styles only; FORMAT is a CLR hot-path hazard |
| Snowflake | DuckDB reference (fallback) | TO_VARCHAR ~15 digits (1e+15 collision); TO_JSON fixed-16 |
| BigQuery | DuckDB reference (fallback) | No shortest-repr decimal path; %.15/16/17g ladder fails at volume |
| DB2 LUW | DuckDB reference (fallback) | Every DOUBLE→string is ~15 sig digits (would false-pass) |
| Redshift | DuckDB reference (fallback) | PG 8.0.2 pre-Ryu; col::varchar is %.15g (1e+15 false-pass) |
"DuckDB reference (fallback)" is correct, not degraded. The rows stream from the engine into Paridity's canonical reference (the same algorithm for every source), so the comparison is guaranteed exact. The only cost is egress: the raw values leave the warehouse instead of being hashed in place. For a proof-phase hash of a large table that egress can be material, which is what the guidance below is about.
What to recommend (SE decision guide)¶
Recommend in this order:
1. Model decimal data as NUMERIC / DECIMAL — the real fix¶
If the column is money, a rate, a percentage, a tax amount, or any figure that appears in
a financial statement, it is a decimal, and storing it as FLOAT is a modeling
accident (very common when data lands via JSON, CSV, or FLOAT64 defaults in BigQuery).
NUMERIC/DECIMAL reconciles with native pushdown on every engine, byte-identical,
no egress, no caveats. This is always the best answer when the customer controls the type.
2. Keep exact (the default) — always correct, pays egress on 6 engines¶
If the column stays FLOAT, Paridity's default float_compare_mode = exact is exactly
today's behavior: native pushdown on the 5 shortest-repr engines, DuckDB reference on the
other 6. Nothing to configure. Choose this whenever egress is acceptable or the mapping
already runs on a pushdown engine.
3. decimal_in_float(N) — opt-in, wins back pushdown when the FLOAT holds decimals¶
Status: rolling out under #700 (this note is Chunk A; the mode itself lands in Chunks B–D). Do not promise it as GA until those ship.
Many customers store scale-N decimals (e.g. 2-dp amounts) in a FLOAT64 column. For that
population, decimal_in_float(N) restores native pushdown on SQL Server, Snowflake, and
BigQuery without giving up byte-identity. It is honest about what it assumes: "this
FLOAT column holds scale-N decimals." It is not an approximate compare.
How it stays exact: before pushing down, Paridity runs a per-run grid-residency guard
— one extra aggregate over the same scan — proving every value is already exactly a
scale-≤N decimal (two conditions: on-grid ABS(v − ROUND(v,N)) ≤ 1e-9 and within the
double's faithful magnitude range ABS(v) < 10^(15−N)). If any row is off-grid, the
whole float extract auto-falls back to the DuckDB reference and the operator is told how
many rows were off-grid. So the mode either pushes down with proven byte-identity or
safely falls back — it can never false-pass.
decimal_in_float(N) availability:
| Engine | decimal_in_float(N) |
Reason |
|---|---|---|
| SQL Server | ✅ (Chunk 0 live-proven at N=2) | CAST(ROUND(v,N) AS DECIMAL(38,N)) exact on-grid |
| Snowflake | ✅ (Chunk 0 live-proven at N=2) | CAST(ROUND(v,N) AS NUMBER(38,N)) exact on-grid |
| BigQuery | ✅ (Chunk 0 live-proven at N=2) | FORMAT('%.Nf', v) on-grid / BIGNUMERIC |
| Oracle | ❌ never | 17-sig double-rounding, irreducible residual |
| DB2 LUW | ❌ never | truncates + corrupts digit 16 (documented false-pass) |
| Redshift | ❌ never | imprecise expansion + %.15g collision |
| PG / MySQL / Trino / SAP HANA / Databricks | Not needed | already exact-pushdown for FLOAT |
On the ❌ engines the mode does nothing unsafe — it simply keeps using the exact DuckDB
reference (same as exact).
4. numeric_tolerance — for genuine measurement floats¶
If the column really is a measurement (sensor reading, computed physics value) where the
customer wants "equal within ε", set numeric_tolerance on the field (#690). It changes
the canonical value itself — ε-bucketing round(v / ε) · ε — and is honored
consistently in both the proof-phase hash and the diff, because Paridity forces the
DuckDB reference on both sides so source and target bucket identically. It is the one
setting that makes Paridity compare approximately, and only when the operator explicitly
turns it on (it is recorded in the resolved config snapshot / config_checksum). Note a
tolerance is an ε-grid, not a moving window: two values within ε that straddle a
bucket boundary still compare unequal. Never reach for decimal_in_float to approximate a
measurement float — that is not what it does, and decimal_in_float will correctly refuse
(off-grid) exactly the values a measurement column is full of.
Quick reference¶
| Customer situation | Recommend |
|---|---|
Amount/rate/tax stored as FLOAT, customer can change the type |
Model as NUMERIC/DECIMAL |
FLOAT amount, can't change type, on PG/MySQL/Trino/HANA/Databricks |
exact (already native pushdown) |
FLOAT amount at fixed scale, on SQL Server/Snowflake/BigQuery, egress-sensitive |
decimal_in_float(N) (once GA) |
FLOAT amount on Oracle/DB2/Redshift |
exact (DuckDB reference; model as NUMERIC to avoid egress) |
| Genuine measurement float, "equal within ε" | numeric_tolerance (#690) |
| Not sure it's really decimal | Start exact; check the run's off-grid count before enabling decimal_in_float |
For auditors¶
Every FLOAT compare mode above (exact, decimal_in_float) produces byte-for-byte
identical canonical values across the source engine, the target engine, and the DuckDB
reference — verified live per engine against the canonical reference (HashService), not
merely engine-vs-engine. Whether a given run used native pushdown or the DuckDB reference
is recorded in run evidence (run_attempts.fallback_used); for decimal_in_float, the
off-grid row count that triggers a fallback will be surfaced in the run (with the mode,
700). The only approximate comparison Paridity performs is numeric_tolerance: it is¶
never a default, must be explicitly configured per field, is recorded in the resolved
config snapshot (and thus the run's config_checksum), and when set it ε-buckets the
canonical value in the proof-phase hash and the diff alike — both sides forced to the
DuckDB reference so they bucket identically (#690).