Databricks-Spark-Assoc Practice Test 5 — 25 Questions

Question 1

A data engineer has a PySpark DataFrame `events` with columns `user_id`, `event_time` (timestamp), and `payload` (string). They must produce a new DataFrame where each row is enriched with the `payload` value from the user's immediately preceding event, ordered by `event_time`, without collapsing any rows. Which TWO approaches accomplish this? (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a Window partitioned by `user_id` and ordered by `event_time`, then use `lag('payload', 1).over(windowSpec)` inside `withColumn`.. Enriching rows with a prior value without collapsing requires a Window function. Partitioning by `user_id` and ordering by `event_time` ensures the 'previous' row is the same user's immediately earlier event, while `lag` returns the payload from that row and preserves row count. Both `lag(...).over(windowSpec)` and the inline `Window.partitionBy(...).orderBy(...)` expression produce identical semantics; the alternatives either collapse rows, use invalid syntax, or lose per-user ordering.

Answer

Use `df.orderBy('event_time').rdd.zipWithIndex()` and manually look up the previous index per user in a driver-side dictionary.

Answer

Apply `window('user_id', 'event_time')` with `lag('payload')` and rely on Spark to fill nulls for the first event.

Answer

Call `groupBy('user_id').agg(collect_list('payload'))` and then explode the resulting list back into rows.

Question 2

A developer is debugging a Spark job and observes that a particular stage has 200 tasks, but only 10 executors with 2 cores each are available. What will happen to the remaining tasks in that stage?

Accepted Answer

The tasks will be queued and executed as cores become available, up to 20 concurrently. The number of concurrently running tasks is bounded by the total number of cores across executors. Here, 10 executors with 2 cores each provide 20 slots, so 20 tasks run at a time while the remaining 180 wait in the scheduler's queue. As tasks finish, new ones are launched. Spark does not fail or coalesce tasks due to resource scarcity.

Answer

The tasks will fail with an insufficient resources error

Answer

The tasks will be executed on the driver node to compensate for the lack of executors

Answer

The tasks will be automatically coalesced to match the number of available cores

Question 3

A Spark job reads a large CSV file, performs a groupBy aggregation, and then writes the result. The Spark UI shows that the job has multiple stages, and one stage has a large number of tasks. Which factor primarily determines the number of tasks in the stage that performs the aggregation?

Accepted Answer

The value of spark.sql.shuffle.partitions.. The number of tasks in a stage that follows a shuffle (like an aggregation) is determined by the number of shuffle partitions, which defaults to spark.sql.shuffle.partitions (200). The input partitions affect the initial stage, while executors and cores affect concurrency, not the total task count.

Answer

The number of partitions in the input RDD/DataFrame.

Answer

The number of cores per executor.

Answer

The number of executors in the cluster.

Question 4

In a Spark application running on a Databricks cluster, the driver program creates a SparkSession and defines a series of transformations. When an action is triggered, the driver requests resources from the cluster manager. Which component is responsible for negotiating and acquiring these resources on behalf of the Spark application?

Accepted Answer

The cluster manager. The cluster manager is the component that allocates resources to the Spark application. The driver requests resources, and the cluster manager launches executors accordingly. The DAG Scheduler and Task Scheduler operate within the driver to plan and schedule tasks, while executors run the tasks but do not negotiate resources.

Answer

The Task Scheduler

Answer

The DAG Scheduler

Answer

The Executor

Question 5

A developer is using Spark on Databricks and notices that a particular job has many stages due to shuffle operations. They want to understand the role of the shuffle in the Spark execution model. Which two statements accurately describe the behavior of a shuffle operation in Spark? (Choose two.)

Accepted Answer

A shuffle writes intermediate data to disk on the map side and reads it over the network on the reduce side.. A shuffle operation writes intermediate data to disk on the map side and transfers it over the network to reduce tasks, creating a stage boundary. These two characteristics are central to understanding why shuffles are expensive and how Spark's DAG is structured. The number of reduce partitions is configurable and not fixed to one per reducer, and shuffles are triggered by transformations, not actions.

Answer

A shuffle eliminates the need for the driver to coordinate task scheduling across stages.

Answer

A shuffle always results in exactly one partition per reducer task, regardless of the number of map tasks.

Answer

A shuffle is only triggered by actions, not by transformations.

Question 6

A developer has a PySpark DataFrame `df` with columns `order_id`, `customer_id`, and `order_ts` (timestamp). They need to return only the most recent order per customer, keeping all original columns, and they want to avoid a self-join or a manual sort-then-dropDuplicates approach. Which DataFrame operation should they use?

Accepted Answer

Use a Window partitioned by `customer_id` ordered by `order_ts` descending, add `row_number()`, then filter for row number equal to 1. Selecting the latest row per group while preserving all columns is exactly what a Window with `row_number()` solves. Partitioning by the grouping key and ordering by the timestamp descending makes the most recent record rank 1, and filtering on that rank returns complete rows. Aggregations drop columns, global limits collapse groups, and dropDuplicates cannot express ordering.

Answer

df.dropDuplicates(["customer_id"])

Answer

