Skip to content
AM
ENRU
← All posts

Streaming financial transactions with CDC, Kafka and Spark

Published · 6 min read

Finance teams don't want data that is fast. They want data that is right, and then as fresh as possible. This post is about a pipeline that had to be both: change data capture from a transactions database into a lakehouse and a warehouse, for a client where a duplicated or missing transaction is not a rounding error.

At a glance

  • Context: a fintech client
  • My role: designed the pipeline and led a team of three engineers through the build
  • Scale: millions of transactions a day
  • Result: end-to-end latency down by about 60%, from roughly 15 minutes to about 6
  • Stack: PostgreSQL, a Python CDC service, Kafka, Spark Structured Streaming, Delta Lake on S3, Airflow, dbt, Amazon Redshift, FluentD, Elasticsearch and Kibana

The problem

The client kept its transactions, accounts and merchants in PostgreSQL. Reporting ran on scheduled extracts every 15 minutes, full-table for small tables and by updated_at for large ones. That worked until it didn't:

  • The finance and risk teams were always behind. Dashboards lagged the business by the extract interval plus the load, and anomaly checks ran on data that was already old.
  • Extracts loaded production. Each run queried the primary, and the queries got heavier as the tables grew.
  • Deletes and intermediate states were invisible. Polling on updated_at sees the latest version of a row, not every change, and never sees a hard delete. In finance, a reversed transaction that silently disappears is exactly the kind of thing auditors ask about.

Constraints

Writing these down first shaped every later decision:

  1. No loss, no double counting. A payment counted twice is worse than a dashboard a few minutes late.
  2. Don't hurt the primary. The source database served the product; analytics could not slow it.
  3. Keep the analysts where they are. They already worked in Redshift, with years of SQL and dashboards built on it.
  4. Sensitive fields stay contained. Card and personal data should never reach an analyst's query.
  5. Every number must be explainable later. If a figure is questioned in three months, we need to show how it was produced.

The architecture

Architecture in four stages. Capture: PostgreSQL write-ahead log, read through a logical replication slot by a Python CDC service that publishes insert, update and delete events, with Avro schemas in a Schema Registry. Transport and process: Kafka, one topic per table keyed by primary key, consumed by Spark Structured Streaming with checkpoints on S3. Lakehouse on Delta Lake: Bronze raw events, Silver deduplicated and merged current state with quality checks and masking, Gold business models, and a quarantine table for failed records. Serve: Airflow and dbt build Gold and load Redshift, which feeds finance and risk dashboards and anomaly alerts. Monitoring covers every stage.

The highlighted boxes are where the delivery guarantees live: the CDC service, the Spark checkpoints and the Silver merge. Tap or click to open it full size.

In one sentence: a Python service reads every change from Postgres's write-ahead log and publishes it to Kafka; Spark lands the events, untouched, in a Bronze Delta table on S3; Silver turns them into clean current state; Gold holds the business models; and Redshift serves them to the people who already lived there.

Capture

The CDC service reads the write-ahead log through a logical replication slot. Each committed change becomes an event with the operation, the row before and after, and its log sequence number (LSN). The service only confirms its position in the log after Kafka has acknowledged the event, so a crash can never skip a change.

Why a custom Python service instead of Debezium? The client didn't allow a Kafka Connect cluster in that network, and sensitive fields had to be tokenized before any event left it. Today Debezium would be my default; I'd write custom CDC only for a constraint like that.

Transport

Kafka holds one topic per table, keyed by primary key. All changes to one account or one transaction land in the same partition, in order. Global order across every record isn't needed and would cost throughput. Schemas live in a Schema Registry as Avro with backward compatibility, so a new nullable column flows through without breaking anyone, and a breaking change has to be planned.

Process and land

Spark Structured Streaming runs on Amazon EMR and reads Kafka in micro-batches every 30 seconds. It appends events to Bronze exactly as received. Offsets and the Delta commit move together through checkpoints on S3, so a restarted job neither skips nor double-writes a batch.

