# 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.**
`lakewright listen` speaks Postgres logical decoding directly,
snapshots the table first with parallel chunked reads, then appends
every change as a row.

## 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:
```sql
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:
```sql
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
**Does this need Kafka?** No. One process speaks Postgres's built-in
logical decoding; there is no broker, no Connect cluster, and no JVM.
**How does it resume?** The LSN is confirmed only after the Iceberg
commit lands, so a crash re-delivers rather than drops, and a restart
picks up from the last confirmed position.
**MySQL, SQL Server, Oracle?** The same command with a different
--source flag, and the changelog contract is identical.
**Replication slot risk?** Slot lag and retained WAL are first-class
metrics; the listener warns before a forgotten slot fills a disk.

## The question that matters
**"How long does an order actually sit in 'pending' before it ships, and is that getting worse?"**

The current-state table cannot answer this, and no amount of warehouse compute
will change that: it knows the order is shipped now, and has forgotten every
moment before that. The duration only exists in the transitions.

Against the changelog it is a gap between two events per key, and in the sample
data the median is unremarkable while the mean is roughly twice it — the familiar
signature of a small tail dragging an average around. The tail is the finding.
It concentrates in a single fulfilment path rather than spreading evenly, which
turns "our shipping is slow" into a specific question for a specific owner, and
the trend line says whether last quarter's fix actually held.

Current-state tables tell you what is true now. The changelog tells you what
happened, and how long it took, which is usually where the money is.