Databricks-Spark-Assoc Practice Test 12 — 15 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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

Question 6

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

Accepted Answer

spark.sql("SELECT o.order_id, r.amount FROM orders o JOIN returns r ON o.order_id = r.order_id"). SparkSession.sql runs a SQL string against views registered in the session catalog and returns a DataFrame, allowing the result to be combined with DataFrame transformations. Because orders and returns are temporary views in the same session, the query resolves and returns a join result that can be further processed. The other calls reference methods that do not exist on those objects.

Answer

spark.catalog.sql("SELECT o.order_id, r.amount FROM orders o JOIN returns r ON o.order_id = r.order_id")

Answer

spark.executeSql("SELECT o.order_id, r.amount FROM orders o JOIN returns r ON o.order_id = r.order_id")

Answer

spark.sqlContext.runSql("SELECT o.order_id, r.amount FROM orders o JOIN returns r ON o.order_id = r.order_id")

Question 7

A data engineer has a PySpark DataFrame `orders` with a string column `order_ts` formatted as `yyyy-MM-dd HH:mm:ss`. They need a new column `order_date` containing only the date portion as a `date` type, so downstream code can filter by day. Which approach is correct?

Accepted Answer

`orders.withColumn('order_date', to_date('order_ts', 'yyyy-MM-dd HH:mm:ss'))`. Converting a formatted timestamp string to a `date` requires an explicit parse with a pattern. `to_date(col, pattern)` parses the string and returns a `date` column, which is the correct type for day-level filters and joins. String manipulation or `from_unixtime` yields strings, and `date_trunc` yields a timestamp, so none of them match the requirement as cleanly or as safely as `to_date`.

Answer

`orders.withColumn('order_date', from_unixtime(unix_timestamp('order_ts', 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd'))`

Answer

`orders.withColumn('order_date', date_trunc('day', 'order_ts'))`

Answer

`orders.withColumn('order_date', substring('order_ts', 1, 10))`

Question 8

A developer is troubleshooting a Spark Connect client that intermittently fails with connection errors to a Databricks cluster. Which two configuration practices help ensure stable connectivity? (Choose two.)

Accepted Answer

Set `spark.remote.connect.grpc.maxInboundMessageSize` to a value large enough for the plans being sent.. Stable Spark Connect connectivity depends on transport-level configuration. Raising the maximum gRPC message size prevents failures when large logical plans are serialized, and configuring retry and timeout policies on the gRPC channel lets the client recover from transient network interruptions. Shuffle partitioning, TLS disabling, and adaptive execution settings do not address connection errors and may harm security or performance.

Answer

Set `spark.sql.adaptive.enabled` to false to stabilize the connection.

Answer

Increase `spark.sql.shuffle.partitions` to 2000 to reduce the number of RPC calls.

Answer

Disable TLS between the client and the cluster to reduce handshake overhead.

Question 9

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

Accepted Answer

COUNT(DISTINCT customer_id). To return a distinct customer count per region within a single GROUP BY region query, the developer can use COUNT(DISTINCT customer_id), which gives an exact deduplicated count per group, or APPROX_COUNT_DISTINCT(customer_id), which returns an approximate count using a sketch and is preferable when cardinality is high. Both are valid aggregates that operate per group and produce one value per region.

Answer

COUNT(customer_id) FILTER (WHERE customer_id IS NOT NULL)

Answer

SUM(DISTINCT customer_id)

Answer

COLLECT_SET(customer_id)

Question 10

A developer runs a PySpark job on Databricks that reads a large Delta table, filters on a timestamp column, and writes results to another Delta table. The job takes 45 minutes, but the Spark UI shows that 90% of task time is spent reading from the source table. The developer wants to reduce the read time. Which action should the developer take?

Accepted Answer

Enable Delta Lake data skipping by ensuring the timestamp column is in the table's partitioning or Z-ORDER BY columns.. The Spark UI indicates that the bottleneck is reading from the source Delta table. Delta Lake data skipping leverages file statistics to avoid reading irrelevant files when a filter predicate is applied. Ensuring the timestamp column is a partition column or has Z-ORDER BY applied allows the engine to prune files effectively, reducing I/O and overall job time.

