Databricks-DE-Assoc Data Transformation and Modeling Practice Test 2 — 15 Questions

Question 1

A data engineer needs to join two streaming tables in a Delta Live Tables (DLT) pipeline using Python. Which approach properly implements a streaming-to-streaming join within the framework?

Accepted Answer

Apply a watermark to both streaming dataframes using `.withWatermark()` before performing the inner join with a time window condition.. Streaming-to-streaming joins in Delta Live Tables require watermarking to manage state and prevent out-of-memory errors by defining a time threshold for late data arrival. Combining watermarked streaming sources ensures the pipeline can reliably evaluate condition windows over unbounded datasets. This pattern is essential for maintaining efficient state management during real-time data transformations in production cloud architectures.

Answer

Join the two streaming tables directly using standard DataFrame join syntax without any time constraints.

Answer

Convert both streaming tables to static tables using `.read` before executing the join operation in the pipeline definition.

Answer

Use a broadcast join hint on the larger streaming table to bypass state management requirements during the join.

Question 2

A data engineer has a Delta table named `sales` with columns `sale_id`, `customer_id`, `sale_date`, and `amount`. The table is partitioned by `sale_date`. The engineer needs to overwrite only the data for `sale_date = '2024-01-01'` with a new DataFrame containing records only for that date, while preserving all other partitions. Which command should the engineer use?

Accepted Answer

spark.sql("INSERT OVERWRITE TABLE sales PARTITION (sale_date = '2024-01-01') SELECT * FROM new_data"). The correct command uses static partition overwrite syntax to replace only the specified partition while preserving all other partitions. It is a single atomic operation. The other options either overwrite the entire table, use a non-atomic two-step process, or perform an upsert that does not remove stale records.

Answer

spark.sql("MERGE INTO sales AS t USING new_data AS s ON t.sale_id = s.sale_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *")

Answer

spark.sql("DELETE FROM sales WHERE sale_date = '2024-01-01'"); spark.sql("INSERT INTO sales SELECT * FROM new_data")

Answer

spark.sql("INSERT OVERWRITE TABLE sales SELECT * FROM new_data")

Question 3

A data engineer is building a Delta Live Tables (DLT) pipeline that ingests streaming data from a Kafka topic. The pipeline must process only new data since the last run and must handle late-arriving data up to 7 days old. The engineer wants to ensure that the pipeline does not reprocess all historical data on each run. Which configuration should the engineer apply to the streaming table?

Accepted Answer

Set the `startingOffsets` option to `latest` and `maxOffsetsPerTrigger` to a fixed number.. The correct configuration sets `startingOffsets` to `latest` to avoid processing historical data on the first run, and DLT's checkpointing ensures subsequent runs continue from the last processed offset. This satisfies the requirement to process only new data. Other options either process all historical data, risk data loss, or misuse watermarking.

Answer

Set the `startingOffsets` option to `earliest` and enable `ignoreDeletes`.

Answer

Set the `startingOffsets` option to `latest` and configure a watermark of 7 days using `withWatermark`.

Answer

Use `spark.readStream.format("kafka")` with `startingOffsets` set to `latest` and set `failOnDataLoss` to `false`.

Question 4

A data engineer is optimizing a Delta table that is used for frequent merge operations. The table has a column `id` that is used as the merge key. The engineer wants to improve the performance of the merge by reducing the amount of data scanned. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Enable Z-Ordering on the `id` column.. Z-Ordering on the merge key colocates related data, enabling Delta Lake to skip files during the merge scan. Applying this via `OPTIMIZE ... ZORDER BY (id)` is the correct action. Partitioning by a high-cardinality column is counterproductive, and collecting statistics or increasing shuffle partitions do not reduce the scanned data for merge.

Answer

Collect statistics on the `id` column using `ANALYZE TABLE ... COMPUTE STATISTICS`.

Answer

Increase the `spark.sql.shuffle.partitions` setting.

Answer

Partition the table by the `id` column.

Question 5

A data engineer is building a Databricks SQL dashboard that must return only the 10 most recent orders for each customer from a large Delta table named sales.orders. The table contains columns customer_id, order_id, order_date, and amount. The engineer wants a single query that avoids scanning the entire table for every customer and returns a deterministic result even when two orders share the same order_date. Which SQL statement should the engineer use?

Accepted Answer

