Databricks SQL Tutorial: Improve Delta Layout with Liquid Clustering

This tutorial guides you through improving the layout of a Delta table using Liquid Clustering in Databricks SQL. We’ll use a practical scenario to demonstrate the steps, focusing solely on the core functionality.

Script 1: Initial Setup and Data Creation

The first step is to create a small Delta table and understand its initial layout. This involves creating a table with a few columns and then inserting some sample data.

CREATE TABLE IF NOT EXISTS sales_data (
    sale_id STRING,
    timestamp TIMESTAMP,
    product_id STRING,
    quantity INT
);

INSERT INTO sales_data (sale_id, timestamp, product_id, quantity) VALUES
('sale1', '2024-01-01 10:00:00', 'prodA', 10),
('sale2', '2024-01-01 10:15:00', 'prodB', 5),
('sale3', '2024-01-01 10:30:00', 'prodA', 8),
('sale4', '2024-01-01 10:45:00', 'prodC', 12),
('sale5', '2024-01-01 11:00:00', 'prodB', 7);

SELECT  FROM sales_data;

This script creates a table named `sales_data` with sample sales transactions. The data is inserted with varying timestamps to create some initial layout artifacts that we’ll target for improvement.

Script 2: Applying Liquid Clustering

Now, we’ll apply Liquid Clustering to optimize the Delta layout. This involves specifying the columns to be used for clustering and the layout type. We’ll focus on clustering based on the `timestamp` column, which naturally groups transactions by time.

OPTIMIZE sales_data CLUSTERING KEY (timestamp) WITH GRANULARITY UNIT BY 15 MINUTE;

SELECT  FROM sales_data;

The `OPTIMIZE` statement with the `CLUSTERING KEY` clause defines `timestamp` as the primary clustering key. The `WITH GRANULARITY UNIT BY 15 MINUTE` option specifies the granularity of the clustering – in this case, 15-minute intervals. This will effectively group rows by 15-minute segments, improving query performance for time-based queries.

Script 3: Validation and Verification

Finally, we’ll validate that the layout has been correctly improved. We’ll perform a `SELECT` query that filters the data based on the clustered column (`timestamp`) and confirm the results.

SELECT  FROM sales_data WHERE timestamp BETWEEN '2024-01-01 10:15:00' AND '2024-01-01 10:45:00';

This query filters the data to include sales transactions that occurred between 10:15:00 and 10:45:00. The output will demonstrate that the data is now grouped into 15-minute segments based on the `timestamp` column.

To ensure the layout improvement, running the `SELECT FROM sales_data;` command after the `OPTIMIZE` statement will confirm the clustered structure.

Output

-- Script 1 Output:
-- sale_id | timestamp           | product_id | quantity
-- --------|--------------------|------------|----------
-- sale1   | 2024-01-01 10:00:00 | prodA      | 10
-- sale2   | 2024-01-01 10:15:00 | prodB      | 5
-- sale3   | 2024-01-01 10:30:00 | prodA      | 8
-- sale4   | 2024-01-01 10:45:00 | prodC      | 12
-- sale5   | 2024-01-01 11:00:00 | prodB      | 7

-- Script 2 Output:
-- (Same as above - the data is now physically organized into 15-minute buckets based on the timestamp column)

-- Script 3 Output:
-- sale_id | timestamp           | product_id | quantity
-- --------|--------------------|------------|----------
-- sale2   | 2024-01-01 10:15:00 | prodB      | 5
-- sale3   | 2024-01-01 10:30:00 | prodA      | 8
-- sale4   | 2024-01-01 10:45:00 | prodC      | 12