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.

Maintenance commandsRow-level securityCompute choiceJoinsSkew and shuffleAQE, Photon, POCost and performance leversDeclarative and semanticAsset BundlesLakeflow ConnectUniFormVector Searchdbt and ODCSEgress and ingressMaintenance in picturesClustering and compactionMaintenance strategySlow writesBackup and recoveryTaggingQuestion bank

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

A. partitionBy(customer_id), 8 tasks33.7 s 8,000 filesB. repartition by key, then partitionBy7.7 s 1,000 filesC. partitionBy(country), 8 tasks1.4 s 80 filesD. no partition, 2 tasks0.5 s 2 files

Measured on a local machine; the ratio matters, not the seconds.

How it was writtenWrite timeFilesAvg file sizeTime to read it back
A. partitionBy(customer_id), 8 tasks33.7 s8,0001.4 KB9.0 s
B. repartition by key, then partitionBy7.7 s1,0004.0 KB1.0 s
C. partitionBy(country), 8 tasks1.4 s8041.5 KB0.2 s
D. no partition, 2 tasks0.5 s21532.5 KB0.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.

StepBasis for the number of partitions
Reading Parquet or Delta filesSpark 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, mapUnchanged. A filter that drops 99 percent of rows still leaves the same number of (now nearly empty) partitions
unionThe sum of the inputs
Created from a list or rangeThe default parallelism (about the number of cores)
Table partitions (folders) from partitionBy or a partitioned tableNot 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 written

Measured on the demo tables (the same ones as the example above):

What I didResult
Read the table of 8,000 tiny files250 partitions. Arithmetic: 8,000 files × 4 MB open cost ÷ 128 MB = 250
Filter that drops most rowsstill 250 partitions
Read the table of 2 files2 partitions
Read a compacted table of 12 files4 partitions
groupBy on the 8,000-file table200 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 value197 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

CauseSignFix
Partitioning on a high-cardinality columnFiles = tasks × partitions; writes stall on file creationRepartition by the partition key before writing, or use liquid clustering instead
Too many tiny commitsThousands of log entries, each with fixed overheadBigger batches; fewer, larger writes
Skew in the data writtenOne task writes far more than the othersFix the key, or repartition to spread rows
Wrong number of tasksToo few: one slow task; too many: tiny filesAim for files near the target size; use optimized writes
coalesce(1) or one giant fileAll data through one taskWrite in parallel and let OPTIMIZE tidy up
Overwriting a whole table to change a littleRewrites everythingMERGE with deletion vectors, or replaceWhere / dynamic partition overwrite
MERGE that rewrites most filesHuge rewrite for few changed rowsCluster on the merge key, put it in the ON clause, enable deletion vectors
Statistics on too many columnsSlow commits on very wide tablesLimit delta.dataSkippingNumIndexedCols
Python UDFs or heavy transforms before the writeThe write stage is not the slow stageReplace with built-ins; look at the whole job, not only the sink
Storage far from compute, or throttledSlow uploads, throttling errorsSame region; spread files over more paths; check storage limits
Small cluster, spillingSpill in the write stageLarger memory nodes, fewer columns, filter earlier

Best approaches

A checklist, in order

  1. Look at the write stage in the Spark UI: task time spread, output rows per task, number of output files.
  2. Count the files you create per write (tasks × partitions) and aim for fewer, larger ones.
  3. Pick the layout first: liquid clustering for new tables; partition only low-cardinality columns on big tables.
  4. Turn on optimized writes when you write partitioned data from many tasks.
  5. Use append for logs and events; use MERGE with deletion vectors for changes; avoid rewriting whole tables.
  6. Batch small commits.
  7. Compact afterwards with OPTIMIZE, scheduled or automatic.

Fastest way

The fastest way to write huge data

  1. Write from many tasks in parallel, with files near 128 MB to 1 GB. Partitions ≈ data size ÷ target file size. Never coalesce(1).
  2. Do not partition on a high-cardinality column. Write unpartitioned or liquid-clustered; if you must partition, repartition by that column first, or turn on optimized writes.
  3. Append; do not rewrite. A bulk load is an append or a CREATE TABLE AS. MERGE and overwrite rewrite files and are far slower.
  4. If the data is already Parquet, do not rewrite it. CONVERT TO DELTA only adds a log over the existing files; COPY INTO and clones also avoid heavy rewrites.
  5. 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).
  6. Remove work before the write: no Python UDFs, no skewed joins, no unneeded shuffle in front of the sink.
  7. 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).
  8. Use Photon and a fast codec (snappy or zstd); measure, since stronger compression costs CPU.
  9. 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

  1. What decides how many partitions a DataFrame has?
  2. What is the fastest way to write a huge dataset?
  3. A job writing 2 TB a day to a partitioned table is slow. Where do you look?
  4. Why does partitioning by user id make writes slow?
  5. How do optimized writes help, and what do they cost?
  6. How would you speed up a MERGE that rewrites 90 percent of the files?
In an interview, say: I count files created per write, check the layout, and fix skew and task counts before touching the cluster.

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