Databricks-DA-Assoc Executing Queries with Databricks SQL Practice Question
Exhibit
CREATE TABLE sales_optimized AS SELECT * FROM sales; OPTIMIZE sales_optimized ZORDER BY (customer_id);
Refer to the exhibit. Why might the analyst choose to create a new table with 'AS SELECT *' instead of just running OPTIMIZE on the original table?
⚠ Common exam trap
Candidates assume CTAS is always more expensive or slower than OPTIMIZE, failing to recognize that for highly fragmented tables, a full rewrite is often the cleanest and most efficient path.
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
✓
It provides a cleaner way to reorganize data and apply Z-Ordering
Creating a new table via CTAS (Create Table As Select) allows the analyst to re-partition or re-cluster the data from scratch, which is often faster and cleaner than reorganizing an existing, fragmented table. This process also allows for the application of Z-Ordering during the initial creation, ensuring that the new table has the optimal data layout from the first day of its existence.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The original table is read-only and cannot be modified
Why it's wrong here
Delta tables are inherently mutable unless defined otherwise by specific permissions. There is no standard 'read-only' property for a Delta table that would prevent an OPTIMIZE command. This is not the primary reason for using a CTAS pattern for data reorganization in typical analytical workflows.
- ✗
It is the only way to delete old data from the table
Why it's wrong here
Data can be deleted from Delta tables using the DELETE FROM command. Creating a new table to remove old data is inefficient and unnecessary, as it involves a complete rewrite of the dataset, whereas DELETE operations target specific rows and update the transaction log accordingly.
- ✓
It provides a cleaner way to reorganize data and apply Z-Ordering
Why this is correct
Using CTAS allows the analyst to rebuild the table with a clean file layout from the start. By applying Z-Ordering during or immediately after the creation, the analyst ensures the data is optimally structured, which is often more efficient than attempting to reorganize a heavily fragmented, existing table.
- ✗
CTAS automatically creates indexes on the table
Why it's wrong here
Databricks SQL does not use traditional indexes in the way legacy relational databases do. A CTAS operation will create a new table, but it does not generate indexes. Performance is achieved through partitioning, Z-Ordering, and data skipping, not through the creation of traditional database indexes during table definition.
About these practice questions
Courseiva writes every Databricks-DA-Assoc question from scratch — 291 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.