I was leading a webinar this week focused on Apache Iceberg when I was asked about the purpose of the is_current_ancestor column of the $history metadata table. I made an educated guess & I also CLEARLY said “I’m surely not sure”. Thankfully, Monica Miller, from Starburst‘s DevRel team offered up the following answer in the chat.

That got my mind turning and I promised myself right then that I’d figure this one out and be able to answer it correctly next time. Like always, a quick search ultimately gave me what I was looking for. 

The Iceberg experts over at Tabular explain it briefly in this blog post. It states, “The is_current_ancestor flag lets you know if a snapshot is part of the linear history of the current table”. If that’s enough of an answer for you, you can walk away right now.

Ok… you’re still here. Let’s try it out for ourselves.

Set up your environment

Being a huge fan of Trino AND being rather lazy, I decided to use Startburst Galaxy. Why not, it is set up for you and you can register for free! Of course, you can do these steps with any SQL engine that supports Apache Iceberg. (Another) of course, my instructions are focused on using Galaxy.

Once you get logged into your very own Galaxy tenant, you need to make sure you have a data lake catalog you can write to — see docs and/or tutorials. On the tutorials page, toggle the Category and Catalog selectors as shown below to get help with the “big 3”.

My querying … data in the cloud post shows you how I setup Galaxy and S3 previously as yet another helper to get you started with this BYOB (Bring Your Own Bucket) model.

Operation 1; create a table

Use appropriate values for mycatalog.myschema then create an Iceberg table and query it afterwards. 

CREATE mycatalog.myschema;  -- or use an existing schema
USE mycatalog.myschema;  

CREATE TABLE test_rb (
    f1 int, f2 varchar
) WITH (type='iceberg');

SELECT * FROM test_rb;

As expected, there are no rows yet.

As you can see below, I’m joining 3 different metadata tables to ultimately get the results I want. To make it easy, I’m also hiding this query behind a view for ease of use later.

CREATE VIEW curr_ver_dets AS
SELECT concat_ws(' > ', r.name, r.type) 
           AS curr_ver,
       date_format(s.committed_at, '%Y/%m/%d-%T')
           AS committed_at,
       s.snapshot_id, s.parent_id, 
       h.is_current_ancestor, s.operation
  FROM "test_rb$snapshots" AS s
  JOIN "test_rb$history" AS h
    ON (s.snapshot_id = h.snapshot_id)
  LEFT JOIN "test_rb$refs" AS r
    ON (h.snapshot_id = r.snapshot_id)
 ORDER BY s.committed_at;

Now, and in future steps, you can just read from the new curr_ver_dets view.

SELECT * FROM curr_ver_dets;

Each change to the structure or content of an Iceberg table creates a new version (aka snapshot). Notice there is only one snapshot at this point and that it is the initial version (identified by curr_ver reporting main > BRANCH) as it has no parent snapshot. And yep, is_current_ancestor reports true. That means it is part of the linear history of the active snapshot. 

Pretty obvious with just one snapshot and the image below is a bit of overkill at this point. Note that I’m only putting the last 4 numbers of the snapshot_id in the diagram and will continue that strategy with future snapshots.

Operations 2-3; two insert statements

Run two separate INSERT statements that will create a new snapshot for each operation.

INSERT INTO test_rb (f1, f2) 
VALUES 
  (1, '1st insert (2nd txn)'), 
  (2, '1st insert (2nd txn)');

INSERT INTO test_rb (f1, f2) 
VALUES 
  (3, '2nd insert (3rd txn)'), 
  (4, '2nd insert (3rd txn)'), 
  (5, '2nd insert (3rd txn)');

SELECT * FROM test_rb ORDER BY f1;

We have 5 records now. What does the list of snapshots look like?

SELECT * FROM curr_ver_dets;

Two more snapshots were created and the final one is the current version. You can trace its parent_id to the previous row and then again from that middle row to the initial snapshot. 

The diagram below shows all 3 snapshots are part of the “current ancestor tree” (i.e. their is_current_ancestor flags are set to true).

Operation 4; delete statement

Delete any record whose f1 value is an even number and verify only the odd numbered records are left.

DELETE FROM test_rb
 WHERE mod(f1, 2) = 0;

We are down to just 3 records now and the contents of the curr_ver_dets view shows we continue to build a very linear set of snapshots whose parent is the snapshot that was created before it. Notice the is_current_ancestor values are all reporting true. 

Operation 5; rollback to operation 2

Rollback to the snapshot_id that was created on the first INSERT statement in Operation 2. In my example the snapshop_id needed is 5611312366620665402. Change it to be the snapshot_id that was created in your environment. Be sure to use appropriate values for mycatalog and myschema, too.

CALL mycatalog.system.rollback_to_snapshot(
   'myschema', 'test_rb',
    5611312366620665402);

Query test_rb to verify we only have the first 2 records that were inserted in the transaction right after the table creation one.

Query curr_ver_dets to see that we now have have the current version of the table pointing to the snapshot_id we rolled backed to. 

The diagram below is showing you what the results above are declaring. No new snapshot was created, but the current version is 2 versions back and the last 2 snapshots are no longer part of the linear history of the active version (indicated by the green shapes). These snapshots still exist and can be rolled backed to at anytime as well.

Operations 6-7; two more inserts

Run 2 more INSERT statements that collectively add 3 more rows and create 2 more snapshots.

INSERT INTO test_rb (f1, f2) 
VALUES 
  (61, '3rd insert (oper #6)'), 
  (62, '3rd insert (oper #6)');

INSERT INTO test_rb (f1, f2) 
VALUES 
  (70, '4th insert (7th operation)');

The most recent 2 snapshots are part of the current ancestor tree and 2 snapshots before them still have their is_current_ancestor set to false.

Operation 8; rollback to operation 4

Rollback to the snapshot_id that was created by Operation 4’s DELETE statement. In my example the snapshop_id needed is 6149468753676344494. Change it to be the snapshot_id that was created in your environment. Be sure to use appropriate values for mycatalog and myschema, too.

CALL webinar2.system.rollback_to_snapshot(
   'myschema', 'test_rb',
    6149468753676344494);

You should only see those early odd numbered records.

As you can see above & below, no new snapshots were created, but the current version has been reset and appropriate changes have been made to is_current_ancestor values.

Operations 9-10; insert 2 records and change 1 of them

Let’s see if it is all making sense…

INSERT INTO test_rb (f1, f2) 
VALUES 
  (91, 'OPER #09'), 
  (92, 'OPER #09');

UPDATE test_rb SET f2 = 'OPER #10'
 WHERE f1 = 92;

Make sense? If not, drop me a comment.

Operations 11-14; drill it home!

This all makes sense, too? Feel free to do one operation at a time and review it every step of the way.

CALL webinar2.system.rollback_to_snapshot(
   'myschema', 'test_rb',
    8421281246956311672);

UPDATE test_rb SET f2 = 'update from Op 12';

INSERT INTO test_rb VALUES (113, 'Op 13');

INSERT INTO test_rb (f1, f2) 
VALUES 
  (114, 'OPER # 14'), 
  (214, 'OPER # 14');

If this makes your head hurt AND you can’t figure it out, I’ll see you in the comments.

HAPPY SNAPSHOTTING!