Altertable

Data Change Feed

Snapshots record every committed change to an Altertable catalog. Time travel reads state — what a table looked like at a snapshot or timestamp. The data change feed reads deltas — which rows were inserted, updated, or deleted between two bounds.

Both features share the same snapshot timeline. Each committed transaction creates one snapshot, and bounds can be snapshot IDs or timestamps in either API.

Run the statements below through Altertable's SQL engine against an Altertable catalog you own.

Time travel vs change feed

Use time travel when you need a full table image at a point in time:

1
2
SELECT sku, label
FROM my_catalog.main.product_skus AT (VERSION => 2);

Use the change feed when you need the row-level mutations that happened in or between snapshots:

1
2
FROM my_catalog.table_changes('product_skus', 2, 4)
ORDER BY snapshot_id;

Goal	Use
Reproduce a report as it ran last Tuesday	Time travel with `AT (TIMESTAMP => …)`
Sync only new and changed rows to a warehouse	`table_insertions` / `table_changes` between snapshot bounds
Investigate who removed a SKU and when	`table_deletions` or `table_changes` for the suspect snapshot range
Compare catalog state before and after a deploy	Time travel at two snapshot versions

Finding snapshot bounds

Pass snapshot IDs or timestamps to table_changes and its helpers. List available snapshots with snapshots(), or read the latest ID with current_snapshot() — the same helpers described in Time travel.

1
2
3
SELECT snapshot_id, snapshot_time, changes, author, commit_message
FROM my_catalog.snapshots()
ORDER BY snapshot_id;

Commit messages from set_commit_message are especially useful here: they label snapshots in the change feed the same way they appear in the snapshot history.

Example workflow

The workflow below uses the same product_skus table referenced in the time travel docs. Each comment marks the snapshot created by that statement:

1
2
3
4
5
6
7
8
9
10
11
-- Snapshot 1
CREATE TABLE my_catalog.main.product_skus (sku VARCHAR, label VARCHAR);

-- Snapshot 2
INSERT INTO my_catalog.main.product_skus VALUES ('SKU-001', 'Oak shelf'), ('SKU-002', 'Maple bench');

-- Snapshot 3
DELETE FROM my_catalog.main.product_skus WHERE sku = 'SKU-001';

-- Snapshot 4
UPDATE my_catalog.main.product_skus SET label = concat(label, ' (v2)');

At snapshot 2 the table contains two rows — the same result as AT (VERSION => 2) in Time travel. Snapshots 3 and 4 each add one kind of delta.

Changes from a single snapshot

Inspect everything that happened in snapshot 2:

FROM my_catalog.table_changes('product_skus', 2, 2);

snapshot_id	rowid	change_type	sku	label
2	0	insert	SKU-001	Oak shelf
2	1	insert	SKU-002	Maple bench

Changes across multiple snapshots

Follow the delete in snapshot 3 and the update in snapshot 4:

1
2
FROM my_catalog.table_changes('product_skus', 3, 4)
ORDER BY snapshot_id;

snapshot_id	rowid	change_type	sku	label
3	0	delete	SKU-001	Oak shelf
4	1	update_preimage	SKU-002	Maple bench
4	1	update_postimage	SKU-002	Maple bench (v2)

Changes over a time range

Bounds accept timestamps as well as snapshot IDs:

FROM my_catalog.table_changes('product_skus', now() - INTERVAL '1 week', now());

table_changes

table_changes takes a table name and two inclusive bounds: a start snapshot and an end snapshot. Bounds can be snapshot IDs or timestamps.

The result uses the table schema as of the end snapshot, plus three extra columns:

Column	Description
`snapshot_id`	The snapshot that made the change
`rowid`	The row identifier of the changed row
`change_type`	`insert`, `update_preimage`, `update_postimage`, or `delete`

Updates appear as two rows: update_preimage is the row before the update; update_postimage is the row after the update.

When the table schema changes between the start and end bounds, results use the schema as of the end snapshot. If a column is dropped, it is omitted from the entire result. If a column is added, changes before the addition show the column's default value.

table_deletions

table_deletions returns only rows deleted between two snapshots. It takes the same arguments as table_changes:

FROM my_catalog.table_deletions('product_skus', 3, 3);

snapshot_id	rowid	sku	label
3	0	SKU-001	Oak shelf

The result includes snapshot_id and rowid, followed by the table's data columns. There is no change_type column because every row is a deletion.

table_insertions

table_insertions returns only rows inserted between two snapshots:

FROM my_catalog.table_insertions('product_skus', 2, 2);

snapshot_id	rowid	sku	label
2	0	SKU-001	Oak shelf
2	1	SKU-002	Maple bench

Compaction and retention

Compaction that expires snapshots limits both time travel and the change feed. If deleted rows are removed during compaction, table_deletions can no longer return them even though you know they existed.

Set snapshot retention with care when downstream pipelines depend on historical change feeds or long-running AT queries.

Learn more

Time Travel: Query table state at a snapshot and list snapshot history
Transactions: Each committed transaction creates one snapshot
SQL Engine: DuckDB dialect and federated queries