DEV Community: Anton Gameza

A backfill can match row counts and totals while still being wrong

Anton Gameza — Thu, 22 Oct 2026 18:25:38 +0000

The backfill completed. Row counts match. The USD total matches. Two payment amounts are still wrong.

A count and a control total are useful checks, but different datasets can satisfy both. Here is a small counterexample you can run without a warehouse.

Three candidates, one summary

The expected payment rows contain 100, 200 and 300 minor units. Each row is identified by provider, payment and currency.

Now consider three candidate outputs:

Candidate	Amounts	Defect
Changed values	p1=100, p2=250, p3=250	Two amounts changed and cancel out in the total
Swapped identity	p1=100, p2=200, p4=300	p3 disappeared and an unrelated p4 appeared
Duplicate key	p1=100, p1=200, p3=300	The expected grain is violated

Every candidate has three rows and a USD total of 600. None equals the expected result.

Compare identities before values

The script below checks uniqueness before building a dictionary. Then it reports missing keys, extra keys and changed values on shared keys.

That ordering matters: converting duplicate keys straight into a dictionary would overwrite one row and hide a defect in the input.

Save this as backfill_compare_demo.py and run it with Python. It uses only the standard library.

from collections import defaultdict


def summary(rows):
    totals = defaultdict(int)
    for provider, payment, currency, amount in rows:
        totals[currency] += amount
    return len(rows), dict(totals)


def keyed(rows):
    result = {}
    for provider, payment, currency, amount in rows:
        key = (provider, payment, currency)
        if key in result:
            raise ValueError(f"duplicate key: {key}")
        result[key] = amount
    return result


def compare(expected, candidate):
    left, right = keyed(expected), keyed(candidate)
    return {
        "missing": sorted(left.keys() - right.keys()),
        "extra": sorted(right.keys() - left.keys()),
        "changed": [(key, left[key], right[key])
                    for key in sorted(left.keys() & right.keys())
                    if left[key] != right[key]],
    }


expected = [("A", "p1", "USD", 100), ("A", "p2", "USD", 200),
            ("A", "p3", "USD", 300)]
changed = [("A", "p1", "USD", 100), ("A", "p2", "USD", 250),
           ("A", "p3", "USD", 250)]
swapped = [("A", "p1", "USD", 100), ("A", "p2", "USD", 200),
           ("A", "p4", "USD", 300)]
duplicate = [("A", "p1", "USD", 100), ("A", "p1", "USD", 200),
             ("A", "p3", "USD", 300)]

for candidate in [changed, swapped, duplicate]:
    assert summary(candidate) == summary(expected) == (3, {"USD": 600})
assert compare(expected, changed) == {
    "missing": [], "extra": [],
    "changed": [(("A", "p2", "USD"), 200, 250),
                (("A", "p3", "USD"), 300, 250)],
}
assert compare(expected, swapped) == {
    "missing": [("A", "p3", "USD")],
    "extra": [("A", "p4", "USD")], "changed": [],
}
assert compare(expected, list(reversed(expected))) == {
    "missing": [], "extra": [], "changed": [],
}
try:
    compare(expected, duplicate)
except ValueError:
    pass
else:
    raise AssertionError("duplicate key accepted")
print("all candidate counts and totals:", summary(expected))
print("changed amounts:", compare(expected, changed))
print("swapped identity:", compare(expected, swapped))
print("duplicate rejection and order independence: passed")

The local run produced:

all candidate counts and totals: (3, {'USD': 600})
changed amounts: {'missing': [], 'extra': [], 'changed': [(('A', 'p2', 'USD'), 200, 250), (('A', 'p3', 'USD'), 300, 250)]}
swapped identity: {'missing': [('A', 'p3', 'USD')], 'extra': [('A', 'p4', 'USD')], 'changed': []}
duplicate rejection and order independence: passed

The example also reverses the expected rows and confirms that physical row order does not create a difference. The contract is about keyed payment state, not file ordering.

Choose an expected result that answers the right question

A backfill may intentionally change business results. Comparing it with yesterday’s production table and rejecting every changed value would prevent the correction you were trying to make.

Define the intended source snapshot, transformation rules and output scope first. The expected rows in this experiment are an independently specified fixture. In a real rebuild, the baseline might be a separately calculated canary result or an approved restatement, with expected changes recorded explicitly.

A difference report tells you what changed. It does not decide whether the change was intended. A fully matching output also does not prove correctness if both sides use the same flawed logic.

For that reason, keep at least one small case with expected rows written from the business rule, rather than generating every expectation with the transformation being tested.

Keep the comparison contract narrow

This script compares only an integer amount at the grain (provider, payment, currency). If the published output also includes status, effective date, allocation or classification, decide which of those fields belong in the payload comparison.

Totals are grouped by currency; summing USD and EUR minor units together would be a different error. The fixture happens to use one currency, but the summary function keeps currency separate.

The dictionaries fit in memory and assume well-formed tuples. This is a teaching example, not a distributed comparison engine. For large data, the same categories can be computed with validated unique keys and joins in the warehouse. Nulls, decimals, timestamps and allowed tolerances need explicit equality rules.

Validation comes before publication

I maintain a free fintech data engineering roadmap. The backfill orchestration lesson asks learners to start with a canary period, reserve daily capacity, and define stop conditions.

The atomic publication lesson separates candidate computation from exposing a result to consumers. The mechanism needs to match the platform’s actual guarantees; this Python comparison does not implement a publication switch.

A useful review artifact is a candidate version, its source/rule manifest, the key-level differences, the expected exceptions, and the decision to publish or stop. Keep the prior version available under the agreed rollback plan.

For your next test, add equal and opposite errors in different payment rows. If the only assertion is a matching grand total, that test should show exactly what the gate misses.

BETWEEN can double-count a fact at an SCD2 version boundary

Anton Gameza — Thu, 22 Oct 2026 18:15:31 +0000

