Optimizing Delta Table Reads with OPTIMIZE and Z-ORDER

Part 1: The "Small File Problem" and Compaction

A common issue in large-scale data systems, especially with streaming or frequent updates, is the creation of many small files. Spark's performance suffers when reading thousands of small files compared to a few larger ones due to the overhead of opening each file and processing its metadata.

This isn't just a theoretical problem; it has significant real-world consequences. The following blog post from Salesforce Engineering describes how the small file problem severely impacted their production jobs.

As the article describes, the primary solution to the small file problem is compaction. Delta Lake provides the OPTIMIZE command to accomplish this. It reads the small files in a partition and rewrites them into a smaller number of larger files (aiming for 1GB by default).

The basic syntax is simple:

OPTIMIZE table_name

While Delta Lake also offers automatic optimization features like Optimize Write and Auto Compaction, the manual OPTIMIZE command gives you the most control and is the foundation for the powerful Z-ORDER technique we'll discuss next.

Part 2: Supercharging Reads with Data Skipping and Z-Ordering

Compacting files is a great first step, but we can do even better. Delta Lake automatically collects statistics (like minimum and maximum values) for the first 32 columns of your table and stores this information in the transaction log. When you run a query with a WHERE clause, the query planner uses these statistics to figure out which files it can safely ignore, or "skip."

For data skipping to be effective, the data must be organized so that the min/max value ranges for a given column have minimal overlap between files. This is where Z-Ordering comes in.

Z-Ordering is a technique that colocates related data in the same set of files. It's like sorting your data, but across multiple dimensions (columns). When you run OPTIMIZE with a ZORDER BY clause, Delta Lake not only compacts the files but also reorganizes the data within them according to the columns you specify.

To understand this concept more deeply, let's watch a short video.

A Real-World Design Choice: Partitioning vs. Z-Ordering

Now, let's go back to the Salesforce Engineering blog. They realized that even with compaction, their partitioning strategy (partition by OrgId and EngagementDate) was still creating many small partitions for smaller organizations. Their solution was a brilliant example of data engineering design.

This example highlights a critical trade-off:

• Partitioning is best for low-cardinality columns (e.g., country, state). It works by physically separating data into different directories.
• Z-Ordering is best for high-cardinality columns that are frequently used in query filters (e.g., timestamp, user_id, event_id). It works by organizing data within files in a single partition directory.

Part 3: Hands-On Practice

Theory is great, but let's see the impact firsthand. We'll walk through a practical example in a notebook. The following video provides a great step-by-step demonstration that you can follow.

Now, let's perform these steps yourself in a Databricks notebook.

Step 1: Create Un-optimized Data
First, we'll create a Delta table with many small files. We'll use the same retail dataset from the video, which is available in Databricks datasets.

# Set a config to allow for smaller file sizes in this demo
# This helps exaggerate the small file problem for learning purposes
spark.conf.set("spark.databricks.delta.optimize.maxFileSize", 10 * 1024 * 1024) # 10MB




# Read the source data and force it into 20 small partitions/files
df = (spark.read
      .format("csv")
      .option("header", "true")
      .option("inferSchema", "true")
      .load("/databricks-datasets/online_retail/data-001/data.csv")
      .repartition(20))




# Write as a Delta table
df.write.format("delta").mode("overwrite").saveAsTable("retail_sales_unoptimized")

Step 2: Query Before Optimization
Now, run a query against a specific StockCode and examine the Spark UI to see how many files were read.

SELECT * FROM retail_sales_unoptimized WHERE StockCode = '85123A'

After the query runs, click on "View" in the cell output, then "Spark UI". Look at the job details and find the "number of files read". You should see that it read all 20 files.

Step 3: Apply OPTIMIZE and Z-ORDER
Let's optimize the table. Since StockCode is a high-cardinality column we filter on, it's a great candidate for Z-Ordering.

OPTIMIZE retail_sales_unoptimized
ZORDER BY (StockCode)

Step 4: Query After Optimization
Run the exact same query again and check the Spark UI.

SELECT * FROM retail_sales_unoptimized WHERE StockCode = '85123A'

This time, you should see that the number of files read has dropped dramatically (likely to just 1 or 2 files). This is the direct performance benefit of combining compaction and Z-Ordering.

Part 4: Advanced Considerations and Best Practices

To use these features professionally, you need to understand the nuances.

1. Combining Partitioning and Z-Ordering

The most effective strategy is often to combine partitioning on a low-cardinality column with Z-Ordering on a high-cardinality column. For example: PARTITION BY (Country) and ZORDER BY (CustomerID).

When you do this, you can also optimize individual partitions, which is much more efficient than re-optimizing the entire table.

OPTIMIZE retail_sales_partitioned
WHERE Country = 'France'
ZORDER BY (CustomerID)

The following video segment demonstrates exactly this powerful combination.

2. Choosing Z-Order Columns

• Choose wisely: Select columns that are frequently used in WHERE clauses and have high cardinality.
• Diminishing returns: You can Z-Order by multiple columns (ZORDER BY (colA, colB)), but the effectiveness decreases with each additional column. One or two columns are typical.
• Stats are required: Z-Ordering is only effective on columns for which Delta Lake has collected statistics. By default, this is the first 32 columns in the table, but this can be configured.

3. OPTIMIZE is a Maintenance Operation

OPTIMIZE with ZORDER can be a computationally expensive operation. It is not part of the auto-optimize features. You must run it as a separate, deliberate maintenance task. For most production tables, this is scheduled to run as a nightly or weekly job using Databricks Workflows.

Conclusion

In this lesson, you've gained a crucial skill for any Databricks Data Engineer: performance tuning for read queries. You've seen how to tackle the small file problem and enable highly efficient data skipping.

Key Takeaways:

• The OPTIMIZE command compacts many small files into fewer large ones, reducing read overhead.
• ZORDER BY colocates data within files based on high-cardinality columns, which dramatically improves the effectiveness of data skipping.
• The best performance often comes from combining partitioning on low-cardinality columns with Z-Ordering on high-cardinality columns.
• OPTIMIZE is a resource-intensive maintenance operation that should be scheduled to run periodically, not with every write.

Preview of the Next Lesson:
We have now covered managing table history (VACUUM) and optimizing read performance (OPTIMIZE). Next, we'll continue our exploration of advanced features by tackling another common challenge: managing changes to your table's schema over time. You'll learn how to implement schema evolution using the mergeSchema and overwriteSchema options.