Databricks Data Analyst Associate Practice Exam

• A. Delta Lake, the open storage layer with ACID transactions
• B. Lakeflow Jobs, the orchestration service for scheduled tasks
• C. Mosaic AI, the toolset for building and serving models
• D. Unity Catalog, the governance layer for data and AI assets Correct answer

Option D is correct because Unity Catalog is the governance layer that centralizes permissions, lineage and data discovery for tables, dashboards and other assets across workspaces. Delta Lake is a storage format, Lakeflow Jobs orchestrates tasks, and Mosaic AI covers model building and serving, so none of them provide governance.

• A. Zero rows, since the drop deleted the files at the location
• B. The original row count, read from the files left at the location Correct answer
• C. An error, since the location stays reserved after the table drop
• D. Zero rows, since a new table starts empty until data is loaded

Option B is correct because dropping an external table removes only the metadata, not the data files at its storage location. A new Delta table created at the same location reads the existing files and transaction log, so the original row count comes back. Options A and D wrongly assume the files were deleted or that a new table starts empty, and the location is not reserved after a drop, so option C is wrong too.

• A. Lineage for the upstream tables, then Insights of each for the latest write
• B. Lineage for the upstream tables, then History of each for the latest write Correct answer
• C. Insights for the upstream tables, then History of each for the latest write
• D. Sample Data for the upstream tables, then Lineage of each for the latest write

Option B is correct because the Lineage view shows which upstream tables feed the dashboard source, and the History of each table shows its commits, so the table whose latest write is missing is the one that stopped receiving data. Sample Data only shows rows, not when they were written, and usage insights describe how a table is queried rather than when it was last loaded.

• A. The History tab, which lists each Delta write with its version number
• B. The Lineage tab, which maps upstream sources and downstream consumers Correct answer
• C. The Details tab, which lists the owner, storage location, and format
• D. The Permissions tab, which lists the grants held by each principal

Option B is correct because the Lineage tab in Catalog Explorer shows upstream sources and downstream consumers such as tables and dashboards, which is what an impact check needs. The History tab lists Delta writes, the Details tab shows metadata such as owner and format, and the Permissions tab shows grants, so none of them show consumers.

• A. An error, since the subquery column contains a NULL value
• B. Two rows, IDs 3 and 4, since the NULL entry is skipped
• C. Four rows, since the NULL entry cancels the NOT IN filter
• D. Zero rows, since the NULL makes each NOT IN test unknown Correct answer

Option D is correct because id NOT IN (1, 2, NULL) evaluates to unknown for ids 3 and 4 (the comparison with NULL is unknown), and WHERE keeps only rows that evaluate to true, so zero rows return. The query does not raise an error, and the NULL does not get skipped. NOT EXISTS or filtering out NULLs in the subquery avoids this trap.

• A. One row: A with a count of 1 Correct answer
• B. Three rows: A with 1, B with 1 and C with 0
• C. Two rows: A with 1 and B with 0
• D. Three rows: A with 1, B with 0 and C with 0

Option A is correct because the WHERE clause runs after the LEFT JOIN and removes every row whose o.status is not 'shipped', including the NULL-extended row for customer C and the pending rows for A and B. Only customer A's shipped order remains, so one row with a count of 1 returns. Moving the status test into the ON clause would keep B and C with 0.

• A. A manual file upload through the Workspace UI after each arrival
• B. A Delta Sharing share created on the bucket's JSON files
• C. Auto Loader reading the S3 path incrementally into a Delta table Correct answer
• D. A Lakeflow Connect managed connector pointed at the S3 bucket

Option C is correct because Auto Loader reads a cloud storage path incrementally and tracks which files it has already ingested, so each new JSON file lands in the Delta table once without reprocessing earlier files. Option A is manual and does not scale to files arriving every few minutes. Option B is wrong because Delta Sharing shares Delta tables with recipients and is not a file ingestion tool. Option D is not the fit here because a managed connector is meant for supported source systems, while incremental file arrival in object storage is the Auto Loader case.

• A. No data; the refresh fails once the local file has been modified
• B. The corrected values; the refresh re-reads the source CSV file
• C. The original values; the table is a point-in-time copy of the file Correct answer
• D. The corrected values for the three rows, after a Delta merge on refresh

Option C is correct because a workspace UI upload reads the file once and creates a table from it, so the table is a point-in-time copy. Editing budget.csv on the laptop afterwards does not change main.fin.budget, and the dashboard keeps showing the original values. Options B and D assume the table stays linked to the local file, which it does not. Option A is wrong because the refresh queries the table, not the laptop file, so it does not fail.

• A. A dynamic view that aggregates the orders table by viewer group membership
• B. A materialized view that aggregates the orders table, refreshed on a schedule Correct answer
• C. A streaming table that aggregates the orders table, refreshed on a schedule
• D. A standard view that aggregates the orders table, queried by the dashboard

Option B is correct because a materialized view stores the aggregated result, so dashboards read it quickly, and a scheduled refresh recomputes it from the source, which handles rows that are updated and deleted. Option C is wrong because a streaming table expects an append-only source and cannot absorb upstream updates and deletes. Options A and D are views that recompute the aggregate on every query, so they do not give fast stored results.

• A. Two rows: customers 1 and 2 with counts 2 and 1
• B. One row: customer 1 with a count of one open order Correct answer
• C. Three rows: customers 1, 2, 3 with counts 2, 1, 0
• D. Three rows: customers 1, 2, 3 with counts 1, 0, 0

Option B is correct because the WHERE clause on o.status = 'open' runs after the LEFT JOIN and removes every row whose order is not open, including the null-extended row for customer 3. Only customer 1 has an open order, so one row is returned with a count of 1. Options C and D assume the outer join keeps unmatched customers, which only holds when the condition sits in the ON clause.

