Databricks-DE-Assoc Data Transformation and Modeling — Timed 25 Questions

Question 1

A data engineer is processing streaming data using Delta Live Tables (DLT) in Python and needs to append incoming records to an existing Delta table without modifying historical records. Which declarative table decorator should be used?

25 minute time limit — choose an answer to begin.

Accepted Answer

@dlt.table. Delta Live Tables uses the @dlt.table decorator for both batch and streaming tables. Streaming behavior is defined by using the read_stream() function within the function body. There is no @dlt.append_flow decorator in the DLT Python API.

Answer

@dlt.view

Answer

@dlt.materialized_view

Answer

@dlt.append_flow

Question 2

A data engineer is writing a PySpark script to transform a DataFrame and needs to compute rolling window statistics across ordered time-series events. Which Spark SQL function should be used in combination with window specifications to assign a unique sequential rank to rows within a partition without gaps?

Accepted Answer

row_number(). The question asks for a function that assigns a unique sequential rank without gaps. Both row_number() and dense_rank() do not have gaps, but row_number() assigns a *unique* sequential rank (1, 2, 3...) to every row even if values are identical, whereas dense_rank() assigns the same rank to ties. The stem specifically requests to 'assign a unique sequential rank to rows within a partition without gaps', which is the exact definition of row_number().

Answer

rank()

Answer

dense_rank()

Answer

ntile()

Question 3

Refer to the exhibit. An engineer is attempting to perform a windowed aggregation on an incoming stream, but the pipeline fails with the provided error. What is the most likely cause of this error?

Accepted Answer

The source data format does not have a timestamp field, and no casting was performed.. The explanation mentions an 'event_time' column is missing, but the correct option (B) refers to the source data format lacking a timestamp field. Furthermore, the explanation incorrectly suggests that schema evolution prevents this, whereas the issue is a fundamental requirement for windowing operations in Structured Streaming.

Answer

The Delta table has not been vacuumed, causing schema drift.

Answer

The watermark definition is missing in the streaming write options.

Answer

The cluster is running an outdated Spark version that does not support windowing.

Question 4

A data engineer needs to perform an UPSERT operation on a Delta table. Which command is the correct way to achieve this functionality?

Accepted Answer

MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *. In Delta Lake SQL, the 'WHEN MATCHED THEN UPDATE SET *' and 'WHEN NOT MATCHED THEN INSERT *' clauses do not support the '*' wildcard. The columns must be explicitly specified (e.g., 'SET target.col = source.col' or 'INSERT (col1, col2) VALUES (source.col1, source.col2)').

Answer

UPDATE target SET * FROM source WHERE target.id = source.id

Answer

INSERT INTO target SELECT * FROM source ON DUPLICATE KEY UPDATE

Answer

APPEND INTO target FROM source WHERE NOT EXISTS (SELECT 1 FROM target WHERE target.id = source.id)

Question 5

You are auditing a Databricks environment and notice that the Silver tables contain massive amounts of historical data that is no longer needed. Which TWO operations should be used to safely remove this data while keeping the Delta Lake ACID consistency?

Accepted Answer

Use the VACUUM command on the Delta table.. The DELETE command is used to remove specific rows from a table, which triggers a rewrite of the affected data files and creates a new version in the transaction log. VACUUM is then required to physically remove the old, unreferenced data files from storage to reclaim space. Together, they ensure data is removed while maintaining ACID consistency.

Answer

Manually delete Parquet files from the underlying cloud storage (S3/ADLS).

Answer

Run an ALTER TABLE DROP PARTITION command to remove old data files instantly.

Answer

Recreate the table by overwriting it with a subset of the original data.

Question 6

Which DLT feature should a data engineer use to ensure that the Silver layer remains highly available and that data lineage is automatically tracked?

Accepted Answer

Define tables using 'CREATE OR REFRESH STREAMING TABLE' syntax.. The explanation mentions 'expectations' (which are for data quality) rather than explaining why 'CREATE OR REFRESH STREAMING TABLE' is the correct syntax for DLT pipelines to ensure high availability and lineage tracking. The explanation should focus on how DLT pipelines manage infrastructure and metadata automatically.

