DEV Community: Anton Gameza

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?