Delta queries slow before the first task starts: transaction log bloat, checkpoints and file counts on Azure Databricks

#Symptom

A point lookup or small aggregate on a big Delta table takes 40 seconds, but the Spark UI shows only a few seconds of task time. The rest is spent before any task starts. Other signs:

• In the SQL warehouse query profile, most of the time is in the optimizing and file-pruning phase, not in execution.
• The first query on a freshly started cluster is much slower than the second. The table snapshot gets cached after the first load.
• The driver uses a lot of memory or garbage-collects heavily while queries are planning.
• Streaming writes to the table take longer to commit with each batch.

Delta builds a snapshot before it plans a query. It reads the latest checkpoint, replays every JSON commit after that checkpoint, and keeps the add and remove actions for every file. The cost grows with three things:

1. The number of active files (one add action each, with per-file column stats).
2. The number of tombstones: remove actions kept until delta.deletedFileRetentionDuration expires (7 days by default).
3. The number of commits since the last checkpoint.

#Measure first

-- Active file count and size
DESCRIBE DETAIL main.sales.events;
-- check numFiles, sizeInBytes, properties, and clusteringColumns or partitionColumns

SHOW TBLPROPERTIES main.sales.events;

Commit rate per day, and which operations dominate:

from delta.tables import DeltaTable
from pyspark.sql import functions as F

h = DeltaTable.forName(spark, "main.sales.events").history()
(h.where(F.col("timestamp") > F.current_timestamp() - F.expr("INTERVAL 7 DAYS"))
  .groupBy(F.to_date("timestamp").alias("day"), "operation")
  .agg(F.count("*").alias("commits"),
       F.sum(F.col("operationMetrics")["numAddedFiles"].cast("bigint")).alias("added"),
       F.sum(F.col("operationMetrics")["numRemovedFiles"].cast("bigint")).alias("removed"))
  .orderBy(F.desc("day"), F.desc("commits"))
  .show(50, truncate=False))

Metric names in operationMetrics differ by operation. MERGE and OPTIMIZE report extra keys, so check one row's output before you aggregate.

On external tables you can look at the log directly, if you have access to the path:

log = "abfss://lake@stacct.dfs.core.windows.net/sales/events/_delta_log/"
files = dbutils.fs.ls(log)
print("json commits:", sum(f.name.endswith(".json") for f in files))
print("checkpoint parts:", [f.name for f in files if "checkpoint" in f.name][-5:])
print(dbutils.fs.head(log + "_last_checkpoint"))

Path access is blocked for Unity Catalog managed tables. Use DESCRIBE DETAIL and DESCRIBE HISTORY for those.

For a fleet view, find the slowest-to-plan statements in query history:

SELECT statement_text, compilation_duration_ms, execution_duration_ms,
       read_files, pruned_files
FROM system.query.history
WHERE start_time > now() - INTERVAL 1 DAY
  AND compilation_duration_ms > 10000
ORDER BY compilation_duration_ms DESC
LIMIT 20;

#Cause → fix map

#Fixes in detail

#1. Cut the active file count

ALTER TABLE main.sales.events SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'   = 'true'
);
OPTIMIZE main.sales.events;               -- with liquid clustering this clusters incrementally

For Unity Catalog managed tables, predictive optimization can run OPTIMIZE for you. Check the table detail or the system.storage.predictive_optimization_operations_history table to see whether it is running.

Warning

The first OPTIMIZE on a table with millions of small files makes the log temporarily bigger. Every compacted file leaves a tombstone until delta.deletedFileRetentionDuration passes. Run it during a quiet window and expect a slower week before it gets better.

#2. Shrink per-file statistics

Every add action stores min, max and null counts for the indexed columns. On a 300-column table that is most of the checkpoint size. Keep stats only for columns you filter or cluster on:

ALTER TABLE main.sales.events SET TBLPROPERTIES (
  'delta.dataSkippingStatsColumns' = 'event_date,customer_id,event_type'
);

New files get the narrower stats. Existing files keep their stats until they are rewritten. Make sure clustering keys stay in the list, or data skipping on them stops working. delta.dataSkippingNumIndexedCols is the older, position-based alternative.

#3. Use V2 checkpoints on large, busy tables

V2 checkpoints store file actions in sidecar files referenced from a manifest. That scales better for big tables and many concurrent writers. Readers and writers need Databricks Runtime 13.3 LTS or above.

ALTER TABLE main.sales.events SET TBLPROPERTIES ('delta.checkpointPolicy' = 'v2');

Tables created with liquid clustering on Databricks Runtime 14.1 and above use V2 by default.

Caution

Enabling V2 checkpoints adds the v2Checkpoint table feature. Older runtimes and external readers (other engines, older Delta libraries) that don't support it can no longer read the table. You can remove it with ALTER TABLE ... DROP FEATURE v2Checkpoint, but test with every consumer first.

#4. Fewer commits

• Streaming writers: move from the default trigger to trigger(processingTime="1 minute"), or use availableNow on a schedule, where latency allows.
• Many small producers writing to one table: land them in a staging table and append in bulk, or merge them into one stream.
• delta.checkpointInterval sets how many commits pass between checkpoints. Defaults vary by Delta version, so check SHOW TBLPROPERTIES before you change it. Lowering it reduces JSON replay but writes checkpoints more often, which costs more on very large tables.

#5. Check that checkpoints are actually written

If _last_checkpoint points at a version far behind the latest commit, every reader replays a long tail of JSON. Look in the driver log of the writing job for checkpoint errors. Out-of-memory errors while writing a huge checkpoint are the common case, and they go away once file counts and stats are reduced. Checkpoints are written automatically on the next eligible commit, so a successful write after cleanup brings readers back to a short replay.

#What delta.logRetentionDuration does and does not do

Lowering log retention (30 days by default on Databricks) deletes old JSON and checkpoint files during checkpoint cleanup. It saves storage and makes listing faster. It doesn't make snapshot loading faster, because readers already start from the latest checkpoint. It also shortens time travel, so leave it alone unless storage or listing is the actual problem.

#Verify

• DESCRIBE DETAIL shows numFiles dropping after compaction.
• compilation_duration_ms for the same statement falls in system.query.history over the following week, once tombstones expire.
• Streaming batch duration stops growing with each commit.

#Prevent it next time

• Turn on optimized writes and auto compaction (or predictive optimization) when you create the table, not after it reaches a million files.
• Set delta.dataSkippingStatsColumns explicitly on wide tables.
• Review the streaming trigger interval for every writer. Sub-second triggers into a reporting table are rarely needed.
• Track numFiles per critical table on a schedule and alert when it grows faster than data volume.