SELECT customer_id, order_id, order_date, amount FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn FROM sales.orders) WHERE rn <= 10. The requirement is a top-N per group: 10 most recent orders for each customer. A window function with PARTITION BY customer_id and ORDER BY order_date DESC, plus order_id as a deterministic tiebreaker, produces a rank per customer that can then be filtered with rn <= 10. This is the standard Databricks SQL pattern and avoids per-customer queries, preserving performance on a large Delta table.

Answer

SELECT customer_id, order_id, order_date, amount FROM sales.orders QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) <= 10

Answer

SELECT customer_id, order_id, order_date, amount FROM sales.orders GROUP BY customer_id ORDER BY order_date DESC LIMIT 10

Answer

SELECT customer_id, order_id, order_date, amount FROM sales.orders ORDER BY order_date DESC LIMIT 10

Question 6

A data engineer is building a Delta Live Tables pipeline that ingests streaming data from a Kafka topic. The pipeline must perform a stream-static join with a slowly changing dimension table stored as a Delta table. The dimension table is updated daily with new and updated records. Which approach ensures the pipeline correctly reflects the latest dimension data without reprocessing all historical data?

Accepted Answer

Declare the dimension as a live table (batch) and the fact as a streaming live table, then perform a stream-static join. Delta Live Tables automatically handles dimension updates by refreshing the static side on each pipeline run.. The correct pattern is a stream-static join where the fact is a streaming live table and the dimension is a live table (batch). Delta Live Tables reads the static side as a snapshot at the start of each pipeline update, so daily updates to the dimension are picked up when the pipeline runs. This avoids reprocessing all historical data and leverages Delta Live Tables' built-in capabilities.

Answer

Use a streaming live table for the fact data and a streaming live table for the dimension, then perform a stream-stream join.

Answer

Use a streaming live table for both fact and dimension, and apply a foreachBatch operation to reload the dimension table inside each micro-batch.

Answer

Use a streaming live table for the fact and a temporary view for the dimension, then join them. Temporary views are refreshed on each micro-batch, ensuring the latest dimension data.

Question 7

A data engineer is designing a Gold-layer Delta table that must support efficient point lookups by a high-cardinality transaction_id column and range filtering by transaction_date. The table is large and partitioned by transaction_date. Which two actions will improve query performance for these access patterns? (Choose two.)

Accepted Answer

Run OPTIMIZE regularly to compact small files before applying Z-Ordering.. The access patterns are equality lookups on a high-cardinality key and range filtering on the partition column. Z-Ordering on transaction_id clusters values so file-level statistics enable effective data skipping, while regular OPTIMIZE compaction keeps file counts low so both skipping and partition pruning remain efficient. Partitioning further by transaction_id would be counterproductive due to excessive small partitions.

Answer

Partition the table additionally by transaction_id.

Answer

Collect statistics and rely on data skipping for the transaction_id column.

Answer

Use OPTIMIZE with ZORDER BY on transaction_date only.

Question 8

A data engineer is building a Silver layer table in a Databricks notebook. The source Bronze Delta table contains raw JSON events with a nested struct column named `payload`. The engineer needs to flatten `payload` into individual columns (`event_id`, `event_type`, `event_time`) and drop all other nested fields. Which approach correctly produces the flattened Silver table while preserving Delta Lake transactional guarantees?

Accepted Answer

Run `SELECT payload.event_id, payload.event_type, payload.event_time FROM bronze` and use `CREATE OR REPLACE TABLE silver AS SELECT ...`.. The correct approach extracts nested struct fields with SQL or DataFrame column notation and persists the result as a Delta table using CTAS or `write.format('delta')`. This keeps schema enforcement, ACID transactions, and time travel intact. Approaches that read raw files, convert to RDDs, or blindly append either lose Delta guarantees or produce duplicate, ungoverned output.

Answer

Use `bronze.selectExpr('payload.*')` and write with `format('delta').mode('append')` on every pipeline run.

Answer

Use `spark.read.json()` on the Bronze table's underlying files, select `payload.*`, and write the result to a new Parquet directory.

Answer

Use `bronze.select('payload').rdd.flatMap(lambda r: r).toDF()` and write the result with `mode('overwrite')`.

Question 9

