A data analyst needs to query historical sales data stored in a Microsoft Fabric Lakehouse. The data is partitioned by `year` and `month` in a Delta table. The analyst frequently queries data for a specific quarter. To optimize query performance, which file organization strategy within the Lakehouse should be recommended for the Delta table?
- AMaintaining a large number of small files to distribute I/O across many workers.
- BConsolidating small files into larger ones using `OPTIMIZE` with `ZORDER` by `SaleDate`.
- CUsing `VACUUM` regularly to remove old data files and compact the table.
- DStoring all data in a single large file to minimize file open/close overhead.
Show answer & explanationAnswer & explanation
Correct answer: B. Consolidating small files into larger ones using `OPTIMIZE` with `ZORDER` by `SaleDate`.
Consolidating small files into larger ones (using `OPTIMIZE`) reduces metadata overhead and improves read performance. `ZORDER` by a frequently queried column like `SaleDate` (which contains year/month/day) further co-locates related data, significantly speeding up queries that filter on that column or ranges within it, thus optimizing for quarter-based queries.
Why the other options are wrong
- A. A large number of small files (the 'small file problem') is detrimental to performance in distributed systems as it increases metadata processing and I/O overhead.
- C. `VACUUM` is for data retention and cleanup, removing unreferenced files, not for optimizing query performance through file consolidation or data co-location.
- D. While minimizing file overhead, a single large file negates the benefits of partitioning and can lead to performance bottlenecks for queries that only need a subset of the data.
Delta Lake OPTIMIZE and ZORDER
`OPTIMIZE` consolidates small files into larger ones, improving read performance. `ZORDER` is an optional clause for `OPTIMIZE` that physically co-locates related data based on specified columns, accelerating data skipping for queries.
- OPTIMIZE reduces small file problem.
- ZORDER improves data skipping for query predicates.
- Best used on frequently queried columns or high-cardinality columns for filtering.
Memory trick: Optimize and ZORDER your Delta tables for lightning-fast queries.