8.1 Partitioning & Data Skew Remediation

Data skew is one of the most common and impactful performance bottlenecks in Apache Spark and Databricks. It occurs when data is unevenly distributed across partitions, causing some executors to process significantly more data than others. This leads to inefficient resource utilization, where most of the cluster sits idle waiting for a single executor to finish its disproportionately large workload. In this section, we will thoroughly cover how to design tables to avoid skew, how to identify it when it occurs, and the strategies to remediate it effectively, including Adaptive Query Execution (AQE), hints, and manual salting. These foundational principles are essential for achieving robust and performant data engineering pipelines.

Table Partitioning vs. Liquid Clustering

Historically, Hive-style partitioning was the primary method for organizing data physically on disk to improve query performance. By dividing data into directories based on a partition column, queries could employ "partition pruning" to skip reading irrelevant data. However, traditional partitioning requires careful planning and can lead to severe performance degradation if implemented incorrectly. Data engineers often struggled with picking the right granularity—too coarse, and partition pruning is ineffective; too fine, and the system suffers from the small file problem.

Traditional Partitioning Best Practices

When utilizing traditional partitioning on Delta tables, you must adhere to several strict guidelines:

• Low Cardinality: Partition columns should have a relatively low number of distinct values (typically under 1,000). High cardinality leads to the creation of thousands of directories and tiny files, crippling the file system metadata operations (the "small file problem").
• Even Data Distribution: The data should be evenly distributed across the partition values. If you partition by customer_id and one customer represents 90% of your business, 90% of the data lands in one partition, exacerbating skew.
• Sufficient File Size: Each physical partition should ideally contain at least 1 GB of data. If partitions are too small, the overhead of opening and closing files negates the benefits of partition pruning.
• Z-Ordering Necessity: Within each partition, you often need to run OPTIMIZE ... ZORDER BY to co-locate related data, which requires expensive compute operations.

-- Creating a traditionally partitioned table
CREATE TABLE sales_traditional (
  transaction_id STRING,
  amount DOUBLE,
  event_date DATE,
  region STRING
)
USING DELTA
PARTITIONED BY (event_date);

Liquid Clustering

To address the shortcomings of traditional partitioning, Databricks introduced Liquid Clustering. Liquid clustering dynamically adapts the data layout to query patterns without requiring massive rewrites of data files. It replaces both traditional partitioning and Z-ordering, offering a much more flexible approach to physical data layout. By keeping cluster data closely located on disk, you can dramatically improve the read performance of downstream analytics queries.

• It allows you to cluster on columns with high cardinality (e.g., UUIDs or fine-grained timestamps).
• It supports clustering on columns whose data distribution changes dramatically over time.
• It is highly recommended over traditional partitioning for all new Delta tables in Databricks, particularly those backing BI dashboards or machine learning workloads where query patterns are unpredictable.

-- Creating a table with Liquid Clustering
CREATE TABLE sales_liquid (
  transaction_id STRING,
  customer_id STRING,
  amount DOUBLE,
  event_date DATE
)
USING DELTA
CLUSTER BY (customer_id, event_date);

Liquid clustering runs in the background and incrementally improves data layout during standard write operations and OPTIMIZE commands, ensuring that skew does not build up over time due to new data ingestion.

Identifying Data Skew

Before you can apply a fix for data skew, you must be able to identify it. Skew typically occurs during wide transformations, such as JOIN, GROUP BY, or Window operations, which require a full shuffle of data across the cluster network.

Symptoms in the Spark UI

The most definitive way to identify data skew is through the Spark UI. Look for these classic symptoms:

1. Uneven Task Duration: Navigate to the Stage details page. If you observe that 199 tasks finished in 2 seconds, but 1 task is taking 15 minutes, you have confirmed data skew. The straggler task is processing the skewed partition.
2. Uneven Data Volumes: In the "Summary Metrics" table for a stage, look at the "Shuffle Read Size / Records" row. If the "Max" value is drastically larger than the "75th percentile" or "Median" value, it indicates that one task processed a disproportionate amount of data compared to its peers.
3. Spilling: Skewed tasks often run out of allocated RAM and are forced to spill data to disk. High "Spill (Memory)" and "Spill (Disk)" metrics on a select few tasks strongly suggest those tasks are overwhelmed by skewed data.

