Moving data from an operational database into an analytical warehouse sounds simple: copy the existing rows, then forward every insert, update, and delete. The difficulty appears as soon as the source stays online.

A snapshot takes time. Writes continue while it runs. Tables are renamed, columns are added, networks fail, processes restart, and consumers may replay a batch after an uncertain acknowledgement. A pipeline that merely reads the Postgres write-ahead log (WAL) has to reconcile all of these events without losing data or exposing a destination state that never existed at the source.

Snowflake’s new data-mirroring design takes an unusual route. Instead of running a connector that continuously pulls a logical-decoding stream over the network, a Postgres extension pushes transactional change batches into Apache Iceberg tables in object storage. Snowflake later applies those batches to analytical tables in separate transactions. A metadata log connects the two sides.

The product is specific to Snowflake Postgres and is currently in public preview, but the architecture is more broadly useful. It shows how moving capture closer to the source, using durable storage as a handoff boundary, and preserving database transaction boundaries can remove whole categories of CDC failure.

Why Ordinary CDC Becomes Fragile

Postgres already provides the essential capture primitive. Logical decoding translates physical WAL records into a stream of row-level changes. A replication slot remembers how far a consumer has progressed, and an output plugin decides how those changes are represented.

That stream is necessary, but it is not a complete replication system.

An external consumer still has to answer difficult questions:

• Where does a table snapshot end and the change stream begin?
• What happens to writes made while the snapshot is running?
• How should DDL be ordered relative to DML in the same transaction?
• If an acknowledgement is lost, should a batch be replayed?
• How are new and removed tables handled?
• Can several destination tables advance to one consistent source point?
• What happens when a replication slot disappears after failover?

The external process also has an incomplete view of the source. A silent connection may mean Postgres is busy, the network is partitioned, the connector is stuck, or the server has failed. Schema state visible now may differ from the state that was valid when an older WAL record was written.

Many systems cope with uncertainty by making destination writes idempotent. Every source operation becomes an upsert keyed by a primary key, and retries are considered safe. That is practical, but expensive on large columnar tables: each insert may require a lookup or scan to prove that the row does not already exist. It also does not automatically solve cross-table consistency or the snapshot-to-stream boundary.

Push Capture into the Database

Snowflake’s design moves the capture coordinator into Postgres as a background extension called snowflake_cdc. The extension uses logical decoding, but it does not expose an endless stream that an external connector must keep open. It writes finite, durable batches into per-table Iceberg change logs and records instructions in a separate metadata log.

This changes the relationship between producer and consumer.

Postgres knows when transactions commit, when schemas change, which catalog version belongs to a WAL record, and when a snapshot aligns with ongoing writes. The extension can coordinate capture with that local state. Object storage then decouples capture from apply: Snowflake does not need to be available at the exact moment Postgres produces a batch, and Postgres does not wait for a warehouse merge to finish.

The handoff looks like this:

The object store is not merely a queue replacement. Iceberg supplies table metadata and atomic commits over immutable data files. Compressed Parquet keeps the handoff columnar and inexpensive to scan. Because the durable change log exists independently of either execution process, capture and apply can restart from explicit checkpoints rather than infer state from a live connection.

One Timeline, Four Positions

A useful way to reason about this system is as four processes moving along one database timeline:

1. Write changes Postgres tables and appends WAL at the current point.
2. Decode reads older WAL and reconstructs logical row changes.
3. Capture commits finalized batches and metadata to Iceberg.
4. Apply advances Snowflake tables to a completed source boundary.

All four run continuously, but each is at a different log sequence number (LSN). Lag is the distance between those positions, not one vague end-to-end number.

Logical decoding uses historic catalog snapshots to understand WAL records using the schema that existed when the write occurred. That detail matters. If a column is dropped after a transaction commits but before its WAL is decoded, interpreting the old record through the current schema could produce nonsense.

The decoder first writes temporary batch files. At a boundary, it finalizes the files and informs the capture worker. Capture appends them to the appropriate Iceberg change logs, records the operation in the metadata log, and advances the captured LSN. Schema changes travel through the same ordered path and may start a new change-log generation.