• A. Add a broadcast join hint to the join with the dimension table
• B. Replace the exact aggregates with approximate aggregate functions
• C. Cluster the table on the date column so the scan can skip files Correct answer
• D. Move the query to a SQL warehouse with a larger cluster size

Option C is correct because the scan reads nearly every file but keeps under 1 percent of rows, so the cost is in file reads. Clustering the table on the date column lets the scan skip files whose date ranges cannot match the filter. Option A targets a join, option B targets an aggregate and option D adds compute, but the join and aggregate operators are already fast and the warehouse size is already appropriate.

• A. The SQL warehouse disk cache for the files the queries read
• B. Hive-style partitioning on the most frequently filtered column
• C. Automatic liquid clustering with predictive optimization on the table Correct answer
• D. A nightly OPTIMIZE job with ZORDER BY on a fixed set of columns

Option C is correct because automatic liquid clustering with predictive optimization lets the platform choose and adjust clustering keys and run maintenance without the team defining partitions or scheduling jobs by hand. Option B fixes the layout to one column, and option D fixes it to a chosen column set, so both need manual upkeep when the filters change. Option A is a cache of files already read and does not change how data is laid out.

• A. Add region to the GROUP BY and filter in the visualization
• B. Create one dataset per region and place them on separate pages
• C. Put region = :region in the dataset's WHERE clause as a parameter Correct answer
• D. Add a filter widget on the region field of the dataset's results

Option C is correct because a parameter referenced in the dataset's WHERE clause (region = :region) is applied inside the query, so the warehouse reads only the selected region's rows. Options A and D filter after the full result set is already computed, so the whole table is still scanned, and option B multiplies datasets without giving viewers a single selector.

• A. Return COUNT(*) of all failed payments ever recorded, then trigger above 100
• B. Keep the row-level query and set the alert to trigger when payment_id exceeds 100
• C. Subscribe to the dashboard on an hourly schedule and read the snapshot email
• D. Return a single hourly COUNT(*) from the query, then trigger when it exceeds 100 Correct answer

Option D is correct because an alert evaluates a value returned by its query, so the query must reduce the last hour's failures to one COUNT(*) number that is compared with 100. Option A counts all payments ever recorded and never reflects the last hour, option B compares an ID column rather than a count, and option C sends a dashboard snapshot on a schedule instead of testing a condition.

• A. More sample questions phrased the way business users ask about revenue
• B. A larger SQL warehouse so the space generates and runs its SQL faster
• C. A trusted asset holding a vetted SQL query for fiscal quarter revenue Correct answer
• D. A longer general instruction paragraph describing the fiscal calendar

Option C is correct because a trusted asset holds a vetted SQL query that Genie can reuse for the same kind of question, so the custom fiscal calendar join is written once and checked by the curator instead of regenerated each time. Options A and D only add wording around a join the stem says written instructions have not fixed, and option B changes speed, not which SQL is produced.

• A. Ask the 20 questions in chat and compare each result by eye
• B. Review a week of thumbs-up and thumbs-down feedback from users
• C. Mark each of the 20 questions as a sample question in the space
• D. Run benchmarks that pair each question with its expected SQL Correct answer

Option D is correct because benchmarks pair each question with its expected SQL, so the curator can run all 20 after the edit and see which still match instead of judging them one by one. Option A is manual and easy to get wrong, option B is delayed user opinion rather than a check against known answers, and option C only changes which questions are suggested to users.

• A. Split dim_product into brand and size tables for more normalization
• B. Reorganize the tables into hubs, links, and satellites as a data vault
• C. Fold the category columns into dim_product, producing a star schema Correct answer
• D. Move product and category attributes into fact_sales as a wide table

Option C is correct because folding the category columns into dim_product (denormalizing the snowflake) removes the second join and gives a star schema, at the cost of repeating category names, which the team accepts. Option A adds joins, option B is a different modeling pattern that does not reduce joins for reporting, and option D drops the separate product dimension the team wants to keep.

• A. The dashboard shows 170 and the bronze sum shows 270 Correct answer
• B. The dashboard shows 150 and the bronze sum shows 270
• C. The dashboard shows 170 and the bronze sum also shows 170
• D. The dashboard shows 270 and the bronze sum shows 170

Option A is correct because silver keeps only the latest event per order (order 7 at 120, order 8 at 50), so gold sums to 170. The bronze table appends every event, so summing it gives 100 + 120 + 50 = 270. Options B, C and D each get one of the two totals wrong.

• A. USE CATALOG on the main catalog for the analysts group
• B. MODIFY on the main.sales schema for the analysts group
• C. SELECT on the main.sales.returns table for the analysts group
• D. USE SCHEMA on the main.sales schema for the analysts group Correct answer

Option D is correct because reaching a table needs USE SCHEMA on its schema as well as USE CATALOG and SELECT. The group's SELECT on the schema is inherited by tables created later, so the missing piece is USE SCHEMA. Granting USE CATALOG again changes nothing, MODIFY is a write privilege, and a table-level SELECT is not enough without USE SCHEMA.

• A. prod.sales.orders and prod.sales.customers
• B. dev.sales.orders and dev.sales.customers
• C. prod.sales.orders and dev.sales.customers Correct answer
• D. None, because one query cannot reference two catalogs

Option C is correct because prod.sales.orders is fully qualified and so reads from prod, while sales.customers has no catalog part and resolves in the session's current catalog, dev. A single query can span catalogs, so option D is wrong, and options A and B each assume both names resolve to the same catalog.