Analyzing Queries

What this chapter covers

Analyzing Queries is 15% of the Databricks Data Analyst Associate exam, the third largest chapter. It covers why a query is slow or wrong and what the platform offers to make it fast and correct.

The chapter has two parts: query performance tools (Photon, query history, the query profile, performance insights and caching), and table level tools (Delta Lake history for auditing and comparing results, predictive optimization, automatic Liquid Clustering and fixing a query that returns the wrong answer).

Questions are mostly short scenarios in which the stated requirement (freshness, cost, who may see what, audience) decides the answer. They are single-answer multiple choice, and short SQL is shown as text. There are no exhibits.

How the topics fit together

Performance questions start in query history, where you find the query, then move to the query profile to see which operator took the time. The fix is either in the SQL itself or in the table layout, which predictive optimization and Liquid Clustering can manage for you.

• Engine questions ask what Photon speeds up and what it does not.
• Diagnosis questions ask where to look for a slow query and what the profile shows.
• Caching questions ask why a rerun was instant and when the cache is not used.
• Table questions ask about DESCRIBE HISTORY, time travel and automatic maintenance.
• Fix questions show a short query and ask which change gives the intended result.

How to use these pages

Each topic page has the same shape, so you can skim for what you need.

• What it is: the idea in plain words.
• How it works: the mechanism, with tables and examples.
• What to remember for the exam: the facts to memorize and how questions are phrased.
• Common mistakes: the classic traps.
• Check yourself: three questions to test your understanding.