Databricks-DE-Pro Data Transformation, Cleansing, Quality — Timed 25 Questions

Question 1

A Data Engineer is building a pipeline using Delta Live Tables (DLT) to clean IoT sensor data. Which TWO of the following statements regarding the implementation of Expectations are correct?

Accepted Answer

Expectations must be applied using standard Python decorators within the DLT pipeline code.. DLT Expectations provide a declarative way to handle data quality in both SQL and Python. There are three primary policies: 'expect' (logs the violation but allows the record to pass), 'expect or drop' (logs the violation and drops the record), and 'expect or fail' (logs the violation and fails the update). This flexibility allows engineers to monitor data quality (using the 'expect' policy) without interrupting the pipeline flow or losing data.

Answer

When an expectation fails, the entire DLT pipeline will stop processing immediately.

Answer

Expectations are only compatible with Bronze-layer tables in a Medallion architecture.

Answer

Expectations require a manual SQL job to trigger the validation logic on every batch.

Question 2

Refer to the exhibit. A pipeline job is failing because of a schema mismatch between the source data and the Delta table. Which solution allows the pipeline to succeed without manually changing the source data or the existing table schema?

Accepted Answer

Transform the DataFrame by explicitly casting the 'ts' column to a TIMESTAMP type before the write operation.. The explanation incorrectly discusses 'mergeSchema' and 'evolving data', which are irrelevant to resolving a type mismatch (e.g., String vs Timestamp). The correct answer (B) is correct because explicit casting resolves the type conflict at the DataFrame level before the write operation, which is the only way to handle this without schema evolution or manual table changes.

Answer

Set the spark configuration 'spark.databricks.delta.schema.autoMerge.enabled' to true.

Answer

Run an ALTER TABLE command to change the 'ts' column type to STRING before the job starts.

Answer

Use 'overwriteSchema' to force the Delta table to adapt to the incoming DataFrame.

Question 3

You are optimizing a pipeline that processes high-volume JSON data. Which THREE techniques will improve the performance and quality of the transformation layer?

Accepted Answer

Convert the JSON source into Parquet during the Bronze layer ingestion.. To optimize a pipeline processing high-volume JSON data, you should convert the raw data into an efficient columnar format like Parquet (Option B), explicitly define schemas using DDL strings with functions like 'from_json' or Auto Loader options to ensure stability (Option D), and leverage Auto Loader's schema evolution capabilities rather than inferring schemas repeatedly. Option A is an anti-pattern for production scale.

Answer

Use 'cloudFiles.inferSchema' to automate schema discovery during ingestion.

Answer

Always set the 'numPartitions' to 1 to ensure that all data is processed in a single batch.

Answer

Perform a 'repartition' on every column before writing to the Delta table.

Question 4

A data engineering team is optimizing a massive Delta Lake table partitioned by date and clustered by customer_id using Liquid Clustering. The table experiences frequent updates and deletes based on streaming CDC feeds. Which underlying Delta Lake feature allows this pattern to perform efficiently without causing file compaction bottlenecks?

Accepted Answer

Delta Lake deletion vectors avoid rewriting entire parquet data files during row-level updates and deletes by recording changes in separate bitmap files.. Deletion vectors are a storage optimization that allows Delta Lake to mark rows as deleted or changed without rewriting the entire Parquet data file. In the traditional 'copy-on-write' model, updating a single row requires rewriting the whole file, which leads to write amplification and compaction bottlenecks during frequent CDC (Change Data Capture) operations. Deletion vectors store these changes in a separate bitmap file, significantly improving write performance for updates and deletes.

Answer

Predictive Optimization automatically compacts small files created by frequent CDC updates in the background without manual intervention.

Answer

Uniform (UniForm) automatically translates Delta Lake transaction logs into Iceberg metadata formats for external engine compatibility.

Answer

Z-ordering indices cluster data across multiple columns within partitions to maximize data skipping during read operations.

Question 5

Refer to the exhibit. A Data Engineer is reviewing a DLT configuration file. What is the impact of the 'on_violation' setting on the pipeline?

