Databricks-DA-Assoc Executing Queries with Databricks SQL Practice Question
An analyst is running queries on a shared Databricks SQL Warehouse. Which TWO actions improve query performance by reducing the impact of high concurrency?
⚠ Common exam trap
Candidates often suggest scaling out compute clusters manually or rewriting queries, ignoring built-in declarative features like Result Set Caching and Materialized Views designed for concurrency.
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
✓
Enable Result Set Caching on the SQL Warehouse.
Managing concurrency in Databricks SQL involves both infrastructure configuration and query optimization techniques. By using materialized views or caching strategies, you reduce the compute load on the warehouse. These strategies prevent redundant processing of complex logic, ensuring that concurrent users receive faster responses without requiring the warehouse to constantly re-compute heavy analytical joins and aggregations on raw tables.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Enable Result Set Caching on the SQL Warehouse.
Why this is correct
Result set caching stores the output of repeated queries in the warehouse's local storage. When a subsequent user runs an identical query, the warehouse returns the cached result immediately without re-executing the query logic. This drastically reduces latency and saves compute resources during high-concurrency periods for frequent dashboards.
- ✗
Use SELECT * exclusively in all production dashboards.
Why it's wrong here
The SELECT * pattern is an anti-pattern because it forces the engine to read every column from the underlying storage, increasing I/O overhead. Selecting only necessary columns allows the query engine to utilize column pruning, which reduces data volume and improves memory efficiency during the execution phase.
- ✓
Convert complex frequently-queried tables into Materialized Views.
Why this is correct
Materialized views precompute the results of complex queries. When an analyst queries the view, the engine reads the precomputed data rather than calculating joins and aggregations from scratch. This significantly lowers the latency for concurrent users and offloads the intensive computation from the standard SQL warehouse execution time.
- ✗
Increase the warehouse size to 4XL regardless of query complexity.
Why it's wrong here
Increasing warehouse size does not always solve concurrency issues and often leads to wasted spend. Larger warehouses provide more resources for individual queries but do not necessarily handle more concurrent users efficiently. Proper warehouse sizing should be based on workload requirements rather than blindly scaling to the maximum size.
- ✗
Always use ORDER BY in every subquery.
Why it's wrong here
Using ORDER BY in subqueries adds unnecessary sorting overhead, which consumes CPU and memory. Sorting should only be applied at the final stage of a query if the presentation layer requires it. Unnecessary sorting operations inside subqueries significantly degrade performance in high-concurrency environments by increasing execution time per query.
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.