PostgreSQL to Snowflake: CDC, Replication & Data Ingestion Guide

PostgreSQL data can be moved to Snowflake with a one-time export and COPY INTO, scheduled incremental queries, or log-based change data capture (CDC). For a production database that changes continuously, CDC is usually the better fit: it reads inserts, updates and deletes from the write-ahead log (WAL) through a logical replication slot instead of re-querying tables, and unlike incremental queries it sees hard deletes.

The main constraints sit at the two ends: PostgreSQL keeps WAL behind a stalled slot and loses slots on failover or upgrade, and Snowflake bills warehouse time for every MERGE. Estuary is one managed option for this route: it captures PostgreSQL changes through logical replication into a durable collection and loads Snowflake on a configurable schedule.

Choosing a PostgreSQL to Snowflake method

Batch methods miss hard deletes and re-scan PostgreSQL every run, so most of this guide assumes CDC.

How PostgreSQL to Snowflake CDC works

CDC decodes committed changes from the WAL and applies them to Snowflake in batches.

• wal_level = logical makes the WAL detailed enough to decode row changes.
• A publication lists the tables that emit change events.
• A replication slot is the consumer's cursor. PostgreSQL retains every WAL segment the slot still needs, even with no consumer connected.
• pgoutput, the built-in output plugin, emits inserts, updates and deletes in commit order.

A new pipeline backfills existing rows, then streams from the slot, reconciling the two so a row changed mid-backfill is neither lost nor applied out of order.

Snowflake is loaded one of two ways. Stage and MERGE merges changes by primary key into a current-state table, on a warehouse. Append (Snowpipe Streaming, the Kafka connector) inserts each change as a row without reading the table, producing a change log.

PostgreSQL requirements

Enabling logical replication depends on the host:

A least-privilege setup on PostgreSQL 14+:

sql
CREATE USER cdc_user WITH PASSWORD '...' REPLICATION;
GRANT pg_read_all_data TO cdc_user;   -- older versions: GRANT SELECT per schema
CREATE PUBLICATION cdc_publication;
ALTER PUBLICATION cdc_publication SET (publish_via_partition_root = true);
ALTER PUBLICATION cdc_publication ADD TABLE public.orders, public.customers;
ALTER SYSTEM SET max_slot_wal_keep_size = '50GB';  -- PG13+; size for your write rate

Three rules prevent most incidents:

1. List tables explicitly. A FOR ALL TABLES publication makes PostgreSQL reject updates and deletes on any table without a primary key or replica identity.
2. Give replicated tables a primary key. Snowflake MERGE matches on it.
3. Set max_slot_wal_keep_size. The default, -1, lets a stalled slot fill the disk.

Snowflake requirements

Snowflake needs a database, schema, virtual warehouse, and a dedicated user and role with grants on them, authenticated with a key pair (JWT) rather than a password.

• Set the warehouse to AUTO_SUSPEND = 60. Snowflake bills per second with a 60-second minimum per resume.
• Choose the timestamp type deliberately (TIMESTAMP_LTZ, TIMESTAMP_NTZ or TIMESTAMP_TZ). If the account sets TIMESTAMP_TYPE_MAPPING, it must match what the pipeline writes.
• Don't enable QUOTED_IDENTIFIERS_IGNORE_CASE for the pipeline user. Quoted identifiers, including reserved-word columns, must be referenced exactly.

Setting up PostgreSQL to Snowflake replication

1. Enable logical replication for your host and confirm SHOW wal_level; returns logical.
2. Create the role and publication, set max_slot_wal_keep_size, and open network access: allowlisted IPs, an SSH tunnel through a bastion, or private networking.
3. Prepare Snowflake as described above.
4. Choose the method, and per table choose MERGE (current state) or append (event tables).
5. Load existing data. For a large database, prioritise tables, skip history you don't need, and watch WAL retention: a tool that snapshots before consuming the slot accumulates WAL for the whole snapshot.
6. Start ongoing replication and set the Snowflake load interval to the freshness people actually use.
7. Validate. Compare row counts. Update a row, wait one load interval, and query that key in Snowflake; repeat for a delete and a new column. Stop the pipeline briefly and confirm it resumes cleanly.

What happens after the initial load?