Silver is where events become state. It deduplicates by primary key and LSN, applies inserts, updates and deletes with MERGE, runs quality checks and masks sensitive fields. Records that fail a check, a missing currency or a negative amount where none is allowed, go to a quarantine table with the reason instead of stopping the stream.

Gold holds what the business reads: facts and dimensions, settlements, and features for anomaly detection.

Serve

Airflow and dbt build the Gold models and load them into Redshift with COPY, and handle backfills and reruns. Analysts kept their warehouse and their dashboards; what changed was how fresh the data in it was.

Decisions and trade-offs

Every decision here cost something. These are the ones I'd defend first.

Log-based CDC instead of polling. Reading the log puts almost no load on the primary, captures every change including deletes and intermediate states, and delivers within seconds of commit. The cost: a replication slot holds write-ahead log on the primary until it's consumed. If the CDC service stops, the primary's disk fills. Slot lag is monitored and alerted on, with a cap on retained log as a last line of defence.

At-least-once delivery with idempotent merges, instead of exactly-once end to end. The CDC service confirms its position only after Kafka acks, so nothing is lost; a crash can resend events, so Silver merges by key and keeps the highest LSN, and a duplicate changes nothing. The cost: every downstream write has to be idempotent. In return the system is much simpler to reason about and to test.

An append-only Bronze as the record of truth. The source keeps only current state. Bronze keeps every change as it arrived, so a bug in Silver or Gold is fixed by replaying Bronze, never by re-extracting from production. It's also the audit trail a regulated business needs. The cost: storage grows forever unless old partitions move to cheaper tiers.

Quarantine instead of failing the stream. One malformed record shouldn't stop millions of good ones. The cost: someone has to own the quarantine queue, or it quietly fills up. Its size is a monitored metric with an owner.

Masking in Silver. Analysts never see raw card or personal data. The cost: Bronze still holds raw values, so it has the strictest access rules in the platform.

Redshift as the serving layer, next to the lakehouse. The lakehouse does replay, cheap storage and features for models; Redshift does fast, concurrent SQL for the people who already use it. The cost: Gold exists in two places. Querying Delta directly would remove one copy; at the time, keeping the analysts' tools unchanged mattered more.

What went wrong

Three weeks after launch the CDC service crashed on a Friday evening, on a numeric value its decoder didn't handle. It restarted, crashed on the same record, and kept doing so all weekend. Nothing was lost, because the replication slot kept every change, but that is exactly the problem: the primary kept every change too. By Sunday the write-ahead log had grown by about 300 GB and the disk alert on the primary fired at 80%.

The fix had three parts. The decoder learned the missing type, and anything it still can't decode now goes to a dead-letter topic instead of crashing the service. Slot lag got its own alert, set far below the point where disk becomes a risk. And max_slot_wal_keep_size now caps retained log, so the worst case is a replay from Bronze, not an outage of the product.

The lesson I took from it is broader than this one bug: in a CDC pipeline, the database you are reading from is part of your blast radius. Monitoring has to watch the source, not only the pipeline.

Results

  • Latency: dashboards went from about 15 minutes behind to about 6, a cut of roughly 60%.
  • Load: extract queries on the primary stopped entirely.
  • Correctness: daily row counts and checksums between source and Silver matched for every table after the first month.
  • Recovery: a logic bug became a replay, not an incident. We rebuilt Silver and Gold from Bronze twice in the first quarter without touching production.

What I'd do differently today

  • Debezium by default. Unless a constraint like the one above applies, I'd use a maintained connector and spend the time on the data instead.
  • Fewer copies. I'd try serving BI straight from Delta before adding a second warehouse.
  • Replay tests from day one. Kill the CDC service mid-stream, restart it, and assert zero loss and zero duplicates in Silver, in CI, on every change to the merge logic.
  • Data contracts with the source team. The schema rules worked, but they worked because people remembered them. A written contract with automated checks is cheaper than one more incident.

What I took away

Streaming is easy to draw and hard to trust. The arrows on the diagram are the easy part. The work is in the boring guarantees between them: when you acknowledge, how you deduplicate, where history lives, and who notices first when something stops.