Data Engineering for FinTech: Reconciliation, Audit Trails

fintech data engineering is the single biggest category shift a data engineer makes when moving from consumer analytics into regulated payments — and the one most engineers underestimate on their first fintech role because they treat "money data" as just another schema with a Decimal column. In fintech, every row is evidence. Every table has a legally-mandated retention. Every write is watched by an auditor who will ask, three years from now, exactly which pipeline run produced the number that landed in your 10-Q.

This guide is the senior-DE playbook you wished existed the first time a fintech interviewer asked "walk me through how you would build a fintech reconciliation engine between our internal ledger and Visa's counterparty file" or "how do you keep a payments pipeline exactly-once when a webhook retries a POST /transfers five times?" It walks through why fintech data pipelines differ in 2026 (regulatory pressure from PSD3, SOX 404, PCI-DSS v4, MiCA, DORA; sub-cent DECIMAL(38,18) precision; T+0 reconciliation SLAs; drop-dead reg-report windows), the three-way reconciliation core (source system ↔ ledger ↔ counterparty, break tables, tolerance windows, FIFO matching, versioned FX snapshots), immutable audit trail data engineering (append-only tables, SYSTEM_VERSIONED temporal history, SHA-256 hash chains, S3 Object Lock + Glacier Vault Lock WORM, 7-year retention), the exactly-once contract for money (Idempotency-Key handlers, the transactional outbox pattern, sagas vs 2PC, dual-write bugs), and the SOX-ready observability layer (OpenLineage column-level lineage, Great Expectations data-quality SLAs, segregation-of-duties evidence bundles). Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on joins problems → for the recon self-join patterns, and sharpen the audit-trail axis with the slowly-changing-data drills →.

---

On this page

• Why FinTech DE differs in 2026
• Reconciliation pipeline patterns
• Immutable audit trails
• Idempotent + exactly-once for money
• Regulatory reporting + observability
• Cheat sheet — fintech DE recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why FinTech DE differs in 2026

FinTech data engineering is bounded by regulators, not by product managers — the pipeline design is a compliance artifact first, an analytics artifact second

The one-sentence invariant: in fintech data engineering, every table has a retention floor, every row is legal evidence, every write is watched, and every calculation is auditable to the source event within seconds. Every other consequence — the DECIMAL(38,18) columns, the append-only tables, the idempotency keys, the SoD approval matrix — follows from that one structural difference. Once you internalise "the auditor is always looking over your shoulder," the entire data engineering finance interview surface collapses to a sequence of deductions from that one constraint.

The regulatory pressure map — what actually forces the design.

• PSD3 (EU, effective 2026) — the successor to PSD2. Adds fraud liability shifts, mandates open-banking APIs with tighter SLAs, and requires transaction-level auditability for every payment initiation. Your pipeline must be able to reconstruct the exact state of any transaction at any past timestamp.
• SOX 404 (US, since 2004, tightened continuously) — segregation of duties, change management, evidence retention. The DE consequence: your pipeline needs a maker-checker-approver flow, immutable job logs, and lineage-per-run that survives a 5-year auditor request.
• PCI-DSS v4.0 (2024) — card-data storage rules. The DE consequence: no PAN in your warehouse, tokenisation at ingestion, encryption everywhere, quarterly key rotation, and a data-flow diagram in your compliance binder.
• MiCA (EU crypto, 2024–2026) — every crypto transaction gets a travel-rule record and a reg-reporting window. Your pipeline must land the crypto data into a reg-report shape within hours, not days.
• DORA (EU digital operational resilience, 2025) — mandates a formal incident-recovery SLA. If the recon pipeline is down for more than 2 hours, you file an incident with the regulator.
• Local regs — RBI in India, MAS in Singapore, FCA in the UK — each add their own reporting cadence and retention floors on top of the international baseline.

Sub-cent precision — why Decimal is non-negotiable.

• Float is banned. float64 cannot represent 0.1 + 0.2 exactly (it gives 0.30000000000000004). One tenth of a cent, multiplied over a billion transactions, is a real loss on the P&L. Every fintech reg audit finds at least one team that used DOUBLE and had to restate.
• Preferred column types. Postgres: NUMERIC(38, 18) (up to 38 total digits, 18 after the decimal — enough for USD-to-satoshi FX conversion). BigQuery: BIGNUMERIC. Snowflake: NUMBER(38, 9) for USD, NUMBER(38, 18) for crypto. Java: BigDecimal with an explicit MathContext and rounding mode (usually HALF_EVEN — "banker's rounding" — because it minimises cumulative bias on large batches).
• Currency-aware types. Never store an amount without its currency code. 500 USD ≠ 500 JPY ≠ 500 sats. The best schemas store (amount_minor_units, currency_code, decimal_places) as a triple so downstream code can format without guessing.

Immutable audit trails — the second non-negotiable.

• Append-only, no UPDATE, no DELETE. Every state change is a new row with a monotonically increasing sequence number. The "current" value is a materialised view over the latest row per aggregate.
• Retention floor — 7 years for US SOX evidence, up to 10 for some MiFID II records, 5 for MiCA, 3 for local retail-banking laws. Set the floor once, propagate it into S3 Object Lock retention policies.
• Retention ceiling — GDPR. For EU customers, you may need to forget PII after the customer closes their account. Solved by pseudonymisation at ingestion — the audit trail keeps the pseudonym, the PII table keeps the mapping (and gets purged on request).

T+1 → T+0 SLAs — recon is real-time, not overnight.

• T+1 was the standard until ~2020 — the ledger reconciled the previous business day's transactions overnight. If a break was found at 06:00, it was investigated during business hours.
• T+0 is the 2026 standard for card, faster-payments, and open-banking rails. Recon runs continuously, breaks appear on a dashboard within seconds, and the ops team investigates in real time. The DE consequence: streaming CDC out of the ledger, Kafka topics for the counterparty file arrivals, and a match engine that runs on every input arrival — not once at midnight.

Reg-report drop-dead dates — you cannot miss the 09:00 filing.

• Regulatory filings have fixed deadlines — daily positions, monthly capital reports, quarterly stress-test exposures, annual audited financials. Missing a filing is a monetary fine plus a written finding from the regulator, and repeated misses erode your banking licence.
• The DE consequence — every reg-report pipeline needs an SLO with alerting well before the deadline, a manual-override runbook for degraded data, and a pre-approved "restate later" process so the filing still goes out on time.

What interviewers listen for.

• Do you say "the auditor is the true customer of this pipeline" in the first sentence? — senior signal.
• Do you name-drop DECIMAL(38, 18) and HALF_EVEN rounding unprompted? — required answer for money handling.
• Do you push back on "just add an UPDATED column" with "audit trails are append-only, we materialise current from history"? — senior signal.
• Do you mention retention-floor SLA + Object Lock + evidence bundles when the question is "how would you keep an audit trail"? — senior signal.

Worked example — Float vs Decimal on a fee calculation

Detailed explanation. A classic interview question — "you multiply a transaction amount by a fee rate. Show why float breaks, and show the fix." The naive Python float answer produces a rounding drift that, at scale, becomes a real revenue leak.

Question. Given a transaction of $19.99 and a fee rate of 2.9% + $0.30 (a standard card-processing fee), compute the fee and the net-to-merchant amount using both float and Decimal. Show the drift and explain the fix.

Input.

field	value
gross_amount	19.99
percent_fee	0.029
flat_fee	0.30
expected_fee (banker's rounded)	0.88
expected_net	19.11

Code.

from decimal import Decimal, ROUND_HALF_EVEN, getcontext

# BROKEN — float arithmetic
gross_f = 19.99
fee_f = gross_f * 0.029 + 0.30
net_f = gross_f - fee_f
print(f"float fee  = {fee_f!r}")   # 0.8797100000000001
print(f"float net  = {net_f!r}")   # 19.1102899999999999

# FIX — Decimal with explicit rounding mode
getcontext().prec = 38
gross_d = Decimal("19.99")
fee_d = (gross_d * Decimal("0.029") + Decimal("0.30")).quantize(
    Decimal("0.01"), rounding=ROUND_HALF_EVEN
)
net_d = (gross_d - fee_d).quantize(
    Decimal("0.01"), rounding=ROUND_HALF_EVEN
)
print(f"decimal fee = {fee_d}")    # 0.88
print(f"decimal net = {net_d}")    # 19.11

Step-by-step explanation.

1. The float multiplication 19.99 * 0.029 gives 0.57971 in exact decimal arithmetic, but the IEEE-754 binary representation of 19.99 and 0.029 are both non-terminating, so the product accumulates a small binary error.
2. Adding 0.30 (also non-terminating in binary) compounds the error. The final float fee comes out as 0.8797100000000001, which would round to 0.88 but which cannot be persisted as-is to a NUMERIC(10,2) column — the driver would either truncate or throw.
3. Decimal("19.99") — critically, constructed from a string not from a float — represents the exact value. The precision is set once via getcontext().prec = 38, so intermediate results carry the full 38 digits.
4. .quantize(Decimal("0.01"), rounding=ROUND_HALF_EVEN) snaps the result to two decimal places with banker's rounding — the required mode for most financial contexts because it does not bias cumulative sums.
5. The net amount is computed from the rounded fee, not the raw fee — this is the ledger convention: every stored amount is a rounded amount, no half-cents in the database.

Output.

approach	fee	net	audit-safe?
float	0.8797100000000001	19.1102899999999999	no
Decimal + HALF_EVEN	0.88	19.11	yes

Rule of thumb. Never construct a Decimal from a float. Always construct from a string or from an integer minor-unit representation. Set the rounding mode explicitly at the quantize call; do not rely on the default.

Worked example — Retention SLA on an audit table

Detailed explanation. An auditor asks: "show me the ledger balance for customer c42 on 2023-11-14 at 09:00:00 UTC." Your production database only keeps the current balance. The audit table you designed years ago must let you answer this in one query. This is why SCD Type 2 (or SYSTEM_VERSIONED) is a default for every fintech balance table.

Question. Design a customer_balance_history table that lets you answer "balance as of any past timestamp." Show the schema and the point-in-time query.

Input — synthetic history for c42.

customer_id	balance	changed_at
c42	100.00	2023-01-01 00:00:00
c42	250.00	2023-06-15 10:00:00
c42	175.00	2024-02-01 12:00:00

Code.

-- SCD Type 2 schema with valid_from / valid_to
CREATE TABLE customer_balance_history (
    customer_id       TEXT           NOT NULL,
    balance           NUMERIC(38, 18) NOT NULL,
    valid_from        TIMESTAMPTZ    NOT NULL,
    valid_to          TIMESTAMPTZ    NOT NULL DEFAULT 'infinity',
    change_reason     TEXT           NOT NULL,
    changed_by        TEXT           NOT NULL,
    PRIMARY KEY (customer_id, valid_from)
);

-- Point-in-time query — as-of 2023-11-14 09:00
SELECT customer_id, balance, change_reason
FROM   customer_balance_history
WHERE  customer_id = 'c42'
  AND  valid_from <= TIMESTAMPTZ '2023-11-14 09:00:00+00'
  AND  valid_to  >  TIMESTAMPTZ '2023-11-14 09:00:00+00';

Step-by-step explanation.

1. The table has no UPDATE semantics — every balance change appends a new row with valid_from = now() and closes the previous open row by setting its valid_to to the same timestamp.
2. valid_to defaults to infinity on insert; a small trigger (or an application-level pattern) closes the previous row atomically inside the same transaction to guarantee no overlap and no gap.
3. The as-of query filters for the single row whose [valid_from, valid_to) half-open interval contains the query timestamp. The <= valid_from and > valid_to predicates guarantee exactly one row per customer at any timestamp.
4. Storage-wise, the table grows monotonically — every change is a row. For a 5-year-retention table with millions of customers and a few changes each, the table is still small enough to partition by year and query with a covering index.
5. This is the SCD Type 2 pattern applied to the balance dimension — it is the same pattern Snowflake calls "temporal tables" and SQL Server calls SYSTEM_VERSIONED; the mechanics are identical.

Output.

customer_id	balance	change_reason
c42	250.00	payment_received_o38

Rule of thumb. Any table representing money-state (balance, position, exposure) needs an SCD Type 2 history from day one. The one-time setup cost is measured in hours; the retrofit cost after an audit finds the gap is measured in months.

Worked example — Detecting a Float-drift bug in production

Detailed explanation. A reconciliation job flags an ever-growing break between the merchant-payout total and the sum of transaction net-amounts. The root cause is a single float column added last year to the payout aggregator by a well-meaning engineer. This is one of the most common payments data engineering incidents.

Question. Given a merchant payout of $10,000.00 and 30,000 small transactions summing to $10,000.87 in the ledger but $10,001.03 in the payout aggregator, show how to detect and diagnose the float drift.

Input.

Source	Sum	Diff from ledger
Ledger (Decimal)	10000.87	0.00
Payout aggregator (float64)	10001.03	+0.16

Code.

-- 1) Detection query — join the aggregator to the ledger sum
SELECT p.merchant_id,
       p.payout_amount           AS aggregator_sum,
       COALESCE(l.txn_sum, 0)    AS ledger_sum,
       p.payout_amount - l.txn_sum AS drift