Accepted Answer

The pipeline halts execution entirely, preventing the current batch from being committed.. In Databricks Delta Live Tables, the 'on_violation' key does not exist as a top-level configuration. Data quality constraints are defined using the 'expectations' parameter, where the 'fail' constraint (e.g., 'expect_or_fail') causes the pipeline to halt. The question incorrectly references a non-existent configuration key.

Answer

The pipeline ignores the violation and continues processing, logging only the error.

Answer

Only the violating rows are removed from the result set, but the pipeline keeps running.

Answer

The pipeline flags the rows as invalid but writes them to the target table anyway.

Question 6

A Data Engineer needs to perform a complex data cleaning operation that involves joining a streaming table with a large, static lookup table. The lookup table is updated daily via a separate batch process. What is the most efficient way to ensure the join is performant and consistent within a DLT pipeline?

Accepted Answer

Use the broadcast join hint on the static lookup table to optimize performance.. Broadcasting is the most performant strategy for joining a streaming table with a static lookup table because it avoids a costly shuffle of the streaming data. While Spark's auto-broadcast threshold is 10MB, using a broadcast hint allows for larger tables (up to several GBs) to be broadcasted, provided they fit in the executor's memory. In a DLT pipeline, the static side of a stream-static join is re-evaluated for each micro-batch, ensuring that daily updates to the lookup table are picked up (consistency) without needing a stateful stream-to-stream join.

Answer

Perform a standard shuffle join to ensure accuracy with the daily updated data.

Answer

Convert the static lookup table into a streaming source and use a streaming-to-streaming join.

Answer

Cache the lookup table at the start of the pipeline and use it in a cross join.

Question 7

A data engineer is building a Delta Live Tables pipeline that ingests raw JSON events and must produce a cleansed Silver table. The pipeline must handle malformed records without failing, capture metrics about data quality, and allow downstream analysts to query only valid records. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Define the Silver table with @dlt.table and use @dlt.expect to track invalid records without dropping them, then create a separate view filtering valid records.. The pipeline must not fail on malformed records, must capture quality metrics, and must allow querying valid records. Using @dlt.expect with a separate view (or @dlt.expect_or_drop with a quarantine flow) achieves this. Both approaches record metrics and prevent pipeline failure. The combination of expect_or_drop plus quarantine also preserves invalid data for analysis, which is often required in production pipelines.

Answer

Define expectations using @dlt.expect_or_drop to filter out invalid records and record metrics in the event log.

Answer

Use @dlt.expect_or_fail to enforce strict quality and halt the pipeline on any invalid record.

Answer

Define expectations using the @dlt.expect decorator with the 'drop' action to remove invalid records from the Silver table.

Question 8

A Data Engineer is working with a Delta table that contains a column `email` with inconsistent casing and leading/trailing spaces. The engineer needs to standardize the email addresses by trimming spaces and converting to lowercase. Which SQL expression correctly transforms the `email` column in a SELECT statement?

Accepted Answer

SELECT lower(trim(email)) AS email_clean FROM table. The correct expression uses `trim` to remove leading and trailing spaces and `lower` to convert to lowercase, achieving standardization. The order of these functions is not critical, but the combination ensures the email is cleansed. Other options either miss case conversion, use verbose alternatives, or rely on regex that does not handle all whitespace or case.

Answer

SELECT trim(lower(email)) AS email_clean FROM table

Answer

SELECT lcase(rtrim(ltrim(email))) AS email_clean FROM table

Answer

SELECT regexp_replace(email, '^\s+|\s+$', '') AS email_clean FROM table

Question 9

A data engineer is implementing a data quality check on a streaming Delta table that receives late-arriving events. The engineer needs to detect records where the event timestamp is more than 2 hours behind the current watermark. Which approach correctly identifies such records for quarantine?

Accepted Answer

Use the event_time column and compare it to current_timestamp() - INTERVAL 2 HOURS in a filter condition.. To identify records that are more than 2 hours behind the current time, the engineer should compare the event timestamp to the current processing time. Using current_timestamp() - INTERVAL 2 HOURS provides a dynamic threshold that correctly flags late-arriving events. This approach works in streaming and batch, and does not rely on watermark internals.

