Databricks-Spark-Assoc Using Spark SQL Practice Question
A developer is building a Spark SQL pipeline in a Databricks notebook. They need to persist an intermediate DataFrame, built from a transformation of a Delta table, as a physical table in the current database so other notebooks in the same cluster can query it. They also want the table metadata to be managed by the metastore and the data to reside in the default warehouse directory. Which Spark SQL statement should they use?
⚠ Common exam trap
The trap here is assuming that a global temporary view persists data across sessions or is stored in the metastore, when it is still session-scoped and stores no physical data.
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
✓
CREATE OR REPLACE TABLE intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'
Creating an OR REPLACE TABLE with a SELECT statement materializes the transformed data as a managed Delta table, registers it in the metastore, and stores it in the default warehouse location. This matches the need for a persistent, cross-session-accessible physical table, unlike temporary views, global temporary views, or logical views, which do not persist materialized data.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
CREATE OR REPLACE TABLE intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'
Why this is correct
This statement creates a managed Delta table in the current database, stores its data in the warehouse directory, and registers metadata in the metastore. Because it is a managed table, it persists beyond the session and is queryable from other notebooks on the same cluster, satisfying both the persistence and metastore-management requirements described in the scenario.
- ✗
CREATE OR REPLACE GLOBAL TEMP VIEW intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'
Why it's wrong here
A global temporary view is visible across sessions within the same application but is still session-scoped and not persisted to the metastore. It also does not materialize data to the warehouse directory. It therefore fails the requirement that the result be a physical, metastore-managed table usable by other notebooks after the session ends.
- ✗
CREATE OR REPLACE VIEW intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'
Why it's wrong here
This creates a logical view that stores only the query definition in the metastore, not the result set. Each query against the view re-executes the underlying SELECT, so no physical data is written to the warehouse directory. It does not satisfy the requirement to persist the transformed output as a materialized table.
- ✗
CREATE OR REPLACE TEMP VIEW intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'
Why it's wrong here
A temporary view exists only for the lifetime of the current SparkSession and is not persisted to the metastore, so other notebooks in separate sessions cannot query it. It also does not write any data to the warehouse directory. This fails the requirement of a physical, metastore-managed table accessible across sessions on the same cluster.
About these practice questions
This Databricks-Spark-Assoc question is part of Courseiva's 295-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-Spark-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-Spark-Assoc exam.