# Time Travel

Every catalog snapshot represents a consistent state of the database. Altertable keeps a record of historic snapshots and their changesets until compaction removes snapshots that fall outside [retention](#snapshot-retention).

Time travel lets you query table state as of any recorded snapshot. Specify the snapshot with a version ID or a timestamp. Use the `snapshots` function to list valid snapshots for a catalog.

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

## Query at a snapshot

Query a table at a specific snapshot version:

```sql
SELECT sku, label
FROM my_catalog.main.product_skus AT (VERSION => 3);
```

Query a table as it was at a point in time:

```sql
SELECT sku, label
FROM my_catalog.main.product_skus AT (TIMESTAMP => now() - INTERVAL '1 week');
```

Time travel applies to individual table references in a query. Join current and historical data by mixing plain and `AT` table references in the same statement. To inspect row-level inserts, updates, and deletes instead of full table state, use the [data change feed](/docs/lakehouse/features/data-change-feed.md).

## Snapshots

Snapshots are commits to a catalog. Each snapshot applies a set of changes — creating tables, inserting or deleting rows, altering schemas — that move the catalog to a new consistent state. Every write goes through a snapshot; changes cannot land outside a transaction.

### List snapshots

Query all snapshots and their changesets:

```sql
SELECT *
FROM my_catalog.snapshots();
```

After creating a table and inserting a row, the history might look like this:

| snapshot\_id | snapshot\_time         | schema\_version | changes                                | author | commit\_message | commit\_extra\_info |
| ------------ | ---------------------- | --------------- | -------------------------------------- | ------ | --------------- | ------------------- |
| 0            | 2026-04-10 12:57:02+02 | 0               | `{schemas_created=[main]}`             | NULL   | NULL            | NULL                |
| 1            | 2026-04-10 12:57:02+02 | 1               | `{tables_created=[main.product_skus]}` | NULL   | NULL            | NULL                |
| 2            | 2026-04-10 12:57:02+02 | 1               | `{inlined_insert=[1]}`                 | NULL   | NULL            | NULL                |

### Current snapshot helpers

Read the latest snapshot ID:

```sql
FROM my_catalog.current_snapshot();
```

When multiple connections write to the same catalog, read the latest snapshot committed on the current connection:

```sql
FROM my_catalog.last_committed_snapshot();
```

A new connection returns `NULL` from `last_committed_snapshot()` until it commits its own transaction.

### Commit messages

Attach author, message, and optional metadata to a snapshot inside a transaction:

```sql
BEGIN;

INSERT INTO my_catalog.main.product_skus VALUES ('SKU-003', 'Walnut desk');

CALL my_catalog.set_commit_message(
  'alex',
  'Add walnut desk SKU',
  extra_info => '{"ticket": "CAT-42"}'
);

COMMIT;
```

Those fields appear on the corresponding row in `snapshots()`:

| snapshot\_id | author | commit\_message     | commit\_extra\_info    |
| ------------ | ------ | ------------------- | ---------------------- |
| 3            | alex   | Add walnut desk SKU | `{"ticket": "CAT-42"}` |

Use commit messages when auditing who changed a catalog and why.

## Snapshot retention

The **Snapshot retention** setting on an Altertable catalog controls how long old snapshots are kept. Retention applies in the catalog UI when you create or edit a catalog.

| Value    | Behavior                                      |
| -------- | --------------------------------------------- |
| Blank    | Retain snapshots indefinitely                 |
| `0`      | Disable snapshot retention after cleanup runs |
| `N` days | Retain snapshots newer than `N` days          |

Set a retention period when storage cost or compliance requires old table versions to expire. Leave it blank when long-running time travel and historical auditability matter more than storage cleanup.

Expired snapshots limit how far back you can query with `AT` and how much history the [data change feed](/docs/lakehouse/features/data-change-feed.md) can return.

## Learn more

- **[Transactions](/docs/lakehouse/features/transactions.md)**: Each committed transaction creates one snapshot
- **[Data Change Feed](/docs/lakehouse/features/data-change-feed.md)**: Read row-level changes between snapshots
- **[Altertable Catalogs](/docs/lakehouse.md)**: Catalogs for storing and querying your data