Snowflake to Postgres: how to serve modeled warehouse data back to your application

How do you connect Snowflake to Postgres?

You connect Snowflake to Postgres by reading modeled tables out of Snowflake and writing them into Postgres tables, because the two are separate systems with no shared storage. Something sits in the middle: a managed connector, or a scheduled job that authenticates to Snowflake, selects or unloads the rows you want, casts each type, and upserts every batch into your application schema keyed on the primary key.

The connection has three parts. First, Snowflake credentials scoped to a role that can read the modeled schema, plus a warehouse to run the extract query. Because Snowflake compute is billed per second while a warehouse is running, the extract should be a lean, incremental query on a small warehouse rather than a broad scan on a large one. Second, a Postgres landing spot: a database, a schema, and a role that can create tables and run upserts. Third, the transfer loop itself, which reads the changed rows, lands each batch in a staging table, casts Snowflake types to Postgres types, and upserts into the target. The managed path is the Snowflake to Postgres sync, which owns the credentials, the watermark, the type casting, the staging tables, and the idempotent writes, so nobody hand-maintains a reverse ETL script.

Note the direction. Loading raw and semi-structured data up into the warehouse for modeling is the forward path, Postgres to Snowflake, and it optimizes for throughput and analytics. Serving modeled results back down to the app is the reverse path this article covers, and it optimizes for low latency and idempotence. Same two systems, opposite goals.

Why push Snowflake data to Postgres instead of querying the warehouse directly?

Because a warehouse query is the wrong shape for an app request: it is optimized for scanning large analytical workloads, not for returning a single user's row in a few milliseconds, and every query wakes a warehouse that Snowflake bills per second while it runs. A per-request warehouse call is slow and expensive; a local Postgres read of the same modeled row is fast and free per query.

The economics and the latency both point the same way. Snowflake warehouses are columnar and built for aggregations over millions of rows, so a point lookup carries the overhead of spinning up compute and scanning micro-partitions, and it competes with your analysts' queries for the same warehouse. Put a user-facing feature behind that and every page load pays warehouse credits and warehouse latency. The common workaround is a hand-rolled cache, and hand-rolled caches drift: the script that refreshes them fails quietly, nobody notices, and a feature shows a stale score for a week. Reverse ETL replaces the cache with a governed sync. The typical use cases are powering an application feature (a recommended list, a plan tier, a computed limit), rendering user-facing dashboards inside the product, and pushing operational segments or scores (a churn risk, a lead grade, a lifetime-value band) back where the app can act on them. Once the modeled data lives in Postgres it also joins cleanly to your live app tables, so a non-technical teammate can ask the data a plain-English question and get the SQL answer without waiting on the data team to write a warehouse query.

How do you map Snowflake data types to Postgres?

Map each Snowflake type to its closest Postgres type with precision preserved: NUMBER(p,s) to NUMERIC(p,s), FLOAT to DOUBLE PRECISION, the semi-structured VARIANT, OBJECT, and ARRAY to JSONB, and the timestamps to TIMESTAMPTZ in UTC. The two traps are numeric precision and identifier case, so handle both explicitly.

Snowflake's NUMBER defaults to NUMBER(38,0), a 38-digit integer, and modeled columns often carry a declared scale like NUMBER(19,4) for money. Carry that precision straight into NUMERIC(19,4) so cents never drift; do not collapse it to a float. The semi-structured types are the other headline: VARIANT holds arbitrary JSON-shaped values, and it maps to Postgres JSONB so nested payloads survive the trip and stay queryable with Postgres JSON operators. The timestamp story matters too. TIMESTAMP_NTZ stores a wall clock with no zone, while TIMESTAMP_TZ carries an offset; normalize both to TIMESTAMPTZ in UTC on the way in so your app never guesses a zone.

Snowflake type	Postgres type	Notes
NUMBER(p,s)	NUMERIC(p,s)	Default is NUMBER(38,0). Carry the declared precision and scale so money and counts stay exact
FLOAT / DOUBLE	DOUBLE PRECISION	Binary floating point in both. Never use it for currency
VARCHAR / STRING	TEXT	Postgres TEXT has no length penalty, so a declared Snowflake length is optional
BOOLEAN	BOOLEAN	Direct one-to-one mapping
DATE	DATE	Calendar date, no time component in either system
TIMESTAMP_NTZ	TIMESTAMP	Wall clock with no zone. Prefer normalizing to TIMESTAMPTZ in UTC at load time
TIMESTAMP_TZ	TIMESTAMPTZ	Carries an offset. Store as UTC so the app never guesses a zone
VARIANT	JSONB	Semi-structured JSON value. JSONB keeps it queryable with Postgres JSON operators
OBJECT	JSONB	Key-value map. Lands as a JSON object
ARRAY	JSONB	JSON array. Use JSONB rather than a Postgres array when elements are mixed or nested
BINARY	BYTEA	Raw bytes. Watch encoding on the way out of Snowflake
Unquoted identifier ORDERS.TOTAL_USD	orders.total_amount	Snowflake folds unquoted identifiers to UPPERCASE; Postgres folds to lowercase. Map the case explicitly or columns miss