Answer

Use a watermark on event_time and filter records where event_time < current_watermark - INTERVAL 2 HOURS.

Answer

Use the event_time column and compare it to the current watermark using the watermark() function.

Answer

Use the event_time column and compare it to the maximum event_time in the table minus 2 hours.

Question 10

A Data Engineer is building a Databricks job to cleanse a large dataset. The engineer needs to identify and handle duplicate records, missing values, and outliers. Which TWO techniques are most appropriate for addressing these data quality issues in a scalable way? (Choose two.)

Accepted Answer

Use Delta Lake's MERGE INTO to deduplicate records based on a unique key.. MERGE INTO and dropDuplicates() are both scalable, Spark-native techniques for handling duplicates. MERGE INTO allows for sophisticated deduplication with conditions, while dropDuplicates() efficiently removes exact duplicates. Other options involve non-scalable single-node operations or are not directly focused on deduplication and missing values in a scalable job context.

Answer

Implement outlier detection using the IQR method on a single node.

Answer

Use pandas to fill missing values with the mean of each column.

Answer

Use Delta Live Tables expectations to drop rows with null values.

Question 11

A Data Engineer is tasked with cleansing a large Delta table containing customer records. The table has columns `customer_id`, `email`, `signup_date`, and `country`. The engineer needs to identify and correct data quality issues such as invalid email formats, future signup dates, and inconsistent country codes. Which TWO approaches are most appropriate for performing these cleansing operations in Databricks? (Choose two.)

Accepted Answer

Use `spark.read.table("customers").withColumn("signup_date", when(col("signup_date") > current_date(), null).otherwise(col("signup_date")))` to nullify future dates and `when(col("country").isin("US", "USA", "United States"), "US").otherwise(col("country"))` to standardize country codes.. The scenario requires correcting invalid emails, future dates, and inconsistent country codes. Delta Live Tables expectations (B) provide a framework for defining and enforcing quality rules, and can be paired with transformations to correct data. Programmatic transformations (D) directly address future dates and country codes. Together, they form a robust cleansing approach. Filtering or dropping duplicates do not correct the issues.

Answer

Use `spark.sql("SELECT *, CASE WHEN email NOT RLIKE '...' THEN NULL ELSE email END AS cleaned_email FROM customers")` to replace invalid emails with NULL.

Answer

Use `spark.read.table("customers").filter(col("email").rlike("^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$"))` to filter out invalid emails.

Answer

Use `spark.read.table("customers").dropDuplicates()` to remove duplicate customer records.

Question 12

A Data Engineer is implementing a data quality framework in Databricks using Delta Live Tables (DLT). The framework must validate incoming data against multiple rules, track metrics, and ensure that critical violations halt the pipeline. Which TWO actions should the engineer take to meet these requirements? (Choose two.)

Accepted Answer

Use `@dlt.expect` to define non-critical expectations that log violations but allow processing to continue.. To track metrics for all violations while ensuring critical violations halt the pipeline, the engineer should use `@dlt.expect` for non-critical rules to log and continue, and `@dlt.expect_or_fail` for critical rules to fail the pipeline. These two actions together provide comprehensive validation and control. The other options either do not fail the pipeline or do not provide the necessary distinction between critical and non-critical rules.

Answer

Use `@dlt.expect_all` to define a set of expectations that are evaluated together, and fail the pipeline if any violation occurs.

Answer

Use `@dlt.expect_or_drop` to define expectations that drop invalid records and continue processing.

Answer

Use `@dlt.expect_all_or_drop` to define a set of expectations that drop records violating any rule and log metrics.

Question 13

A data engineer is using Delta Live Tables to build a pipeline that ingests CSV files with a known schema. The source files occasionally contain extra columns that are not in the schema. The engineer wants the pipeline to ignore these extra columns without failing. Which option should be set in the DLT table definition?

Accepted Answer

