Home / Data Analyst practice test / Analyzing Queries

Free · 8 questions with explanations

Analyzing Queries: Databricks Data Analyst Associate Practice Questions

Exam-style questions on Analyzing Queries. 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 Analyzing Queries

A data analyst writes the following query to find customers who have made at least one purchase in 2025: SELECT customer_id FROM orders WHERE year = 2025 GROUP BY customer_id HAVING COUNT(*) >= 1; The query runs but returns far fewer customers than expected. After investigation, the analyst finds the orders table has a column named 'purchase_year' (not 'year'). The WHERE clause references a non-existent column. How should the query be fixed?

  1. AAdd an alias: SELECT customer_id FROM orders WHERE year AS purchase_year = 2025 GROUP BY customer_id HAVING COUNT(*) >= 1 — using AS in the WHERE clause allows the engine to resolve the column name at filter time without changing the schema
  2. BAdd a table alias and qualify the column: SELECT customer_id FROM orders o WHERE o.year = 2025 GROUP BY customer_id HAVING COUNT(*) >= 1 — qualifying the column name with the table alias resolves column name ambiguity in tables that have multiple columns with similar names
  3. CRemove the WHERE clause entirely and change the HAVING clause to HAVING purchase_year = 2025 AND COUNT(*) >= 1, because HAVING is evaluated after GROUP BY and can apply both aggregate and non-aggregate conditions including column-level filters like the year
  4. DUse a CASE expression: SELECT customer_id FROM orders WHERE CASE WHEN year IS NULL THEN purchase_year END = 2025 GROUP BY customer_id HAVING COUNT(*) >= 1 — this safely handles the possibility that the column is named 'year' in some partitions and 'purchase_year' in others
  5. EReplace 'year' with the correct column name: SELECT customer_id FROM orders WHERE purchase_year = 2025 GROUP BY customer_id HAVING COUNT(*) >= 1 — this corrects the filter and returns all customers with at least one 2025 order
Show answer & explanation

Correct answer: E

WHY E: The root cause is a misspelled column name in the WHERE clause (year instead of purchase_year). The correct fix is straightforward — replace 'year' with 'purchase_year' to reference the actual column. The rest of the query logic (GROUP BY + HAVING COUNT(*) >= 1) is correct. WHY NOT A: SQL does not support AS aliases inside WHERE clauses to rename column references; this syntax is invalid. WHY NOT C: Moving the column filter to HAVING is technically possible but non-standard, less efficient (filters all rows through GROUP BY before applying the year condition), and doesn't fix the root cause of referencing the wrong column name. WHY NOT D: The table has one fixed schema; CASE WHEN to handle different column names in different partitions is not valid for this scenario and would not resolve the misspelling. WHY NOT B: A table alias qualifies the column but does not rename it; o.year still references a non-existent column and would still fail.

2 Analyzing Queries

A team enables liquid clustering on a Delta table with CLUSTER BY (event_date, region). After enabling clustering, they run OPTIMIZE to recluster the data. They later decide to change the clustering keys to just CLUSTER BY (event_date). What happens to the already-clustered data after running ALTER TABLE table_name CLUSTER BY (event_date)?

  1. AAll previously written data files are immediately rewritten to reflect the new single-key clustering layout, because ALTER TABLE CLUSTER BY triggers a synchronous full recompaction of all existing files to match the new key specification
  2. BThe ALTER TABLE command fails with an error because liquid clustering keys cannot be changed on a table that already has clustered data; the table must be dropped and recreated with the new key configuration to change the clustering scheme
  3. CThe key definition updates to CLUSTER BY (event_date), but existing data files keep their old two-key (event_date, region) layout; only new writes and future OPTIMIZE runs use the new key — a full recluster of all data requires OPTIMIZE FULL
  4. DChanging clustering keys removes the clustering metadata but does not change any data files; the table behaves as an unclustered table until the next OPTIMIZE run, at which point all data is automatically rewritten using the new key regardless of how much data there is
  5. EALTER TABLE CLUSTER BY (event_date) is valid only through the Databricks API; changing clustering keys using SQL DDL statements is not supported and requires using the DeltaTable Python API or a Databricks workflow configuration
Show answer & explanation

Correct answer: C

