Change Data Capture Into Snowflake From Postgres and MySQL

Any team that has tried to build change data capture by polling source tables on a schedule has already discovered why the approach fails. A row that was inserted and then updated twice before the next poll appears in the next poll as a single, flattened change, with the intermediate states gone for good, because polling misses whatever happened between one query and the next. It also puts a recurring read load on a production database that has better things to do, and without extra bookkeeping columns to track what changed, a poll-based system cannot even reliably tell an update apart from an insert. None of this is a tuning problem. It is a structural limit of asking a table what changed instead of reading the record of the changes themselves.

That record already exists inside every transactional database, and it is the only place a CDC pipeline should start. PostgreSQL keeps it in the Write-Ahead Log, MySQL keeps it in the binary log, and both record every committed change in the order it happened, before any downstream tool gets involved. Snowflake's Openflow engineering blog frames the task in those terms directly: building CDC means tapping into each database's own transaction log, speaking that database's particular replication protocol, streaming the resulting changes without dropping them, handling schema evolution as it happens, and maintaining exactly-once delivery semantics on the way in. Reading from the WAL or binlog also costs the source database far less than repeated polling, since the pipeline consumes log segments that have already been written. Everything that follows, the Postgres-specific mechanics, the MySQL-specific mechanics, the latency budget, the schema handling, the delivery guarantees, builds on that single starting point.

PostgreSQL logical replication mechanics and required capture configuration

PostgreSQL does not expose its WAL to external consumers by default. Capturing from it requires logical decoding, and logical decoding requires three configuration steps completed in the right order, before an external tool can read a single changed row. First, the server's wal_level must be set to logical rather than the default replica setting, which tells Postgres to retain enough information in the WAL to reconstruct row-level changes. Second, a replication slot must be created, which gives the connector a durable position to read from and signals to Postgres that it needs to hold onto WAL segments until that position advances. Third, a publication must be defined, naming which tables the slot's consumer is allowed to see.

The publication syntax itself is simple once those prerequisites are in place. Snowflake's Openflow blog shows the pattern as CREATE PUBLICATION snowflake_publication FOR ALL TABLES, or narrowed to particular tables with ALTER PUBLICATION … ADD TABLE, and the connector's own ingestion settings accept either an explicit list of table names or a regex pattern to match them. SquareShift's setup documentation describes the same mechanism from the operational side: the publication captures changes on the tables it names and writes them into the replication slot, where the connector monitors and consumes them.

The replication slot is what makes the whole arrangement reliable, and the risk lives there too. A slot guarantees that PostgreSQL will not discard WAL segments the connector hasn't yet consumed, which is exactly the property CDC needs. But if the consumer stops running, whether it crashes, loses network connectivity, or simply falls behind, Postgres keeps retaining WAL indefinitely on that slot's behalf, and the disk backing the database fills up. Streamkap's analysis of this failure mode calls it a critical operational risk and recommends continuous monitoring of replication slot lag as a baseline operational practice. PostgreSQL 13 added a direct mitigation, the max_slot_wal_keep_size parameter, which caps how much WAL a slot can force the server to retain. Teams running CDC against Postgres should set that parameter deliberately rather than leaving retention unbounded, since an idle or crashed consumer is a matter of when, not if.

Even with all three steps configured correctly, logical decoding has a blind spot that configuration cannot close. The external consumer reading from the slot has no visibility into Postgres's internal state beyond the stream of decoded records it receives. It cannot tell, from the stream alone, when a schema change has occurred, how a snapshot taken for an initial load lines up with the ongoing change stream, or whether Postgres is still running versus the network connection has simply gone quiet. Snowflake's data mirroring engineering post identifies this as a core limitation of pull-based logical decoding architectures generally, and it sets up the schema evolution problem that later sections return to.

MySQL binlog replication versus PostgreSQL WAL

MySQL's binary log plays the same foundational role that the WAL plays in Postgres: it records every committed transaction, at the statement or row level depending on configuration, and it is the authoritative source a CDC pipeline reads from. But the binlog is not a WAL with different syntax. It runs under its own configuration model and its own replication protocol, and a tool built to capture change data from both databases is really maintaining two separate implementations under one product name. SquareShift's documentation and Snowflake's Openflow blog both describe this directly: MySQL CDC means reading binlog events over MySQL's replication protocol, while PostgreSQL CDC means reading WAL through logical decoding, and a connector has to speak each protocol natively. Openflow's own architecture reflects that convergence at the product level even while the protocols diverge underneath it, describing its MySQL binlog connector as built on the same underlying architecture as its PostgreSQL connector.

In table-level filtering, one concrete difference occurs immediately. PostgreSQL's publication model puts the decision in the hands of the operator at the source: a publication explicitly names which tables are included, and nothing outside that list reaches the replication slot. MySQL's binlog does not work that way. It captures all transactions across the server by default, and the server-side filtering options available, binlog_do_db and binlog_ignore_db, operate at the database level rather than giving the fine-grained, per-table control Postgres offers natively. That gap gets pushed down to the connector. That is why Openflow's MySQL connector configuration includes Included Table Names, Included Table Regex, and column-level include and exclude filters: those settings exist specifically to do the filtering job that MySQL's binlog mechanism does not do on its own.

The other meaningful divergence is durability. Postgres's replication slot is a server-side promise: WAL segments stay on disk until the consumer confirms it has processed them, which is also why an idle slot can fill a disk. MySQL's binlog has no equivalent mechanism. There is no built-in handshake that holds log segments hostage to a consumer's progress. Instead, resilience depends on the connector itself tracking its position precisely, the binlog file name and byte offset it has read up to, combined with the source server's own log retention settings keeping those files around long enough for the connector to catch up after an outage. That shifts more of the durability burden onto the connector's bookkeeping and the source server's retention configuration, and less onto a server-enforced guarantee.

