Running time travel queries

You query historical snapshots of data using the FOR SYSTEM_TIME AS OF '<timestamp>' FOR SYSTEM_VERSION AS OF <snapshot_id> clauses in a select statement. You see how to use AS OF to specify a snapshot of your Iceberg data at a certain time.

You can inspect the history of an Iceberg table to see the snapshots. ou can query the metadata of the Iceberg table using a SELECT ... AS OF statement to run time travel queries. You use history information from a query of the database to identify and validate snapshots, and then query a specific snapshot AS OF a certain Timestamp value.

  • You must be aware of the table history.

    However, this can include commits that have been rolled back.

  • You must have access to valid snapshots.
  • You must meet the prerequisites to query Iceberg tables from a Virtual Warehouse mentioned earlier.
  1. View the table history.
    SELECT * FROM db.table.history;
  2. Check the valid snapshots of the table.
    SELECT * FROM db.table.snapshots;
  3. Query a specific snapshot by providing the timestamp and snapshot_id.
    SELECT * FROM T 
    FOR SYSTEM_TIME AS OF <TIMESTAMP>;
    
    SELECT * FROM t 
    FOR SYSTEM_VERSION AS OF <SNAPSHOT_ID>;