A customer changes tier at midnight. A payment happens at exactly that instant. A historical join returns the payment twice: once under the old tier and once under the new one.

This can happen even when the history has no duplicate change records. An inclusive upper boundary is enough.

Put a fact on the boundary

Assume the following effective changes for customer c. They arrive in this order:

basic, effective January 1
premium, effective January 3
plus, effective January 2

The third change arrives late. Once all three changes are known, the valid-time history is:

Tier	Valid from, inclusive	Valid to, exclusive
basic	January 1	January 2
plus	January 2	January 3
premium	January 3	open

A fact at January 2 belongs to plus. A fact at January 3 belongs to premium.

SQLite’s BETWEEN documentation defines it using an inclusive lower and upper comparison. So joining a fact timestamp between valid_from and valid_to includes both adjoining intervals at their shared boundary.

For these half-open intervals, use:

f.at >= h.valid_from
AND (h.valid_to IS NULL OR f.at < h.valid_to)

The business contract chooses the interval convention. The SQL must implement that convention consistently.

A complete local experiment

This Python script creates a SQLite database in memory. It compares an inclusive join with a half-open join. The amounts are synthetic minor units in one currency.

Save it as temporal_join_demo.py and run it with Python.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE changes(customer TEXT, valid_from TEXT, tier TEXT);
CREATE TABLE facts(id TEXT, customer TEXT, at TEXT, amount_minor INTEGER);
INSERT INTO changes VALUES
 ('c', '2026-01-01T00:00:00Z', 'basic'),
 ('c', '2026-01-03T00:00:00Z', 'premium'),
 ('c', '2026-01-02T00:00:00Z', 'plus');
INSERT INTO facts VALUES
 ('f1', 'c', '2026-01-01T00:00:00Z', 100),
 ('f2', 'c', '2026-01-02T00:00:00Z', 200),
 ('f3', 'c', '2026-01-03T00:00:00Z', 300),
 ('f4', 'missing-customer', '2026-01-02T00:00:00Z', 400);
""")

history = """
WITH history AS (
 SELECT customer, tier, valid_from,
        LEAD(valid_from) OVER (PARTITION BY customer ORDER BY valid_from) AS valid_to
 FROM changes
)
"""
select = """
SELECT f.id, COALESCE(h.tier, 'UNKNOWN'), f.amount_minor
FROM facts AS f LEFT JOIN history AS h
 ON f.customer = h.customer AND %s
ORDER BY f.id, h.valid_from
"""
bad = db.execute(history + select % """
 f.at BETWEEN h.valid_from AND COALESCE(h.valid_to, '9999-12-31T23:59:59Z')
""").fetchall()
good = db.execute(history + select % """
 f.at >= h.valid_from AND (h.valid_to IS NULL OR f.at < h.valid_to)
