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

Question 1

A data engineer is designing a Bronze-to-Silver transformation pipeline using Delta Lake. They need to ensure that the Silver table contains only records where the 'transaction_id' is not null and the 'amount' is positive. Which technique best ensures data quality at this stage?

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

Accepted Answer

Use a filter transformation in the Spark Structured Streaming query before writing to the target table.. Implementing Delta Lake expectations or inline 'where' clauses during the write process is critical for maintaining high-quality Silver layers. By filtering records before they are committed, you prevent corrupt data from propagating to downstream analytical tables. This pattern is fundamental to the Medallion Architecture, ensuring the Silver layer serves as a reliable, cleaned source of truth for downstream consumption and complex modeling tasks.

Answer

Apply a post-load DELETE statement to the Silver table after every micro-batch.

Answer

Define a separate table for rejected records and use manual SQL queries to move them later.

Answer

Set the table property 'delta.constraints.check' to filter incoming null values.

Question 2

A data engineer is migrating legacy batch jobs to Delta Live Tables (DLT). They want to optimize performance for a complex join operation between two large tables. Which TWO strategies should they implement to improve the join efficiency?

Accepted Answer

Use Z-Ordering on the columns frequently used in the JOIN clause.. Optimizing joins in DLT involves physical data layout tuning and resource management. Z-Ordering helps reduce the data scanned during join operations, while enabling Auto-Optimize allows the system to manage file sizes effectively. These techniques are essential in Databricks environments to reduce I/O overhead and minimize the execution time of expensive join operations, directly impacting the cost and performance of large-scale production data pipelines.

Answer

Increase the number of partitions to the maximum allowed by the cluster size.

Answer

Convert the tables to temporary views before executing the join.

Answer

Disable the Delta Lake cache to ensure data is read from cloud storage.

Question 3

You are tasked with handling late-arriving data in a streaming pipeline that performs windowed aggregations. Which approach ensures that the output remains accurate while balancing memory usage?

Accepted Answer

Implement a watermark on the timestamp column and aggregate over a window.. Watermarking is the primary mechanism to handle late-arriving data. It allows the system to define a threshold for how long state should be maintained for late updates. By setting an appropriate watermark duration, you balance the requirement for accuracy (including late data) with the need for memory efficiency (cleaning up expired state). This is a foundational concept for robust streaming ETL on Databricks.

Answer

Increase the state TTL to infinity to ensure no data is ever dropped.

Answer

Use the 'complete' output mode to ensure all historical data is reprocessed.

Answer

Filter out records with timestamps older than the current processing time.

Question 4

When implementing a Medallion Architecture, what is the primary purpose of the 'Silver' layer?

Accepted Answer

To provide a cleaned and integrated source of truth for business reporting.. The Silver layer acts as the validated, cleaned, and enriched integration layer. It transforms raw Bronze data into a usable state by applying data quality checks, schema enforcement, and deduplication. This layer serves as the foundation for downstream modeling, allowing data scientists and analysts to work with reliable data without needing to handle the noise and structural inconsistencies inherent in raw data sources.

Answer

To store raw, unmodified data in its original format from source systems.

Answer

To act as a sandbox environment for ad-hoc exploration and training.

Answer

To aggregate data into pre-calculated metrics for high-performance dashboards.

Question 5

A team is preparing to optimize their Databricks data transformation pipeline. Which THREE of the following actions are considered best practices for optimizing Delta Lake performance?

Accepted Answer

Run the OPTIMIZE command to compact small files into larger files.. Optimizing Delta Lake performance requires a holistic approach involving file management, data layout, and efficient querying. Using OPTIMIZE for compaction, Z-Ordering for query locality, and avoiding excessive partitioning are standard practices. These techniques reduce the metadata overhead and I/O costs, leading to faster query execution and lower costs, which are essential for maintaining scalable and performant data pipelines in large enterprise environments.

Answer

Partition the data by high-cardinality columns like 'user_id' or 'transaction_id'.

