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

Question 1

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?

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

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 2

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 3

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 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 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 6

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 7

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

Accepted Answer

ARRAY_CONTAINS(tags, 'urgent'). ARRAY_CONTAINS provides a direct boolean test for whether an array includes a given value, making it ideal for filtering rows without altering their structure. SIZE returns the element count of an array, which satisfies the need for a new column with the number of tags. Together, they allow the query to filter and augment the DataFrame efficiently. Other functions like EXPLODE change row cardinality, COLLECT_LIST aggregates, and ARRAY_MAX finds a maximum, none of which meet the specific goals.

Answer

ARRAY_MAX(tags)

Answer

EXPLODE(tags)

Answer

COLLECT_LIST(tags)

Question 8

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 9

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 10

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 11

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 12

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 13

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

Accepted Answer

OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). A running total requires a window frame that starts at the beginning of the partition and ends at the current row. The specification with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, combined with PARTITION BY customer_id and ORDER BY transaction_date, achieves this by summing all rows up to and including the current one for each customer. Other frames either sum future rows, include tied rows, or limit to a small window, none of which produce the desired cumulative sum.

Answer

OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

Answer

OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

Answer

OVER (PARTITION BY customer_id ORDER BY transaction_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Question 14

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

Accepted Answer

Delta data skipping uses per-file min/max statistics, and if the table was written without collecting statistics or the file count is small, the country filter cannot eliminate files even though the value is low cardinality.. Delta data skipping prunes files using per-file statistics such as min/max and null counts recorded in the transaction log. A filter on country can only eliminate files if those statistics were collected at write time and the file layout separates country values. Low cardinality does not by itself enable skipping, and skipping is not limited to join conditions or partition columns, nor does it require a special enablement flag for basic operation.

Answer

Delta data skipping only works on columns used in JOIN conditions, so a WHERE filter on country is never eligible for file pruning regardless of statistics.

Answer

Data skipping requires the filtered column to be the first column in the table's partitioning scheme, so country must be a partition column for any file pruning to occur.

Answer

Data skipping is disabled by default and must be enabled with spark.databricks.delta.dataSkipping.enabled before any file pruning can happen on any column.

Question 15

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

Accepted Answer

array_contains(tags, 'spark'). To filter rows where the array contains 'spark', array_contains is the direct and efficient function. To transform each element of the array to uppercase, transform with a lambda using upper is the correct approach. Together, they satisfy both requirements without altering the DataFrame's structure.

Answer

upper(tags)

Answer

filter(tags, x -> x = 'spark')

Answer

explode(tags)