Home / Data Analyst practice test / Executing queries using Databricks SQL and Databricks SQL Warehouses

Free · 8 questions with explanations

Executing queries using Databricks SQL and Databricks SQL Warehouses: Databricks Data Analyst Associate Practice Questions

Exam-style questions on Executing queries using Databricks SQL and Databricks SQL Warehouses. Pick your answer, then open the explanation to see why it's right — and why the other options are wrong.

Updated November 10, 2026

1 Executing queries using Databricks SQL and Databricks SQL Warehouses

A data analyst needs to create a new Unity Catalog managed Delta table by combining data from three sources: an existing Delta table registered in Unity Catalog, a CSV file at an external cloud path, and a Parquet file at another external path. Which SQL approach is correct?

  1. AUse CREATE TABLE catalog.schema.unified AS SELECT ... FROM delta_table JOIN read_files('/path/to/file.csv', format => 'csv') csv_src JOIN read_files('/path/to/file.parquet', format => 'parquet') par_src as one statement
  2. BCreate three staging tables, one per source, and then define a CREATE VIEW that joins them; in Databricks SQL a CREATE TABLE AS SELECT statement accepts one source relation, and the view provides the unified managed object
  3. CCreate an empty table with a matching schema and run three INSERT INTO statements in sequence, one per source; a single SELECT in Databricks SQL reads one file format at a time, which rules out one CTAS over mixed formats
  4. DUse COPY INTO three times in sequence, one per source; with Unity Catalog enabled, COPY INTO is the required command for loading CSV and Parquet files into a Delta table, and it also loads the registered Delta table
  5. ECreate the table with CTAS from the Delta source, then use ALTER TABLE ADD COLUMNS and MERGE statements for the CSV and Parquet sources; read_files() is restricted to streaming tables and is rejected inside a CTAS query
Show answer & explanation

Correct answer: A

WHY A: Databricks SQL supports CREATE TABLE AS SELECT (CTAS) that joins multiple sources, including registered Delta tables and files read inline with read_files(). One declarative statement reads the three sources and materializes a single managed Unity Catalog Delta table from heterogeneous inputs. WHY NOT B: CTAS supports multi-source joins, so the one-relation limit is false. A view would also not materialize the data as a managed table. WHY NOT C: A single SELECT can combine sources of different formats, so multi-source CTAS is supported. Sequential INSERT INTO statements are less efficient and require pre-creating an empty table. WHY NOT D: COPY INTO appends files into an existing table; it is not a required command under Unity Catalog and it does not join with other sources or load from a registered table in one operation. WHY NOT E: read_files() works in batch queries, including CTAS, so joins across Delta and file-based sources are handled natively and no ALTER TABLE plus MERGE workaround is necessary.

2 Executing queries using Databricks SQL and Databricks SQL Warehouses

A data analyst queries a billion-row customer events table to estimate the number of unique customer IDs. Exact accuracy is not required, but the query must return quickly with minimal memory usage. Which SQL function is most appropriate?

  1. ACOUNT(*) — reads row counts from Delta file statistics instead of scanning the data, returning the number of unique customers quickly and with very little memory on billion-row tables
  2. BCOUNT(DISTINCT customer_id) — computes the number of unique customer IDs from a compact sketch of the column, which keeps memory usage low and the query fast on billion-row tables
  3. CMAX(customer_id) - MIN(customer_id) — derives the number of unique customers from the highest and lowest IDs, which are read from column statistics without scanning the billion rows
  4. DAPPROX_COUNT_DISTINCT(customer_id) — estimates the number of unique customer IDs with a HyperLogLog sketch, accepting a small relative error (about 5% by default) for far less time and memory
  5. ESUM(CASE WHEN customer_id IS NOT NULL THEN 1 ELSE 0 END) — counts customers in a single pass with constant memory, giving an approximate unique total faster than COUNT(DISTINCT)
Show answer & explanation

Correct answer: D

