Analyzing Queries: Databricks Data Analyst Associate Practice Questions

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. BReplace '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
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. EAdd 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

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

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

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. BWhen 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
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. DPhoton 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
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

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 only generates Query Profiles for queries that exceed a minimum duration threshold to avoid profiling overhead for short-running operations
2. BThe query result was served from the query cache rather than being executed by the SQL warehouse compute engine; queries satisfied by the cache do not generate an execution DAG, so no profile is available
3. CThe analyst does not have CAN MANAGE permission on the SQL warehouse that ran the query, and Query Profiles are only available to workspace administrators or users with warehouse-level CAN MANAGE access
4. DThe query was written in Python (PySpark) rather than SQL, and the Query Profile feature only captures profiling data for queries submitted through the SQL editor using pure SQL syntax, excluding all notebook-based Python executions
5. EThe Query Profile is only available for queries that produced an error or exceeded a configurable slow-query threshold, and since this query completed successfully within the normal time range, no profile was retained

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 — automatically co-locates related rows in fewer files, reducing scan times on reruns; the drawback is that clustering is asynchronous and may not apply to newly written rows until an OPTIMIZE job runs
2. BQuery result caching — identical subsequent queries return the cached result without re-executing on the warehouse; the drawback is that no Query Profile is generated for cache-served queries, which can obscure performance diagnostics
3. CDelta table caching (disk cache) — caches decompressed Delta table files on local NVMe storage for fast repeated scans; the drawback is that this cache is per-node and does not survive cluster restarts, meaning cold-start reruns still require full scans
4. DPhoton vectorized execution — the first run warms the Photon JIT compiler, making subsequent runs much faster due to compiled code reuse; the drawback is that Photon JIT warmup requires at least three query executions before full acceleration is achieved
5. EMaterialized view auto-refresh — reuses pre-computed results from a materialized view that covers the query pattern; the drawback is that materialized views require manual scheduling or TRIGGER ON UPDATE configuration and do not refresh automatically on every data change

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 reset the table to its state from one week ago and then query it, which permanently replaces the current table version with the historical snapshot
2. BUse SELECT COUNT(*) FROM table_name TIMESTAMP AS OF date_sub(current_date(), 7) to time-travel to the snapshot from seven days ago, then compare it with the current count — Delta Lake time travel is non-destructive and requires no table changes
3. CExport the table to a separate staging Delta table via CLONE, then query both the original and the clone to compare counts, because time travel queries cannot be run inline in SELECT statements and require a physical snapshot copy
4. DRun DESCRIBE HISTORY table_name and manually look up the row count in the operationMetrics column to find the historical count from seven days ago, since operationMetrics records the total row count at each version for comparison
5. EUse VACUUM RETAIN 168 HOURS USING HISTORY to expose the historical version from seven days ago and then query the exposed snapshot file directly at its cloud storage path with SELECT * FROM delta.'/storage/path/@timestamp'

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 accelerates all workloads including Python UDFs, RDD-based transformations, and Dataset API operations, making it a universal replacement for the standard Spark runtime regardless of workload type
2. BPhoton is a Databricks-native vectorized engine that accelerates SQL and DataFrame workloads on Delta and Parquet, speeds up Delta writes (MERGE INTO, UPDATE) and replaces sort-merge joins with hash-joins, but does not support Python UDFs, RDD APIs, or Dataset APIs
3. CPhoton can accelerate any workload but only provides benefit for queries that run longer than 30 seconds, making it unsuitable for short-running operational queries or latency-sensitive dashboard refreshes
4. DPhoton is available exclusively on serverless SQL warehouses and cannot be enabled on all-purpose or jobs compute clusters, limiting its use to SQL editor and dashboard workloads only
5. EPhoton is a third-party open-source query engine that Databricks integrates optionally; because it is incompatible with Apache Spark APIs, existing PySpark and Spark SQL code must be rewritten before Photon can execute those workloads