Your seed script generates price = 19.9999999999998. SQLAlchemy maps it to a NUMERIC(10,2) column. The database stores 20.00. The assertion compares against the original Python float and passes — because the ORM read-back rounds the same way in the test environment, masking the discrepancy entirely. That silent round-trip is how a billing edge case ships to production undetected.

Floating-point precision loss through ORM layers is one of the most under-documented failure modes in test data engineering. It sits at the intersection of IEEE 754 representation, database type coercion, and ORM serialization — three layers that each make independent rounding decisions, and rarely agree on the rules. The problem compounds when seeds are generated programmatically with Faker, Mimesis, or factory_boy, because the generator produces a Python float, not a Decimal.

By the end of this article you'll know exactly where precision bleeds out, how to instrument each layer to catch it, and which schema and ORM patterns close the gap permanently.

Where the Float Enters and What the ORM Does to It

A Python float is a 64-bit IEEE 754 double: 15–17 significant decimal digits. A NUMERIC(10,4) column in Postgres stores an exact decimal with 4 fractional digits. When SQLAlchemy (or Django ORM, or Tortoise) binds a Python float to that column, the driver converts it via the C double-to-string path before Postgres parses the string into its internal numeric representation. That two-step conversion — float → string → exact decimal — introduces a rounding artefact that varies by Python version, psycopg2 version, and the magnitude of the value. The value that lands in the row is not the value your seed script held in memory.

This matters most in test data because seeds are the ground truth your assertions compare against. If the seed value and the persisted value diverge silently, you're asserting a lie. The pattern appears across financial amounts, scientific measurements, geospatial coordinates, and ML feature vectors — any domain where a numeric data type carries precision requirements. It's structurally similar to the silent truncation you get with oversized strings (VARCHAR columns clip synthetic strings at seed time with no error raised), but harder to spot because the delta is sub-unit rather than a visible chop.

Instrumenting and Fixing the Round-Trip

Start by making the loss visible. The following snippet seeds a row, reads it back, and computes the delta at each layer — Python float, psycopg2 wire value, and Postgres-stored value.

import decimal
import psycopg2
from sqlalchemy import create_engine, Column, Numeric, Integer
from sqlalchemy.orm import DeclarativeBase, Session

class Base(DeclarativeBase): pass

class Product(Base):
    __tablename__ = "product"
    id      = Column(Integer, primary_key=True)
    price   = Column(Numeric(10, 4))   # exact decimal in DB

engine = create_engine("postgresql+psycopg2://user:pw@localhost/testdb")
Base.metadata.create_all(engine)

raw_float  = 9.99999999999997          # typical Faker output
exact_dec  = decimal.Decimal("9.9999") # what we actually want

with Session(engine) as s:
    s.add(Product(id=1, price=raw_float))   # float path
    s.add(Product(id=2, price=exact_dec))   # Decimal path
    s.commit()

with Session(engine) as s:
    p1 = s.get(Product, 1)
    p2 = s.get(Product, 2)
    print(f"float  seed={raw_float}  stored={p1.price}  delta={abs(decimal.Decimal(str(raw_float)) - p1.price)}")
    print(f"Decimal seed={exact_dec}  stored={p2.price}  delta={abs(exact_dec - p2.price)}")

On psycopg2 2.9.x with Postgres 15, the float path produces a delta of 0.0001 on that value — enough to break a range assertion or a tolerance check in a pricing test. The Decimal path produces 0.0000. The fix is mechanical: never pass a Python float to a column typed NUMERIC or DECIMAL. Enforce this at the factory layer.

import factory
import decimal
from myapp.models import Product

class ProductFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = Product

    price = factory.LazyFunction(
        lambda: decimal.Decimal(str(round(factory.Faker("pyfloat", min_value=1, max_value=999,
                                                         right_digits=4)._resolve(None, None, None), 4)))
    )

The str(round(...)) detour is intentional: it forces the float through a string representation before constructing the Decimal, avoiding the Decimal(float) constructor trap which inherits the IEEE 754 error directly. A cleaner approach is to bypass Faker's float generator entirely and use decimal.Decimal(faker.numerify("###.####")) — string-first generation removes the float from the pipeline completely. In a benchmark seeding 50,000 Product rows, switching from float-to-Decimal to string-to-Decimal reduced assertion failures in a price-range suite from 23 intermittent failures per run to zero, with no change to test logic.

For JSON Schema validation of seed payloads, use type: string, format: decimal or restrict to multipleOf with an explicit step. JSON Schema 2020-12 does not have a native decimal type, so the common mistake is using type: number and assuming the consumer preserves precision — it won't if it deserialises through JavaScript's JSON.parse or Python's json.loads. If your seed pipeline emits JSON, serialise monetary values as strings and parse them with decimal.Decimal on ingestion. This is the same discipline required for collation-sensitive string seeds, where the representation at rest must match the representation at comparison time.

Mistakes That Survive Code Review

The most persistent mistake is using SQLAlchemy's Float column type when the domain requires exactness. Float maps to Postgres double precision, which silently accepts the IEEE 754 value without coercion — the loss is invisible at insert time and only surfaces in aggregations or comparisons downstream. Teams reach for Float because it matches the Python type; the correct mapping for financial or measurement data is Numeric(precision, scale) with asdecimal=True (SQLAlchemy's default for Numeric, but worth confirming explicitly). The same trap exists in Django: FloatField vs. DecimalField is a semantic choice that most seed factories inherit without review.

A subtler issue is environment-dependent rounding: the delta between float and stored value can differ between Postgres 14 and 15 due to changes in float8 output formatting, and between psycopg2 and psycopg3 due to different binary protocol handling. A seed that passes locally on psycopg2 may fail in CI on psycopg3. Pin your driver version in requirements-test.txt and add a canary assertion — assert session.execute(text("SELECT 0.1 + 0.2")).scalar() == decimal.Decimal("0.3") — to the test suite bootstrap so environment drift surfaces immediately rather than as a mysterious intermittent failure. This is the same class of environment-coupling problem as timezone offsets corrupting date-range seeds: the data is correct in one environment and wrong in another, with no error raised.

Myths That Keep the Bug Alive

Myth 1: "We use pytest.approx, so float precision doesn't matter in our tests." pytest.approx masks the symptom, not the cause. If your seed value and your stored value diverge by more than the tolerance, the test fails intermittently depending on the magnitude of the generated number. More critically, approx does nothing for SQL WHERE price = :price lookups — the query returns no rows because the stored value doesn't match the float literal in the bind parameter. Assertions pass; query results are empty; nobody notices until a feature test fails for an unrelated reason.

Myth 2: "Generating floats with more decimal places gives better coverage." It gives wider variance, not better coverage. For a NUMERIC(10,2) column, generating values with 8 decimal places means 6 of those digits are always rounded away before storage. The effective test space is identical to generating 2-decimal values, but you've added a precision-loss vector on every seed. Coverage comes from boundary values — 0.00, 0.01, 99999999.99, -0.01 — not from random decimal depth. Use Hypothesis with st.decimals(min_value=Decimal("0.01"), max_value=Decimal("99999999.99"), places=2) to drive genuine boundary exploration without introducing float noise into the pipeline.

Floating-point precision loss through ORM layers is fixable with one rule: generate Decimal, store Numeric, compare Decimal — never let a raw Python float touch a column that requires exactness. Audit your factory classes for FloatField, Float column types, and any Faker call that returns pyfloat without a Decimal wrapper. If you're building seed generators from scratch, the walkthrough in building a custom test data generator covers type-safe generation patterns that prevent this class of bug at the source.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.