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

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. ACreate three separate staging tables (one per source) and then write a CREATE VIEW joining them, because CREATE TABLE AS SELECT does not support joining more than one source in a single statement within Databricks SQL
2. BUse 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 to read all three sources in one SELECT and materialize a single managed Delta table in Unity Catalog
3. CUse three separate INSERT INTO statements after creating an empty schema-matched table, inserting each source individually in sequence, because multi-source SELECT statements with heterogeneous formats cannot be combined into a single CTAS operation in Databricks SQL
4. DUse COPY INTO for each of the three sources in sequence, because COPY INTO is the required ingestion command for loading CSV and Parquet files into a Delta table when Unity Catalog is enabled and multi-source CTAS is not supported
5. ECreate the table as a CTAS from the Delta source only, then use ALTER TABLE ADD COLUMNS and subsequent MERGE statements for the CSV and Parquet sources, because combining different file formats in one CREATE TABLE AS SELECT is unsupported in Databricks SQL

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(*) — counts every row in the table including duplicate customer IDs, returning the total row count rather than the number of unique customers, which does not answer the question
2. BCOUNT(DISTINCT customer_id) — computes the exact number of unique customer IDs but requires the engine to accumulate all distinct values in memory, making it significantly more expensive and slower on billion-row tables
3. CAPPROX_COUNT_DISTINCT(customer_id) — applies a HyperLogLog probabilistic algorithm to estimate the number of unique customer IDs with a small configurable relative error (approximately 5% by default), completing in a fraction of the time and memory required by COUNT(DISTINCT) on large datasets
4. DMAX(customer_id) - MIN(customer_id) — computes the numeric range between the highest and lowest customer IDs, which measures the span of the ID space and not the count of distinct customers actually present in the data
5. ESUM(CASE WHEN customer_id IS NOT NULL THEN 1 ELSE 0 END) — counts all non-null customer_id rows including duplicates and is mathematically equivalent to COUNT(customer_id), providing no distinct-count estimate and no performance advantage

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 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
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 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

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

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 materialized view with ROW FILTER and MASK clauses, because materialized views physically store pre-computed results with security filters embedded, but these filters are evaluated at refresh time against a fixed user context rather than per-querying user at query time
2. BA standard (dynamic) view, because standard views are virtual and compute results at query execution time using the current user's identity, allowing row-level security conditions using functions like current_user() or is_member() without storing any data and with no extra storage overhead
3. CA Streaming Table with a WHERE clause referencing current_user(), because Streaming Tables incrementally process data and can apply per-user row filters natively without caching results in Unity Catalog storage
4. DA materialized view with TRIGGER ON UPDATE and a current_user() expression in the WHERE clause, because this configuration refreshes the cached data whenever upstream tables change and dynamically resolves user identity at each refresh interval
5. EA Lakehouse Federation foreign table view with row security inherited from the source database, because foreign table security policies are applied natively on the external system and no data is stored inside Unity Catalog

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 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
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 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
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

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

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. CCatalog 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
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. ELakeflow 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