Question 64

You maintain a Lakeflow Spark Declarative Pipeline that produces the `silver.payments` streaming table for a regulated finance workload. The data steward gives you a strict rule: a payment record with a **null `account_id` is never acceptable** — if even one such record arrives, the pipeline update must **stop immediately and atomically roll back the table update** so no partial, dirty data is committed, forcing manual investigation of the upstream source before reprocessing. Three candidate SQL expectation clauses are below: ```sql -- Option 1 CONSTRAINT valid_account EXPECT (account_id IS NOT NULL) -- Option 2 CONSTRAINT valid_account EXPECT (account_id IS NOT NULL) ON VIOLATION DROP ROW -- Option 3 CONSTRAINT valid_account EXPECT (account_id IS NOT NULL) ON VIOLATION FAIL UPDATE ``` Which clause meets the steward's requirement?

• AOption 1 — `EXPECT (account_id IS NOT NULL)` (warn). It keeps invalid rows but flags them in metrics so the team can react.
• BOption 2 — `EXPECT ... ON VIOLATION DROP ROW`. It discards null-`account_id` rows so they never reach the target.
• COption 3 — `EXPECT ... ON VIOLATION FAIL UPDATE`. It stops the update on the first invalid record and atomically rolls back the transaction.
• DNone of these; expectations cannot halt a pipeline, so you must add a Delta `NOT NULL` constraint to the target table instead.