RDS Postgres to Snowflake: Migration Patterns and Choosing the Right Approach

The Question Behind the Question

"How do we get our Postgres data into Snowflake?" is usually a stand-in for three other questions:

1. How fresh does the data need to be? Daily, hourly, or minutes?
2. How much operational load can the source database take? Is it a busy production OLTP database or a quiet replica?
3. Who is going to own the pipeline? A data team with capacity, or one engineer who also looks after the API?

Answer those three honestly and the right pattern usually picks itself. I've moved data from Postgres (and before that MySQL) into Redshift, and more recently Snowflake, several times. Here are the patterns I've seen work, roughly in order of complexity.

Pattern 1: Batch Extract with Full or Incremental Loads

The simplest option. A scheduled job (Airflow, a cron'd container, a Lambda) runs a query against Postgres, writes the results to S3 as Parquet or CSV, and Snowflake loads them with COPY INTO.

-- Incremental extract using a high-water mark
SELECT *
FROM orders
WHERE updated_at > :last_successful_watermark
ORDER BY updated_at;

-- Snowflake side
COPY INTO raw.orders_staging
FROM @raw.s3_stage/orders/2026-08-04/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
 
MERGE INTO raw.orders t
USING raw.orders_staging s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Good for: small to medium tables, daily or hourly freshness, teams without streaming experience.

Watch out for:

• Hard deletes are invisible. If a row is deleted in Postgres, an updated_at watermark never sees it. You either soft-delete in the application, periodically do a full reload, or move to CDC.
• updated_at must actually be maintained. Check every write path, including bulk updates and admin scripts. One UPDATE that bypasses the ORM and your warehouse silently drifts.
• Run it against a read replica. A large extract on the primary during peak traffic is a self-inflicted incident.

Pattern 2: RDS Snapshot Export to S3

RDS can export a snapshot to S3 in Parquet format. Snowflake then loads from S3 as above.

Good for: the initial historical backfill, or large tables where a daily full refresh is fine.

Watch out for: export time on large databases can run to hours, and you get a point-in-time copy, not a stream. It's an excellent first load mechanism paired with CDC for ongoing changes. It's a poor pattern on its own if the business wants fresh data.

Pattern 3: Managed ELT Connectors (Fivetran, Airbyte, Stitch, Estuary and friends)

You point a SaaS connector at Postgres, it reads the write-ahead log (WAL) through logical replication, and it lands changes in Snowflake. I used StitchData years ago for our first Redshift platform and it got us to a working warehouse in days rather than months.

Good for: startups and scaleups that want CDC-quality data without running any infrastructure. For most companies with fewer than a few hundred tables this is the right answer.

Watch out for:

• Pricing models based on rows changed. A table that updates frequently (a sessions table, a job queue, a cart) can quietly dominate your bill. Exclude noisy tables or sync them less often.
• Replication slots. Logical replication uses a replication slot on the source. If the connector stops consuming, Postgres keeps WAL around for it, and your disk fills up. Set a CloudWatch alarm on OldestReplicationSlotLag and TransactionLogsDiskUsage from day one.
• Data leaves your VPC. For regulated data you'll need to check where the connector processes it, or use a self-hosted option (Airbyte OSS, for example).

Pattern 4: DMS or Debezium CDC Into S3, Kafka or Kinesis

Run your own change data capture. AWS DMS reads the Postgres WAL and writes changes to S3 (or Kinesis). Debezium does the same into Kafka (MSK or Confluent). Snowflake picks them up with Snowpipe (auto-ingest from S3) or the Kafka connector with Snowpipe Streaming.

RDS Postgres (logical replication)
   → DMS / Debezium
   → S3 (Parquet) or Kafka topic
   → Snowpipe / Snowpipe Streaming
   → Snowflake RAW schema (append-only change log)
   → dbt models / Dynamic Tables to build current-state tables

Good for: near-real-time requirements, high volumes where per-row SaaS pricing gets painful, and organisations that already run Kafka.

Watch out for:

• You own it now. DMS task failures, schema changes, Kafka Connect upgrades and replication slot hygiene all become your team's problem.
• Schema evolution. A column rename in the app becomes a broken pipeline at 2am unless someone has thought about it. Add the data pipeline to your migration checklist.
• Land raw, then model. Store the change events append-only and build current-state tables downstream. It makes replays and debugging possible.

Pattern 5: Zero-ETL and Native Connectors

The cloud providers and Snowflake keep shipping "no pipeline" options: Snowflake's own connector for PostgreSQL, and managed zero-ETL integrations elsewhere in the ecosystem. The pitch is attractive: fewer moving parts, no third-party vendor in the middle.

Good for: teams that want the simplicity of a managed connector with fewer vendors on the contract.

Watch out for: check the current feature set against your needs before you commit: supported Postgres versions, how DDL changes are handled, table limits and how it's billed (it will usually consume warehouse credits). These products move fast, so evaluate what's shipping today rather than the launch blog post.

Comparison at a Glance

The Migration Itself: What Actually Goes Wrong

The pipeline is usually not the hard part. These are:

Type mapping. Postgres jsonb becomes Snowflake VARIANT, which is great, but numeric without precision, arrays, timestamp without time zone and custom enums all need a deliberate decision. Timestamps are the classic trap: decide on TIMESTAMP_NTZ in UTC everywhere and write it down.

Case sensitivity. Postgres folds unquoted identifiers to lower case; Snowflake folds them to upper case. Quoted mixed-case column names from an ORM will haunt your analysts. Normalise at the landing layer.

Reconciliation. Build row counts and checksums per table per day, comparing source to warehouse, before anyone relies on a dashboard. Trust in a data platform is lost in one bad board meeting and takes months to earn back.

Cost controls. Snowflake makes it very easy to spend money. Set warehouse auto-suspend to 60 seconds, use resource monitors, and keep ingestion on a separate small warehouse from BI queries so you can see what's costing what.

Parallel running. If you're migrating from an existing warehouse (Redshift, BigQuery or reports straight off Postgres), run both for a few weeks and diff the key business metrics. Revenue, active subscribers, orders per day. When they match, switch.

My Recommendation by Stage

Startup (one data person or none): Managed ELT connector for the tables that matter, dbt on top, and nothing else. Resist building a streaming platform. Your constraint is people, not technology.

Scaleup (a small data team, growing volumes): Keep the managed connector for most tables, and move the two or three high-churn tables that dominate the bill to DMS → S3 → Snowpipe. Invest in reconciliation and data contracts with the application teams.

Enterprise (platform team, compliance requirements, many sources): Standardise on a CDC backbone (Debezium/Kafka or DMS) that feeds more than just Snowflake, so the same change stream can serve search indexes, caches and event consumers. Treat the pipeline as a product with an owner, SLAs and a schema-change process.

The Takeaway

Pick the least sophisticated pattern that meets your real freshness requirement, not the one you'd like on your CV. Most "we need real-time" requirements turn into "hourly is fine" after one honest conversation with the people asking. And whatever you choose, keep an alarm on your replication slot lag, because that's the one that takes production down.