Skip to content

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

    Partly covered by: Transactions & ACID

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

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?

  1. AThe view's join, written against main.sales.orders VERSION AS OF 73 and main.sales.stores VERSION AS OF 15
  2. BSELECT * FROM main.gold.v_store_sales VERSION AS OF 73
  3. CThe view's join, written against main.sales.orders VERSION AS OF 73 and main.sales.stores VERSION AS OF 73
  4. 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.

Question 2 · choose 1

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?

  1. ASELECT * FROM t VERSION AS OF 18 EXCEPT SELECT * FROM t
  2. BSELECT * FROM t EXCEPT ALL SELECT * FROM t VERSION AS OF 18
  3. CSELECT * FROM t VERSION AS OF 18 EXCEPT ALL SELECT * FROM t
  4. 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.

Question 3 · choose 1

CREATE TEMPORARY TABLE tmp_orders CLUSTER BY (order_date) AS SELECT * FROM main.sales.orders fails. What change lets the temporary table be created?

  1. AAdd USING DELTA to the statement
  2. BAdd a LOCATION clause for the table files
  3. CUse a three-level name such as main.sales.tmp_orders
  4. 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.

Question 4 · choose 1

A setup script runs CREATE TABLE IF NOT EXISTS main.sales.targets (...) every day. What happens on days when main.sales.targets already exists?

  1. AThe existing table is replaced with an empty one
  2. BThe statement is ignored; the table stays
  3. CThe statement fails with an error
  4. DA second table with a numeric suffix is created
Show the answer and why
  • AThe existing table is replaced with an empty one

    Incorrect

    Replacing is what CREATE OR REPLACE does, not IF NOT EXISTS.

  • BThe statement is ignored; the table stays

    Correct

    With IF NOT EXISTS, if a table with the same name exists the statement is ignored.

  • CThe statement fails with an error

    Incorrect

    IF NOT EXISTS prevents the error for an existing table.

  • DA second table with a numeric suffix is created

    Incorrect

    No new table is created when the name exists.

IF NOT EXISTS makes CREATE TABLE do nothing when a table with the same name already exists. It cannot be combined with REPLACE.

Question 5 · choose 1

Which query reads version 123 of the Delta table events with the @ syntax?

  1. ASELECT * FROM events@v123
  2. BSELECT * FROM events@123
  3. CSELECT * FROM events#123
  4. 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.

Databricks documentation

Question 6 · choose 1

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?

  1. AAS OF DATE '2026-10-18'
  2. BTIMESTAMP AS OF '2026-10-18T22:15:00.000Z'
  3. CWHERE _commit_time = '2026-10-18T22:15:00.000Z'
  4. 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'.

Practice domain 4 →Practice all domains →