Databricks Data Analyst: executing queries
Executing queries using Databricks SQL and Databricks SQL Warehouses is worth 20% of the Data Analyst Associate exam — around 9 of the 45 scored questions and the largest section. It covers writing SQL that is correct, and running it on compute that is appropriate.
The SQL you are expected to write
Standard analytical SQL, at a level a working analyst should have:
- Joins — inner, left, and the consequences of each. Knowing why a join can multiply rows.
- Aggregations with GROUP BY and HAVING, and the difference between filtering before and after aggregation.
- Subqueries and CTEs — and preferring CTEs for readability in multi-step logic.
- Window functions — ranking, running totals, lag and lead, period-over-period comparison. These are the most analyst-specific SQL on the exam and they appear reliably.
- Date and string handling.
The most examined correctness trap is the join that inflates row counts. If a fact table has 50,000 rows and joining a dimension produces 73,000, the join key is not unique on the dimension side. Analysts meet this constantly in practice and the exam knows it.
SQL warehouses and performance
Running the query is half the section:
| Lever | Affects |
|---|---|
| Warehouse size | How fast one complex query runs |
| Scaling | How many concurrent queries are served |
| Auto-stop | Cost when nobody is querying |
| Caching | Repeat queries returning without rescanning |
That table is the section in miniature. A single slow query is a size question. Thirty analysts queuing is a scaling question. A warehouse idling overnight is an auto-stop question. The exam offers all three as options and expects you to match the lever to the symptom.
Query history and profiling
- Finding a query that ran badly, and when.
- Reading a query profile: how much data was scanned, where time went.
- Recognising the obvious causes — scanning far more data than needed, an expensive join, no filtering applied early.
Writing queries that scan less
The habits the exam rewards:
- Filter early, so less data flows through the query.
- Select the columns you need rather than everything.
- Take advantage of partitioning where the table has it.
- Avoid repeating expensive work that could be computed once.
Sample questions
Question 1. Thirty analysts run small queries simultaneously each morning and queries queue. Individual queries are fast once they start. What is the appropriate change?
- A. Increase the warehouse size
- B. Enable scaling so more concurrent queries can be served
- C. Shorten the auto-stop interval
- D. Ask analysts to stagger their start times
Show answer
Answer: B
Queuing with individually fast queries is a concurrency problem, which scaling addresses by serving more queries at once. Increasing warehouse size makes single queries faster without improving concurrency, auto-stop affects idle cost, and staggering schedules works around the problem rather than solving it.
Question 2. An analyst needs each customer's most recent order, one row per customer, from an orders table containing full history. Which SQL approach is most appropriate?
- A. SELECT DISTINCT customer_id FROM orders
- B. GROUP BY customer_id with no other columns selected
- C. A window function ranking orders per customer by date, filtered to rank 1
- D. Ordering by date and relying on the query returning the newest first
Show answer
Answer: C
A window function ranking orders per customer by date and filtering to the top rank returns exactly one row per customer. A plain GROUP BY on customer cannot return the other columns of the latest order, a DISTINCT does not identify recency, and a self-join on max date is workable but more fragile and less direct.
Question 3. A dashboard query scans an entire five-year table each time, although it only displays the current month. What is the most effective improvement?
- A. Add a date filter so the query only scans the current month
- B. Increase the warehouse size
- C. Refresh the dashboard less frequently
- D. Remove some visualisations from the dashboard
Show answer
Answer: A
Filtering to the required period reduces the data scanned, which is the direct cause of the cost and latency. A larger warehouse pays more to scan the same volume, refreshing less often reduces frequency not per-query cost, and removing visualisations does not change the query.
What to practise
Write one query with a window function returning the latest row per group — it is the single most useful analyst pattern and it appears on the exam. Then deliberately create a row-inflating join and check the count before and after.
Finally, run the same query on a small and a large warehouse and compare the time and the cost. The lever-to-symptom mapping becomes obvious once you have seen it.