Data Analyst Associate · Domain 4: Executing queries using Databricks SQL and Databricks SQL Warehouses · 20% of the exam
Task 4.4: 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; Use Delta Lake's time travel to access and query historical data versions
CREATE TABLE and CREATE TABLE AS SELECT for managed and external tables, read_files over CSV and Parquet, and querying earlier versions with VERSION AS OF and TIMESTAMP AS OF.
Study it
Managed and external tables, CREATE TABLE AS SELECT and Delta time travel
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
Analysts read main.gold.v_store_sales, a view that joins the Delta tables main.sales.orders and main.sales.stores. An auditor wants the view's result as it was at 06:00 on November 1. DESCRIBE HISTORY of orders shows version 73 committed at 05:40 and version 74 at 07:15 that day; DESCRIBE HISTORY of stores shows version 15 from October 28 and version 16 from November 5. Which query returns what the auditor wants?
AThe view's join, written against main.sales.orders VERSION AS OF 73 and main.sales.stores VERSION AS OF 15
BSELECT * FROM main.gold.v_store_sales VERSION AS OF 73
CThe view's join, written against main.sales.orders VERSION AS OF 73 and main.sales.stores VERSION AS OF 73
DThe view's join, written against main.sales.orders VERSION AS OF 73 and the current main.sales.stores
Show the answer and why
AThe view's join, written against main.sales.orders VERSION AS OF 73 and main.sales.stores VERSION AS OF 15
Correct
Time travel works on the Delta tables, and each version comes from that table's own history: 73 for orders and 15 for stores were current at 06:00.
BSELECT * FROM main.gold.v_store_sales VERSION AS OF 73
Incorrect
Views cannot be time traveled; a version clause belongs after the name of a Delta table.
CThe view's join, written against main.sales.orders VERSION AS OF 73 and main.sales.stores VERSION AS OF 73
Incorrect
A version number is read from the history of its own table, so version 73 of stores is not its state at 06:00.
DThe view's join, written against main.sales.orders VERSION AS OF 73 and the current main.sales.stores
Incorrect
The version clause applies to the table it follows, so this joins the current stores rows, which changed on November 5.
Delta tables support time travel with a clause after the table name, such as VERSION AS OF, where the version is taken from DESCRIBE HISTORY of that table. Views cannot be time traveled (error DELTA_UNSUPPORTED_TIME_TRAVEL_VIEWS), so to see what a view returned at an earlier moment, run the view's query against each underlying table at the version that was current then.
Version 18 of the Delta table t held the rows (1, 'A'), (2, 'B'), (2, 'B') and (3, 'C'). A faulty job then deleted both (2, 'B') rows, creating version 19. An analyst wants every deleted row back as a result, duplicates included, within the time travel window. Which query returns exactly the two deleted rows?
ASELECT * FROM t VERSION AS OF 18 EXCEPT SELECT * FROM t
BSELECT * FROM t EXCEPT ALL SELECT * FROM t VERSION AS OF 18
CSELECT * FROM t VERSION AS OF 18 EXCEPT ALL SELECT * FROM t
DSELECT * FROM t VERSION AS OF 18 INTERSECT ALL SELECT * FROM t
Show the answer and why
ASELECT * FROM t VERSION AS OF 18 EXCEPT SELECT * FROM t
Incorrect
EXCEPT without ALL defaults to DISTINCT and removes duplicates, so it returns (2, 'B') only once.
BSELECT * FROM t EXCEPT ALL SELECT * FROM t VERSION AS OF 18
Incorrect
This keeps rows of the current version that are missing from version 18, which returns no rows.
CSELECT * FROM t VERSION AS OF 18 EXCEPT ALL SELECT * FROM t
Correct
Time travel reads version 18, and EXCEPT ALL keeps duplicates, so both (2, 'B') rows missing from the current version are returned.
DSELECT * FROM t VERSION AS OF 18 INTERSECT ALL SELECT * FROM t
Incorrect
INTERSECT returns the rows found in both versions, which are the rows that were kept.
VERSION AS OF reads an earlier version of a Delta table. Set operators default to DISTINCT; EXCEPT ALL keeps the rows of the left input that are not matched in the right input, with duplicates, so comparing version 18 with the current version returns both deleted copies.
CREATE TEMPORARY TABLE tmp_orders CLUSTER BY (order_date) AS SELECT * FROM main.sales.orders fails. What change lets the temporary table be created?
AAdd USING DELTA to the statement
BAdd a LOCATION clause for the table files
CUse a three-level name such as main.sales.tmp_orders
DRemove the CLUSTER BY clause
Show the answer and why
AAdd USING DELTA to the statement
Incorrect
Temporary tables must not specify a data source in USING.
BAdd a LOCATION clause for the table files
Incorrect
Temporary tables must not specify LOCATION.
CUse a three-level name such as main.sales.tmp_orders
Incorrect
The name is not the cause; the clause list is.
DRemove the CLUSTER BY clause
Correct
PARTITIONED BY, CLUSTER BY and row-level filters are not supported for temporary tables.
For temporary tables, don't specify USING or LOCATION; PARTITIONED BY, CLUSTER BY and row-level filters are not supported, nor are GENERATED columns and column masks.
Which query reads version 123 of the Delta table events with the @ syntax?
ASELECT * FROM events@v123
BSELECT * FROM events@123
CSELECT * FROM events#123
DSELECT * FROM events VERSION 123
Show the answer and why
ASELECT * FROM events@v123
Correct
With the @ syntax, a version is given after @ with a v prefix, as in events@v123.
BSELECT * FROM events@123
Incorrect
A version after @ is written with a v prefix; otherwise the value after @ must be a timestamp in yyyyMMddHHmmssSSS format, and 123 is neither.
CSELECT * FROM events#123
Incorrect
The time travel shorthand uses @, not #.
DSELECT * FROM events VERSION 123
Incorrect
The keyword form is VERSION AS OF 123.
Besides its keyword clauses, Delta time travel accepts the @ syntax in the table name: a timestamp in yyyyMMddHHmmssSSS format, or a version with a v prefix, such as events@v123.
An analyst wants main.sales.orders as it was at 22:15 UTC on 18 October 2026. Which clause does time travel provide for this?
AAS OF DATE '2026-10-18'
BTIMESTAMP AS OF '2026-10-18T22:15:00.000Z'
CWHERE _commit_time = '2026-10-18T22:15:00.000Z'
DHISTORY AT '2026-10-18T22:15:00.000Z'
Show the answer and why
AAS OF DATE '2026-10-18'
Incorrect
The documented clauses are TIMESTAMP AS OF and VERSION AS OF.
BTIMESTAMP AS OF '2026-10-18T22:15:00.000Z'
Correct
TIMESTAMP AS OF reads the table as it was at the given timestamp.
CWHERE _commit_time = '2026-10-18T22:15:00.000Z'
Incorrect
A WHERE filter reads the current version; time travel uses a temporal clause on the table.
DHISTORY AT '2026-10-18T22:15:00.000Z'
Incorrect
There is no HISTORY AT clause; use TIMESTAMP AS OF.
Time travel queries use table_name TIMESTAMP AS OF timestamp_expression or table_name VERSION AS OF version, for example SELECT * FROM events TIMESTAMP AS OF '2018-10-18T22:15:12.013Z'.