Home / Data Analyst practice test / Managing Data / Question

Databricks Data Analyst Associate · free practice question

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)…

Exam-style question on Managing Data from the free Data Analyst practice test, with the answer and a full explanation below.

Updated November 10, 2026

Managing Data

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?

  1. AStep 1: TRUNCATE TABLE bronze.sales.transactions; to remove all rows. Step 2: Re-insert valid rows using INSERT INTO bronze.sales.transactions SELECT * FROM bronze.sales.transactions_backup WHERE transaction_amount >= 0 AND customer_id IS NOT NULL AND country_code IN ('US','CA','MX','GB'); This approach is preferred because Delta Lake does not support row-level DELETE with compound predicates — only full-table TRUNCATE followed by selective re-insert is guaranteed to avoid write conflicts on partitioned Delta tables registered in Unity Catalog.
  2. BUse a single MERGE statement: MERGE INTO bronze.sales.transactions AS t USING (SELECT transaction_id FROM bronze.sales.transactions WHERE transaction_amount < 0 OR customer_id IS NULL) AS src ON t.transaction_id = src.transaction_id WHEN MATCHED THEN DELETE; followed by a separate UPDATE bronze.sales.transactions SET country_code = 'UNKNOWN' WHERE country_code NOT IN ('US','CA','MX','GB'); This is equivalent to option B but forces the entire operation into a single MERGE scan, which is always required when deleting rows from a Delta table that has more than 1 million rows — smaller tables can use plain DELETE.
  3. CRow-level DELETE is not supported on DOUBLE columns in Databricks SQL because floating-point comparison is non-deterministic under ACID semantics; only integer and string column predicates are supported in DELETE WHERE clauses. The analyst must first cast transaction_amount to BIGINT with ALTER TABLE bronze.sales.transactions ALTER COLUMN transaction_amount TYPE BIGINT, then run the DELETE. The NOT IN predicate is also unsupported in Delta Lake UPDATE statements and must be rewritten as a LEFT ANTI JOIN subquery in a MERGE statement.
  4. DCombine both deletion conditions into a single DELETE statement, and use a separate UPDATE for the country code correction: DELETE FROM bronze.sales.transactions WHERE transaction_amount < 0 OR customer_id IS NULL; followed by UPDATE bronze.sales.transactions SET country_code = 'UNKNOWN' WHERE country_code NOT IN ('US', 'CA', 'MX', 'GB'); This approach uses Delta Lake's native ACID DML to permanently remove invalid rows in one DELETE operation (combining both predicates with OR), then correct the invalid country codes with a targeted UPDATE — both operations commit independently as separate ACID transactions on the Delta table.
  5. EAll three cleaning operations can be combined into a single MERGE statement where the source dataset contains three logical branches: one for rows to delete due to negative transaction_amount, one for rows to delete due to NULL customer_id, and one for rows requiring a country_code update. MERGE is the only DML statement capable of mixing DELETE and UPDATE actions against the same target table in a single pass, making it the most efficient approach. Plain DELETE for row removal followed by a separate UPDATE is not supported when both operations target the same Delta table within the same Databricks session — Databricks SQL enforces a 'one DML per session transaction' rule for Delta tables registered in Unity Catalog.
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.

Take the full Data Analyst practice test free →

Would you pass today?

This question is one of 45 in the free, timed Databricks Data Analyst Associate practice test. Take it and get a section-by-section score.

Start the free test →