Reconcile ledgers with SQL/Python and late events

You own a daily ETL + reconciliation job between two financial ledgers. Late postings (“delay time”) up to 48 hours are common. Schema:

• payments_raw(payment_id INT, user_id INT, amount DECIMAL(10,2), currency CHAR(3), created_at TIMESTAMP)
• ledger_a(txn_id STRING, user_id INT, amount_usd DECIMAL(10,2), posted_at TIMESTAMP)
• ledger_b(txn_id STRING, user_id INT, amount_usd DECIMAL(10,2), posted_at TIMESTAMP)
• fx_rates(rate_date DATE, currency CHAR(3), usd_rate DECIMAL(10,6)) -- USD per 1 currency unit Small ASCII samples:

payments_raw
payment_id | user_id | amount | currency | created_at
1 | 101 | 100.00 | USD | 2025-08-30 10:05:00
2 | 102 | 90.00 | EUR | 2025-08-30 16:40:00
3 | 101 | 50.00 | USD | 2025-08-31 23:30:00
4 | 103 | 100.00 | EUR | 2025-09-01 01:20:00

fx_rates
rate_date | currency | usd_rate
2025-08-30 | USD | 1.000000
2025-08-30 | EUR | 1.100000
2025-08-31 | USD | 1.000000
2025-08-31 | EUR | 1.120000
2025-09-01 | USD | 1.000000
2025-09-01 | EUR | 1.150000

ledger_a
txn_id | user_id | amount_usd | posted_at
A-1 | 101 | 100.00 | 2025-08-30 10:06:00
A-2 | 102 | 99.00 | 2025-08-30 18:00:00
A-3 | 103 | 115.00 | 2025-09-01 08:00:00

ledger_b
txn_id | user_id | amount_usd | posted_at
B-1 | 101 | 100.00 | 2025-09-02 11:00:00
B-2 | 101 | 50.00 | 2025-09-01 00:10:00
B-3 | 102 | 99.01 | 2025-08-30 18:02:00

Tasks:

1. Write a single SQL query that produces a reconciliation report at the payment level with columns: payment_id, user_id, amount_usd_from_fx, a_txn_id, b_txn_id, match_status (one of: matched, late_within_48h, late_beyond_48h, amount_mismatch, missing_in_a, missing_in_b), late_by_hours (NULL if not late), and abs_amount_diff. Rules: (a) Convert payments_raw.amount to USD using the fx rate effective on created_at’s date (use the most recent rate on or before created_at). (b) Attempt to match each payment to at most one txn from each ledger on same user_id, |amount_usd_from_fx − amount_usd| ≤ 0.01, and |posted_at − created_at| ≤ 48 hours for a normal match; if > 48 hours but otherwise matching, classify as late_beyond_48h and compute late_by_hours; if amounts within 0.01 in only one ledger, set the other as missing_in_*. If both ledgers match but amounts differ by > 0.01, classify amount_mismatch. Break ties by the smallest absolute time difference to created_at using window functions.
2. In Python, outline an idempotent daily ETL design that: (a) partitions by event_date (created_at::date), (b) maintains a rolling 72-hour backfill to catch late arrivals, (c) writes a reconciliation snapshot table with a deterministic primary key, and (d) guarantees exactly-once downstream effects. Mention how you would detect and merge late_beyond_48h fixes without duplicating rows.
3. Generate a discrepancy distribution chart. First, produce a pie chart of counts by match_status using matplotlib (or plotly). Then justify why a bar chart with percentages and 95% CIs is superior here and sketch how you’d produce it. Name the exact Python packages you’d use.
4. Defend Python vs R for this pipeline. Be specific about libraries (e.g., pandas/pyarrow/duckdb vs data.table/arrow/dbplyr), deployment/runtime concerns, and interoperability with the rest of a modern data stack.

Payment-level reconciliation across two ledgers with FX conversion and late postings

You own a daily reconciliation job between a raw payments table and two downstream financial ledgers (ledger_a and ledger_b). Late postings up to 48 hours can occur.

Write a SINGLE SQL query that outputs a payment-level reconciliation report with these columns:

