Executing Queries with Databricks SQL

What this chapter covers

Executing queries using Databricks SQL and Databricks SQL Warehouses is 20% of the Databricks Data Analyst Associate exam, the largest share of any chapter. It covers the SQL an analyst writes every day and the compute that runs it.

The chapter has three parts: how SQL warehouses run queries, how Genie Code helps write and debug them, and how to join Delta tables with federated sources; the core query patterns (aggregates, joins, set operations, sorting and filtering); and creating tables, views and materialized views and reading old versions with Delta Lake time travel.

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

You write a query in the SQL editor or a notebook, with Genie Code helping. A SQL warehouse runs it against Unity Catalog tables, including foreign catalogs from other databases. The result is returned, saved as a table, wrapped in a view, or precomputed as a materialized view.

• Warehouse questions ask what a SQL warehouse does and which type fits a need.
• Assistant questions ask how Genie Code explains, fixes or writes a query.
• Query questions show SQL and ask what it returns, especially for outer joins, UNION and NULLs.
• Table and view questions ask managed vs external, view vs materialized view vs streaming table, and time travel syntax.

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.