Set the option 'enforceSchema' to 'false'.. The 'enforceSchema' option, when set to false, tells the CSV reader to ignore columns in the file that are not present in the specified schema. This is the correct way to handle extra columns without failing. Other options either control malformed rows, apply to different file formats, or do not exist.

Answer

Set the option 'mode' to 'PERMISSIVE'.

Answer

Set the option 'ignoreExtraColumns' to 'true'.

Answer

Set the option 'mergeSchema' to 'true'.

Question 14

A data engineer is building a Delta Live Tables (DLT) pipeline to ingest and cleanse streaming data from a Kafka topic. The pipeline must ensure data quality by dropping records that fail validation rules and recording metrics about dropped records. Which two features should the engineer use to achieve this? (Choose two.)

Accepted Answer

Use the expect_all decorator with on_violation set to 'drop' to apply multiple expectations.. DLT provides expectations to enforce data quality. To drop invalid records, the on_violation action must be set to 'drop'. The expect decorator handles a single expectation, while expect_all applies multiple expectations. Both record metrics in the event log. Using 'fail' stops the pipeline, 'warn' keeps records, and custom code is unnecessary. Thus, the expect and expect_all decorators with drop action are correct.

Answer

Use the expect decorator with on_violation set to 'fail' to stop the pipeline on violation.

Answer

Implement a custom foreachBatch function to filter out invalid records and log metrics manually.

Answer

Use the expect decorator with on_violation set to 'warn' to log violations but keep records.

Question 15

A Data Engineer is implementing a data quality framework for a Delta table that receives streaming updates. The engineer needs to ensure that any record with a negative `amount` is not written to the table, but also wants to capture these invalid records in a separate quarantine table for later analysis. The pipeline must not fail on invalid records. Which approach using Delta Live Tables achieves this?

Accepted Answer

Define two expectations: one with `@dp.expect_or_drop("valid_amount", "amount >= 0")` on the main table, and another with `@dp.expect("invalid_amount", "amount < 0")` on a separate quarantine table that reads from the same source.. To drop invalid records from the main table while capturing them in a quarantine table, use `expect_or_drop` on the main table and define a separate quarantine table that filters the source for invalid records. This keeps the pipeline running and provides a repository for bad data. Using `expect_all_or_fail` would halt the pipeline, and relying on the event log does not preserve the actual records.

Answer

Use `@dp.expect_or_drop("valid_amount", "amount >= 0")` on the main table, and rely on the event log to retrieve dropped records for quarantine.

Answer

Use `@dp.expect_all_or_fail({"valid_amount": "amount >= 0"})` on the main table, and configure a separate quarantine table using `@dp.expect_all({"invalid_amount": "amount < 0"})`.

Answer

Use `@dp.expect("valid_amount", "amount >= 0")` on the main table, and create a quarantine table that uses `@dp.expect_or_drop("invalid_amount", "amount < 0")`.

Question 16

A Data Engineer is designing a Delta Live Tables pipeline that ingests data from a Kafka stream. The pipeline must ensure that records with a null 'transaction_id' are dropped, and records where 'amount' is less than 0 are flagged but still processed. Additionally, the pipeline should quarantine records that fail both conditions. Which combination of DLT expectations and pipeline settings achieves this?

Accepted Answer

Define two expectations: @dlt.expect_or_drop("valid_transaction_id", "transaction_id IS NOT NULL") and @dlt.expect("non_negative_amount", "amount >= 0"), and create a separate quarantine table that captures records failing both conditions.. The correct approach uses expect_or_drop for transaction_id to drop nulls, expect for amount to flag negatives without dropping, and a separate quarantine table to capture records failing both conditions. This satisfies all requirements: dropping null transaction_id, flagging negative amounts, and quarantining records that fail both. Other options either use inappropriate expectation types or assume non-existent built-in quarantine features.

Answer

Use @dlt.expect_all_or_drop({"valid_transaction_id": "transaction_id IS NOT NULL", "non_negative_amount": "amount >= 0"}) and configure a quarantine table in the pipeline settings.

Answer