A data engineer needs to create a new Delta table that contains all columns from an existing Parquet-based table but with an additional column `ingestion_date` set to the current date. The engineer wants to minimize data movement and avoid rewriting all data. Which SQL command should they use?

Accepted Answer

CREATE TABLE new_table AS SELECT *, current_date() AS ingestion_date FROM parquet.`/path/to/table`;. The correct command is a CREATE TABLE AS SELECT that reads the Parquet data and adds the new column. This creates a new Delta table with the desired schema and data. Other options either attempt to alter a non-Delta table, create an empty table, or convert without backfilling the new column.

Answer

CONVERT TO DELTA parquet.`/path/to/table`; ALTER TABLE delta.`/path/to/table` ADD COLUMN ingestion_date DATE DEFAULT current_date();

Answer

ALTER TABLE parquet.`/path/to/table` ADD COLUMN ingestion_date DATE DEFAULT current_date();

Answer

CREATE TABLE new_table LIKE parquet.`/path/to/table`; ALTER TABLE new_table ADD COLUMN ingestion_date DATE; UPDATE new_table SET ingestion_date = current_date();

Question 10

A data engineer is modeling a slowly changing dimension (SCD) Type 2 in Delta Lake for a `dim_customer` table. The table has columns `customer_key`, `customer_id`, `name`, `address`, `effective_date`, `end_date`, and `is_current`. A nightly job receives a batch of updated customer records. The engineer must close the current row and insert a new row only when tracked attributes change, while avoiding duplicate current rows. Which MERGE statement correctly implements this SCD Type 2 update?

Accepted Answer

MERGE INTO dim_customer AS t USING updates AS s ON t.customer_id = s.customer_id AND t.is_current = true WHEN MATCHED AND (t.name <> s.name OR t.address <> s.address) THEN UPDATE SET t.end_date = current_date(), t.is_current = false WHEN NOT MATCHED THEN INSERT (customer_key, customer_id, name, address, effective_date, end_date, is_current) VALUES (monotonically_increasing_id(), s.customer_id, s.name, s.address, current_date(), null, true); followed by a second MERGE that inserts changed source rows whose current target row was just expired. Delta Lake MERGE evaluates the source against the target once, so a row matched and updated in the same statement cannot also be inserted. Implementing SCD Type 2 therefore requires either a two-step MERGE or a staging pattern that separates expiration from insertion. The two-pass approach reliably closes the old current row and creates the new version, keeping one active record per customer.

Answer

MERGE INTO dim_customer AS t USING updates AS s ON t.customer_id = s.customer_id WHEN MATCHED AND t.is_current = true AND (t.name <> s.name OR t.address <> s.address) THEN UPDATE SET t.end_date = current_date(), t.is_current = false WHEN NOT MATCHED THEN INSERT (customer_key, customer_id, name, address, effective_date, end_date, is_current) VALUES (s.customer_key, s.customer_id, s.name, s.address, current_date(), null, true)

Answer

MERGE INTO dim_customer AS t USING updates AS s ON t.customer_id = s.customer_id WHEN MATCHED AND t.is_current = true AND (t.name <> s.name OR t.address <> s.address) THEN UPDATE SET t.end_date = current_date(), t.is_current = false WHEN NOT MATCHED THEN INSERT *

Answer

MERGE INTO dim_customer AS t USING updates AS s ON t.customer_id = s.customer_id AND t.is_current = true WHEN MATCHED AND (t.name <> s.name OR t.address <> s.address) THEN UPDATE SET t.end_date = current_date(), t.is_current = false WHEN NOT MATCHED THEN INSERT (customer_key, customer_id, name, address, effective_date, end_date, is_current) VALUES (monotonically_increasing_id(), s.customer_id, s.name, s.address, current_date(), null, true)

Question 11

A data engineer has a Delta table partitioned by region with a nested struct column named address containing fields city and postal_code. Analysts frequently filter on address.city. The engineer wants to store the city value as a separate top-level column to speed up these queries without rewriting the entire table. Which Delta Lake feature should be used?

Accepted Answer

Add a generated column defined as address.city and let Delta compute it on write.. Generated columns derive a new top-level column from an existing expression, in this case address.city, and Delta populates it automatically on write. The derived column can then participate in data skipping and be filtered directly, improving analyst query performance. New writes populate it automatically, and existing rows can be backfilled by rewriting the relevant files.

Answer

