A lakehouse table slows down after months of small incremental writes. What is the likely cause and fix?
Why C is the answer
Too many small files, fixed by table maintenance such as OPTIMIZE
Frequent small writes fragment Delta tables. Compaction restores read performance.
In detail
- Many small writes create many small files.
- OPTIMIZE compacts them.
- Schedule maintenance, rather than waiting for complaints.
Use case
An illustrative scenario, not a real incident.
A table loaded every five minutes holds thousands of tiny files, so reads open them one by one. OPTIMIZE compacts them into fewer, larger files.
See it in different ways
flowchart LR A["Many small files"] --> B["OPTIMIZE compacts them"] B --> C["VACUUM removes unreferenced files"] C --> D["Faster reads"]
stateDiagram-v2 [*] --> SmallFiles: many small writes SmallFiles --> Compact: OPTIMIZE Compact --> Sorted: V-Order or Z-Order Sorted --> Cleaned: VACUUM removes unreferenced files Cleaned --> SmallFiles: more writes
stateDiagram-v2 state "Thousands of small files" as s0 state "Fewer large files" as s1 state "Faster reads" as s2 [*] --> s0 s0 --> s1: OPTIMIZE s1 --> s2 s2 --> [*]
Key point: Small files slow Delta reads.
Why the other options are wrong
- A. A missing sensitivity label, fixed by labeling the table
- A label does not change performance.
- B. An expired deployment pipeline, fixed by redeploying it
- A pipeline does not affect file layout.
- D. Too few workspaces, fixed by splitting the table across more of them
- Workspaces have no effect on file layout.