WHY C: Databricks documentation states that when you change clustering keys via ALTER TABLE, subsequent OPTIMIZE and write operations use the new clustering approach, but existing data is NOT rewritten. The old files retain their previous layout. To force all records to be reclustered under the new keys, you must run OPTIMIZE FULL, which is documented as potentially taking hours for large tables. WHY NOT A: ALTER TABLE does not trigger a synchronous full recompaction; it only updates the clustering key metadata. WHY NOT B: Clustering keys can be changed at any time on liquid-clustered tables; this is one of liquid clustering's key advantages over partitioning. WHY NOT D: The table is not treated as unclustered after the key change; future OPTIMIZE runs incrementally recluster only new/modified data unless OPTIMIZE FULL is used. WHY NOT E: ALTER TABLE CLUSTER BY is valid standard SQL DDL syntax in Databricks; no API workaround is required.

3 Analyzing Queries

A data analyst manages a 50 TB Delta table that is currently partitioned by ingestion_date. Analysts frequently filter on customer_region and product_category — two high-cardinality columns that are NOT the partition key — leading to very slow queries. She wants to improve filter performance without rewriting the table format from scratch. Which approach is most appropriate?

  1. AAdd a secondary partition by customer_region and product_category in addition to the existing ingestion_date partition, because Databricks supports multi-column hierarchical partitioning that handles high-cardinality columns as sub-partitions efficiently
  2. BRun OPTIMIZE table_name ZORDER BY (customer_region, product_category) to improve multi-column filter performance; ZORDER is compatible with the existing Hive-style ingestion_date partition and requires no table rewrite
  3. CEnable liquid clustering via ALTER TABLE table_name CLUSTER BY (customer_region, product_category), then run OPTIMIZE to incrementally recluster; liquid clustering enables data skipping without static-partition or ZORDER limitations
  4. DCreate a new materialized view pre-filtered on the most common customer_region and product_category combinations, so analysts can query the materialized view instead of the base table; this avoids the need to change the underlying table layout or run any OPTIMIZE operations
  5. EDelete and recreate the table using CTAS with customer_region and product_category as partition columns and ingest all 50 TB of data again, because liquid clustering and Z-ordering cannot be applied to existing large tables without a full reload operation
Show answer & explanation

Correct answer: B

WHY B: ZORDER is fully compatible with existing Hive-style partitioned tables. Running OPTIMIZE table_name ZORDER BY (customer_region, product_category) improves multi-column filter performance via data co-location within each partition, without changing the partition scheme or rewriting the table. WHY NOT A: Adding high-cardinality columns like customer_region and product_category as Hive sub-partitions creates millions of tiny partitions, which severely degrades rather than improves performance. WHY NOT C: Liquid clustering is incompatible with existing Hive-style partitioned tables; enabling it requires removing the current partition first — effectively a full table rewrite — which the analyst explicitly wants to avoid. WHY NOT D: A materialized view pre-filtered on specific values duplicates storage and does not improve arbitrary filter queries on the base table. WHY NOT E: A full CTAS reload is unnecessary; OPTIMIZE ZORDER can be applied incrementally to the existing table without reloading data.

4 Analyzing Queries

A data analyst notices that after enabling Photon on a jobs cluster, some operations in the job fall back to the standard Spark runtime engine mid-execution. Which of the following BEST explains this behavior?

  1. APhoton cannot run in a mixed cluster environment. When any one task encounters an unsupported operation, Photon disables itself for the entire job, requiring the analyst to restructure the pipeline to avoid those operations entirely
  2. BPhoton selectively accelerates only physical scan and filter operators; all join, aggregate, and sort operations always fall back to the standard Spark engine even when Photon is enabled and those operators are theoretically supported
  3. CPhoton activates only for the first query in a job. All subsequent operations are executed by the standard Spark engine to preserve consistent memory usage across the entire job run, regardless of whether those operations are Photon-supported
  4. DWhen Photon encounters an unsupported operation (such as a Python UDF, RDD transformation, or Dataset API call), it automatically switches to the standard Spark runtime for that operation and all remaining ones, letting the job complete without error
  5. EPhoton switches to standard Spark execution only for queries returning more than one million rows, because Photon's vectorized engine is optimized for narrow result sets and degrades in performance for large output materializations
Show answer & explanation

Correct answer: D