WHY D: APPROX_COUNT_DISTINCT uses a HyperLogLog sketch to approximate distinct counts with about 5% relative error by default (configurable) at a tiny fraction of the time and memory of an exact COUNT(DISTINCT). That makes it the best choice when speed and resource efficiency matter more than precision. WHY NOT A: COUNT(*) counts every row including duplicate customer IDs, so it returns the total row count, not the number of unique customers. WHY NOT B: COUNT(DISTINCT) is exact, not sketch-based: the engine has to accumulate the distinct values, which makes it far more expensive and slower at billion-row scale. WHY NOT C: MAX minus MIN is a range computation over the ID space; it measures the span of IDs and has nothing to do with how many distinct values are present. WHY NOT E: A SUM over a CASE that counts non-nulls counts every occurrence of the column, duplicates included. It is equivalent to COUNT(customer_id), giving no distinct-count estimate and no performance advantage.

3 Executing queries using Databricks SQL and Databricks SQL Warehouses

A data analyst needs to register a table in Unity Catalog that reads Parquet files stored at a specific external cloud storage path. The underlying data files must NOT be deleted when the table is dropped. Which CREATE TABLE statement achieves this?

  1. ACREATE TABLE catalog.schema.my_table USING DELTA AS SELECT * FROM parquet./external/path/ — this materializes Parquet data into a new managed Delta table stored in Unity Catalog managed storage; the original Parquet files are unaffected but the new managed table's data files would be deleted on DROP TABLE
  2. BCREATE TABLE catalog.schema.my_table USING PARQUET MANAGED LOCATION '/external/path/' — this syntax is invalid in Databricks SQL; the MANAGED LOCATION keyword at table level does not create an external table pointing to the specified path
  3. CCREATE TABLE catalog.schema.my_table AS SELECT * FROM read_files('/external/path/', format => 'parquet') — this performs a CTAS that materializes query results into a new managed Delta table in Unity Catalog storage, copying data away from the original external Parquet path
  4. DCREATE EXTERNAL TABLE catalog.schema.my_table USING PARQUET LOCATION '/external/path/' — the EXTERNAL keyword is not required or recognized in Unity Catalog DDL; external tables are defined solely by the presence of the LOCATION clause without the EXTERNAL keyword
  5. ECREATE TABLE catalog.schema.my_table USING PARQUET LOCATION '/external/path/' — this creates an external table that references the Parquet files at the specified cloud storage path; when the table is dropped, Unity Catalog removes only the table metadata and leaves the underlying data files at the LOCATION path intact
Show answer & explanation

Correct answer: E

WHY E: In Unity Catalog, providing a LOCATION clause without copying data creates an external table. External tables reference files at the specified path, and dropping the table only removes Unity Catalog metadata — the underlying files are preserved. WHY NOT A: DELTA AS SELECT * materializes data into managed storage; this creates a managed table whose data IS deleted on DROP TABLE. WHY NOT B: MANAGED LOCATION is a catalog/schema-level property, not a table-level keyword; this syntax is invalid. WHY NOT D: The EXTERNAL keyword is not valid in Unity Catalog DDL; the LOCATION clause alone designates a table as external. WHY NOT C: CTAS with read_files() materializes data into managed storage, so the data would be deleted on DROP TABLE.

4 Executing queries using Databricks SQL and Databricks SQL Warehouses

A data analyst running a query in a Databricks SQL Editor receives a syntax error. She wants Databricks Assistant to automatically identify the problem and return a corrected, working version of the query. Which Databricks Assistant command is most appropriate?

  1. A/explain — narrates what each part of the query does and flags potential logic issues, but does not automatically rewrite or repair the query to fix syntax errors
  2. B/fix — detects syntax and semantic errors and automatically rewrites the query to produce a corrected, working version without any manual intervention
  3. C/generate — writes a new query from a natural language prompt describing the desired result, but does not repair an existing query that contains a known error
  4. D/optimize — restructures the query to improve runtime performance by suggesting more efficient execution strategies, but does not handle syntax correctness or error resolution
  5. E/help — provides documentation and a listing of available Databricks Assistant commands without actually modifying or correcting the query in the editor
