Traditional Writes &
the Small-File Problem.

What it is

When Spark writes a DataFrame to a Delta table, it does not write one tidy file. The data is split into partitions; each parallel task writes its own file. So if 200 tasks are writing, you get roughly 200 files — even for a tiny result. Repeat that on every append, and you accumulate thousands of tiny files: the small-file problem.

Analogy 200 cashiers each bag one item into its own bag. You leave with 200 bags instead of a few well-packed ones — carrying them later (reading them) is slow and clumsy.

Use case A streaming job appends a micro-batch every 10 seconds. After a day that's ~8,600 commits, each writing several files. A dashboard query over that table now opens tens of thousands of files to answer one question.

Why it matters — the cost of a tiny file

A query's cost is not just how many bytes — it's also how many files. Every file carries fixed overhead the engine pays no matter how small it is:

• File open / object-store request — each file is a separate cloud-storage GET; listing and opening 50,000 files is dominated by request latency, not data size.
• Footer read — Parquet keeps its schema + column metadata in a footer the engine must read per file before reading data.
• Min/max statistics parse — Delta records per-file min/max/null/count stats; more files = more stats to load and evaluate.
• Task scheduling — Spark schedules ~one read task per file; 50,000 files = 50,000 tasks to plan, ship, and collect, flooding the driver.
• Metadata bloat — every file is an entry in the transaction log (_delta_log); millions of tiny files make the log huge and slow to replay.

See it work — write → files

Pick how many writing tasks (shuffle partitions) the write fans out to, then choose a manual control. Watch the file grid, the file count, the approximate average file size, and a qualitative read-cost indicator.

Simplified illustration. Real file counts depend on AQE coalescing, data size, skew, and the target file size; sizes shown are approximate for a ~1 GB write.

How it works — deep dive

1 · One file per writing task

A write is a distributed job. Each partition is processed by one task, and each task writes its own file — so output files ≈ partitions at write time. More parallelism = a faster write but more, smaller files; less parallelism = fewer files but a slower write. You want both right-sized files and parallelism — exactly what optimized writes gives you for free later.

PySpark# The number of DataFrame partitions decides the number of output files
df = spark.range(0, 1_000_000)
print(df.rdd.getNumPartitions())   # e.g. 8 -> a plain write makes ~8 files

# Plain write: ~one Parquet file per partition. Delta is the default format.
df.write.mode("overwrite").saveAsTable("main.delta_opt_demo.events")

2 · spark.sql.shuffle.partitions & micro-batch appends multiply files

Any shuffle (join, groupBy, window) resets the partition count to spark.sql.shuffle.partitions (historically 200). A shuffle right before a write → ~200 files per write. Streaming/frequent batch jobs append on every micro-batch, so 200 files × thousands of commits = a tiny-file avalanche — and nothing in a plain write merges across commits.

PySpark# A shuffle repartitions the data to spark.sql.shuffle.partitions
spark.conf.get("spark.sql.shuffle.partitions")   # historically "200"

agg = df.groupBy((df.id % 5).alias("bucket")).count()  # forces a shuffle
# Without AQE/optimized writes this write would emit ~200 files for a tiny result.
agg.write.mode("overwrite").saveAsTable("main.delta_opt_demo.events_agg")

3 · The old manual controls: coalesce(n) vs repartition(n)

coalesce(n) — no shuffle. Merges neighboring partitions down to n. Cheap, but makes uneven files and can cut the upstream stage's parallelism. repartition(n) — full shuffle. Evenly redistributes into n partitions (clean sizes) but pays a full network shuffle. Both are brittle because you must guess n, and it goes stale as data grows.

PySpark# Naive (brittle) — guess a file count and force it before writing:
df.coalesce(1).write.saveAsTable("main.delta_opt_demo.events")      # one big file, kills parallelism
df.repartition(8).write.saveAsTable("main.delta_opt_demo.events")   # full shuffle, must guess 8

# Right (Databricks): DON'T coalesce/repartition before a write when
# optimized writes is on — let the platform size files (Lesson 05).
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", True)
df.write.mode("overwrite").saveAsTable("main.delta_opt_demo.events")

4 · AQE coalescing — a partial mitigation

Adaptive Query Execution (on by default) inspects the actual shuffle output sizes at runtime and coalesces many small post-shuffle partitions into fewer, larger ones — so a job that would have made 200 tiny partitions may collapse to ~12, with no tuning. But AQE tunes a single query; it never merges files across separate commits and isn't a layout solution. Append-heavy tables still accumulate small files.

