Overview
Analyzing Queries is worth 15% and has six objectives. You need to know what Photon is, the vectorised native engine on SQL warehouses, which workloads it accelerates and which it does not. You must identify poorly performing queries using query history, Query Insights and the query profile, which shows operators, time spent, rows, spill and bytes scanned. You use Delta Lake history to audit changes, validate results and compare figures over time.
Caching appears through query history and the result and disk caches that cut latency for repeated queries. The 2026 guide changed the clustering objective: you now analyse table access patterns to decide when Predictive Optimization and automatic liquid clustering should maintain and lay out tables, rather than only applying liquid clustering by hand. The last objective is practical: fix a query so it returns the intended result, which often means repairing joins, grouping or filters.
How it's tested
Expect diagnosis scenarios. A symptom is described, such as slow filters on a large table, a long-running dashboard, unexpected totals or a stale result, and you choose the tool that reveals the cause or the change that fixes it. Some items show a faulty query and ask for the corrected version.
-
Find the bottleneck
A slow query is described and you choose where to look, such as the query profile, Query Insights or query history, and what metric matters.
-
Choose the optimisation
A table access pattern is described, and you choose automatic liquid clustering, Predictive Optimization, caching or Photon.
-
Fix the logic
A query returns the wrong totals or rows and you pick the edit that corrects joins, grouping, filters or NULL handling.
A query filtering a two-billion-row sales table by store_id and order_date takes minutes. The query profile shows most files scanned and little pruning. Raising warehouse size would add cost without fixing the scan. Enabling automatic liquid clustering on this Unity Catalog managed table, with Predictive Optimization running maintenance, lays data out by the filtered columns so files are skipped, which addresses the cause.
Objectives 6
Official objectives from the Databricks outline, in outline order.
5.1 Understand the Features, Benefits, and Supported Workloads of Photon.The native vectorised query engine.
- What it means
- Photon is Databricks' native, vectorised query engine written in C++ and compatible with Apache Spark APIs. It is on by default for SQL warehouses and speeds up SQL and DataFrame operations such as scans, joins, aggregations and writes to Delta and Parquet.
- What to understand
- Photon gives the biggest gains on large scans, joins, aggregations and data-heavy writes, and it needs no code changes. Workloads that rely heavily on Python UDFs, RDD operations or very short queries see little benefit because those parts fall back to the standard engine. Photon does not replace good table layout: a query that reads every file still reads every file, just faster.
- How it shows up
- A workload is described and you decide whether Photon would help, or what Photon accelerates.
- Common confusion
- Expecting Photon to speed up UDF-heavy Python code or to fix poor data layout.
5.2 Identify poorly performing queries in the Databricks Intelligence platform, such as Query Insights, Query Profiler log, etc.Tools for finding and diagnosing slow queries.
- What it means
- Query history lists queries with duration, status, user, warehouse and source. Opening a query shows its profile: a graph of operators with time, rows, memory, spill and bytes or files read and pruned. Query Insights highlights problems such as full scans, data skew, exploding joins or spill and suggests fixes.
- What to understand
- Diagnosis starts with history to find the slow or frequent queries, then the profile to see which operator dominates. Signs include large bytes read with few files pruned, a join producing far more rows than its inputs, spill to disk, and long queue time before execution starts, which points at warehouse capacity rather than the SQL. Filtering history by user, warehouse or dashboard helps locate the source.
- How it shows up
- A symptom is given and you choose whether to inspect query history, the query profile or Query Insights, and what metric matters.
- Common confusion
- Blaming SQL when time in queue shows the warehouse was saturated.
5.3 Utilize Delta Lake to audit and view history, validate results, and compare historical results or trends.DESCRIBE HISTORY and version comparison for auditing.
- What it means
- Delta Lake records every table change in its transaction log. DESCRIBE HISTORY returns each version with timestamp, user, operation, parameters and metrics such as rows written, so you can see who changed what and when.
- What to understand
- To validate a report, compare current results with a previous version queried by VERSION AS OF or TIMESTAMP AS OF, for example with EXCEPT to list rows that changed or with aggregates side by side to show trends. The change data feed, if enabled, gives row-level inserts, updates and deletes between versions. History is per table, and retention limits apply to how far back data can be read.
- How it shows up
- Questions ask how to find who deleted rows yesterday or how to show what changed between two loads.
- Common confusion
- Using query history to audit table changes; it records queries run on warehouses, not table versions.
5.4 Utilize query history and caching to reduce development time and query latency.Reusing past queries and cached results.
- What it means
- Query history lets you find and reopen past queries, see their text and profiles, and avoid rewriting work. Caching reduces latency: the query result cache returns results for identical queries when the underlying data has not changed, and the disk cache keeps copies of remote data files on local storage for faster reads.
- What to understand
- The result cache works only if the query text and data are unchanged, and non-deterministic functions such as current_timestamp prevent reuse. Serverless warehouses also have a remote result cache shared across warehouse restarts. The disk cache speeds repeated reads of the same files, even for different queries. Dashboards benefit from caching because many viewers run the same queries. Restarting a classic warehouse clears local caches.
- How it shows up
- Expect a scenario where repeated dashboard queries are slow or a cached result looks stale, and you explain the cache behaviour.
- Common confusion
- Thinking the result cache returns stale data after the table changes; it is invalidated.
5.5 Analyze table access patterns and query workloads to determine when to leverage Predictive Optimization and Automatic Liquid Clustering to optimize table performance.Automated maintenance and adaptive data layout.
- What it means
- Predictive Optimization automatically runs maintenance such as OPTIMIZE, VACUUM and statistics collection on Unity Catalog managed tables when Databricks predicts a benefit. Liquid clustering organises data by chosen columns to improve file skipping, and automatic liquid clustering, set with CLUSTER BY AUTO, lets Databricks choose and change the clustering keys from observed query patterns.
- What to understand
- Look at how a table is queried: frequent filters or joins on high-cardinality columns such as customer_id or event_date benefit from clustering. Liquid clustering replaces Hive-style partitioning and Z-ordering for new tables and can change keys without rewriting everything. Automatic liquid clustering and Predictive Optimization require Unity Catalog managed tables, which is one reason managed tables are recommended. Small tables rarely need clustering at all.
- How it shows up
- A large managed table with changing filter patterns points to automatic liquid clustering plus Predictive Optimization.
- Common confusion
- Choosing manual partitioning on a high-cardinality column, which creates many small files.
5.6 Fix a query to achieve the desired results.Correcting SQL logic errors.
- What it means
- This objective tests reading a query, spotting why its output does not match the requirement and choosing the correction. Typical faults include wrong join type or key, missing GROUP BY columns, WHERE versus HAVING, NULL handling, integer division and incorrect date filters.
- What to understand
- Work from the requirement back to the query: which rows should appear, at what grain and with which totals. Duplicate inflation usually means a one-to-many join before aggregation; missing rows suggest an inner join or a filter on an outer-joined column; wrong averages often trace to NULLs or to dividing integers. Date ranges need inclusive or exclusive boundaries chosen deliberately, especially with timestamps.
- How it shows up
- A short faulty query plus a requirement is shown, and you choose the one edit that produces the intended result.
- Common confusion
- Picking a performance change when the problem is incorrect logic.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
Photon
Native vectorised engine on SQL warehouses.
- Remember
- Speeds scans, joins, aggregations and writes.
- Tested as
- Workload fit questions.
- Trap
- Expecting gains for Python UDFs.
-
Query profile
Operator-level view of one query's execution.
- Remember
- Shows time, rows, spill and pruning.
- Tested as
- Bottleneck diagnosis.
- Trap
- Ignoring queue time.
-
Query Insights
Automatic detection of query performance issues.
- Remember
- Suggests fixes such as clustering.
- Tested as
- Diagnosis scenarios.
- Trap
- Treating it as a cost report.
-
Query history
Record of queries run on warehouses.
- Remember
- Filter by user, warehouse, status, duration.
- Tested as
- Finding slow or failed queries.
- Trap
- Using it to audit table changes.
-
DESCRIBE HISTORY
Lists Delta table versions and operations.
- Remember
- Who changed what and when.
- Tested as
- Auditing questions.
- Trap
- Confusing it with query history.
-
Result cache
Reuses results of identical queries.
- Remember
- Invalidated when data changes.
- Tested as
- Dashboard latency.
- Trap
- Non-deterministic functions block reuse.
-
Disk cache
Local copies of remote data files.
- Remember
- Speeds repeated reads of the same files.
- Tested as
- Latency questions.
- Trap
- Thinking it stores query results.
-
Predictive Optimization
Automatic OPTIMIZE, VACUUM and statistics.
- Remember
- Unity Catalog managed tables only.
- Tested as
- Maintenance choice.
- Trap
- Expecting it on external tables.
-
Liquid clustering
Flexible data layout by clustering keys.
- Remember
- Replaces partitioning and Z-order.
- Tested as
- Filter performance.
- Trap
- Partitioning on high-cardinality keys.
-
CLUSTER BY AUTO
Automatic liquid clustering key selection.
- Remember
- Adapts to query patterns.
- Tested as
- New 2026 objective wording.
- Trap
- Assuming keys must be set by hand.
-
Data skipping
Skipping files using statistics.
- Remember
- Depends on layout and stats.
- Tested as
- Profile reading.
- Trap
- Expecting bigger compute to skip files.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Diagnosis and audit tools
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Query history | List of executed queries with metrics. | Starting point to find slow queries. | Used to see table changes. |
| Query profile | Detailed execution graph of one query. | Shows the expensive operator. | Skipped in favour of guessing. |
| Query Insights | Automated problem detection and advice. | Highlights skew, spill and scans. | Assumed to change settings itself. |
| DESCRIBE HISTORY | Version log of a Delta table. | Audits writes, not reads. | Confused with query history. |
Performance features
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Photon | Faster execution engine. | Speeds each operator. | Expected to reduce bytes read. |
| Automatic liquid clustering | Adaptive data layout. | Reduces files read for filters. | Replaced by bigger warehouses. |
| Predictive Optimization | Automated table maintenance. | Keeps files compacted and stats fresh. | Thought to apply to external tables. |
| Result cache | Stored answers to identical queries. | Skips execution entirely. | Expected to work after data changes. |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Bigger warehouse for poor pruning
- What candidates pick
- Increase the warehouse size.
- Why it looks right
- More compute feels like a fix.
- Why it's wrong
- If the profile shows most files read, the problem is layout; clustering reduces data read, while size only reads it faster at higher cost.
Clue to watch for Few files pruned, large bytes scanned.
-
Photon for Python UDFs
- What candidates pick
- Enable Photon to speed a UDF-heavy job.
- Why it looks right
- Photon is the engine speed answer.
- Why it's wrong
- Python UDFs run outside Photon's native execution, so gains are small; rewriting with built-in SQL functions helps more.
Clue to watch for Custom Python functions dominate run time.
-
Query history for auditing
- What candidates pick
- Search query history for the deletion.
- Why it looks right
- History sounds like an audit log.
- Why it's wrong
- Table changes are recorded in the Delta transaction log, so DESCRIBE HISTORY on the table shows the delete, user and version.
Clue to watch for Who changed the table and when.
-
Stale cache worry
- What candidates pick
- Disable caching to see new data.
- Why it looks right
- Caches can be stale in other systems.
- Why it's wrong
- The result cache is invalidated when underlying Delta data changes, so fresh data is returned automatically.
Clue to watch for Data was updated, results seem old.
-
Partitioning by customer_id
- What candidates pick
- PARTITIONED BY customer_id for faster filters.
- Why it looks right
- Partitioning is a classic optimisation.
- Why it's wrong
- High-cardinality partitions create huge numbers of small files; liquid clustering handles such keys without that problem.
Clue to watch for Millions of distinct values in the filter column.
-
Performance fix for wrong totals
- What candidates pick
- Add caching or clustering to correct a report.
- Why it looks right
- The question mentions a dashboard.
- Why it's wrong
- Wrong totals are a logic issue, usually a join that multiplies rows or a missing GROUP BY column, not a speed problem.
Clue to watch for Results are incorrect, not slow.
Answer framework
Use this sequence when a question describes a slow or incorrect query.
- Decide whether the problem is speed, freshness or correctness, because each leads to different tools and fixes.
- For speed, open query history, then the query profile, and note queue time, bytes read, files pruned, spill and row explosion. High queue time points to warehouse capacity, not to the SQL itself.
- Match the cause: layout problems to automatic liquid clustering and Predictive Optimization, engine-bound work to Photon, concurrency to warehouse scaling.
- For repeated identical queries, rely on the result and disk caches, remembering that data changes invalidate cached results.
- For correctness, compare with Delta history or earlier versions, then fix join type, keys, grouping, filters or NULL handling. A many-to-many join often explains inflated totals.
Practice question
Want more like this? The Databricks DA Assoc practice test has questions for every domain.
Checklist
0 of 12
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Approach this section as a diagnosis exercise. Spend the first session in query history: run several queries on a SQL warehouse, including one that reads a large sample table without filters, then open each query's profile. Learn where queue time, bytes read, files pruned, rows produced and spill appear. Read the Query Insights documentation so you recognise the issues it flags.
In the second session, focus on Delta. Update and delete rows in a small managed table, run DESCRIBE HISTORY, and compare an old version with the current one using EXCEPT. Then create a table with CLUSTER BY AUTO and read about how Predictive Optimization decides when to run OPTIMIZE, VACUUM and ANALYZE. Note the managed-table requirement.
In the third session, test caching. Run the same query twice and see the second return from cache, then change the data and observe the cache being bypassed. Add current_timestamp to the query and note that reuse stops.
Finally, collect ten faulty queries from your own practice and fix them, labelling each fault: join fan-out, filter on outer join, missing GROUP BY column, NULL averages or integer division.
FAQ
What percentage of the Databricks Data Analyst exam is Analyzing Queries?
Analyzing Queries is 15% of the exam, about seven of the 45 scored questions. Only the query execution section and the dashboards section carry more weight.
What changed in this section in the 2026 guide?
The clustering objective now asks when to use Predictive Optimization and automatic liquid clustering based on access patterns, rather than only applying liquid clustering manually.
Do I need to tune Spark configurations?
No. The analyst exam focuses on SQL warehouses, query profiles, caching and table layout features rather than cluster configuration.
Is Photon enabled by default?
Yes, on SQL warehouses. It needs no query changes, though some operations such as Python UDFs do not benefit.
What is the difference between query history and table history?
Query history records queries executed on warehouses and their performance. Table history, from DESCRIBE HISTORY, records each write to a Delta table.
Does the result cache return old data after an update?
No. Cached results are invalidated when the underlying data changes, so the query is executed again.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 5 knowledge
Test your ability to diagnose slow queries, choose between clustering, caching and Photon, and correct faulty SQL under exam timing.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the Databricks DA Assoc AI tutor Get any concept explained your way