LFC Databricks SQL & Warehousing 3 — Questions and Answers
Question 1: Which Databricks SQL function returns the current timestamp in the session's local timezone?
- NOW()
- CURRENT_TIMESTAMP()
- LOCALTIMESTAMP()
- Both NOW() and CURRENT_TIMESTAMP() (Correct answer)
Correct answer: Both NOW() and CURRENT_TIMESTAMP()
In Databricks SQL both NOW() and CURRENT_TIMESTAMP() return the current timestamp with the session timezone applied.
Question 2: A data engineer wants to read only the new rows added to a Delta table since the last pipeline run. Which Databricks SQL / Delta Lake feature best supports this pattern?
- MERGE INTO with a timestamp filter
- CHANGE DATA FEED (CDF) with table_changes() (Correct answer)
- SHALLOW CLONE of the target table
- COPY INTO with VALIDATE mode
Correct answer: CHANGE DATA FEED (CDF) with table_changes()
Change Data Feed exposes a table_changes() function that returns only inserted, updated, or deleted rows since a given version or timestamp.
Question 3: In Databricks SQL, what does the QUALIFY clause do?
- Filters rows based on a condition applied after window functions are evaluated (Correct answer)
- Restricts which columns are returned by a SELECT statement
- Defines row-level security predicates on a table
- Specifies the minimum partition value for pruning
Correct answer: Filters rows based on a condition applied after window functions are evaluated
QUALIFY filters the result set of window functions similarly to how HAVING filters aggregations, without needing a subquery.
Question 4: A SQL Warehouse is configured with min clusters = 1 and max clusters = 5. Under what condition will the warehouse scale UP to additional clusters?
- When a single query takes longer than 60 seconds
- When the query queue length reaches a platform-defined threshold (Correct answer)
- When CPU utilization on a cluster exceeds 80%
- When a new user logs into Databricks SQL
Correct answer: When the query queue length reaches a platform-defined threshold
Databricks auto-scales SQL Warehouses by adding clusters when the query queue grows beyond an internal threshold, not based on CPU.
Question 5: Which statement correctly describes a Databricks SQL Dashboard compared to a Notebook?
- Dashboards support interactive widget parameters; notebooks do not
- Dashboards are read-only presentations of visualizations; notebooks allow code execution (Correct answer)
- Dashboards use a different SQL dialect than notebooks
- Dashboards require a dedicated SQL Warehouse; notebooks can only use job clusters
Correct answer: Dashboards are read-only presentations of visualizations; notebooks allow code execution
Databricks SQL Dashboards are designed as read-only visual presentations of query results, while notebooks are interactive coding environments.
Question 6: What is the purpose of the OPTIMIZE command in Databricks SQL when run on a Delta Lake table?
- Rebuilds all indexes on the table
- Compacts small files into larger files to improve read performance (Correct answer)
- Removes all duplicate rows from the table
- Updates table statistics used by the query optimizer
Correct answer: Compacts small files into larger files to improve read performance
OPTIMIZE compacts small Parquet files in a Delta table into larger, more efficient files, reducing the overhead of small file reads.
Question 7: In Databricks SQL, which privilege must be granted on a SQL Warehouse to allow a user to execute queries against it?
- USE WAREHOUSE
- CAN USE (Correct answer)
- EXECUTE
- CAN RUN
Correct answer: CAN USE
The CAN USE privilege on a SQL Warehouse allows users to connect and run queries against that warehouse.
LFC Databricks SQL & Warehousing 3
Which Databricks SQL function returns the current timestamp in the session's local timezone?