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

Question 1

A data engineer is building a Spark SQL job that reads a JSON file where each line is a separate record. The schema is variable, and many fields are missing in some records. The engineer wants to ensure that the query does not fail due to malformed records and that missing fields are treated as null. Which approach should be used when creating the DataFrame?

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

Accepted Answer

Use spark.read.json with mode='PERMISSIVE' and inferSchema=true.. The correct approach uses PERMISSIVE mode, which is the default but explicitly set here, to handle malformed records by setting corrupt fields to null. Combined with schema inference, it accommodates variable schemas without manual intervention. This ensures the job does not fail and missing fields become null, satisfying both requirements.

Answer

Use spark.read.json with the option 'allowMissingFields' set to true and inferSchema=true.

Answer

Use spark.read.json with mode='FAILFAST' and inferSchema=true.

Answer

Use spark.read.json with mode='DROPMALFORMED' and a predefined schema.

Question 2

You are working with a Spark SQL DataFrame that contains a column named metadata of type STRING, which stores JSON strings like '{"key": "value", "count": 10}'. You need to extract the value of the "count" field as an integer for each row. Which Spark SQL function should you use?

Accepted Answer

CAST(get_json_object(metadata, '$.count') AS INT). The correct approach extracts the JSON field as a string using get_json_object and then casts it to integer. This is efficient and straightforward. Other functions either return strings without casting or require more complex schema specifications.

Answer

json_tuple(metadata, 'count')

Answer

get_json_object(metadata, '$.count')

Answer

from_json(metadata, 'count INT')

Question 3

A developer needs to join a 40 GB fact table named orders with a 2 MB lookup table named currency_rates on the currency_code column. The lookup table is read from a Delta table and is broadcast correctly, but the developer notices the broadcast exchange still shuffles the small side in one of two consecutive join stages because the same small DataFrame is reused in both joins. Which Spark SQL technique avoids re-broadcasting the small relation for the second join?

Accepted Answer

Reuse the same broadcast relation by referencing a single cached broadcast through a DataFrame that is broadcast once and joined twice, ensuring the plan contains one BroadcastExchange shared by both joins.. Broadcast joins avoid shuffling the large side, but each join independently builds a broadcast exchange unless the plan can share one. By constructing the small side once as a broadcast relation and joining with that same relation in both stages, the physical plan contains a single BroadcastExchange consumed by both joins, so the 2 MB lookup is shipped once instead of twice. Caching or hinting alone does not fuse separate exchanges.

Answer

Persist the small DataFrame with cache() and rely on the automatic broadcast hint detection to reuse the cached broadcast relation across both joins.

Answer

Wrap the small DataFrame in a SQL broadcast hint and reference the same broadcast relation through a temporary view so the optimizer can reuse the broadcast exchange across both joins.

Answer

Use the spark.sql.adaptive.enabled and spark.sql.adaptive.coalescePartitions.enabled settings so adaptive query execution merges the two broadcast exchanges into one.

Question 4

A developer is writing a Spark SQL query in Databricks that filters a large Delta table by a partition column and then applies a high-cardinality distinct count on a non-partition column. They notice the query reads far more data than expected and the distinct count causes a large shuffle. They want to reduce data scanned and shuffle size without changing the result semantics. Which approach is most appropriate?

Accepted Answer

Ensure the partition filter predicate is applied early and use a two-stage aggregation with a partial distinct count before the final distinct count. The best approach combines early application of the partition filter, which enables partition pruning to cut scanned data, with a two-stage aggregation that computes partial distinct counts before the final combine. This reduces shuffle volume while keeping exact distinct-count semantics, unlike approximate functions or indiscriminate repartitioning.

Answer

Convert the distinct count to a groupBy on the non-partition column followed by a count, and disable partition pruning

Answer

Replace the distinct count with approx_count_distinct and add a partition filter predicate on the partition column

Answer

Increase spark.sql.shuffle.partitions to a very large value and cache the entire Delta table in memory

Question 5

A developer has a Spark SQL DataFrame `df` with columns `id` and `value`. They need to create a new DataFrame that contains only rows where `value` is greater than 100. Which code snippet correctly achieves this using the DataFrame API?