df.groupBy("customer_id").max("order_ts")

Answer

df.orderBy("order_ts", ascending=False).limit(1)

Question 7

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 8

A 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.)

Accepted Answer

Use a `LEFT OUTER JOIN` between `transactions` and `customers`.. Preserving unmatched left-side rows requires an outer join oriented to the left table, and eliminating duplicated combinations requires a distinct projection. Together, a left outer join plus `SELECT DISTINCT` returns every transaction, matches customers where possible, and collapses repeated pairs into single rows. Inner and full outer joins change which unmatched rows survive and do not meet the stated retention rule.

Answer

Use an `INNER JOIN` instead of an outer join.

Answer

Use a `FULL OUTER JOIN` between the two tables.

Answer

Add a `GROUP BY` on all columns from both tables.

Question 9

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 10

A Spark application on Databricks uses a broadcast variable to distribute a large lookup table to all executors. The developer notices that the broadcast variable is not being used efficiently, as executors are still fetching the data multiple times. Which component is responsible for ensuring that the broadcast data is distributed only once per executor and cached there?

Accepted Answer

The Executors' Block Managers. Broadcast variables are distributed using a BitTorrent-like protocol where the driver divides the data into blocks. Executors use their Block Managers to fetch these blocks from the driver or from other executors, and then cache the assembled broadcast data locally. This ensures that each executor retrieves the broadcast data only once, even if multiple tasks on that executor need it. The Block Manager is the key component for caching and serving broadcast blocks on executors.

Answer

The Driver's Block Manager

Answer

The DAG Scheduler

Answer

The Cluster Manager

Question 11

A Spark application is running in cluster mode on Databricks. The driver program is running on a worker node, and the application has been running for several hours. Suddenly, the driver node experiences a hardware failure and crashes. What happens to the running tasks and the application?

Accepted Answer

The application fails completely, and all running tasks are lost; the application must be restarted from scratch.. The driver is the single point of failure in a Spark application. It hosts the SparkContext, DAG Scheduler, and Task Scheduler. If the driver crashes, the application fails, and all running tasks are lost. While the cluster manager may restart the driver, the application does not automatically resume from checkpoints unless explicitly configured. Thus, the correct outcome is complete failure and restart from scratch.

Answer

The executors continue to run tasks and complete the job, and a new driver is automatically elected from the executors.

Answer

The running tasks continue on executors until they finish, and then the application terminates gracefully.

Answer

The cluster manager restarts the driver on another node, and the application resumes from the last checkpoint.

Question 12

A data engineer has a PySpark DataFrame `events` with columns `user_id`, `event_time` (timestamp), and `payload` (string). They must produce a new DataFrame where each row is enriched with the `payload` value from the user's immediately preceding event, ordered by `event_time`, without collapsing any rows. Which TWO approaches accomplish this? (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

readings.select("sensor_id", (col("celsius") * 9/5 + 32).alias("fahrenheit")). The requirement is a column-wise transformation that preserves sensor_id and adds fahrenheit, evaluated across executors. select() with a Column expression and alias is the canonical DataFrame API approach: it is lazy, Catalyst-optimized, and does not move data to the driver. Converting to the RDD API is unnecessary, dropping celsius changes the schema, and collect() pulls everything to the driver, which the scenario forbids.

Answer

readings.collect().map(lambda r: (r.sensor_id, r.celsius * 9/5 + 32))

Answer

readings.rdd.map(lambda r: (r.sensor_id, r.celsius * 9/5 + 32)).toDF(["sensor_id", "fahrenheit"])

Answer

readings.withColumn("fahrenheit", col("celsius") * 9/5 + 32).drop("celsius")

Question 13

A developer has a DataFrame `events` with columns `user_id`, `event_type`, and `payload` (a JSON string). They need to extract the `device` field from `payload` into a new column without changing the other columns or the row count. Which approach is correct?

Accepted Answer

events.withColumn("device", get_json_object("payload", "$.device")). Extracting a single scalar from a JSON string column is exactly what `get_json_object` does, and `withColumn` adds the result without altering other columns or row counts. Parsing the whole payload with `from_json` is unnecessary when only one field is needed, and any use of `explode` or RDD conversion changes the shape of the DataFrame.

Answer

events.withColumn("device", explode(from_json("payload", schema)))

Answer

events.select("user_id", "event_type", from_json("payload", schema).getField("device"))

Answer

events.rdd.map(lambda r: r.payload["device"]).toDF()

Question 14

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

Accepted Answer

customers.join(orders, customers.customer_id == orders.customer_id, 'left'). A left outer join preserves every row from the customers DataFrame while attaching matching order rows, so customers without orders appear with nulls in the order columns. Inner, right, and full outer joins either drop unmatched customers or add unmatched orders, so they do not meet the stated requirement.

Answer

customers.join(orders, customers.customer_id == orders.customer_id, 'full')

Answer

customers.join(orders, customers.customer_id == orders.customer_id, 'inner')

Answer

customers.join(orders, customers.customer_id == orders.customer_id, 'right')

Question 15

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

Accepted Answer

