Skip to content

Data Analyst Associate · Domain 5: Analyzing Queries · 15% of the exam

Task 5.2: Utilize Delta Lake to audit and view history, validate results, and compare historical results or trends; Utilize query history and caching to reduce development time and query latency

DESCRIBE HISTORY and its operation metrics, comparing versions, RESTORE, and the result, disk and UI caches that make repeated queries faster.

Study it

  • Table history and query caching

    Partly covered by: Caching

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

A nightly MERGE applies 1,000 source rows to the Delta table main.crm.accounts. To validate last night's run, an analyst wants the number of existing rows that were changed and the number of new rows that were added. Where in DESCRIBE HISTORY output does she find them?

  1. AnumOutputRows in the operationMetrics map of the MERGE row in the history
  2. BnumSourceRows in the operationMetrics of the MERGE row
  3. CThe operationParameters column of the MERGE row
  4. DnumTargetRowsUpdated and numTargetRowsInserted in operationMetrics
Show the answer and why
  • AnumOutputRows in the operationMetrics map of the MERGE row in the history

    Incorrect

    For MERGE, numOutputRows is the total number of rows written out, including copied target rows; it does not separate updates from inserts.

  • BnumSourceRows in the operationMetrics of the MERGE row

    Incorrect

    numSourceRows is the number of rows in the source, which is known before the merge.

  • CThe operationParameters column of the MERGE row

    Incorrect

    operationParameters holds the parameters of the operation, such as its predicates, not row counts.

  • DnumTargetRowsUpdated and numTargetRowsInserted in operationMetrics

    Correct

    These MERGE metrics give the number of rows updated in and inserted into the target table.

DESCRIBE HISTORY returns provenance for each write, and its operationMetrics column holds metrics per operation. For MERGE these include numSourceRows, numTargetRowsInserted, numTargetRowsUpdated, numTargetRowsDeleted, numTargetRowsCopied and numOutputRows, the total number of rows written out.

Question 2 · choose 1

A published AI/BI dashboard reads a small gold table (40,000 rows). A Lakeflow pipeline updates the table at 06:00, but at 07:00 viewers still see yesterday's numbers, and their filter changes leave no entries in query history. What should the analyst do so that the dashboard reflects each morning's load?

  1. ARun SET use_cached_result = false in the SQL editor before the viewers open the dashboard
  2. BRestart the dashboard's SQL warehouse after the pipeline finishes
  3. CAdd a dashboard schedule that runs after the 06:00 pipeline load
  4. DMake the dataset larger than 100,000 rows so filtering runs on the server
Show the answer and why
  • ARun SET use_cached_result = false in the SQL editor before the viewers open the dashboard

    Incorrect

    That setting is per session and concerns the query result cache, which never returns stale data; the stale results come from the dashboard cache.

  • BRestart the dashboard's SQL warehouse after the pipeline finishes

    Incorrect

    The dashboard cache is served without the warehouse running; a restart does not refresh it.

  • CAdd a dashboard schedule that runs after the 06:00 pipeline load

    Correct

    A data change or pipeline step does not refresh the dashboard cache; a dashboard schedule refreshes it reliably.

  • DMake the dataset larger than 100,000 rows so filtering runs on the server

    Incorrect

    Browser-side filtering explains why filter changes leave no history entries; it does not cause the stale numbers.

AI/BI dashboards first check a dashboard cache (24-hour, best effort) and then the query result cache. Unlike the result cache, the dashboard cache is not invalidated by data changes, and refreshing data in a pipeline step does not refresh it; a dashboard schedule does. Small datasets are filtered in the browser, so those interactions do not appear in query history.

Question 3 · choose 1

To check what last touched main.sales.orders, an analyst wants only the most recent operation from the table's history. Which statement returns it?

  1. ADESCRIBE DETAIL main.sales.orders
  2. BSHOW TBLPROPERTIES main.sales.orders
  3. CDESCRIBE HISTORY main.sales.orders LIMIT 1
  4. DSELECT * FROM main.sales.orders LIMIT 1
Show the answer and why
  • ADESCRIBE DETAIL main.sales.orders

    Incorrect

    DESCRIBE DETAIL shows table details such as properties and constraints, not the history of operations.

  • BSHOW TBLPROPERTIES main.sales.orders

    Incorrect

    Table properties are settings, not a list of operations.

  • CDESCRIBE HISTORY main.sales.orders LIMIT 1

    Correct

    DESCRIBE HISTORY with LIMIT 1 gets the last operation only.

  • DSELECT * FROM main.sales.orders LIMIT 1

    Incorrect

    This returns one data row, not history.

DESCRIBE HISTORY table_name returns the full history of a Delta table, and DESCRIBE HISTORY table_name LIMIT 1 returns only the last operation.

Question 4 · choose 1

In DESCRIBE HISTORY output for a Delta table, which column names the command that produced each version?

  1. Aoperation
  2. BreadVersion
  3. CisBlindAppend
  4. DclusterId
Show the answer and why
  • Aoperation

    Correct

    The operation column holds the name of the operation that created the version.

  • BreadVersion

    Incorrect

    readVersion is the table version the operation read.

  • CisBlindAppend

    Incorrect

    isBlindAppend tells whether the write only appended data.

  • DclusterId

    Incorrect

    clusterId identifies the compute that ran the operation.

The table history schema includes version, timestamp, userId, userName, operation (the name of the operation), operationParameters, job, notebook, clusterId, readVersion, isolationLevel, isBlindAppend and operationMetrics.

Question 5 · choose 1

When an analyst reopens a saved query in Databricks SQL, its most recent result appears at once without running the query again. Which cache does this, and how long can it keep results?

  1. AThe disk cache, until the warehouse restarts
  2. BThe remote result cache, for 30 days
  3. CThe Databricks SQL UI cache, per user, for at most 7 days
  4. DThe dashboard cache, for 24 hours
Show the answer and why
  • AThe disk cache, until the warehouse restarts

    Incorrect

    The disk cache stores data read from storage on local SSDs, not saved query results for the UI.

  • BThe remote result cache, for 30 days

    Incorrect

    The result cache serves repeated queries on warehouses; the UI result on reopening comes from the UI cache.

  • CThe Databricks SQL UI cache, per user, for at most 7 days

    Correct

    The UI cache keeps query results per user and has at most a 7-day life cycle.

  • DThe dashboard cache, for 24 hours

    Incorrect

    The dashboard cache belongs to AI/BI dashboards, not saved queries.

The Databricks SQL UI cache is a per-user cache of query results and SQL editor visualizations; when you open a query it shows the most recent result. It has at most a 7-day life cycle and does not apply to AI/BI dashboards.

Databricks documentation

Question 6 · choose 1

Analysts keep retyping the same long JOIN between orders and customers in new queries. Which Databricks SQL feature lets them share it and insert it through autocomplete?

  1. AQuery history
  2. BQuery tags
  3. CGenie Agent instructions
  4. DQuery snippets
Show the answer and why
  • AQuery history

    Incorrect

    Query history lists past runs; it does not insert reusable fragments.

  • BQuery tags

    Incorrect

    Query tags label queries for tracking.

  • CGenie Agent instructions

    Incorrect

    These guide a Genie Agent's answers, not the SQL editor's autocomplete.

  • DQuery snippets

    Correct

    Query snippets are segments of queries that you can share and trigger with autocomplete, such as frequent JOIN statements.

Query snippets are reusable segments of queries, such as frequent JOIN statements or complicated WITH and CASE clauses, that users share and insert through autocomplete.

Practice domain 5 →Practice all domains →