For the same records, a database reports 1.01 while the displayed line amounts add to 1.02. Both show two decimal places and neither contains duplicate facts. Increasing tolerance and asking a model to describe the difference as normal error hides a rule disagreement. Acceptance must establish where precision is retained, when rounding happens and who receives residual amounts.
Freeze the calculation contract, not just the column type
For each monetary metric, record currency, unit, business status, fact grain, input and working precision, rounding stage, rounding mode and output precision. Unit prices, exchange rates, proportions and settled amounts can require different precision. Two decimal places in the final display do not justify truncating every intermediate field. This article concerns engineering contracts, not a substitute for applicable accounting or tax rules.
The semantic layer should reference that contract rather than let a model choose between rounding and summing on every request. Posted amounts should come from the appropriate confirmed facts, not a silent recalculation using today's prices or rules. Simulated allocations and estimates should disclose their rule version and assumptions instead of being mixed with actual receipts.
Join multiplicity is another cause of amount discrepancies. First check cardinality and amount conservation in multi-table joins so each fact contributes once, then examine precision. This article assumes the fact population is correct and studies how representation, operation order and transport change values. Separating these layers prevents rounding from becoming a patch for double counting.
Decimal types address representation, but precision still needs design
PostgreSQL 17 treats numeric and decimal as equivalent and recommends numeric rather than approximate floating-point types when exact monetary storage and calculation are required.[1] A numeric(p,s) declaration still has limits: p is total precision and s is scale. Inputs with more fractional digits are rounded, and excessive integer digits produce an error.
A unit price requiring four fractional digits loses information if its first landing table stores only two. Widening a downstream column cannot reconstruct that information. Inspect types across source, staging, transformation, metrics, APIs and display, especially implicit conversions. Converting a decimal result back to binary floating point can reintroduce approximation in a subsequent stage.
Integer minor units are another option for amounts definitively settled in cents, provided currency, unit and aggregate range are explicit. They do not automatically suit finer unit prices, ratios or exchange rates. Conversion to minor units also needs a rounding policy. Multiplying everything by 100 is not a business contract, and amounts in different currencies cannot simply be added.
Rounding lines and rounding totals define different metrics
Consider a constructed example using nearest rounding with ties away from zero, not an assumed industry rule. Three unrounded line amounts are each 0.335. Rounding each to two places produces 0.34 three times, totaling 1.02. Summing first produces 1.005, which rounds to 1.01. The 0.01 difference arises because these operations are not interchangeable.
If the contract sums individually settled lines, aggregate those settled results. If it settles an aggregate of raw amounts, preserve raw precision to the specified point. Do not change the total's meaning merely to make it equal the sum of formatted screen values. The interface can explain that raw values are aggregated before rounding, or show an explicit rounding adjustment.
Modes must agree too. Python Decimal's default context uses ROUND_HALF_EVEN, while PostgreSQL numeric rounds ties away from zero.[1][2] Exact decimal 1.005 becomes 1.00 under the former and 1.01 under the latter approach; -1.005 becomes -1.00 and -1.01 respectively. Include negative refunds in tests rather than establishing compatibility from positive examples alone.
In Python, construct Decimal('1.005') from decimal text and use quantize with an explicit target and mode.[2] Converting through float first does not let Decimal recover the original input intention. Decimal arithmetic also has context precision, particularly relevant to division and large products. Using Decimal does not mean intermediate calculations can never round.
Allocate residuals deterministically and reproducibly
In another constructed example, allocate 1.00 equally to three objects in cents. Three amounts of 0.33 total only 0.99. One possible policy calculates base minor-unit allocations, then distributes remaining units in an agreed order, producing 0.34, 0.33 and 0.33. The extra cent is the previously unallocated part of the original total.
For a nonnegative total and nonnegative weights, a largest-remainder method can calculate ideal shares, round each down to the minor unit, count remaining units and assign them by descending fractional remainder. Break ties with a stable business key and retain the rule version. Undeclared database row order is unsuitable: rerunning could assign the same cent to another object.
The assumptions matter. Negative amounts, mixed-sign weights, a zero denominator, caps and ineligible objects require separate policies rather than blind application of the positive-value method. Refunds may reverse the original allocation or follow another approved scheme, but must not accidentally use changed weights to recalculate an old transaction and break reconciliation.
Acceptance checks conservation of the input total, eligibility, per-object constraints and stability. Changing input order for the same business keys should preserve allocations. Repeating the same inputs and rule version should return the same object amounts. An analytical allocation must not be described as an independently recorded payment for each object.
Nulls, transport and display create three more boundaries
PostgreSQL sum aggregates non-null values and returns null when no rows are selected.[3] Missing amounts, no eligible records and genuine zero amounts therefore need distinct representations. Return fact count, non-null amount count and total together where useful. Replace empty results with zero only when the business definition permits it; otherwise missing arrivals may be presented as no revenue.
Cross-service transport needs a precision contract. Options include integer minor amounts with currency and unit, or canonical decimal strings parsed as decimals by the receiver. A normal-looking JSON literal does not prove every client preserves its precision. Test round trips for large amounts and long fractions: database output, API encoding and decoding, client reading, then reconstruction and comparison under the contract.
Thousands separators, two-place display and presentation in larger units should remain separate from calculation. Do not feed formatted strings into the next aggregation. Give the model unit, precision, definition version and missing-data information with the result. Its job is to explain, not mentally recompute another total. Additional arithmetic should use the same controlled calculation path.
Locate differences through staged reconciliation, not universal tolerance
Build a small explicit regression set covering ordinary decimals, midpoint ties, negative refunds, tiny amounts, large-value limits, empty sets, partial nulls, equal thirds and zero weights. Preserve raw input, exact intermediates, rounding points, expected output and rule version. The rounding examples here were checked with Python decimal arithmetic; no database performance experiment was run and no production acceptance is claimed.
Compute an independent control total for the same fact population, then compare raw facts, transformations, allocations, API output and final display in order. Find the first changed stage: parsing changes belong to ingestion; failed allocation conservation belongs to the algorithm; display-only differences point to formatting or units. This avoids repeatedly adding round to the final query without diagnosis.
Tolerance must implement an approved rule. Settled integer-cent amounts over the same population can require exact equality. Metrics involving conversion or multiple rounding stages need explicit adjustment categories and accepted limits, not one percentage for every amount. An undefined rule should produce an uncertified result or suspend the metric's formal answer, rather than automatically zeroing the discrepancy.
Deliver a readable monetary contract, cross-language compatibility cases, residual details and exception handling. Treat future rounding or allocation changes as metric versions and assess whether history must be recalculated. Trustworthy amounts come from a reproducible calculation chain, not the final two digits on a screen.