Data Transformation and Modeling — Databricks Data Engineer Associate Practice Quiz

Question 1

A data engineer is joining a large fact table (billions of rows) with a small dimension table (about 20 MB) in a Spark SQL query. The job is running slowly because of heavy shuffling during the join. Which technique should the engineer use to eliminate the shuffle and improve performance?

Accepted Answer

Broadcast the small dimension table so it is sent to all executors and joined locally without shuffling the fact table

Answer

Repartition both tables by the join key so the data is co-located before joining

Answer

Apply a window function partitioned by the join key to avoid the join altogether

Answer

Cache the fact table in memory before executing the join

Question 2

A data engineer is building a Silver-layer transformation from a Bronze customer table. The column `preferred_contact` frequently contains NULL values, and the business rule states that when it is NULL, the pipeline should fall back to the value in `secondary_contact`, and if that is also NULL, use the literal string 'unknown'. Which Spark SQL expression correctly implements this rule in a single column definition?

Accepted Answer

COALESCE(preferred_contact, secondary_contact, 'unknown') AS contact

Answer

NVL(preferred_contact, secondary_contact) AS contact

Answer

IFNULL(preferred_contact, 'unknown') AS contact

Answer

NULLIF(preferred_contact, secondary_contact, 'unknown') AS contact

Question 3

A data engineer has a Silver table 'order_items' with one row per line item, containing columns 'order_id', 'product_name', and 'quantity'. For a reporting requirement, they must produce a Gold table with exactly one row per 'order_id' where a new column 'products' holds an array of all 'product_name' values for that order. Which Spark SQL approach produces the correct result?

Accepted Answer

SELECT order_id, collect_list(product_name) AS products FROM order_items GROUP BY order_id

Answer

SELECT order_id, explode(product_name) AS products FROM order_items GROUP BY order_id

Answer

SELECT order_id, array(product_name) AS products FROM order_items

Answer

SELECT DISTINCT order_id, product_name AS products FROM order_items

Question 4

A data engineer is building a Gold-layer table that reports the number of unique customers who placed at least one order on each calendar day. The Silver table `orders` has columns `order_date`, `customer_id`, and `order_amount`. Which Spark SQL query correctly produces one row per day with the count of distinct customers?

Accepted Answer

SELECT order_date, COUNT(DISTINCT customer_id) AS unique_customers FROM orders GROUP BY order_date

Answer

SELECT order_date, COUNT(customer_id) AS unique_customers FROM orders GROUP BY order_date

Answer

SELECT DISTINCT order_date, COUNT(customer_id) AS unique_customers FROM orders

Answer

SELECT order_date, COUNT(DISTINCT customer_id) AS unique_customers FROM orders

Question 5

A data engineer runs `CREATE TABLE sales_summary AS SELECT region, SUM(amount) AS total FROM silver.orders GROUP BY region` in a Databricks SQL notebook without specifying a LOCATION. The catalog and schema are configured with default settings. Where will the underlying data files for sales_summary be stored, and what happens if the table is later dropped?

Accepted Answer

Files are stored in the metastore's managed storage location, and dropping the table deletes both the metadata and the underlying data files.

Answer

Files are stored in the metastore's managed storage location, but dropping the table only removes the metadata while leaving data files intact.

Answer

No files are stored because CTAS creates only a view, so dropping it removes the query definition but no data.

Answer

Files are stored in a user-specified external path, and dropping the table removes only the metadata.

Question 6

A data engineer needs to build a complete availability matrix for a Gold-layer report. Each of the 500 store locations must appear once for every date in a 365-row date dimension table, regardless of whether any sales occurred. Both tables share no common join key. Which Spark SQL join produces all 182,500 store-date combinations?

Accepted Answer

A CROSS JOIN between the stores table and the date dimension table

Answer

An INNER JOIN on store_id = date_id

Answer

A LEFT OUTER JOIN with the date table on the left side

Answer

A FULL OUTER JOIN between the two tables

Question 7

A data engineer is designing a Silver Delta table for e-commerce events. Queries almost always filter on the event date, but the source only provides an event_timestamp column of type TIMESTAMP. The engineer wants the table to be physically partitioned by date without requiring downstream writers to manually compute and populate a separate date column on every insert. Which approach best satisfies this requirement?

Accepted Answer

Define a generated column, e.g. event_date DATE GENERATED ALWAYS AS (CAST(event_timestamp AS DATE)), and partition the table by event_date.

Answer

Partition the table directly by event_timestamp so Delta automatically buckets rows into daily folders.

