Answer: CONSTRAINT valid_location EXPECT (location != NULL) ON VIOLATION FAIL UPDATE

# DLT Constraints and Validation 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. | ## Requirement Analysis 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` ## Option Review - **A**: `location = NULL` is invalid SQL syntax (should be `IS NULL`), and the logic is reversed. ❌ - **B**: ✅ Correct — `location != NULL` (ideally `IS NOT NULL` for SQL correctness) with `ON VIOLATION FAIL UPDATE` matches the requirement and official syntax. - **C**: Uses `ON DROP ROW`, which drops invalid rows instead of failing the pipeline. ❌ - **D**: `ON VIOLATION FAIL` is not valid syntax in DLT SQL. ❌ ## Correct Answer ✅ **B**: ```sql CONSTRAINT valid_location EXPECT (location IS NOT NULL) ON VIOLATION FAIL UPDATE ``` ### Why? - Ensures that if any row has a missing `location`, the update fails. - Matches the official Databricks DLT syntax for failing updates. - Meets the requirement to terminate the pipeline when location details are missing. ## Reference [Databricks Documentation – Action on invalid record](https://docs.databricks.com/en/delta-live-tables/constraints.html)

Author: LeetQuiz Editorial Team