Time Travel with Iceberg
Just like native Snowflake tables, Apache Iceberg tables natively support Time Travel. This allows you to query the table exactly as it existed at a specific point in time in the past.
However, because Iceberg is an open-source format, the mechanics of how Time Travel works under the hood differ slightly from native Snowflake Time Travel.
Topics Covered
- Iceberg Snapshots and Time Travel Mechanics
- Comparison with Snowflake Native Time Travel
- Querying Historical Versions
- Retention and Cleanup
Iceberg Snapshots vs Native Time Travel
Every time you perform a DML operation (INSERT, UPDATE, DELETE) on an Iceberg table, Iceberg creates a new Snapshot. A snapshot is a metadata file that contains a complete list of all the data files that make up the table at that exact moment.
| Feature | Snowflake Native Time Travel | Iceberg Time Travel |
|---|---|---|
| Mechanics | Based on internal micro-partition versioning. | Based on Iceberg Snapshot metadata files. |
| Max Retention | 90 Days (Enterprise Edition). | Configurable (depends on snapshot expiration settings). |
| Interoperability | Only queryable within Snowflake. | Queryable by Snowflake, Spark, Trino using the same syntax. |
Hands-On Demonstration: Iceberg Time Travel
Let's simulate a scenario where we accidentally delete critical financial data, and we need to use Time Travel on an Iceberg table to recover it.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_DATALAKE_DB;
USE DATABASE BEEMS_DATALAKE_DB;
CREATE SCHEMA IF NOT EXISTS TIME_TRAVEL_LAB;
USE SCHEMA TIME_TRAVEL_LAB;
2. Creating the Iceberg Table and Initial Snapshot
(Note: These CREATE ICEBERG TABLE statements assume you have an External Volume named MY_ICEBERG_VOL configured. If not, treat this as conceptual).
-- Create our Managed Iceberg Table
CREATE OR REPLACE ICEBERG TABLE TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS (
TXN_ID STRING,
ACCOUNT_NAME STRING,
STATE STRING,
AMOUNT NUMBER(10,2)
)
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'MY_ICEBERG_VOL'
BASE_LOCATION = 'transactions/';
-- Insert Initial Data (Snapshot 1 created)
INSERT INTO TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS VALUES
('TXN-101', 'Arjun Nair', 'Kerala', 50000.00),
('TXN-102', 'Neha Reddy', 'Telangana', 25000.50),
('TXN-103', 'Siddharth Patel', 'Gujarat', 100000.00);
SELECT * FROM TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS;
3. Creating Subsequent Snapshots (The "Mistake")
Now, let's pretend a junior data engineer runs a DELETE statement without a WHERE clause, accidentally wiping out all transactions.
-- Wait 5 seconds to ensure timestamps are distinctly different
-- CALL SYSTEM$WAIT(5);
-- The Accidental Delete (Snapshot 2 created)
DELETE FROM TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS;
-- Oh no, the table is empty!
SELECT * FROM TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS;
4. Querying Historical Versions (Time Travel)
Because Iceberg retained Snapshot 1, we can easily query the data as it looked 5 minutes ago.
You can use the exact same Time Travel syntax (AT or BEFORE) that you use for native Snowflake tables!
-- Query the data exactly as it looked 5 minutes ago
SELECT *
FROM TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS
AT(OFFSET => -60 * 5);
-- Alternatively, query before a specific timestamp
-- SELECT * FROM TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS BEFORE(TIMESTAMP => '2023-10-01 10:00:00'::timestamp_tz);
5. Recovering the Data
To fix the mistake, you can use the Time Travel query to insert the data back into the table.
-- Restore the data from the past
INSERT INTO TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS
SELECT *
FROM TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS
AT(OFFSET => -60 * 5);
-- Verify recovery
SELECT * FROM TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS;
Retention and Cleanup (Snapshot Expiration)
Because every change creates a new Snapshot (and retains the old, un-deleted data files), your cloud storage costs can grow infinitely if you never clean up old snapshots.
Iceberg has a process called Snapshot Expiration. When a snapshot expires, Iceberg deletes the old metadata files and physically deletes any old Parquet files that are no longer referenced by active snapshots.
In Snowflake, you manage this using standard data retention parameters.
-- Set Time Travel retention to 7 days for this Iceberg table
ALTER ICEBERG TABLE TIME_TRAVEL_LAB.FINANCIAL_TRANSACTIONS SET DATA_RETENTION_TIME_IN_DAYS = 7;
After 7 days, Snowflake will automatically run the snapshot expiration process, cleaning up the old Parquet files in your external volume to save you money.
(To verify your current user and role to ensure you have permissions to alter retention settings, you can run SELECT CURRENT_USER(); and SELECT CURRENT_ROLE();)
Summary
- Iceberg Time Travel is powered by Snapshots.
- You use the exact same SQL syntax (
ATorBEFORE) to Time Travel on Iceberg tables as you do on native tables. - You must manage Snapshot Expiration (via
DATA_RETENTION_TIME_IN_DAYS) to prevent infinite cloud storage growth.