SyncSpur | Postgres CDC to Snowflake with Fivetran: Setup, Gotchas, and Monitoring

Postgres to Snowflake is the most common database pipeline we build, and the log-based version (change data capture over logical replication) is the one that scales. It is also the one with the most ways to go quietly wrong: a replication slot that fills a disk, a table without a primary key that cannot replicate deletes, an initial sync that blows the MAR budget. This tutorial is the checklist we use. Fivetran's own PostgreSQL setup guide is the authority for the exact steps per hosting platform (RDS, Aurora, Cloud SQL, Azure, self-hosted); read it alongside this.

Why logical replication

Fivetran's Postgres connector has two incremental modes: logical replication (reads the write-ahead log through a replication slot) and XMIN (periodically scans tables for rows whose transaction id advanced). XMIN needs no database configuration but cannot capture deletes, scans every table on every sync, and gets expensive on large tables. Logical replication captures inserts, updates, and deletes in order with minimal load on the source. Use it for anything that matters.

Prerequisites on the database

Logical replication needs these settings (restart required for wal_level):

-- postgresql.conf (or the RDS/Aurora parameter group)
wal_level = logical
max_replication_slots = 10      -- at least 1 more than you use
max_wal_senders = 10

On Amazon RDS set rds.logical_replication = 1 in the parameter group, which turns on wal_level = logical for you. Aurora and Cloud SQL have equivalents; the setup guide lists them.

Create a dedicated user with replication rights and read access to the schemas you will sync:

create user fivetran with password '...' replication;
grant usage on schema public to fivetran;
grant select on all tables in schema public to fivetran;
alter default privileges in schema public grant select on tables to fivetran;

On RDS, replication is granted via grant rds_replication to fivetran; instead of the replication attribute.

Publication and slot

Fivetran's recommended decoding plugin is pgoutput, which is built into Postgres 10+. It needs a publication, and a replication slot that Fivetran will consume:

create publication fivetran_pub for all tables;
select pg_create_logical_replication_slot('fivetran_slot', 'pgoutput');

If you cannot use for all tables (it requires superuser on some platforms, or you want to limit scope), create the publication for specific tables and remember to add new tables to it as they are created; tables outside the publication silently fall back to non-log-based updates or are not captured at all, depending on connector settings.

Then, in Fivetran, create the PostgreSQL connection, pick the logical replication update method, and provide the slot and publication names. Fivetran validates connectivity, the slot, and privileges before the first sync.

Replica identity

Logical replication needs to identify the row being updated or deleted. Tables with a primary key are fine. Tables without one need:

alter table public.events replica identity full;

replica identity full writes the entire old row to the WAL on every update and delete, which inflates WAL volume and MAR. The better fix is to add a primary key; do replica identity full only for tables where you genuinely cannot.

Sizing the initial sync

The first sync reads every selected table in full. Two things to plan:

MAR. Every row in every selected table is active in the first month. Use the 14-day free-use period for new connections, and deselect large tables you do not need before the first sync rather than after.
Slot growth during the initial sync. The slot is created before the historical load, so the WAL accumulates until the historical load finishes and Fivetran catches up on the log. For a multi-terabyte database that can be days of WAL. Make sure the disk (or on RDS, the allocated storage) can absorb it, and avoid kicking off the initial sync right before a bulk data load.

Order tables so that the ones your team needs first are selected first; you can add the rest after the initial sync settles.

Schema drift: what Fivetran does downstream

Fivetran handles most DDL automatically, and it is worth knowing what "handles" means in Snowflake:

New column: added to the Snowflake table on the next sync, populated for rows changed after the column appeared (historical rows are NULL until re-synced).
Dropped column: kept in Snowflake and no longer populated. Fivetran does not drop destination columns.
Type change: Fivetran widens the destination column where it can (for example INT to NUMBER), and where it cannot it may create a new column and mark the old one. Review type changes in the connector's log.
New table: captured if the publication covers all tables, otherwise ignored until you add it to the publication and select it in Fivetran.
Renamed table: treated as a drop plus a new table. The old Snowflake table stays.

Every row Fivetran lands carries _fivetran_synced and, when deletes are captured, _fivetran_deleted. Fivetran soft-deletes by default: deleted source rows remain in Snowflake flagged _fivetran_deleted = true. Your dbt staging models should filter on it.

-- models/staging/stg_app__orders.sql
select *
from {{ source('app', 'orders') }}
where not _fivetran_deleted

Gotchas we hit repeatedly

Slot left behind after a connection is deleted. Postgres does not know the consumer is gone, so the slot holds WAL forever and eventually fills the disk. When you delete a Fivetran connection, select pg_drop_replication_slot('fivetran_slot'); yourself.
Failover on Aurora or patched RDS. Slots do not survive a failover to a replica on some configurations. Fivetran will report the slot missing; recreate it and the connector will need a re-sync of the affected tables.
Long-running transactions. A transaction open for hours blocks WAL cleanup for the slot and delays the changes behind it. Monitor pg_stat_activity for old xact_start values.
TOASTed columns. Large text and JSON columns that were not modified in an update are not written to the WAL unless replica identity full is set, and Fivetran fills them from its own copy. Usually fine; occasionally surprising when debugging a diff.
Sync frequency does not equal latency. With logical replication the changes are read continuously from the slot but applied on the sync schedule. A 15-minute schedule means up to 15 minutes of lag plus load time.

Monitoring

Three things to watch, from the database side and the Fivetran side.

Slot lag on Postgres. Alert when the retained WAL for the slot exceeds a threshold appropriate to your disk:

select slot_name,
       active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as retained_wal
from pg_replication_slots;

An inactive slot with growing retained_wal means Fivetran is not consuming; check the connector status before the disk fills.

Sync health from the Fivetran Platform Connector. Enable it and query the log table for failures per connection. A daily query we alert on:

select connection_id, count(*) as failures
from fivetran_platform.log
where event = 'sync_end'
  and message_data:status::string = 'FAILURE'
  and time_stamp >= current_timestamp - interval '1 day'
group by 1;

(The exact column and JSON shape vary by destination; check the Platform Connector schema documentation for your version.)

Freshness in dbt. A loaded_at_field: _fivetran_synced freshness check on each source catches the case where the sync "succeeds" but nothing new arrives because the slot or publication silently stopped covering a table.

Fivetran also sends sync status to Slack, email, and a webhook (configured per account in the dashboard, or via the REST API); we recommend the webhook into whatever already pages your on-call rather than a new channel nobody reads.

Checklist

wal_level = logical, slots and senders sized, restart done
Dedicated replication user with SELECT and default privileges
Publication and pgoutput slot created
Primary keys (or replica identity full) on every selected table
Tables deselected before the initial sync; MAR and WAL disk sized
Slot lag, sync failure, and source freshness alerts wired

If you would rather have this done for you, with a monitoring dashboard left behind, see our data pipeline development and platform maintenance services.