FROM   payouts p
JOIN   (SELECT merchant_id, SUM(net_amount) AS txn_sum
        FROM   ledger_transactions
        WHERE  date = CURRENT_DATE - 1
        GROUP  BY merchant_id) l
       USING (merchant_id)
WHERE  ABS(p.payout_amount - l.txn_sum) > 0.005;

-- 2) Diagnosis — reproduce the drift with the same fee formula
WITH sample AS (
  SELECT amount FROM ledger_transactions WHERE merchant_id = 'm1' LIMIT 30000
)
SELECT SUM(amount)::NUMERIC(38,18) AS decimal_sum,
       SUM(amount)::DOUBLE PRECISION AS float_sum,
       SUM(amount)::DOUBLE PRECISION - SUM(amount)::NUMERIC(38,18) AS drift
FROM   sample;

Step-by-step explanation.

1. The detection query joins the payout row against the ledger sum for the same day and flags any merchant where the drift exceeds half a cent. The ABS(...) > 0.005 guard filters ordinary rounding noise from real drift.
2. The diagnosis query re-runs the same sum in both NUMERIC(38,18) and DOUBLE PRECISION for a controlled sample. If the drift reappears in the sample, the aggregator is the source of the bug.
3. The fix is a data-type change on the aggregator column: ALTER TABLE payouts ALTER COLUMN payout_amount TYPE NUMERIC(38, 18) USING payout_amount::NUMERIC(38, 18), plus a backfill to recompute the affected payouts from the ledger.
4. The fix is not complete without a CHECK constraint (CHECK (payout_amount = ROUND(payout_amount, 2))) or an inbound-schema contract to prevent a future engineer from re-introducing the same bug.
5. In post-mortem, the finding is filed as a "data-type violation" in the compliance log — the auditor will ask about it at the next review, and you will want the fix, the backfill, and the guardrail already documented.

Output.

merchant_id	aggregator_sum	ledger_sum	drift
m1	10001.03	10000.87	+0.16

Rule of thumb. Add a nightly recon between every aggregate you compute and its ledger truth source. The drift alert is your canary — if it fires, you have a type-safety bug somewhere in your DAG, and finding it before the auditor does is free.

FinTech DE interview question on why regulated pipelines differ

A senior interviewer might open with: "You are joining as a senior data engineer at a payments company. Walk me through the top three things you would audit about the existing pipelines in your first two weeks, and why each one matters more in fintech than in a normal e-commerce shop."

Solution Using the compliance-first audit checklist

First-two-weeks compliance audit — fintech DE
=============================================

1. Column-type audit
   - Grep every schema for FLOAT / DOUBLE on any amount column.
   - Grep for TIMESTAMP WITHOUT TIME ZONE on any event-time column.
   - Confirm currency column exists next to every amount column.
   - Findings → issue tracker; propose migration windows.

2. Audit-trail coverage audit
   - For each balance / position / exposure table, is there a history table?
   - For each write, is there a change-reason and a changed-by column?
   - Confirm retention SLA is written down (7y for SOX evidence).
   - Findings → gap-remediation plan.

3. Idempotency + retry audit
   - For each payment-facing endpoint, is there an Idempotency-Key header?
   - Is the key-store sized and pruned (24h TTL is typical)?
   - Are retries safe on the ingestion side (Kafka consumer offsets committed
     after successful processing, not before)?
   - Findings → dedup guardrail plan.

Step-by-step trace.

Week	Focus	Deliverable
1	Column-type audit (float, timezone, currency)	schema-grep report
1	Audit-trail coverage (history tables, retention)	gap list + owners
2	Idempotency + retry (keys, TTL, offset commits)	dedup plan
2	Present findings to eng leadership + compliance	signed action plan

The output is a signed remediation plan that becomes the first-year DE roadmap. The auditor at year-end will ask "what did you find in your first two weeks, and what did you fix?" — this is the answer.

Output:

Finding class	Why it matters more in fintech	Typical remediation
Float on amount	0.1-cent drift accumulates; audit finding	ALTER TABLE + backfill
Missing timezone	UTC/local ambiguity breaks recon	make TIMESTAMPTZ + parse hint
Missing history	Auditor cannot query as-of past date	add SCD Type 2 history table
Missing Idempotency-Key	Retries cause double-charges	add key store + 24h TTL
Offset committed before success	Kafka replay loses records	commit after write + outbox

Why this works — concept by concept:

• Compliance is the true customer — every "why" in fintech DE traces back to a regulator. Framing the audit as "what would the SOX inspector ask?" aligns the work with the highest-priority stakeholder from day one.
• Column-type audit is O(schema) — a one-time grep across every DDL file catches the majority of money-representation bugs. The remediation is mechanical.
• Audit-trail coverage is O(tables) — every "state" table (balance, position, KYC status) needs history. Enumerating them once creates a durable checklist.
• Idempotency audit is O(endpoints) — every write-facing endpoint gets the same treatment. A missing key-store is the number-one root cause of double-charge incidents.
• Cost — the audit itself is 1-2 weeks of one senior engineer. The remediations are 1-6 months of team time. Doing this in-year is dramatically cheaper than doing it after a finding.

SQL
Topic — SQL
SQL problems for fintech DE fundamentals

Practice →

SQL Topic — data validation Data validation drills

Practice →

---

2. Reconciliation pipeline patterns

fintech reconciliation is a three-way match — source system, internal ledger, and counterparty — with tolerance windows and break tables

The mental model in one line: a fintech reconciliation engine takes three streams — the source system (your app), the internal ledger (your book of record), and the counterparty (Visa, ACH, SWIFT, exchange, banking partner) — and produces three outputs per aggregate: MATCHED, HOLD, and BREAK, each tied to an audit-visible reason code. Once you say "three inputs, three outputs, tolerance-window matching," the entire fintech reconciliation interview surface becomes a deduction from that shape.

The three feeds — one truth per input.

• Source system feed — the operational database, the payment gateway callback, the app-produced Kafka topic. This is the "we say we did this" signal. Latency: sub-second.
• Ledger feed — the book of record. Every debit and credit is booked into a double-entry ledger; the ledger's truth is what the finance team reports. Latency: seconds to minutes behind the source.
• Counterparty feed — Visa / MC settlement file, ACH NACHA file, SWIFT MT-940, exchange trade file. This is the "the outside world confirms" signal. Latency: hours to days; often batch.

The match engine — three outcomes per aggregate.

• MATCHED — all three sides agree within tolerance. The aggregate posts to the "settled" table; downstream reg reports pick it up.
• HOLD (suspense) — two of three sides agree but the third is late. The aggregate sits in a suspense table with a held_until timestamp; the engine re-checks on every counterparty arrival.
• BREAK — three sides disagree, or a two-of-three agreement exceeds the max hold window. The aggregate opens a case in the ops-investigation queue with a break_reason code.

Tolerance windows — the three axes.

• Amount tolerance. For USD, typical is $0.01 or 5 basis points, whichever is greater. For crypto, 0.00001 BTC or 1 basis point.
• Time tolerance. Counterparty timestamps drift; a 60-second tolerance is normal for card, 5-minute for ACH, 1-hour for SWIFT.
• FX tolerance. For cross-currency, use the fixing-rate for the value date — not the current spot rate. Tolerance is usually 5 basis points on the converted amount.

DECIMAL(38,18) and rounding-mode rules.

