The FLOAT Money Bug

Concepts covered: Data Types

The subscription billing platform this lesson models has 3 tables that matter here: customers, invoices and invoice_lines. One row of invoice_lines is one priced line on one invoice: a quantity, a unit price and a line total. The first migration declared unit_price and line_total as FLOAT because the ORM mapped a Python float to that type by default. The revenue report summed line_total and matched the bank for 2 years, then drifted the month volume crossed 10 million lines.

Why 0.1 has no binary form

FLOAT and DOUBLE follow IEEE 754: a sign, an exponent and a binary fraction. A value is exact only when it is a sum of powers of 2, so 0.5 and 19.75 are exact while 0.1 repeats in binary the way 1/3 repeats in decimal. The nearest 64-bit double to 0.1 is 0.1000000000000000055511151231257827, and every operation carries that difference forward. Width depends on the platform: FLOAT is 32-bit single precision in MySQL, as is REAL in PostgreSQL, while PostgreSQL's bare FLOAT is a 64-bit double. The billing platform's columns were the 32-bit kind, good for about 7 significant digits, which is why the drift reached dollars.

SELECT 0.1::float8 + 0.2::float8 = 0.3::float8   AS equal_float,
       0.1::float8 + 0.2::float8                  AS total_float,
       0.1::numeric + 0.2::numeric = 0.3::numeric AS equal_decimal;

Two effects follow. The first is drift under aggregation: summing 0.1 across 1,000,000 rows returns 100000.00000133288 in DOUBLE and 100958.34 in 32-bit REAL, because each addition rounds and the rounding never cancels. The second is failed matching: a reconciliation join on invoices.total = payments.amount drops every pair computed along different arithmetic paths, and the dropped rows are the discrepancy nobody can explain.

DECIMAL and integer minor units

DECIMAL(p, s)

• p total digits, s of them after the point: DECIMAL(12,2) tops out at 9,999,999,999.99
• Addition, subtraction and multiplication are exact; division still rounds to s digits
• Readable in every tool with no conversion, which is why reporting schemas prefer it
• Arithmetic runs in software, roughly 3 to 10 times slower than hardware floats

BIGINT minor units

• $19.99 stored as 1999; the currency's minor unit is the whole unit of the column
• Integer arithmetic is exact and hardware-fast, with no rounding anywhere
• Needs a currency column beside it, because JPY has no minor unit and KWD has 3 digits
• Division by 100 belongs to the presentation layer, and every consumer has to know that

The platform's invoice_lines moves to unit_price DECIMAL(12,4) and line_total DECIMAL(12,2); 4 places on the unit price give a $0.0025 per-request metered rate a home. Tax rates get DECIMAL(6,5), which holds 0.08250 exactly. The rounding rule for a line total becomes a written policy, since 3 units at $0.0025 is $0.0075 and somebody decides whether that invoices as $0.01 or $0.00. Stripe's API takes every amount as an integer in the currency's smallest unit, the minor-units design applied to a whole payments company.

The integer design has a ceiling worth knowing. A 32-bit INT holds 2,147,483,647, so a running balance_cents INT on a customer account overflows after $21.47 million of lifetime billing, which one enterprise contract can reach. BIGINT removes that concern for any business that exists.

• $92.2 quadrillion

FLOAT remains the right type where approximation is the nature of the value: sensor readings, GPS coordinates, ML feature vectors, percentages computed for a chart. Measurement error there dwarfs representation error, and hardware speed matters when a query scans billions of rows. The rule per column is one question: will anyone ever reconcile this number against another system? If yes, the type is exact.

The platform adds exchange_rate to invoices so that EUR invoices can be reported in USD. A rate like 1.08734 is multiplied against every line total. Which type holds it?

• FLOAT, since a rate is a measurement and multiplication is fast
• DECIMAL(18,8), with the rounding of the converted total defined in one place
• BIGINT, storing the rate as 108734
• DECIMAL(5,2), the same shape as the money columns

About this lesson

This section is part of the Schema Types lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.