Partitions & Adaptive Query Execution

Databricks · Apache Spark on Databricks

Theory

A partition is a chunk of a DataFrame's rows processed by one task. Spark reads files into partitions based on file sizes, and after a shuffle it uses spark.sql.shuffle.partitions (200 by default in open-source Spark). Too few partitions leave cores idle and cause large, spilling tasks; too many create thousands of tiny tasks. repartition(n, col) reshuffles into n partitions, and coalesce(n) merges partitions without a full shuffle.

Adaptive Query Execution (AQE), on by default in Databricks, re-optimizes a query while it runs using real statistics from finished stages. It coalesces small shuffle partitions, switches a sort-merge join to a broadcast join when one side turns out small, and splits skewed partitions so one hot key (for example region West with most of the orders) does not make one task run for an hour. Because of AQE, you rarely need to tune shuffle partitions by hand on Databricks.

Why we use it

Parallelism and data skew decide how fast a Spark job runs. AQE fixes many partition and skew problems automatically, but you still need the concepts to read query profiles and to choose repartition or coalesce correctly.

When to use it

Leave AQE on and start with defaults. Use coalesce to reduce the number of output files, repartition by a key before a heavy operation on that key, and investigate skew when one task runs much longer than the others.

How it works

  1. Stage finishes and writes shuffle data
  2. AQE reads real sizes of the shuffle partitions
  3. Are many partitions tiny? Yes: Coalesce them into fewer tasks. No: Keep the partition count.
  4. Is one partition much bigger (skew)? Yes: Split it into smaller tasks. No: Run the next stage normally.
  5. Next stage runs with the re-optimized plan

Syntax

df.rdd.getNumPartitions()      (classic compute)
df.repartition(n) | df.repartition(n, "col") | df.coalesce(n)
spark.sql.shuffle.partitions
spark.sql.adaptive.enabled = true (default)
spark.sql.adaptive.skewJoin.enabled

Example

orders = spark.read.table("main.sales.orders")

print(spark.conf.get("spark.sql.adaptive.enabled"))      # true

by_region = orders.groupBy("region").sum("amount")       # shuffle; AQE coalesces partitions

# Spread work by key before a heavy per-region step
balanced = orders.repartition(8, "region")

# Write fewer, larger files
(by_region.coalesce(1)
          .write.mode("overwrite")
          .saveAsTable("main.sales.region_totals"))

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic