Integrations

Postgres to Snowflake

Replicate Postgres tables into Snowflake for analytics, using xmin, CDC or a cursor column to keep them current.

The pipeline as it appears on the Pipeloom canvas. You can add transforms or triggers on the same canvas later.

What to know about Postgres into Snowflake

Pick the replication method on the Postgres side

Xmin needs no cursor column and is the simple default. CDC reads the write-ahead log, also captures deletes, and suits databases of 500 GB or more or tables with a primary key but no usable cursor. Standard incremental needs a cursor column such as updated_at.

Deletes only arrive with CDC

Without CDC, a row deleted in Postgres stays in the Snowflake table. If your reports must reflect deletes, use CDC and set the replica identity on each table.

JSON columns land as text

Postgres json and jsonb are copied as strings, which Snowflake stores as TEXT. Parse them in SQL with PARSE_JSON when you need to query inside them.

Mind special numeric values

Infinity, -Infinity and NaN in double precision and numeric columns become null, and bytea arrives as a hex string. Check those columns if you rely on them.

Example: query inside a jsonb column

SELECT
  ID,
  PARSE_JSON(PAYLOAD):customer.email::STRING AS customer_email
FROM PUBLIC.EVENTS;

Assumes a Postgres table events with a jsonb column payload, synced into a PUBLIC schema. Names will differ in your setup.

Set it up

Create the Postgres sourceAuthenticate with A read-only Postgres user (plus REPLICATION permission for CDC). You need: A read-only Postgres user that can read the tables you want to copy. For CDC only: logical replication turned on, a replication slot, and a publication with a replica identity on each table.
Create the Snowflake destinationAuthenticate with Key pair (an RSA key of 2048 bits or more). You need: A Snowflake account and the ACCOUNTADMIN role for the one-time setup. A warehouse, database, user and role for Pipeloom, with a key pair attached to the user. Network policies that allow Pipeloom to connect.
Connect them and pick streamsDraw the edge on the canvas and choose streams and sync modes. Postgres supports every sync mode; Snowflake supports every mode: full refresh overwrite, overwrite deduped, append, incremental append and incremental append deduped.
Run, then scheduleThe first sync loads each stream in full. Then set the schedule.

Step-by-step guides: Postgres and Snowflake.

What lands in Snowflake

A final table per stream with typed columns, plus _AIRBYTE_RAW_ID, _AIRBYTE_GENERATION_ID, _AIRBYTE_EXTRACTED_AT, _AIRBYTE_LOADED_AT and _AIRBYTE_META.
A raw table per stream in the airbyte_internal schema (or the internal dataset name you set), holding each record as JSON.

Type mapping

object → OBJECT
array → ARRAY
union and unknown → VARIANT
timestamp with time zone → TIMESTAMP_TZ
integer → NUMBER
number → FLOAT (or NUMBER(38,9))

When Postgres's schema changes

New columns are added and types changed as the source changes. A column removed at the source is kept, with its data, and new rows get NULL. Column names that are SQL reserved words get a leading underscore.

Streams you can sync

Includes Tables, Views, Materialized views and more. The full list is in the reference.

What it costs

A sync is metered at 1 credit per vCPU-minute, and a default sync uses 2.5 vCPUs. As an example, not a benchmark, a sync that takes 3 minutes uses 8 credits. Run 24 times a day, that is about 5,760 credits a month, more than Free's 1,500 but within Starter's 12,000 ($19 a month). Your own sync time decides the real figure, so try the calculator with your numbers.

Questions and fixes

Which replication method should I use?

Xmin is the simplest: it picks up inserts and updates without a cursor column, but it does not support regular views and is a poor fit under very heavy write traffic. Use CDC when you need deletes, when the database is 500 GB or more, or when a table has a primary key but no good cursor column. Standard incremental needs a cursor column you choose, such as updated_at.

Why do NaN and Infinity become null?

Infinity, -Infinity and NaN are not supported for double precision and numeric columns and are written as null.

Why does the sync fail with "Current role does not have permissions on the target schema"?

The Pipeloom role is missing a grant on the schema it writes to. Re-run the role setup from the Snowflake guide, or grant the role usage and create-table rights on that schema.

Sync Postgres to Snowflake today

Free forever, no card. Starter is $19 a month for three seats.

Start free →Migrating from another tool?