Use @dlt.expect_or_fail("valid_transaction_id", "transaction_id IS NOT NULL") and @dlt.expect_or_drop("non_negative_amount", "amount >= 0") and rely on DLT's automatic quarantine of failed records.

Answer

Use @dlt.expect_or_drop("valid_transaction_id", "transaction_id IS NOT NULL") and @dlt.expect("non_negative_amount", "amount >= 0") on the same table, and enable quarantine via pipeline configuration.

Question 17

A Data Engineer is using Databricks SQL to cleanse a table `sales` that has a column `amount` stored as a string, but it contains numeric values with dollar signs and commas. The engineer needs to convert this column to a decimal type for analysis. Which expression correctly casts the cleaned string to DECIMAL(10,2)?

Accepted Answer

CAST(regexp_replace(amount, '[$,]', '') AS DECIMAL(10,2)). The correct expression uses `regexp_replace` to remove both dollar signs and commas in one step, then casts to DECIMAL(10,2). This accurately cleans the string by stripping the unwanted characters. The other options either miss removing commas, use overly broad regex, or misuse the `translate` function, leading to incorrect or failed conversions.

Answer

CAST(regexp_replace(amount, '[^0-9.]', '') AS DECIMAL(10,2))

Answer

CAST(replace(amount, '$', '') AS DECIMAL(10,2))

Answer

CAST(translate(amount, '$,', '') AS DECIMAL(10,2))

Question 18

A data engineer is tasked with cleaning a dataset in Databricks. The dataset contains a column 'email' with some null values. The engineer wants to replace nulls with a default value 'unknown@example.com'. Which PySpark function should be used?

Accepted Answer

df.na.fill({'email': 'unknown@example.com'}). The na.fill function is specifically designed to replace null values in a DataFrame. By passing a dictionary with column names and replacement values, it efficiently replaces nulls in the specified columns. This is the most concise and idiomatic approach in PySpark for null replacement. Other methods like dropna remove rows, and using when/otherwise is more verbose. Therefore, na.fill is the correct choice.

Answer

df.dropna(subset=['email'])

Answer

df.withColumn('email', when(col('email').isNull(), 'unknown@example.com').otherwise(col('email')))

Answer

df.fillna('unknown@example.com', subset=['email'])

Question 19

A Data Engineer needs to enforce a NOT NULL constraint on a specific column in a Delta table while maintaining the ability to perform high-performance streaming writes. Which approach is the most efficient and native method to ensure this data quality requirement?

Accepted Answer

Add a CHECK constraint to the table using the ALTER TABLE ADD CONSTRAINT command.. Using Delta Lake's CHECK constraints is the most efficient way to enforce data quality at write time. By defining a constraint, the Delta engine validates every incoming row against the business logic before committing the transaction. This avoids post-process cleanup jobs, reduces storage waste, and ensures that downstream consumers always receive valid data, which is critical for maintaining robust data pipelines in a Lakehouse architecture.

Answer

Apply a filter transformation in the DataFrame after the data is written to the table.

Answer

Use an external Delta Live Tables expectation to quarantine bad records.

Answer

Perform a manual check within the Spark Structured Streaming loop before appending to the sink.

Question 20

Refer to the exhibit. A Data Engineer is attempting to merge data into a table with these constraints defined. If the incoming batch contains rows that violate these rules, what is the default behavior of the Delta Lake engine during the merge operation?

Accepted Answer

The transaction fails entirely, and no changes are committed to the Delta table.. Delta Lake maintains strict ACID compliance. When a transaction violates defined CHECK or NOT NULL constraints, the engine throws an error, and the entire transaction is rolled back. This behavior prevents data corruption and ensures that only valid state transitions occur. This is essential for maintaining data integrity in complex ETL pipelines where maintaining a 'known good' state is preferred over partial updates or silent failures.

Answer

The engine silently ignores the invalid rows and completes the merge for valid records only.

Answer

The invalid rows are automatically routed to a hidden sidecar table for later review.

Answer

The engine automatically updates the invalid rows to the default column value.