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. The exam guide does not name them, but they are everyday analyst SQL and make multi-step query scenarios easier to reason about.
- 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.
Materialized views and streaming tables
One objective in this section asks you to create a materialized view, to know when a streaming table fits better, and to tell dynamic views from materialized ones. Both object types come from the framework the platform section calls Delta Live Tables, now Lakeflow Spark Declarative Pipelines, but in Databricks SQL you create them with one statement from the SQL editor. Databricks runs each refresh on a serverless pipeline it creates for you, billed separately from your warehouse.
CREATE OR REFRESH MATERIALIZED VIEW sales.gold.revenue_by_region
SCHEDULE CRON '0 0 6 * * ?' AT TIME ZONE 'UTC'
AS SELECT region, SUM(amount) AS revenue
FROM sales.silver.orders GROUP BY region;
CREATE OR REFRESH STREAMING TABLE sales.bronze.web_events
SCHEDULE EVERY 1 HOUR
AS SELECT * FROM STREAM read_files('/Volumes/sales/raw/events/', format => 'json');
The choice follows the source and the shape of the query:
| Materialized view | Streaming table | |
|---|---|---|
| Source | Any tables, including ones where rows are updated or deleted | Append-only data: files landing in storage or a message stream |
| Each refresh | Brings the stored result in line with the current source, incrementally where possible | Processes only the rows that arrived since the last refresh |
| Good for | Aggregates and joins read by dashboards | Loading new data as it arrives |
You can refresh both manually (REFRESH MATERIALIZED VIEW ..., REFRESH STREAMING TABLE ...) or on a schedule; a materialized view can also use TRIGGER ON UPDATE to refresh when its sources change.
Dynamic versus materialized views. A standard (dynamic) view stores only the query and computes the result every time someone reads it, so it is always current but every read costs compute. A dynamic view in Unity Catalog can also filter rows or mask columns per user with functions such as is_account_group_member(). A materialized view stores the result and is only as fresh as its last refresh. The trade-off between them is covered in data modelling.
Pitfall: a streaming table is the wrong place for a daily revenue total. It reads each source row once, so a correction to an earlier order never changes the stored sum. Use a materialized view over the cleaned table. The Lakeflow Declarative Pipelines guide explains the dataset types in depth.
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.
Want more questions like this? Full Data Analyst practice tests →
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.
Want more questions like this? Full Data Analyst practice tests →
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.
Want more questions like this? Full Data Analyst practice tests →
Question 4. JSON files with web events land in a volume every few minutes. An analyst wants a table that picks up only the new files each hour, and a second object with hourly visit counts per page that a dashboard reads. Which pair fits?
- A. A view over the files and a view for the counts
- B. A materialized view over the files and a streaming table for the counts
- C. A streaming table over the files and a materialized view for the counts
- D. A streaming table over the files and a streaming table for the counts
Show answer
Answer: C
A streaming table processes only data that arrived since the last refresh, which suits files appended to a volume, and a materialized view stores an aggregate that stays in line with its source, which suits a dashboard. A view would recompute the count on every dashboard read. A materialized view over raw files gives up the incremental, once-only loading. Two streaming tables would not keep the aggregate correct if earlier data changed.
Want more questions like this? Full Data Analyst practice tests →
What to practise
Write one query with a window function returning the latest row per group — it is the single most useful analyst pattern, even though the exam guide does not list window functions by name. 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.