The four-stage timeline from write to Snowflake

Every CDC pipeline moving data from Postgres or MySQL into Snowflake passes through the same four stages in sequence, and the total time a change takes to become visible in Snowflake is the sum of all four, not whichever single number a vendor chooses to advertise. Snowflake's data mirroring engineering post names them explicitly. Write is the moment the transaction commits on the source database and lands in its WAL or binlog. Decode is the translation of that raw log record into a row-level insert, update, or delete operation that downstream systems can interpret. Capture is the batching of those decoded changes and their delivery to whatever destination store sits between the source and Snowflake. Apply is the final step, where those batches get merged into queryable Snowflake tables.

Capture latency is the figure most commonly quoted in product marketing, but apply latency is the number that determines what an analyst actually sees when they query a table, and the distance between the two can be substantial. The apply cadence, how often Snowflake actually merges captured changes into tables a query can read, is the real measure of data freshness for anyone downstream, and it tends to sit buried in product documentation rather than stated up front the way capture latency is. Teams building SaaS products or analytical layers on top of a CDC pipeline should negotiate and contract on apply latency specifically, since that number determines how stale a user-facing dashboard is, whether it reflects a change from ten seconds ago or ten minutes ago.

Streamkap's architecture comparison lays out how that latency budget shifts depending on which pattern a pipeline uses to move data between capture and Snowflake. Routing the change stream through a durable message bus like Kafka before it reaches Snowflake adds a modest amount of latency compared to a more direct path, and it adds real operational complexity in exchange, but it buys the ability to replay the stream and fan it out to multiple independent consumers. Batch ETL, where a system runs periodic full or incremental queries against the source instead of consuming a log stream continuously, sits at the opposite end of both axes: latency stretches from minutes to hours, operational complexity is the lowest of the three patterns, and the approach cannot capture intermediate row states any more than naive polling can, for the same underlying reason. Choosing among these patterns means choosing a point on that latency-versus-complexity curve deliberately, with the apply stage, not the capture stage, as the number that matters to whoever is reading the data on the other end.

Schema evolution as the primary cause of pipeline fragility

Column additions, renames, drops, and type changes cause more CDC pipeline failures in production than any other single factor, because a consumer decoding a WAL or binlog record has to understand what that record meant at the moment it was written. A record written against last week's schema and decoded against today's schema is a correctness problem waiting to surface, and it is the main reason CDC pipelines that ran fine for months break after an otherwise routine migration.

Pull-based logical decoding in PostgreSQL carries a structural exposure to this problem. The external consumer reading from a replication slot has no independent way of knowing when a DDL change has occurred on the source, so it can attempt to decode a binary WAL record using a schema definition that no longer matches the format the record was actually written in. Snowflake's mirroring engineering post explains the mechanism Postgres itself uses to avoid this internally: logical decoding relies on a "historic snapshot," meaning it reads the catalog tables as they existed at the exact moment a given WAL record was written, which lets Postgres decode that binary record correctly even after the table's structure has since changed. This correctness holds only when the external system consuming the replication stream implements that historic-snapshot logic faithfully, and most pull-based consumers implement it only partially.

Push-based capture handles the same problem from the other direction, by moving the capture logic inside the database. When the capture logic runs as a database extension, it has direct access to catalog state at the exact moment of the write, rather than having to reconstruct that state after the fact from outside the system. Snowflake's snowflake_cdc extension, used in its data mirroring product and currently in public preview, works this way: it pushes changes from inside Postgres directly into Iceberg change logs, coordinating schema changes alongside complex DDL and DML transactions from within the database. Because schema changes travel through the same write, decode, and capture path that ordinary data changes follow, they arrive correctly sequenced in the stream even in cases where a DDL statement and a DML statement occur inside the same transaction.

Pull-based connectors solve the same problem after the fact, through automatic adaptation. Openflow's connector detects DDL changes as they appear in the stream and adjusts its decoding accordingly, which works well under normal operation. That approach depends on a sequencing assumption: the connector has to actually observe the DDL event before it processes the DML that follows it. Under heavy load, or after a connector restart that resumes mid-stream, that ordering assumption can fail, and that failure mode is the practical cost of handling schema evolution downstream of the write.

Delivery guarantees for Snowflake targets and deduplication

Deduplicating individual rows and preserving transactional consistency are two different guarantees, and the second is harder to achieve than the first. A pipeline can successfully deduplicate every row it delivers and still present Snowflake users with a view of the database that no single consistent moment in time ever actually showed, if a multi-row transaction on the source gets split across separate apply batches on the way in. A transaction that updates three related rows atomically on Postgres or MySQL needs to arrive and apply atomically on the Snowflake side too, or the table a user queries midway through that apply process reflects a state the source database never actually had.

Snowflake's mirroring engineering post addresses this directly as a design requirement rather than an incidental feature: data mirroring pushes changes through the pipeline in transactional batches, and those batches are applied transactionally and serverlessly inside Snowflake, so the transaction boundaries established on the source database are preserved all the way through to the destination table. That is what distinguishes a pipeline that merely avoids duplicate rows from one that guarantees exactly-once delivery semantics end to end. Exactly-once delivery into Snowflake comes from coordinating the write, decode, capture, and apply stages together, with transaction boundaries carried through every stage.