# Data Change Feed

Snapshots record every committed change to an Altertable catalog. [Time travel](/docs/lakehouse/features/time-travel.md) 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](/docs/lakehouse/features/transactions.md) creates one snapshot, and bounds can be snapshot IDs or timestamps in either API.

Run the statements below through Altertable's [SQL engine](/docs/lakehouse/sql-engine.md) against an [Altertable catalog](/docs/lakehouse.md) you own.

## Time travel vs change feed

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

```sql
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:

```sql
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](/docs/lakehouse/features/time-travel.md) 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](/docs/lakehouse/features/time-travel.md#snapshots).

```sql
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:

```sql
-- 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](/docs/lakehouse/features/time-travel.md). Snapshots 3 and 4 each add one kind of delta.

### Changes from a single snapshot

Inspect everything that happened in snapshot 2:

```sql
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:

```sql
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:

```sql
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`:

```sql
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:

```sql
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](/docs/lakehouse/features/time-travel.md#snapshot-retention) with care when downstream pipelines depend on historical change feeds or long-running `AT` queries.

## Learn more

- **[Time Travel](/docs/lakehouse/features/time-travel.md)**: Query table state at a snapshot and list snapshot history
- **[Transactions](/docs/lakehouse/features/transactions.md)**: Each committed transaction creates one snapshot
- **[SQL Engine](/docs/lakehouse/sql-engine.md)**: DuckDB dialect and federated queries