Microsoft Certified: Fabric Analytics Engineer AssociatePlan and implement data analytics solutions (10-15%)Medium
A data engineer is working with a large Delta Lake table in a Microsoft Fabric Lakehouse that stores historical sensor readings. Over time, the table has accumulated many small files due to frequent micro-batch ingests, leading to suboptimal query performance. The engineer needs to consolidate these small files into larger, more manageable ones to improve read performance without altering the data. Which Delta Lake command should the engineer execute?
- ARESTORE
- BMERGE INTO
- CVACUUM
- DOPTIMIZE
Show answer & explanationAnswer & explanation
Correct answer: D. OPTIMIZE
The OPTIMIZE command on a Delta Lake table is specifically designed to combine small files into larger ones, which significantly improves query performance by reducing metadata overhead and the number of file reads.
Why the other options are wrong
- A. RESTORE is used to revert a Delta table to an earlier version, not for file optimization.
- B. MERGE INTO is used for upsert operations (insert, update, delete) based on a condition, not for file consolidation.
- C. VACUUM is used to remove old, unreferenced data files from a Delta table, not to consolidate small files.
Delta Lake OPTIMIZE
The Delta Lake OPTIMIZE command improves query performance on Delta tables by compacting small files into larger, more efficient files.
- Reduces metadata overhead for query engines.
- Can be run with optional ZORDER by columns for further performance gains.
- Does not alter the data content, only its physical storage.
Memory trick: Small files slow? Optimize and go!