Case Study 2: $0.03

"The variance was three cents on twenty million dollars. Finance would not sign it off, and they were right not to."

Executive Summary

Kestrel's monthly reconciliation compares gold-layer net revenue against the source database. For eleven months it matched exactly. In the twelfth, it was off by three cents — and the investigation that followed found a DOUBLE PRECISION column in an intermediate model, introduced by a well-intentioned refactor.

Three cents is not a business problem. It is a correctness signal, and this case study is about why a team that had built a reconcile-to-the-cent acceptance criterion took a three-cent variance seriously, what they found, and what it revealed about the rest of the platform.

It also examines the counter-argument honestly, because it is a real one: several people on the team thought spending a week on three cents was absurd, and they had a case.

Skills applied: integer cents (§7.4); type propagation through transformations; the reconciliation criterion (Chapter 1 §1.7); testing the data rather than the pipeline (Chapter 1 §1.6).

Background

The acceptance criterion, from the platform charter written in Chapter 1:

For any calendar month, total net revenue computed from the gold layer equals total net revenue computed directly from the source kestrel_app database, to the cent — and every difference is explained by a documented, tested rule.

The check, run nightly and formally at month end:

WITH source AS (
    SELECT SUM(oi.quantity * oi.unit_price_cents - oi.discount_cents) AS cents
      FROM order_items oi
      JOIN orders o USING (order_id)
     WHERE o.placed_at >= :month_start AND o.placed_at < :month_end
       AND o.status NOT IN ('pending', 'cancelled')
),
gold AS (
    SELECT SUM(net_revenue_cents) AS cents
      FROM gold.fct_order_item
     WHERE date_key BETWEEN :start_key AND :end_key
       AND NOT is_cancelled
)
SELECT source.cents, gold.cents, source.cents - gold.cents AS variance_cents
  FROM source, gold;

Eleven months of variance_cents = 0. Then:

  source_cents  |  gold_cents  | variance_cents
----------------+--------------+----------------
   1,942,118,204| 1,942,118,201|              3

Three cents on $19,421,182.04.

The Problem

The immediate question was whether to care.

The case for not caring, made in the incident channel by two engineers and worth taking seriously:

• Three cents on nineteen million dollars is a relative error of $1.5 \times 10^{-10}$.
• No business decision changes.
• Finance's own systems have rounding differences larger than this with the payment processor.
• The team had a backlog and a week is a week.

The case for caring, which won:

• The criterion says "to the cent." A criterion that is relaxed the first time it is inconvenient was never a criterion. The value of the check is that it is binary; a check with a tolerance nobody agreed in advance is a check that will be argued about every month.
• Three cents is not a rounding difference in a system that uses integers. In a system where every amount is BIGINT cents, arithmetic is exact and a variance of any size means something is not an integer somewhere. The magnitude was uninformative; the existence was the signal.
• Eleven months of exact matches established a baseline. The interesting fact was not "three cents" but "the first non-zero variance in a year," and something had changed.

That second point is the transferable one. In an exact-arithmetic system, a small error and a large error are the same finding. The team had built a system where variance should be structurally impossible, so any variance meant the structure was violated. In a floating-point system, a three-cent variance would genuinely have been noise, and they would have had no signal at all.

📐 Design Decision — A tolerance you set in advance, or none at all

The debate produced a rule worth adopting.

A reconciliation with no tolerance is binary and unarguable. It also fails on legitimate timing differences, so it only works where the comparison is genuinely exact — same rows, same arithmetic, both sides integers.

A reconciliation with a tolerance survives timing noise and requires you to set a number. The danger is that the number gets set after the first failure, at whatever value makes the failure go away, which is how a check becomes decorative. Chapter 4's Case Study 2 is the other half of this: a 0.5% tolerance hid twenty-six nights of systematic loss.

Kestrel's rule, adopted here: every reconciliation declares its tolerance in the same commit that creates it, with a written justification, and changing a tolerance requires the same review as changing a metric definition. Where exactness is structurally achievable, the tolerance is zero and stays zero.

And a second check regardless of tolerance: sign persistence. Seven consecutive same-sign variances alert even if every one is inside tolerance. That is the check Chapter 4's incident needed and this one did not.