Answer

Perform a 'VACUUM' operation with a retention period of zero to save costs.

Question 6

Refer to the exhibit. The merge operation is failing in your pipeline. What is the root cause of this error, and how should it be resolved?

Accepted Answer

The source DataFrame must be aggregated or deduplicated to ensure one match per target ID.. The error occurs because the join condition in the MERGE statement is not unique relative to the source data. When the source contains multiple records with the same identifier that exists in the target, the engine cannot decide which source record to use for the update. To resolve this, you must deduplicate the source data before performing the merge, ensuring each key has exactly one entry.

Answer

The target table does not have a primary key defined.

Answer

The target table is too large to perform a MERGE and should be replaced with a full overwrite.

Answer

The cluster configuration needs more memory to handle the join operation.

Question 7

You are building a pipeline where a Bronze table contains JSON data with a nested 'user_info' struct. You need to promote this to a Silver table where 'user_id' is a top-level column. Which approach is the most efficient for this transformation?

Accepted Answer

Use the select() transformation with dot notation, e.g., 'user_info.user_id'.. Using Spark's native column selection and dot notation is the most efficient way to flatten nested structures. This avoids complex UDFs or manual parsing, which are slow and difficult to maintain. By flattening data early in the pipeline, downstream consumers can easily access specific fields without repeatedly parsing the nested JSON structure, which enhances the usability of the Silver-layer data.

Answer

Write a custom Python UDF to parse the JSON string and extract the field.

Answer

Cast the entire column to a String type and use regex to extract the ID.

Answer

Convert the DataFrame to an RDD to iterate over rows and manually extract values.

Question 8

Your organization requires that all data processing pipelines enforce a strict schema to prevent corrupt data from landing in the Silver layer. Which feature should be configured to ensure that only data matching the expected schema is written?

Accepted Answer

Rely on the default Delta Lake schema enforcement mechanism.. Schema enforcement (often called schema validation) is the standard mechanism in Delta Lake to protect downstream tables. By setting 'mergeSchema' to false or relying on default behavior, you ensure that any incoming data that deviates from the predefined target schema will cause the pipeline to fail. This is critical for data quality in production, preventing unexpected data structures from breaking analytical models and downstream dashboards.

Answer

Enable 'spark.databricks.delta.schema.autoMerge.enabled'.

Answer

Use a JSON schema file during the read process to discard invalid records.

Answer

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

Question 9

What is the primary benefit of using Data Live Tables (DLT) for managing dependencies between tables in a pipeline?

Accepted Answer

It automatically manages the dependency graph and ensures execution order.. DLT automatically manages the Directed Acyclic Graph (DAG) of the entire data pipeline. By defining table relationships through code, DLT handles the execution order and ensures dependencies are met. This simplifies pipeline management and maintenance, reducing the need for manual orchestration and ensuring that data is always processed in the correct order across the entire Medallion architecture.

Answer

It allows for manual control of the Spark cluster settings for every single step.

Answer

It eliminates the need for any data transformation logic to be written in SQL.

Answer

It forces all data to be stored in the Gold layer for final reporting.

Question 10

A data engineer wants to use 'Expectations' in Delta Live Tables to monitor data quality. What happens if a record violates an expectation defined with the 'fail' constraint?

Accepted Answer

The pipeline execution stops immediately, and the batch is not committed.. In DLT, the 'fail' expectation is a strict data quality gate. If any record in a micro-batch fails the validation, the entire pipeline will stop processing, and the current batch will fail. This prevents bad data from reaching the target, which is essential for pipelines where data integrity is the highest priority. It forces the developer to address the underlying data quality issues before the pipeline can continue.

Answer

The record is automatically moved to a side table for manual review.

Answer

The record is dropped, and the pipeline continues to process the next record.

Answer

The error is logged, and the pipeline continues to process the batch.

Question 11

Refer to the exhibit. The streaming pipeline is experiencing memory issues because the state store size increases continuously. What is the most effective way to address this while maintaining aggregation accuracy?