• Updates merge into the existing row by primary key, or append as a new version in append mode.
• Deletes are soft by default in most tools (a metadata column marks the row). Hard deletes remove it.
• New tables emit changes only once they are in the publication.
• New columns are added automatically by most managed tools. Earlier rows show NULL until re-loaded.
• Type changes that widen, such as dropping NOT NULL, pass through. Narrowing changes usually force a table rebuild.
• Dropped columns stay in Snowflake and stop receiving values.
• TOASTed values. If an update doesn't touch a large out-of-line (TOAST) value, PostgreSQL omits it from the WAL. A merging loader keeps the last known value; an append loader writes the row without it, unless the table uses REPLICA IDENTITY FULL, which costs WAL volume.

PostgreSQL to Snowflake data type mapping

NUMERIC precision

NUMERIC values lose precision when the pipeline loads them as Snowflake FLOAT (a double, about 15 significant digits) instead of NUMBER(p,s). Money amounts, high-scale ratios and long identifiers get rounded. Map each column explicitly: NUMBER(p,s) where p ≤ 38, and VARCHAR for values that don't fit. Then re-load the table; values already rounded in Snowflake can't be recovered there.

Performance, latency and Snowflake cost

Capture can be continuous, but Snowflake freshness is set by the load interval. Cost comes mainly from how often the warehouse resumes to merge and how much of the table each MERGE scans.

Reducing MERGE cost

• Lengthen the load interval. Each load resumes the warehouse for at least 60 billed seconds, so a one-minute interval keeps it running permanently.
• Coalesce by key. A row updated 500 times between loads should be merged once.
• Fix pruning. Snowflake prunes micro-partitions by per-column min/max. Keys that follow insert order touch few partitions; a random UUID scans most of the table, and a composite key like (tenant_id, id) does the same when each batch spans many tenants. Put a write-order column first, or cluster on the merge columns (clustering bills its own credits). Match key types exactly, because implicit casts stop pruning.
• Start at X-Small. Merges are mostly I/O.

Snowpipe Streaming vs staged MERGE

Use Snowpipe Streaming for insert-only or event-shaped tables where low load latency matters, and staged MERGE for tables that are updated and deleted. Snowpipe Streaming only inserts: an update arrives as another row for the key, and a delete as a row with a delete marker. Current state then needs a view or dynamic table that keeps the latest row per key, which costs compute too, so for update-heavy tables a merge every 15–30 minutes is often cheaper.

Production considerations

• Retries and recovery. A restarted pipeline resumes from the slot's confirmed position. MERGE tables absorb a replayed batch; append tables duplicate it unless the tool commits its read position and the Snowflake write together.
• Source impact. The slot pins WAL and catalog rows until confirmed (see troubleshooting). Backfills add table reads; batch methods re-query every run.
• Schema changes and deletes. Decide per table: soft or hard deletes, and what happens on an incompatible type change (see the initial-load section).
• Backfills. Re-backfills that merge into existing tables keep them queryable; truncate-and-reload empties them until the load finishes.
• Monitoring. Alert on retained WAL and wal_status in pg_replication_slots, not only on Snowflake row counts, and on time since the last successful Snowflake load.
• Security. Least-privilege PostgreSQL role, key-pair auth on Snowflake, and private connectivity where available.

Common problems and troubleshooting

WAL growth behind an inactive replication slot

WAL fills the disk when a slot stops advancing, because PostgreSQL keeps every segment after the slot's restart_lsn. Find the slot:

sql
SELECT slot_name, active, wal_status, safe_wal_size,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

• Pipeline stopped or deleted: restart it, or drop an abandoned slot with pg_drop_replication_slot('slot_name').
• Long-running transaction:restart_lsn can't pass the oldest open transaction. Find it in pg_stat_activity.
• Idle published tables on a busy server: the slot only confirms progress when a published table changes. Add a heartbeat table updated every few minutes.
• Prevention: set max_slot_wal_keep_size, so PostgreSQL invalidates the slot instead of filling the disk. Size it for worst-case open-transaction time at peak write rate, and alert when wal_status becomes extended or unreserved.

Replication slot lost after failover, upgrade or WAL limit

Without its slot, a pipeline fails with replication slot "..." does not exist and must re-read tables from PostgreSQL. Snowflake stays correct up to the failure. Slots are lost through:

• Failover. The new primary has no copy of the slot unless it was a synchronized failover slot (PostgreSQL 17+; RDS for PostgreSQL supports it from 17).
• Major-version upgrade. RDS blocks upgrades while logical slots exist on sources before 17. From 17, pg_upgrade can carry fully consumed logical slots over.
• max_slot_wal_keep_size exceeded (wal_status = lost), for example when WAL ages out during an outage.
• Manual cleanup.

Recovery, cheapest first:

1. Planned, writes paused: confirm the pipeline has caught up, run the upgrade or failover, create a new slot, and resume from the current position without re-reading tables. On 17+ sources, check whether your platform keeps slots (RDS does for non-replica sources). Treat blue/green upgrades as failovers.
2. Outage start known: re-read only rows changed since then, by timestamp or xmin transaction ID. Rows deleted during the outage are missed.
3. Otherwise: re-read every table and merge into Snowflake without truncating.

CDC from Aurora, Global Database or a read replica

Capture from the writer. Aurora PostgreSQL doesn't support logical decoding on readers, although community PostgreSQL 16 added it. Where standby decoding works (PostgreSQL 16+), set hot_standby_feedback = on, or the primary can vacuum catalog rows the slot needs and invalidate it. For Aurora Global Database, capture from the primary Region's writer. AWS doesn't document whether logical slots survive a writer failover within a cluster or a cross-Region switchover, so check pg_replication_slots after any failover and be ready to re-backfill.

Using Estuary for PostgreSQL to Snowflake

Estuary pairs the PostgreSQL CDC capture with the Snowflake materialization. The capture manages the slot (flow_slot by default) and backfill, reads the publication you created, and writes each table to a collection (below). The materialization stages each transaction in an internal Snowflake stage and applies it with MERGE, or with COPY INTO when all its keys are new. Against the concepts above:

• Load interval: the sync schedule defaults to 30 minutes when caught up; fast-sync windows apply your interval in business hours and 4-hour syncs outside them, and backfills ignore it. Changes to the same key are combined between syncs, which is the coalescing that cuts MERGE cost.
• Merge vs append: merge is the default. Delta updates are per binding and load through Snowpipe Streaming when auth is key-pair (JWT), still on the sync schedule; set Sync Frequency to 0s for low latency.
• Deletes: soft (_meta/op = 'd') unless hardDelete: true.
• TOAST: merge bindings keep unchanged TOASTed values; delta bindings don't.
• Types:NUMERIC becomes FLOAT by default. To keep exact values, set castToString: true on the field, which passes the original decimal string through unchanged. To query it as a number, cast it to NUMBER(38,10) in a view. A DDL override on its own changes only the column type: values are still converted through FLOAT first, so it doesn't fix precision. jsonb and arrays become VARIANT (multidimensional arrays flattened), bytea becomes BINARY, infinity timestamps become 9999-12-31T23:59:59Z, and values over 8 MiB are truncated.

Implementing PostgreSQL to Snowflake with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in PostgreSQL requirements and Snowflake requirements. Estuary also needs:
   • A public.flow_watermarks table in cdc_publication that cdc_user can write to; run this as the publication's owner (setup SQL):

sql
CREATE TABLE public.flow_watermarks (slot TEXT PRIMARY KEY, watermark TEXT);
GRANT ALL PRIVILEGES ON TABLE public.flow_watermarks TO cdc_user;
ALTER PUBLICATION cdc_publication ADD TABLE public.flow_watermarks;

• The Snowflake objects from the connector's setup script, with a public key registered for key-pair authentication. Password auth is deprecated.

2. Create the PostgreSQL capture. On the Sources page, click New Capture, choose PostgreSQL, name the capture, and fill in the endpoint properties:
   • Server Address: host:port. Allowlist Estuary's IP addresses, or use Network Tunnel for an SSH tunnel. Private and BYOC deployments can use private networking.
   • User and Database.
   • Authentication: Password, or AWS, Google Cloud or Azure IAM.
   • Advanced Options: Publication Name, Slot Name and Watermarks Table default to flow_publication, flow_slot and public.flow_watermarks. With the SQL above, set User to cdc_user and Publication Name to cdc_publication.

3. Select tables. Click Next. Estuary discovers the published tables and lists one binding per table under Output Collections.
   • Remove tables you don't want. Each binding writes its own collection.
   • Raise Backfill Priority to load key tables first; leave Backfill Mode at its default.
   • Keep Automatically keep schemas up to date on (see schema evolution).

Click Save and publish. The backfill starts, with change capture running alongside it.

4. Create the Snowflake materialization. On the capture's details page, click Materialize, choose Snowflake, and fill in the endpoint properties:
   • Host (Account URL): orgname-accountname.snowflakecomputing.com, no protocol.
   • Database, Schema, Warehouse and Role from the setup script.
   • Snowflake Timestamp Type: the type you chose above.
   • Authentication: Private Key (JWT), with User and Private Key.
   • Hard Delete: on to delete rows; off keeps soft deletes.

