Databricks-DE-Pro Data Transformation, Cleansing, Quality Practice Test 2 — 15 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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.

Question 6

You are migrating a legacy CSV-based ETL process to Databricks. The source CSV files contain inconsistent date formats. Which approach provides the most scalable way to handle these inconsistencies during the bronze-to-silver transformation?

Accepted Answer

Use the 'to_timestamp' function with an array of acceptable formats to parse the column.. Using Spark's `to_timestamp` with multiple format strings or a custom UDF is the standard, scalable way to handle format drift. By applying this logic in the Silver layer, you preserve the raw data in the Bronze layer while ensuring the refined data is standardized. This strategy follows the Medallion architecture pattern, allowing for lineage tracking and the ability to reprocess data if requirements change or better parsing logic is developed.

Answer

Convert all columns to string type to avoid parsing errors, and handle formatting in the visualization tool.

Answer

Use the 'from_unixtime' function with a hardcoded string format for all records.

Answer

Delete all rows with invalid date formats using a 'drop' expectation in a DLT pipeline.

Question 7

You need to perform a deduplication task on a streaming source that includes late-arriving data. Which Delta Lake feature is best suited to manage this while ensuring efficient state cleanup?

Accepted Answer

Use the dropDuplicates() method with watermark settings to manage state.. The `withWatermark` and `dropDuplicates` combination is specifically designed for streaming deduplication. Watermarks tell the engine how long to wait for late-arriving data, allowing it to clear old state from memory. This is critical for high-volume streaming jobs where state growth would otherwise lead to out-of-memory errors or significant performance degradation, ensuring the job remains stable over long periods of execution.

Answer

Use a standard SQL DELETE query with a subquery to identify and remove duplicates.

Answer

Set the table property 'delta.enableChangeDataFeed' to true and filter on the change log.

Answer

Increase the 'spark.sql.shuffle.partitions' setting to ensure all duplicates land on the same node.

Question 8

A Data Engineer is implementing a medallion architecture. Which THREE steps are critical for effectively implementing a high-quality 'Silver' layer from 'Bronze' data?

Accepted Answer

Enforce strict schema validation on incoming Bronze files.. The Silver layer is the 'single source of truth' for refined data. By applying strict schema enforcement, deduplication, and standardized data types, you ensure that analytical tools receive reliable, high-quality information. Proper implementation here prevents the 'garbage in, garbage out' scenario, allowing data scientists and analysts to focus on modeling and reporting rather than constant, redundant data cleaning tasks, significantly increasing organizational productivity and trust in data.

Answer

Apply business logic and complex transformations to create aggregate summary tables.

Answer

Convert all column names to uppercase to ensure case-insensitive consistency.

Question 9

You are tasked with handling PII (Personally Identifiable Information) in your data pipeline. Which approach is best for protecting this data while maintaining the ability to perform analytics?

Accepted Answer

Hash the PII columns using a salted, non-reversible cryptographic hash function.. Hashing or tokenizing PII allows for unique identification without exposing sensitive information. This is a standard practice for compliance (e.g., GDPR, CCPA). By using consistent hashing, you maintain the ability to join tables or perform group-bys on the hashed key while ensuring that unauthorized users cannot reverse-engineer the sensitive original values, balancing security with functional analytical utility.

Answer

Simply drop the PII columns during the Bronze-to-Silver transformation.

Answer

Encrypt the PII data using a shared key stored in the pipeline code.

Answer

Use a public, non-salted MD5 hash function to ensure consistency across teams.

Question 10

A Data Engineer wants to monitor data quality trends over time for a critical table. Which tool provides the most native and easy-to-use visualization of these metrics?

Accepted Answer

Create a Databricks SQL dashboard using the 'expectations' table provided by DLT.. Delta Lake's integration with Databricks SQL and DLT provides native dashboards that track data quality metrics automatically. Using Expectation history logs, engineers can visualize failure rates and quality trends without building custom monitoring solutions. This visibility is essential for proactive maintenance, allowing teams to catch degrading data quality before it impacts critical downstream business reporting, ensuring higher service levels for end-users.

Answer

Export all data quality logs to an external S3 bucket and use a third-party BI tool.

