Snowflake Postgres Data Mirroring Now Generally Available for Modern CDC

How Data Mirroring Works

Data mirroring replicates specified tables and schemas from a Snowflake Postgres instance into a target Snowflake database. The setup can be easily managed through the Snowflake Postgres UI, SQL commands, or via Snowflake CoPilot. Unlike traditional replication solutions, data mirroring is integrated directly into both Postgres and Snowflake, leveraging object storage as an intermediary without requiring additional components. This method ensures secure, reliable, and performant data transfer while maintaining a queryable change history.

At its core, Postgres pushes snapshots and small batches of change records for each table into Apache Iceberg™ change logs stored in object storage, facilitated by a snowflake_cdc extension. Snowflake then employs a periodic serverless task to apply these changes to regular Snowflake or Snowflake-managed Iceberg tables. For immediate access to the latest data, a $live view is available, which combines mirrored tables with pending changes to offer low lag, irrespective of the apply task's frequency. The entire pipeline is designed for continuous operation, featuring automatic recovery from failures, seamless schema change replication, transactional integrity to prevent partial failures, and end-to-end pipeline management.

Enhancing Reliability and Resilience

A primary focus of data mirroring is to build highly resilient pipelines by minimizing system complexity and dependencies. By eliminating intermediate replication services, changes are pushed directly from Postgres into object storage, reducing potential failure points and avoiding issues like disk filling on the source database.

Data mirroring ensures transactional consistency by committing all changes or none, preventing intermediate states that are difficult to recover from. A "meta log" tracks high-level operations, coordinating schema changes and other critical updates. Automatic recovery mechanisms are in place to handle invalidated or dropped replication slots, ensuring the system can resnapshot tables and restart coordination via the meta log, turning catastrophic failures into momentary lag increases without operator intervention. The entire pipeline is managed by Snowflake, with continuous monitoring and proactive mitigation strategies.

Cost and Lag Optimization

Data mirroring is engineered to reduce the operational costs and latency associated with data replication. It incurs costs only for applying changes using Snowflake's serverless compute infrastructure and temporary storage. The system employs exactly-once delivery for rows, optimizing the application process by avoiding duplicate scans. Serverless tasks in Snowflake are billed by the second, making the application of changes, especially updates and deletes, more cost-effective.

To balance lag and cost, data mirroring offers deferred apply with live views. This allows changes to be applied in larger, more cost-efficient batches while still providing low-latency data access through $live views. These views combine unapplied changes with target tables at query time, offering flexibility. While change batches are pushed from Postgres every 15 seconds and refreshed in Snowflake every 30 seconds, the $live views provide near real-time data access. The system also supports high-throughput snapshots and change feeds, capable of handling over 1 million rows per second during snapshots and sustaining over 100,000 row changes per second under heavy load.

Transactional Consistency Across Systems

Preserving transactional consistency is a core tenet of data mirroring. Change batches pushed from Postgres represent ordered sequences of transactions, with each batch end marking a transaction boundary. These batches are made available to Snowflake within a single Postgres-side transaction, and Snowflake applies them in its own single-transaction batches. This ensures that target tables in Snowflake adhere to Postgres write transaction boundaries, maintaining both internal consistency (e.g., no duplicate keys) and inter-table consistency (e.g., foreign keys hold), leading to accurate results when joining mirrored tables.

Future Developments

Snowflake also hinted at future capabilities, noting that the mirroring technology is being developed for Snowflake to Postgres mirroring, which will enable full bi-directional data exchange. This upcoming feature promises to further enhance data integration possibilities within the Snowflake ecosystem.