Answer

Create a regular event_date column and require every writer to include a CAST(event_timestamp AS DATE) expression in each INSERT statement.

Answer

Run OPTIMIZE with ZORDER BY (event_timestamp) instead of partitioning to physically organize data by date.

Question 8

A data engineer runs a MERGE INTO command to upsert a batch of customer updates into a Delta target table. The source DataFrame contains multiple rows with the same customer_id (the merge key) because the daily extract captured several updates per customer. The MERGE fails with an error stating that a matched target row was matched by more than one source row. What is the correct approach to resolve this while keeping only the most recent update per customer?

Accepted Answer

Deduplicate the source before the MERGE by using a window function to keep only the latest record per customer_id, then run the MERGE against the deduplicated source.

Answer

Add the SKIP DUPLICATES clause to the MERGE statement so Delta automatically retains one row per key.

Answer

Change the target table to allow multiple matches by disabling ACID enforcement with a table property.

Answer

Repartition the source DataFrame by customer_id so each partition contains only one row per key before the MERGE.

Question 9

A data engineer maintains a Silver customer table in Delta Lake. Each night a source extract arrives that contains both existing customers (already loaded) and brand-new customers. The business rule is strict: existing customer rows must NEVER be modified, but any customer not yet in the table must be inserted. Which MERGE statement correctly implements this requirement?

Accepted Answer

MERGE INTO silver_customers t USING updates s ON t.customer_id = s.customer_id WHEN NOT MATCHED THEN INSERT *

Answer

MERGE INTO silver_customers t USING updates s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *

Answer

MERGE INTO silver_customers t USING updates s ON t.customer_id = s.customer_id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT *

Answer

INSERT OVERWRITE silver_customers SELECT * FROM updates

Question 10

A data engineer is writing a MERGE statement to update a Delta table of customer accounts. Business rules require that when a matched record has a status of 'CLOSED' in the source, the target row should be deleted; for all other matched records, the target row should be updated with the new balance. Which MERGE clause construction correctly implements both behaviors?

Accepted Answer

Use two WHEN MATCHED clauses: the first with condition `s.status = 'CLOSED'` THEN DELETE, followed by a second WHEN MATCHED THEN UPDATE SET t.balance = s.balance.

Answer

Use a single WHEN MATCHED THEN UPDATE clause and rely on a separate DELETE statement run before the MERGE.

Answer

Use two WHEN MATCHED clauses ordered as UPDATE first, then the DELETE with condition `s.status = 'CLOSED'`, since order does not affect evaluation.

Answer

Use a single WHEN MATCHED clause with a CASE expression that returns DELETE or UPDATE based on `s.status`.

Question 11

A data engineer maintains a Silver dimension table `dim_customer` and receives a daily batch of updated customer records in a staging view `stg_customer`. Both tables share the key `customer_id`. The requirement is: update existing customers with new attribute values, and insert customers that do not yet exist. Which single Spark SQL statement correctly implements this upsert?

Accepted Answer

MERGE INTO dim_customer t USING stg_customer s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *

Answer

INSERT OVERWRITE dim_customer SELECT * FROM stg_customer

Answer

MERGE INTO dim_customer t USING stg_customer s ON t.customer_id = s.customer_id WHEN NOT MATCHED BY SOURCE THEN DELETE

Answer

INSERT INTO dim_customer SELECT * FROM stg_customer

Question 12

A data engineer maintains a Silver Delta table 'customers' that must be fully synchronized with a daily full snapshot loaded into a staging view 'stg_customers'. The requirement is: insert new customers, update changed customers, and delete customers that no longer exist in the daily snapshot. Which single MERGE statement clause correctly handles the deletion of records that are present in the target but absent from the source?

Accepted Answer

WHEN NOT MATCHED BY SOURCE THEN DELETE

Answer

WHEN NOT MATCHED THEN DELETE

Answer

WHEN MATCHED AND source.id IS NULL THEN DELETE

Answer

WHEN NOT MATCHED BY TARGET THEN DELETE

Question 13

A data engineer maintains a Silver Delta table `customers` and receives a daily source batch `updates` containing both new and existing customer records, keyed by `customer_id`. The requirement is: when a `customer_id` already exists in the target, update its `email` and `status` columns; when a `customer_id` does not exist in the target, insert the full new row. Which single Spark SQL statement correctly implements this in one pass?

Accepted Answer

MERGE INTO customers t USING updates s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET t.email = s.email, t.status = s.status WHEN NOT MATCHED THEN INSERT *

