High-Concurrency Financial Reconciliation Pipeline
High-transaction financial data, ingested in batches of up to 50 thousand records by a hybrid pipeline: a near real-time API lane driven by a high-water mark, and a daily lane that downloads a CSV and anti-joins it against the database to recover gaps invisible to the API (pagination offset drift). Extraction runs on a producer-consumer architecture with a bounded queue, and checkpoint state lives per page, not per day, resuming from the exact point of interruption after any failure. Historical backfill runs in a strictly sequential procedural process, isolated from the production ingestion.
Case Study
Problem
The source system returned inconsistent timestamps, omitted critical metadata, and suffered pagination offset drift, with records silently disappearing or duplicating. Real-time ingestion, running every minute, kept colliding with the historical load in lock contention, exhausting the connection pool and stalling writes.
Solution
A hybrid model: the API covers near real-time ingestion via a high-water mark, and a daily routine downloads the consolidated CSV report and runs an anti-join against the database to fill exactly the gap the API missed. The producer-consumer architecture, with a bounded queue, decouples the HTTP call from the database write; checkpoint state is saved per page in JSONB, so a failure resumes from the exact point. Backfill runs in a sequential procedural process, one day at a time, single session, which removes the race condition a concurrent approach would cause. New indexes are built with CONCURRENTLY so they never block production writes, and loading runs through COPY, a temp table and UPSERT, with in-memory deduplication by primary key before every batch of up to 50 thousand records.
Impact
Full historical backfill of the highest-volume asset since the source's inception, with the CSV layer recovering on its own the gap the API alone would never capture. The pipeline served as the transition ledger of the financial system until the full migration to the definitive platform, and was decommissioned once it had served its purpose.
Need to reconcile high-transaction financial data without losing a record? Let's design that pipeline for your context.
Discuss your case