Answer

Use standard Spark SQL 'CREATE TABLE' statements.

Answer

Manually create a pipeline using 'dbutils.notebook.run' for each stage.

Answer

Execute the transformation using a Databricks Workflow job.

Question 7

Which THREE of the following are valid ways to define schemas in a Databricks data engineering pipeline?

Accepted Answer

Providing a DDL-formatted string to the 'schema' parameter in a read operation.. Option A (DDL strings) and Option B (StructType) are standard Spark/Databricks methods. Option D is a valid architectural pattern where schemas are stored as metadata (JSON) and converted to StructType at runtime. Option C is incorrect because schema inference is discouraged in production due to performance overhead and potential schema drift. Option E is incorrect because ALTER TABLE modifies existing table metadata rather than defining the schema for an ingestion operation.

Answer

Relying on 'spark.read.inferSchema=true' for all production tables.

Answer

Using the 'ALTER TABLE' command to define the schema before ingestion.

Question 8

A data engineer is tasked with reading a legacy CSV dataset with inconsistent column formats. Which approach ensures the most reliable data ingestion into a Delta table?

Accepted Answer

Define a strict schema and use the 'rescuedDataColumn' option.. When dealing with legacy, inconsistent data, defining a strict schema combined with the 'rescuedDataColumn' option (such as '_rescued_data') ensures that any data that doesn't match the expected schema is not silently dropped or causes the job to fail. Instead, it is captured in the rescued data column for later inspection, providing a robust and reliable ingestion pipeline.

Answer

Use the 'permissive' mode and allow the pipeline to continue with nulls.

Answer

Convert the entire CSV to Parquet before reading, assuming it fixes formatting.

Answer

Use a regex-based parser in a UDF to clean the data before schema application.

Question 9

A data engineer is designing a pipeline using Delta Live Tables (DLT) to process streaming data. The engineer needs to ensure that only records with a valid 'user_id' are processed, while records without one are quarantined for further analysis. Which DLT constraint is best suited for this requirement?

Accepted Answer

CONSTRAINT user_id_check EXPECT (user_id IS NOT NULL) ON VIOLATION DROP ROW. To process only valid records and exclude (quarantine/drop) invalid ones without failing the entire pipeline, you use the 'ON VIOLATION DROP ROW' clause. This drops the invalid records from the target dataset while the pipeline continues to run successfully.

Answer

EXPECT (user_id IS NOT NULL)

Answer

CONSTRAINT user_id_check EXPECT (user_id IS NOT NULL) ON VIOLATION FAIL UPDATE

Answer

EXPECT (user_id IS NOT NULL) ON VIOLATION FAIL UPDATE

Question 10

A data engineer is designing a Gold-level table. Which THREE of the following are best practices to ensure optimal performance and usability for downstream BI reporting?

Accepted Answer

Apply Z-Ordering on commonly used filter columns.. While Z-Ordering is correct, using high-cardinality columns for partitioning (Option D) is a known anti-pattern in Delta Lake as it leads to the 'small file problem'. The explanation correctly identifies that Gold tables should be aggregated and optimized, but the justification for partitioning needs to be clear that low-cardinality columns are preferred for partitioning, while high-cardinality columns should be Z-Ordered.

Answer

Include all columns from the Bronze table to ensure maximum flexibility.

Answer

Use high-cardinality columns for partitioning to improve filter performance.

Question 11

Which of the following describes the behavior of the 'MERGE INTO' command in Delta Lake when dealing with duplicate source records?

Accepted Answer

It raises an error and aborts the operation.. The MERGE INTO command fails if multiple source rows match the same target row. However, it does not fail if a single source row matches multiple target rows (this is a standard join behavior). The error specifically occurs when multiple source rows attempt to update or delete the same target row, which is non-deterministic.

Answer

It automatically picks the first record and ignores the others.

Answer

