Study guide

8.1 Partitioning & Data Skew Remediation

Databricks Certified Data Engineer Professional Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Developing Code for Data Processing using Python and SQL - Core Spark SQL & PySpark

1.1 Complex PySpark Transformations & DataFrame API 1.2 Advanced Spark SQL Patterns 1.3 Streaming Workloads with Structured Streaming 1.4 Lakeflow & Delta Live Tables (DLT) Declarative Pipelines

2 Chapter 2: Developing Code for Data Processing using Python and SQL - Streaming & Advanced Patterns

2.1 Advanced Delta Lake Features 2.2 Custom & Stateful Streaming Operations 2.3 Python & PySpark Code Modularization 2.4 User-Defined Functions (UDFs) & Performance Impact

3 Chapter 3: Data Ingestion & Acquisition

3.1 Auto Loader (cloudFiles) Production Ingestion 3.2 Change Data Capture (CDC) Ingestion Patterns 3.3 Batch & Streaming Connectors

4 Chapter 4: Data Transformation, Cleansing, and Quality

4.1 Data Validation & Quality Frameworks 4.2 Data Cleansing, Deduplication & Standardization 4.3 Silver and Gold Medallion Layer Refinement 4.4 Handling Schema Drift and Evolution

5 Chapter 5: Data Sharing and Federation

5.1 Delta Sharing Architecture & Governance 5.2 Lakehouse Federation

6 Chapter 6: Monitoring and Alerting & Observability

6.1 System Tables & Observability 6.2 Databricks Workflows & Job Monitoring 6.3 Spark UI & Query Execution Diagnostics 6.4 Data Quality & Pipeline Alerting

7 Chapter 7: Cost & Performance Optimisation - Data Layout & Compute

7.1 Data Storage & Layout Optimization 7.2 Compute Choice & Cluster Sizing 7.3 Photon Engine & Query Acceleration

8 Chapter 8: Cost & Performance Optimisation - Skew & Cost Governance

8.1 Partitioning & Data Skew Remediation 8.2 Cost Governance & DBU Optimization

9 Chapter 9: Ensuring Data Security and Compliance

9.1 Unity Catalog Access Control & Least Privilege 9.2 Dynamic Data Masking & Row/Column-Level Security 9.3 Secrets Management & Network Security 9.4 Compliance, Audit & Data Privacy

10 Chapter 10: Data Governance in Unity Catalog

10.1 Unity Catalog Governance Architecture 10.2 Data Lineage & Tagging in Unity Catalog 10.3 Managed vs External Tables & Storage Governance

11 Chapter 11: Debugging and Deploying - Asset Bundles & CI/CD

11.1 Databricks Asset Bundles (DABs) & Infrastructure as Code 11.2 CI/CD Automation & Databricks CLI / REST API 11.3 Troubleshooting Pipeline & Job Failures 11.4 Code Versioning & Databricks Git Integration

12 Chapter 12: Data Modelling & Lakehouse Analytics

12.1 Medallion Architecture Design Patterns 12.2 Dimensional Modeling in the Lakehouse 12.3 Analytics & BI Optimization

8.1 Partitioning & Data Skew Remediation

Key Takeaways Explain This Topic AI

Traditional Hive-style partitioning is recommended only for columns with low cardinality (usually < 1000 distinct values) where data is evenly distributed.

Liquid Clustering replaces traditional partitioning and Z-ordering, dynamically adapting data layout to query patterns without requiring rewrite of all files.

Data skew frequently manifests in the Spark UI as one or a few tasks taking significantly longer to complete than the rest of the tasks in the same stage.

Adaptive Query Execution (AQE) skew join optimization (spark.sql.adaptive.skewJoin.enabled) automatically detects and splits skewed partitions during sort-merge joins.

When AQE is insufficient, manual salting can mitigate skew by adding a random salt key to uniformly distribute skewed values across multiple partitions.

Last updated: July 2026

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:

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.

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.

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.