Overview
Executing queries is the heaviest section, worth 20% of the exam, and it has nine objectives. Two are about the environment: using Genie Code (the new name for Databricks Assistant) in a notebook or the SQL editor to write and debug queries, and explaining how a SQL warehouse provides the compute that runs Databricks SQL. One covers Lakehouse Federation, where a foreign catalog lets you join a Delta table with a table in an external database such as PostgreSQL or Snowflake.
The rest is core SQL. You create materialized views and decide between them and streaming tables, and you tell a materialized view apart from a dynamic view. You write aggregates including approx_count_distinct and summary statistics, combine tables with every join type and with UNION or UNION ALL, sort and filter, create managed and external tables from CSV, Parquet and Delta sources, and query earlier table versions with time travel.
How it's tested
Expect a mix of SQL-reading items and short design choices. Query items give a statement and ask for its output or which statement meets a requirement. Design items describe a reporting need and ask for a materialized view, streaming table, view, federated query or table type. Count rows carefully and watch NULLs in joins.
-
Predict the result
A join, set operation, aggregate or filter is shown with small sample tables, and you choose the correct row count or output.
-
Pick the object
A refresh or freshness requirement is described, and you choose between a materialized view, streaming table, standard view or dynamic view.
-
Run it where
A question asks what compute executes Databricks SQL, how warehouse sizing or scaling helps, or how to query an external database without copying data.
Table A has customers 1, 2 and 3; table B has orders for customers 2, 3, 3 and 4. A LEFT JOIN from A to B keeps every customer, giving customer 1 with NULL order columns, customer 2 once and customer 3 twice, so four rows. An INNER JOIN would return three rows and drop customer 1. Customer 4 appears only with a RIGHT or FULL join.
Objectives 9
Official objectives from the Databricks outline, in outline order.
4.1 Utilize Genie Code (Databricks Assistant) within a Notebook or SQL Editor to facilitate query writing and debugging.AI help for writing, explaining and fixing queries.
- What it means
- Genie Code, previously called Databricks Assistant, is the context-aware AI pair programmer in notebooks and the SQL editor. It generates SQL from a natural language prompt, explains an existing query, suggests fixes when a query fails, and completes code inline, using Unity Catalog metadata such as table and column names and comments.
- What to understand
- It works best when tables have good comments, because it reads Unity Catalog metadata to choose columns. Typical uses are slash commands or prompts to explain, fix or optimise a query, and the diagnose error action after a failure. It respects your permissions, so it only references data you can access. Output still needs review: it can choose a wrong join key or misread a business rule, and the analyst remains responsible for correctness.
- How it shows up
- A scenario describes a failing query or a vague request and asks how to use Genie Code productively.
- Common confusion
- Assuming Genie Code can see tables the user has no privileges on.
4.2 Explain the role a SQL Warehouse plays in query execution.The compute resource behind Databricks SQL.
- What it means
- A SQL warehouse is the compute that runs SQL from the SQL editor, dashboards, alerts, Genie and BI tools connected over JDBC or ODBC. It comes in serverless, pro and classic types, has a size that sets cluster capacity, and can scale out to more clusters for concurrency.
- What to understand
- Serverless warehouses start in seconds and are managed by Databricks, which is why they are recommended. Warehouse size affects how fast an individual complex query runs, while the min and max cluster settings handle many concurrent users through scaling out. Auto stop shuts an idle warehouse down to save cost. Photon is enabled on SQL warehouses. Users need CAN USE on a warehouse to run queries on it, separate from data permissions.
- How it shows up
- Expect questions on whether to increase size or cluster count, or which warehouse type suits a bursty BI workload.
- Common confusion
- Scaling up size to fix queueing caused by many concurrent users, where adding clusters is the fix.
4.3 Querying cross-system analytics by joining data from a Delta table and a federated data source.Lakehouse Federation joins without copying data.
- What it means
- Lakehouse Federation lets you query external databases such as PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery or Redshift through Unity Catalog. An admin creates a connection and a foreign catalog, after which the external tables appear with three-level names and can be joined to Delta tables in ordinary SQL.
- What to understand
- Federation is suited to ad hoc analysis, proofs of concept and reporting on data that does not justify an ingestion pipeline. Databricks pushes filters and some operations down to the source to reduce data transfer, but heavy or frequent queries may be better served by ingesting the data. Foreign catalogs are governed by Unity Catalog, so grants and lineage apply. Queries are read-only against the source.
- How it shows up
- A requirement to combine live operational database rows with lakehouse tables without building a pipeline points to a federated join.
- Common confusion
- Assuming federation copies the external table into Delta storage.
4.4 Create a materialized view, including knowing when to use Streaming Tables and Materialized Views, and differentiate between dynamic and materialized views.Precomputed results versus incremental ingestion versus access-controlled views.
- What it means
- A materialized view stores the precomputed result of a query and is refreshed on demand or on a schedule, incrementally where possible. A streaming table processes new rows from an append-only source exactly once. A dynamic view is a standard view whose logic changes the output per user, for example with current_user or is_account_group_member, and stores nothing.
- What to understand
- Use CREATE MATERIALIZED VIEW for expensive aggregations and joins that dashboards read often; add a SCHEDULE clause or refresh it with REFRESH MATERIALIZED VIEW. Use a streaming table when ingesting continuously arriving data such as files or event streams, typically with read_files or a stream source. Materialized views reflect the source as of the last refresh, while a dynamic view computes at query time and is mainly a security tool for row and column restrictions.
- How it shows up
- A freshness, cost or ingestion pattern is described and you choose a materialized view, streaming table or dynamic view.
- Common confusion
- Calling a dynamic view materialized; dynamic views never store results.
4.5 Perform aggregate operations such as count, approximate count distinct, mean, and summary statistics.Counting, averaging and describing data with SQL.
- What it means
- Aggregates summarise many rows into one value per group: COUNT, COUNT(DISTINCT), approx_count_distinct, AVG or mean, SUM, MIN, MAX, stddev and percentile functions, used with GROUP BY and filtered with HAVING.
- What to understand
- approx_count_distinct trades a small, configurable error for much faster distinct counts on large tables. COUNT(*) counts rows while COUNT(col) ignores NULLs, and AVG ignores NULLs too, which can raise an average compared with treating missing values as zero. Summary statistics can come from aggregate functions, from the table profile in the results pane, or from summarize-style commands in notebooks. Filter rows before grouping with WHERE and filter groups after with HAVING.
- How it shows up
- A large-table distinct count with a tolerance for small error points to approx_count_distinct.
- Common confusion
- Using WHERE on an aggregated value, which fails; HAVING is required.
4.6 Write queries to combine tables using various join operations (inner, left, right, and so on) with single or multiple keys, as well as set operations like union and union all, including the differences between the joins (inner, left, right, and so on).Join types, multi-key joins and set operations.
- What it means
- Joins combine columns from two tables on matching keys: inner keeps matches only, left keeps all left rows, right keeps all right rows, full keeps everything, and semi and anti joins keep left rows that do or do not match. Set operations stack rows: UNION removes duplicates, UNION ALL keeps them, and INTERSECT and EXCEPT compare row sets.
- What to understand
- Multi-key joins put every key in the ON clause with AND, otherwise rows multiply. One-to-many relationships duplicate rows from the one side, which inflates sums. Unmatched outer join rows produce NULL columns, so a later WHERE on the right table can turn a left join into an inner join. UNION and UNION ALL need the same number of columns in compatible types and match by position. UNION ALL is faster because it skips deduplication.
- How it shows up
- Small sample tables are shown and you pick the row count or output of a given join or set operation.
- Common confusion
- Expecting UNION to keep duplicate rows, or LEFT and RIGHT joins to give the same result.
4.7 Perform sorting and filtering operations on a table.ORDER BY, WHERE, HAVING, LIMIT and QUALIFY.
- What it means
- Filtering limits rows with WHERE before aggregation, HAVING after aggregation, and QUALIFY after window functions. Sorting orders results with ORDER BY on one or more columns, ascending or descending, with explicit NULLS FIRST or NULLS LAST, and LIMIT restricts how many rows return.
- What to understand
- Logical order matters: FROM and joins, WHERE, GROUP BY, HAVING, window functions, QUALIFY, SELECT, ORDER BY, then LIMIT. Aliases defined in SELECT can be used in ORDER BY. Filters such as IN, BETWEEN, LIKE and ILIKE are common. Combining a window function with QUALIFY is the clean way to return top N rows per group. LIMIT without ORDER BY can return any matching rows.
- How it shows up
- A requirement such as top three products per region asks you to pick the correct combination of window function, QUALIFY and ORDER BY.
- Common confusion
- Filtering a window function result in WHERE, which runs before the window is calculated.
4.8 Create managed tables and external tables, including creating tables by joining data from multiple sources (e.g., CSV, Parquet, Delta tables) to create unified datasets, including Unity Catalog.Table creation from files and joins in Unity Catalog.
- What it means
- A managed table is created with CREATE TABLE without a location, and Unity Catalog stores and manages its files. An external table adds a LOCATION within a registered external location, so the files stay where you put them. CREATE TABLE AS SELECT builds a table from a query, which can join Delta tables with files read through read_files or a CSV and Parquet source.
- What to understand
- Managed tables are the default and recommended choice, gaining automatic maintenance such as predictive optimization. Dropping a managed table removes data, with a short window to UNDROP, while dropping an external one leaves files intact. CTAS infers the schema from the query; CREATE OR REPLACE TABLE swaps in a new definition while keeping history. All tables use a three-level name catalog.schema.table, so you need USE CATALOG, USE SCHEMA and CREATE TABLE privileges.
- How it shows up
- Expect to choose the statement that creates a unified table from mixed sources, or to predict the effect of dropping a table.
- Common confusion
- Using LOCATION when a managed table was required, or assuming DROP removes external files.
4.9 Use Delta Lake's time travel to access and query historical data versions.Querying earlier versions of a Delta table.
- What it means
- Every write to a Delta table creates a new version recorded in the transaction log. Time travel queries an earlier version with VERSION AS OF or TIMESTAMP AS OF, or the @v syntax, and RESTORE TABLE rolls a table back to a prior version.
- What to understand
- DESCRIBE HISTORY lists versions with timestamps, operations and users, which tells you which version to query. Time travel depends on the data files still existing: VACUUM removes files no longer referenced after the retention period, seven days by default, after which old versions cannot be read. Time travel is read-only, so restoring is a separate operation that itself creates a new version. It is useful for audits, reproducing reports and recovering from bad writes.
- How it shows up
- A bad update must be undone, or last week's figures reproduced; the answer uses RESTORE or VERSION/TIMESTAMP AS OF.
- Common confusion
- Expecting time travel to reach versions whose files were removed by VACUUM.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
SQL warehouse
Compute that runs Databricks SQL workloads.
- Remember
- Serverless is recommended; size and cluster count differ.
- Tested as
- Execution and scaling questions.
- Trap
- Confusing size with concurrency scaling.
-
Genie Code
AI assistant in notebooks and the SQL editor, formerly Databricks Assistant.
- Remember
- Uses Unity Catalog metadata and user permissions.
- Tested as
- Query writing and debugging.
- Trap
- Trusting output without review.
-
Foreign catalog
Unity Catalog mirror of an external database via federation.
- Remember
- Join external tables with Delta tables in SQL.
- Tested as
- Cross-system analytics.
- Trap
- Assuming data is copied.
-
Materialized view
Stored, refreshable query result.
- Remember
- Refresh on schedule or with REFRESH.
- Tested as
- Dashboard performance and freshness.
- Trap
- Treating results as real-time.
-
Streaming table
Table that processes new append-only data once.
- Remember
- For continuous ingestion.
- Tested as
- Choosing between maintained objects.
- Trap
- Using it for heavy aggregations.
-
Dynamic view
View whose output depends on the querying user.
- Remember
- Uses current_user or group membership.
- Tested as
- Row and column access.
- Trap
- Thinking it stores data.
-
approx_count_distinct
Fast estimated distinct count.
- Remember
- Small error, large speed gain.
- Tested as
- Large-table aggregates.
- Trap
- Choosing COUNT(DISTINCT) when speed is required.
-
HAVING
Filters groups after aggregation.
- Remember
- WHERE cannot reference aggregates.
- Tested as
- Filter placement.
- Trap
- Swapping WHERE and HAVING.
-
UNION versus UNION ALL
Set operations that stack rows.
- Remember
- UNION deduplicates, UNION ALL keeps all.
- Tested as
- Row count questions.
- Trap
- Expecting duplicates from UNION.
-
QUALIFY
Filters on window function results.
- Remember
- Top N per group pattern.
- Tested as
- Ranking queries.
- Trap
- Putting window filters in WHERE.
-
CTAS
CREATE TABLE AS SELECT.
- Remember
- Creates and fills a table from a query.
- Tested as
- Unified dataset creation.
- Trap
- Expecting it to stay in sync.
-
Time travel
Query earlier Delta versions.
- Remember
- VERSION AS OF, TIMESTAMP AS OF, RESTORE.
- Tested as
- Audit and recovery.
- Trap
- Ignoring VACUUM retention.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Maintained query objects
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Materialized view | Precomputed result stored as a table. | Refreshed on demand or schedule. | Assumed always current. |
| Streaming table | Incremental, exactly-once ingestion table. | Processes only new data. | Used for complex joins and aggregates. |
| Standard view | Saved query computed at read time. | No storage, always current. | Expected to be faster than a materialized view. |
| Dynamic view | View that filters or masks per user. | Access control at query time. | Confused with a materialized view. |
Join and set operation results
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| INNER JOIN | Rows with matches in both tables. | Drops unmatched rows. | Used when all left rows must remain. |
| LEFT JOIN | All left rows plus matches. | Unmatched right columns are NULL. | Later WHERE on right columns removes NULL rows. |
| LEFT ANTI JOIN | Left rows with no match. | Returns only left columns. | Writing NOT IN with NULLs instead. |
| UNION ALL | Stacks all rows from both queries. | Keeps duplicates and is faster. | Picking UNION when duplicates must remain. |
Warehouse scaling settings
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Warehouse size | Capacity of each cluster. | Speeds up individual heavy queries. | Raised to fix queueing. |
| Max clusters | Upper limit for scaling out. | Handles many concurrent queries. | Ignored when users queue. |
| Auto stop | Shuts down an idle warehouse. | Controls cost. | Set too long for occasional use. |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Size versus scale-out
- What candidates pick
- Increase the warehouse from Small to Large.
- Why it looks right
- Bigger sounds faster.
- Why it's wrong
- When dozens of users queue at peak, extra clusters through a higher max cluster count relieve concurrency; size mainly speeds individual queries.
Clue to watch for Many users, queries waiting in queue.
-
UNION keeps duplicates
- What candidates pick
- UNION to combine two monthly extracts with repeated rows.
- Why it looks right
- UNION is the familiar keyword.
- Why it's wrong
- UNION removes duplicate rows, so legitimate repeated transactions disappear; UNION ALL keeps every row.
Clue to watch for Duplicates must be preserved.
-
Streaming table for aggregates
- What candidates pick
- A streaming table for a daily sales summary.
- Why it looks right
- Streaming sounds fresh.
- Why it's wrong
- Streaming tables suit append-only ingestion; precomputed joins and aggregates for dashboards belong in a materialized view.
Clue to watch for Expensive aggregation read by dashboards.
-
Federation copies data
- What candidates pick
- Federation imports the external table into Delta.
- Why it looks right
- Most integrations ingest.
- Why it's wrong
- A foreign catalog queries the source in place, pushing work down where possible; nothing is copied unless you create a table from it.
Clue to watch for No pipeline, live data from an external database.
-
Time travel after VACUUM
- What candidates pick
- Query VERSION AS OF from a month ago.
- Why it looks right
- Versions are listed in history.
- Why it's wrong
- History metadata can outlive the files; once VACUUM removes the files past retention, those versions cannot be read.
Clue to watch for VACUUM ran with default retention.
-
Left join turned inner
- What candidates pick
- LEFT JOIN with WHERE right_table.col = 'X'.
- Why it looks right
- It looks like a filtered left join.
- Why it's wrong
- The WHERE clause removes rows where right columns are NULL, so unmatched left rows vanish; put the condition in ON instead.
Clue to watch for All customers must appear, even without orders.
-
DROP on external tables
- What candidates pick
- Dropping the external table frees its storage.
- Why it looks right
- Managed tables do delete files.
- Why it's wrong
- External tables only lose their Unity Catalog registration; the files at the location stay until removed separately.
Clue to watch for The table was created with LOCATION.
Answer framework
Work through these steps for any query or object-choice question in the largest section.
- Decide whether the stem asks for a result, a statement, an object type or a compute setting before reading options.
- For results, sketch the sample rows, apply joins first, then WHERE, GROUP BY, HAVING, QUALIFY, ORDER BY and LIMIT. Getting the order wrong is the usual source of a plausible but incorrect answer.
- For objects, match freshness and cost: materialized view for precomputed results, streaming table for ingestion, dynamic view for per-user access.
- For compute, separate single-query speed (warehouse size) from concurrency (cluster count) and startup time (serverless). A larger size helps heavy queries, while extra clusters absorb many users at once.
- For history or external data, choose time travel or RESTORE for past versions and a foreign catalog for live external tables.
Practice question
Want more like this? The Databricks DA Assoc practice test has questions for every domain.
Checklist
0 of 13
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Give this section the largest share of your study time, around a quarter of the total, and make it hands-on. Use a workspace with a serverless SQL warehouse, such as the Databricks Free Edition.
Start with joins and set operations. Create two small tables with deliberate edge cases: unmatched keys, duplicate keys and NULL keys. Run every join type and UNION versus UNION ALL, and write down the row count before each run. Then practise aggregates with GROUP BY, HAVING, approx_count_distinct and AVG over a column containing NULLs, followed by window functions with QUALIFY for top N per group.
Next, work with objects. Create a managed table, an external table if you have an external location, and a CTAS table that joins a CSV read through read_files with a Delta table. Create a materialized view with a schedule and refresh it manually. Update a table three times, run DESCRIBE HISTORY, query an old version and restore it.
Finally, open the warehouse settings to see size, scaling and auto stop, and use Genie Code to explain and fix a broken query. Read the Lakehouse Federation overview if you cannot create a connection yourself.
FAQ
How much is Executing queries using Databricks SQL and Databricks SQL Warehouses worth?
This section is 20% of the Databricks Data Analyst exam, about nine of the 45 scored questions, making it the largest section.
What is Genie Code?
Genie Code is the current name for Databricks Assistant, the AI helper in notebooks and the SQL editor. The October 2026 guide uses both names.
Do I need to memorise SQL syntax exactly?
You need to read and choose correct SQL confidently. Exact keyword spelling matters for items such as VERSION AS OF, QUALIFY and CREATE MATERIALIZED VIEW.
When should I use a streaming table instead of a materialized view?
Use a streaming table for incremental ingestion of append-only data. Use a materialized view for precomputed transformations such as joins and aggregates that consumers read repeatedly.
Is Lakehouse Federation the same as Delta Sharing?
No. Federation queries external databases through a connection and foreign catalog. Delta Sharing shares Delta data between organisations or platforms.
Which warehouse type should I default to?
Databricks recommends serverless SQL warehouses for fast startup and managed scaling, where available in your region.
How far back can time travel go?
As far as the data files still exist. VACUUM removes unreferenced files older than the retention threshold, seven days by default.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 4 knowledge
This is the largest section. Test your join reading, aggregate placement, materialized view choices and time travel syntax with timed practice questions.
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