5. Choose the sync schedule. Under Sync Schedule, Sync Frequency defaults to 30 minutes. Shorter intervals give fresher tables and more warehouse resumes. For business-hours freshness, set Timezone, Fast Sync Start Time, Fast Sync Stop Time, and optionally Fast Sync Enabled Days; outside that window, syncs run every 4 hours. See sync schedules.

6. Map collections to tables. Click Next. When prompted for a naming convention, choose Match source structure to keep PostgreSQL schema and table names, or Set a default schema. Each binding under Source Collections has:
   • Table and Alternative Schema, to override names.
   • Delta Updates, for event tables. Tables with AUTOINCREMENT/IDENTITY or geospatial columns, column defaults or collations can't use Snowpipe Streaming.
   • Clustering Keys, for large, selectively filtered tables; clustering bills its own credits.
7. Publish. Click Save and publish. Estuary creates the Snowflake tables and loads the backfill without waiting for the schedule.
8. Verify that data is landing.
   • On the capture details page, Connector Status shows backfill progress and the Shard Information card reads PRIMARY; open a collection from the Collections list to see its Data Preview.
   • The materialization details page charts Data Read.
   • In Snowflake, query a target table, then run the checks from Setting up PostgreSQL to Snowflake replication.

How Estuary handles backfills, recovery and extra destinations

The capture writes every change to a collection: a durable log stored as JSON files in your cloud storage bucket. Destinations read the collection, not PostgreSQL, so rebuilding a Snowflake table or adding a destination doesn't touch the database. Only a lost slot makes the capture re-read it.

One collection for the backfill and the change stream

The backfill and ongoing WAL changes land in the same collection, one per table. By default the capture reads key-ordered backfill chunks while reading the WAL, so changes flow while large tables still backfill. The materialization commits its read checkpoint and the Snowflake table state together, so a crash mid-load neither applies a batch twice nor skips one.

What a re-backfill does and doesn't avoid

The limits: replay depends on retention (trial storage keeps about 20 days; use your own bucket for full history); a WAL gap still needs a source read, because the collection can't supply changes it never received, though other materializations keep running meanwhile; and delta-update tables get duplicates from incremental backfills.

Snowflake and a low-latency store from one capture

To stream PostgreSQL changes to Snowflake and a real-time store from one pipeline, add a second materialization on the same collection. PostgreSQL keeps one slot and serves one read. Snowflake loads on its sync schedule, while database and streaming destinations such as PostgreSQL, Elasticsearch, MongoDB and DynamoDB apply changes as they arrive, with no schedule.

Schema evolution

The capture re-checks its tables' schemas every 15 minutes by default; auto-discovery can add new tables. New scalar columns are added to Snowflake. An incompatible type change rebuilds the Snowflake table from the collection by default; per binding, onIncompatibleSchemaChange can instead abort, disable the binding, or disable the task.

When another approach may make more sense

• Snowflake-only, native tooling: the Openflow Connector for PostgreSQL reads logical replication (PostgreSQL 11–18), marks deletes in a _SNOWFLAKE_DELETED column, and runs on an Openflow runtime in Snowpark Container Services or your own AWS account. Its documented recovery after a lost slot is a fresh snapshot of the tables, unless writes were stopped first.
• Debezium and Kafka already run well: add the Snowflake Kafka connector. It only inserts, so you build the dedup/MERGE layer; a lost slot means a new snapshot, and Snowflake rebuilds are limited by topic retention.
• One-time migration or daily freshness on small tables: an export with COPY INTO, or a nightly updated_at query, avoids a slot on production. The cost is a full read or a query per table per run, and deleted rows never arrive.
• Logical replication isn't available (some managed tiers, or replicas before PostgreSQL 16): use batch capture with an xmin or updated_at cursor, and accept that hard deletes are missed. Estuary's PostgreSQL Batch connector covers this case, polling by xmin on a schedule.

Related resources

• Estuary PostgreSQL CDC connector, Amazon RDS, Aurora, Cloud SQL, AlloyDB, Supabase, Neon, batch
• Estuary Snowflake materialization and sync schedules
• Replication slot recovery, backfilling data, schema evolution, custom column types
• PostgreSQL: replication settings, logical decoding, pg_replication_slots, upgrading with logical slots
• Snowflake: Openflow Connector for PostgreSQL, Snowpipe Streaming, micro-partitions

To try the route, follow the PostgreSQL CDC to Snowflake tutorial or create a PostgreSQL capture in the Estuary dashboard.