A national archive's Lakehouse stores humidity, light, and vibration telemetry streamed from conservation sensors in ten vault rooms, with each sensor writing a new file every few seconds. Curators notice that queries against the sensor table have grown steadily slower even though the table's total row count is only growing at a steady, predictable rate. What is the most likely cause?
Select an answer to reveal the explanation.
Short Explanation
Think of every sensor write as dropping one index card into a filing cabinet instead of a whole folder — eventually a query has to flip through thousands of cards just to read one page of information. That's the small-file problem: lots of tiny writes pile up as separate Parquet files, and the engine pays file-open overhead for each one, not just for the bytes it needs. Compacting those files back into fewer, larger ones is what actually restores speed.
Full Explanation
Every write from a high-frequency sensor stream lands as its own small Parquet file under the Delta table, and the transaction log tracks each one individually. As file count climbs into the thousands, a scan spends more time opening file footers and merging metadata than it does reading rows — a classic small-file problem that worsens over time even when total row growth stays steady, because file count grows faster than byte count. The fix is table maintenance: running the table's OPTIMIZE compaction rewrites many small files into fewer right-sized ones without changing a single value in the data. A bigger Spark pool addresses processing capacity, not the structural cost of opening excessive files, so more nodes would speed up the compaction job itself but wouldn't stop the fragmentation from recurring. Row-level security controls which rows a user can see; it adds a predictable filter cost, not one that grows with file count. And a Lakehouse table isn't held resident in a pool's memory between queries — every query reads from OneLake storage, so file layout on disk, not pool memory, is what's driving the slowdown. One caveat: because sensor writes are continuous, a single OPTIMIZE run only helps until the next burst of writes re-fragments the table, so compaction needs to run on a recurring schedule. A concrete check: run DESCRIBE DETAIL against the table and compare its numFiles against what the data volume would justify.