Repartition the table by address.city using INSERT OVERWRITE.

Answer

Use schema evolution with mergeSchema to promote the nested field to a top-level column.

Answer

Create a view that selects address.city as city and query the view instead of the table.

Question 12

A data engineer is working with a Delta table `events` that has a schema with columns `event_id`, `user_id`, `event_type`, and `event_timestamp`. The table is partitioned by `event_date` (derived from `event_timestamp`). The engineer needs to optimize the table for queries that frequently filter on `user_id` and `event_type`. Which two actions should the engineer take to improve query performance? (Choose two.)

Accepted Answer

Run OPTIMIZE events;. To improve query performance for filters on user_id and event_type, the engineer should run OPTIMIZE with ZORDER BY on those columns, which co-locates related data and enables data skipping. Additionally, running OPTIMIZE without ZORDER compacts small files, which is a general performance improvement. These two actions together optimize file layout and data skipping.

Answer

Run VACUUM events RETAIN 0 HOURS;

Answer

Enable Delta Lake change data feed on the events table.

Answer

Run OPTIMIZE events ZORDER BY (event_date);

Question 13

A data engineer is designing a Delta Lake table that will store transactional data. The table will be frequently queried by a composite key consisting of transaction_date (date) and customer_id (integer). The engineer wants to optimize query performance and reduce the amount of data scanned. The table is expected to grow to several terabytes. Which Delta Lake feature should be used to achieve this?

Accepted Answer

Use Z-Ordering on the columns transaction_date and customer_id to co-locate related data and improve data skipping.. Z-Ordering is the optimal feature for optimizing queries that filter on multiple columns, such as transaction_date and customer_id. It co-locates related data within files, allowing Delta Lake to use file-level statistics to skip irrelevant files. This reduces the amount of data scanned and improves query performance without the downsides of partitioning by high-cardinality columns. Partitioning by date alone would not fully optimize queries that also filter by customer_id, and partitioning by customer_id would cause small file issues.

Answer

Partition the table by customer_id only, because customer_id is a high-cardinality column and partitioning by high-cardinality columns is recommended.

Answer

Enable liquid clustering on the table using the columns transaction_date and customer_id to automatically optimize data layout.

Answer

Partition the table by transaction_date only, as partitioning by date is a common best practice for time-series data.

Question 14

A data engineer is implementing a Delta Live Tables pipeline that processes streaming data from a cloud storage location. The pipeline must ensure exactly-once processing semantics and handle late-arriving data. The engineer needs to configure the pipeline to achieve these goals. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Set the pipeline's storage location to a persistent cloud storage path to maintain checkpoints and state across runs.. To achieve exactly-once processing and handle late-arriving data in a Delta Live Tables pipeline, the engineer must ensure that the pipeline's state is persisted and that the streaming source is configured correctly. Setting a persistent storage location maintains checkpoints and state across runs, preventing data loss or duplication. Using STREAMING LIVE TABLE with cloudFiles and a schema location enables reliable ingestion with schema tracking. These two actions together provide the necessary foundation for exactly-once semantics and late data handling.

Answer

Configure the pipeline to use a checkpoint location in DBFS that is unique per run to avoid conflicts.

Answer

Enable auto-optimization on the target Delta tables to improve write performance and reduce file fragmentation.

Answer

Set the pipeline mode to continuous to ensure that the pipeline runs without interruption and processes data as it arrives.

Question 15

A data engineer is designing a Delta Lake Bronze-to-Silver pipeline in Databricks and needs to ensure that downstream consumers receive high-quality data. Which TWO data quality enforcement mechanisms are natively supported in Delta Live Tables using expectations?

Accepted Answer

CONSTRAINT expectation_name EXPECT (column_name IS NOT NULL) ON VIOLATION DROP ROW. Delta Live Tables provides native expectation syntax to monitor and enforce data quality constraints directly within pipeline definitions. Data engineers can configure expectations to either drop invalid records or fail the pipeline execution when violations occur, ensuring robust governance. These declarative validation rules are critical for maintaining trusted data assets across modern enterprise Medallion architectures without writing complex custom validation code.

Answer

ASSERT expectation_name ON VIOLATION RETRY

Answer

FILTER expectation_name WHERE column_name IS NOT NULL

Answer

VALIDATE expectation_name ON ERROR IGNORE