Accepted Answer

df.filter(col('value') > 100). The correct approach is to use `df.filter(col('value') > 100)` or `df.where(col('value') > 100)`. The `col` function creates a column reference, and the comparison yields a boolean column that `filter` uses to select rows. The other options either use invalid syntax, perform selection instead of filtering, or reference an undefined variable.

Answer

df.filter(df.value > 100)

Answer

df.where(value > 100)

Answer

df.select(col('value') > 100)

Question 6

A developer needs to run a Spark SQL query that calculates the total sales per region from a table named 'sales'. The table has columns 'region' and 'amount'. Which SQL statement correctly computes the total sales per region?

Accepted Answer

SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region;. To compute total sales per region, you must group by the region column and aggregate the amount column using SUM. The correct statement does exactly that, producing one row per region with the total sales. The other options either omit grouping, use an invalid alias in GROUP BY, or add an extra grouping column that alters the result.

Answer

SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region, amount;

Answer

SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY total_sales;

Answer

SELECT region, SUM(amount) AS total_sales FROM sales;

Question 7

A data engineer is working with a Spark SQL DataFrame that contains a column 'tags' as an array of strings. They need to filter rows where the array contains the string 'urgent'. Which TWO Spark SQL functions can be used to achieve this? (Choose two.)

Accepted Answer

array_position(tags, 'urgent') > 0. array_contains directly checks for the presence of a value in an array and returns a boolean, making it ideal for filtering. array_position returns the index of the element, so checking if it is greater than 0 also indicates presence. Both are valid Spark SQL functions for this purpose. The other options either use invalid syntax, return non-boolean results, or require additional conditions to work.

Answer

tags.contains('urgent')

Answer

explode(tags) = 'urgent'

Answer

filter(tags, x -> x = 'urgent') IS NOT NULL

Question 8

A data engineer is building a Spark SQL job that reads a JSON file where each line is a separate record. The schema is variable, and many fields are missing in some records. The engineer wants to ensure that the query does not fail due to malformed records and that missing fields are treated as null. Which approach should be used when creating the DataFrame?

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

Accepted Answer

/*+ BROADCAST(dimension_table) */. The BROADCAST hint is the correct way to force a broadcast hash join in Spark SQL. It tells the optimizer to send the specified small table to all executors, eliminating the need to shuffle the large fact table. This reduces network overhead and speeds up the join. Other hints like SHUFFLE_HASH or MERGE request different join strategies that involve shuffling, which is less efficient for a small dimension table. BROADCASTJOIN is not valid Spark SQL syntax.

Answer

/*+ MERGE(dimension_table) */

Answer

/*+ SHUFFLE_HASH(dimension_table) */

Answer

/*+ BROADCASTJOIN(dimension_table) */

Question 9

A data engineer is working with a Spark SQL DataFrame that has a column 'timestamp' of type string in the format 'yyyy-MM-dd HH:mm:ss'. The engineer needs to filter rows where the timestamp is on or after '2023-01-01 00:00:00'. Which approach correctly performs this filtering?

Accepted Answer

df.filter(to_timestamp(col('timestamp')) >= to_timestamp(lit('2023-01-01 00:00:00'))). The correct approach uses to_timestamp to convert both the column and the literal to timestamp type, ensuring an accurate chronological comparison. This method handles the string format correctly and includes records on or after the specified timestamp. Other options either rely on string comparison, use date casting with incorrect comparison operators, or use less precise date functions that exclude the threshold day.

Answer

df.filter(col('timestamp').cast('date') >= lit('2023-01-01').cast('date'))

Answer

df.filter(unix_timestamp(col('timestamp'), 'yyyy-MM-dd HH:mm:ss') >= unix_timestamp(lit('2023-01-01 00:00:00'), 'yyyy-MM-dd HH:mm:ss'))

Answer

df.filter(col('timestamp') >= '2023-01-01 00:00:00')

Question 10

A data analyst is working with a Spark SQL DataFrame that has a column 'price' with some null values. They want to replace all nulls in the 'price' column with 0. Which Spark SQL expression should they use?

Accepted Answer

