Home › Learn › Databricks interview prep › Slow writes
Databricks interview prep · data engineering
Why writing lots of data is slow, and how to fix it
A measured example of a slow write (8,000 files against 2) and the causes and fixes: layout, task counts, commits, MERGE, statistics and storage.
Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.
Example
The same 300,000 rows, four ways to write them
Measured on a local machine; the ratio matters, not the seconds.
| How it was written | Write time | Files | Avg file size | Time to read it back |
|---|---|---|---|---|
| A. partitionBy(customer_id), 8 tasks | 33.7 s | 8,000 | 1.4 KB | 9.0 s |
| B. repartition by key, then partitionBy | 7.7 s | 1,000 | 4.0 KB | 1.0 s |
| C. partitionBy(country), 8 tasks | 1.4 s | 80 | 41.5 KB | 0.2 s |
| D. no partition, 2 tasks | 0.5 s | 2 | 1532.5 KB | 0.2 s |
Case A is the classic mistake. With 8 tasks and 1,000 customers, each task holds rows for most customers and writes a file for each one, so 8 × 1,000 ≈ 8,000 files of 1.4 KB. It wrote about 67 times slower than case D, and reading it back was slower too, because the cost is paid again on every read.
Partition count
What decides how many partitions a DataFrame has
A partition is one chunk of the data that one task works on. The count is not a setting you make once: each step either keeps it or recomputes it, and the write uses whatever the DataFrame has at that moment. One task writes at least one file per partition.
| Step | Basis for the number of partitions |
|---|---|
| Reading Parquet or Delta files | Spark packs file pieces into partitions of up to maxSplitBytes, which is about spark.sql.files.maxPartitionBytes (128 MB), lowered for small inputs so every core gets work. Each file also costs an assumed spark.sql.files.openCostInBytes (4 MB), so thousands of tiny files count as far more data than they hold. Parquet can be split inside a file; a compressed CSV or JSON file cannot (one file, one partition). |
| After a shuffle (join, groupBy, distinct, window) | Rows are assigned by hash of the key modulo spark.sql.shuffle.partitions (200 by default). With adaptive query execution on, Spark merges small partitions at run time. |
| repartition(n) | Exactly n; rows spread evenly (round-robin) |
| repartition(col) or repartition(n, col) | Hash of the column, into the shuffle partition count (or n). All rows with the same value land in one partition, so a popular value makes one huge partition |
| repartitionByRange(n, col) | Sorted ranges sampled from the data |
| coalesce(n) | At most n, by merging neighbours with no shuffle; cannot increase the count |
| filter, select, withColumn, map | Unchanged. A filter that drops 99 percent of rows still leaves the same number of (now nearly empty) partitions |
| union | The sum of the inputs |
| Created from a list or range | The default parallelism (about the number of cores) |
| Table partitions (folders) from partitionBy or a partitioned table | Not the same thing. They only let a read skip folders; they do not set the DataFrame partition count |
See it:
df.rdd.getNumPartitions() # partitions right now (planned; AQE may change it at run time)
len(df.inputFiles()) # source files behind a read
from pyspark.sql import functions as F # rows per partition: shows skew and empty partitions
df.groupBy(F.spark_partition_id()).count().orderBy("count").show()
df.explain("formatted") # Exchange nodes show shuffle partitioning
# Spark UI, Stages tab: tasks in the write stage = partitions being writtenMeasured on the demo tables (the same ones as the example above):
| What I did | Result |
|---|---|
| Read the table of 8,000 tiny files | 250 partitions. Arithmetic: 8,000 files × 4 MB open cost ÷ 128 MB = 250 |
| Filter that drops most rows | still 250 partitions |
| Read the table of 2 files | 2 partitions |
| Read a compacted table of 12 files | 4 partitions |
| groupBy on the 8,000-file table | 200 partitions (the shuffle setting) with AQE off; planned as 1 with AQE on, because it merges small ones |
| repartition(8) | 8 partitions with 37,500 rows each |
| repartition by a key where 10 percent of rows share one value | 197 partitions with rows; smallest 284 rows, largest 31,244 |
| Write with repartition(8) | 8 files |
| Write with repartition(8) and partitionBy(customer_id) | 8,000 files |
| Write with repartition(customer_id) and partitionBy(customer_id) | 1,000 files |
So when a write is slow, the partition count is inherited from the last step that set it: the file read, a shuffle, or your own repartition. Print it just before the write, and change it there.
Why writes get slow
The usual causes
| Cause | Sign | Fix |
|---|---|---|
| Partitioning on a high-cardinality column | Files = tasks × partitions; writes stall on file creation | Repartition by the partition key before writing, or use liquid clustering instead |
| Too many tiny commits | Thousands of log entries, each with fixed overhead | Bigger batches; fewer, larger writes |
| Skew in the data written | One task writes far more than the others | Fix the key, or repartition to spread rows |
| Wrong number of tasks | Too few: one slow task; too many: tiny files | Aim for files near the target size; use optimized writes |
| coalesce(1) or one giant file | All data through one task | Write in parallel and let OPTIMIZE tidy up |
| Overwriting a whole table to change a little | Rewrites everything | MERGE with deletion vectors, or replaceWhere / dynamic partition overwrite |
| MERGE that rewrites most files | Huge rewrite for few changed rows | Cluster on the merge key, put it in the ON clause, enable deletion vectors |
| Statistics on too many columns | Slow commits on very wide tables | Limit delta.dataSkippingNumIndexedCols |
| Python UDFs or heavy transforms before the write | The write stage is not the slow stage | Replace with built-ins; look at the whole job, not only the sink |
| Storage far from compute, or throttled | Slow uploads, throttling errors | Same region; spread files over more paths; check storage limits |
| Small cluster, spilling | Spill in the write stage | Larger memory nodes, fewer columns, filter earlier |
Best approaches
A checklist, in order
- Look at the write stage in the Spark UI: task time spread, output rows per task, number of output files.
- Count the files you create per write (tasks × partitions) and aim for fewer, larger ones.
- Pick the layout first: liquid clustering for new tables; partition only low-cardinality columns on big tables.
- Turn on optimized writes when you write partitioned data from many tasks.
- Use append for logs and events; use MERGE with deletion vectors for changes; avoid rewriting whole tables.
- Batch small commits.
- Compact afterwards with OPTIMIZE, scheduled or automatic.
Fastest way
The fastest way to write huge data
- Write from many tasks in parallel, with files near 128 MB to 1 GB. Partitions ≈ data size ÷ target file size. Never
coalesce(1). - Do not partition on a high-cardinality column. Write unpartitioned or liquid-clustered; if you must partition,
repartitionby that column first, or turn on optimized writes. - Append; do not rewrite. A bulk load is an append or a CREATE TABLE AS. MERGE and overwrite rewrite files and are far slower.
- If the data is already Parquet, do not rewrite it.
CONVERT TO DELTAonly adds a log over the existing files;COPY INTOand clones also avoid heavy rewrites. - Defer the tidying. Load first, then run OPTIMIZE or clustering afterwards. Turn off auto compaction during the load, and keep statistics to the columns you filter on (
delta.dataSkippingNumIndexedCols). - Remove work before the write: no Python UDFs, no skewed joins, no unneeded shuffle in front of the sink.
- Give it enough compute and storage throughput: scale out the workers, keep compute in the same region as the storage, and spread very large loads over several tables or partitions written in parallel (watch for commit conflicts).
- Use Photon and a fast codec (snappy or zstd); measure, since stronger compression costs CPU.
- Make the job restartable: spot workers with retries, and idempotent writes so a retry does not duplicate.
The demo shows the effect: the same rows wrote in 0.5 s as two right-sized files, against 33.7 s as 8,000 tiny files. The fastest write is the one that makes the fewest, largest files in parallel, and does the least extra work.
Practice
Questions to rehearse
- What decides how many partitions a DataFrame has?
- What is the fastest way to write a huge dataset?
- A job writing 2 TB a day to a partitioned table is slow. Where do you look?
- Why does partitioning by user id make writes slow?
- How do optimized writes help, and what do they cost?
- How would you speed up a MERGE that rewrites 90 percent of the files?
FAQ
Questions people ask
Is more cluster the answer to slow writes?
Rarely. The usual cause is too many files or a rewrite of too much data.
Do optimized writes always help?
They cut file counts but add a shuffle, so they cost time on the write itself.
Next
Where to go next
All Databricks interview topics · All data engineering · Databricks on this site