Most organizations that need their operational data in Snowflake buy something to get it there. A Fivetran connector, a Debezium managed service, a commercial CDC appliance. The licensing cost is predictable, the sales pitch is clean, and the failure modes are someone else’s problem — until they aren’t.

This is the story of building it instead — and then rebuilding it, once the first version had taught us exactly where CDC breaks. The architecture that came out the other side handles 3.5 million change events per day at 15-second ingest latency, shares data live to four downstream Snowflake accounts, and costs roughly $65 per million events applied — with a flat cost curve regardless of how many tables you add.

Why CDC Into Snowflake

The core problem is straightforward. Operational databases — MySQL, PostgreSQL, the transactional systems that clinicians and operations staff actually use — are not built to be queried analytically. Running reporting workloads against them slows down the application. Scheduling nightly ETL jobs introduces a 24-hour lag. Neither is acceptable when the data is clinical.

Change data capture reads the database’s own transaction log — the binlog in MySQL, the WAL in PostgreSQL — and streams every insert, update, and delete into Snowflake as it happens. Done right, the result is a Snowflake environment that reflects the source system within seconds, never touches production query capacity, and preserves the full change history rather than just the current state.

Done wrong, it silently drifts. Ghost rows accumulate. Deleted records stick around. You don’t notice until a report doesn’t add up.

Gen 1: MySQL, Two Servers, One Hard Problem

The pipeline was built for a vertically integrated managed care company — a large primary care operator managing hundreds of thousands of patients across more than a hundred care centers. Their system of record was MySQL: two large, busy production servers covering the full clinical and operational footprint.

The initial architecture was straightforward: MySQL binlog → Maxwell’s Daemon → JSON stream → Amazon S3 → Snowpipe → a DAG of Snowflake tasks and stored procedures that reassembled the change stream into queryable tables. A layer of views over those tables pivoted the semi-structured representation into a normal tabular schema, so analysts could query the Snowflake tables with minimal modification to queries originally written for MySQL.

It worked. And then it accumulated problems.

Maxwell would occasionally fail to parse a malformed binlog segment and skip the file entirely — silently, without raising an alarm. The merge procedure that was supposed to apply deletes correctly had subtle bugs; deleted rows persisted as ghosts. The core procedure was rewritten five times over two years, each rewrite fixing one failure mode and occasionally introducing another. Correctness drift accumulated slowly enough that it took reconciliation queries to catch it.

The advantages of the approach, meanwhile, all materialized exactly as designed. Analytics queries moved off MySQL. Dashboards reflected clinical activity within minutes instead of overnight. The full change history was preserved. Schema evolution was handled in the view layer, not in application code. The DBA team eventually started using the Snowflake tables as a warm standby — a disaster recovery layer they hadn’t asked for but trusted enough to rely on. That organic adoption was the clearest signal that the data quality was real.

Gen 2: Rebuilding Around the Failure Modes

The Gen 1 failure modes drove a rebuild rather than another patch. The Snowflake Streaming API replaced S3 and Snowpipe as the ingest mechanism, delivering change events directly into Snowflake without the S3 intermediary, with stronger ordering guarantees and lower latency — and with fewer moving parts: no S3 lifecycle management, no Snowpipe credit monitoring. A bespoke Python CDC service replaced Maxwell — more resilient to malformed binlog segments, with configurable error handling rather than silent skips. The merge and apply logic was rearchitected rather than patched again.

The changes didn’t all happen at once. The Python service came first, then the Streaming API, then the final restructuring of the apply layer. Iterative hardening over time, each change addressing the specific failure mode that had motivated it.

The current architecture uses Snowflake serverless tasks for the apply layer and Snowpipe Streaming for ingest — both pay-per-use, both scaling with actual workload rather than provisioned capacity. An INSERT FIRST routing architecture means that adding a new source table has approximately zero marginal cost; the pipeline’s economics don’t grow with coverage.

Delete fidelity — historically one of the hardest CDC problems to get right — was specifically hardened: roughly 72,000 deletes per day flow through correctly, and an automated reconciliation guard now detects and prevents the kind of row-count drift that accumulated silently in Gen 1.

What It Looks Like at Scale

The production pipeline (point-in-time, June 2026):

Failures are self-healing by design: Snowflake streams do not advance their offset on a failed task execution, so the next run retries automatically and no data is lost.

The Build vs. Buy Question

The case for buying CDC tooling is real. Managed services handle operations, support contracts exist, and the time-to-first-data is shorter. For straightforward use cases with well-supported source systems, the economics often work.

The case for building is also real, and it gets stronger in proportion to how unusual your requirements are. Unusual source configurations, mixed database engines, a need for custom merge semantics, a requirement to own the data pipeline IP, or simply a scale at which licensing costs become material — any of these tips the balance.

What this work demonstrated is that a purpose-built capture service on the MySQL binlog, combined with the Snowflake Streaming API and serverless tasks is sufficient for production-grade CDC at clinical-data scale. The hard part isn’t the technology — it’s getting the merge semantics right, handling deletes correctly, and building the reconciliation layer that catches drift before it compounds. Those problems don’t go away with a commercial product. They just move to a different place in the stack.

← All case studies  ·  Engage me on similar work