Repartition vs Coalesce vs partitionBy on Azure Databricks: Controlling Output Files When Writing Delta Tables

Almost every PySpark job ends with a write, and that write is where a lot of quiet damage happens: ten thousand tiny files, one giant file written by a single task, or a job that suddenly takes an hour because someone added coalesce(1). Interviewers love this area because it tests whether you understand what a partition actually is. This post walks through repartition(), coalesce(), partitionBy(), and the settings that control how many files land in your Delta table on Azure Databricks.

Three different things called "partition"

Most confusion comes from one word meaning three things:

1. In-memory partitions: the chunks of a DataFrame that Spark processes, one task per partition.
2. Shuffle partitions: how many partitions Spark creates after a wide operation like groupBy or join, controlled by spark.sql.shuffle.partitions.
3. Table (directory) partitions: the folders created by df.write.partitionBy("col"), like event_date=2026-10-26/.

repartition() and coalesce() change the first. partitionBy() controls the third. The number of files you get is a product of both.

You can always check the first one:

from pyspark.sql import functions as F

df = spark.read.format("delta").load("/mnt/bronze/events")
print(df.rdd.getNumPartitions())

# Rows per in-memory partition, useful for spotting skew
(df.groupBy(F.spark_partition_id().alias("pid"))
   .count()
   .orderBy(F.desc("count"))
   .show(10))

When reading files, the number of input partitions is driven mostly by spark.sql.files.maxPartitionBytes (128 MB by default), not by how many files exist.

repartition(): a full shuffle, on purpose

repartition() always shuffles. It has three useful forms:

# 1. Round-robin into exactly 64 partitions (even sizes, no ordering)
df64 = df.repartition(64)

# 2. Hash partition by a column (rows with the same key end up together)
by_cust = df.repartition("customer_id")          # uses spark.sql.shuffle.partitions
by_cust_32 = df.repartition(32, "customer_id")   # explicit count

# 3. Range partition (sorted ranges, good before writing sorted output)
by_ts = df.repartitionByRange(32, "event_ts")

Use it when you need to increase parallelism (for example, after reading one large gzip file, which Spark can't split), when you need data co-located by key before a write, or when partitions are badly uneven and you want to rebalance.

The cost is a full shuffle: every row is serialized, sent over the network, and written to shuffle files. On a big table that's not free, so don't sprinkle repartition() around "just in case".

One subtle point: hash partitioning by a skewed key doesn't fix skew. If one customer_id has 40% of the rows, repartition("customer_id") puts all of them in one partition.

coalesce(): cheap, but it can shrink your whole stage

coalesce(n) only reduces the partition count, and it does it without a shuffle by merging existing partitions. That makes it a narrow transformation, which sounds like pure win. The catch is that Spark pipelines narrow transformations into the same stage, so the reduced parallelism can flow backwards into the work before it.

# Looks harmless, but the expensive transform now runs in only 1 task
(df.filter(F.col("country") == "CA")
   .withColumn("score", expensive_udf("payload"))
   .coalesce(1)
   .write.mode("overwrite").parquet("/mnt/tmp/out"))

Because there's no shuffle boundary between the read, the UDF, and the coalesce(1), the whole stage runs as one task. If you really need a single output file, repartition(1) is often faster here: the upstream work stays parallel and only the final write collapses to one task.

Rule of thumb:

• Reducing a lot of partitions after heavy work, where the data has already shrunk (for example after a selective filter following a shuffle): coalesce() is fine.
• Reducing to a very small number right after expensive narrow work: prefer repartition().
• Needing more partitions: only repartition() can do that. coalesce(1000) on a 10-partition DataFrame still gives you 10.

Shuffle partitions and AQE

After a join or aggregation, Spark creates spark.sql.shuffle.partitions partitions, 200 by default in open-source Spark. For a 2 GB shuffle that's about 10 MB each; for a 2 TB shuffle it's 10 GB each, and tasks start spilling.

Adaptive Query Execution (on by default in Databricks) helps a lot by coalescing small shuffle partitions at runtime:

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

# Target size AQE aims for when merging shuffle partitions
spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "128m")

