Explanation:
In Delta Live Tables (DLT), constraints are defined using the EXPECT clause to validate data. When a record fails validation, the ON VIOLATION clause specifies the action to take:
| Action | SQL Syntax | Behavior |
|---|---|---|
| Warn (default) | EXPECT ... | Invalid records are written to the target with a warning. |
| Drop | EXPECT ... ON VIOLATION DROP ROW | Invalid records are dropped before writing; pipeline continues. |
| Fail | EXPECT ... ON VIOLATION FAIL UPDATE | Invalid records prevent the update from succeeding; manual intervention required. |
The requirement is to stop the pipeline if the location field is missing (NULL). This necessitates failing the update when the constraint is violated. Per the official Databricks documentation, the correct syntax is:
ON VIOLATION FAIL UPDATE
location = NULL is invalid SQL syntax (should be IS NULL), and the logic is reversed. ❌location != NULL (ideally IS NOT NULL for SQL correctness) with ON VIOLATION FAIL UPDATE matches the requirement and official syntax.ON DROP ROW, which drops invalid rows instead of failing the pipeline. ❌ON VIOLATION FAIL is not valid syntax in DLT SQL. ❌✅ B:
CONSTRAINT valid_location EXPECT (location IS NOT NULL) ON VIOLATION FAIL UPDATE
CONSTRAINT valid_location EXPECT (location IS NOT NULL) ON VIOLATION FAIL UPDATE
location, the update fails.Ultimate access to all questions.
No comments yet.
A data engineer has implemented an ETL pipeline using Delta Live Tables to manage travel reimbursement details. They need to ensure the pipeline terminates when location details are missing from employee submissions.
What is the difference between using ON VIOLATION DROP ROW and ON VIOLATION FAIL UPDATE for handling constraint violations in this scenario, and which approach should be used to meet the requirement?
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