The metadata log is effectively a replay program. Snowflake’s apply process reads it as a finite-state machine: create a target, install a snapshot, apply adjacent change batches, change a schema, add or remove a table, or recover by replacing state with a newer snapshot.

This explicit instruction stream is stronger than trying to reconstruct global order independently from per-table feeds.

Snapshots and Changes Must Share a Boundary

Initial loads are one of the most failure-prone parts of CDC. A table may contain billions of rows, so a snapshot can run for hours while fresh writes continue.

A naive sequence loses data:

1. Start copying the table.
2. Finish the copy.
3. Begin reading changes.

Writes committed between steps one and three may fall into a gap. Starting the change stream first avoids the gap but creates overlap: changes already reflected in the snapshot can be replayed again.

The mirroring extension can take a Postgres snapshot while decoding changes and record exactly how the two relate. The destination installs the snapshot and begins applying only the correct subsequent batches. If an unrecoverable capture gap appears, the metadata protocol can instruct the destination to consume a replacement snapshot instead of asking an operator to repair an ambiguous stream by hand.

This same mechanism handles a table added after mirroring has already started. The new table receives its own aligned snapshot and then joins the continuous change timeline without pausing every other table.

Transactions Are the Recovery Protocol

Distributed pipelines often accumulate checkpoints, deduplication keys, retry ledgers, and compensating jobs because they lose the strongest primitive their databases already provide: transactions.

This design uses transactions on both sides of the durable handoff.

On the source side, pg_lake can commit data and metadata changes to multiple Iceberg tables as one Postgres transaction. On the destination side, Snowflake can apply several adjacent batches across multiple target tables in one transaction. The target advances to a known Postgres transaction boundary or does not advance at all.

That has two important consequences.

First, retry logic becomes simpler. If a transaction failed, repeat it. If it committed, the checkpoint proves which work is complete. There is no half-applied batch to reverse.

Second, readers of materialized target tables do not observe broken cross-table invariants. If an order and its line items were committed together in Postgres, they become visible together in Snowflake. Joins do not see the order at one source position and the line items at another.

This is not globally synchronous replication. Postgres commits before Snowflake applies the batch, so analytical data still lags. The guarantee is about a consistent historical boundary, not zero latency.

Delete and Append Beat Universal Upserts

Conventional CDC commonly translates all operations into key-based upserts. That gives replay tolerance, but it is a poor default for an append-heavy workload landing in columnar storage.

The mirroring system can emit exact delete and insert records because snapshot overlap and replay position are already controlled. Updates become a delete/insert pair. Pure inserts can be appended without scanning a large target table for matching keys.

Snowflake can also combine multiple pending batches before applying them. This amortizes fixed work and allows a larger refresh interval to reduce compute cost. The trade-off is a less frequently updated materialized table.

The architecture separates freshness from compaction through live views. Each mirrored table has three useful forms:

• the materialized target table, updated on the configured apply interval
• a $changes Iceberg table containing recent captured operations
• a $live view that overlays unapplied changes on the target

The live view can surface changes at roughly the auto-refresh cadence—documented as about 30 seconds—without running full target-table apply that often. Filters and projections can be pushed into scans of both the base table and Parquet change files, so queries do not always pay to process the entire backlog.

There is an important limitation: Snowflake documents $live views as non-transactional. Change files arrive incrementally, so a live query can observe only part of a multi-table source transaction. Use the materialized target tables when cross-table transactional consistency matters. Use live views when lower lag is worth weaker read semantics.

Failure Recovery Is Still Operational Work

The architecture removes infrastructure, not responsibility.

Logical replication slots must be healthy. A stalled consumer can force Postgres to retain WAL, filling storage. Modern Postgres can synchronize failover-enabled logical slots to standbys, but the synchronization is asynchronous and must be verified before promotion. If required WAL is lost anyway, the system needs a new aligned snapshot.

Operators should monitor at least four positions:

• current source WAL LSN
• decoded LSN
• captured LSN committed to Iceberg
• applied source boundary in Snowflake

