PostgreSQL to Snowflake: How to Move Your Data
Replicate PostgreSQL into Snowflake with Airbyte. CDC deletion modes, warehouse sizing for cost, and the number type that quietly approximates your values.
Replicating PostgreSQL to Snowflake takes your operational data out of the database that serves your application and puts it in the warehouse where analysis, modelling, and reporting actually happen. It is a well-worn path, and the connectors on both ends are among Airbyte's most heavily used.
That maturity means the setup is predictable. What still catches people is Snowflake's compute billing model, a default numeric type that quietly approximates your values, and the replication slot you will forget you created.
PostgreSQL to Snowflake at a glance:
How should you replicate from Postgres?
The Postgres source supports CDC, xmin, and standard cursor-based replication. For a Snowflake destination the decision usually comes down to whether your analysts need to know when rows were deleted.
If they do, CDC is the only option that captures deletions, and Snowflake gives you a further choice about how to represent them. The destination's CDC deletion mode defaults to hard deletes, which propagate source deletions by removing the row. Soft deletes instead leave a tombstone record in the destination, which is often what you want in a warehouse where history has analytical value.
If deletions do not matter, xmin is less work and less to maintain. Airbyte recommends moving to CDC once a database passes roughly 500 GB regardless.
Whichever you pick, treat the replication slot as an operational object with a lifecycle. Creating it is one line, SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');, but an abandoned slot causes Postgres to retain WAL indefinitely and can fill the disk on your production database. Delete it when you retire the connection.
If syncs unexpectedly revert to full refreshes, check the logs for a message about the saved offset preceding the replication slot's confirmed flush LSN. That means Postgres removed WAL segments Airbyte still needed, usually after heavy update volume or a long gap between syncs. The troubleshooting guide covers mitigation.
How do you prepare Snowflake without overspending?
Step 1: Run the setup script
The Snowflake destination docs provide a script that creates a dedicated role, user, warehouse, database, and schema for Airbyte. Run it from a worksheet using the ACCOUNTADMIN role. Dedicated entities keep permissions contained and make Airbyte's spend visible separately on your bill.
Step 2: Understand the warehouse cost model
Snowflake bills compute per second, and the warehouse resumes every time Airbyte loads data. A large warehouse that stays warm between syncs is pure waste for this workload. The setup script provisions an X-Small warehouse with a 60-second auto-suspend for exactly that reason: the smallest compute tier, shutting down promptly after each load.
The Data Retention Period setting is the other cost lever. It controls how many days of Snowflake Time Travel apply to Airbyte's tables and defaults to 1. Raising it increases storage cost. If you want transient tables to avoid Fail-safe storage charges entirely, create a dedicated transient database for Airbyte rather than trying to convert tables afterwards.
Step 3: Authenticate
Username and password works, but key pair is the stronger option. Generate an unencrypted PKCS#8 key with openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt, derive the public key, and register it with alter user <user_name> set rsa_public_key=<public_key_value>; If a network policy is set on your account, add the Airbyte Cloud IP addresses to the allowlist.
Why does the number type setting matter?
Snowflake's Decimal Data Type option determines how the Airbyte number type is stored, and the default is FLOAT, approximate binary floating point with about 15 digits of precision. The alternative is NUMBER(38,9), an exact fixed-point type allowing up to 29 digits before the decimal point and 9 after.
Postgres NUMERIC columns holding currency, tax, or rates will arrive as approximate floats unless you change this. For financial data that is the wrong representation, and the difference will not announce itself. Totals will simply be slightly off.
Set it before the first sync. Changing it on an existing connection makes Airbyte convert columns in place, and because the types have different range and precision characteristics, values can be nulled or lose precision during conversion. Run a full refresh afterwards if you must change it.
Snowflake enforces size limits too: VARCHAR up to 16 MB and VARIANT up to 128 MB. Values exceeding the bounds are nulled and recorded in the _airbyte_meta column; values within range but with excessive precision are rounded and flagged the same way. That column is worth querying periodically as a data-quality check.
Frequently asked questions
Should I use hard or soft CDC deletes?
Hard deletes are the default and keep Snowflake matching Postgres exactly. Soft deletes leave a tombstone record instead, which is often preferable in a warehouse where knowing that a row once existed has analytical value.
Which Snowflake warehouse size should I use?
X-Small with a short auto-suspend is right for most Airbyte workloads. Snowflake bills per second and the warehouse wakes on every load, so minimising idle time matters more than raw size.
Should I change the number data type from FLOAT?
Yes, if you are replicating currency or any value needing exact decimals. NUMBER(38,9) preserves precision. Set it before the first sync, because converting an existing column can null or truncate values.
Do I need to delete the replication slot when I am done?
Yes, if you are decommissioning a CDC connection. An unused slot makes Postgres retain write-ahead log segments indefinitely, which can fill your production disk.
Why do I get a target schema permission error?
The connection is likely set to mirror the source structure and is writing to a schema your role lacks rights on, often PUBLIC. Switch the destination namespace to Destination Default or grant the role permissions on the namespace you want.
Get your PostgreSQL data into Snowflake
Size the warehouse small, set the number type deliberately, and keep the replication slot on your decommissioning checklist. Evaluating other warehouses? See our guides to PostgreSQL to BigQuery and PostgreSQL to ClickHouse.
Connector behaviour described here reflects the Postgres source and Snowflake destination v4.x, verified August 2026. Check the linked documentation for current versions.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.