Databricks SQL: Compact Delta Files with OPTIMIZE Tutorial

This tutorial focuses on using Compact Delta Files and the OPTIMIZE command within Databricks SQL. We’ll explore how to efficiently manage and optimize your data storage for query performance.

Script 1: Initial Data and Compact Delta Files

This script creates a small Delta table, ensures it’s in compact format, and then validates the data.

CREATE TABLE IF NOT EXISTS compact_demo (
  id INT,
  name STRING
);

INSERT INTO compact_demo (id, name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

OPTIMIZE compact_demo;

SELECT COUNT() FROM compact_demo;

Explanation:

• CREATE TABLE IF NOT EXISTS compact_demo: Creates a table named compact_demo if it doesn’t already exist.
• INSERT INTO compact_demo...: Inserts three rows of sample data.
• OPTIMIZE compact_demo: This is the core command. It compacts the Delta files for the compact_demo table. Databricks SQL automatically determines the optimal format for storage based on the data characteristics.
• SELECT COUNT() FROM compact_demo: This query counts the number of rows in the table, verifying the data integrity.

The OPTIMIZE command automatically uses compact files because of the data’s structure.

Output

1

Script 2: Introducing Data Bloat & OPTIMIZE

This script simulates data bloat to demonstrate the effect of the OPTIMIZE command. We’ll add a large number of rows and then optimize.

CREATE TABLE IF NOT EXISTS bloated_demo (
  id INT,
  name STRING
);

INSERT INTO bloated_demo (id, name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO bloated_demo (id, name) SELECT id, 'Dummy' FROM compacted_demo WHERE id IN (1, 2, 3);

INSERT INTO bloated_demo (id, name) VALUES
  (4, 'David'),
  (5, 'Eve'),
  (6, 'Frank'),
  (7, 'Grace'),
  (8, 'Henry'),
  (9, 'Ivy'),
  (10, 'Jack');

OPTIMIZE bloated_demo;

SELECT COUNT() FROM bloated_demo;

Explanation:

• We recreate the compact_demo table.
• We then duplicate the existing data into bloated_demo.
• Finally, we add more rows to bloated_demo. This will introduce a large number of empty files, simulating data bloat.
• OPTIMIZE bloated_demo: This command rebuilds the Delta table structure, consolidating the files into a more efficient compact format.
• SELECT COUNT() FROM bloated_demo: This query confirms that the table now contains the total number of rows after the data duplication.

Output

11

Script 3: Optimizing Large Data Volumes

This script demonstrates how `OPTIMIZE` works with larger datasets. It adds a significant amount of data to a table and then optimizes it.

CREATE TABLE IF NOT EXISTS large_demo (
  id INT,
  name STRING
);

INSERT INTO large_demo (id, name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie'),
  (4, 'David'),
  (5, 'Eve'),
  (6, 'Frank'),
  (7, 'Grace'),
  (8, 'Henry'),
  (9, 'Ivy'),
  (10, 'Jack'),
  (11, 'Kelly'),
  (12, 'Liam'),
  (13, 'Mia'),
  (14, 'Noah'),
  (15, 'Olivia');

INSERT INTO large_demo (id, name) SELECT id, 'Extra' FROM large_demo WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
INSERT INTO large_demo (id, name) SELECT id, 'Another' FROM large_demo WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

OPTIMIZE large_demo;

SELECT COUNT() FROM large_demo;

Explanation:

• We create a table named large_demo.
• We insert 15 rows into the table.
• We duplicate this data 2 times, adding a significant volume of data.
• OPTIMIZE large_demo: This command rebuilds the Delta table structure, consolidating the files and improving query performance.
• SELECT COUNT() FROM large_demo: This confirms the total row count after data duplication and optimization.

Output

15