Databricks Data Engineer Professional · 10% practice allocation

Databricks Data Engineer Professional: Data Transformation, Cleansing, and Quality Practice Questions

5 free sample questions from the Data Transformation, Cleansing, and Quality domain, with answers and explanations. The full Data Engineer Professional bank has 311 questions across 10 domains. Questions are scenario-based and written from the official Databricks exam guide.

A data engineer defines the following DLT pipeline table:

@dlt.table
@dlt.expect_or_fail("non_negative_amount", "amount >= 0")
def validated_transactions():
    return dlt.read("raw_transactions")

During a pipeline run, 3 records arrive with amount = -5. What happens?

A. The 3 records are written to the output table with a _violated_expectations metadata column.
B. The 3 invalid records are quarantined into a separate DLT quarantine table.
C. The pipeline completes successfully but logs the 3 violations as warnings in the DLT event log.
D. The entire pipeline update fails immediately when the first invalid record is encountered.✓ Correct
E. The 3 invalid records are silently dropped and the pipeline continues normally.

Explanation

@dlt.expect_or_fail is the strictest DLT expectation mode — if any single record violates the constraint, the entire pipeline update is halted and marked as failed. This is appropriate for data quality gates where bad data must never proceed. @dlt.expect (warn) would log violations without stopping the pipeline. @dlt.expect_or_drop would silently drop violating rows and continue. DLT does not automatically create quarantine tables unless the engineer explicitly defines one using a filter. There is no _violated_expectations column added automatically by DLT.

A data engineer wants to understand all changes made to a production Delta Lake table named orders over the past week, including who ran each operation and what type of operation it was. Which command should the data engineer run?

A. SELECT * FROM orders WHERE _commit_timestamp >= current_date() - INTERVAL 7 DAYS
B. DESCRIBE DETAIL orders
C. SELECT * FROM delta./path/to/orders/_delta_log
D. DESCRIBE HISTORY orders✓ Correct
E. SHOW TBLPROPERTIES orders

Explanation

DESCRIBE HISTORY orders returns the full audit log of all operations performed on the Delta Lake table, including operation type, timestamp, user, and notebook/job details. DESCRIBE DETAIL returns current metadata (file count, size, schema) but not historical operations. SHOW TBLPROPERTIES lists table configuration properties, not operation history. Querying _commit_timestamp on the base table does not exist as a built-in column. Directly querying the _delta_log path returns raw JSON transaction log files and is not the intended interface.

A data engineer is working on a Photon-accelerated Databricks cluster. They need to decide which of the following workload components are expected to benefit from Photon acceleration. Select TWO that Photon is known to accelerate effectively.

A. Python UDFs defined with @udf decorator applied row-by-row on a DataFrame
B. Graph processing using GraphX RDD-based APIs
C. Structured Streaming jobs using mapGroupsWithState for arbitrary stateful processing
D. Delta Lake MERGE INTO operations that perform large-scale upserts✓ Correct
E. ML model inference using a custom MLflow Python model wrapped as a UDF
F. SQL aggregate queries with GROUP BY and SUM/COUNT on large Delta Lake tables✓ Correct

Explanation

Photon is Databricks' vectorized query engine written in C++ that accelerates SQL and DataFrame operations, particularly aggregations (GROUP BY, SUM, COUNT), joins, and Delta Lake operations including MERGE INTO. These are the workloads where Photon provides the most significant speedup. Python UDFs (row-by-row) are not accelerated by Photon because they run in a Python process outside the Photon engine — they break the vectorized execution pipeline. MLflow Python model UDFs similarly execute in Python and are not Photon-compatible. GraphX is RDD-based and runs outside the SQL/DataFrame engine entirely; Photon only accelerates the Spark SQL / DataFrame API layer. mapGroupsWithState is a custom stateful operation in Structured Streaming that runs user-defined Python/Scala code and is not accelerated by Photon's vectorized engine.

A data engineer implements the following Structured Streaming query that reads from a Delta Lake source:

(
  spark.readStream
    .format("delta")
    .option("ignoreChanges", "true")
    .load("/delta/events")
    .writeStream
    .format("delta")
    .option("checkpointLocation", "/chk/events_out")
    .trigger(availableNow=True)
    .start("/delta/events_out")
)

Which TWO statements correctly describe the behavior of this query?

A. The query will process all data available in the source at the time of job startup, then stop automatically.✓ Correct
B. The ignoreChanges=True option allows the query to handle Delta source tables that have been modified by UPDATE or OPTIMIZE operations without failing.✓ Correct
C. The query will run continuously until manually stopped, processing all new files as they arrive.
D. The ignoreChanges=True option enables the pipeline to reprocess previously seen records to ensure exactly-once delivery semantics.
E. The trigger(availableNow=True) trigger processes all available data across multiple micro-batches and then stops, unlike trigger(once=True) which processes only one micro-batch.
F. The trigger(availableNow=True) trigger behaves identically to trigger(once=True) and processes only a single micro-batch.

Explanation

trigger(availableNow=True) instructs the streaming job to process all data currently available in the source and then stop — it does not run continuously. Unlike trigger(once=True) (which processes all available data in exactly one micro-batch regardless of size), availableNow can process data across multiple micro-batches, respecting maxFilesPerTrigger / maxBytesPerTrigger rate limits, making it more resource-efficient for large backlogs. ignoreChanges=True is needed when the Delta source table undergoes operations that rewrite files (such as UPDATE, MERGE, DELETE, or OPTIMIZE), which would otherwise cause the streaming reader to error because it sees files that were previously committed being replaced. It does not cause reprocessing of data for exactly-once guarantees — checkpointing handles that. The job does not run continuously with this trigger.

A data engineer is implementing an SCD Type 2 pattern using Delta Lake MERGE. The target customer_dim table has columns: customer_id, email, status, effective_start, effective_end, is_current. The source contains new/changed records. Which MERGE logic correctly implements SCD Type 2? (Select TWO)

A. WHEN MATCHED THEN UPDATE SET * — this automatically handles the SCD Type 2 versioning by overwriting with the latest values.
B. WHEN MATCHED AND target.is_current = false THEN UPDATE SET target.is_current = true
C. WHEN NOT MATCHED BY SOURCE AND target.is_current = true THEN UPDATE SET target.effective_end = current_timestamp(), target.is_current = false
D. WHEN MATCHED AND target.is_current = true AND (target.email != source.email OR target.status != source.status) THEN UPDATE SET target.effective_end = source.effective_start, target.is_current = false✓ Correct
E. WHEN MATCHED AND target.is_current = true THEN DELETE
F. WHEN NOT MATCHED THEN INSERT (customer_id, email, status, effective_start, effective_end, is_current) VALUES (source.customer_id, source.email, source.status, source.effective_start, NULL, true)✓ Correct

Explanation

SCD Type 2 requires two operations per change: (1) close the current row by setting effective_end and is_current=false (Option D — correct WHEN MATCHED clause that only updates rows where is_current=true AND the data has actually changed), and (2) insert a new row with the new values, effective_start set to the change time, effective_end=NULL, and is_current=true (Option F). Together these correctly version the history. Option E is wrong — DELETEing matched rows destroys history, which is the opposite of SCD Type 2. Option A is wrong — UPDATE SET * overwrites the current record (SCD Type 1 behavior), losing history. Option C is wrong — this handles records where the source has no matching record (hard deletes), which is a valid extension but is not a core SCD Type 2 operation; more critically, it doesn't insert the new version. Option B is wrong — reactivating an old row without inserting a new one creates an incorrect history.

Test yourself across every Data Engineer Professional domain

The free Data Engineer Professional practice test mixes about 20 questions weighted like the exam. The full 311-question bank adds instant grading, flashcards and a timed mock exam.

Take the free Data Engineer Professional practice test Practice the full bank

Study this domain

Data Transformation, Cleansing, and Quality study guide (Data Engineer Professional) →

Other Data Engineer Professional domains

← All Databricks Data Engineer Professional study resources