PostgreSQL to Snowflake
Copy the tables once, or keep the warehouse following the write-ahead log
DBConvert Streams copies PostgreSQL tables into Snowflake, and can keep the warehouse current afterwards. PostgreSQL has the richer type system of the two, so the schema is the part that needs deciding: what the warehouse has a real equivalent for, and what has to arrive as text.
The stream's mode then decides how the data itself follows.
Load writes the selected tables as Parquet through your user stage, then counts what arrived against what PostgreSQL handed over and fails the run on a shortfall.
CDC reads logical replication instead and streams changes over Snowpipe Streaming, which requires wal_level = logical, a replication slot and REPLICA IDENTITY FULL on the PostgreSQL side and key-pair authentication on the Snowflake side.
What PostgreSQL Types Become in Snowflake
Every mapping below is a default applied from the source schema.
| In PostgreSQL | In Snowflake | Worth knowing |
|---|---|---|
jsonb, json |
VARIANT |
The cleanest part of this route: the document stays queryable in the warehouse instead of becoming a string to parse on every read. |
| Array columns | VARIANT |
Snowflake's own ARRAY is semi-structured rather than typed, so arrays are carried as VARIANT. The elements survive; the element type declaration does not. |
timestamptz |
TIMESTAMP_TZ |
The one timestamp case that carries fully, zone awareness included. A plain timestamp becomes TIMESTAMP_NTZ, matching what it already was. |
numeric, decimal |
NUMBER(p,s) |
A declared precision and scale carry across. PostgreSQL also permits numeric with neither and no fixed limit; that becomes NUMBER(38,18), so a value beyond 38 digits needs the target column widened by hand. |
uuid |
VARCHAR(36) |
The canonical text form is exactly 36 characters, so nothing is truncated - but joins on it become string joins. |
inet, macaddr, interval |
VARCHAR(45), VARCHAR(17), VARCHAR(255) |
Each sized to hold its longest text form. PostgreSQL's operators on these types have no counterpart in the warehouse either. |
bytea |
VARCHAR, base64-encoded |
Encoding is what avoids UTF-8 validation errors during the load. Budget roughly 33% more storage for those columns, and decode on read. |
bigserial, serial, smallserial |
NUMBER(19,0), NUMBER(10,0), NUMBER(5,0) |
Existing key values arrive and the primary key is declared, but the nextval default does not follow: Snowflake is not set up to issue the next key. |
Enums, domains, money, cidr, xml, tsvector |
VARCHAR |
PostgreSQL's long tail, anything user-defined included, arrives as text - readable and comparable, but no longer constrained by its type. |
Several PostgreSQL schemas arrive as one
A Snowflake load puts every table in the single schema the stream targets. A source spread across public, billing and audit arrives flattened, so two tables sharing a name across those schemas will collide. Worth checking before the first run against a schema you did not design.
Constraints do not travel, and would enforce nothing if they did
Indexes, foreign keys and CHECK constraints are not created: Snowflake has no indexes, and accepts foreign key and CHECK syntax while enforcing neither. A schema that leans on the database for integrity keeps doing that in PostgreSQL, and the warehouse holds a copy that guarantees nothing about itself. Views, materialized views, functions and triggers stay in the source: Streams moves table data.
What CDC Requires Before It Will Start
Each of these is checked at configuration time, so a stream that cannot work is refused rather than started.
| Requirement | Value | What goes wrong without it |
|---|---|---|
wal_level |
logical |
The default WAL records enough to recover the database, not enough to describe which rows changed. Set in postgresql.conf; changing it needs a restart. |
| Replication slot | One per stream | The slot is what keeps WAL segments until the stream has read them. A slot nothing consumes holds WAL indefinitely, so drop slots you stop using. |
REPLICA IDENTITY |
FULL, per table |
Under the default identity an update or delete is logged with only the key columns, so the change reaching Snowflake describes a row it does not contain. |
| Primary key | On every replicated table | A separate requirement from the replica identity, and easy to conflate with it: the identity decides how much of a row is logged, the key is what current state is grouped by. |
| Snowflake authentication | Key pair | Snowpipe Streaming accepts a signed key and neither a password nor an access token. A load is unaffected; only CDC is refused. |
One LSN is not enough to order a transaction
Rows committed together share a single LSN, so log position alone cannot order them. Each change also carries its arrival order within the run, which is what stops an insert and a later delete of the same key from being resolved arbitrarily - a real defect before the tiebreaker existed, where the insert survived the delete.
Checking What Landed
Loaded tables appear in the Data Explorer beside the PostgreSQL connection they came from.
The identifier folding inverts
PostgreSQL folds unquoted names to lower case; Snowflake folds them to upper. Tables are created as quoted lowercase identifiers to stay faithful to the source, so SELECT * FROM orders looks for ORDERS and reports that it does not exist.
SELECT COUNT(*) FROM ANALYTICS.PUBLIC."orders";The Data Explorer quotes for you. This matters in Snowsight, or when a BI tool is pointed at the schema.
Open the file in your browser first
Your file is opened straight from your disk and stays on your computer. Each one gives you the file's structure, a read-only SQL editor over it, and a CSV export of the rows you select.
Neighbouring Routes
PostgreSQL to MySQL
Moving the application's database to another engine rather than feeding a warehouse. That is a conversion with a real schema on the far side, and it has a converter of its own.
See the PostgreSQL to MySQL converterThe Snowflake target itself
Load and CDC compared, the four steps of a load, the three objects a CDC stream creates and what each costs in storage.
Snowflake data loading and CDCLoad One PostgreSQL Table First, and Count It
Pick a table whose row count you already know and check it in the warehouse before pointing a schema at it. Runs on Linux, Windows, macOS and Docker.