Correction and Reversal Patterns

Concepts covered: Immutable Logs

On 2 October the billing feed delivers charge 7731: account 4410, 15.49. It loads into fact_charge in the 2 October partition and the month's revenue report sums it. On 9 October the feed delivers a correction: charge 7731 was prorated and should have been 9.99. The 2 October partition is closed, the report went out, and finance has read it. A correction is a late fact that disagrees with a fact already loaded, and that disagreement is what makes it harder than plain lateness, because now 2 rows claim the same event.

3 ways to carry a correction

Update in place is what a transactional system does and what a fact table should refuse. It rewrites a closed partition, leaves no trace that 15.49 was reported, and a reader reproducing the 3 October revenue report gets a number it never showed. Versioned rows keep the history but push a filter onto every reader, and the moment someone forgets is_current = true, revenue doubles for every corrected charge.

A 4th shape deserves a name: the accumulating snapshot, one row per charge whose columns update as the charge is posted, then prorated, then refunded. It's a legitimate design for process-tracking facts with a bounded lifecycle, and it is update in place by construction, so it belongs on a table nobody reproduces historical numbers from.

The reversal ledger

A reversal appends 2 rows: an exact negation of the row being corrected, and the corrected row. Additive measures sum correctly with no filter at all. Counting is the one thing the pattern breaks, and a sign column fixes it.

sum(amount) is 9.99 and sum(row_sign) is 1, so a count of charges is a sum of signs, never count(*), which would say 3. Every measure on the table must be additive or be computed from additive parts. A max(amount) over this ledger returns 15.49 and means nothing, and an average must be sum(amount) / sum(row_sign). That constraint is the real cost of the reversal pattern, and it's the one to state in an interview before the interviewer asks.

INSERT INTO fact_charge
SELECT
  f.charge_id,
  c.source_version,
  f.account_id,
  f.charge_date,
  c.received_date AS processed_date,
  -1 * f.amount AS amount,
  -1 AS row_sign
FROM stg_charge_correction c
JOIN fact_charge f
  ON f.charge_id = c.charge_id
  AND f.source_version = c.supersedes_version
  AND f.row_sign = 1
LEFT JOIN fact_charge applied
  ON applied.charge_id = c.charge_id
  AND applied.source_version = c.source_version
WHERE applied.charge_id IS NULL

The join on supersedes_version negates the exact row the source says it is correcting, so a charge corrected twice reverses version 2 and leaves version 1's reversal alone. The anti-join against rows already carrying this correction's source_version is what makes the job safe to rerun. Without it, a retried load appends a second reversal, revenue for the account goes negative, and the error is found by an accountant rather than a test. The corrected 9.99 row is the second half of a UNION ALL in the same statement, omitted here, so a run writes both rows or neither.

Which partition holds the reversal

The ledger above puts the reversal in the 9 October arrival partition while keeping charge_date of 2 October on the row. That is option B from section 1 applied to one row, with the same consequence: a report filtered on charge_date must scan forward through later arrival partitions to see the correction. If the serving table is partitioned by charge_date, the reversal lands in the 2 October partition through the lookback rewrite, which works only when the correction arrives inside the window. A correction 3 months late falls outside every window, the strongest argument for keeping processed_date on every row: with it, 'in the 3 October report, this charge was 15.49' remains answerable after the row has moved.

Do

• Negate the exact row being corrected, by source_version, never the account's running total
• Carry row_sign and sum it for counts and averages
• Anti-join on the correction's version before inserting
• Keep processed_date on reversal rows so as-of reads still work

Avoid

• Update a closed partition's amount in place
• Use count(*) or max() on a ledger of reversals
• Apply a correction to a row that another correction already superseded
• Let a retry append a second reversal

About this lesson

This section is part of the Late-Arriving Data: Advanced 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.