Show answer & explanation

Correct answer: B

WHY B: /fix is designed to detect syntax and logic errors in a query and automatically return a corrected version, which is exactly what the analyst needs. WHY NOT A: /explain only describes query logic and identifies potential issues; it does not write fixes. WHY NOT C: /generate builds queries from scratch using natural language, not from an existing broken query. WHY NOT D: /optimize is a performance tool, not a correctness tool; it expects a valid query as input. WHY NOT E: /help only lists commands and links, providing no query repair capability.

5 Executing queries using Databricks SQL and Databricks SQL Warehouses

A security team needs a view in Databricks SQL that applies row-level filtering based on the current querying user's identity and group membership. They require no additional storage overhead and want the filtering logic to be evaluated dynamically at query time. Which view type should they use?

  1. AA standard (dynamic) view, which is virtual and computes its results at query execution time, so functions such as current_user() or is_member() resolve to the querying user and no data is stored
  2. BA materialized view with ROW FILTER and MASK clauses, which stores pre-computed results with the security filters embedded and resolves them against the querying user when the cached rows are read
  3. CA Streaming Table with a WHERE clause referencing current_user(), which processes data incrementally and applies the per-user row filter natively without caching results in Unity Catalog storage
  4. DA materialized view with TRIGGER ON UPDATE and current_user() in its WHERE clause, which refreshes the cached data when upstream tables change and resolves the user identity at each refresh
  5. EA Lakehouse Federation foreign table view with row security inherited from the source database, which applies the external system's policies natively and stores no data inside Unity Catalog
Show answer & explanation

Correct answer: A

WHY A: Standard (dynamic) views evaluate their defining SQL at query time, so identity functions like current_user() or is_member() resolve to the actual querying user. This gives dynamic row-level security with zero storage cost, because a standard view is virtual and stores no data. WHY NOT B: A materialized view physically caches pre-computed results, which is the storage overhead the team wants to avoid. Filtering embedded in a materialized view's definition is evaluated against a fixed refresh-time context, not per individual querying user. WHY NOT C: Streaming Tables are append-based ingestion objects that store their data; they do not provide per-user row filtering at query time through a WHERE clause on current_user(). WHY NOT D: A materialized view with current_user() in the WHERE clause captures the refresh-time user's identity into a static stored snapshot, not the querying user's identity, and it still stores data. WHY NOT E: This approach relies on the external database's security and does not give Unity Catalog-level governance over row filtering by the Databricks user's identity and group membership.

6 Executing queries using Databricks SQL and Databricks SQL Warehouses

A data analyst sorts a Unity Catalog table using ORDER BY sale_date ASC. Several rows have NULL values in the sale_date column. Where do NULL rows appear in the sorted result by default in Databricks SQL?

  1. ANULL rows appear last in an ascending sort by default in Databricks SQL, because NULL is considered greater than any non-NULL value for ordering purposes; to force NULLs to appear first the analyst must explicitly add NULLS FIRST to the ORDER BY clause
  2. BNULL rows are excluded entirely from the result set, because Databricks SQL treats NULL as an unknown value and automatically filters it out during ORDER BY evaluation just as NULL values fail equality comparisons in WHERE clauses
  3. CNULL rows appear first in an ascending sort, because Databricks SQL treats NULL as a sentinel value lower than any real date which forces nulls to the top of an ascending order sequence
  4. DNULL rows appear in unpredictable positions throughout the result set, because the placement of NULLs in ORDER BY output is non-deterministic and can vary with each query execution in Databricks SQL
  5. ENULL rows appear in the middle of the result set, sorted as if their value were zero, because Databricks SQL coerces NULL to a numeric zero equivalent for ordering purposes when the column being sorted contains a mix of NULL and non-NULL date values
Show answer & explanation

Correct answer: A

