Migrating from PostgreSQL to Snowflake

"Migrate PostgreSQL to Snowflake" has meant one thing for a decade: copy your operational data into Snowflake's columnar warehouse for analytics. Since February 24, 2026, it can mean something else entirely -- Snowflake Postgres, a managed PostgreSQL service running inside the Snowflake platform, is generally available. These are completely different migrations with completely different mechanics, so the first step is deciding which one you're actually doing.

Moving your application database? Snowflake Postgres runs real PostgreSQL on dedicated VMs. Your schema, your extensions (a supported subset), your drivers -- nothing about your application changes except the connection string. The migration is a standard Postgres-to-Postgres move.

Offloading analytics? The Snowflake warehouse is a columnar engine with a different type system, no indexes, and unenforced constraints. PostgreSQL usually stays as the system of record and you replicate into Snowflake. This is the more common project, and most of this guide covers it.

Path A: PostgreSQL to Snowflake Postgres

Because the target is genuine PostgreSQL, the standard tools apply.

Dump and restore works for smaller databases -- Snowflake's own guidance suggests roughly the 50-150 GB range where a downtime window is acceptable:

pg_dump -Fc --no-owner -d "$SOURCE_URL" -f app.dump
pg_restore --no-owner -d "$SNOWFLAKE_PG_URL" app.dump

Logical replication is the minimal-downtime path for anything larger. The source becomes a publisher, Snowflake Postgres subscribes, the initial table sync copies historical data, and ongoing changes stream until you cut over:

-- on the source
CREATE PUBLICATION migration_pub FOR ALL TABLES;
 
-- on Snowflake Postgres
CREATE SUBSCRIPTION migration_sub
  CONNECTION 'host=source.example.com dbname=app user=repl password=...'
  PUBLICATION migration_pub;

The gotchas are the usual logical-replication ones, not Snowflake-specific: schema and DDL don't replicate (migrate schema first with pg_dump --schema-only), sequences don't replicate (resync them with setval() at cutover), and the source needs wal_level = logical. Monitor lag via pg_stat_subscription, stop writes, wait for the subscriber to catch up, repoint the application, drop the subscription.

If this is your migration, you're done -- the rest of this guide is about the warehouse.

Path B: PostgreSQL to the Snowflake Warehouse

Schema translation

Snowflake's type system is smaller than PostgreSQL's and several familiar features simply don't exist:

The one that bites people: on standard Snowflake tables, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are metadata only -- they are not enforced (only NOT NULL is). A load that would violate a unique key in PostgreSQL succeeds silently in Snowflake. Deduplication has to happen in your pipeline or with QUALIFY ROW_NUMBER() OVER (...) = 1 at query time. (Snowflake's hybrid tables do enforce keys, but they're a transactional feature, not the analytics default.)

One-time batch load

Export with COPY, stage, and COPY INTO. Snowflake's documented sweet spot is files of roughly 100-250 MB compressed, so split large tables:

psql "$SOURCE_URL" -c "\copy (SELECT * FROM orders) TO 'orders.csv' WITH (FORMAT csv, HEADER false, NULL '\\N')"
split -l 2000000 orders.csv orders_part_

CREATE STAGE migration_stage;
-- from SnowSQL or a driver session:
PUT file://orders_part_* @migration_stage AUTO_COMPRESS=TRUE;
 
COPY INTO orders
FROM @migration_stage
FILE_FORMAT = (TYPE = CSV, NULL_IF = ('\\N'), FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = ABORT_STATEMENT;

CSV loses type information at the edges (empty string vs NULL, timestamp formats), which is why NULL_IF matters. If your pipeline can produce Parquet instead, prefer it -- types travel with the file and COPY INTO ... MATCH_BY_COLUMN_NAME maps columns by name.

Continuous replication (CDC)

Two Snowflake-native options exist, and one of them is a trap for readers of older tutorials. The original Snowflake Connector for PostgreSQL never left preview -- Snowflake states that moving it to general availability "is currently not on our product roadmap" and points everyone at its replacement (verified against Snowflake's docs, July 2026). Don't build on it.

The current answer is the Openflow Connector for PostgreSQL: log-based CDC via pgoutput logical decoding, PostgreSQL 11-18 supported (self-hosted, RDS, Aurora, Cloud SQL, Azure). Requirements worth knowing before you commit:

• Every replicated table needs an identity key: a primary key, a unique index declared via REPLICA IDENTITY USING INDEX, or a logical key with REPLICA IDENTITY FULL. Tables without one replicate INSERTs only -- UPDATEs and DELETEs are skipped.
• It must connect to the primary; logical replication doesn't run on read replicas.
• Schema changes are handled except primary-key changes and numeric precision/scale changes.

Third-party CDC (Fivetran, Airbyte, Estuary, Debezium-based pipelines) works fine too; the mechanics are the same logical-decoding slot underneath. Whatever you pick, the standing PostgreSQL warning applies: a replication slot nobody is consuming retains WAL forever and will eventually fill the source's disk. Drop slots from abandoned experiments (SELECT pg_drop_replication_slot(...)).

Query dialect deltas

Less painful than most pairs -- Snowflake is closer to PostgreSQL than to MySQL. The ones you'll hit: DISTINCT ON doesn't exist (use QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1), unquoted identifiers resolve to uppercase, generate_series becomes GENERATOR, and jsonb operators become VARIANT path notation (data->>'city' → data:city::string). ILIKE, :: casts, CTEs, window functions, and LATERAL all carry over.

Validation

Run identical aggregate checks against both engines before trusting the copy -- stick to portable functions:

SELECT COUNT(*), SUM(amount), MIN(created_at), MAX(created_at)
FROM orders;

Row counts per table, sums of numeric columns, min/max of timestamps. For CDC pipelines, re-run after cutover traffic has flowed. Mako connects to PostgreSQL and Snowflake side-by-side, so running the same validation query against both and diffing the results is a quick loop.

Common Mistakes

For the underlying platform comparison, see Snowflake vs PostgreSQL. Related how-tos: importing CSV to Snowflake, connecting to Snowflake from Python, and connecting to PostgreSQL from Python.

Mako connects to both PostgreSQL and Snowflake with AI-powered autocomplete -- useful for running the same validation queries against both engines during cutover. Try it free at mako.ai.