PySpark# AQE is on by default; these are the relevant knobs (read, don't blindly change):
spark.conf.get("spark.sql.adaptive.enabled")                      # "true"
spark.conf.get("spark.sql.adaptive.coalescePartitions.enabled")   # "true"

5 · What a Delta write actually does (Parquet + _delta_log)

A Delta table = Parquet data files + a transaction log (_delta_log). A write (a) writes new Parquet files, then (b) commits one atomic JSON log entry listing the added files with their size + min/max/null/count stats. Readers trust the log, not the directory listing — that's what makes writes ACID. But a plain write does no file sizing; sizing only happens via optimized writes, auto compaction, or OPTIMIZE.

SQL-- The transaction log is the source of truth. Inspect what a write produced:
DESCRIBE DETAIL main.delta_opt_demo.events;   -- numFiles, sizeInBytes, format = delta
DESCRIBE HISTORY main.delta_opt_demo.events;  -- one row per commit: WRITE / MERGE / OPTIMIZE

6 · spark.sql.files.maxRecordsPerFile — capping rows per file

Caps how many rows go into one output file; when a task exceeds the cap it rolls to a new file. 0 or negative = no limit (the default). It's a guardrail against one task writing a single enormous file — but it bounds rows, not a true byte target, and set too low it creates small files.

PySpark# Cap rows per file (a guardrail against one giant file). 0/neg = no limit.
spark.conf.set("spark.sql.files.maxRecordsPerFile", 5_000_000)

# Or as a per-write option (does not change the session-wide config):
df.write.option("maxRecordsPerFile", 5_000_000) \
  .mode("overwrite").saveAsTable("main.delta_opt_demo.events")

The lifecycle of a Delta write

Click each step to expand. This is exactly what happens between df.write and a committed, queryable table.

Simplified illustration of the Delta write path; real commits also handle conflict detection, checkpoints, and protocol metadata.

What fixes this later

This lesson defines the problem; the rest of the track is the cure:

• Lesson 04 — OPTIMIZE (bin-packing): merge many small files into fewer right-sized ones after the fact.
• Lesson 05 — Optimized writes: size files as you write (a shuffle before the write). On by default for MERGE/UPDATE/DELETE.
• Lesson 06 — Auto compaction: a small compaction that runs right after a successful write to merge the just-written small files.
• Lesson 08 — Liquid clustering: the modern layout that keeps related data together and files right-sized — replacing partitioning + Z-order.
• Lesson 09 — Predictive optimization: Databricks runs OPTIMIZE/VACUUM/ANALYZE for you on UC managed tables.

Comparison — the file-count controls

Uses, edge cases & limitations

Uses (when the manual controls still make sense)

• One-off non-Databricks / OSS Spark jobs where optimized writes isn't available — repartition(n) is the pragmatic file-count control.
• A deliberate single-file export (small CSV/Parquet handoff) via coalesce(1) — fine for small data, never for large tables.

Edge cases an interviewer probes

• Streaming sink, tiny trigger: each micro-batch commits files; thousands of micro-batches → file explosion. Fix with auto compaction / OPTIMIZE.
• repartition(1) on a growing table: fine on day 1, then a multi-GB single file (or OOM) — the classic stale-hardcode trap.
• coalesce reducing upstream parallelism: coalesce(1) before a wide transform can serialize the whole stage, not just the write.
• AQE false safety: it fixes one query's partitions but never compacts across commits — small files still pile up on append.
• maxRecordsPerFile too low: turns a guardrail into a small-file generator.

Limitations

• A plain Delta write does no automatic file sizing — files are appended exactly as tasks produced them.
• coalesce/repartition need a hand-picked n that doesn't adapt to growth — guesswork by design.
• AQE coalescing works within a single query, not across the table's history.
• Hive-style directory layout is not the source of truth for Delta — the _delta_log is.

Common gotchas

• Don't coalesce/repartition right before a write when optimized writes is enabled — it fights the engine's own file sizing.
• A shuffle resets your partition count to spark.sql.shuffle.partitions. If a small write makes ~200 files, a join/groupBy is why.
• Tiny-file pain is about count, not bytes. 100,000 × 50 KB files can be slower than a few hundred-MB files holding the same data.
• Appends never self-merge. Frequent small appends accumulate until you OPTIMIZE / auto-compact.
• maxRecordsPerFile is a ceiling, not a target. It can't raise file size; it only caps rows.
• Read the log, not the folder. Use DESCRIBE DETAIL / DESCRIBE HISTORY for numFiles and what each commit did.