WHY A: In Databricks SQL (which follows ANSI SQL semantics), NULLs are sorted as if they are greater than any non-NULL value, meaning they appear at the end of an ASC sort and at the beginning of a DESC sort by default. To override this, you can append NULLS FIRST or NULLS LAST to the ORDER BY expression. WHY NOT C: NULLs appear LAST in ASC order by default in Databricks SQL, not first. WHY NOT B: ORDER BY never removes rows; NULL rows are included in the result set, just placed at the end. WHY NOT D: NULL placement in ORDER BY is deterministic and follows the NULLS LAST default for ASC. WHY NOT E: NULLs are not coerced to zero; they have a defined sort position at the tail of ascending sorts.

7 Executing queries using Databricks SQL and Databricks SQL Warehouses

A data analyst wants to combine the results of two SELECT queries with identical column schemas. Some rows appear in both result sets. She wants the final output to contain each unique row exactly once, with duplicates automatically removed. Which SQL set operation achieves this?

  1. AUNION ALL — combines the full result sets of both queries and retains every row from each, including exact duplicates, so rows that appear in both result sets will appear twice in the output rather than being deduplicated
  2. BINTERSECT — returns only rows that appear in both result sets, producing a much smaller result that is the opposite of combining the full outputs; it is a subtraction-style operation, not a union
  3. CUNION — combines the result sets of both queries and automatically removes duplicate rows using an implicit DISTINCT operation, returning each unique row exactly once across the full combined output
  4. DEXCEPT — returns rows from the first query that do not appear in the second query, subtracting the second result set from the first rather than merging both result sets together
  5. EMERGE — is a DML statement for row-level upsert operations between a source and target table; it is used to insert, update, or delete rows in a target, not to combine SELECT result sets from two queries with deduplication
Show answer & explanation

Correct answer: C

WHY C: UNION combines two SELECT results and applies implicit DISTINCT deduplication, returning each unique row from the combined output exactly once. WHY NOT A: UNION ALL combines without deduplication, so duplicates across both result sets are preserved. WHY NOT B: INTERSECT returns only rows present in both sets — it narrows rather than broadens the result. WHY NOT D: EXCEPT subtracts rows of the second set from the first; it does not combine both sets. WHY NOT E: MERGE is a DML command for row-level upsert operations, not for combining SELECT result sets; it cannot produce a deduplicated union of two query outputs.

8 Executing queries using Databricks SQL and Databricks SQL Warehouses

A data engineering team connects their Snowflake environment to Unity Catalog using Lakehouse Federation. They want all query execution for Snowflake data to happen exclusively on Databricks compute, not on the Snowflake engine. Which Lakehouse Federation type achieves this?

  1. AQuery federation — works by pushing SQL down to the remote database via JDBC, meaning Snowflake compute executes the remote portion of the query rather than Databricks, which does not satisfy the requirement for all-Databricks execution
  2. BAuto Loader ingestion federation — loads Snowflake export files from S3 into Delta and runs all compute on Databricks, but this requires materializing data first rather than federating live access
  3. CLakeflow Declarative Pipelines federation — reads from a Snowflake connection defined in Unity Catalog and writes results into managed Delta tables, but pipelines must materialize data before any Databricks SQL query can access it
  4. DDelta Sharing reverse connection — pulls Snowflake data into a Delta Sharing provider and allows Databricks recipients to query it, but this model still triggers Snowflake-side compute to serve the share
  5. ECatalog federation — creates a foreign catalog that accesses the Snowflake tables directly from their underlying object storage without using Snowflake's query engine, so all query execution runs entirely on Databricks compute
Show answer & explanation

Correct answer: E

WHY E: Catalog federation accesses table data directly from object storage using the external catalog's layout, bypassing the remote database engine entirely, so all compute runs on Databricks. WHY NOT A: Query federation pushes compute to the remote Snowflake engine via JDBC, which violates the all-Databricks requirement. WHY NOT B: Auto Loader is a file ingestion tool, not a live federation mechanism. WHY NOT D: Delta Sharing is not a pull-from-Snowflake federation protocol and does not support this reverse integration. WHY NOT C: Lakeflow Declarative Pipelines materialize data first; they do not provide live federated access.

Take the full Data Analyst practice test →