It performs an 'upsert' on all duplicate records in the target.

Answer

It updates the target once and inserts new rows for the remaining duplicates.

Question 12

A data engineer is tasked with ensuring that a Delta table's schema is enforced strictly during ingestion. Which feature should they use to reject any data that does not match the predefined schema?

Accepted Answer

Rely on default schema enforcement behavior during write. While schema enforcement is the default behavior, the question asks which 'feature' to use. The correct answer should explicitly reference 'Schema Enforcement' as a feature, not just 'relying on default behavior'. Furthermore, Option A is a common distractor, but the options provided do not include the actual mechanism (Schema Enforcement).

Answer

Enable 'mergeSchema' in the write option

Answer

Set the table property 'delta.columnMapping.mode' to 'none'

Answer

Use a UDF (User Defined Function) to validate data types

Question 13

A data engineer is processing streaming data using Delta Live Tables (DLT) in Python and needs to append incoming records to an existing Delta table without modifying historical records. Which declarative table decorator should be used?

25 minute time limit — choose an answer to begin.

Accepted Answer

spark.readStream.format("cloudFiles").option("cloudFiles.format", "json").option("cloudFiles.schemaEvolutionMode", "rescue").load(path). The correct parameter value to capture unexpected data in a rescue column is 'cloudFiles.rescuedDataColumn'. The 'cloudFiles.schemaEvolutionMode' option accepts values like 'addNewColumns', 'failOnNewColumns', or 'none'. To achieve the described behavior, one must set 'cloudFiles.schemaEvolutionMode' to 'addNewColumns' (or leave it default) and define 'cloudFiles.rescuedDataColumn'.

Answer

spark.readStream.format("cloudFiles").option("cloudFiles.format", "json").option("cloudFiles.schemaEvolutionMode", "failOnNewColumns").load(path)

Answer

spark.readStream.format("cloudFiles").option("cloudFiles.format", "json").option("cloudFiles.schemaEvolutionMode", "none").load(path)

Answer

spark.readStream.format("cloudFiles").option("cloudFiles.format", "json").option("cloudFiles.inferSchema", "false").load(path)

Question 14

A data engineer is designing a Delta Lake Bronze-to-Silver pipeline using Delta Live Tables (DLT) in Python. The engineer wants to ensure that incoming records are validated for data quality, dropping records that fail null checks on critical ID fields while failing the pipeline for invalid business logic. Which TWO DLT expectation decorators should be used to accomplish this?

Accepted Answer

@dlt.expect_or_drop("valid_id", "id IS NOT NULL"). Delta Live Tables expectations enforce data quality directly within transformation pipelines by specifying constraints on streaming datasets. The expectation modifier drop row allows the pipeline to silently discard invalid records while keeping the stream healthy, whereas fail update immediately halts execution when critical business invariants are violated. Selecting these precise decorators allows data engineers to balance fault tolerance against strict compliance rules.

Answer

@dlt.expect("valid_id", "id IS NOT NULL")

Answer

@dlt.drop_where("id IS NOT NULL")

Answer

@dlt.fail_update("valid_id", "id IS NOT NULL")

Question 15

Refer to the exhibit. A data engineer runs the provided Structured Streaming code to ingest CSV files using Auto Loader. The pipeline fails after a few hours with an AnalysisException stating that the inferred schema has changed due to a new column header in an incoming file. How should the engineer resolve this issue permanently?

Accepted Answer

Add .option("cloudFiles.schemaEvolutionMode", "rescue") to the readStream configuration.. To resolve schema evolution issues where new columns are added in incoming files, Auto Loader provides the 'addNewColumns' schema evolution mode, which allows the schema to automatically incorporate new columns without failing the query.

Answer

Delete the directory specified in the checkpointLocation option and restart the stream.

Answer

Set .option("mergeSchema", "true") on the writeStream portion of the Spark query.

Answer

Change the cloudFiles.format option from csv to json to automatically bypass schema checks.

Question 16

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 17

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 18

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 19

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 20

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