• payment_id
• user_id
• amount_usd_from_fx
• a_txn_id
• b_txn_id
• match_status (must be one of: matched, late_within_48h, late_beyond_48h, amount_mismatch, missing_in_a, missing_in_b)
• late_by_hours (NULL if not late)
• abs_amount_diff

Rules:

1. Convert payments_raw.amount to USD using fx_rates.usd_rate effective on created_at's DATE.
   • Use the most recent fx_rates row where rate_date <= created_at::date for the given currency.
2. For each payment, attempt to match AT MOST ONE transaction from each ledger (A and B) using:
   • same user_id
   • ABS(amount_usd_from_fx - ledger.amount_usd) <= 0.01
   • If multiple candidate transactions match for a payment within a ledger, break ties by choosing the transaction with the smallest absolute time difference between posted_at and created_at.
3. Classification:
   • If a match exists in both ledgers and ABS(a.amount_usd - b.amount_usd) > 0.01 => amount_mismatch
   • Else if both ledgers matched and max(|posted_at - created_at| in hours) > 48 => late_beyond_48h and set late_by_hours
   • Else if both ledgers matched and max(|posted_at - created_at| in hours) > 24 and <= 48 => late_within_48h and set late_by_hours
   • Else if both ledgers matched => matched
   • Else if only ledger_a matched => missing_in_b
   • Else if only ledger_b matched => missing_in_a
4. abs_amount_diff:
   • If both ledgers matched: ABS(a.amount_usd - b.amount_usd)
   • If only one ledger matched: ABS(amount_usd_from_fx - matched_ledger.amount_usd)

Return one row per payment_id.

Table payments_raw

column	type	constraints
payment_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
currency	CHAR(3)	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

amount	created_at	currency	payment_id	user_id
100	2025-08-30 10:05:00	USD	1	101
90	2025-08-30 16:40:00	EUR	2	102
50	2025-08-31 23:30:00	USD	3	101
100	2025-09-01 01:20:00	EUR	4	103

Table fx_rates

column	type	constraints
rate_date	DATE	NOT NULL
currency	CHAR(3)	NOT NULL
usd_rate	DECIMAL(10,6)	NOT NULL

Sample data:

currency	rate_date	usd_rate
USD	2025-08-30	1
EUR	2025-08-30	1.1
USD	2025-08-31	1
EUR	2025-08-31	1.12
USD	2025-09-01	1
EUR	2025-09-01	1.15

Table ledger_a

column	type	constraints
txn_id	VARCHAR(20)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
amount_usd	DECIMAL(10,2)	NOT NULL
posted_at	TIMESTAMP	NOT NULL

Sample data:

amount_usd	posted_at	txn_id	user_id
100	2025-08-30 10:06:00	A-1	101
99	2025-08-30 18:00:00	A-2	102
115	2025-09-01 08:00:00	A-3	103

Table ledger_b

column	type	constraints
txn_id	VARCHAR(20)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
amount_usd	DECIMAL(10,2)	NOT NULL
posted_at	TIMESTAMP	NOT NULL

Sample data:

amount_usd	posted_at	txn_id	user_id
100	2025-09-02 11:00:00	B-1	101
50	2025-09-01 00:10:00	B-2	101
99.01	2025-08-30 18:02:00	B-3	102

Expected output

One row per payment. Includes USD amount derived from FX, best matched txn_id from each ledger (if any), a status classification, lateness in hours for late_* statuses, and an absolute amount difference as defined in the rules.

a_txn_id	abs_amount_diff	amount_usd_from_fx	b_txn_id	late_by_hours	match_status	payment_id	user_id
A-1	0	100	B-1	72.92	late_beyond_48h	1	101
A-2	0.01	99	B-3	NULL	matched	2	102
NULL	0	50	B-2	NULL	missing_in_a	3	101
A-3	0	115	NULL	NULL	missing_in_b	4	103

Hints

1. Use ROW_NUMBER() to pick the most recent FX rate (rate_date <= created_at::date) per payment.
2. Generate candidate matches per ledger and use ROW_NUMBER() to pick the closest posted_at to created_at (smallest absolute time difference).
3. Do status classification in a final CASE expression after you have the best match (or NULL) from each ledger.