Pitfall

Soft delete and NULL

WHERE NOT is_deleted also drops the rows where the flag is NULL.

NOT NULL is NULL, and WHERE keeps only true rows. When a deletion flag can be empty (a column added later, an old system), write WHERE is_deleted IS NOT TRUE, or better, turn it into a real boolean once in staging: COALESCE(is_deleted, false) AS is_deleted.

The trap

WITH products(sku, is_deleted) AS (VALUES ('A', false), ('B', true), ('C', NULL))
SELECT sku FROM products WHERE NOT is_deleted ORDER BY sku;

sku
A

sku

Try it in Randori

The fix

WITH products(sku, is_deleted) AS (VALUES ('A', false), ('B', true), ('C', NULL))
SELECT sku FROM products WHERE is_deleted IS NOT TRUE ORDER BY sku;

sku
A
C

sku

Try it in Randori

Practise:Soft-deleted, or just NULL?