Answer

Write a custom Spark job to parse the Delta transaction log and send alerts via email.

Answer

Manually query the 'information_schema' every hour to track table row counts.

Question 11

When designing a Data Quality framework in Databricks, what is the recommended approach for handling 'quarantined' records?

Accepted Answer

Move invalid records to a 'quarantine' table with an additional column indicating the error.. Storing invalid records in a separate, dedicated table allows for auditing and correction without blocking the main pipeline. This ensures high throughput for valid data while providing a clear path for reprocessing bad data once the underlying issue is fixed. It is a fundamental pattern for resilient data engineering, ensuring that data is never silently dropped and that stakeholders maintain confidence in the data's reliability.

Answer

Delete the bad records and notify the team via an automated Slack notification.

Answer

Update the records in place by setting the invalid fields to NULL.

Answer

Stop the entire pipeline execution to ensure no bad data reaches the final target.

Question 12

You are performing a complex data transformation involving a self-join on a large, skewed table. Which technique is most effective for preventing data skew and improving join performance?

Accepted Answer

Apply a 'salt' to the join key to redistribute the skewed data across more partitions.. Skewed joins are a primary cause of performance failure in distributed computing. Using a 'salted' key—adding a random prefix to the join key—distributes data evenly across partitions during the join. This prevents 'hot' partitions where one executor processes the majority of the data, significantly speeding up the query and preventing OOM (Out of Memory) errors, ensuring consistent performance for large-scale analytical tasks.

Answer

Increase the number of executors in the cluster to handle the load.

Answer

Use the 'repartition' hint on the join keys to force uniform distribution.

Answer

Convert the join into a broadcast join to prevent shuffling entirely.

Question 13

A Data Engineer needs to verify that the column 'user_id' is unique in a critical Gold table. What is the most efficient, non-blocking way to perform this check in a production environment?

Accepted Answer

Define a primary key constraint in the Delta table definition.. Using a Delta constraint or a DLT expectation is the most efficient way to enforce uniqueness at the storage layer. Unlike a full table scan query, which is reactive and performance-intensive, native constraints are checked during the commit process, providing proactive quality assurance. This ensures that the table never enters an invalid state, which is vital for the integrity of downstream machine learning models and reporting applications.

Answer

Run a daily query: SELECT COUNT(user_id) - COUNT(DISTINCT user_id).

Answer

Use a custom UDF to check for duplicates inside a Spark streaming loop.

Answer

Perform a left-anti join with the previous day's data every time the pipeline runs.

Question 14

When refining data in a Medallion architecture, why is it recommended to perform schema enforcement as early as possible in the Bronze layer?

Accepted Answer

It prevents malformed data from polluting downstream layers and makes debugging easier.. Early enforcement catches errors at the ingestion point, preventing corrupt data from propagating into Silver or Gold layers. This practice keeps the 'upstream' data quality high, reducing the need for costly, complex fixes in downstream tables. It ensures that the Medallion architecture remains clean and reliable, minimizing the time spent debugging issues that could have been resolved at the very first step of the data pipeline.

Answer

It eliminates the need for any further schema validation in the Silver or Gold layers.

Answer

It ensures that the storage cost of the Bronze layer is kept at a minimum.

Answer

It automatically converts all incoming file formats into the optimal Delta format.

Question 15

You are building a pipeline and notice that the 'Gold' layer tables are experiencing significant write latency due to frequent small file commits. What is the most effective way to resolve this while maintaining ACID integrity?

Accepted Answer

Run the 'OPTIMIZE' command periodically on the Gold layer tables.. The 'OPTIMIZE' command with 'ZORDER' is the standard way to fix the small file problem in Delta Lake. It compacts small files into larger, better-performing ones while physically organizing data to speed up reads. This is critical for Gold tables, which are typically read by BI tools. Maintaining performance here is essential for providing end-users with a responsive, high-performance experience when accessing critical business metrics and dashboards.

Answer

Increase the 'spark.sql.shuffle.partitions' to 5000 to distribute writes more widely.

Answer

Switch the table from Delta format to standard Parquet files to improve write performance.

Answer

Use the 'vacuum' command every 5 minutes to clear out the small files.