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. Twice, across two separate healthcare engagements, for two different source database stacks. Each build informed the other. 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.

Engagement One: MySQL, Two Servers, One Hard Problem

The first 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.

Engagement Two: MySQL and PostgreSQL, Dozens of Sources, a Cleaner Division of Labor

The second engagement was a six-month embed with a hospital operations management company — a platform serving tens of millions of patient encounters per year, with a couple dozen source databases: one sharded instance per hospital, a mix of MySQL and PostgreSQL.

The division of labor was different here. The binlog capture layer — Maxwell for MySQL, logical replication for PostgreSQL — was owned by another team. The Snowflake side was the engagement scope. That boundary turned out to matter: not owning the capture layer meant not being exposed to its failure modes. The pipeline ran more cleanly than the first one had, not because the problem was easier, but because the failure surface was narrower.

More significantly, this engagement was where the Snowflake Streaming API replaced S3 and Snowpipe as the ingest mechanism. The Streaming API delivers change events directly into Snowflake streams without the S3 intermediary, with stronger ordering guarantees and lower latency. The operational simplicity was immediately apparent — fewer moving parts, no S3 lifecycle management, no Snowpipe credit monitoring. The tradeoff was tighter coupling to Snowflake’s own infrastructure, which in this context was acceptable.

The unification story here was also meaningful. Each hospital shard had been an analytical island. Consolidating them into a single Snowflake environment through a common CDC pipeline made cross-hospital analytics possible for the first time — operational dashboards that spanned the network rather than being scoped to a single facility.

Bringing It Back: Gen 2 at the First Engagement

The lessons from the second engagement directly informed a rebuild of the first pipeline. The Streaming API replaced S3 and Snowpipe. 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 at the managed care engagement (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 the open-source tooling (Maxwell, PostgreSQL logical replication) 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