Answer

Increase the number of shuffle partitions by setting spark.sql.shuffle.partitions to a higher value.

Answer

Cache the source DataFrame in memory using .cache() before applying the filter.

Answer

Repartition the source DataFrame by the timestamp column before filtering.

Question 11

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

Accepted Answer

`ALTER TABLE products ADD COLUMNS (discounted_price DOUBLE GENERATED ALWAYS AS (price * 0.9))`. Adding a persisted computed column to a Delta table is done with `ALTER TABLE ... ADD COLUMNS` using a `GENERATED ALWAYS AS` expression. Spark computes the value from the base column for existing and future rows, so queries see a populated `discounted_price` without a manual update. Statements that assume the column already exists or recreate the table either fail or risk data integrity.

Answer

`ALTER TABLE products ADD COLUMNS (discounted_price DOUBLE)`

Answer

`UPDATE products SET discounted_price = price * 0.9`

Answer

`CREATE OR REPLACE TABLE products AS SELECT *, price * 0.9 AS discounted_price FROM products`

Question 12

A developer has a DataFrame `raw` and needs to permanently persist it as Parquet partitioned by `region`, overwriting any existing data at that path, without registering it in the metastore. Which call achieves this?

Accepted Answer

raw.write.mode("overwrite").partitionBy("region").parquet("/data/out"). The DataFrameWriter path with `mode("overwrite")`, `partitionBy("region")`, and `.parquet` writes typed Parquet files into per-region directories and replaces prior contents in one step. Because it targets a filesystem path rather than a table name, no metastore entry is created, satisfying the "without registering it in the metastore" constraint.

Answer

raw.rdd.saveAsTextFile("/data/out")

Answer

raw.write.format("parquet").saveAsTable("out")

Answer

raw.createOrReplaceTempView("out").write.parquet("/data/out")

Question 13

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

Accepted Answer

Enable adaptive query execution (AQE) and set spark.sql.adaptive.enabled to true.. Broadcasting the smaller table eliminates shuffling of the larger table. Enabling AQE allows Spark to dynamically optimize the join, potentially converting to a broadcast join or adjusting partitions. These two actions together minimize shuffling and improve efficiency.

Answer

Use the MERGE command to combine the tables.

Answer

Partition the customers table by customer_id to match the orders table.

Answer

Repartition both tables by customer_id before the join.

Question 14

A developer is building a Python application that connects to a Databricks cluster using Spark Connect. The application uses the `databricks-connect` package and is configured with the cluster ID and authentication credentials. During a test run, the developer calls `spark.sql("SELECT * FROM sales")` and then `df.show()`. What happens when the `show()` action is executed?

Accepted Answer

The client sends the logical plan to the Spark Connect server, which executes it on the cluster and returns the result rows back to the client.. In Spark Connect, the client builds a logical plan and sends it to the Spark Connect server via gRPC. The server executes the plan on the cluster and returns results. The client does not download data or execute locally. This architecture decouples the client from the driver, enabling remote execution and improved stability.

Answer

The client uses a local SparkContext to connect to the cluster's driver and executes the query as if it were local.

Answer

The client downloads the entire `sales` table to the local machine and executes the SQL query using a local Spark session.

Answer

The client compiles the SQL query into a JVM bytecode and sends it to the cluster for execution.

Question 15

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

Accepted Answer

COALESCE(GREATEST(discount, 0.0), 0.0). The requirement is to treat both nulls and negatives as zero. GREATEST(discount, 0.0) ensures negatives become 0.0 but yields null for null input. COALESCE then replaces that null with 0.0. The nested expression COALESCE(GREATEST(discount, 0.0), 0.0) correctly handles both cases and leaves positive values untouched. Using COALESCE alone misses negatives, GREATEST alone misses nulls, and the IF expression misses nulls.

Answer

IF(discount < 0, 0.0, discount)

Answer

GREATEST(discount, 0.0)

Answer

COALESCE(discount, 0.0)