""").fetchall()
assert good == [("f1", "basic", 100), ("f2", "plus", 200),
                ("f3", "premium", 300), ("f4", "UNKNOWN", 400)]
assert len(bad) == 6 and sum(row[2] for row in bad) == 1500
assert len(good) == 4 and sum(row[2] for row in good) == 1000
assert len({row[0] for row in good}) == 4
print("inclusive boundaries:", len(bad), "rows; total", sum(row[2] for row in bad))
print("half-open intervals:", len(good), "rows; total", sum(row[2] for row in good))
print("historical result:", good)

The local run produced:

inclusive boundaries: 6 rows; total 1500
half-open intervals: 4 rows; total 1000
historical result: [('f1', 'basic', 100), ('f2', 'plus', 200), ('f3', 'premium', 300), ('f4', 'UNKNOWN', 400)]

The inclusive join duplicates f2 and f3. Their amounts add another 200+300 to the output. A report over the joined rows reaches 1,500 even though the input contains only 1,000.

The corrected join keeps all four fact IDs exactly once and assigns the expected tier to each known customer fact.

Sort the history by effective time

The history CTE uses LEAD over valid_from within each customer. The next effective timestamp becomes the current interval’s end. SQLite documents this behavior in its window-function reference.

That is why the late January 2 change splits the earlier basic-to-premium history correctly in this fixture. Inserting history rows in arrival order and treating that order as effective time would assign the wrong tier even after fixing the boundary predicate.

This is a small batch reconstruction with one non-conflicting change per customer and effective instant. It does not implement an incremental dimension loader, conflict detection, or duplicate normalization. Those need explicit rules before LEAD is applied.

Missing dimensions must remain visible

The fourth fact references a customer missing from the dimension. The LEFT JOIN preserves it, and the output labels the tier UNKNOWN.

An INNER JOIN would drop its 400 minor units from the report. A right-side filter placed in WHERE can also eliminate unmatched facts after a LEFT JOIN, depending on the predicate. Keep the intended population explicit when reviewing the query.

There are two separate checks here:

Every input fact remains represented exactly once.
Every represented fact receives the correct historical classification.

A row-count assertion alone cannot establish the second. A total alone cannot establish either if errors cancel each other out. The script checks the full expected result as well as count, distinct IDs, and sum.

State the time model

Every timestamp in this script is a fixed-width UTC string ending in Z. Text ordering works for that fixture. Mixed offsets or inconsistent formats must be normalized to comparable instants first.

This query answers “which tier was valid when the fact happened, using the history we now have?” It does not answer “which tier did the system know about at that time?” Reconstructing historical knowledge requires an additional time/version model. A late effective change can alter the first answer without having been available to the original decision.

Extend the exercise

I maintain a free fintech data engineering roadmap. C02: late dimensions and a historical SQL join adds UTC normalization, exact-repeat handling, same-instant conflicts, and totals grouped by tier and currency.

The exercise asks for a temporal join, its query plan, and evidence that missing dimensions do not lose money. This article isolates the join-boundary failure; it is not a complete C02 submission.

A useful next test is a fact before the first known dimension version. Decide whether it should remain UNKNOWN, then verify that a convenience filter has not silently removed it.

A deleted CDC row still needs a version: a Python counterexample

Anton Gameza — Thu, 22 Oct 2026 18:11:23 +0000

A current-state projection removes a merchant record. Later, an older update for that key arrives and the record comes back.

The delete worked. The consumer forgot enough ordering information to accept stale state afterward.

Here is a small Python experiment that separates the visible value from the latest version applied to its key.

The source order and delivery order differ

Assume a single source namespace and a strictly ordered integer version per key. Higher versions describe newer source state. Payloads at a given version are immutable.

Source version	Operation	Value
1	upsert merchant-7	10
2	upsert merchant-7	20
3	delete merchant-7	absent

Now deliver versions in the order 1, 3, 2. This could represent reordering in a downstream pipeline; the example does not claim that a particular source connector reorders its own log.

A consumer that applies every arrival ends with merchant-7=20. The source’s newest state says the key is deleted.

Adding version checks only to visible rows still leaves a hole if deleting the row also discards the version. When version 2 arrives, the consumer finds no row and no evidence of version 3.

Keep a versioned deletion marker

The experiment stores the latest tuple for each key: version, operation, and value. Deletes keep ordering metadata while the visible projection excludes deleted keys.

An older version is stale.
An identical repeat at the same version is harmless.
A different payload at the same version is a conflict.
A higher version replaces the previous state, including a legitimate newer upsert after a delete.

The code uses plain dictionaries and has no external dependencies. Save it as cdc_delete_demo.py and run it with Python.

from itertools import permutations


def apply(latest, seq, key, op, value=None):
    # Teaching contract: comparable integer versions per key; immutable payloads.
    if op not in {"upsert", "delete"} or (op == "delete" and value is not None):
        raise ValueError("invalid operation")
    previous = latest.get(key)
    incoming = (seq, op, value)
    if previous is not None:
        if seq < previous[0]:
            return "stale"
        if seq == previous[0]:
            if incoming != previous:
                raise ValueError("conflicting payload at the same version")
            return "repeat"
    latest[key] = incoming
    return "applied"


def visible(latest):
    return {key: value for key, (_, op, value) in latest.items() if op == "upsert"}


changes = [(1, "merchant-7", "upsert", 10),
           (3, "merchant-7", "delete", None),
           (2, "merchant-7", "upsert", 20)]

naive = {}
for seq, key, op, value in changes:
    if op == "delete":
        naive.pop(key, None)
    else:
        naive[key] = value
assert naive == {"merchant-7": 20}

latest = {}
statuses = [apply(latest, *change) for change in changes]
assert statuses == ["applied", "applied", "stale"]
assert visible(latest) == {}
assert latest["merchant-7"] == (3, "delete", None)
assert apply(latest, 3, "merchant-7", "delete") == "repeat"
before = latest.copy()
try:
    apply(latest, 3, "merchant-7", "upsert", 999)
except ValueError:
    pass
else:
    raise AssertionError("same-version conflict accepted")
assert latest == before

for delivery_order in permutations(changes):
    trial = {}
    for change in delivery_order:
        apply(trial, *change)
    assert trial == {"merchant-7": (3, "delete", None)}

unknown = {}
apply(unknown, 3, "merchant-7", "delete")
assert apply(unknown, 2, "merchant-7", "upsert", 20) == "stale"
assert visible(unknown) == {}
apply(latest, 4, "merchant-7", "upsert", 30)
assert visible(latest) == {"merchant-7": 30}

print("arrival-order projection:", naive)
print("version-aware statuses:", statuses)
print("six delivery orders, repeat, conflict, unknown delete: passed")
print("newer upsert:", visible(latest))

The local run produced:

arrival-order projection: {'merchant-7': 20}
version-aware statuses: ['applied', 'applied', 'stale']
six delivery orders, repeat, conflict, unknown delete: passed
newer upsert: {'merchant-7': 30}

Notice that a delete for an unknown key still records its version. Dropping it as a no-op would let an older upsert recreate the key later.

The six permutations test the same three immutable changes under every delivery order. They all end at the version-3 deletion marker. That establishes the result for this fixture; it does not establish correctness for arbitrary source schemas or ordering rules.

This marker is not a Kafka tombstone

The dictionary’s deletion marker is local consumer state that retains a version. A Kafka tombstone has a different wire-level meaning. Debezium’s PostgreSQL documentation describes a delete event followed, by default, by a same-key record with a null value for log compaction. See the official delete-event documentation.

Do not assume a null Kafka value carries the version tuple used in this Python example. A real consumer needs to handle the connector’s delete envelope, key, metadata, configuration, and subsequent tombstone according to its actual contract.

The ordering contract does the work

The integer seq here is a teaching device. It must not be replaced casually with a wall-clock timestamp. Equal timestamps, separate source partitions, source resets, and key reuse can require additional ordering or identity information.

Likewise, the dictionary is not durable. If values and version metadata are stored separately, both must survive failure consistently. Otherwise a restart can recreate the same bug even when the in-memory implementation is correct.

Removing deletion markers also needs a replay boundary. If an old update can still arrive, removing the only evidence of a newer delete makes the key look unseen again. This example does not choose a safe retention duration, model source epochs, or handle concurrent writers.

A current-state delete also does not establish that a payment was refunded. Financial events and customer or merchant profile state need separate business rules.

Start with a finite exercise, then change one assumption

I maintain a free fintech data engineering roadmap. Its E14 CDC current projection exercise has a smaller contract: take a finite input, sort by seq, then apply upserts and deletes.

Sorting the complete finite input resolves that exercise’s delivery-order problem. The incremental version-aware consumer above is an extension, not a claim that E14 requires a streaming service.

The lesson on deletes and tombstones asks learners to distinguish current-state deletion from financial reversal and historical handling.

For a next counterexample, clear only the version metadata after processing version 3, then deliver version 2. Write down which recovery invariant the consumer lost before adding another feature.

An old event timestamp can still leak future information into training data

Anton Gameza — Thu, 22 Oct 2026 18:02:20 +0000

A payment happened at 11:45. Your system received it at 12:10. Could a decision made at noon have used it?

Filtering on the event timestamp alone says yes. Reconstructing what the system knew at noon says no.

That difference matters when building historical features. A backfilled event can be old in business time and new information to the system.

Two rows are enough to expose it

Assume one entity, one currency, unique immutable event IDs, and a decision at 12:00 UTC. The feature is the sum of amounts from the preceding hour.

ID	Amount, minor units	Event time	Available to the decision system
a	1,000	11:30	11:31
b	2,500	11:45	12:10

An event-time filter includes both rows and returns 3,500. The historically available sum is 1,000, contributed by a alone.

The second amount is valid data. It is simply unavailable for this decision. Cleaning duplicates or fixing its timestamp would not address the problem.

Make the cutoff explicit

This example uses a half-open event window: [11:00, 12:00). Event time exactly at the decision is excluded. Availability exactly at the decision is included. These are deliberate exercise rules; a real system needs a cutoff that matches its actual read semantics.

The additional predicate is:

WHERE event_time >= :window_start
  AND event_time < :decision_at
  AND available_at <= :decision_at

Here is the complete experiment. Save it as point_in_time_demo.py and run it with Python. It uses only the standard-library sqlite3 module and an in-memory database.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE events(id TEXT, amount_minor INTEGER, event_time TEXT, available_at TEXT)")
db.executemany("INSERT INTO events VALUES (?, ?, ?, ?)", [
    ("a", 1000, "2026-01-02T11:30:00Z", "2026-01-02T11:31:00Z"),
    ("b", 2500, "2026-01-02T11:45:00Z", "2026-01-02T12:10:00Z"),
])

start = "2026-01-02T11:00:00Z"
decision = "2026-01-02T12:00:00Z"


def feature_rows():
    return db.execute("""
        SELECT id, amount_minor FROM events
        WHERE event_time >= ? AND event_time < ? AND available_at <= ?
        ORDER BY id
    """, (start, decision, decision)).fetchall()


event_time_total = db.execute("""
    SELECT COALESCE(SUM(amount_minor), 0) FROM events
    WHERE event_time >= ? AND event_time < ?