Accepted Answer

Add a watermark to the stream and use a windowed aggregation instead of a global count.. Continuous state growth is a common issue when grouping by high-cardinality keys like 'user_id' in streaming without time-bound constraints. By introducing a watermark and a window, you inform Spark which state is eligible for eviction. This allows the engine to periodically clean up expired state, ensuring the pipeline remains stable and memory usage stays within the bounds of the allocated cluster resources.

Answer

Increase the cluster memory and executor count indefinitely.

Answer

Change the output mode to 'Append' to flush the state to the sink immediately.

Answer

Use the 'dropDuplicates' method instead of 'groupBy' to simplify the state.

Question 12

A data engineer is designing a Delta Live Tables (DLT) pipeline. They need to ensure that records with missing values in the 'customer_id' column are dropped during the ingestion process. Which constraint syntax should be used?

Accepted Answer

@expect_or_drop(customer_id IS NOT NULL). The EXPECT DROP VIOLATION constraint is a native DLT feature designed specifically for data quality enforcement. By applying this, the pipeline automatically discards rows that fail the specified predicate while allowing valid records to proceed. This approach is essential in production data engineering to maintain data integrity and prevent downstream errors caused by null values, ensuring only high-quality data enters the silver or gold tables.

Answer

@expect_or_fail(customer_id IS NOT NULL)

Answer

@expect(customer_id IS NOT NULL)

Answer

@filter(customer_id IS NOT NULL)

Question 13

Which TWO of the following statements correctly describe the behavior of the Delta Lake 'MERGE' operation when handling schema evolution?

Accepted Answer

The 'mergeSchema' option must be enabled for any new columns to be added to the target table during a MERGE.. Schema evolution in Delta Lake allows for flexible data structures. By using MERGE with specific options, engineers can automate updates to target table structures as incoming data evolves. Understanding these behaviors is critical for maintaining robust ELT pipelines where source system changes might otherwise break downstream consumers or cause data ingestion failures that require manual schema repairs.

Answer

MERGE operations automatically drop columns in the target table if they are missing from the source DataFrame.

Answer

MERGE operations support renaming columns in the target table using the 'RENAME' keyword inside the 'WHEN MATCHED' clause.

Answer

The 'overwriteSchema' option is required for MERGE to successfully insert new columns into the target table.

Question 14

Refer to the exhibit. A data engineer is attempting to ingest JSON data into a Delta table. Based on the error log, what is the most appropriate transformation step to implement before loading this data into the production table?

Accepted Answer

Apply a 'coalesce' function or a 'filter' transformation on the DataFrame before writing to the Delta table.. The exhibit shows a failure due to null values in a required field during ingestion. In production pipelines, failing at the ingestion layer stops data flow. Implementing a transformation step to handle nulls, either by providing default values or filtering, ensures the pipeline is resilient. This is a core Data Engineering principle: handle dirty source data before it reaches the target, preventing ingestion failures.

Answer

Use the 'DROP TABLE' command to clear the target and recreate it with nullable constraints.

Answer

Modify the target table schema using 'ALTER TABLE' to change the column to nullable.

Answer

Set the 'spark.sql.execution.nullValue' configuration to 'ignore' for the session.

Question 15

Which of the following is the primary benefit of using 'Auto Loader' (cloudFiles) for data ingestion in Databricks compared to standard batch processing?

Accepted Answer

It eliminates the need to perform manual directory listing by tracking file state.. Auto Loader is designed for scalable, incremental ingestion from cloud object storage. It maintains its own state, allowing it to pick up new files without performing expensive directory listings. This functionality is essential for data engineers managing high-frequency data streams where traditional batch processing would become increasingly inefficient and costly as the number of files in storage grows over time.

Answer

It automatically cleans and normalizes all incoming data formats using AI.

Answer

It is the only method that supports reading data from Delta tables.

Answer

It requires less compute resources than a simple 'spark.read' command.