Databricks Certified Data Engineer Associate — Question 90

Identify the impact of ON VIOLATION DROP ROW and ON VIOLATION FAIL UPDATE for a constraint violation.

A data engineer has created an ETL pipeline using Delta Live table to manage their company travel reimbursement detail, they want to ensure that the if the location details has not been provided by the employee, the pipeline needs to be terminated.

How can the scenario be implemented?

Answer options

• A. CONSTRAINT valid_location EXPECT (location = NULL)
• B. CONSTRAINT valid_location EXPECT (location != NULL) ON VIOLATION FAIL UPDATE
• C. CONSTRAINT valid_location EXPECT (location != NULL) ON DROP ROW
• D. CONSTRAINT valid_location EXPECT (location != NULL) ON VIOLATION FAIL

Correct answer: B

Explanation

The correct answer is B because it specifies that if the location is not provided, the update will fail, ensuring data integrity. Option A suggests a condition that allows NULL values, which contradicts the requirement. Option C incorrectly uses ON DROP ROW, which doesn't apply to this scenario, and option D does not specify the update behavior needed in this situation.