How to build a change data capture pipeline

What matters here

• Read the log, not a timestamp column. Polling misses hard deletes and any update that does not touch the column.
• Overlap the snapshot and the stream. Rows written during the snapshot belong to both, and abutting them loses those rows.
• Order matters per key, not globally. Two updates to one row arriving backwards leave the wrong value in place.
• A delete is an event. Dropping it turns the target into a store of everything that ever existed.
• Checkpoint the log position after the write lands, never before.
• A source schema change is the most common way a pipeline stops. Detect it and pause rather than silently dropping the column.

Who this is for

You run data or platform engineering. The warehouse is loaded by nightly queries, it disagrees with production often enough that people check both, and nobody trusts a row count.

How it works in practice

The path from a row changing in production to that change being true downstream.

1. 1

   The connector reads the database log

   The write-ahead log or binlog, which is the only place a hard delete appears.

2. 2

   An initial snapshot runs alongside the stream

   Overlapping, so rows written during the snapshot are captured by one or the other.

3. 3

   Each change becomes an event with its key

   Operation, key, before and after images, and the log position it came from.

4. 4

   Events are applied in per-key order

   Two updates to one row must land in the order the database made them.

5. 5

   Position is checkpointed after the write

   So a crash replays rather than skips.

6. 6

   A schema change pauses rather than drops

   Because a silently dropped column is discovered a month later.

Paste these into Claude Code or Codex with the Tray Headless plugin installed. Each stage runs on its own. The systems named in them are the worked example rather than a requirement, and every prompt says so.

Once per project, run /tray-workflows:set-workspace to pick the workspace these build in. Point it at a sandbox first.

1. 1

   Set up and read the log

   A timestamp column cannot see a hard delete.

   Headless skills build-workflow tray-patterns

   Copy

   Use build-workflow. The systems in play are Postgres and Snowflake, or
   whatever we run in those seats.
   
   Capture changes from the write-ahead log, not from a query over a
   modified timestamp. For each change record the operation, the primary
   key, the before and after images, the transaction identifier and the log
   position.
   
   The before image matters more than it looks. Without it you cannot tell
   which fields actually changed, and downstream consumers end up
   reprocessing whole rows because the event does not say what moved.
   
   Confirm the source is configured for logical replication and that the
   replication slot has a retention policy. An unconsumed slot holding log
   files is how a capture pipeline takes the production database down, and
   it is the failure that gets this pattern banned in an organisation.

   Monitor replication slot lag from the first day. A stalled consumer makes the source database retain log files until the disk fills, so the pipeline failure becomes a production incident.

2. 2

   Overlap the snapshot with the stream

   Rows written during the snapshot belong to both.

   Headless skills build-workflow

   Copy

   Use build-workflow. Start streaming before the snapshot, and keep
   streaming throughout:
   
     Note the current log position
     Begin buffering the stream from that position
     Take the snapshot of existing rows
     Apply the buffered stream on top, keyed, so a row that changed during
     the snapshot ends at its latest value
   
   Never take a snapshot and then start the stream from afterwards. Every
   row written in the gap is lost, silently, and shows up months later as a
   handful of records that were correct in production and never arrived.
   
   Make the snapshot resumable. A snapshot of a large table that fails at
   80% and restarts from zero is how this stalls for a week.

3. 3

   Preserve order per key, and treat a delete as an event

   Two updates arriving backwards leave the wrong value in place.

   Copy

   Preserve ordering per primary key. Global ordering is not required and
   is expensive; per-key ordering is required and is not optional.
   
   If events are partitioned, partition by primary key so every change to
   one row travels the same path.
   
   Handle deletes explicitly:
   
     A hard delete is an event with the key and the before image
     Prefer a soft delete in the target: mark the row deleted with a
     timestamp instead of removing it
     Never silently drop a delete event, which turns the target into a store
     of everything that has ever existed
   
   Handle a primary key update as a delete followed by an insert, because
   that is what it is, and treating it as an update leaves an orphan behind
   under the old key.

4. 4

   Checkpoint after the write, and survive a schema change

   Checkpointing first turns a crash into silent data loss.

   Headless skills tray-gotchas tray-patterns

   Copy

   Use tray-gotchas, then get the failure behaviour right.
   
   Commit the log position only after the target write has landed. On a
   crash this replays some events, so make the target write idempotent,
   keyed on primary key and log position. Replaying is cheap and skipping is
   not recoverable.
   
   On a source schema change:
   
     A new column is added to the target and backfilled as null
     A dropped column is retained in the target with a note, never dropped
     in step
     A type change pauses the pipeline and alerts, because coercing silently
     is how a numeric column becomes text downstream
     A new table is not captured until somebody says it should be
   
   Send anything unprocessable to a dead letter store with the full event,
   and alert on the first one. A dead letter queue nobody watches is a data
   loss mechanism with extra steps.

5. 5

   Reconcile, because a pipeline that looks healthy can still be wrong

   Lag is not correctness.

   Headless skills tray-patterns

   Copy

   Reconcile periodically rather than trusting the stream:
   
     Row counts per table, source against target
     Checksums over a sample of keys, which catches a value that drifted
     without the count changing
     The newest log position consumed against the newest produced, which is
     the real lag
   
   Report: lag in seconds and in events, replication slot size, dead letter
   volume, snapshot progress, and reconciliation differences by table.
   
   Replication slot size is the one that becomes an incident. Everything
   else degrades; that one fills a disk on the source database.

6. 6

   Test it, then hand the table list to data engineering

   Because tables get added and schemas move.

   Copy

   Run the per-step schema checks and the whole-workflow audit before this
   touches production. Delete a row in a sandbox source and confirm it
   arrives as a delete event. Kill the pipeline mid-snapshot and confirm it
   resumes without duplicating or losing rows.
   
   Then open the same workflow in Tray Build so data engineering can add
   tables, set the soft-delete behaviour and adjust the reconciliation
   cadence in the visual canvas.

What it connects to

Capture happens at the source log and lands in the warehouse, with somewhere to put what cannot be processed.

Named systems are the ones most teams run, not the only ones that work. Each is an authentication in your Tray workspace, referenced by name, so the workflow never holds a credential. Where we have a connector page, the name links to it.

Questions people ask

Related guides