Databricks SQL Tutorial: Query Historical Delta Table Versions

This tutorial demonstrates how to query historical versions of a Delta table using Databricks SQL. We will execute three scripts, each building upon the previous one, to progressively explore the capabilities of querying historical data.

Script 1: Initial Version Inspection

This script creates a simple Delta table and then examines its initial version.

CREATE TABLE IF NOT EXISTS my_delta_table (
  id INT,
  value STRING
)
USING DELTA
;

INSERT INTO my_delta_table (id, value) VALUES
(1, 'A'),
(2, 'B'),
(3, 'C');

SELECT version, start_time, end_time
FROM system.history
WHERE table_name = 'my_delta_table'
ORDER BY start_time;

This script first creates a Delta table named `my_delta_table` with two columns: `id` and `value`. Then, it inserts three rows of sample data. Finally, it uses the `system.history` view to retrieve information about the table’s versions. The `system.history` view provides metadata about each version, including its start time, end time, and the version number. The output of this query helps understand how Delta Lake tracks changes.

Note that the `system.history` view only stores information about versions that were created or dropped, not every increment. It shows the full range of versions available.

The `ORDER BY start_time` clause ensures that the versions are listed in chronological order.

SELECT version
FROM system.history
WHERE table_name = 'my_delta_table'
ORDER BY start_time DESC
LIMIT 1;

Output:

1

Script 2: Querying a Specific Historical Version

This script demonstrates how to query data from a specific historical version of the Delta table.

-- Insert some new data to create a new version
INSERT INTO my_delta_table (id, value) VALUES
(4, 'D');

SELECT version, start_time, end_time
FROM system.history
WHERE table_name = 'my_delta_table'
ORDER BY start_time DESC
LIMIT 1;

-- Query the data from version 1
SELECT 
FROM my_delta_table
WHERE version = 1;

First, we insert a new row into `my_delta_table`. This creates a new version of the table. Then, we use `system.history` to find the latest version (version 1 in this case). Finally, we query the data from version 1 using the `WHERE version = 1` clause. This allows us to see the data as it existed at that specific point in time.

SELECT 
FROM my_delta_table
WHERE version = 1;

Output:

id  value
1   A
2   B
3   C

Script 3: Querying a Version Before an Update

This script demonstrates querying a version before an update operation.

-- Insert some initial data
CREATE TABLE IF NOT EXISTS my_delta_table (
  id INT,
  value STRING
)
USING DELTA
;

INSERT INTO my_delta_table (id, value) VALUES
(1, 'A'),
(2, 'B');

-- Insert a new row
INSERT INTO my_delta_table (id, value) VALUES
(3, 'C');

-- Update a row
UPDATE my_delta_table SET value = 'D' WHERE id = 2;

-- Query version 1
SELECT version, start_time, end_time
FROM system.history
WHERE table_name = 'my_delta_table'
ORDER BY start_time DESC
LIMIT 1;

-- Query the data from version 1
SELECT 
FROM my_delta_table
WHERE version = 1;

-- Query the data from version 2
SELECT 
FROM my_delta_table
WHERE version = 2;

This script simulates a series of changes to the `my_delta_table`. First, it inserts initial data. Then, it inserts another row, and subsequently updates a row, effectively creating two distinct versions. Finally, it queries the data from both version 1 and version 2 to demonstrate how historical versions are preserved, even after updates. Note that the `system.history` view always reflects the current state, regardless of the changes made to the underlying table. The update operation does not affect the historical versions stored in `system.history`.

SELECT 
FROM my_delta_table
WHERE version = 1;

Output:

id  value
1   A
2   B
3   C

SELECT 
FROM my_delta_table
WHERE version = 2;

Output:

id  value
1   A
2   D
3   C