Databricks-DA-Assoc Executing Queries with Databricks SQL Practice Question
An analyst needs to query a table that has many small files resulting from frequent streaming ingestions. Which command is most appropriate to consolidate these files into larger, more efficient files for future analytical queries?
⚠ Common exam trap
Candidates often pick commands like VACUUM or REORG, which serve different purposes. They fail to realize that OPTIMIZE is specifically designed to handle file compaction for performance improvement in Delta tables.
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 way to compact small files in Delta tables. It merges multiple small files into larger files, which reduces metadata overhead and improves read performance. By combining this with Z-Ordering, the data layout is optimized for common query patterns, ensuring that the SQL engine reads only the necessary data blocks during execution, which is vital for efficient data warehousing.
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 files that are no longer referenced by the Delta log and are older than the retention threshold. While it cleans up storage, it does not perform compaction or file reorganization. It does not consolidate existing small files into larger, more performant ones for query engines.
- ✓
OPTIMIZE table_name
Why this is correct
OPTIMIZE performs file compaction in Delta Lake. It takes small, fragmented files and rewrites them into larger, optimized files. This process significantly improves read performance by reducing the number of files the query engine needs to scan, making it the correct solution for small file ingestion issues.
- ✗
REFRESH TABLE table_name
Why it's wrong here
REFRESH TABLE only invalidates the metadata cache. It does not perform any physical reorganization of the data files on the underlying storage. It cannot consolidate small files into larger ones; it only ensures the engine sees the latest metadata state of the existing files.
- ✗
ALTER TABLE table_name REORGANIZE
Why it's wrong here
There is no 'REORGANIZE' keyword for the ALTER TABLE command in Databricks SQL. The correct operation for file reorganization is OPTIMIZE. This command is non-existent, and using it would result in a syntax error when executed in the SQL editor, making it an invalid choice.
About these practice questions
This Databricks-DA-Assoc question is part of Courseiva's 291-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 Databricks exam blueprint
This Databricks-DA-Assoc practice question is part of Courseiva's free Databricks 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 Databricks-DA-Assoc exam.