Silent Data Corruption from an Off-By-One Migration

Overview

A currency migration finished with no errors, and 11 days later reconciliation found $847,000 of revenue recorded short.

What happened

The payment platform stored money as integer cents. A migration converted a legacy float column, which held dollars with two decimal places, into that integer format. It passed every test environment and staging with a sample of production data.

Why it broke

The migration used integer division in Python, amount / 100, instead of int(round(amount * 100)). That floor operation silently truncated values such as $19.999... to $19.99 instead of $20.00.

> [!WARNING] The migration log said success. Zero SQL errors says nothing about whether the values are right.

Impact

• Rows migrated — 14 million
• Migration time — 22 minutes
• Time to detection — 11 days
• Size of the errors — Off by 1 cent, occasionally by 1 dollar
• Under-recorded revenue — $847,000

The fix

The team was fortunate to have an event store: replaying the original transaction events rebuilt the correct amounts. Without it, the corruption would have been unrecoverable.

Key concepts

Floating point precision error

Binary floating point cannot represent many decimal fractions exactly (e.g., 0.1 in binary is a repeating fraction), leading to tiny rounding errors that accumulate.

Integer arithmetic for currency

Store monetary amounts as integers (cents or the smallest currency unit) and avoid floating point entirely for any financial calculation.

Migration dry run with comparison

Running a migration against a production replica and comparing before/after value distributions (min, max, sum, sample of rows) before applying to production.

Event sourcing

Storing every state change as an immutable event, allowing the current state to be rebuilt by replaying events — providing a recovery path even after corruption.

When to use it

• Discussing database migration safety and verification practices
• Explaining why financial systems require integer arithmetic
• Designing event sourcing for auditability and recovery

Interview questions

• How do you safely run a data-transforming migration on a large production table?

More War Stories topics

• Internal DNS Cache Poisoning That Redirected API Traffic
• Malicious npm Package Exfiltrated Environment Variables at Build Time
• N+1 Query Pattern That Collapsed Under Load
• NAT Gateway Port Exhaustion Under Bursty Lambda Traffic
• Node.js Memory Leak That Required Daily Restarts
• Public S3 Bucket Exposed Customer PII for 8 Months
• Single-Region Dependency That Caused a Global Outage
• The Cascade That Took Down Three Services
• Third-Party API Outage That Was Mistaken for Own Code
• Thundering Herd After a Cache Cluster Restart
• TLS Certificate Expiry That Took Down an Internal API