The identifier row deserves emphasis because it bites silently. Snowflake stores unquoted identifiers in UPPERCASE and treats them case-insensitively, so orders and ORDERS are the same object there. Postgres folds unquoted identifiers to lowercase. If your extract selects TOTAL_USD and your app expects total_amount, the mapping has to rename and recase deliberately, which is exactly the field mapping the managed Snowflake to Postgres integration captures in the browser so the names line up before a single row moves.

How do you sync Snowflake to Postgres incrementally?

Sync incrementally by extracting only rows that changed since the last run, either with a watermark column or with a Snowflake STREAM. A watermark filters on a monotonically increasing column such as an updated_at timestamp or a load id; a STREAM records the insert, update, and delete changes on a table so you can consume just the delta. Both beat re-reading the whole model every cycle.

The watermark approach is the simple default. Store the maximum updated_at you have already loaded, then run SELECT ... FROM analytics.orders WHERE updated_at > :last_watermark against a small warehouse, land the result, and advance the watermark to the new maximum. Overlap the window by a few minutes so a row committed slightly out of clock order is never skipped, which is safe because the load is idempotent. The STREAM approach is the CDC upgrade: a Snowflake stream tracks row-level changes against a source table or view and exposes them as change records with metadata for the action, so a delete in the model can flow through as a delete in Postgres rather than a silently orphaned row. Streams shine when the model is materialized as a table (dbt commonly materializes models as tables) and you need deletes to propagate, while the watermark is enough for append-heavy or update-only tables. Either way, keep the extract query narrow so the warehouse runs for seconds, not minutes, since it is billed for every one of them.

Extraction method	What it is	When it fits
Full reload	A SELECT * of the whole model each cycle, truncate and reload the Postgres table	The first backfill and small reference tables. Wasteful and slow to rerun on large models, and it hammers warehouse compute
Watermark column	WHERE updated_at > :last_watermark resuming from your stored maximum, with a small overlap window	The steady-state default for append and update heavy tables. Misses hard deletes, so pair it with reconciliation
Snowflake STREAM (CDC)	A stream on the source table or view that exposes row-level insert, update, and delete change records	When deletes must propagate to Postgres and the model is materialized as a table. More setup than a watermark
Managed sync tool	A hosted connector that owns the watermark, type casting, upserts, deletes, and backoff	When you would rather not maintain the extract loop and the reverse-ETL plumbing yourself

How do you keep a Snowflake Postgres sync from creating duplicates?

Make the load idempotent. Give every target table a primary key, land each batch in a staging table, and upsert with INSERT ... INTO ... ON CONFLICT (id) DO UPDATE SET .... Because the key is stable, replaying an overlapping watermark window or retrying a failed batch updates the existing row in place instead of inserting a second copy, so the target converges to the state a clean run would produce.

The load has a consistent shape. Read the changed rows, land them in a staging table, cast every column to its Postgres type, then upsert from staging into the target in one statement so inserts and updates happen together. The conflict target is the primary key you carried down from Snowflake (in the sample mapping, ORDERS.ID becomes orders.id), and the DO UPDATE SET clause overwrites the mutable columns with the freshest values. Deletes are the case a watermark cannot catch on its own: a row removed from the model never appears in an updated_at > query, so a watermark-only sync keeps rows the warehouse no longer has. Two fixes, and you want one of them. Consume a Snowflake STREAM so deletes arrive as change records you apply against the target, or run a periodic reconciliation that compares the id set over a trailing window and removes local ids the model no longer returns. Keep a loaded_at column on every table so freshness checks and drift debugging are a one-line query.

How often should you sync Snowflake to Postgres?

Match the interval to how fresh the served feature needs to be and to how often the model rebuilds. For most reverse ETL, every fifteen minutes to hourly on the watermark is the right steady state, since the upstream dbt models usually rebuild on a schedule anyway and syncing faster than the model changes just wakes a warehouse for nothing. Real-time features are the exception and call for a stream.

Two things set the ceiling. First, freshness: a churn score that updates nightly gains nothing from a five-minute sync, while an in-product dashboard that users refresh all day wants fifteen minutes or better. Second, the model's own cadence: if the dbt job that materializes analytics.orders runs hourly, sync shortly after each run finishes rather than on a fixed clock that lands mid-rebuild. Trigger the sync off the model's completion when you can. Backfilling is a different job from the steady state: run a full extract per table, chunk the large ones by an id or date range so each chunk is a bounded, retriable load, land into staging, then upsert. Only after the backfill lands should you set the watermark, and set it to the start time of the backfill so anything that changed while it ran gets picked up on the first incremental pass. Metered per-row pipeline pricing interacts badly with frequent reverse ETL, since more syncs mean more counted rows exactly when you want fresher data. Our flat pricing is by plan rather than per row, so a tighter interval does not change the bill. If you want to see the pair running against your own model first, book a walkthrough or map it yourself with the Snowflake to Postgres connector.