Removing Invalid Data from Delta Tables in SQL

1.Splitting good and bad rows with WHERE

Invalid data is any row that breaks a business rule. Examples include a negative quantity, an event timestamp in the future, or a code that does not match the expected format. When you clean a table in SQL you have two choices. You can stop invalid rows from reaching the cleaned table, or you can fix the table after they have landed. Databricks documents the first approach as a quarantine pattern built from ordinary WHERE clauses.

DECLARE current_time = now()

INSERT INTO silver_table
  SELECT * FROM bronze_table
  WHERE event_timestamp <= current_time AND quantity >= 0;

INSERT INTO quarantine_table
  SELECT * FROM bronze_table
  WHERE event_timestamp > current_time OR quantity < 0;

The two predicates are logical opposites. The silver filter ANDs the rules together, and the quarantine filter ORs their negations. Bad records don't reach downstream tables, but they aren't thrown away either: you can inspect them, fix them and replay them. current_time is captured once with DECLARE so both inserts compare against the same moment. Databricks recommends always processing filtered data as a separate write operation, as shown here with two INSERT statements. One caution: a row whose quantity is NULL makes both predicates NULL, so it lands in neither table. If missing values are possible, handle them explicitly with IS NULL.

Sources1

2.Turning placeholder values into real NULLs

Some invalid data is not wrong so much as disguised. An upstream system that cannot encode NULL may write a placeholder such as -1 for a missing weight. If you leave it in, every average and every range filter downstream has to remember to ignore -1. A better fix is to apply the conversion once, while the data is being transformed, with a CASE WHEN expression.

INSERT INTO silver_table
  SELECT
    * EXCEPT weight,
    CASE
      WHEN weight = -1 THEN  ? 
      ELSE weight
    END AS weight
  FROM bronze_table;

* EXCEPT weight selects every column except the raw weight, and the CASE expression adds a cleaned weight column back in its place. The ELSE branch passes valid values through unchanged. The same structure handles other conditional business rules: list each predictable violation as a WHEN branch and say what it should become. For a single sentinel, nullif(weight, -1) is a shorter equivalent, because nullif returns NULL when its two arguments are equal and the first argument otherwise.

Sources12

3.Fixing tables in place with DELETE and UPDATE

When bad rows are already in a table, you can remove or correct them in place. DELETE FROM table_name [WHERE predicate] removes the rows that match the predicate. Leave out the WHERE clause and every row is deleted, so always write the predicate first. UPDATE table_name SET col = expr [WHERE clause] rewrites column values on the matching rows. Both statements are supported only on Delta Lake tables, and neither can target a foreign table.

UPDATE events SET eventType = 'click' WHERE eventType = 'clk'

Both WHERE clauses accept subqueries such as IN, EXISTS and NOT EXISTS, with two exceptions: nested subqueries, and a NOT IN subquery inside an OR. Databricks recommends rewriting NOT IN as NOT EXISTS where possible, because NOT IN subqueries can be slow. In UPDATE, SET col = DEFAULT resets a value to the column's DEFAULT expression, or to NULL if the column has none. Some SQL dialects allow UPDATE ... FROM ... JOIN to correct values from a lookup table. Databricks SQL does not, so use MERGE INTO instead.

MERGE INTO t1
  USING t2 ON t1.c2 = t2.c2
  WHEN MATCHED THEN UPDATE SET t1.c1 = t2.c1;

Sources34

4.Stopping invalid data from coming back

Cleaning a table once does not keep it clean. Constraints check rows as they are written. All constraints on Databricks require Delta Lake, and two kinds are enforced. A NOT NULL constraint means the column cannot hold NULL. A CHECK constraint names a boolean expression that must be true for every row. If a write violates either one, the transaction fails with an error, so the bad row is never stored.

ALTER TABLE main.default.people_demo ALTER COLUMN middleName DROP NOT NULL;
ALTER TABLE main.default.people_demo ALTER COLUMN ssn SET NOT NULL;

This sets the order of work: clean first, then constrain. Use UPDATE with coalesce, or DELETE FROM, to remove the existing NULLs, and only then add NOT NULL. CHECK constraints work the same way. ALTER TABLE ADD CONSTRAINT checks all existing rows before adding the constraint. You can write value ranges such as CHECK (birthDate > '1900-01-01') and regex patterns with REGEXP or RLIKE. A CHECK expression cannot use user-defined, aggregate or window functions. To list a table's CHECK constraints, run DESCRIBE DETAIL or SHOW TBLPROPERTIES.

Sources5

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Databricks SQL supports UPDATE ... FROM ... JOIN for correcting values from another table.Why is that wrong?

   That syntax is not supported. To update a table from a join with another table or subquery, use MERGE INTO.

   Covered in Fixing tables in place with DELETE and UPDATE

2. 2.Adding a NOT NULL or CHECK constraint to an existing table only affects new rows, so you can add it before cleaning.Why is that wrong?

   Databricks checks every existing row before it adds the constraint. Existing violations must be cleaned first or the ALTER TABLE fails.

   Covered in Stopping invalid data from coming back