Skew Remediation Strategies

Once skew is identified, there are several techniques to remediate it. You should always attempt the simplest, most automated solutions before resorting to manual code rewrites.

1. Adaptive Query Execution (AQE) Skew Join Optimization

Starting with Spark 3.0, Adaptive Query Execution (AQE) can automatically handle data skew in sort-merge joins at runtime. This is the first line of defense and requires no code changes. AQE uses runtime statistics to dynamically adjust the execution plan.

AQE skew optimization works by identifying skewed partitions using runtime statistics. If a partition's size exceeds the calculated threshold, AQE splits the skewed partition into smaller sub-partitions. The corresponding partition on the other side of the join is then duplicated to match these sub-partitions, allowing the join to be processed in parallel across multiple executors.

Configuration:

# These properties are enabled by default in modern Databricks runtimes
spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")

# You can aggressively tune the thresholds for what Spark considers "skewed"
# Lowering this threshold makes AQE split partitions more readily
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionFactor", "3")
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes", "128MB")

2. Broadcast Joins

If one side of the skewed join is small enough to fit entirely within the memory of a single executor (typically under 10MB to 20MB, though configurable), a Broadcast Hash Join completely eliminates the shuffle phase. Without a shuffle, data skew becomes irrelevant because the small table is simply broadcast to all executors holding chunks of the large table.

Implementation: You can explicitly encourage the catalyst optimizer to perform a broadcast join using hints.

-- SQL Hint forcing a broadcast of the dimension table
SELECT /*+ BROADCAST(small_table) */ *
FROM large_skewed_table l
JOIN small_table s ON l.key = s.key;

3. Skew Join Hints

If AQE isn't splitting the skewed data aggressively enough, or you want to explicitly declare the skew ahead of time to the optimizer, you can use a SKEW hint. This instructs Databricks exactly which table and which column are skewed, allowing the engine to plan the split before runtime execution begins.

-- Hinting that 'large_table' is generally skewed on the 'customer_id' column
SELECT /*+ SKEW('large_table') */ *
FROM large_table l
JOIN normal_table n ON l.customer_id = n.customer_id;

-- Hinting a specific skewed value (e.g., a massive volume of nulls or 'UNKNOWN')
SELECT /*+ SKEW('large_table', 'customer_id', 'UNKNOWN') */ *
FROM large_table l
JOIN normal_table n ON l.customer_id = n.customer_id;

4. Manual Salting

When built-in optimizations fail, or when skew occurs during a GROUP BY operation (which AQE skew optimization does not cover), you must resort to manual salting. Salting is a highly effective, albeit complex, mechanism for breaking up skewed partitions manually.

Salting involves adding a random number (the salt) to the skewed key. This distributes the massive block of skewed records across multiple distinct partitions based on the random salt. To make the join work, you must replicate the other side of the join (the dimension table) to match every possible salt value.

Scenario: Joining a massive fact table (heavily skewed on store_id = 999) with a smaller dimension table.

import pyspark.sql.functions as F

# 1. Add a random salt (e.g., numbers 0 to 9) to the skewed large table
# This breaks the skewed '999' into 10 smaller keys: '999_0', '999_1', etc.
salted_large_df = large_df.withColumn(
    "salted_key", 
    F.concat(F.col("store_id"), F.lit("_"), F.floor(F.rand() * 10))
)

# 2. Replicate the small table 10 times to match the salt values
# Create a DataFrame with values 0 to 9
salt_df = spark.range(10).withColumnRenamed("id", "salt_val")

# Cross join to replicate the dimension table
replicated_small_df = small_df.crossJoin(salt_df).withColumn(
    "salted_key",
    F.concat(F.col("store_id"), F.lit("_"), F.col("salt_val"))
)

# 3. Perform the join on the new salted key
# The workload is now evenly distributed across 10 executors
result_df = salted_large_df.join(replicated_small_df, "salted_key")

Manual salting is complex, bloats the code, and adds processing overhead via data replication. It should be used strictly as a last resort when AQE, Broadcast Joins, and Liquid Clustering cannot resolve the bottleneck. However, it remains a vital skill for dealing with massive, intractable skew in production pipelines.