Answer

INSERT INTO customers SELECT * FROM updates; then run UPDATE customers SET email = ... to fix duplicates afterward

Answer

MERGE INTO customers t USING updates s ON t.customer_id = s.customer_id WHEN NOT MATCHED THEN UPDATE SET t.email = s.email WHEN MATCHED THEN INSERT *

Answer

INSERT OVERWRITE customers SELECT * FROM updates

Question 14

A data engineer is creating a Silver Delta table for web clickstream events. The table will hold billions of rows, and analysts almost always filter queries by event_date. A colleague suggests partitioning the table by user_id because most joins reference that column. What is the best partitioning strategy, and why?

Accepted Answer

Partition by event_date, because it is a low-cardinality column frequently used in filter predicates, enabling effective partition pruning.

Answer

Partition by user_id, because partitioning on the most-joined column always improves join performance the most.

Answer

Partition by both event_date and user_id together to satisfy both filtering and joining patterns.

Answer

Do not partition at all; Delta tables cannot benefit from partitioning on any column.

Question 15

A data engineer needs to add a column to a Spark DataFrame of sales transactions that shows, for each row, the total number of transactions made by that customer — while still keeping every individual transaction row in the output. Which approach produces the correct result without collapsing rows?

Accepted Answer

Apply a window function: count('*').over(Window.partitionBy('customer_id')) as a new column

Answer

Use groupBy('customer_id').count() and use the resulting DataFrame as the final output

Answer

Use groupBy('customer_id').agg(count('*')) then drop duplicate customer rows

Answer

Apply select(countDistinct('transaction_id')) over the entire DataFrame

Question 16

A data engineer is building a Silver-layer table from ingested event records. Each row has columns event_id, user_id, event_type, and ingest_timestamp. Duplicate events can arrive with the same event_id but slightly different ingest_timestamp values. The engineer wants to keep only one row per unique event_id, regardless of the timestamp. Which PySpark approach correctly deduplicates the DataFrame based on event_id alone?

Accepted Answer

df.dropDuplicates(["event_id"])

Answer

df.distinct()

Answer

df.dropDuplicates()

Answer

df.drop("event_id")

Question 17

A data engineer is transforming a bronze Delta table where each row represents an order with a column `items` containing an array of struct values (each struct has `product_id` and `qty`). The goal for the silver layer is to produce one row per individual item, keeping the parent `order_id`. Which PySpark transformation correctly flattens this structure?

Accepted Answer

df.select("order_id", explode("items").alias("item")).select("order_id", "item.product_id", "item.qty")

Answer

df.select("order_id", collect_list("items").alias("item"))

Answer

df.groupBy("order_id").agg(count("items").alias("item_count"))

Answer

df.select("order_id", flatten("items").alias("items"))

Question 18

A data engineer is reconciling two daily snapshots: a 'crm_customers' table and a 'billing_customers' table, both keyed on customer_id. The goal is to produce a single result that includes every customer_id present in either table, showing NULLs for columns from whichever side is missing so that mismatches can be flagged. Which join type should be used?

Accepted Answer

FULL OUTER JOIN on customer_id

Answer

INNER JOIN on customer_id

Answer

LEFT SEMI JOIN on customer_id

Answer

CROSS JOIN on customer_id

Question 19

A data engineer is building a Silver table that combines an 'orders' DataFrame with a 'customers' DataFrame on customer_id. The business requirement is that every order must appear in the result, even if the order references a customer_id that does not exist in the customers table (so those customer columns should be null). Which join type should the engineer use?

Accepted Answer

orders.join(customers, "customer_id", "left")

Answer

orders.join(customers, "customer_id", "inner")

Answer

orders.join(customers, "customer_id", "left_semi")

Answer

orders.join(customers, "customer_id", "left_anti")

Question 20

A data engineer maintains two Delta tables: `active_customers` and `orders`. They need to produce a result set containing only the rows from `orders` whose `customer_id` does NOT exist in the `active_customers` table, so the marketing team can identify orphaned orders. Which single join operation produces exactly this result most efficiently?

Accepted Answer

A LEFT ANTI JOIN between `orders` and `active_customers` on `customer_id`

Answer

An INNER JOIN between `orders` and `active_customers`, then filter where `active_customers.customer_id IS NULL`

Answer

A LEFT SEMI JOIN between `orders` and `active_customers` on `customer_id`

Answer

A FULL OUTER JOIN between `orders` and `active_customers`, then filter where both keys are NULL

More Databricks Data Engineer Associate practice