Threshold Tuning for Tolerance › Setting Floating-Point Tolerance for Numeric Parity

Setting Floating-Point Tolerance for Numeric Parity

This page answers one deceptively hard question: when two engines compute the same numeric column and produce values that differ in the fifteenth decimal place, are they equal? It sits under the threshold tuning for tolerance reference and turns its policy into concrete comparison code. Get the tolerance too tight and IEEE-754 rounding floods the manifest with meaningless discrepancies; too loose and a real error — a truncated decimal, a currency conversion bug — hides under the epsilon. The right answer is not one epsilon but a per-column policy that knows the difference between a floating-point aggregate and a money value that should never have been a float at all.

Problem Framing

A revenue rollup computed in Spark and re-computed in Trino disagree by fractions of a cent. Two failure shapes hide here. The first is benign: floating-point addition is not associative, so summing the same values in a different partition order yields a result that differs in the last few bits — real, expected, and meaningless. The second is a genuine bug: a DECIMAL(18,4) that was cast through a double somewhere lost precision, and the error is small but systematic. A single absolute epsilon cannot tell these apart, because the benign error scales with magnitude while the bug may not. The policy must choose absolute tolerance for values near zero, relative tolerance for large magnitudes, and exact decimal comparison for money — never a float epsilon for currency.

Implementation

The comparator applies a combined absolute-and-relative tolerance for floating-point columns and switches to exact decimal comparison for columns declared monetary, so each column is judged by the rule its type actually warrants.

python
import logging
import math
from dataclasses import dataclass
from decimal import Decimal
from typing import Dict

logging.basicConfig(
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s", level=logging.INFO
)
logger = logging.getLogger("recon.diffing.tolerance")


@dataclass(frozen=True)
class ColumnTolerance:
    kind: str                 # "float" or "decimal"
    abs_eps: float = 1e-9     # absolute floor, dominates near zero
    rel_eps: float = 1e-6     # relative bound, dominates at large magnitude
    scale: int = 4            # decimal places for money


def floats_parity(a: float, b: float, tol: ColumnTolerance) -> bool:
    """Combined absolute + relative comparison: max(abs, rel*|larger|)."""
    if math.isnan(a) or math.isnan(b):
        return math.isnan(a) and math.isnan(b)     # NaN parity is explicit, never silent
    diff = abs(a - b)
    bound = max(tol.abs_eps, tol.rel_eps * max(abs(a), abs(b)))
    return diff <= bound


def decimal_parity(a: str, b: str, tol: ColumnTolerance) -> bool:
    """Exact comparison at a fixed scale — the only correct rule for money."""
    qa = Decimal(a).quantize(Decimal(1).scaleb(-tol.scale))
    qb = Decimal(b).quantize(Decimal(1).scaleb(-tol.scale))
    return qa == qb


def values_parity(a, b, tol: ColumnTolerance) -> bool:
    if tol.kind == "decimal":
        return decimal_parity(str(a), str(b), tol)
    return floats_parity(float(a), float(b), tol)

Tolerance Strategy Trade-Off

The rule is chosen per column from what the number means, not from a global default.

Rule Best for False positives False negatives Compliance / regulatory
Absolute epsilon Values near a known scale Floods at large magnitude Hides small errors on tiny values Weak alone; magnitude-blind.
Relative epsilon Large-magnitude aggregates Fails near zero (divide-by-small) Hides absolute errors on small values Moderate; needs an absolute floor.
Combined abs + rel General floating-point columns Rare when both are tuned Rare Strong: documented dual bound is defensible.
Exact decimal Money and regulated quantities None from rounding None Strongest: currency must never ride a float epsilon.

Key Implementation Notes

  • Money is not a float. The single most consequential rule: never compare currency with a floating-point epsilon. Compare it as a fixed-scale Decimal, exactly, because a tolerance on money is a tolerance on how much error an audit will accept, and the correct answer is none.
  • Combine absolute and relative bounds. A pure relative epsilon explodes near zero and a pure absolute epsilon is blind to magnitude. max(abs_eps, rel_eps*|larger|) uses the absolute floor for small values and the relative bound for large ones, which matches how floating-point error actually behaves.
  • Make NaN parity explicit. NaN != NaN in IEEE-754, so two engines that both produce NaN would read as a discrepancy unless you decide, in policy, that NaN-equals-NaN. Encode that choice rather than letting the language’s default surprise you.
  • Tolerance is per column, versioned, and audited. A single global epsilon is always wrong for some column; store tolerance per column with the profile version, so a verdict records exactly which bound it applied — the reproducibility the delta manifest generation stage depends on.

Verification

Assert that benign rounding reconciles, a systematic decimal error is caught, and money uses exact comparison.

python
ftol = ColumnTolerance(kind="float", abs_eps=1e-9, rel_eps=1e-6)
mtol = ColumnTolerance(kind="decimal", scale=2)

# Benign last-bit rounding on a large aggregate -> parity.
assert values_parity(1_000_000.0001, 1_000_000.0002, ftol)
# A 1-cent systematic difference on money -> NOT parity, no epsilon hides it.
assert not values_parity("19.99", "20.00", mtol)
# Exact money match survives representation differences.
assert values_parity("19.990", "19.99", mtol)
# NaN parity is explicit.
assert values_parity(float("nan"), float("nan"), ftol)
logger.info("numeric tolerance harness passed")

Operational Considerations

Derive each column’s epsilon from observed benign variance during a burn-in against known-good data, not from a guess, and re-derive when an engine or its numeric library changes, since floating-point behavior is version-sensitive. Store the per-column tolerance profile in version control and record its version with every verdict so a “these matched within tolerance” claim is reproducible and defensible. Expose tolerance_suppressed_total alongside value_diverged_total so an operator can see how much variance the epsilon is absorbing — a sudden rise in suppression can hide a real regression behind a too-generous bound. For regulated numeric columns, default to exact decimal comparison and require an explicit, reviewed exception to relax it, so the safe rule is the one you get without asking.