Be able to write correct Spark SQL SELECT queries with WHERE, GROUP BY, and HAVING, create DataFrames from tables through the catalog, and tune Delta writes and driver memory. The key is knowing HAVING filters aggregates while WHERE filters rows before grouping.
Start practicing
Using Spark SQL — choose a session length
Free · No account required
Domain overview
This domain covers querying and transforming data with Spark SQL on Databricks: SELECT statements, aggregations, joins, CTEs, and window functions, plus creating DataFrames from tables and tuning reads/writes. Questions test clause syntax and semantics, DataFrame creation paths, and configuration choices that control file sizes and driver memory during large result collection.
Exam objectives
Using GROUP BY with HAVING to filter on aggregated values rather than WHERE
Creating DataFrames from existing tables via spark.sql, spark.table, and spark.read.table
Reducing small-file output when writing to Delta Lake with partitioning and compaction settings
Adjusting Spark driver memory and result-size configs to avoid Driver OOM on collect
Using WHERE instead of HAVING to filter aggregated results, which fails because WHERE runs before aggregation
Assuming spark.sql and spark.table behave identically for all catalog and temp-view resolution cases
Collecting full result sets to the driver without limiting rows or raising driver memory, triggering OOM
Click any question to see the full explanation and answer options, or start a focused practice session above.
You are processing a large dataset in Spark SQL and need to ensure that small files are avoided when writing data to Delta Lake. Which approach effectively minimizes small file generation during write operations?
2Which SQL command is used to view the history of operations performed on a Delta table, including timestamps and operation types?
3When performing a join between a very small table and a massive table, which Spark SQL optimization technique should be applied to prevent a full shuffle?
4Which clause is used in a SELECT statement to filter the results based on aggregated values?
5Which of the following describes the behavior of a Delta table when a 'DELETE' operation is performed?
6Which TWO of the following are benefits of using Delta Lake over standard Parquet files in Spark SQL?
7When working with Delta Lake tables in Databricks, which command should you use to optimize the physical layout of files to improve query performance?
8Which TWO of the following are true concerning Spark SQL's handling of NULL values?
9What is the primary purpose of the 'Cache' command in Spark SQL?
10Which of the following is the most efficient way to convert a Spark DataFrame into a format suitable for low-latency SQL queries in Databricks?
11Which of the following describes the behavior of a 'Left Outer Join' in Spark SQL?
12When executing a Spark SQL query, what does the Catalyst optimizer perform during the 'Analysis' phase?
13Which THREE of the following are valid ways to monitor or debug Spark SQL query performance in Databricks?
14When performing a 'Z-ORDER' operation on a Delta table, how does it improve query performance?
15You are optimizing a Spark SQL query that performs a large join between a 10GB table and a 5MB lookup table. To ensure performance efficiency, which command should you use to hint to the optimizer?
16Which of the following Spark SQL configuration settings should be adjusted to prevent the 'Driver OOM' error when collecting massive amounts of query results to the driver node?
17In Spark SQL, what is the primary difference between a temporary view and a global temporary view?
18Which clause is used in a Spark SQL query to limit the number of rows returned by a query, and in which logical order is it executed?
19What is the purpose of the 'ANALYZE TABLE' command in Databricks?
20What is the result of applying the COALESCE function in Spark SQL when multiple arguments are provided?
21Which of the following describes the behavior of a 'Broadcast Hash Join' in Spark SQL?
22You are querying a large Delta table partitioned by 'event_date'. You need to calculate the daily count of unique users, but the query is running slowly due to data skew on high-traffic dates. Which SQL approach effectively mitigates this skew during the aggregation?
23Which SQL function is used to create a temporary view that persists only for the duration of the current Spark session?
24You need to combine two datasets in Spark SQL, retaining all records from the left table and matching records from the right table, while filling unmatched right columns with null values. Which join type should you use?
25Which SQL function is used to concatenate strings while allowing you to specify a custom separator, handling null values by ignoring them?
26Which THREE of the following are valid ways to create a DataFrame from an existing table in Spark SQL?
27When joining two large tables in Spark SQL, which join type helps avoid expensive shuffles by loading a small table into memory on all executor nodes?
28Which command is used to display the logical and physical execution plans for a given Spark SQL query?
29A data engineer must produce a report that shows each department's total salary, but only for departments where the total salary exceeds 500,000. The source DataFrame is created from a Delta table with columns department and salary. Which Spark SQL query correctly returns the desired result?
30A data analyst needs to run a Spark SQL query that returns the top 5 highest-paid employees from a table named employees, ordered by salary descending. Which query correctly returns exactly five rows?
31A developer is building a Spark SQL pipeline in a Databricks notebook. They need to persist an intermediate DataFrame, built from a transformation of a Delta table, as a physical table in the current database so other notebooks in the same cluster can query it. They also want the table metadata to be managed by the metastore and the data to reside in the default warehouse directory. Which Spark SQL statement should they use?
32An analyst has a Spark SQL DataFrame named events with a string column event_time in the format 'yyyy-MM-dd HH:mm:ss'. They want to add a new column event_date containing only the date portion, keeping the original column intact. Which expression should they use in a select statement?
33A data engineer is building a Spark SQL pipeline that must return the top 3 highest-paid employees within each department from a Delta table named `employees` with columns `dept`, `name`, and `salary`. The engineer wants a single query that produces one row per qualifying employee, ranked by salary descending within each department, without collapsing rows. Which approach should be used?
34You are using Spark SQL to analyze a Delta table named transactions that is partitioned by a column region. You need to run a query that filters on region and also on a non-partitioned column amount. The table has statistics collected on region and amount. Which of the following best describes how Spark SQL will optimize the query?
35A data engineer runs the following statement in a Databricks notebook: CREATE OR REPLACE TEMP VIEW high_value_customers AS SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id HAVING SUM(amount) > 10000. Later, the same engineer opens a new notebook attached to the same cluster and tries to run SELECT * FROM high_value_customers. What will happen?
36A data engineer has two Spark SQL DataFrames: customers (customer_id, name) and orders (order_id, customer_id, amount). They want to retrieve every customer along with their orders, but they also want to include customers who have placed no orders, showing null for the order columns. Which operation should they use?
37A developer runs `spark.sql("SELECT * FROM sales")` and receives an error stating the table or view cannot be found, even though a Parquet directory exists at `dbfs:/mnt/raw/sales/`. The developer wants to query that Parquet data using Spark SQL without moving or copying the files. Which action should the developer take?
38A data engineer is working with a Spark SQL DataFrame in Databricks that has a column named event_time stored as a string in the format 'yyyy-MM-dd HH:mm:ss'. They need to filter rows where event_time falls within the last 7 days relative to the current timestamp. Which Spark SQL expression correctly achieves this?
39A data engineer is writing a Spark SQL query that joins a `transactions` table to a `customers` table on `customer_id`. The engineer wants to ensure that rows from `transactions` with no matching customer are still returned, with nulls for customer columns, and also wants to exclude duplicate rows that arise from the join. Which TWO clauses should the engineer include? (Choose two.)
40A developer is using Spark SQL to join two DataFrames: a large fact table 'orders' and a smaller dimension table 'customers'. The join key is customer_id. The developer notices that the join is causing a shuffle and wants to avoid it. Which Spark SQL technique should be used to eliminate the shuffle for this join?
41A developer is using Spark SQL to analyze a DataFrame that contains a column named tags, which holds an array of strings for each row. The developer needs to filter rows where the array contains the string 'urgent' and also produce a new column with the number of elements in the array. Which TWO Spark SQL expressions should be used in the query? (Choose two.)
42A developer needs to add a computed column `discounted_price` equal to `price * 0.9` to an existing Delta table `products` and persist the change so all future queries see the new column. The table already contains data. Which statement should the developer run?
43You are using Spark SQL to join two large Delta tables, orders and customers, on a common column customer_id. The orders table is partitioned by order_date, and the customers table is not partitioned. You need to ensure the join is efficient and minimizes shuffling. Which TWO actions should you take? (Choose two.)
44An analyst notices that a Spark SQL query filtering a Delta table with `WHERE order_date = '2024-03-15'` scans far more data than expected, even though the table is partitioned by `order_date`. The partition column was loaded as a string in `yyyy-MM-dd` format. Which explanation best accounts for the excessive scan?
45A developer writes a Spark SQL query that groups orders by region and computes the total revenue per region, but also needs to return the number of distinct customers per region in the same result set. Which TWO expressions correctly compute the distinct customer count per region in a single GROUP BY region query? (Choose two.)
46A developer is writing a PySpark script that must run a SQL statement against DataFrames already registered as temporary views named orders and returns. The developer wants the query to use Spark SQL syntax while returning a DataFrame that can be further transformed with the DataFrame API. Which call accomplishes this?
47A data scientist is using Spark SQL to compute a running total of sales amounts for each customer, ordered by transaction date. The query must return, for each row, the sum of all previous sales for that customer up to and including the current row. Which window specification should be used?
48A developer has a Delta table events with a high-cardinality column user_id and a low-cardinality column country. A query filters on country = 'US' and also on user_id IN (...). The developer runs EXPLAIN and sees a full scan of all files. Which statement about data skipping and the Delta table's statistics correctly explains why the filter on country is not skipping files?
49A developer is working with a Spark SQL DataFrame that contains a column 'tags' which is an array of strings. The developer needs to filter rows where the array contains the string 'spark' and also transform the array to uppercase. Which TWO Spark SQL functions should be used to achieve these requirements? (Choose two.)
50A developer is writing a Spark SQL query that must handle null values in a column named discount. The requirement is to replace null discounts with 0.0, but also to replace any negative discount values with 0.0, leaving positive values unchanged. Which expression should be used?
51A developer is using Spark SQL to process a streaming DataFrame from a Kafka source. They need to perform a stateful operation that maintains state across micro-batches to count occurrences of each key over a sliding window of 10 minutes, sliding every 5 minutes. Which Spark SQL operation should they use?
52A developer has a Spark SQL DataFrame `df` with an array column named `scores` containing integers. They need to create a new column `passing` that is true only when every element in `scores` is greater than or equal to 70. Which Spark SQL higher-order function should they use?
Be able to write correct Spark SQL SELECT queries with WHERE, GROUP BY, and HAVING, create DataFrames from tables through the catalog, and tune Delta writes and driver memory. The key is knowing HAVING filters aggregates while WHERE filters rows before grouping.
The Courseiva Databricks-Spark-Assoc question bank contains 52 questions in the Using Spark SQL domain. Click any question to see the full explanation and answer breakdown.
Start with a 10-question focused session to identify your baseline accuracy in this domain. Read every explanation — even for questions you answer correctly — to understand the reasoning. Once you score consistently above 80%, move to a 20–30 question session to confirm depth before moving to the next domain.
Yes — the session launcher on this page draws questions exclusively from the Using Spark SQL domain. Choose 10, 20, 30, or 50 questions for a focused session, or click individual questions to review them one by one.
Save your results, see per-domain analytics, and get readiness scores — free, for every certification.
Sign Up FreeFree forever · Every certification included