Databricks-Spark-Assoc Using Spark SQL Practice Test 5 — 15 Questions

Question 1

What is the result of applying the COALESCE function in Spark SQL when multiple arguments are provided?

Accepted Answer

It returns the first non-null argument.. The COALESCE function is a standard SQL tool for handling null values. It returns the first non-null argument from a list. This is extremely useful in data engineering for providing default values or merging columns that have sparse data. Understanding how to use it helps in writing cleaner SQL that handles missing values without needing complex CASE WHEN statements, which improves code readability and maintainability.

Answer

It returns the sum of all arguments.

Answer

It returns the last non-null argument.

Answer

It throws an error if any argument is null.

Question 2

Which of the following describes the behavior of a 'Broadcast Hash Join' in Spark SQL?

Accepted Answer

The smaller table is sent to all executors, avoiding a shuffle of the large table.. A Broadcast Hash Join is a highly efficient join strategy where the smaller table is sent to all worker nodes, allowing the join to occur locally in memory. This eliminates data shuffles, which are usually the slowest part of a distributed join. Understanding when the optimizer chooses this—and when to force it—is essential for optimizing SQL performance in Databricks environments where network bandwidth is a common bottleneck.

Answer

The larger table is shuffled to match the smaller table's partitions.

Answer

Both tables are shuffled to a common partition based on the join key.

Answer

The join is performed entirely on the driver node.

Question 3

You 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?

Accepted Answer

Add a new column with a random integer suffix to the grouping key, aggregate by the salted key, then aggregate again to remove the suffix.. Salting the grouping key by appending a random integer suffix distributes the skewed keys across multiple partitions. By creating a temporary column that combines the original key with a random value, you ensure that the aggregation logic is parallelized across the cluster. This technique is essential in Spark SQL when a specific key value results in a task significantly larger than others, preventing straggler tasks from bottlenecking the entire job.

Answer

Increase the 'spark.sql.shuffle.partitions' configuration to a significantly higher number.

Answer

Use the 'DISTINCT' keyword on all columns in the SELECT statement.

Answer

Convert the table to a broadcast join using a dummy table.

Question 4

Which SQL function is used to create a temporary view that persists only for the duration of the current Spark session?

Accepted Answer

CREATE TEMPORARY VIEW. The CREATE TEMPORARY VIEW statement is the standard method for registering a DataFrame or table as a temporary view. These views are scoped to the SparkSession, making them invisible to other sessions or users. This is critical for modularizing complex SQL pipelines where intermediate results need to be referenced by name without polluting the global metastore or requiring permanent persistence in underlying storage.

Answer

CREATE GLOBAL TABLE

Answer

CREATE PERSISTENT VIEW

Answer

CREATE SESSION TABLE

Question 5

You 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?

Accepted Answer

LEFT OUTER JOIN. A left outer join ensures every single record from the left dataset appears in the final result set, regardless of whether a matching key exists in the right dataset. Missing matches on the right side are populated with null values, maintaining structural integrity for downstream analysis.

Answer

RIGHT OUTER JOIN

Answer

FULL OUTER JOIN

Answer

INNER JOIN

Question 6

Which SQL function is used to concatenate strings while allowing you to specify a custom separator, handling null values by ignoring them?

Accepted Answer

concat_ws(). The 'concat_ws' function stands for 'concatenate with separator'. It is specifically designed to take a delimiter as the first argument, followed by a variable number of strings. A key feature of 'concat_ws' is its ability to skip NULL values automatically, preventing the entire result from becoming NULL—a common issue with the standard 'concat' function. This makes it ideal for building CSV-like strings or concatenating fields that may contain missing data.

Answer

concat()

Answer

array_join()

Answer

format_string()

Question 7

Which THREE of the following are valid ways to create a DataFrame from an existing table in Spark SQL?

Accepted Answer

spark.table('table_name'). Spark provides multiple entry points to access table data. You can use the 'spark.table()' method for direct catalog access, 'spark.sql()' to execute a SELECT statement, or the 'spark.read.table()' method. All three provide the same underlying Dataset/DataFrame API, allowing for flexible programmatic interaction with metadata managed by the Hive Metastore or Unity Catalog, ensuring consistency across different development styles and API usage patterns in Databricks.

Answer

spark.open('table_name')

Answer

spark.from_table('table_name')

Question 8

When 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?

Accepted Answer

Broadcast Hash Join. A Broadcast Hash Join is a highly efficient join strategy in Spark SQL. By marking the smaller table as a broadcast candidate, Spark sends a complete copy of that table to every executor node. This allows the join to be performed locally at each node, eliminating the need to shuffle the large table across the network, which is often the primary bottleneck in large-scale distributed join operations.

