Lakewright solutions / brief

Stream Postgres change data capture into Snowflake without Kafka or Snowpipe

Postgres inserts, updates and deletes land in an append-only Apache Iceberg changelog that Snowflake reads in place — no Kafka, no Debezium cluster, no Snowpipe, and no connector running inside your warehouse.

What Snowflake actually reads

Snowflake reads the landed table through an Iceberg external volume and a catalog integration — no copy, and no ingestion job on the Snowflake side. The credits you spend are the credits you spend querying, which is a different cost shape from a pipeline that pushes rows in continuously whether anyone reads them or not.

Each changelog row carries four control columns — _op (I/U/D/R), _position (the source LSN), the capture time, and the key — followed by the row image.

Current state without materialising anything

The cheapest correct answer is a view. Nothing is copied, nothing runs on a schedule, and it is never stale:

CREATE OR REPLACE VIEW orders AS
SELECT * EXCLUDE (_op, _position, _captured_at_ms, _key)
FROM   orders_changes
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY _position DESC) = 1
   AND  _op <> 'D';

Latest row per key, deletes dropped. For dimension-sized tables and dashboards that run a few hundred times a day this is usually where the argument ends — you have current state and you paid nothing to keep it.

Current state as a real table

When the changelog is large enough that re-deriving state on every query stops being free, materialise it. A Dynamic Table keeps the same SQL and hands Snowflake the refresh problem:

CREATE OR REPLACE DYNAMIC TABLE orders
  TARGET_LAG = '5 minutes' WAREHOUSE = loading AS
SELECT * EXCLUDE (_op, _position, _captured_at_ms, _key)
FROM   orders_changes
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY _position DESC) = 1
   AND  _op <> 'D';

A stream plus a task running MERGE is the classic equivalent and behaves identically. Which you choose is a Snowflake question, not a Lakewright one: the changelog is the same table underneath, and you can change your mind later without re-ingesting anything.

Why the changelog is the thing we ship

A mirrored table overwrites history by definition. The changelog keeps it, so the current-state view is a derivation you can always rebuild — and the intermediate states, which is where most of the interesting questions live, are still there. Deletes are appended _op=D rows rather than destructive deletes, which is the form auditors ask for.

FAQ