A data analyst notices that a Databricks SQL query against a large Delta table ran much faster last week than it does today, even though the table schema has not changed. Audit logs show that a new batch of data was added daily and no OPTIMIZE was run. What is the most likely explanation for the performance degradation?

Question

Accepted Answer

B. The Delta table has accumulated many small files from daily inserts, increasing the number of files that must be scanned. When data is continuously inserted into a Delta table without running OPTIMIZE, each insert (especially if it writes small batches) creates many small files. Over time, the number of files grows, and the query must open and read metadata for a larger number of files even if data skipping skips some. This file fragmentation degrades scan performance. Warehouse size changes would appear in warehouse settings. Column statistics are automatically maintained by Delta Lake. Cache expiration would affect only repeated identical queries.

Related Questions

Discussion