The query fails with TABLE_OR_VIEW_NOT_FOUND because the temporary view is scoped to the SparkSession that created it and is not visible to a different notebook session.. Temporary views are registered in a session-scoped catalog, so a view created in one notebook is invisible to other notebooks even when they run on the same cluster. A second notebook gets its own SparkSession and cannot resolve the view name, producing TABLE_OR_VIEW_NOT_FOUND. To share results across sessions, the engineer must persist them as a table in the metastore or use a global temporary view in the global_temp database.

Answer

The query fails with an analysis error only if the underlying sales table was dropped; otherwise the temporary view remains globally accessible.

Answer

The query succeeds only if the second notebook calls REFRESH TABLE high_value_customers before selecting from it, because temp views are lazily registered.

Answer

The query succeeds because temporary views are stored in the cluster-wide Spark catalog and shared across all notebooks on that cluster.

Question 16

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

Accepted Answer

Create a table using `CREATE TABLE sales USING parquet LOCATION 'dbfs:/mnt/raw/sales/'` and then query it.. Spark SQL resolves table names through the metastore catalog, so a directory on storage is invisible until a table definition points at it. Using `CREATE TABLE ... USING parquet LOCATION ...` registers the path without moving data, enabling immediate querying by name. Commands that repair or refresh metadata presuppose an existing table and cannot create one.

Answer

Set `spark.sql.warehouse.dir` to `dbfs:/mnt/raw/sales/` and rerun the query.

Answer

Execute `REFRESH TABLE sales` so Spark reloads the metadata from disk.

Answer

Run `MSCK REPAIR TABLE sales` to register the directory with the metastore.

Question 17

A developer is building a PySpark job that reads a Parquet dataset with 2,000 files into `df`. They call `df.cache()` and then execute three separate actions in the same session. The Spark UI shows the Parquet files are read from storage three times, and the cache never appears in the Storage tab. What is the most likely cause?

Accepted Answer

`cache()` was applied to `df` but a variable reassignment such as `df = df.withColumn(...)` created a new logical plan that no longer references the cached node.. Caching in Spark marks a specific logical plan node as persistent. Any subsequent transformation that derives a new DataFrame from it produces a different plan that does not include the cached node, so actions against the new DataFrame bypass the cache entirely. The tell-tale signs here are repeated Parquet reads and an empty Storage tab. To fix this, either cache after the final transformation or reuse the exact same cached DataFrame reference across actions.

Answer

`cache()` only takes effect after `persist(StorageLevel.MEMORY_ONLY)` is called, so the default call does nothing.

Answer

`cache()` stores data in memory only, and the dataset is too large, so Spark silently drops the cached blocks.

Answer

The Parquet files are stored in a format that Spark cannot cache, so `cache()` is a no-op for Parquet sources.

Question 18

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

Accepted Answer

The literal is compared against a partition column of a different type or format, so pruning cannot match partitions.. Partition pruning depends on Catalyst proving that a filter predicate matches partition values, which requires the literal's type and format to align with the partition column. A string partition column compared to a differently typed or formatted literal prevents static pruning, causing a full scan. Correcting the literal's type and format lets the optimizer eliminate irrelevant partitions.

Answer

Partition pruning is disabled by default and must be enabled with `spark.sql.optimizer.enablePartitionPruning`.

Answer

The query lacks a `LIMIT` clause, which forces Spark to read every file in the table.

Answer

Delta tables never support partition pruning; only Parquet tables do.

Question 19

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

Accepted Answer

SELECT * FROM events WHERE to_timestamp(event_time, 'yyyy-MM-dd HH:mm:ss') >= current_timestamp() - INTERVAL 7 DAYS. The correct expression uses to_timestamp to parse the string column with the explicit format, then compares it to current_timestamp() minus an interval of 7 days. This ensures accurate filtering based on the full timestamp, including time-of-day, and leverages Spark SQL's built-in interval arithmetic. Other options either ignore the time component, rely on implicit casting that may fail, or use less precise date-only comparisons.

Answer

SELECT * FROM events WHERE date_format(event_time, 'yyyy-MM-dd') >= date_sub(current_date(), 7)

Answer

SELECT * FROM events WHERE event_time >= current_date() - 7

Answer

SELECT * FROM events WHERE unix_timestamp(event_time) >= unix_timestamp(current_timestamp()) - 604800

Question 20

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

Accepted Answer

Use a broadcast hint: SELECT /*+ BROADCAST(customers) */ * FROM orders JOIN customers ON orders.customer_id = customers.customer_id. The broadcast hint instructs Spark to send the smaller table to all executors, so the large table can be joined locally without shuffling. This is the optimal approach when one side of the join is small enough to fit in memory. Other options either disable broadcast, use an invalid hint, or introduce additional shuffles. The broadcast join is a key optimization in Spark SQL for star-schema joins.

Answer

Set spark.sql.autoBroadcastJoinThreshold to -1 to force broadcast joins.

Answer

Repartition both DataFrames on customer_id before joining.

Answer

Use a merge hint: SELECT /*+ MERGE(customers) */ * FROM orders JOIN customers ON orders.customer_id = customers.customer_id