LFC Databricks SQL & Warehousing 2 — Questions and Answers
Question 1: Which SQL Warehouse type in Databricks is best suited for ad-hoc queries by business analysts who need fast startup with no idle cost?
- Serverless SQL Warehouse (Correct answer)
- Classic SQL Warehouse
- Pro SQL Warehouse
- High-Concurrency Cluster
Correct answer: Serverless SQL Warehouse
Serverless SQL Warehouses start in seconds and have no idle cost because compute is fully managed by Databricks.
Question 2: In Databricks SQL, what does the CLUSTER BY clause do when used in a CREATE TABLE statement with liquid clustering enabled?
- Sorts data at write time by the specified columns
- Defines clustering keys for incremental file reorganization without full rewrites (Correct answer)
- Partitions the table into equal-sized buckets by the given column
- Creates a secondary index on the specified columns
Correct answer: Defines clustering keys for incremental file reorganization without full rewrites
Liquid clustering uses CLUSTER BY to define keys for incremental, background reorganization of data files, replacing traditional partitioning.
Question 3: A Databricks SQL query uses PIVOT to transform rows into columns. Which SQL keyword is required within the PIVOT clause to define the aggregation?
- GROUP BY
- AGGREGATE
- FOR
- An aggregate function such as SUM or AVG (Correct answer)
Correct answer: An aggregate function such as SUM or AVG
The PIVOT clause requires an aggregate function (e.g., SUM, COUNT, AVG) to compute the values for each pivoted column.
Question 4: What is the default behavior of Databricks SQL when a query references a table that does not exist in the current catalog?
- It searches all available catalogs automatically
- It raises a TABLE_OR_VIEW_NOT_FOUND error (Correct answer)
- It creates an empty table with that name
- It falls back to the hive_metastore catalog
Correct answer: It raises a TABLE_OR_VIEW_NOT_FOUND error
Databricks SQL raises TABLE_OR_VIEW_NOT_FOUND if the table is not found in the current catalog and schema.
Question 5: Which feature of Databricks SQL Warehouses allows multiple queries from different users to share a single running warehouse without performance degradation?
- Auto-termination
- Query federation
- Query queuing with auto-scaling (Correct answer)
- Photon vectorized engine
Correct answer: Query queuing with auto-scaling
SQL Warehouses use query queuing combined with auto-scaling clusters to handle concurrent users efficiently.
Question 6: In Databricks SQL, which system table or view would you query to monitor the historical execution time of past queries on a SQL Warehouse?
- system.runtime.query_history
- information_schema.query_log
- system.query.history (Correct answer)
- hive_metastore.sys.queries
Correct answer: system.query.history
system.query.history is the system table that records historical query execution metadata for SQL Warehouses.
Question 7: When creating a Databricks SQL alert, what condition triggers the alert notification?
- When the scheduled query's runtime exceeds a threshold
- When a query result value meets a defined comparison condition (Correct answer)
- When a user edits the underlying dashboard
- When the SQL Warehouse is terminated unexpectedly
Correct answer: When a query result value meets a defined comparison condition
Databricks SQL alerts fire when a scheduled query returns a result that satisfies the configured value comparison (e.g., count > 100).
LFC Databricks SQL & Warehousing 2
Which SQL Warehouse type in Databricks is best suited for ad-hoc queries by business analysts who need fast startup with no idle cost?