""", (start, decision)).fetchone()[0]
rows = feature_rows()
available_total = sum(amount for _, amount in rows)
assert (event_time_total, available_total, rows) == (3500, 1000, [("a", 1000)])
print(f"event-time-only={event_time_total}; available-at-decision={available_total}")
print("contributing events:", rows)

# Boundaries are part of this teaching contract, not inferred from timestamp names.
db.executemany("INSERT INTO events VALUES (?, ?, ?, ?)", [
    ("start", 25, start, "2026-01-02T11:01:00Z"),
    ("known-boundary", 5, "2026-01-02T11:59:00Z", decision),
    ("at-decision", 99, decision, decision),
    ("too-old", 50, "2026-01-02T10:59:59Z", "2026-01-02T11:01:00Z"),
])
assert feature_rows() == [("a", 1000), ("known-boundary", 5), ("start", 25)]
assert sum(amount for _, amount in feature_rows()) == 1030
print("window and availability boundary checks: passed")

The output from the local run was:

event-time-only=3500; available-at-decision=1000
contributing events: [('a', 1000)]
window and availability boundary checks: passed

The extra assertions test the start of the window, a row becoming available exactly at the decision, an event exactly at the decision, and an event just before the window. They check contributing IDs as well as the total.

Define what “available” means before trusting the query

In this fixture, available_at means the information was usable by the component making the decision. A warehouse ingestion timestamp may be too early or too late to represent that. A message can arrive before enrichment finishes, before a materialization commits, or before a serving system can read it.

The query only enforces the meaning you give that column. It cannot recover historical availability from a table that overwrites it during a backfill.

For corrected records, the problem gets another dimension: which version was known then? This immutable-event example deliberately does not implement version history.

The text comparisons are also intentionally narrow. Every timestamp here uses the same fixed-width UTC format ending in Z. Lexicographic comparisons are not a general solution for mixed offsets or timestamp formats. Normalize instants before comparing them, and use appropriate timestamp types in a production database.

For multiple entities and currencies, filter or join on those keys too. Adding EUR and USD minor units would produce an invalid feature even if every time predicate were correct.

Labels have a separate availability cutoff

Features answer: what could be used at decision time?

Training labels answer: what was known when this dataset was assembled?

For example, a decision on January 2 may have a label that becomes known on January 25. A training snapshot as of January 20 cannot use that label. A label maturity requirement is separate again: an observation period may still be incomplete even when an early label exists.

Passing the feature-time check does not establish either label condition, and it does not replace a time-based train/validation/test split.

Turn the result into a reproducible exercise

I maintain a free fintech data engineering roadmap. C08: leakage-safe training data with delayed labels extends this example with entity/currency matching, capture-only events, normalized timestamp offsets, label maturity, label knowledge time, and temporal splits.

It returns contributing event IDs alongside each training row. Keep that provenance when testing: a plausible sum can still come from the wrong events.

The exercise builds a synthetic dataset, not a classifier or a production feature store. The repository runs its core Python and SQL exercises locally without paid cloud services.

A useful next counterexample is a correction received after the decision that replaces an earlier value. Write down which version should be visible before changing the query.

Parsing money in Python: reject bad precision before choosing how to round

Anton Gameza — Thu, 22 Oct 2026 13:43:04 +0000

A source sends the amount 12.345. Your destination stores integer cents. What should happen?

Rounding is one possible answer. Rejection is another. The important part is choosing the rule before converting the value.

This Python expression returns 12.34:

Decimal("12.345").quantize(Decimal("0.01"), rounding=ROUND_HALF_EVEN)

That is correct for the requested rounding operation. It is a bad validation rule if the source contract promises amounts with at most two decimal places. In that contract, the third decimal is something to investigate, not silently discard.

Separate three decisions

Before writing a parser, decide:

Which input spellings are allowed?
What scale and range does the field use?
Is excess precision rejected or rounded under an explicit business rule?

A Decimal constructor handles numeric representation. It does not define your entire input contract. For example, scientific notation can be a valid decimal number while still being forbidden in a particular source field.

A deliberately narrow parser

Here is one exercise contract:

Input must be a string containing ASCII digits.
An optional leading minus sign is allowed; a leading plus is not.
There are one to nine digits before the decimal point, with no extra leading zeros.
The fractional part, if present, contains one or two digits.
The field uses exactly 100 minor units per major unit.
Whitespace, separators, scientific notation, non-finite values, and floats are rejected.
Negative zero normalizes to integer zero.

These are teaching choices, not a universal money format. In particular, this function does not infer scale from a currency code. A different currency or upstream schema needs its own explicit scale and validation contract.

The parser uses string validation and integer arithmetic. Decimal rounding is shown separately so it cannot quietly turn an invalid source amount into an accepted one.

Save the complete script below as strict_amount_demo.py and run it with Python. It uses the standard library. I checked it on Python 3.14.3.

import re
from decimal import Decimal, ROUND_HALF_EVEN


def parse_cents(text):
    """One exercise contract: signed ASCII amounts, at most two decimals."""
    if not isinstance(text, str):
        raise ValueError("amount must arrive as text")
    match = re.fullmatch(r"(-?)(0|[1-9][0-9]{0,8})(?:\.([0-9]{1,2}))?", text)
    if match is None:
        raise ValueError("invalid amount format or range")
    sign, whole, fraction = match.groups()
    minor = int(whole) * 100 + int((fraction or "").ljust(2, "0"))
    return -minor if sign else minor


accepted = {
    "0": 0,
    "0.1": 10,
    "-0.01": -1,
    "12.30": 1230,
    "-0.00": 0,
    "999999999.99": 99999999999,
}
for raw, expected in accepted.items():
    assert parse_cents(raw) == expected

rejected = [
    "12.345", "1e2", "1,000.00", " 12.30", "12.30 ",
    "NaN", "Infinity", "+12.30", "01.00", "1000000000.00",
    "12.30\n", "١٢.٣٠", 12.3, None,
]
for raw in rejected:
    try:
        parse_cents(raw)
    except ValueError:
        pass
    else:
        raise AssertionError(f"accepted {raw!r}")

rounded = Decimal("12.345").quantize(Decimal("0.01"), rounding=ROUND_HALF_EVEN)
assert rounded == Decimal("12.34")
print(f"accepted cases: {len(accepted)}; rejected cases: {len(rejected)}")
print("explicit rounding of 12.345:", rounded)

Output:

accepted cases: 6; rejected cases: 14
explicit rounding of 12.345: 12.34

Why the small details matter

re.fullmatch makes the entire input follow the grammar. A trailing newline does not slip through. The explicit [0-9] character class also makes the ASCII restriction visible.

Right-padding the fraction distinguishes 0.1 from 0.01: the first becomes 10 cents, the second one cent. The sign is applied to the completed integer amount.

The nine-digit bound is part of this example's contract. It bounds the work done by the integer conversion, but it does not replace a request-size limit at the transport boundary. The returned integer also still needs to fit whatever storage or downstream field you choose.

If your application needs to display a signed zero or preserve the original spelling, retain that information separately; converting to integer minor units intentionally loses it.

When rounding is the right operation

Calculated values such as allocations or conversions may genuinely require rounding. Put that operation at a named boundary, choose the rounding mode explicitly, and define what happens to any remainder. Do not make the ingestion parser guess the policy.

Python's decimal documentation covers exact decimal representation, rounding modes, and contexts. In particular, constructing a Decimal from a float preserves that float's value; it does not recover an original decimal string that was already lost upstream.

Extend the exercise

I maintain a free fintech data engineering roadmap. Its E01 exact-money exercise asks you to convert strings using explicit currency scales and reject floats and excess precision. It has a different contract from the narrow parser above, so do not submit this function unchanged and assume it solves E01.

A useful next step is to write down your own source grammar, then add one accepted and one rejected boundary case before implementing it. For this example, changing the maximum whole-part length or permitting +12.30 should be a conscious contract change with a matching test.

Your SQLite backup restored. Does its checkpoint still match the data?

Anton Gameza — Thu, 22 Oct 2026 13:26:56 +0000

A backup can restore successfully and still leave your pipeline skipping data.

The awkward case is a checkpoint that says “batch 2 is done” while the rows from batch 2 never made it into the restored database. The next run trusts the checkpoint, skips the batch, and carries on with a hole.

Here is a small SQLite experiment that makes the boundary visible. It uses three ordered batches of synthetic events, all in one currency:

Batch	Event payloads	Total after commit
1	a: 100	100
2	a: 100, b: 50	150
3	b: 50, c: 25	175

The repeated IDs are deliberate. An exact repeat contributes nothing new. A different amount under an existing ID is a conflict.

Put progress in the same transaction as the data

The experiment fails after inserting rows for batch 2, just before updating the checkpoint. Both changes belong to the same transaction. After the exception, the database must still contain only event a and checkpoint 1.

Then it retries batch 2, commits batch 3, restores a backup taken after batch 1 into a separate database, and replays the source batches. The restored result must match the live result.

This is a single-writer teaching example. The batches are immutable, ordered, and complete; end_seq is a trusted batch boundary. It does not model gaps, repartitioning, corrections to already committed batches, or concurrent consumers. Those would need a different progress contract.

Run the experiment

Save this as checkpoint_demo.py and run it with Python. I checked the complete script on Python 3.14.3 using the standard-library sqlite3 module. It creates three separate in-memory databases and writes no database files.

import sqlite3


def state(db):
    seq = db.execute("SELECT seq FROM checkpoint").fetchone()[0]
    rows = db.execute("SELECT id, minor FROM events ORDER BY id").fetchall()
    return seq, rows, sum(amount for _, amount in rows)


def apply_batch(db, end_seq, events, fail=False):
    with db:
        checkpoint = db.execute("SELECT seq FROM checkpoint").fetchone()[0]
        if end_seq <= checkpoint:
            return
        for event_id, minor in events:
            previous = db.execute(
                "SELECT minor FROM events WHERE id = ?", (event_id,)
            ).fetchone()
            if previous is None:
                db.execute("INSERT INTO events VALUES (?, ?)", (event_id, minor))
            elif previous[0] != minor:
                raise ValueError("conflicting event")
        if fail:
            raise RuntimeError("injected failure before checkpoint")
        db.execute("UPDATE checkpoint SET seq = ?", (end_seq,))


live = sqlite3.connect(":memory:")
live.executescript("""
CREATE TABLE events(id TEXT PRIMARY KEY NOT NULL, minor INTEGER NOT NULL);
CREATE TABLE checkpoint(seq INTEGER NOT NULL);
INSERT INTO checkpoint VALUES (0);
""")
apply_batch(live, 1, [("a", 100)])
backup = sqlite3.connect(":memory:")
live.backup(backup)

try:
    apply_batch(live, 2, [("a", 100), ("b", 50)], fail=True)
except RuntimeError:
    pass
assert state(live) == (1, [("a", 100)], 100)
print("after failure:", state(live))
apply_batch(live, 2, [("a", 100), ("b", 50)])
apply_batch(live, 3, [("b", 50), ("c", 25)])

restored = sqlite3.connect(":memory:")
backup.backup(restored)
apply_batch(restored, 1, [("a", 100)])
apply_batch(restored, 2, [("a", 100), ("b", 50)])
apply_batch(restored, 3, [("b", 50), ("c", 25)])
assert state(live) == state(restored) == (3, [("a", 100), ("b", 50), ("c", 25)], 175)
print("live:", state(live))
print("restored:", state(restored))
before_conflict = state(restored)
try:
    apply_batch(restored, 4, [("d", 10), ("a", 999)])
except ValueError:
    pass
else:
    raise AssertionError("conflict was not rejected")
assert state(restored) == before_conflict
print("conflict rollback: passed")

Expected output:

after failure: (1, [('a', 100)], 100)
live: (3, [('a', 100), ('b', 50), ('c', 25)], 175)
restored: (3, [('a', 100), ('b', 50), ('c', 25)], 175)
conflict rollback: passed

What the assertions prove

The first assertion checks the failed attempt. The new b row must disappear, and progress must remain at batch 1. Checking only the final sum would miss that intermediate failure boundary.

The second compares checkpoint, event IDs, individual amounts, and total after restore and replay. A total alone is weak evidence: one missing event and one duplicate can cancel each other out.

The final check submits a batch containing a new d event followed by a conflicting version of a. Rejecting the conflict must also roll back d. Otherwise a rejected batch still changes the database.

The function skips a batch at or below the committed checkpoint. That is safe only under the immutable ordered-input contract above; it deliberately does not revalidate old payloads. The conflict test uses a new batch boundary so the payload is actually examined.

A few SQLite details worth making explicit

The script uses sqlite3's default legacy transaction mode. In that mode, the writes inside with db are committed on success and rolled back when an exception escapes. Do not copy this transaction wrapper into a connection configured with autocommit=True and expect the same behavior. The Python transaction documentation explains the modes.

The connection backup API copies one SQLite database into another. These calls happen after committed work, outside an active write transaction.

An in-memory backup is useful here because the experiment is about state consistency. It is not durable disaster recovery. A production restore drill also needs persistent backup storage, retention, encryption and access controls, recovery-time measurements, and a check that the source history needed for replay still exists.

This test also uses an injected Python exception, not a killed process or a power failure. It checks rollback behavior at a chosen boundary; it does not establish every crash-durability property of the storage stack.

Extend the exercise

I maintain a free fintech data engineering roadmap. C06: backup restore with an atomic checkpoint turns this idea into a scenario lab with a restoration trace, injected failure, replay, and conflict handling.

If you try it, add a failure case before reading the reference solution. A useful starting point is moving the checkpoint update into a helper that commits independently, then finding a test that exposes the broken transaction boundary.

The full repository also includes SQL and Python exercises using synthetic payment data.

A restore drill should answer more than “did the file open?” It should show which source position the restored data represents and whether replay can safely carry it forward.

Replaying payment events: test the second run, not just the first

Anton Gameza — Thu, 22 Oct 2026 13:22:39 +0000

A payment ingestion job can pass its first-run test and still double the balance on retry. The second run is where the ingestion contract becomes visible.

Consider one synthetic event: evt_1 records a capture of 100 USD minor units. Deliver it twice. The warehouse should retain one event and one monetary effect. Two separate captures of the same amount should still produce two effects.

Use event identity, not amount

An amount is not an idempotency key. Neither is (payment_id, amount): one payment can have several legitimate captures of the same amount.

A useful teaching model stores a stable source event ID alongside its payload:

CREATE TABLE payment_events (
    source TEXT NOT NULL,
    event_id TEXT NOT NULL,
    payment_id TEXT NOT NULL,
    currency TEXT NOT NULL,
    amount_minor INTEGER NOT NULL,
    PRIMARY KEY (source, event_id)
);

The source is part of the key because identifiers may only be unique within a provider. The model still needs an explicit rule for conflicting deliveries: the same key arriving with a different amount is an exception to investigate, not an ordinary duplicate.

Three tests that tell different stories

First delivery: ingest six distinct events and assert six accepted rows.
Exact replay: ingest the identical batch again and assert zero new rows and unchanged monetary totals.
Conflicting replay: change a payload while retaining its source/event key and assert the documented conflict behavior. Do not silently treat it as equivalent.

Also test two different event IDs with equal amounts. Both must survive. This catches accidental deduplication by value.

Keep the monetary effect atomic

A uniqueness constraint only protects the event table. If a job inserts an event, commits, and then separately updates a balance, a crash between those steps can lose the effect. Reverse that order and a retry can apply it twice.

For a local relational exercise, store the accepted event and its derived journal entries in one transaction. Alternatively, derive the report from the durable event records. External side effects need their own delivery and idempotency design; a database constraint alone does not provide end-to-end exactly-once processing.

Try a local payment lab

I maintain an open-source fintech data engineering roadmap with synthetic payment examples. Its quickstart intentionally shows six inserted events followed by zero inserted on replay and six duplicates. It also includes reconciliation exceptions, because replay safety does not prove the amounts agree with the processor.

With Git and Python 3.12+ available:

git clone https://github.com/antony502189-max/fintech-data-engineering-roadmap.git
cd fintech-data-engineering-roadmap
python -m fintech_lab.quickstart

The quickstart runs in memory using the Python standard library. No paid cloud or API key is required. The data and accounting model are simplified teaching examples.

Repository and learning path

For your next ingestion review, ask one concrete question: what changes after delivering the same batch twice?

Prepared with AI assistance.

Free SQL practice: why SUM(DISTINCT) doesn't fix inflated payment totals

Anton Gameza — Wed, 21 Oct 2026 16:28:56 +0000

Free SQL practice, with the full data and solution below. No account, paid course, cloud service, or API key is needed. This is a SQLite exercise using synthetic payment data, with a Python standard-library runner if you do not have the SQLite CLI.

Two distinct capture events are each worth 1000 USD cents. A report should show 2000 cents. After a join, one query reports 4000; adding DISTINCT reports 1000. Both are wrong.

I maintain a free, MIT-licensed fintech data engineering roadmap. This article works through the class of bug explored in its C07 scenario: independent detail tables multiply one another before aggregation.

First, define the report's grain

We want one row for each payment and currency with at least one capture:

gross_minor: sum of capture amounts.
net_minor: captures minus fees in the same currency.
attempt_count: attempts at the payment grain, shared across that payment's currencies.

For this synthetic exercise, a missing fee match means zero fees, and a missing attempt match means zero attempts. That is an explicit fixture contract, not a general rule that unknown monetary data should become zero. Fee-only payments are outside the report's population.

Set up the data

Save this block as setup.sql. Run it once in a fresh database.

CREATE TABLE captures (
  id TEXT PRIMARY KEY, payment TEXT, currency TEXT, amount INTEGER
);
CREATE TABLE fees (
  id TEXT PRIMARY KEY, payment TEXT, currency TEXT, amount INTEGER
);
CREATE TABLE attempts (id TEXT PRIMARY KEY, payment TEXT);

INSERT INTO captures VALUES
  ('c1', 'p1', 'USD', 1000),
  ('c2', 'p1', 'USD', 1000),
  ('c3', 'p1', 'EUR', 500),
  ('c4', 'p2', 'USD', 700);
INSERT INTO fees VALUES
  ('f1', 'p1', 'USD', 50),
  ('f2', 'p1', 'EUR', 20);
INSERT INTO attempts VALUES ('a1', 'p1'), ('a2', 'p1');

c1 and c2 have different event IDs and the same amount. Neither is a duplicate event. p1 has both USD and EUR captures. p2 has a capture but no matching fee or attempt.

Before running anything, calculate p1 / USD by hand: 1000 + 1000 = 2000 gross; subtract the 50-cent fee to get 1950 net; there are two attempts.

The query that looks plausible

SELECT c.payment, c.currency,
       SUM(c.amount) AS gross_minor,
       SUM(c.amount) - COALESCE(SUM(f.amount), 0) AS net_minor,
       SUM(DISTINCT c.amount) AS distinct_amount_shortcut,
       COUNT(a.id) AS attempt_count
FROM captures AS c
LEFT JOIN fees AS f
  ON c.payment = f.payment AND c.currency = f.currency
LEFT JOIN attempts AS a ON c.payment = a.payment
GROUP BY c.payment, c.currency
ORDER BY c.payment, c.currency;

For p1 / USD, its output is:

Field	Wrong output	Expected
gross_minor	4000	2000
net_minor	3800	1950
distinct_amount_shortcut	1000	2000
attempt_count	4	2

There are two captures, one USD fee, and two attempts. Their joins create four rows. Each capture appears twice; the same fee appears four times. Summing those joined rows inflates gross and fee totals independently.

SUM(DISTINCT c.amount) removes repeated values. The two valid 1000-cent captures collapse into a single value, so it returns 1000. It does not deduplicate by event ID. This follows SQLite's aggregate DISTINCT behavior.

COUNT(DISTINCT a.id) could repair this particular attempt count, but it would not repair the monetary sums. The report needs a consistent grain for each independent measure.

Aggregate each input before joining

Save this corrected query as report.sql.

WITH capture_totals AS (
  SELECT payment, currency, SUM(amount) AS gross_minor
  FROM captures
  GROUP BY payment, currency
), fee_totals AS (
  SELECT payment, currency, SUM(amount) AS fee_minor
  FROM fees
  GROUP BY payment, currency
), attempt_totals AS (
  SELECT payment, COUNT(*) AS attempt_count
  FROM attempts
  GROUP BY payment
)
SELECT c.payment, c.currency, c.gross_minor,
       c.gross_minor - COALESCE(f.fee_minor, 0) AS net_minor,
       COALESCE(a.attempt_count, 0) AS attempt_count
FROM capture_totals AS c
LEFT JOIN fee_totals AS f
  ON c.payment = f.payment AND c.currency = f.currency
LEFT JOIN attempt_totals AS a ON c.payment = a.payment
ORDER BY c.payment, c.currency;

The independently specified expected output, also checked by executing the query in SQLite, is:

payment	currency	gross_minor	net_minor	attempt_count
p1	EUR	500	480	2
p1	USD	2000	1950	2
p2	USD	700	700	0

Each capture and fee CTE has one row per payment/currency. The attempts CTE has one row per payment. The final joins therefore do not multiply these input aggregates.

LEFT JOIN retains p2. Joining fees on both payment and currency prevents an EUR fee from reducing a USD total. SQLite documents the relevant FROM and JOIN processing.

The two attempts appear on both p1 currency rows because the contract measures attempts per payment. Do not sum that column across currencies and report four attempts. Measures with different grains need different aggregation rules.

Run it with Python, without extra packages

If Python includes sqlite3, save the setup and corrected query under the filenames above, then save this as run_report.py in the same directory:

import sqlite3
from pathlib import Path

with sqlite3.connect(":memory:") as db:
    db.executescript(Path("setup.sql").read_text(encoding="utf-8"))
    query = Path("report.sql").read_text(encoding="utf-8")
    for row in db.execute(query):
        print(row)

Run python run_report.py, or py -3 run_report.py on Windows if needed. The in-memory database starts clean each time.

Try breaking your fix

Add a second USD fee with a different ID but the same 50-cent amount. The correct p1 / USD result should become 2000 gross and 1900 net, with two attempts. A DISTINCT-on-fee-amount shortcut would fail that check too.

Then add a third attempt. Gross and net should remain 2000 and 1900; only attempt_count should become three. These changes test that unrelated detail rows cannot change the monetary total.

Pre-aggregation fixes fan-out; it does not establish that the source events are correct or that settlement agrees. Duplicate delivery and conflicting event identities need separate ingestion checks.

Continue with the free practice repository

The C07 task, hints, cases, and reference extend this into a runnable repair exercise. After cloning the repository:

python challenges/run.py init C07
python challenges/run.py grade C07

The starter intentionally contains a defective query. Edit work/challenges/C07.py, then grade it again. The full repository has 100 English lessons, 40 foundation exercises, and 8 scenario labs, freely available on GitHub. Core practice uses Python 3.12+ and SQLite without paid infrastructure.

Disclosure: I maintain the linked repository. Its first edition and this article were prepared with AI assistance using Codex. All data here is synthetic, and these are teaching examples.

Which extra case would you use to catch a report that accidentally multiplies financial amounts?

Free fintech data engineering lab: find a missing dollar with Python + SQLite

Anton Gameza — Wed, 21 Oct 2026 16:07:12 +0000

Free and open source: 100 English lessons, 40 foundation exercises, and 8 scenario labs for fintech data engineering. The repository's lessons and core practice are available without payment or a signup wall. Clone it and start locally with Python 3.12+ and SQLite: no paid cloud account or API key is needed.

I maintain this learning project. This walkthrough uses six synthetic payment events to show why a balanced journal can still disagree with an external settlement.

Three checks that answer different questions

Idempotency: if a delivery is repeated, do we count the same business event twice?

Journal balance: do the signed entries for each event and currency add up to zero?

Reconciliation: does our internal expected amount agree with an independently supplied settlement record?

None of these checks replaces the other two. A balanced pair of entries can faithfully record an incorrect amount. A replay-safe pipeline can faithfully reproduce that incorrect amount every time.

Run the experiment

Use Python 3.12 or later with SQLite support. From a terminal:

git clone https://github.com/antony502189-max/fintech-data-engineering-roadmap.git
cd fintech-data-engineering-roadmap
python -m fintech_lab.quickstart

On Windows, use py -3 instead of python if necessary.

Every run starts with a fresh in-memory SQLite database and writes no files. The first delivery inserts six events; repeating the delivery inserts zero and recognizes six duplicates. The journal check passes.

Reconciliation still produces two exceptions:

Payment	Currency	Expected minor units	External minor units	External minus expected	Result
p1	USD	7700	7700	0	matched
p2	EUR	4900	4900	0	matched
p3	USD	2500	2400	-100	amount mismatch
p9	USD	unknown	500	unknown	unexpected external payment

Explain the dollar before fixing the code

For p1, capture minus fee minus refund is:

10000 - 300 - 2000 = 7700 USD minor units

For p3, the internal record expects $25.00, but the external settlement reports $24.00. The difference is negative 100 USD minor units: a $1.00 shortfall.

For p9, there is no corresponding internal payment. Its expected amount is unknown, so the difference is unknown too. Treating that missing value as zero would hide the distinction between a known zero balance and a payment we cannot explain.

USD and EUR are kept separate throughout. Adding them together would not produce a meaningful monetary total.

Change one input and predict the result

In the repository's fintech_lab/synthetic.py, find p3 inside settlement() and change its net_minor from 2400 to 2500.

Run the quickstart again. p3 should now match. p9 should still be unexpected. Repairing one amount does not explain an unrelated external payment.

Restore 2400 afterward: the repository's tests expect the original deliberate defect.

You can also keep the full database and report for inspection:

python -m fintech_lab --out work/article-demo

That command writes warehouse.db and report.json and includes historical customer tiers and late-event feature examples. Use a new output directory for a clean run: unlike the in-memory quickstart, this version retains its database.

Where to go next

If the example is useful, the repository includes 100 English lessons, 40 foundation exercises, and 8 scenario labs. C07 is a SQL bug hunt about join fan-out; C06 covers backup recovery; C08 covers feature availability and delayed labels.

Start with the first-win walkthrough, or browse the repository.

The goal is to produce an explanation backed by code and checks, rather than only read a roadmap. If this helps you learn or teach, a voluntary GitHub star is appreciated. A reproducible edge case is even more useful feedback.

Scope and disclosure: I maintain the linked project. Its first edition and this article were prepared with AI assistance using Codex. The data is synthetic, and the signed journal is a simplified teaching model, not a production banking system. The repository documents its provenance and limitations.

Which reconciliation edge case would you add next: a partial refund, an unmatched fee, or a late settlement adjustment?