DP-700 Ingest and Transform Data Practice Question
You have a large Delta table that is frequently queried. You notice that queries are slow due to small file overhead. Which command should you run to optimize this table?
⚠ Common exam trap
Candidates often attempt to manually delete or merge files. They overlook the built-in OPTIMIZE command, which is the standard, optimized method for compacting small files in Delta Lake.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
OPTIMIZE table_name
The OPTIMIZE command is the standard Delta Lake maintenance operation that compacts small files into larger ones, drastically improving read performance. When combined with Z-Ordering, it also organizes data physically based on column values, which further accelerates query performance for filters. This is a critical task for maintaining high-performance analytical tables in a Lakehouse environment, preventing the performance degradation caused by file fragmentation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
VACUUM table_name
Why it's wrong here
VACUUM removes old files that are no longer referenced by the transaction log, helping manage storage costs. It does not compact existing files or improve query performance through data organization. While important for maintenance, it is not the correct command for addressing small file overhead issues.
- ✗
ANALYZE TABLE table_name COMPUTE STATISTICS
Why it's wrong here
ANALYZE updates metadata statistics, which helps the query optimizer make better decisions about join strategies and scan plans. It does not physically rearrange or merge the underlying data files, meaning it will not solve the issue of excessive small files causing slow read performance.
- ✓
OPTIMIZE table_name
Why this is correct
The OPTIMIZE command directly addresses small file fragmentation by rewriting the data into larger, more efficient files. This process reduces metadata overhead and improves overall scan speeds, making it the correct solution for tables suffering from performance degradation due to a high volume of tiny data files.
- ✗
REORG TABLE table_name APPLY
Why it's wrong here
There is no REORG command in standard Delta Lake implementations. This command is typically associated with legacy database systems like DB2 or SQL Server. Using invalid syntax will result in execution errors, as it is not part of the Spark-based Delta Lake command set used in Fabric.
About these practice questions
This DP-700 question is part of Courseiva's 152-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-700 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-700 exam.