CDC (Change Data Capture)

Change Data Capture reads a database's change log instead of querying its tables. Every insert, update and delete reaches your warehouse in near real time, and Weld never scans the table after the first copy.

You turn it on per table, with the Change Data Capture option in the table's settings.

When to use it

A normal incremental sync asks the table for rows whose cursor column, such as updated_at, has moved since the last sync. That misses rows that were deleted, and rows updated without touching the cursor. A full sync catches everything but reads the whole table every time.

Use CDC when:

• You need deletes and every update, not just new rows.
• Tables are large enough that full syncs put load on the database or take too long.
• You want changes in the warehouse within minutes rather than at the next scheduled sync.

For small tables, or data where a few hours of delay is fine, a scheduled full or incremental sync is simpler to run.

Supported databases

Before you start

• CDC is part of the Business plan. On other plans the option is shown but can't be turned on.
• Prepare the database by following its setup guide above. Each one covers the replication user, the log settings, and how long the database must keep its log.
• Every table you capture needs a primary key or a unique index, so Weld can apply updates and deletes to the right row. Tables without one don't get the option.

Turn on CDC for a table

1. Add the data source in Weld, or open an existing one and click Configure. See Data sources.
2. For PostgreSQL, pick the CDC Slot name and CDC Publication name under CDC Settings (1). Weld lists the replication slots and publications it finds in the database.
3. Click the cogwheel next to a table (2) and turn on Change Data Capture (3). Tables that capture changes show a CDC label (4).
4. Click Update Sync, or finish adding the data source.

If the database isn't ready, the table list shows Change Data Capture is not available yet with the reason, such as wal_level not being logical, and the option stays hidden until it's fixed.

Weld first copies the whole table, then switches to reading the change log. On large tables the first copy can take a while.

Turning on CDC also changes the table's other options:

• Protected is turned on, so a schema change never triggers a full reload on its own.
• Always full sync and Scheduled full sync aren't available, since CDC replaces them.
• Setting the table back to incremental sync turns CDC off.

What changes in your warehouse

A CDC table has two extra columns next to _weld_synced:

Deleted rows stay in the table, marked with _weld_deleted_at, so you keep their history. To use only live rows in a Weld data model, filter them out:

select *
from {{raw.postgres.orders}}
where _weld_deleted_at is null

{{raw.postgres.orders}} is how a model refers to the synced orders table. In SQL you run directly in your warehouse, use the table's warehouse name instead, such as postgres.orders.

See Weld managed fields for every column Weld adds.

Keep the change log available

Weld reads changes from the database's log, so the database has to keep the log until Weld has read it.

• MySQL and MariaDB expire binary logs after a set time. Weld requires at least 6 hours, and more is safer: if Weld can't read for longer than that, the missed changes are gone and the table needs a fresh copy.
• PostgreSQL keeps write-ahead log for the replication slot until Weld reads it. If a CDC sync is paused or deleted while the slot remains, the log keeps growing and uses disk on the whole server. Drop the replication slot when you stop using CDC.

Each setup guide covers its database's settings in detail.

Related

• Data sources
• Capture deletes, for marking deleted rows on sources without CDC
• History tables
• Weld managed fields