AQE can merge partitions, but it can't split one shuffle partition into more than it started with (apart from skew-join handling). So a common approach is to set the initial count high enough for your largest shuffles and let AQE merge them down. On Databricks you can also set spark.sql.shuffle.partitions to auto and let the platform pick the initial number.

partitionBy(): where file explosions come from

Here's the classic small-files trap:

(df.write
   .format("delta")
   .partitionBy("event_date")
   .mode("append")
   .saveAsTable("silver.events"))

Each task writes a separate file for every event_date value it contains. If the DataFrame has 200 partitions and the data spans 90 dates spread across all of them, you can get up to 200 × 90 = 18,000 files from one write.

The fix is to cluster rows by the partition column before writing, so each date is handled by one (or a few) tasks:

(df.repartition("event_date")
   .write
   .format("delta")
   .partitionBy("event_date")
   .mode("append")
   .saveAsTable("silver.events"))

Now each date goes to a single task and usually a single file. That creates the opposite risk: a big date becomes one huge file written by one slow task. Two ways to balance that:

# Cap rows per file so big partitions are split into several files
(df.repartition("event_date")
   .write
   .format("delta")
   .option("maxRecordsPerFile", 5_000_000)
   .partitionBy("event_date")
   .mode("append")
   .saveAsTable("silver.events"))

# Or spread each date over a few tasks with a salt
(df.repartition("event_date", (F.rand() * 4).cast("int"))
   .write.format("delta")
   .partitionBy("event_date")
   .mode("append")
   .saveAsTable("silver.events"))

maxRecordsPerFile limits file size but doesn't add parallelism; the salt does both, at the cost of up to four files per date.

And the bigger question: should the table be partitioned at all? Databricks' guidance is that tables under roughly 1 TB usually don't need directory partitioning, and that each partition should hold at least about 1 GB. Partitioning by a high-cardinality column like customer_id or by timestamp instead of date is how you end up with millions of tiny files. For new tables, liquid clustering is usually the better default.

Let Delta size the files for you

On Databricks, Delta has two features aimed at exactly this problem:

• Optimized writes add a shuffle before the write so each task writes larger, well-sized files per table partition.
• Auto compaction runs a small, synchronous compaction after a write when a partition has accumulated many small files.

ALTER TABLE silver.events SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'   = 'true'
);

Or for a session:

spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")
spark.conf.set("spark.databricks.delta.autoCompact.enabled", "true")

Recent Databricks Runtime versions turn these on by default for many operations (for example MERGE, UPDATE, and DELETE, and for Unity Catalog managed tables), so check your runtime's docs before adding manual repartition() calls that duplicate the work. Scheduled OPTIMIZE still handles whatever slips through.

How to check what you produced

After a write, look at the table, not your assumptions:

detail = spark.sql("DESCRIBE DETAIL silver.events").select("numFiles", "sizeInBytes").first()
print(detail.numFiles, round(detail.sizeInBytes / detail.numFiles / 1024**2, 1), "MB avg")

# Operation metrics from the last write
(spark.sql("DESCRIBE HISTORY silver.events LIMIT 1")
      .select("operation", "operationMetrics.numFiles", "operationMetrics.numOutputRows")
      .show(truncate=False))

An average file size in the low single-digit megabytes is a signal to revisit partitioning or turn on optimized writes. Something between roughly 100 MB and 1 GB is healthy for most analytic tables.

Interview questions worth practising

1. What's the difference between repartition() and coalesce(), and why can coalesce(1) make a job slower than repartition(1)?
2. A daily job writing with partitionBy("event_date") produces 15,000 files. Explain why and give two fixes.
3. Does repartition("customer_id") fix data skew? Why or why not?
4. What does AQE do to shuffle partitions, and what can't it do?
5. When would you not partition a Delta table at all?

If you can reason through those with DESCRIBE DETAIL output in front of you, you'll handle most write-performance questions.

---

If you want more practice like this, there's a free 10-question sample of Azure Databricks and PySpark interview questions with worked answers: free sample. The full set is in the Azure Databricks & PySpark Interview Pack.