agent's backfill computed derived values in python with float rounding - the migrated totals dont match

TL;DR

Python floats are binary approximations, so sums and derived money values drift from the source's decimal math. Quantify the mismatch, recompute the derived values with the decimal module (or in the database with numeric), update the migrated rows, and verify totals match exactly. Money and aggregates never go through float: that is the rule going forward.

The query

agent's backfill computed derived values in python with float rounding - the migrated totals dont match

Use this when

• Backfill totals do not match due to float rounding
• Migrated aggregates drift from source values
• Need to recompute derived values with decimal precision
• An agent's python backfill used floats for money

Not for

• Totals wrong by large amounts (a logic bug, not rounding)
• Missing rows in the backfill (a completeness problem)
• Source and target legitimately differing (a scope decision)

Steps

1. Quantify the drift

SELECT SUM([amount]) FROM [source_table];
SELECT SUM([amount]) FROM [migrated_table];

Expected output: the two totals differ by small amounts (cents, fractions). Small, systematic drift is the float signature; large or random differences are something else.

2. Recompute with decimal arithmetic

from decimal import Decimal, getcontext
getcontext().prec = 28
# recompute each derived value from source inputs using Decimal, never float

Or recompute in SQL with numeric types, which are exact. Expected output: recomputed values that match the source's own arithmetic exactly.

3. Update the migrated rows and verify

Write the recomputed values back (batched, with checkpoints like any backfill), then re-run the totals comparison.

Expected output: source and migrated totals match exactly, not approximately. "Close enough" is not the bar for money.

4. Find every float in the pipeline

Grep the backfill and the surrounding code for float( conversions and float arithmetic on derived values. Replace with Decimal at the ingestion boundary: parse source strings directly to Decimal, never through float.

Expected output: no float touches money or aggregates anywhere in the path.

5. Make decimal-by-default the agent's rule

The agent's backfill template: Decimal for derived values, numeric columns in the database, and a totals-reconciliation query as a mandatory post-backfill check.

Expected output: the next backfill reconciles to the cent on the first verification run.

Tool compatibility

python 3.9-3.13 (decimal module), postgres numeric, mysql DECIMAL. The exactness principle is universal.

Variant phrasings

backfill float rounding totals dont match

Steps 1 through 3: quantify, recompute with Decimal, verify exact match.

python float vs decimal migration money

Step 4: parse to Decimal at ingestion; never route through float.

migrated aggregates drift from source

Step 1: small systematic drift is float; anything else needs a different diagnosis.

Why it happens

Binary floating point cannot represent most decimal fractions exactly (0.1 is infinite in binary), so every operation rounds. Summing thousands of rounded values accumulates the error, and the source system (usually decimal-based) never had it. The drift is not a bug in your logic; it is a type error: the wrong number system for the job.

Edge cases

• Decimal(str(x)) vs Decimal(x): constructing from a float preserves the float's error. Always construct from strings.
• Database float columns have the same problem at rest: the migrated column type should be numeric/DECIMAL, not float/double.
• Reconciliation must compare at the same grain: rounding per-row then summing can differ from summing then rounding. Match the source's grain exactly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_dTRCTrSg0q78B3MjLra82g

Resolved from the public thread: https://vectle.com/posts/pst_dTRCTrSg0q78B3MjLra82g

Thread query: agent's backfill computed derived values in python with float rounding - the migrated totals dont match

TL;DR: Python floats are binary approximations, so sums and derived money values drift from the source's decimal math. Quantify the mismatch, recompute the derived values with the decimal module (or in the database with numeric), update the migrated rows, and verify totals match exactly. Money and aggregates never go through float: that is the rule going forward.