The Analysis

The investigation took three days, most of it spent narrowing rather than fixing.

Day 1 — Locate the layer

The layer-count technique from Chapter 2, adapted to sums rather than row counts:

SELECT 'source'        AS layer, SUM(...) FROM order_items ...
UNION ALL SELECT 'bronze', SUM(...) FROM bronze.order_items ...
UNION ALL SELECT 'silver', SUM(...) FROM silver.order_items ...
UNION ALL SELECT 'gold',   SUM(net_revenue_cents) FROM gold.fct_order_item ...

 layer  |      cents
--------+----------------
 source | 1,942,118,204
 bronze | 1,942,118,204
 silver | 1,942,118,204
 gold   | 1,942,118,201     <- here

Bronze and silver were exact. The loss was in the silver-to-gold transformation.

Day 2 — Find the rows

Comparing at the row level rather than the aggregate:

SELECT s.order_item_id,
       s.quantity * s.unit_price_cents - s.discount_cents AS silver_cents,
       g.net_revenue_cents                                AS gold_cents
  FROM silver.order_items s
  JOIN gold.fct_order_item g USING (order_item_id)
 WHERE s.quantity * s.unit_price_cents - s.discount_cents <> g.net_revenue_cents;

Seven rows. Each off by one cent, in both directions — four low, three high, netting to three cents.

That distribution was the clue. A systematic bug would err in one direction. Errors in both directions at ±1 cent is the signature of rounding, and rounding should not be happening anywhere in an integer pipeline.

Day 3 — Find the type

The seven rows had one thing in common: all involved a promotion with a percentage discount, and all had quantities where the percentage produced a fractional cent.

The transformation, added six weeks earlier in a refactor that consolidated discount logic:

-- silver_to_gold_order_item.sql, as it was
SELECT
    order_item_id,
    quantity,
    unit_price_cents,
    -- The refactor. Looks harmless.
    ROUND(quantity * unit_price_cents * (p.discount_percent / 100.0))::BIGINT
        AS discount_cents,
    quantity * unit_price_cents
        - ROUND(quantity * unit_price_cents * (p.discount_percent / 100.0))::BIGINT
        AS net_revenue_cents
FROM silver.order_items
LEFT JOIN dim_promotion p USING (promotion_key)

p.discount_percent was DOUBLE PRECISION. Dividing by 100.0 — a float literal — produced a float. Multiplying an exact integer by a float produces a float. ROUND on a float rounds a value that is already slightly wrong, and ::BIGINT then casts the slightly-wrong rounded value.

For most rows the float error was far below half a cent and the rounding absorbed it. For seven rows across a month, the true value sat close enough to a .5 boundary that the float error pushed it across.

Example, from row 3 of the seven:

  quantity = 3, unit_price_cents = 4,995, discount_percent = 15.0

  exact:  3 * 4995 * 15 / 100  = 224,775 / 100 = 2,247.75  -> rounds to 2,248
  float:  3 * 4995 * (15.0/100.0)
          15.0/100.0 = 0.1499999999999999944488848768742172978818416595458984375
          = 2,247.7499999999999167...                       -> rounds to 2,247

One cent, from the fifteenth decimal place of a float literal.

⚠️ Failure Mode — A float that entered through a percentage

The money columns were all BIGINT. Every code review had checked that. scripts/validate.py checks it. The float did not enter through a money column — it entered through a percentage.

discount_percent is not money. It is a rate, it is legitimately fractional, and DOUBLE PRECISION looks like a perfectly reasonable type for it. The moment it multiplies an integer money value, the result is a float, and the integer discipline is broken silently.

The general shape: an exact system is only exact if every operand is exact. Guarding the money columns is necessary and not sufficient; you must guard everything that touches them.

Three defenses:

1. Store rates as integers too. discount_basis_points INTEGER — 1,500 for 15% — so the arithmetic is quantity * unit_price_cents * basis_points / 10000 with integer division and an explicit rounding rule.
2. If you must use a decimal rate, use NUMERIC, never a float. NUMERIC(7,4) is exact and 15.0 / 100.0 in NUMERIC is exactly 0.15.
3. Make the rounding rule explicit and single. ROUND(x) in the middle of an expression is a business decision (round half up? half even? toward zero?) made by whatever the engine's default is. Name it.

