RESTORE TO TIMESTAMP

Restores a Delta table to the state it had at a specific point in time.

Category: maintenanceDeltaForge extension

Syntax

RESTORE <table> TO TIMESTAMP '<timestamp>'

Description

## Overview RESTORE TO TIMESTAMP reverts a Delta table to its state at a specific point in time. The system locates the table version whose commit timestamp is the latest one at or before the specified timestamp, then restores to that version. ## How It Works 1. **Timestamp resolution**: Scans the table's commit timestamps to find the latest version committed at or before the specified timestamp. 2. **Snapshot diff**: Computes the difference between the resolved version and the current version, generating AddFile and RemoveFile actions. 3. **Commit**: Writes a new table version that matches the state at the resolved timestamp. The underlying mechanics are identical to RESTORE TO VERSION; the only difference is how the target version is determined. See RESTORE TO VERSION for full details on the restore process, data file dependencies, and version history behavior. ## Timestamp Resolution The timestamp is compared against each version's commit timestamp (recorded in the CommitInfo action of each log entry). The system selects the highest version number whose commit timestamp is at or before the specified time. If no version exists at or before the timestamp (for example, the timestamp predates the table's creation), the operation returns an error. ## Data File Dependency As with RESTORE TO VERSION, the resolved version's data files must still exist on storage. If VACUUM has removed them, the restore fails. Ensure that the VACUUM retention period covers the time range you may need to restore to. ## Result Set Returns a message with the following information: - Target version restored to - Number of files added (restored from the target version) - Number of files removed (from the current version) - New table version number ## Access Control | Privilege | Object | Notes | |-----------|--------|-------| | Ownership or write | Table | Required to commit the restore operation. | ## Compatibility RESTORE TO TIMESTAMP is a DeltaForge extension implementing the Delta Lake timestamp-based restore protocol.

Parameters

Name	Type	Description
`table`		Specifies the name or path of the Delta table to restore. The table must be registered in the session. Fully qualified names (zone.schema.table) are supported.
`timestamp`		Specifies the point in time to restore to. Accepted formats include RFC3339 (e.g., '2024-01-15T08:00:00Z'), datetime without timezone ('2024-01-15 08:00:00'), or epoch milliseconds as an integer. The system resolves the timestamp to the latest table version whose commit timestamp is at or before the specified time.

Examples

-- Restore to a specific datetime
RESTORE orders TO TIMESTAMP '2024-01-15 08:00:00';

-- Restore using AS OF syntax with RFC3339 format
RESTORE orders TO TIMESTAMP AS OF '2024-06-01T00:00:00Z';

-- Restore using epoch milliseconds
RESTORE orders TO TIMESTAMP 1705305600000;

-- Restore with the optional TABLE keyword
RESTORE TABLE warehouse.sales.transactions TO TIMESTAMP '2024-03-01 12:00:00';

-- Restore to the state before yesterday's batch run
DESCRIBE HISTORY orders;
-- Identify the timestamp before the problematic load
RESTORE orders TO TIMESTAMP '2024-06-14 23:59:59';

Pitfalls

Timestamp resolution depends on commit timestamps in the transaction log. If commit timestamps are not monotonically increasing (due to clock skew across writers), the resolved version may not match expectations.
The supported timestamp formats are RFC3339 ('2024-01-15T08:00:00Z') and 'YYYY-MM-DD HH:MM:SS'. Other date formats may fail to parse.
If VACUUM has deleted files required by the resolved version, the restore fails. Use DESCRIBE HISTORY to verify that the target version's files are still intact.
Restoring to a timestamp near a version boundary may yield unexpected results. If precision matters, use RESTORE TO VERSION with an explicit version number instead.