Join Starburst on May 28th for Launch Point, our new product summit showcasing the future of Starburst.

Apache Iceberg Time Travel & Rollbacks in Trino

See what type of operation was performed and when it was executed & Roll back a table to a previous snapshot
  • Tom Nats

    Tom Nats

    Director of Customer Solutions

    Starburst

Share

Linkedin iconFacebook iconTwitter icon

This post is part of the Iceberg blog series. Read the entire series:

  1. Introduction to Apache Iceberg in Trino
  2. Iceberg Partitioning and Performance Optimizations in Trino
  3. Apache Iceberg DML (update/delete/merge) & Maintenance in Trino
  4. Apache Iceberg Schema Evolution in Trino
  5. Apache Iceberg Time Travel & Rollbacks in Trino
  6. Automated maintenance for Apache Iceberg tables in Starburst Galaxy
  7. Improving performance with Iceberg sorted tables
  8. Hive vs. Iceberg: Choosing the best table format for your analytics workload

Iceberg Time Travel

Time travel in Trino using Iceberg is a handy feature to “look back in time” at a table’s history. As we covered in this blog, each change to an Iceberg table creates a new “snapshot” which can be referred to by using standard sql.

As you can see from the diagram below, a new snapshot is created for the table creation, insert and update.

To see the snapshots on a table, you can use the handy metadata table that exists for each table:

SELECT * FROM "customer_iceberg$snapshots";

The above snapshot table shows the create, insert and update operations on the customer_iceberg table. You can see what type of operation was performed and when it was executed.

To select a certain snapshot, you use the “for version as of” syntax. In the following two examples, we show the customer name before and after an update:

SELECT custkey,name
FROM customer_iceberg FOR VERSION AS OF 5043425904354141100 where custkey = 2732;

SELECT custkey,name
FROM customer_iceberg FOR VERSION AS OF 3117754680069542695 where custkey = 2732;

You can also specify a timeframe to retrieve an older snapshot of a table. For example, the following query brings back the data for the first snapshot on or before a given timestamp:

SELECT custkey,name
FROM s3lakehouse.demo_tpch.customer_iceberg FOR TIMESTAMP AS OF TIMESTAMP '2022-09-18 07:18:09.002 America/New_York' where custkey = 2732;

Rolling Back

Another great feature of Iceberg is the ability to roll back a table to a previous snapshot. Sometimes this is used when a row was accidentally deleted or updated. As long as the snapshot exists, (it hasn’t been cleaned up yet) then you can roll back to any existing snapshot.

For example, in the scenario above, if I wanted to roll back to the state of the table before the update on the customer, then I would issue the following command:

CALL iceberg.system.rollback_to_snapshot('demo_tpch', 'customer_iceberg', 5043425904354141100)

Then we can query the tablea again to see the customer’s name was “rolled back” to the previous version before the update:

SELECT custkey,name
FROM s3lakehouse.demo_tpch.customer_iceberg where custkey = 2732;

Both time travel and rolling back are database functions that are now available in your data lakehouse. This is a game changer as it allows database type functionality to objects stores which were not available until now.

Schedule a call with an expert

Book time

Cookie Notice

This site uses cookies for performance, analytics, personalization and advertising purposes. For more information about how we use cookies please see our Cookie Policy.

Manage Consent Preferences

Essential/Strictly Necessary Cookies

Required

These cookies are essential in order to enable you to move around the website and use its features, such as accessing secure areas of the website.

Analytical/Performance Cookies

These are analytics cookies that allow us to collect information about how visitors use a website, for instance which pages visitors go to most often, and if they get error messages from web pages.

Functional/Preference Cookies

These cookies allow our website to properly function and in particular will allow you to use its more personal features.

Targeting/Advertising Cookies

These cookies are used by third parties to build a profile of your interests and show you relevant adverts on other sites.