Those measurements locate the bottleneck. A growing write-to-decode gap suggests pressure inside Postgres. A decode-to-capture gap points to batching or object-storage writes. A capture-to-apply gap points to Snowflake scheduling, apply capacity, or cost settings.

Storage needs equal attention. Change logs, metadata, snapshots, and retained WAL are durable by design; durability becomes a bill and, without retention controls, a capacity risk. The mirror’s serverless apply jobs and Iceberg storage should be attributed per pipeline rather than hidden inside one platform total.

Schema Evolution Needs Explicit Semantics

“Supports schema changes” is not a sufficient contract. Teams need to know what each operation means at the destination.

Adding a nullable column is relatively simple. Dropping or renaming one can be harder because old change files still use the previous layout. Type changes may be binary-compatible, require a cast, or make replication impossible. A primary-key change affects how future deletes identify old rows.

The ordered metadata log lets DDL share the same timeline as DML, but it cannot make incompatible types compatible or infer business intent. Production evaluation should test the exact migration patterns an application uses:

• add, backfill, and constrain a column
• rename a column while writes continue
• widen and narrow numeric or text types
• change primary or replica identity keys
• create, truncate, detach, and drop tables
• roll back an application release after its migration has replicated

Treat unsupported DDL as a planned stop condition with an alert and recovery procedure, not an edge case to discover during deployment.

What Is Open and What Is Product-Specific

The system uses open building blocks: Postgres logical decoding, Parquet, Iceberg, and the Apache-licensed pg_lake extension. That does not make the complete mirroring feature portable.

The HN discussion highlighted that the dedicated CDC extension and Snowflake apply machinery are not part of the public pg_lake repository. Data mirroring is tied to Snowflake Postgres and Snowflake’s transactional apply implementation. It is also a preview feature, so availability, behavior, limits, and pricing can change.

That distinction matters when evaluating lock-in. Writing changes to open Iceberg tables improves inspectability and creates a useful architectural boundary, but end-to-end recovery semantics live in the producer extension, metadata protocol, and consumer state machine—not in the file format alone.

A Design Checklist for Any CDC System

Whether you use this product, another managed service, or build an internal pipeline, ask the same questions:

1. Snapshot boundary: How are concurrent writes aligned with the initial copy?
2. Transaction boundary: Can related tables advance atomically?
3. DDL ordering: Is schema state versioned with data changes?
4. Durable handoff: Can producer and consumer restart independently?
5. Replay rule: What proves that a batch was applied exactly once?
6. Failover: Do logical slots survive primary promotion, and how is readiness verified?
7. Backpressure: What fills first when the destination stops—WAL, local disk, object storage, or memory?
8. Freshness modes: Are low-lag reads weaker than materialized-table reads?
9. Cost curve: How do batch size, apply interval, and live-query frequency affect spend?
10. Exit path: Which components and data remain usable outside the vendor’s control plane?

The best CDC system is not the one with the smallest demo lag. It is the one whose behavior remains understandable during snapshots, schema changes, retries, failovers, and long outages.

The Durable Idea

The most valuable idea in this design is not “put CDC inside Postgres” by itself. It is assigning each concern to the layer with the best information.

Postgres owns transaction order, historic schema context, and snapshot alignment. Object storage owns a durable, scalable boundary between systems. Iceberg owns atomic table metadata over columnar files. Snowflake owns the expensive work of mapping row changes into analytical tables. Transactions on both sides turn retries into normal control flow instead of incident response.

That division does not eliminate trade-offs. Live views weaken consistency, preview software carries product risk, replication slots still need monitoring, and the complete system is vendor-specific. But it replaces an opaque, permanently connected process with an ordered sequence of durable state transitions.

For replication, clockwork does not mean nothing can fail. It means every component knows which tick completed and which one must run next.

Sources

• How we pushed CDC into Postgres, and turned replication into clockwork
• Snowflake Postgres data mirroring documentation
• Query mirrored data and live views
• PostgreSQL logical decoding documentation
• PostgreSQL logical replication failover
• pg_lake source repository
• Hacker News discussion