To speed up queries, an engineer partitions a branch loan-tracking table by branch code, loan date, and exact loan time, resulting in thousands of partitions each holding only a handful of rows. Query performance gets worse, not better, after this change. What is the most likely explanation?
Select an answer to reveal the explanation.
Short Explanation
Slicing a filing cabinet into thousands of drawers that each hold one sheet of paper doesn't make filing faster — now the effort of opening and closing drawers outweighs the tiny bit of paper found inside each one. That's what happens when a partition key is too fine-grained: instead of skipping irrelevant data, the query engine spends its time managing a huge number of nearly-empty partitions. The fix is choosing a coarser partition key that matches how the data and queries actually group.
Full Explanation
Partitioning helps only when the chosen column (or combination) groups data into a reasonable number of meaningfully-sized folders that queries can prune between; choosing branch code plus loan date plus exact loan time is a high-cardinality combination that produces thousands of partitions, most containing only a few rows each — reintroducing the same small-file overhead that compaction and sensible partitioning are meant to avoid, since the query engine now has to open and coordinate across an excessive number of tiny partitions. Delta tables do support multi-column partitioning, so the claim that only a single column is allowed is factually wrong and not the issue here. V-Order is a write-time encoding optimization; re-applying it wouldn't undo the structural cost of having too many tiny partitions. The SQL analytics endpoint can query partitioned Lakehouse tables normally, so that isn't a real constraint being hit. The real lesson is that partition granularity should be chosen to match actual query filters and expected row counts per partition, not simply layered onto every column that might someday be filtered. A caveat: the right partition strategy often trades off against write patterns too — a column that groups writes well but is rarely filtered on isn't worth partitioning by either. A concrete check: inspect the average row count and file size per partition after partitioning and compare it against a healthy target size before rolling the change out broadly.