Answer

Shuffle Hash Join

Answer

Sort Merge Join

Answer

Cartesian Product Join

Question 9

Which command is used to display the logical and physical execution plans for a given Spark SQL query?

Accepted Answer

EXPLAIN SELECT * FROM table. The 'EXPLAIN' command is the essential tool for inspecting the Catalyst query optimizer's work. It provides a breakdown of the logical plan (the raw representation of the query), the analyzed plan, the optimized plan, and the physical plan (how Spark will actually execute the tasks). Understanding this output is crucial for performance tuning, as it reveals how Spark handles joins, filters, and projections before the query actually runs on the distributed cluster nodes.

Answer

SHOW PLAN

Answer

DESCRIBE QUERY

Answer

DEBUG SELECT * FROM table

Question 10

A 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?

Accepted Answer

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING SUM(salary) > 500000. Filtering on an aggregate result requires the HAVING clause, which executes after GROUP BY. The query that groups by department, sums salary, and then applies HAVING with the aggregate condition returns only departments whose total salary exceeds 500,000, exactly matching the requirement.

Answer

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department WHERE total_salary > 500000

Answer

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department WHERE SUM(salary) > 500000

Answer

SELECT department, SUM(salary) AS total_salary FROM employees WHERE SUM(salary) > 500000 GROUP BY department

Question 11

A 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?

Accepted Answer

SELECT * FROM employees ORDER BY salary DESC LIMIT 5. To get the top 5 highest-paid employees, the query must sort by salary descending and then apply LIMIT 5. Spark SQL supports ORDER BY followed by LIMIT, which returns the first five rows after sorting, exactly matching the requirement.

Answer

SELECT TOP 5 * FROM employees ORDER BY salary DESC

Answer

SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY

Answer

SELECT * FROM employees LIMIT 5 ORDER BY salary DESC

Question 12

A 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?

Accepted Answer

CREATE OR REPLACE TABLE intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'. Creating an OR REPLACE TABLE with a SELECT statement materializes the transformed data as a managed Delta table, registers it in the metastore, and stores it in the default warehouse location. This matches the need for a persistent, cross-session-accessible physical table, unlike temporary views, global temporary views, or logical views, which do not persist materialized data.

Answer

CREATE OR REPLACE GLOBAL TEMP VIEW intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'

Answer

CREATE OR REPLACE VIEW intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'

Answer

CREATE OR REPLACE TEMP VIEW intermediate_sales AS SELECT * FROM sales WHERE region = 'EU'

Question 13

An 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?

Accepted Answer

to_date(event_time, 'yyyy-MM-dd HH:mm:ss'). Using to_date with the matching pattern correctly parses the timestamp string and returns a date-typed column containing only the date component. The other functions either return strings, operate on date types rather than strings, or rely on fragile character extraction, so they do not produce a proper date column as required.

Answer

date_format(event_time, 'yyyy-MM-dd')

Answer

trunc(event_time, 'MM')

Answer

substring(event_time, 1, 10)

Question 14

A 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?

Accepted Answer

Use the `rank()` window function partitioned by `dept` and ordered by `salary DESC`, then filter on the rank column.. Window functions are the correct tool for top-N-per-group problems because they compute a value across a set of rows related to the current row while preserving all rows. Partitioning by department and ordering by salary descending yields a per-department rank, and filtering that rank to three returns exactly the desired rows in a single query.

Answer

Use `GROUP BY dept` with the `MAX(salary)` aggregate and a `HAVING` clause limiting results to three rows.

Answer

Use `ORDER BY salary DESC` on the full table and apply `LIMIT 3`.

Answer

Use `DISTINCT` on `dept` and `salary`, then sort the result with `SORT BY salary DESC`.

Question 15

You 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?

Accepted Answer

Spark SQL will use partition pruning based on the region filter and then apply data skipping on amount using the collected statistics to skip files where amount does not match the filter.. Spark SQL uses partition pruning for the region filter and Delta Lake's data skipping for the amount filter, thanks to collected statistics. This minimizes the amount of data read. The other options incorrectly assume limitations or alternative optimizations.

Answer

Spark SQL will perform a full table scan because the filter on amount is not on a partition column, and then apply the filter after reading all data.

Answer

Spark SQL will dynamically repartition the data based on the amount filter to optimize the query, using adaptive query execution.

Answer

Spark SQL will only use partition pruning on region and will not use any statistics for amount because statistics are only collected on partition columns.