Mastering Data Organization: A Guide to Partitioning Databricks Delta Tables

This article was inspired by a real-world underperforming Spark Streaming pipeline handling IoT data. One of my initial investigative steps was to identify the root cause of performance bottlenecks during upserts or inserts into a delta table. It became evident when I attempted to read that Delta table and create a copy of it in a test folder within my Azure Data Lake Storage container, and it took six hours to copy 67 million records from that table even though it had only 10 fields. Notably, there were no lengthy text fields in this table.

At that point, I realized that something was seriously amiss. Upon inspecting the Delta table in ADLS, I noticed that it was partitioned much like a library organizes books. It had an excessive number of partitions without careful consideration of how the downstream data consumption would occur. Even the smallest partition had file sizes ranging from 10 to 5 kilobytes. Contrastingly, Databricks recommends a minimum of 1 gigabyte per partition. Moreover, partitioning should generally be reserved for tables with sizes of 1 terabyte or more.

Once I managed to copy the data to this new, non-partitioned Delta table, the resulting files were in the hundreds of megabytes in size. Consequently, the task of copying the data from this new non-partitioned Delta table to another Delta table took only one minute. This dramatic reduction in time, from 6 hours to 1 minute, was solely attributed to improving a poorly devised partitioning strategy.

Furthermore, thanks to the latest enhancements in Databricks, a feature known as “liquid clustering” is available to assist you in steering clear of both over-partitioning and under-partitioning your Delta table. I recommend reviewing the official documentation for more in-depth information. This approach will help you avoid performance issue, just ensure to select the most suitable column(s) as your key.

I encourage you to read the article below for further guidance and ensure that you only partition your data when you have a comprehensive understanding of the basics. I also attached reference links from Microsoft that talks about partitioning of delta tables and big data small file problem.

In the world of big data and data engineering, efficient data organization is the key to optimizing performance and ensuring that your data operations run smoothly. One essential technique that plays a crucial role in this process is partitioning Databricks Delta tables. In this blog post, we’ll explore what partitioning is, why it matters, and how to do it effectively.

Understanding Partitioning

At its core, partitioning is the practice of dividing your data into smaller, more manageable subsets based on one or more columns in your dataset. These subsets are organized into separate directories or data files, making it easier to access and query the data efficiently.

Why Does Partitioning Matter?

Partitioning offers several benefits for data management and processing:

1. Improved Query Performance: By dividing data into smaller partitions, you reduce the amount of data that needs to be scanned during a query. This can significantly speed up query performance, especially when filtering data based on partitioned columns.
2. Efficient Data Pruning: Partitioning enables what’s known as “data pruning.” When you query data and specify a condition based on partitioned columns, Databricks Delta can skip reading entire partitions that don’t match the filter condition, saving both time and resources.
3. Simplified Data Management: Partitioning makes it easier to manage your data. You can quickly add, update, or delete partitions without affecting the entire dataset.

Choosing the Right Columns to Partition

Effective partitioning depends on selecting the most appropriate columns to partition your data. The key factors to consider are:

1. Data Distribution: Choose columns with a relatively even distribution of values. Over-partitioning can lead to performance issues.
2. Query Patterns: Consider the columns often used in your queries. Partitioning by frequently filtered columns is generally a good practice.
3. Data Size: Databricks Delta recommends that each partition contains at least a gigabyte of data to maintain efficiency.

When Is the Best Time to Partition a Table?

Choosing the right time to partition a table is crucial. The ideal scenarios for table partitioning are:

1. Large Datasets: Partitioning becomes particularly beneficial when dealing with large datasets. It helps to manage and query data efficiently in such cases.
2. Frequently Filtered Columns: When specific columns are frequently used as filters in your queries, consider partitioning by those columns. This optimizes query performance.
3. Changing Data: If your data changes frequently, partitioning can simplify the process of adding, updating, or deleting partitions without affecting the entire dataset.

Drawbacks of Partitioning

While partitioning offers substantial benefits, it’s important to be aware of potential drawbacks:

1. Over-Partitioning: Partitioning by too many columns or over-partitioning can lead to reduced performance. It may cause excessive metadata overhead and slower query execution.
2. Data Management Complexity: Maintaining a highly partitioned table can become complex. You’ll need to ensure consistency in your partitioning strategy across the entire data pipeline.
3. Storage Overhead: Partitioning can increase storage overhead due to the creation of multiple directories and files. This creates the big data small file problem in Spark.

Picking the Right Partition Strategy

Selecting the appropriate partition strategy is pivotal. Here’s how to go about it:

1. Understand Query Patterns: Analyze your query patterns and identify the columns commonly used for filtering or joining. These columns are prime candidates for partitioning.
2. Even Distribution: Choose columns with a relatively even distribution of values. Over-partitioning can lead to performance issues.
3. Data Size: Databricks Delta recommends that each partition contains at least a gigabyte of data to maintain efficiency. Ensure your partitions meet this size requirement.
4. Consistency: Maintain consistency in your partitioning strategy across your entire data pipeline. Consistency ensures that your queries and operations work as expected.

How to Partition Databricks Delta Tables

Let’s walk through the steps to partition a Databricks Delta table:

1. Create a Delta Table: Start by creating a Databricks Delta table, either by converting an existing DataFrame or reading data from an external source.
2. Use Liquid Clustering: Liquid clustering provides flexibility to redefine clustering keys without rewriting existing data, allowing data layout to evolve alongside analytic needs over time.

-- Create an empty table
CREATE TABLE table1(col0 int, col1 string) USING DELTA CLUSTER BY (col0);

-- Using a CTAS statement
CREATE EXTERNAL TABLE table2 CLUSTER BY (col0)  -- specify clustering after table name, not in subquery
LOCATION ‘table_location’
AS SELECT * FROM table1;

-- Using a LIKE statement to copy configurations
CREATE TABLE table3 LIKE table1;

1. Partition By Clause: When writing data into your Delta table, use the PARTITIONED BY clause in your write operation to specify the column(s) by which you want to partition the data.

df.write.format("delta").partitionBy("column_name").save("delta_table")

3. Maintain Consistency: Remember to maintain consistency in your partitioning strategy across your entire data pipeline. Consistency ensures that your queries and operations work as expected.

Conclusion

Partitioning Databricks Delta tables is a powerful technique to optimize data organization and improve query performance. By selecting the right columns to partition and following best practices, you can streamline your data operations and make the most of your big data infrastructure. Whether you’re working with real-time analytics or large-scale batch processing, effective partitioning can be a game-changer in managing and querying your data efficiently.

References

https://siraj-deen.medium.com/small-file-large-impact-addressing-the-small-file-issue-in-spark-2e431ed5c91b#:~:text=Here%20are%20some%20effective%20strategies,scheduling%2C%20resulting%20in%20improved%20performance.

https://docs.databricks.com/en/delta/clustering.html#optimize

About Me

I am passionate about empowering, educating, and encouraging individuals pursuing a career in data engineering. Currently a Senior Data Engineer at Capgemini, specializing in Azure Databricks

Linkedin: https://www.linkedin.com/in/nobieyisi/