SELECT coalesce(price, 0) AS price FROM sales. coalesce is the standard Spark SQL function to return the first non-null value from a list. Using coalesce(price, 0) replaces nulls in the price column with 0 while leaving existing values unchanged. Other options either use invalid syntax, perform the opposite operation, or incorrectly null out non-null values due to a missing ELSE clause.

Answer

SELECT case when price is null then 0 end AS price FROM sales

Answer

SELECT nvl(price, 0) AS price FROM sales

Answer

SELECT ifnull(price, 0) AS price FROM sales

Question 11

A data engineer is using Spark SQL to join two large tables, 'orders' and 'customers', on customer_id. The 'orders' table has a column 'order_date' and the 'customers' table has a column 'signup_date'. The engineer wants to include only orders placed after the customer's signup date. Which join condition correctly implements this requirement?

Accepted Answer

orders.join(customers, orders.customer_id == customers.customer_id).filter(orders.order_date > customers.signup_date). The correct approach is to perform an equi-join on customer_id and then apply a filter for the date comparison. This works because Spark SQL supports non-equi conditions in filters after the join. Attempting to include the inequality in the join condition is not supported and will cause an error. Other options either select a boolean instead of filtering or use the wrong comparison operator.

Answer

orders.join(customers, orders.customer_id == customers.customer_id, 'inner').filter(orders.order_date > customers.signup_date)

Answer

orders.join(customers, (orders.customer_id == customers.customer_id) & (orders.order_date > customers.signup_date))

Answer

orders.join(customers, orders.customer_id == customers.customer_id).where(orders.order_date > customers.signup_date)

Question 12

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?

Accepted Answer

Enable 'autoOptimize' and 'optimizeWrite' at the Delta table level.. Optimizing file sizes is crucial for read performance and metadata management in Delta Lake. Using the OPTIMIZE command or enabling Auto Optimize are the standard patterns to address the small file problem. These techniques consolidate fragmented data into larger, performant files, reducing the overhead on the query engine and preventing performance degradation during subsequent read operations. This is a fundamental skill for maintaining healthy, scalable data lakes on Databricks.

Answer

Execute a DROP TABLE command before overwriting the existing table every time.

Answer

Increase the spark.sql.shuffle.partitions configuration to a very high value.

Answer

Use the 'repartition(1)' method on the DataFrame before writing to storage.

Question 13

Which SQL command is used to view the history of operations performed on a Delta table, including timestamps and operation types?

Accepted Answer

DESCRIBE HISTORY table_name. The DESCRIBE HISTORY command is a core utility in Databricks for auditing and troubleshooting. It provides a detailed log of all modifications, including writes, updates, and deletes, along with version numbers. This is essential for understanding table evolution, debugging data pipelines, and implementing data governance. Knowing this command is mandatory for any Spark developer responsible for maintaining and auditing production-level Delta Lake tables.

Answer

SHOW METADATA ON table_name

Answer

SELECT * FROM audit_log(table_name)

Answer

GET TABLE VERSION table_name

Question 14

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

Accepted Answer

Use the /*+ BROADCAST(small_table) */ hint.. Broadcasting is a critical optimization technique for star-schema joins. By sending a copy of the small table to every executor, Spark avoids the expensive shuffle phase associated with shuffling the massive table. This significantly reduces network I/O and latency. For a Databricks developer, recognizing when to use hints or rely on the optimizer to perform broadcast joins is vital for writing performant, scalable SQL queries on large datasets.

Answer

Increase the shuffle partitions to 2000.

Answer

Enable the 'autoBroadcastJoinThreshold' to a very low value.

Answer

Convert the massive table into an unmanaged view.

Question 15

Which clause is used in a SELECT statement to filter the results based on aggregated values?

Accepted Answer

HAVING. The HAVING clause is essential for filtering the output of an aggregation. While WHERE filters rows before aggregation, HAVING filters groups after the aggregation is calculated. Distinguishing between these two clauses is a foundational concept in SQL and critical for developers using Spark SQL to summarize data. Failure to use the correct filter leads to syntax errors or incorrect analytical results, highlighting the necessity of this fundamental SQL skill.

Answer

WHERE

Answer

FILTER

Answer

LIMIT