WHY D: According to Databricks documentation, if a workload hits an unsupported operation, the compute resource automatically switches to the standard runtime engine for the remainder of the workload — the job still completes, it just won't benefit from Photon acceleration for that portion. WHY NOT A: Photon does not disable itself globally across the job; only the specific unsupported operation and subsequent operators fall back. WHY NOT C: Photon is not limited to the first query; it accelerates any supported operation throughout the job. WHY NOT B: Databricks explicitly lists Hash Aggregate, Hash Join, Sort, Filter, and other operators as Photon-supported; they are not forced to fall back. WHY NOT E: Photon's fallback threshold is the nature of the operation (e.g., UDF vs. SQL), not the row count of the output.

5 Analyzing Queries

A data analyst clicks on a query in Query History and notices that the Query Profile panel shows 'Query profile is not available' for that execution. What is the MOST likely reason for this message?

  1. AThe query ran for less than five seconds, and Databricks generates a Query Profile once a query passes a minimum duration threshold, which avoids profiling overhead on short-running operations
  2. BThe query finished successfully within the normal time range, and a Query Profile is retained for queries that produced an error or exceeded a configurable slow-query threshold on the warehouse
  3. CThe analyst lacks CAN MANAGE permission on the SQL warehouse that ran the query, and Query Profiles are shown to workspace administrators and to users holding warehouse-level CAN MANAGE access
  4. DThe query was written in Python (PySpark) rather than SQL, and the Query Profile captures statements submitted through the SQL editor in SQL syntax, leaving out notebook-based Python executions
  5. EThe query result was served from the query cache instead of being executed on the SQL warehouse, and a query answered from the cache produces no execution metrics from which to build a profile
Show answer & explanation

Correct answer: E

WHY E: The documentation states that a Query Profile is not available for queries that run from the query cache. To bypass the cache and get a fresh profile, the analyst makes a trivial change to the query, such as altering or removing the LIMIT clause. WHY NOT A: There is no documented minimum duration for generating a Query Profile; short queries produce profiles when they are actually executed. WHY NOT C: Viewing a profile requires being the query owner or having CAN MONITOR (not CAN MANAGE) on the warehouse, and a missing permission is not what this message indicates. WHY NOT D: Query Profiles are also accessible from notebooks attached to SQL warehouses or serverless compute, not just from the SQL editor. WHY NOT B: Query Profiles are generated for successfully completed queries; the cache, not the success or failure status, is what suppresses the profile.

6 Analyzing Queries

A data analyst in a Databricks SQL Editor frequently reruns the same complex query during iterative development. She notices that after the first run, subsequent reruns return almost instantly. Which Databricks mechanism is responsible for this behavior, and what is a potential drawback the analyst should be aware of?

  1. ALiquid Clustering — co-locates related rows in fewer files, which shortens scans on reruns of the same query; the drawback is that clustering is applied asynchronously and newly written rows stay unclustered until OPTIMIZE runs
  2. BQuery result caching — an identical rerun is served from the cached result instead of executing again on the warehouse; the drawback is that no Query Profile is generated for cache-served runs, which can obscure performance diagnostics
  3. CDisk cache (Delta cache) — keeps decompressed copies of the table files on local NVMe storage for fast repeated scans; the drawback is that the cache is per-node and is lost on restart, with the next cold run paying for full scans again
  4. DPhoton vectorized execution — the first run warms the Photon JIT compiler and later runs of the query reuse the compiled code; the drawback is that the warmup takes about three executions of the query to reach full acceleration
  5. EMaterialized view auto-refresh — reruns reuse pre-computed results from a materialized view that covers the query pattern; the drawback is that the view needs a schedule or TRIGGER ON UPDATE and can serve stale rows between refreshes
Show answer & explanation

Correct answer: B

WHY B: Databricks SQL has a query result cache. When an identical query is run again, the cached result is returned almost instantly without compute execution. The documented drawback is that no Query Profile is available for cache-served queries; to get a fresh profile, the analyst has to make a trivial change to the query. WHY NOT A: Liquid Clustering speeds up file pruning at scan time but does not return instant results; the query is still executed on each rerun. WHY NOT C: Disk caching warms up scan I/O but does not return pre-computed result sets; query execution still runs on every rerun. WHY NOT D: Photon does not have a JIT warmup phase that spans several executions of a query in the way described. WHY NOT E: Materialized views are separate objects that have to be created explicitly; they do not intercept and cache ad hoc queries run in the SQL Editor.

