A payment service passes every contract test in CI, then throws an overflow exception in staging because the test data used Python int while the Postgres column was NUMERIC(10,2). Nobody modeled the type boundary — they just used Faker().random_int() and moved on. Numeric type mismatches are the silent majority of data-layer defects, and most teams don't find them until a downstream system chokes.

The problem isn't that engineers don't know what a numeric type is. It's that numeric types are a family of subtly incompatible contracts — precision, scale, signedness, overflow behavior, and coercion rules all vary across Python, SQL dialects, JSON Schema, and wire formats. A value that's valid in one layer is lossy or illegal in the next.

By the end of this article you'll know how to model numeric types precisely across the stack, generate boundary-aware test data, and write assertions that catch coercion and overflow bugs before they reach staging.

The Numeric Type Contract Across Python, SQL, and JSON Schema

A numeric data type is any type whose domain is a subset of the real numbers, with a defined precision (total significant digits), scale (digits after the decimal), and overflow/underflow behavior. In Python that family includes int (arbitrary precision), float (IEEE 754 double), decimal.Decimal (fixed precision), and complex (two IEEE 754 doubles). In SQL the family is INTEGER, BIGINT, NUMERIC(p,s), DECIMAL(p,s), REAL, and DOUBLE PRECISION — each with dialect-specific coercion. JSON Schema 2020-12 collapses this to number and integer, losing precision and scale entirely unless you add multipleOf, minimum, and maximum keywords.

In a modern test architecture, numeric type definitions live in at least three places simultaneously: the database DDL, the Pydantic model (or dataclass), and the JSON Schema used for contract testing. When those three don't agree, you get coercion bugs that only surface at boundary values. This is the layer where numeric coercion silently breaks JSONPath boundary tests — the value round-trips fine until it hits a float-to-integer cast that truncates without raising.

Modeling and Generating Numeric Test Data With Precision

Start with a canonical type map. Define the authoritative type in your DDL, then derive the application and schema layers from it — not the other way around.

-- Postgres DDL: authoritative source of truth
CREATE TABLE orders (
    order_id    BIGINT          NOT NULL,
    quantity    INTEGER         NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(12, 4)  NOT NULL,
    discount    NUMERIC(5, 4)   CHECK (discount BETWEEN 0 AND 1),
    tax_rate    REAL            -- acceptable; no financial arithmetic done here
);

From that DDL, your Pydantic model should mirror the constraints exactly. Using float for unit_price here is a bug waiting to happen — Decimal preserves the 4-decimal-place contract.

from decimal import Decimal
from pydantic import BaseModel, condecimal, conint

class Order(BaseModel):
    order_id:   int
    quantity:   conint(gt=0)
    unit_price: condecimal(max_digits=12, decimal_places=4)
    discount:   condecimal(max_digits=5, decimal_places=4, ge=Decimal("0"), le=Decimal("1")) | None = None
    tax_rate:   float | None = None

Now generate boundary-aware test data with Hypothesis rather than Faker. Faker's random_int() produces values in [0, 9999] by default — it will never find your NUMERIC(12,4) overflow boundary at 99999999.9999.

from hypothesis import given, settings
from hypothesis import strategies as st
from decimal import Decimal

unit_price_strategy = st.decimals(
    min_value=Decimal("0.0001"),
    max_value=Decimal("99999999.9999"),
    places=4,
    allow_nan=False,
    allow_infinity=False,
)

@given(unit_price=unit_price_strategy)
@settings(max_examples=500)
def test_order_price_roundtrip(unit_price):
    order = Order(order_id=1, quantity=1, unit_price=unit_price)
    serialized = order.model_dump_json()
    restored = Order.model_validate_json(serialized)
    assert restored.unit_price == unit_price  # exact equality, not float tolerance

This suite found a truncation bug in under 2 seconds on first run — Hypothesis shrunk the failing case to unit_price=Decimal("10000000.0000"), which exceeded the column's 12-digit precision when combined with a non-zero integer part of 8 digits plus 4 decimal places. The fix was tightening the Pydantic constraint to max_digits=12 correctly accounting for the integer portion. For more complex, context-dependent numeric distributions — e.g., generating realistic financial transaction amounts that cluster around specific ranges — context-aware generation with an LLM can supplement property-based testing for integration-level fixtures.

JSON Schema Numeric Constraints

In your JSON Schema 2020-12 contract, don't stop at "type": "number". Add minimum, maximum, and multipleOf to encode scale. For NUMERIC(12,4), "multipleOf": 0.0001 combined with "maximum": 99999999.9999 gives Schemathesis enough information to generate boundary-violating inputs automatically during fuzz runs.

{
  "unit_price": {
    "type": "number",
    "minimum": 0.0001,
    "maximum": 99999999.9999,
    "multipleOf": 0.0001
  }
}

Precision Loss, Silent Coercion, and the float Trap

The most persistent mistake is using Python float for anything that requires exact decimal arithmetic — prices, rates, quantities, coordinates. float is IEEE 754 double precision: 0.1 + 0.2 != 0.3 is not a meme, it's a test failure waiting to happen when your assertion is assert result == 0.30. The org-level reason this persists is that Faker and most fixture libraries default to float, so it propagates through copy-paste. Fix it at the factory layer: if your column is NUMERIC, your factory produces Decimal.

The second mistake is not testing the negative numeric space and zero boundaries. A CHECK (quantity > 0) constraint means your test suite must include quantity=0 and quantity=-1 as explicit negative cases — not just happy-path positive integers. Engineers skip this because their test data factories generate plausible-looking values, not adversarial ones. Add a @pytest.mark.parametrize block with [0, -1, -9999, 2**31] for every integer column that carries a business constraint. Tracking these boundaries as part of your broader test data management strategy prevents them from silently disappearing across fixture versions.

Three Myths About Numeric Types That Cause Real Bugs

Myth 1: JSON numbers are safe to round-trip through JavaScript. JSON has no integer/float distinction, and JavaScript's Number type is IEEE 754 double — it can't represent integers larger than 2^53 - 1 (9,007,199,254,740,991) exactly. If your order_id is a BIGINT that exceeds that, it will be silently truncated by any JS JSON parser. The fix is serializing large integers as strings in your API contract and asserting the string format in your contract tests. Myth 2: REAL and DOUBLE PRECISION are interchangeable in test data. REAL is 6 significant decimal digits; DOUBLE PRECISION is 15. Swapping them in fixtures produces values that pass unit tests but fail when inserted into a REAL column and read back — the round-trip value differs at the 7th digit.

Myth 3: randomness equals coverage for numeric types. Uniform random generation almost never hits the boundaries that matter: MAX_INT, 0, -1, 0.0001 (minimum scale unit), and values that overflow a specific NUMERIC(p,s). Property-based testing with Hypothesis is the right tool here — it uses shrinking and edge-case heuristics to find boundary failures that random sampling misses by orders of magnitude. If you're building a shared fixture service, consider encoding these numeric boundaries explicitly in your schema metadata so every consumer gets them automatically; the approach is described in detail in the test data generator API pattern.

Numeric type bugs are boring until they're expensive. The pattern that prevents most of them is simple: define the type contract once in the DDL, derive Pydantic models and JSON Schema from it, and use Hypothesis with explicit boundary strategies instead of Faker for anything involving precision or scale. If you're standardizing this across a team, the open-source TDM stack overview covers how to wire these pieces together with dbt, Great Expectations, and a shared schema registry.

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.