Databricks-DA-Assoc Practice Question: A data analyst writes a complex Spark SQL query involving multiple window functions,…
A data analyst writes a complex Spark SQL query involving multiple window functions, CTEs, and aggregations. To debug the query performance, the analyst wants to inspect the logical optimization phases applied by the Catalyst optimizer. Which SQL command can the analyst use to view the unoptimized logical plan, optimized logical plan, and physical execution plan?
⚠ Common exam trap
Candidates often select the 'EXPLAIN' command alone, failing to realize that the basic command only provides the physical plan, omitting the critical logical optimization phases required for debugging complex queries.
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
✓
EXPLAIN EXTENDED SELECT ...;
Spark SQL provides the EXPLAIN command to inspect the underlying plans generated by the Catalyst optimizer. By specifying the EXTENDED or FORMATTED modifier, analysts can review parsed logical plans, analyzed logical plans, optimized logical plans, and final physical execution plans.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
DESCRIBE EXTENDED table_name;
Why it's wrong here
DESCRIBE EXTENDED shows metadata about a table or view: its columns and data types, owner, location, format, partitioning, properties and statistics. That is useful when investigating a query, for example to check partitioning, but it shows nothing about how a particular query is planned or optimised. Query plans are shown by prefixing the query with EXPLAIN, with modes such as EXTENDED or FORMATTED.
- ✗
SHOW QUERY PLAN SELECT ...;
Why it's wrong here
Spark SQL has no SHOW QUERY PLAN statement; plans are displayed by putting EXPLAIN in front of a query, and other databases use similar but different syntax, which makes this option look plausible. EXPLAIN supports several modes: plain for the physical plan, EXTENDED for all four plans, FORMATTED for a readable outline, CODEGEN for generated code and COST for statistics.
- ✓
EXPLAIN EXTENDED SELECT ...;
Why this is correct
EXPLAIN EXTENDED prints every stage Catalyst produces: the parsed logical plan, the analysed logical plan with names resolved, the optimised logical plan after rules such as predicate pushdown and column pruning, and the physical plan with chosen operators such as join strategies. With adaptive query execution, the final plan can change at runtime, so the Databricks query profile shows what actually ran.
- ✗
ANALYZE TABLE table_name COMPUTE STATISTICS;
Why it's wrong here
ANALYZE TABLE ... COMPUTE STATISTICS collects table and column statistics, such as row counts and value distributions, which the cost-based optimiser uses to choose better plans, for example join order and broadcast joins. It improves plans but displays none. To see how a query will run, use EXPLAIN, and run ANALYZE TABLE if the plan suggests the optimiser is working without good statistics.
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 →
You might also like
Similar Databricks-DA-Assoc questions
- Refer to the exhibit. The execution plan shows a SortMergeJoin with two Exchange nodes. What is the primary cause of the…
- What is the primary benefit of using a 'Materialized View' in Databricks SQL for frequent, expensive queries?
- A data analyst notices that a query involving a large join between two tables is consistently slow. The analyst suspects…
- A data analyst is analyzing a slow-running Databricks SQL query. The analyst suspects that the query is suffering from e…
- An analyst is optimizing a query that performs multiple aggregations on a Delta table. Which TWO actions can the analyst…
Written and reviewed by Johnson Ajibi, MSc IT Security
Security Systems Architect · 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.