Databricks-DE-Assoc Data Transformation and Modeling Practice Test 1 — 15 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?

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

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?

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

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.