• Precision. 38 total digits, 18 after the decimal — enough to represent USD-to-satoshi FX without loss.
• Rounding. ROUND_HALF_EVEN (banker's rounding) for aggregates. ROUND_HALF_UP (away from zero) for consumer-facing display. Never mix modes in the same pipeline.
• Storage-vs-compute rounding. Store the full-precision NUMERIC(38, 18). Round only at the display / report boundary. The intermediate DECIMAL math is where the accuracy is preserved.

Matching strategies — FIFO, LIFO, N:M.

• FIFO (default for most jurisdictions). The oldest open source-side row matches the earliest counterparty row for the same key. Preferred for tax purposes in the US.
• LIFO — the newest open source-side row matches first. Uncommon in payments; more common in inventory / commodities.
• N:M matching (partial fills). A single counterparty row settles multiple source rows (batch payout), or a single source row is settled across multiple counterparty rows (partial fill on an exchange). The match engine records the partial-match relationship in a link table.

Currency-conversion snapshots — the versioned FX rule.

• Never convert on the fly with today's spot. Always use the FX rate as of the value date of the underlying transaction. This is a hard reg rule for cross-border payments.
• Design a fx_rate_history table keyed by (base_ccy, quote_ccy, valid_from, valid_to) and always join through it. This is the same SCD Type 2 pattern as the balance history.

Break-table schema — what a good BREAK row looks like.

• break_id — surrogate key.
• aggregate_key — the join key (usually external_txn_id).
• source_snapshot, ledger_snapshot, counterparty_snapshot — full JSON snapshots of the three sides at time-of-break.
• break_reason — enum: AMOUNT_DIFF, MISSING_LEDGER, MISSING_COUNTERPARTY, AMOUNT_AND_DATE_DIFF, etc.
• opened_at, resolved_at, resolved_by, resolution_notes — the audit trail on the break itself. The break table is also SCD Type 2.

Common interview probes on recon.

• "What are the three feeds?" — source, ledger, counterparty.
• "What are the three outcomes?" — MATCHED, HOLD, BREAK.
• "What tolerances do you set and why?" — amount, time, FX; each depends on the rail.
• "How do you handle a partial fill?" — N:M link table with matched_amount on each link.
• "How do you handle a missing counterparty file?" — HOLD with held_until = expected_arrival + 4h, alert at 4h, escalate at 8h.

Worked example — Two-way match on card settlement

Detailed explanation. The simplest recon: match card_authorizations (from your gateway) against visa_settlement (from the Visa daily file). Amount and merchant match are strict; date can be off by 1 business day.

Question. Write the SQL that produces the three outcome tables (matched, held, broken) from card_authorizations and visa_settlement. Use a 60-second time tolerance and an exact amount match.

Input — card_authorizations.

auth_id	external_txn_id	amount	authorized_at
a1	ext-100	25.00	2026-07-16 10:00:05
a2	ext-101	40.00	2026-07-16 10:00:10
a3	ext-102	12.50	2026-07-16 10:00:30

Input — visa_settlement.

settle_id	external_txn_id	amount	settled_at
s1	ext-100	25.00	2026-07-16 10:00:20
s2	ext-101	40.05	2026-07-16 10:00:15
—	ext-102	—	—

Code.

WITH joined AS (
  SELECT a.auth_id,
         a.external_txn_id,
         a.amount        AS auth_amount,
         s.amount        AS settle_amount,
         a.authorized_at,
         s.settled_at,
         CASE
           WHEN s.settle_id IS NULL                             THEN 'BREAK'
           WHEN ABS(a.amount - s.amount) > 0.01                 THEN 'BREAK'
           WHEN ABS(EXTRACT(EPOCH FROM s.settled_at - a.authorized_at)) > 60 THEN 'HOLD'
           ELSE 'MATCHED'
         END AS outcome,
         CASE
           WHEN s.settle_id IS NULL                             THEN 'MISSING_COUNTERPARTY'
           WHEN ABS(a.amount - s.amount) > 0.01                 THEN 'AMOUNT_DIFF'
           WHEN ABS(EXTRACT(EPOCH FROM s.settled_at - a.authorized_at)) > 60 THEN 'TIME_DIFF'
         END AS reason
  FROM   card_authorizations a
  LEFT JOIN visa_settlement s
         ON s.external_txn_id = a.external_txn_id
)
SELECT * FROM joined ORDER BY outcome, external_txn_id;

Step-by-step explanation.

1. The LEFT JOIN on external_txn_id gives one row per authorisation, with NULL on the settle side when the counterparty is missing. This is the core recon join.
2. The CASE on outcome codifies the three outcomes in the order they must be checked: missing counterparty → amount mismatch → time mismatch → matched. The order matters; a missing counterparty is more urgent than an amount diff.
3. The amount tolerance uses ABS(a.amount - s.amount) > 0.01 — half-cent tolerance, tighter than the typical 1-cent tolerance because card USD is normally exact.
4. The time tolerance uses ABS(EXTRACT(EPOCH FROM ...)) > 60 — 60 seconds. Card settlements normally arrive within 5 seconds; anything past 60 is a HOLD, past 4 hours is escalated to BREAK.
5. Downstream, three tables are populated by three INSERT ... SELECT statements filtered by outcome. The MATCHED rows post to the settled table; HOLDs go to a suspense table with a retry timestamp; BREAKs open a case.

Output.

external_txn_id	outcome	reason
ext-100	MATCHED	—
ext-101	BREAK	AMOUNT_DIFF
ext-102	BREAK	MISSING_COUNTERPARTY

Rule of thumb. Start every recon design with the outcome-CASE on paper before you write any join. If you cannot enumerate the outcomes, you do not yet understand the reconciliation.

Worked example — Three-way match with a ledger in the middle

Detailed explanation. The full three-way match adds the internal ledger between source and counterparty. All three sides must agree; if any two agree and the third disagrees, the break reason names which side disagrees so ops can investigate the right system first.

Question. Extend the two-way match to include a ledger_postings table. Show the SQL that produces a break reason of SOURCE_VS_LEDGER, LEDGER_VS_COUNTERPARTY, or SOURCE_VS_COUNTERPARTY depending on where the disagreement is.

Input — synthesised.

external_txn_id	source_amt	ledger_amt	cparty_amt
ext-200	100.00	100.00	100.00
ext-201	100.00	100.00	99.95
ext-202	100.00	99.90	100.00
ext-203	100.00	99.90	99.90

Code.

WITH j AS (
  SELECT s.external_txn_id,
         s.amount AS source_amt,
         l.amount AS ledger_amt,
         c.amount AS cparty_amt
  FROM   card_authorizations s
  LEFT JOIN ledger_postings   l USING (external_txn_id)
  LEFT JOIN visa_settlement   c USING (external_txn_id)
),
tolerances AS (
  SELECT *,
         ABS(source_amt - ledger_amt) > 0.01 AS s_l_break,
         ABS(ledger_amt - cparty_amt) > 0.01 AS l_c_break,
         ABS(source_amt - cparty_amt) > 0.01 AS s_c_break
  FROM   j
)
SELECT external_txn_id,
       CASE
         WHEN NOT s_l_break AND NOT l_c_break AND NOT s_c_break THEN 'MATCHED'
         WHEN s_l_break AND NOT l_c_break                       THEN 'BREAK: SOURCE_VS_LEDGER'
         WHEN NOT s_l_break AND l_c_break                       THEN 'BREAK: LEDGER_VS_COUNTERPARTY'
         WHEN s_l_break AND l_c_break AND NOT s_c_break         THEN 'BREAK: LEDGER_ONLY_WRONG'
         WHEN s_l_break AND l_c_break AND s_c_break             THEN 'BREAK: ALL_DIFFER'
         ELSE 'BREAK: UNKNOWN'
       END AS outcome
FROM   tolerances;

Step-by-step explanation.

1. The CTE j joins all three sources on external_txn_id. Every row has three amounts; nulls appear if any side is missing (in practice, wrap each LEFT JOIN with COALESCE(amount, 0) if you also want missing-side handling).
2. The tolerances CTE computes three pairwise break flags — one for every pair of the three sides. Each flag is a boolean of "difference exceeds tolerance."
3. The final CASE codifies the reasoning: if only one pair breaks, that pair's reason is the break reason. If two pairs break but the third pair matches, the ledger is the outlier (the two-agreeing sides confirm each other).
4. This mirrors the ops team's mental model: they look at which two systems agree, then investigate the third. The break_reason tells them where to look first.
5. The five explicit outcomes are the only outcomes possible with three sides and one tolerance per pair. If you find yourself adding more, you probably have a fourth side sneaking in — investigate.

Output.

external_txn_id	outcome
ext-200	MATCHED
ext-201	BREAK: LEDGER_VS_COUNTERPARTY
ext-202	BREAK: LEDGER_ONLY_WRONG
ext-203	BREAK: SOURCE_VS_LEDGER

Rule of thumb. Name every break reason after which pair disagrees. Ops teams triage by "which system is the outlier"; a well-named reason cuts investigation time in half.

Worked example — FX conversion with versioned rates

Detailed explanation. A EUR transaction is booked in the source system in EUR; the ledger stores USD; the counterparty file arrives in USD. Comparing them requires converting EUR → USD at the transaction's value date, not today's rate. The fx_rate_history table is joined temporally.

Question. Given a EUR-denominated source transaction with a value date, an internal USD ledger amount, and a fx_rate_history table, show the recon SQL that uses the value-date FX rate.

Input — source_txn.

external_txn_id	value_date	eur_amount
ext-300	2026-07-10	1000.00

Input — ledger_postings (USD).

external_txn_id	usd_amount
ext-300	1082.50

Input — fx_rate_history (EUR/USD).

base_ccy	quote_ccy	rate	valid_from	valid_to
EUR	USD	1.0800	2026-07-09	2026-07-11
EUR	USD	1.0825	2026-07-11	infinity

Code.

SELECT s.external_txn_id,
       s.value_date,
       s.eur_amount,
       fx.rate                              AS fx_rate,
       (s.eur_amount * fx.rate)::NUMERIC(38, 4) AS converted_usd,
       l.usd_amount                         AS ledger_usd,
       ABS(s.eur_amount * fx.rate - l.usd_amount) AS drift,
       CASE
         WHEN ABS(s.eur_amount * fx.rate - l.usd_amount) <= 0.55  -- 5bps of 1082
              THEN 'MATCHED'
         ELSE 'BREAK: FX_OR_AMOUNT_DIFF'
       END AS outcome
FROM   source_txn s
JOIN   ledger_postings l USING (external_txn_id)
JOIN   fx_rate_history fx
       ON fx.base_ccy   = 'EUR'
      AND fx.quote_ccy  = 'USD'
      AND fx.valid_from <= s.value_date
      AND fx.valid_to   >  s.value_date;

Step-by-step explanation.

1. The fx_rate_history join uses the [valid_from, valid_to) interval on the transaction's value_date, not on now(). This picks the rate that was in force when the transaction was priced.
2. The half-open interval convention means one and only one FX row matches any given date — no double-count, no gap.
3. The tolerance is expressed in absolute USD (0.55 on a 1082.50 amount is ~5 bps). Bps-based tolerances are the norm for FX because absolute cents scale poorly across currencies.
4. If the tolerance is exceeded, the break reason is deliberately vague — FX_OR_AMOUNT_DIFF — because at this stage you don't know whether the source amount is wrong or the FX rate is wrong. Ops disambiguates by pulling the trade blotter.
5. The converted_usd and drift columns are persisted in the recon output so the investigator sees the arithmetic without re-running the query. This is a hard requirement for audit.

Output.

external_txn_id	fx_rate	converted_usd	ledger_usd	drift	outcome
ext-300	1.0800	1080.00	1082.50	2.50	BREAK: FX_OR_AMOUNT_DIFF

Rule of thumb. Always join to the FX rate that was live on the transaction's value date. Never use SELECT rate FROM fx WHERE valid_to = infinity ORDER BY valid_from DESC LIMIT 1 — that gives today's rate and silently breaks historical restatements.

Senior FinTech interview question on reconciliation

A senior interviewer might ask: "Design a reconciliation engine for a card processor that runs continuously against Visa's daily settlement file. Walk me through the schema, the match logic, and the SLA on breaks."

Solution Using an event-driven three-way match with a break-tracking table

-- Break table — SCD Type 2 with resolution audit
CREATE TABLE recon_breaks (
    break_id           BIGSERIAL       PRIMARY KEY,
    aggregate_key      TEXT            NOT NULL,   -- external_txn_id
    break_reason       TEXT            NOT NULL,   -- SOURCE_VS_LEDGER, ...
    source_snapshot    JSONB           NOT NULL,
    ledger_snapshot    JSONB,
    cparty_snapshot    JSONB,
    opened_at          TIMESTAMPTZ     NOT NULL DEFAULT now(),
    resolved_at        TIMESTAMPTZ,
    resolved_by        TEXT,
    resolution_notes   TEXT,
    UNIQUE (aggregate_key, opened_at)
);

-- Match engine — runs on every counterparty arrival
INSERT INTO recon_breaks (aggregate_key, break_reason,
                          source_snapshot, ledger_snapshot, cparty_snapshot)
SELECT t.external_txn_id,
       t.outcome,
       row_to_json(s.*)::JSONB,
       row_to_json(l.*)::JSONB,
       row_to_json(c.*)::JSONB
FROM   three_way_match_view t     -- the CTE from the previous worked example
JOIN   card_authorizations s USING (external_txn_id)
LEFT JOIN ledger_postings   l USING (external_txn_id)
LEFT JOIN visa_settlement   c USING (external_txn_id)
WHERE  t.outcome LIKE 'BREAK:%'
  AND  NOT EXISTS (
        SELECT 1 FROM recon_breaks rb
        WHERE  rb.aggregate_key = t.external_txn_id
          AND  rb.resolved_at IS NULL
       );

Step-by-step trace.

Event	Match engine action	Break table effect
Source authorises ext-201 at $100	insert into card_authorizations	none (no cparty yet)
Ledger posts ext-201 at $100	insert into ledger_postings	none (no cparty yet)
Visa settle arrives ext-201 at $99.95	trigger 3-way match	insert BREAK: LEDGER_VS_COUNTERPARTY
Ops investigates, updates ledger	UPDATE ledger, re-run match	second BREAK row if still off
Ops resolves manually	UPDATE recon_breaks SET resolved_at, ...	audit trail preserved

The engine is idempotent: a re-run over the same input does not create duplicate breaks because of the NOT EXISTS guard on unresolved breaks. Every resolution is captured with resolved_at, resolved_by, resolution_notes for the audit trail.

Output:

Component	SLA
Match engine trigger	≤ 5s after counterparty file lands
Break dashboard update	≤ 15s after match
BREAK ops-team page	≤ 5min
Escalation to team lead	4h unresolved
Escalation to CFO	24h unresolved

Why this works — concept by concept:

• Three-way match is the invariant — two-way match is a specialisation. If you build three-way from day one, the two-way case is a WHERE ledger IS NULL filter, not a rewrite.
• Break table as SCD Type 2 — every open break is an unresolved row; every resolution is an in-place update to resolved_at. Never DELETE a break — it is part of the audit trail.
• JSONB snapshots — persist the three sides at time-of-break so the investigation sees the state that produced the break, not the state as it is now (which may have been corrected mid-investigation).
• Idempotent inserts — the NOT EXISTS guard on unresolved breaks lets you re-run the match engine safely on every counterparty arrival without creating duplicate open cases.
• Cost — the match query is O(source rows × cparty rows joined on key) with proper indexing (unique index on external_txn_id on all three tables). Breaks table growth is bounded by real break rate, typically < 1% of transactions.

SQL
Topic — joins
Joins problems for recon SQL

Practice →

SQL Topic — aggregation Aggregation problems for match totals

Practice →

---

3. Immutable audit trails

audit trail data engineering means append-only tables, hash-chained events, and WORM storage — the auditor query pattern is as-of past-timestamp, not now

The mental model in one line: an immutable audit trail is an append-only event log where every state change is a fact, every fact carries a hash of the previous fact, and the underlying storage is legally-locked WORM so that even a database admin cannot rewrite history. Once you say "append-only, hash-chained, WORM-vaulted," the entire audit trail data engineering interview surface becomes a deduction from those three properties.

Event sourcing — every state change is a fact.

• A fact is a row that says "at time T, event E happened." Facts are never modified, never deleted. The current state is a fold over the fact log.
• Contrast with CRUD. In CRUD, the current state is the row; the past state is lost. In event sourcing, the past state is the row; the current state is a derivation.
• Consequence for the DE. The primary "table" is the event log. The "current-balance" tables downstream are materialised views that can always be rebuilt by replaying the log.

Append-only tables — the enforcement layer.

• Database-level. Postgres: REVOKE UPDATE, DELETE ON ledger_events FROM app_role. Only INSERT is permitted. A separate admin role has update permission for legally-mandated corrections (which are themselves appended as reversal events, never in-place edits).
• Trigger-level. A BEFORE UPDATE OR DELETE trigger raises an exception. Defense-in-depth against future role misconfigurations.
• Application-level. The DAO layer exposes only insert_event(...); no update_event(...) or delete_event(...) symbols exist. The compiler enforces the pattern.

SYSTEM_VERSIONED and temporal tables.

• SQL Server, MariaDB, and Snowflake ship SYSTEM_VERSIONED tables: every UPDATE and DELETE auto-writes the prior row to a history table with valid_from / valid_to timestamps. Queries use FOR SYSTEM_TIME AS OF ... to read historical state.
• Postgres does not ship this natively, but the SCD Type 2 pattern from Section 1 is equivalent — a valid_from / valid_to history table plus a trigger to close the prior row on write.
• When to use temporal vs event-sourced. Temporal is a mechanical mapping of a mutable table into a history table; simpler to retrofit. Event-sourced is a deeper architectural choice; better when the business logic naturally decomposes into events.

Cryptographic hash chains — tamper-evidence.

• Every event row carries prev_hash (the hash of the previous row) and curr_hash (SHA-256(prev_hash || row_body)). Any modification to a past row invalidates its curr_hash and cascades to every subsequent row.
• Detection query. A periodic job walks the chain and verifies each row's hash. Any mismatch is an alert to compliance.
• The chain does not prevent tampering (a determined attacker can rewrite the whole chain if they have write access to every row). It provides evidence of tampering — the alert fires, the incident is filed, and the attacker cannot silently deny it.

WORM storage — the outer perimeter.

• S3 Object Lock (Governance or Compliance mode). Governance allows admin overrides; Compliance is truly immutable even for the root account. Compliance mode is the reg default.
• Glacier Vault Lock. An additional lock at the vault level, immune even to bucket-policy changes.
• Retention policy. Set to the regulatory floor (7 years for SOX, 10 for MiFID II). The object cannot be deleted or overwritten until the retention expires.
• Pipeline pattern. Append events to the OLTP table for real-time queries; a daily job exports the day's events (in Parquet, one file per day) to the S3 WORM bucket. The WORM bucket is the legal source of truth; the OLTP table is a queryable cache.

Retention rules — floor vs ceiling.

• Retention floor (regulator). 7 years for SOX evidence, 10 for MiFID II, 5 for MiCA, 3 for local retail-banking. Set the floor once and let Object Lock enforce it.
• Retention ceiling (privacy). GDPR requires PII to be deleted when the customer closes their account. Solved by pseudonymisation — the audit trail stores a pseudonym, the PII map is separately retained.
• Purge process. A quarterly job purges rows whose retention has expired and have no legal-hold flag. The purge itself is an appended audit event.

Query patterns — as-of and replay.

• As-of query. "What was the balance of c42 on 2023-11-14 09:00?" — walked over the event log by summing all events with event_time <= T.
• Point-in-time restore. The whole state of the ledger at time T can be rebuilt by folding the log. This is the disaster-recovery invariant.
• Replay for backfill. New downstream systems can be populated by replaying the event log from t=0. This is how you add a new reg-report calculation retroactively.

Common interview probes on audit trails.

• "What is the difference between temporal tables and event sourcing?" — mechanical mapping vs architectural choice.
• "How do you detect tampering?" — hash-chain verification job.
• "What is S3 Object Lock Compliance mode?" — even the root account cannot delete.
• "How do you handle GDPR-mandated deletion?" — pseudonymise at ingestion, purge the PII map, keep the audit trail intact.

Worked example — Append-only ledger with hash chain

Detailed explanation. A minimal ledger-event table: every debit/credit is a row, and every row carries a hash of the prior row. The hash chain lets you detect any tampering.

Question. Design the schema for ledger_events with a hash chain, and write the trigger that fills prev_hash and curr_hash on insert.

Input — first three events.

seq	account_id	delta	event_time
1	c42	+100	2026-07-16 10:00:00
2	c42	+50	2026-07-16 11:00:00
3	c42	−25	2026-07-16 12:00:00

Code.

CREATE TABLE ledger_events (
    seq          BIGSERIAL       PRIMARY KEY,
    account_id   TEXT            NOT NULL,
    delta        NUMERIC(38, 18) NOT NULL,
    event_time   TIMESTAMPTZ     NOT NULL,
    prev_hash    TEXT            NOT NULL,
    curr_hash    TEXT            NOT NULL
);

REVOKE UPDATE, DELETE ON ledger_events FROM app_role;

CREATE OR REPLACE FUNCTION fill_hash_chain() RETURNS trigger AS $$
DECLARE
    v_prev TEXT;
BEGIN
    SELECT curr_hash INTO v_prev
    FROM   ledger_events
    ORDER  BY seq DESC
    LIMIT  1;

    NEW.prev_hash := COALESCE(v_prev, 'GENESIS');
    NEW.curr_hash := encode(
        digest(NEW.prev_hash || NEW.account_id || NEW.delta::TEXT
               || NEW.event_time::TEXT, 'sha256'),
        'hex'
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_fill_hash
BEFORE INSERT ON ledger_events
FOR EACH ROW EXECUTE FUNCTION fill_hash_chain();

Step-by-step explanation.

1. The ledger_events table is INSERT-only; the REVOKE UPDATE, DELETE line enforces it at the SQL role layer. Only a super_admin role (used exclusively for legally-mandated migrations) can bypass.
2. The trigger runs BEFORE INSERT, reads the most recent curr_hash (or "GENESIS" for the first row), and sets prev_hash = that.
3. curr_hash = SHA-256(prev_hash || account_id || delta || event_time). Any change to any of the input fields would produce a different curr_hash, breaking the chain.
4. The pgcrypto extension provides digest(...). Postgres computes SHA-256 in a few microseconds per row — negligible overhead compared to the write.
5. A separate periodic job runs SELECT seq FROM ledger_events WHERE curr_hash != expected_curr_hash(seq) to detect tampering. Any mismatch alerts compliance immediately.

Output — filled hash chain.

seq	account_id	delta	prev_hash	curr_hash
1	c42	+100	GENESIS	3a5f...1b2c
2	c42	+50	3a5f...1b2c	8d1e...4f7a
3	c42	−25	8d1e...4f7a	c02b...9e3d

Rule of thumb. Always chain the hash with the content of the row, not just its seq. Chaining by seq alone lets an attacker rewrite the content while keeping the chain valid. Chaining by content-plus-prev-hash is the tamper-evident invariant.

Worked example — SYSTEM_VERSIONED table with as-of queries

Detailed explanation. For a mutable dimension (customer profile, KYC status), you want the "current" row to be updatable while the history is preserved automatically. The SCD Type 2 pattern with valid_from / valid_to gives you this without adopting event sourcing everywhere.

Question. Build a customer_profiles table with automatic history-tracking and an as-of query.

Input — profile changes for c42.

customer_id	risk_tier	changed_at
c42	LOW	2023-01-01
c42	MEDIUM	2023-08-01
c42	HIGH	2024-11-01
c42	MEDIUM	2025-06-01

Code.

CREATE TABLE customer_profiles (
    customer_id  TEXT           NOT NULL,
    risk_tier    TEXT           NOT NULL,
    valid_from   TIMESTAMPTZ    NOT NULL,
    valid_to     TIMESTAMPTZ    NOT NULL DEFAULT 'infinity',
    changed_by   TEXT           NOT NULL,
    PRIMARY KEY (customer_id, valid_from)
);

-- Update = close old row + insert new row (atomic)
CREATE OR REPLACE FUNCTION upsert_customer_profile(
    p_customer_id TEXT, p_risk_tier TEXT, p_changed_by TEXT
) RETURNS void AS $$
BEGIN
    UPDATE customer_profiles
    SET    valid_to = now()
    WHERE  customer_id = p_customer_id
      AND  valid_to   = 'infinity';

    INSERT INTO customer_profiles (customer_id, risk_tier, valid_from, changed_by)
    VALUES (p_customer_id, p_risk_tier, now(), p_changed_by);
END;
$$ LANGUAGE plpgsql;

-- As-of query — profile as of 2024-01-01
SELECT customer_id, risk_tier
FROM   customer_profiles
WHERE  customer_id = 'c42'
  AND  valid_from <= TIMESTAMPTZ '2024-01-01 00:00:00+00'
  AND  valid_to   >  TIMESTAMPTZ '2024-01-01 00:00:00+00';

Step-by-step explanation.

1. The upsert_customer_profile function runs the two-statement close-and-insert inside an implicit transaction; either both succeed or neither does, so the history is never gapped or overlapping.
2. valid_from and valid_to form a half-open interval [valid_from, valid_to). This is the convention that guarantees no double-counting on adjacency.
3. The as-of query filters for the single row whose interval contains the query timestamp. The composite primary key (customer_id, valid_from) supports the query with an index seek.
4. changed_by records which principal made the change — a soft SoD signal for the audit log. In prod, this comes from the authenticated session, not a raw parameter.
5. To answer "how did the risk tier evolve for c42?", one query walks all rows for that customer ordered by valid_from.

Output — as-of 2024-01-01.

customer_id	risk_tier
c42	MEDIUM

Rule of thumb. Never combine SCD Type 2 with in-place UPDATE on the same table. Every write goes through upsert_customer_profile — the DAO layer enforces the discipline.

Worked example — WORM export to S3 Object Lock

Detailed explanation. The OLTP ledger table is queryable but not legally immutable — a rogue DBA could still DROP TABLE. The pattern is a daily append-only export to an S3 bucket with Object Lock in Compliance mode, retention 7 years. That bucket is the legal source of truth.

Question. Given a daily job that exports the day's ledger_events to Parquet, show the S3 client call that lands the file in an Object-Lock bucket with 7-year retention.

Input.

Field	Value
Source	ledger_events rows for date 2026-07-16
Format	Parquet, snappy compression
Destination	s3://ledger-worm/events/date=2026-07-16/events.parquet
Retention	7 years from event date

Code.

import boto3, io, datetime as dt
import pandas as pd, pyarrow, pyarrow.parquet as pq

s3 = boto3.client("s3")

def export_day(events_df: pd.DataFrame, day: dt.date) -> None:
    buf = io.BytesIO()
    pq.write_table(pyarrow.Table.from_pandas(events_df), buf, compression="snappy")
    buf.seek(0)

    key = f"events/date={day.isoformat()}/events.parquet"
    retain_until = dt.datetime.combine(day, dt.time.min) + dt.timedelta(days=365 * 7)

    s3.put_object(
        Bucket="ledger-worm",
        Key=key,
        Body=buf.read(),
        ObjectLockMode="COMPLIANCE",
        ObjectLockRetainUntilDate=retain_until,
        ServerSideEncryption="aws:kms",
        SSEKMSKeyId="alias/ledger-worm-cmk",
    )

Step-by-step explanation.

1. ObjectLockMode="COMPLIANCE" puts the object into the strictest mode — even the AWS root account cannot delete the object before retain_until.
2. ObjectLockRetainUntilDate = day + 7y sets the retention floor. This satisfies the SOX 404 evidence-retention requirement without any human intervention.
3. SSEKMSKeyId="alias/ledger-worm-cmk" encrypts at rest with a customer-managed key. Key rotation is a scheduled process; every rotation is itself an audit event.
4. The one-file-per-day partitioning keeps the export idempotent — a re-run for a given day would fail if the key already exists (block put-object at bucket level with Object Ownership: BucketOwnerEnforced and a Deny PutObject when exists policy), or (safer) the retry uses a unique versioned key.
5. On the read side, an auditor query is a simple S3-select or a Trino / Athena query over the S3 prefix; the OLTP table is bypassed entirely for legal reads.

Output.

S3 property	Value
Bucket versioning	Enabled
Object Lock mode	COMPLIANCE
Retention	+7 years
Encryption	SSE-KMS (CMK)
Access	audit-role read-only

Rule of thumb. WORM is a bucket-level property; it cannot be toggled after the bucket is created. Design the buckets with Object Lock enabled from day one, even if you do not enforce it yet — retrofit is an S3 support ticket.

Senior FinTech interview question on immutable audit trails

A senior interviewer might ask: "You are asked by compliance to prove that a specific ledger balance on a specific past date has not been altered. Walk me through how you would design the system so that answer is easy — and how you would prove it."

Solution Using event-sourced ledger + hash chain + WORM export

Answering "prove balance for c42 on 2023-11-14 09:00 has not been altered"

Layer 1 — Query answer
----------------------
SELECT SUM(delta) FROM ledger_events
WHERE  account_id = 'c42' AND event_time <= '2023-11-14 09:00';

Layer 2 — Hash-chain proof
--------------------------
For each row in the answer set:
  - recompute curr_hash from (prev_hash || row_body).
  - verify recomputed hash matches stored curr_hash.
  - if any mismatch → tampering evidence.

Layer 3 — WORM proof
--------------------
Pull the corresponding parquet file from s3://ledger-worm/events/date=2023-11-14/.
Verify Object Lock is still active (get-object-retention shows COMPLIANCE + future date).
Diff the WORM parquet against the OLTP rows — must be identical.

Deliverable to auditor
----------------------
  - The as-of balance number
  - The verified hash-chain for the account's events
  - The WORM parquet file
  - The Object Lock retention API response

Step-by-step trace.

Step	Layer	Evidence produced
Run as-of SUM query	OLTP	balance number
Recompute hash chain for account	OLTP	chain-valid boolean
Retrieve S3 parquet for date	WORM	file + retention API response
Diff WORM vs OLTP	Cross-check	zero-diff proof
Bundle into audit response	Assembly	one PDF + signed CSV attachments

Any one layer failing is enough to disprove tampering claims; two layers passing plus WORM retention proof is the industry-standard evidence bundle.

Output:

Evidence layer	What it proves
SQL as-of query	balance number is reproducible
Hash chain verify	OLTP has not been silently rewritten
WORM parquet	primary evidence is legally immutable
Object Lock API	retention floor still enforced
Diff report	OLTP and WORM agree

Why this works — concept by concept:

• Event-sourced ledger — the balance is derived, never stored, so "the balance on date T" is a query, not a lookup. This makes as-of queries trivial and prevents "the balance was silently patched" attack vectors.
• Hash chain — tamper-evidence at the row level. Detects post-hoc modifications; does not prevent them, but ensures they cannot be silent.
• WORM parquet — legal immutability at the storage level. S3 Object Lock in Compliance mode is the strongest AWS retention primitive.
• Cross-layer diff — the triangulation between OLTP and WORM catches any single-layer compromise. An attacker would need to compromise both simultaneously and without leaving a trail.
• Cost — one row of storage per event, one hash compute per insert (~10 microseconds), one S3 put per day per partition. Total cost is dominated by the WORM S3 storage, which is Glacier-tier priced for old data.

SQL
Topic — slowly-changing-data
Slowly-changing-data problems for audit tables

Practice →

SQL Topic — window-functions Window-functions drills for as-of queries

Practice →

---

4. Idempotent + exactly-once for money

exactly-once payments need two patterns in tandem — an idempotency-key handler at the API edge and a transactional outbox at the DB write layer

The mental model in one line: every write-facing endpoint accepts an Idempotency-Key header and stores the request-response mapping in a keyed cache; every DB-level write to money-tables goes through a transactional outbox so the business row and its outbound event are committed atomically. Once you say "idempotency key up-front, outbox at write-time," the entire exactly-once payments interview surface becomes a deduction from those two halves.

Idempotency keys — the API-edge contract.

• Idempotency-Key header (Stripe convention, now industry-standard). A client-generated UUID sent with every write request. Two requests with the same key must produce the same effect and return the same response.
• Key store — a Redis / DynamoDB / Postgres table keyed by (key, endpoint) with a stored response body and a TTL (typically 24 hours). On cache hit, the handler returns the stored response without processing.
• Cache-fill-race protection. Use INSERT ... ON CONFLICT DO NOTHING RETURNING (or a Redis SETNX) so that concurrent requests with the same key do not both "win." The loser waits for the winner's response.
• Response snapshot. The store keeps the exact response body and status code — including any generated IDs (e.g. transfer_id) — so a retry gets the same IDs as the original request.

Exactly-once at the message-bus layer.

• Kafka transactional producer. enable.idempotence=true (default in 3.x) + transactional.id=<producer-id>. A batch of send() calls followed by commitTransaction() is atomic — either all messages are visible on the topic or none.
• Consumer offsets committed inside the transaction. The read process write loop reads from source-topic, writes to sink-topic, and commits offsets to __consumer_offsets — all in one Kafka transaction. This is what gives Kafka Streams EOS-v2 its guarantee.
• Sink-side dedup. If the downstream is not Kafka (e.g. Postgres), the sink adds a unique constraint on (external_txn_id) so a replayed record silently upserts instead of duplicating.

Sagas vs 2PC — cross-account transfers.

• 2PC (two-phase commit). Coordinator prepares each participant, waits for all PREPARED, then sends COMMIT. Atomic across all participants. Practical only within one database or one XA-compliant broker cluster; brittle in distributed systems.
• Saga. A sequence of local transactions with compensating actions for rollback. If step 3 fails, step 2 and step 1 are compensated (reversed via a new transaction). Practical in distributed fintech systems where 2PC is not available.
• Choreography vs orchestration. Choreography: each service listens for the previous step's event and emits the next. Orchestration: a central coordinator drives the state machine. Orchestration is easier to reason about; choreography is easier to scale.

Dual-write outbox — the lost-write killer.

• The problem. App writes to Postgres and publishes to Kafka in two separate calls. If Postgres succeeds and Kafka fails (network partition), the DB has data but no downstream event. If Kafka succeeds and Postgres fails, the event was published but the DB has no evidence.
• The pattern. Write the business row and an outbox row in the same transaction. A separate log-based reader (Debezium / a polling job) tails the outbox and publishes to Kafka. Because the two writes are one transaction, the outbox has a row iff the business row has a row.
• Once-published cleanup. The reader deletes (or marks-as-published) the outbox row after Kafka ack. The outbox table is a bounded queue; steady-state size is O(pending publications).

Retry-safe API design — the response snapshot.

• Deterministic response. Given the same input and same key, the response bytes must be identical. Never include now() or a random session ID in the response body.
• Stored response replay. On cache hit, return the stored response body and status without re-processing. Even if the underlying resource has since changed, the retry sees the original response.
• Key TTL policy. 24h is the industry norm — long enough to cover most retries, short enough to bound the key-store size.

Ledger consistency invariants.

• Sum-to-zero. For every transfer, the sum of all deltas across affected accounts equals zero. Double-entry bookkeeping — every debit has a credit.
• Never-negative (for asset accounts). A CHECK constraint prevents an insert that would make the balance negative unless the account is explicitly overdraftable.
• Currency-consistency. All deltas in a single transfer are in the same currency, or the FX-conversion delta is explicit and separately booked.
• Invariant checks in-line and offline. Enforce the sum-to-zero invariant at write time via a transaction-level check; also run a nightly offline verifier to catch drift.

Common interview probes on exactly-once for money.

• "What is an idempotency key?" — client-generated UUID; stored response; safe retries.
• "What is the transactional outbox?" — write business + outbox in one transaction; tail outbox to bus.
• "Saga vs 2PC?" — 2PC is brittle across systems; saga uses compensating transactions.
• "Why not just retry until success?" — non-idempotent retries produce duplicates; retries need the key handshake.
• "How do you detect a missed publication?" — reconcile outbox-emitted count vs Kafka-consumed count on a schedule.

Worked example — Idempotency-Key handler

Detailed explanation. A POST /transfers endpoint accepts an Idempotency-Key header. Two requests with the same key must produce the same transfer_id and the same response. The pattern is a keyed cache-lookup before processing.

Question. Implement a Python (Flask) idempotent POST /transfers handler that persists the response per key with 24h TTL.

Input — two requests, same key.

#	Idempotency-Key	body
1	k-abc-123	{"from":"a1","to":"a2","amount":50}
2	k-abc-123	{"from":"a1","to":"a2","amount":50}

Code.

from flask import Flask, request, jsonify
import uuid, json, time
import psycopg2, psycopg2.extras

app = Flask(__name__)
DB = psycopg2.connect("dbname=payments")

@app.post("/transfers")
def create_transfer():
    key = request.headers.get("Idempotency-Key")
    if not key:
        return jsonify(error="Idempotency-Key required"), 400
    body = request.get_json(force=True)

    with DB, DB.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
        # atomic: reserve the key or read the existing response
        cur.execute(
            """INSERT INTO idempotency_keys (key, endpoint, request_hash, created_at)
               VALUES (%s, '/transfers', %s, now())
               ON CONFLICT (key, endpoint) DO NOTHING""",
            (key, hash_request(body)),
        )
        if cur.rowcount == 0:
            cur.execute(
                "SELECT response_body, status FROM idempotency_keys "
                "WHERE key=%s AND endpoint='/transfers'",
                (key,),
            )
            row = cur.fetchone()
            if row["response_body"] is None:
                return jsonify(error="request in-flight, retry later"), 409
            return jsonify(json.loads(row["response_body"])), row["status"]

        # first-writer path: process and persist response
        transfer_id = str(uuid.uuid4())
        cur.execute(
            "INSERT INTO transfers (id, from_account, to_account, amount) "
            "VALUES (%s, %s, %s, %s)",
            (transfer_id, body["from"], body["to"], body["amount"]),
        )
        response = {"transfer_id": transfer_id, "status": "posted"}
        cur.execute(
            "UPDATE idempotency_keys SET response_body=%s, status=200 "
            "WHERE key=%s AND endpoint='/transfers'",
            (json.dumps(response), key),
        )
    return jsonify(response), 200

Step-by-step explanation.

1. The INSERT ... ON CONFLICT DO NOTHING reserves the (key, endpoint) pair atomically. If two requests arrive concurrently with the same key, only one wins the insert; the other sees rowcount == 0.
2. The loser reads the row. If response_body IS NULL, the winner is still processing — return 409 and let the client retry after a short backoff. If response_body is populated, the loser returns the cached response.
3. The winner performs the real transfer work in the same transaction, generates the transfer_id, then updates the idempotency row with the response body and status.
4. hash_request(body) (not shown) captures the request payload hash so a later retry with the same key but a different body can be rejected — a strong "your key was used for a different request" defense.
5. A separate purge job deletes rows with created_at < now() - '24h'. Row growth is bounded by traffic-per-day.

Output — two requests, one effect.

#	Idempotency-Key	transfer_id	status
1	k-abc-123	3f2e-...	200 (winner)
2	k-abc-123	3f2e-...	200 (cached)

Rule of thumb. Always hash the request body and store the hash. If a retry arrives with the same key but a different body, return an error — that is a client bug, not a legitimate retry.

Worked example — Transactional outbox for a payment event

Detailed explanation. The POST /transfers handler must both write to transfers and publish TransferPosted to Kafka. If the two are separate calls, a network partition can leave the DB and Kafka out of sync. The outbox pattern folds both writes into one DB transaction.

Question. Extend the transfer handler to write an outbox_events row in the same transaction as the transfer insert. Show the schema and the reader-side skeleton.

Input.

Step	Effect
1	Insert row into transfers
2	Insert row into outbox_events
3	Commit transaction
4	Debezium tails outbox_events → publishes to Kafka
5	Debezium marks row as published (or delete)

Code.

CREATE TABLE outbox_events (
    id           BIGSERIAL PRIMARY KEY,
    aggregate    TEXT      NOT NULL,     -- e.g. 'transfer'
    aggregate_id TEXT      NOT NULL,
    event_type   TEXT      NOT NULL,     -- e.g. 'TransferPosted'
    payload      JSONB     NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    published_at TIMESTAMPTZ
);

CREATE INDEX outbox_unpublished_idx
    ON outbox_events (id)
    WHERE published_at IS NULL;

# Handler — atomic write of business row + outbox row
with DB, DB.cursor() as cur:
    cur.execute(
        "INSERT INTO transfers (id, from_account, to_account, amount) "
        "VALUES (%s, %s, %s, %s)",
        (transfer_id, body["from"], body["to"], body["amount"]),
    )
    cur.execute(
        "INSERT INTO outbox_events (aggregate, aggregate_id, event_type, payload) "
        "VALUES (%s, %s, %s, %s::JSONB)",
        ("transfer", transfer_id, "TransferPosted", json.dumps(body)),
    )
# both writes commit together; Debezium later publishes and marks published_at

Step-by-step explanation.

1. The two INSERTs share a single transaction — either both are committed or both are rolled back. The DB itself becomes the source of truth for "has this event been published-worthy?"
2. The outbox_unpublished_idx partial index gives Debezium (or a polling worker) an O(1) lookup for the next batch of unpublished events.
3. Debezium tails the outbox using logical replication (Postgres WAL). Each row it reads is emitted to a Kafka topic named after the aggregate (e.g. transfer.events).
4. After a successful Kafka ack, Debezium UPDATEs the row's published_at. A janitor job purges rows with published_at < now() - '7d'.
5. If Debezium crashes, it resumes from its last-committed WAL position. Because publication is idempotent (the Kafka topic dedupes on id), the worst case is a small burst of duplicate TransferPosted events, which the downstream consumer dedupes on aggregate_id.

Output — outbox row + Kafka publication.

Component	State after transfer
transfers table	1 new row (transfer_id)
outbox_events	1 new row (unpublished)
Kafka topic	1 event published (moments later)
outbox_events	published_at set (moments later)

Rule of thumb. Every payment-facing write goes through the outbox. Never call the message bus directly from the request handler — the temptation is a lost-write bug waiting to happen.

Worked example — Saga for a cross-account transfer

Detailed explanation. A cross-bank transfer touches three services: account-a debits, account-b credits, and settlement posts to the counterparty rail. 2PC across three services is impractical; a saga with compensating actions is the pattern.

Question. Design a saga for TransferInitiated → DebitPosted → CreditPosted → SettlementPosted. Show what happens if CreditPosted fails.

Input.

Step	Local action	Compensating action
1	account-a: hold funds	release hold
2	account-a: debit	reverse debit
3	account-b: credit	reverse credit
4	settlement: post	reverse settlement

Code.

# Orchestration-style saga using a state machine

def run_transfer_saga(transfer_id):
    try:
        put_hold(transfer_id)                     # step 1
        debit(transfer_id)                        # step 2
        credit(transfer_id)                       # step 3
        settle(transfer_id)                       # step 4
        emit("TransferCompleted", transfer_id)
    except CreditFailed:
        reverse_debit(transfer_id)                # compensate step 2
        release_hold(transfer_id)                 # compensate step 1
        emit("TransferFailed", transfer_id, reason="credit_failed")
    except SettleFailed:
        reverse_credit(transfer_id)               # compensate step 3
        reverse_debit(transfer_id)                # compensate step 2
        release_hold(transfer_id)                 # compensate step 1
        emit("TransferFailed", transfer_id, reason="settle_failed")

Step-by-step explanation.

1. Each step is a local transaction inside its owning service — the debit is atomic in account-a's DB; the credit is atomic in account-b's DB. No 2PC across services.
2. On any step failure, the orchestrator walks backwards through the completed steps and calls each one's compensating action. Compensations must themselves be idempotent (retries are common).
3. The compensating actions are usually not perfect inverses (a debit reversal is a new credit, not a delete of the original debit). This preserves the audit trail — every action, including the reversal, is a row in the ledger.
4. Emitting TransferFailed or TransferCompleted at the end lets downstream reg-report pipelines pick up the final state.
5. A persistent saga log records every step's outcome so a restart after orchestrator crash resumes correctly. Without persistence, an orchestrator crash mid-saga leaves the world in an inconsistent state until an operator intervenes.

Output — happy path vs failed credit.

Step	Happy path	Credit fails
put_hold	done	done
debit	done	done
credit	done	FAILED
settle	done	(skipped)
reverse_debit	(not needed)	done
release_hold	(not needed)	done
Final state	Completed	Failed, funds returned

Rule of thumb. Every step in a saga needs a persisted state entry. The orchestrator's state machine is itself a piece of durable data — do not keep it in memory only.

Senior FinTech interview question on exactly-once payments

A senior interviewer might frame this as: "A payment webhook can be retried multiple times by an upstream partner. Walk me through, end-to-end, how you would make sure a single POST /webhook/payment retried five times produces exactly one credit to the recipient account."

Solution Using idempotency key + transactional outbox + downstream dedup

End-to-end exactly-once webhook path

Step 1 — API-edge (idempotency key)
- Reject request without Idempotency-Key.
- INSERT ... ON CONFLICT on (key, /webhook/payment).
- If loser: return stored response; do NOT re-process.

Step 2 — Handler (single transaction)
- BEGIN
- INSERT INTO payments (external_txn_id, ...)  -- unique constraint on external_txn_id
- INSERT INTO outbox_events (aggregate_id=payment_id, event_type='PaymentReceived', ...)
- UPDATE idempotency_keys SET response_body=..., status=200 WHERE key=?
- COMMIT

Step 3 — Publisher (Debezium)
- Tails outbox_events; emits to Kafka topic `payments.events`.
- Marks outbox row published_at after Kafka ack.

Step 4 — Consumer (ledger service)
- Reads payments.events with EOS-v2 processing.guarantee.
- INSERT ... ON CONFLICT (external_txn_id) DO NOTHING into ledger_events.
- Commit consumer offset in same Kafka transaction.

Step 5 — Reconciliation
- Nightly: sum(payments) vs sum(ledger_events) grouped by external_txn_id.
- Any drift → break table.

Step-by-step trace.

Retry #	Step 1 outcome	Step 2 outcome	Step 3 outcome	Ledger state
1	winner	commit	publish	+1 credit
2	loser	(skipped)	(skipped)	unchanged
3	loser	(skipped)	(skipped)	unchanged
4	loser	(skipped)	(skipped)	unchanged
5	loser	(skipped)	(skipped)	unchanged

Five retries, one credit. The idempotency key stops re-processing at step 1; even if it slipped past, the unique constraint on external_txn_id in the ledger table would deduplicate; even if that failed, the nightly recon would catch the drift.

Output:

Component	EOS contribution
Idempotency key	first line of defense at API
Transactional outbox	no lost publication
Debezium logical replication	at-least-once publication, dedupe downstream
Consumer unique constraint	second line of defense at DB
Nightly recon	third line of defense, catches everything else

Why this works — concept by concept:

• Three lines of defense — the idempotency key is the primary guarantee; the unique constraint is a belt-and-braces; the recon is the audit. Defense in depth is the fintech default because the cost of a duplicate is real money.
• Idempotency key at the edge — catches duplicates before any work happens. Cheapest place to reject.
• Transactional outbox — solves the dual-write bug. The DB is the source of truth for what should be published.
• Downstream unique constraint — catches replays that slipped past. Cheap DB-level guarantee.
• Cost — key store: one row per unique request, TTL-bounded. Outbox: one row per event, deleted after publication. Recon: O(payments per day). Total is a small fraction of the transaction volume itself.

SQL
Topic — SQL
SQL problems for idempotent design

Practice →

Streaming Topic — streaming Streaming problems for exactly-once patterns

Practice →

---

5. Regulatory reporting + observability

data engineering finance operates under SoD, lineage, and evidence obligations — every reg-report is a pipeline plus a paper trail the auditor can follow

The mental model in one line: regulatory reporting is a pipeline that produces both a number and the evidence that the number was produced correctly — lineage (which inputs), quality tests (which invariants held), approvals (which humans signed off), and job logs (when it ran) all get bundled into an evidence artifact that the auditor consumes. Once you say "every reg-report is a number plus a paper trail," the entire financial data pipeline observability surface becomes a checklist of what to bundle and where to store it.

SOX SoD — segregation of duties in the pipeline.

• Maker. The engineer who drafts a pipeline change (new column, new transformation). Cannot approve their own change.
• Checker. A peer engineer who reviews the change (code review, dbt-style contract validation).
• Approver. A separate role — usually a tech lead or a compliance-designated approver — who authorises deploy to prod.
• Enforced in the CI/CD. The deploy pipeline reads the PR's approvals and blocks unless maker ≠ approver and at least one checker signed off. This is the auditable segregation-of-duties evidence.

Lineage — OpenLineage / Marquez.

• Row-level lineage — every downstream row can name its upstream inputs.
• Column-level lineage — every downstream column can name which upstream columns fed it and which transformations were applied.
• Emit-on-run — every job run emits a RunEvent to an OpenLineage endpoint with inputs, outputs, and column-level lineage. Marquez / DataHub / Amundsen stores the events.
• Auditor value — "prove that our reg-report line 42 was produced from these inputs" becomes a lineage query, not a code archaeology exercise.

PII masking for reg reports.

• Ingestion-side tokenisation. PAN, SSN, email — replaced by a deterministic token at the ingestion edge. The reverse-mapping lives in a separate vault (Vault / KMS / a dedicated Postgres schema).
• Report-time masking. For reports that must include PII (e.g. subpoena response), the mapping is applied only at the report boundary, with an audit event fired every time the mapping is used.
• GDPR pseudonymisation. For EU customers, the mapping is deletable on right-to-be-forgotten. The audit trail remains intact but the PII becomes unrecoverable.

Data-quality SLAs — Great Expectations, Soda, dbt tests.

• Row-count SLA. "The reg-report source table should have between 950k and 1.05M rows daily." A test fails below or above.
• Sum-to-zero SLA. "For every settlement day, the sum of debits equals the sum of credits." Fires on any drift.
• Uniqueness SLA. "External_txn_id must be unique." Duplicates block the reg-report build.
• Freshness SLA. "The last-updated timestamp on transactions must be within 5 minutes of now() during business hours." Alerts on stale pipelines.
• Distribution SLA. "The mean transaction amount should be within ±20% of the last 30-day average." Fires on distribution drift (e.g. a fee-cap misconfiguration).

Evidence artifacts for auditors.

• Lineage snapshot. OpenLineage JSON for the reg-report run.
• Test outcomes. Great Expectations validation JSON with pass/fail per expectation.
• Job logs. Timestamped log lines with run id, git SHA, environment.
• Approval signatures. The PR's approval records (from GitHub / GitLab API).
• Bundle. All of the above packaged into a single dated folder (evidence/reg-report-2026-07-16/) and stored in the WORM S3 bucket. Retention floor: same as the reg-report retention (7 years).

Runbook — what to hand the auditor at year-end.

• Data-flow diagram — one page per reg-report, showing sources → transformations → output.
• Lineage export — machine-readable OpenLineage JSON for the year's runs.
• Test outcome summary — pass rate per expectation, per month.
• Change log — every PR that touched the reg-report pipeline, with maker/checker/approver.
• Incident log — every SLA breach, root cause, remediation.
• Access log — who ran the pipeline manually, when, for what reason.

Common interview probes on reg observability.

• "What is SoD in a data pipeline?" — maker ≠ approver, at least one checker.
• "What is OpenLineage?" — open spec for run-time lineage events; producers emit, backends store.
• "How do you handle PII in a reg-report?" — tokenise at ingestion, mask at report boundary, log every unmask.
• "What is an evidence bundle?" — the folder you hand the auditor: lineage + tests + logs + approvals.

Worked example — Great Expectations expectations for a payments table

Detailed explanation. A transactions_daily table feeds a reg report. You define expectations that block the reg-report build if any fails. The pattern is code-as-config: expectations are versioned, tested in CI, and executed as part of the pipeline.

Question. Write a Great Expectations suite for a transactions_daily table with row-count, uniqueness, not-null, and sum-to-zero invariants.

Input — expected shape.

column	rule
external_txn_id	not null, unique
amount	not null, sum ≥ 0
currency	not null, in {USD, EUR, GBP}
booked_at	not null, within last 24h
row count	between 900_000 and 1_100_000

Code.

import great_expectations as ge
from great_expectations.core.expectation_suite import ExpectationSuite

suite = ExpectationSuite(name="transactions_daily.suite")

suite.add_expectation(ge.expectations.ExpectTableRowCountToBeBetween(
    min_value=900_000, max_value=1_100_000
))

suite.add_expectation(ge.expectations.ExpectColumnValuesToNotBeNull(
    column="external_txn_id"
))

suite.add_expectation(ge.expectations.ExpectColumnValuesToBeUnique(
    column="external_txn_id"
))

suite.add_expectation(ge.expectations.ExpectColumnValuesToNotBeNull(
    column="amount"
))

suite.add_expectation(ge.expectations.ExpectColumnValuesToBeInSet(
    column="currency", value_set=["USD", "EUR", "GBP"]
))

suite.add_expectation(ge.expectations.ExpectColumnMaxToBeBetween(
    column="booked_at",
    min_value={"$PARAMETER": "yesterday_utc"},
    max_value={"$PARAMETER": "now_utc"},
))

# Custom sum-to-zero expectation (via SQL)
suite.add_expectation(ge.expectations.ExpectColumnPairValuesAToBeGreaterThanB(
    column_A="credits_sum", column_B="0"
))

Step-by-step explanation.

1. Each expectation is a versioned rule in the suite. The suite is checked into git; changes go through the standard PR review.
2. The row-count expectation catches macro-level breaks (e.g. a dropped connector, a partial file arrival). The uniqueness and not-null catch row-level breaks.
3. The booked_at expectation uses runtime parameters — yesterday_utc and now_utc are substituted at run time so the same expectation works every day without editing.
4. Custom expectations (like sum-to-zero) can be expressed as SQL through GX's SqlAlchemyExecutionEngine. The invariant is domain-specific but the machinery is the same.
5. The suite runs in the pipeline before the reg-report is built. On any fail, the run halts and pages the on-call DE; the reg-report is not produced until the underlying issue is fixed.

Output — validation result summary.

Expectation	Success	Observed
row_count_between	true	987,231
external_txn_id not null	true	0 nulls
external_txn_id unique	true	0 duplicates
amount not null	true	0 nulls
currency in set	true	{USD, EUR, GBP}
booked_at within 24h	true	last: now − 12m

Rule of thumb. Every reg-report source table gets a suite with at least four expectations: row-count, uniqueness, not-null, and one domain-specific invariant. Fewer than four and you are relying on downstream to catch upstream bugs.

Worked example — OpenLineage emit-on-run for a dbt model

Detailed explanation. Every dbt run emits OpenLineage events to a Marquez backend, capturing which upstream tables fed which downstream model. The auditor can later reconstruct the exact inputs of any reg-report run.

Question. Configure a dbt project to emit OpenLineage events to Marquez, and show what a single RunEvent for a daily_positions model looks like.

Input.

dbt model	inputs	output
daily_positions	transactions, fx_rate_history	positions.daily_positions

Code.

# profiles.yml — enable openlineage
outputs:
  prod:
    type: postgres
    host: db-prod
    dbname: warehouse
    schema: positions
    # OpenLineage
    openlineage_url: http://marquez:5000
    openlineage_namespace: fintech-prod

# dbt run — emits RunEvent per model
OPENLINEAGE_URL=http://marquez:5000 \
OPENLINEAGE_NAMESPACE=fintech-prod \
dbt run --select daily_positions

{
  "eventType": "COMPLETE",
  "eventTime": "2026-07-16T09:12:00Z",
  "run": { "runId": "b6f0-...-1a" },
  "job": { "namespace": "fintech-prod", "name": "positions.daily_positions" },
  "inputs": [
    { "namespace": "fintech-prod", "name": "transactions" },
    { "namespace": "fintech-prod", "name": "fx_rate_history" }
  ],
  "outputs": [
    { "namespace": "fintech-prod", "name": "positions.daily_positions",
      "facets": {
        "columnLineage": {
          "fields": {
            "position_usd": {
              "inputFields": [
                { "namespace": "fintech-prod", "name": "transactions", "field": "amount" },
                { "namespace": "fintech-prod", "name": "fx_rate_history", "field": "rate" }
              ],
              "transformationDescription": "amount * fx_rate at value_date"
            }
          }
        }
      }
    }
  ]
}

Step-by-step explanation.

1. The openlineage_url in profiles.yml tells dbt to emit RunEvent for every model execution. Marquez ingests them.
2. Each RunEvent names the model (job.name), its unique run id, its input datasets, and its output datasets.
3. The columnLineage facet inside the output captures per-column lineage — for position_usd, the sources are transactions.amount and fx_rate_history.rate, with a description of the transformation.
4. Marquez indexes these events; a query like "which inputs fed daily_positions on 2026-07-16?" becomes an API call, not a code-reading exercise.
5. At year-end, dumping the year's RunEvent JSONs into the WORM bucket is a one-command evidence-bundle step.

Output — auditor query result.

model	run_id	inputs	outputs
daily_positions	b6f0-...-1a	transactions, fx_rate_history	positions.daily_positions

Rule of thumb. Enable OpenLineage from day one on every new pipeline. Retrofitting it after two years of pipeline growth is a large project; enabling it before day one is a config change.

Worked example — SoD approval enforcement in CI

Detailed explanation. A GitHub Actions workflow blocks the deploy step unless the PR was authored by one identity, checked by a second identity, and approved by a third with the dp-approver role. This is the SoD enforcement layer for the audit trail.

Question. Write a GitHub Action step that fails the deploy if maker == approver or if no dp-approver review is present.

Input — PR review metadata.

Reviewer	Association	Role
alice (maker)	author	—
bob	REVIEWED	eng peer
carol	APPROVED	dp-approver

Code.

name: deploy-fintech-pipeline
on: [pull_request]

jobs:
  sod-check:
    runs-on: ubuntu-latest
    steps:
      - name: Verify SoD
        env:
          GH_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        run: |
          PR=${{ github.event.pull_request.number }}
          AUTHOR=${{ github.event.pull_request.user.login }}
          APPROVER=$(gh pr view $PR --json reviews \
            --jq '.reviews[] | select(.state=="APPROVED") | .author.login' \
            | head -n1)

          if [ -z "$APPROVER" ]; then
            echo "::error::No approver on PR #$PR"; exit 1
          fi
          if [ "$AUTHOR" = "$APPROVER" ]; then
            echo "::error::SoD violation: author == approver ($AUTHOR)"; exit 1
          fi

          # verify APPROVER has dp-approver role via a JSON registry
          ROLE=$(jq -r ".\"$APPROVER\"" .github/dp-approvers.json)
          if [ "$ROLE" != "dp-approver" ]; then
            echo "::error::Approver $APPROVER lacks dp-approver role"; exit 1
          fi

          echo "SoD OK — author=$AUTHOR approver=$APPROVER"

Step-by-step explanation.

1. The action reads the PR's author and approver identities from GitHub's API. Both come from authenticated identities, so they cannot be forged.
2. The AUTHOR = APPROVER check enforces the maker ≠ approver rule — the most basic SoD invariant.
3. The dp-approvers.json file is a git-controlled registry of who holds the dp-approver role. Changes to that file are themselves under SoD (via a CODEOWNER rule).
4. The action runs on every PR; a failing SoD check blocks the merge button. Bypassing is only possible via an org-admin, which is itself audited.
5. The echo output ("SoD OK — author=alice approver=carol") is captured in the CI log and archived to the evidence bundle for the release.

Output — SoD check log.

Field	Value
PR #	1284
Author	alice
Approver	carol
SoD verdict	OK
Timestamp	2026-07-16T14:22:00Z

Rule of thumb. Never accept "we trust our engineers" as SoD evidence. Auditors require automated enforcement — a green CI badge that says "maker ≠ approver" is worth ten pages of process docs.

Senior FinTech interview question on reg reporting

A senior interviewer might frame this as: "You are asked to build a monthly capital-adequacy report that must be filed by the 5th of each month. Walk me through the pipeline, the observability, and the evidence bundle you would hand the auditor."

Solution Using a versioned pipeline + OpenLineage + Great Expectations + WORM evidence bundle

Monthly capital-adequacy pipeline

Sources (T-1 close-of-day)
--------------------------
- ledger_events (append-only, hash-chained)
- positions (SCD Type 2)
- fx_rate_history (SCD Type 2)
- capital_buffers (config)

Transformations (dbt)
---------------------
- daily_positions      -- fold ledger events per account
- daily_exposures      -- risk-weight positions by asset class
- monthly_capital      -- aggregate exposures, compute ratios

Quality gates (Great Expectations)
----------------------------------
- row-count SLA on daily_positions
- sum-to-zero on ledger_events
- uniqueness on external_txn_id
- freshness on positions (≤ 30min behind)
- distribution SLA on daily_exposures

Observability (OpenLineage → Marquez)
-------------------------------------
- every dbt run emits RunEvent
- column-level lineage captured for every downstream column
- year of RunEvents dumped to WORM bucket on 31 Dec

Approval (SoD)
--------------
- pipeline code: maker + checker + dp-approver (CI-enforced)
- monthly report: DE runs, treasury reviews, CFO signs off
- signatures stored alongside the report in WORM

Evidence bundle (per month)
---------------------------
- final capital-adequacy CSV
- OpenLineage RunEvents for the month
- Great Expectations validation JSON
- git SHA + PR list of every code change during the month
- CFO signature receipt
Bundled into s3://reg-evidence-worm/capital-adequacy/2026-07/
Object Lock: COMPLIANCE, retain 7 years.

Step-by-step trace.

Stage	Owner	Deliverable
Day T-1 close	ingest pipeline	ledger_events populated
Day T 06:00	dbt run	daily_positions computed
Day T 06:15	Great Expectations	validation results
Day T 07:00	monthly_capital	ratios computed
Day T 08:00	DE on-call	numeric sanity check
Day T 09:00	treasury review	line-by-line variance
Day T 12:00	CFO sign-off	signed PDF
Day T 14:00	evidence bundling	WORM upload
Day 5th of month	filing	report submitted
Year-end	auditor request	evidence bundle handed over

The pipeline itself is straightforward SQL + dbt; the evidence around the pipeline is what makes it reg-ready. Missing lineage or missing quality tests are the gaps auditors find; missing sign-off is the gap regulators find.

Output:

Evidence layer	Auditor use
Numeric output	proves the answer
Lineage export	proves the inputs
Quality test results	proves the invariants held
Approval trail	proves human oversight
WORM bundle	proves nothing was altered post-filing

Why this works — concept by concept:

• Bundle every filing — the reg-report is not just the number; it is the number plus the trail. Bundling at filing time locks the evidence to the number.
• WORM at the bundle level — Object Lock on the whole folder means the evidence cannot be re-touched by a post-filing "correction."
• Lineage + quality + approvals — three orthogonal signals; an auditor comfortable with any one still asks for the other two. Ship all three.
• Automated SoD in CI — replaces "we trust our people" with "our CI blocks maker == approver merges." The evidence is a CI log, not a process doc.
• Cost — the observability infra (OpenLineage backend, Great Expectations, WORM bucket) is a small fraction of the pipeline compute cost. Adding evidence generation is O(1) per run.

SQL
Topic — data validation
Data-validation drills for reg-report SLAs

Practice →

SQL
Topic — aggregation
Aggregation problems for daily positions

Practice →

---

Cheat sheet — fintech DE recipes

• Decimal precision by asset class. Fiat USD/EUR/GBP: NUMERIC(38, 4) is enough for most books; NUMERIC(38, 6) if you carry sub-basis-point FX. Crypto (BTC/ETH): NUMERIC(38, 18) to represent full satoshi / wei. Store minor units as BIGINT for the fastest arithmetic when precision permits (e.g. cents-only USD).
• Rounding-mode default. ROUND_HALF_EVEN (banker's rounding) for aggregates and posting; ROUND_HALF_UP for consumer-facing display. Set the mode once at context creation; never mix.
• Three-way recon SQL template. source LEFT JOIN ledger USING (external_txn_id) LEFT JOIN counterparty USING (external_txn_id) + CASE on the three pairwise tolerance flags + INSERT into MATCHED / HOLD / BREAK tables.
• Break-table schema. break_id, aggregate_key, break_reason, source_snapshot JSONB, ledger_snapshot JSONB, cparty_snapshot JSONB, opened_at, resolved_at, resolved_by, resolution_notes — SCD Type 2 by design.
• Append-only + hash chain schema. seq BIGSERIAL, prev_hash, curr_hash, REVOKE UPDATE, DELETE, BEFORE INSERT trigger to fill hashes. Verifier job runs nightly to detect chain breaks.
• SCD Type 2 history table. (entity_id, valid_from PRIMARY KEY (entity_id, valid_from)), valid_to DEFAULT 'infinity', upsert closes the previous open row atomically.
• WORM export. Daily Parquet export to s3://ledger-worm/events/date=YYYY-MM-DD/; ObjectLockMode=COMPLIANCE, retention = 7 years from event date, SSE-KMS with CMK.
• Idempotency-key handler skeleton. Idempotency-Key required header; INSERT ... ON CONFLICT DO NOTHING reserves the key; loser returns stored response; winner processes and updates response. 24h TTL on the key store.
• Transactional outbox SQL. INSERT INTO business_table + INSERT INTO outbox_events inside a single transaction; Debezium (or a polling worker) tails the outbox and publishes to Kafka; UPDATE published_at after ack.
• Saga vs 2PC rule. Within one DB / one XA broker: 2PC. Across services: saga with per-step compensating transactions. Persist the saga state; do not keep it in memory only.
• Kafka EOS-v2. processing.guarantee=exactly_once_v2, min.in.sync.replicas>=2, replication.factor>=3 on internal topics; consumer offsets committed inside the producer transaction.
• SOX SoD approval matrix. maker ≠ approver, at least one checker; enforced in CI by reading PR review metadata against a git-controlled approver registry; SoD verdict captured in the CI log and archived.
• OpenLineage emit-on-run snippet. Set OPENLINEAGE_URL and OPENLINEAGE_NAMESPACE in the environment; every dbt / Airflow / Spark run emits RunEvents to Marquez with column-level lineage.
• Great Expectations bare-minimum suite. row-count-between, unique on external_txn_id, not-null on amount, in-set on currency, freshness on booked_at, plus one domain-specific invariant (sum-to-zero for ledgers).
• PII masking pattern. Tokenise at ingestion (Vault / KMS deterministic tokens); reverse-mask only at report boundary; log every reverse-mask with reason and requester; GDPR delete = drop the mapping row.
• Evidence bundle recipe. Per filing: numeric output CSV + OpenLineage RunEvent JSONs + Great Expectations validation JSONs + git SHA + PR list + signature receipt → s3://reg-evidence-worm/{report}/{period}/ with Object Lock retention = filing retention floor.
• T+0 recon SLA envelope. Match engine trigger ≤ 5s after counterparty arrival; break dashboard update ≤ 15s; page on-call ≤ 5min; escalate to team lead at 4h unresolved; escalate to CFO at 24h.
• Reg-report SLA envelope. Data-ready by T+6h; pipeline complete by T+12h; treasury review by T+18h; CFO sign-off by T+24h; filing by the 5th of the following month (or the local jurisdiction's deadline).

Frequently asked questions

What makes fintech data engineering different from e-commerce data engineering?

Three structural differences dominate. Regulation is the primary customer — PSD3, SOX 404, PCI-DSS v4, MiCA, DORA all impose retention, auditability, and evidence obligations that shape schema, storage, and process. Money precision is exact, not approximate — NUMERIC(38, 18) and banker's rounding (ROUND_HALF_EVEN) replace float because 0.1 cent times a billion transactions is real revenue leak. Every write is watched — append-only tables, Idempotency-Key headers, transactional outboxes, and segregation-of-duties CI gates are the defaults, not the exceptions. In e-commerce DE, "eventually consistent" and "good enough" are acceptable; in fintech data engineering, they are audit findings. The mental model shift is treating the auditor as your true customer — every design choice traces back to "how will this look in a SOX 404 review."

Why is Decimal preferred over Float for money in fintech pipelines?

Float (IEEE-754 binary) cannot exactly represent most decimal fractions. 0.1 + 0.2 in float64 is 0.30000000000000004, not 0.3. On any single transaction the drift is invisible; multiplied across a billion transactions per year the drift is measured in tens of thousands of dollars — a real P&L hit and, worse, a real audit finding. Decimal (or Postgres NUMERIC, Java BigDecimal, Snowflake NUMBER) represents the exact decimal value. Combined with an explicit rounding mode (ROUND_HALF_EVEN for aggregates, ROUND_HALF_UP for consumer display) and a fixed precision (NUMERIC(38, 18) covers USD to satoshi FX), it is safe for money. Every payments data engineering interview probe on money precision expects you to name Float as the bug, Decimal as the fix, and HALF_EVEN as the rounding mode.

What is a three-way reconciliation in fintech data engineering?

A three-way fintech reconciliation matches three independent feeds — the source system (your app/gateway), the internal ledger (your book of record), and the counterparty (Visa, ACH, SWIFT, exchange). For every aggregate (typically an external_txn_id), the engine produces one of three outcomes: MATCHED (all three agree within amount, time, and FX tolerance), HOLD (two agree, third is late — sits in suspense with a retry timestamp), or BREAK (three disagree, or hold expired). Each BREAK carries a break_reason naming which pair disagreed (SOURCE_VS_LEDGER, LEDGER_VS_COUNTERPARTY, etc.) so ops can investigate the outlier first. The break table itself is SCD Type 2 with opened_at, resolved_at, resolved_by, and resolution_notes for the audit trail. Full-precision DECIMAL(38, 18) on every amount and versioned FX rates via a fx_rate_history SCD Type 2 join round out the design.

How do you design an immutable audit trail for a payments ledger?

Three layers of enforcement. Append-only at the database. REVOKE UPDATE, DELETE ON ledger_events at the app-role level; a BEFORE UPDATE OR DELETE trigger raises an exception even on admin roles. The primary key is a BIGSERIAL seq so every event has a monotonic order. Hash-chain for tamper-evidence. Each row carries prev_hash (the previous row's curr_hash) and curr_hash = SHA-256(prev_hash || row_body), filled by a BEFORE INSERT trigger. A nightly verifier job walks the chain and alerts on any mismatch. WORM at the storage layer. A daily Parquet export lands in an S3 bucket with ObjectLockMode=COMPLIANCE and ObjectLockRetainUntilDate=+7 years. Not even the AWS root account can delete the object before retention expires. Together, these three layers give tamper-detection at every level — database, cryptography, and cloud storage — and the resulting audit trail data engineering design satisfies SOX 404, PCI-DSS v4, and MiCA evidence requirements.

What is the transactional outbox pattern and why do fintech pipelines need it?

The transactional outbox solves the dual-write bug — the situation where an app writes to a database and then publishes an event to a message bus (Kafka) in two separate calls, and a network partition leaves the two out of sync. The pattern: inside a single database transaction, write the business row and an outbox_events row. Because the two writes commit atomically, the outbox has a row if-and-only-if the business row does. A separate log-based reader (Debezium tailing Postgres WAL, or a polling worker) reads the outbox and publishes to Kafka, then marks the outbox row published. If Kafka is momentarily unavailable, the events wait durably in the outbox; when Kafka recovers, publication resumes without any lost writes. Combined with an Idempotency-Key handler at the API edge and a downstream unique constraint on external_txn_id, this gives exactly-once payments for the full path — API → DB → bus → consumer.

How do you prepare a fintech data pipeline for a SOX audit?

Six deliverables, bundled into a single evidence folder per reg-report per period. Data-flow diagram — one page per report, showing sources → transformations → output. Lineage export — OpenLineage RunEvents captured at every job run, with column-level lineage stored in Marquez / DataHub and dumped to WORM. Test outcomes — Great Expectations validation JSONs per run, with pass rate summarised per expectation per month. Approval trail — PR-by-PR review metadata proving segregation of duties (maker ≠ approver, at least one checker), captured from the git-provider API and archived. Change log — every PR that touched the reg-report pipeline for the period, with author, reviewers, approvers, and merge timestamps. Incident log — every SLA breach with root cause and remediation. All six bundle into s3://reg-evidence-worm/{report}/{period}/ with Object Lock retention matching the filing retention floor (7 years for SOX). At year-end, the auditor request becomes a folder handover instead of a code-archaeology exercise.

Practice on PipeCode

• Drill the SQL practice library → for the recon-join and audit-query family of probes.
• Rehearse on joins problems → when the interviewer wants three-way match SQL depth.
• Sharpen the aggregation axis with aggregation drills → for daily-position and sum-to-zero patterns.
• Stack window-functions problems → for as-of and running-balance queries.
• Layer slowly-changing-data drills → for SCD Type 2 balance and history tables.
• Stack data-validation problems → for the Great Expectations mindset.
• Sharpen streaming problems → for the CDC + outbox + exactly-once path.
• For the broader surface, read top data engineering interview questions →.
• Stack the prerequisites with the only 5 skills you need to become a data engineer →.
• Sharpen the batch axis with the Apache Spark internals course →.
• For ETL system design, work through the ETL system design course →.

Pipecode.ai is Leetcode for Data Engineering — every reconciliation, audit-trail, idempotency, and reg-report recipe above ships with hands-on practice rooms where you wire the three-way match SQL, the hash-chained ledger, the transactional outbox handler, and the Great Expectations suite against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `fintech data engineering` answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Recon join drills →