delta lake time-travel (just reference the version)

This quick blog post is a follow-up to my prior delta lake in starburst galaxy (intro & integration) one. I wanted to capture the additional feature of time-travel which is possible due to how Delta Lake creates new versions each time content is changed. Please check the prior post to see what steps were taken to get us to version 2 of the dune_characters table we will work with below.

Starting point

Let’s make sure you are seeing the same results before we move forward.

SELECT * FROM dune_characters;

SELECT version, timestamp, operation
  FROM "dune_characters$history";

The table_changes function is pretty cool as it can identify the changes that have occurred between two versions. The example below compares the initial version with the most recent version.

SELECT
  *
FROM
  TABLE(
    system.table_changes(
      schema_name => 'dlblog1',
      table_name => 'dune_characters',
      since_version => 0
    )
  );

Make changes

Run some SQL to change the table’s contents.

INSERT INTO dune_characters 
  (id, name, notes)
VALUES
  (104, 'Wellington', 'Physician of Duke');

-- get rid of those with odd numbered id's
DELETE FROM dune_characters
 WHERE mod(id,2) = 1;

Verify the results.

SELECT * FROM dune_characters;

Notice 2 more versions are present (we ran 2 SQL statements).

SELECT version, timestamp, operation
  FROM "dune_characters$history";

Time-travel

Query the version of the table that we started this blog post with.

SELECT * FROM dune_characters 
FOR VERSION AS OF 2;

Yep, that’s right!

Additional features

Unfortunately, as of Trino 471, the Delta Lake connector does not yet allow for time-travel queries to be based on a timestamp as the Spark API has.

Additionally, the Trino connector for Delta Lake does not yet offer a rollback function as the Spark API has.