A data analyst is tasked with cleaning a Unity Catalog Delta table called bronze.sales.transactions. The table has the following data quality issues: (1) rows where transaction_amount (DOUBLE) is negative — these represent data entry errors and must be permanently removed, (2) rows where customer_id (STRING) is NULL — these must also be permanently removed, and (3) rows where country_code (STRING) contains values not in the approved list ('US', 'CA', 'MX', 'GB') — these must be updated to 'UNKNOWN' rather than deleted. Which SQL block CORRECTLY addresses all three requirements in a single transaction-efficient approach?
Answer & explanation
Correct answer: D
WHY D is correct: Delta Lake fully supports ACID DELETE and UPDATE DML on tables registered in Unity Catalog. DELETE FROM bronze.sales.transactions WHERE transaction_amount < 0 OR customer_id IS NULL correctly removes all rows matching either invalid condition in a single atomic operation — the OR compound predicate is fully supported. UPDATE bronze.sales.transactions SET country_code = 'UNKNOWN' WHERE country_code NOT IN ('US', 'CA', 'MX', 'GB') correctly updates only the rows with invalid country codes to 'UNKNOWN'. Both statements commit as separate ACID transactions, maintaining full Delta Lake transaction log integrity. This is the cleanest, most readable, and correct approach for the given requirements.
WHY NOT A: Delta Lake supports efficient row-level DELETE with any predicate expression — including compound predicates with AND/OR. Full-table TRUNCATE followed by backup re-insert is not recommended; it is more error-prone, requires a backup table to exist, copies all data, and touches far more data than a predicate-based DELETE. It is NOT a documented requirement or limitation for partitioned Unity Catalog Delta tables.
WHY NOT C: Floating-point (DOUBLE) columns are fully supported in DELETE WHERE predicates in Databricks SQL and Delta Lake. Comparison operators (<, >, =) on DOUBLE columns are deterministic when evaluating stored values — the non-determinism concern applies to computed floating-point arithmetic, not simple inequality comparisons on stored data. NOT IN with a fixed list is fully supported in UPDATE WHERE clauses on Delta tables; no MERGE rewrite is required.
WHY NOT B: While MERGE can effectively perform the deletion by matching on a source subquery, the claim that plain DELETE requires MERGE for tables with more than 1 million rows is entirely fabricated. Delta Lake's DELETE statement works for any table size — query planning and file selection are handled internally by the Delta protocol and there is no row-count threshold that forces MERGE usage.
WHY NOT E: While MERGE can indeed mix DELETE and UPDATE in a single pass (a valid performance optimization), the claim that a plain DELETE followed by a separate UPDATE is not supported in the same session is false. Databricks SQL enforces no 'one DML per session transaction' rule. Multiple DML statements against the same Delta table in the same session are fully supported and each commits as an independent ACID transaction. The 'single MERGE pass' approach in option E is valid as an alternative, but the premise that it is the only option is incorrect.