7 Analyzing Queries

A data analyst wants to compare the row counts in a Delta table as they existed one week ago against the counts today to detect unexpected data changes. Which approach correctly retrieves historical data for comparison?

  1. ARun RESTORE TABLE table_name TO TIMESTAMP AS OF date_sub(current_date(), 7) to open a read-only session snapshot from one week ago, count its rows, and compare; the current table version stays in place
  2. BRun DESCRIBE HISTORY table_name and read the operationMetrics column of the version from seven days ago, which records the total row count of the table at each version, then compare with today
  3. CCopy the table to a staging Delta table with CLONE, then count rows in the original and in the clone; time travel clauses are accepted by CLONE and RESTORE but not inline in a SELECT statement
  4. DRun SELECT COUNT(*) FROM table_name TIMESTAMP AS OF date_sub(current_date(), 7) to read the snapshot from seven days ago, then compare it with the current count; the query leaves the table unchanged
  5. ERun VACUUM table_name RETAIN 168 HOURS to expose the version from seven days ago, then count the rows of that exposed snapshot directly at its cloud storage path and compare with the current count
Show answer & explanation

Correct answer: D

WHY D: Delta Lake time travel supports inline SELECT queries with TIMESTAMP AS OF or VERSION AS OF, so the analyst reads historical data in place without altering the table. Running a historical count and a current count and comparing them is the correct, non-destructive approach. WHY NOT A: RESTORE does not open a temporary snapshot: it rolls the table itself back to the historical version, replacing the current state. That is destructive and wrong for a read-only comparison. WHY NOT C: CLONE creates a physical copy, which is unnecessary; time travel SELECT queries work natively inline and do not require a clone. WHY NOT B: operationMetrics records per-operation metadata such as numOutputRows, not a total table row count at each version that could be used for a historical comparison. WHY NOT E: VACUUM removes old data files and does not expose historical versions; querying a snapshot at a storage path after VACUUM in this way is not valid.

8 Analyzing Queries

A data engineering team wants to speed up large-scale SQL aggregations and Delta table MERGE operations without changing any code. They are evaluating whether to enable Photon. Which statement MOST accurately describes Photon's capabilities and limitations?

  1. APhoton is a vectorized engine that accelerates SQL, DataFrame, Python UDF, RDD and Dataset API workloads alike, so it serves as a drop-in replacement for the standard Spark runtime across workload types
  2. BPhoton is an open-source query engine that Databricks integrates as an option; it is incompatible with Apache Spark APIs, so existing PySpark and Spark SQL code has to be rewritten before it runs
  3. CPhoton is a vectorized engine that can accelerate SQL and Delta write workloads, but it brings a benefit to queries that run longer than 30 seconds, which rules out short dashboard refresh queries
  4. DPhoton is a vectorized engine offered on serverless SQL warehouses and not on interactive or jobs clusters, which limits its use to SQL editor and dashboard workloads rather than MERGE pipelines
  5. EPhoton is a Databricks-native vectorized engine that accelerates SQL and DataFrame workloads and Delta writes such as MERGE INTO and UPDATE, but it does not support Python UDFs, RDD APIs or Dataset APIs
Show answer & explanation

Correct answer: E

WHY E: Photon is Databricks' native vectorized engine. It accelerates SQL and DataFrame operations on Delta and Parquet, replaces sort-merge joins with hash-joins, improves Delta write performance (MERGE, UPDATE, DELETE, INSERT, CTAS), and is compatible with Apache Spark APIs, so no code changes are needed. It does not support Python UDFs, RDD APIs, or Dataset APIs. WHY NOT A: Photon does not support UDFs, RDDs, or the Dataset API; it falls back to the standard engine for those operations, so it is not a universal replacement for the Spark runtime. WHY NOT C: The documented threshold is two seconds, not 30: Photon does not impact queries that already run in under two seconds. WHY NOT D: Photon runs by default on SQL warehouses and serverless compute, and it can also be enabled on all-purpose and jobs clusters. WHY NOT B: Photon is a Databricks-proprietary engine that is fully compatible with Apache Spark APIs and requires no code rewrites.

Take the full Data Analyst practice test →