Kestrel adopted all three. And the validator was extended: it now flags a float anywhere in an expression that produces a _cents column, not just a float money column.

The Decision

Four changes.

1. discount_percent DOUBLE PRECISION became discount_basis_points INTEGER. 1,500 for 15%. All arithmetic is integer:

-- Integer throughout. The rounding rule is explicit, named, and in one place.
-- Half-up, matching what finance does, decided in a meeting and written here.
(quantity * unit_price_cents * discount_basis_points + 5000) / 10000 AS discount_cents

The + 5000 before integer division is half-up rounding on a 10,000 divisor, done in integers. It is less readable than ROUND(...) and it is exactly reproducible, which is the trade.

2. The validator was extended to flag floats in expressions producing _cents columns, not just float column types.

3. A type audit across the whole platform, looking for the same shape. It found three more:

None had yet produced a visible error. All three were the same latent bug, waiting for a value near a rounding boundary. This is the finding that justified the week: the reconciliation caught one instance and the audit caught three more, and the three had been in production for between four and fourteen months.

4. The seven affected rows were corrected and the month restated, with a note. Three cents. Finance signed it.

What Happened

Since the fix, twenty-two consecutive months of variance_cents = 0.

Three follow-on observations:

The audit is now annual. Not because floats keep appearing, but because new columns keep appearing, and the check costs an hour.

The + 5000 idiom got a macro. After the second time someone wrote it slightly wrong in review, it became {{ round_half_up(expr, 10000) }} in the dbt project, with a test. An idiom that is easy to write wrong should be a function, and the fact that it was written wrong twice is the evidence.

The two engineers who argued against investigating changed their position, and their stated reason is the useful part: not that three cents mattered, but that they had not appreciated that three cents in an integer system is a structural violation rather than a small number. The magnitude had misled them, and the magnitude was the least informative thing about it.

Lessons

1. In an exact-arithmetic system, a small variance and a large one are the same finding. The existence is the signal; the magnitude is uninformative.

2. A criterion relaxed the first time it is inconvenient was never a criterion. Set the tolerance in the commit that creates the check, with a justification, and change it only under review.

3. Errors in both directions at ±1 unit is the signature of rounding. A systematic bug errs one way. That distribution located the bug on day two.

4. An exact system is only exact if every operand is exact. The money columns were all correct. The float entered through a percentage.

5. Guard the expressions, not just the columns. The validator now checks anything producing a _cents column.

6. When you find one instance, audit for the shape. Three more, in production between four and fourteen months, none yet visible.

7. Make the rounding rule explicit and single. ROUND(x) mid-expression delegates a business decision to an engine default.

8. An idiom written wrong twice should become a function. {{ round_half_up() }} with a test.

Questions for Discussion

1. Two engineers argued the investigation was not worth a week. Reconstruct their strongest case, and say what — other than the eventual finding of three more instances — would have justified the decision at the time it was made.

2. The rule adopted is that a tolerance is declared in the commit that creates the check. What happens when a check with a zero tolerance starts failing for a legitimate reason? Design the process, and say how you keep it from becoming a rubber stamp.

3. discount_percent DOUBLE PRECISION is a reasonable type for a percentage in isolation. Where should the guard live — the column type, the transformation, the test, or the review? Argue for one primary location.

4. The fix uses (x * bp + 5000) / 10000 for half-up rounding. Write the half-even (banker's rounding) version. Which should a retailer use, and does the answer depend on jurisdiction?

5. The audit found three latent instances, one of them fourteen months old. Estimate how many similar latent type issues a five-year-old platform carries. What is a proportionate response?

6. This reconciliation compares gold to source using the same arithmetic on both sides. What class of error does that design fail to catch, and what second check would you add?

7. Chapter 4's Case Study 2 had a 0.5% tolerance that hid systematic loss; this one had a zero tolerance that caught three cents. Write the guidance you would give a team designing its first reconciliation, given both.