Pattern

Quarantine table

Bad rows go to a separate table with the rule they break, instead of being filtered out and forgotten.

Compute one reason per row with a single CASE whose WHENs follow the order of the rules. Rows with no reason go to the staging table, the others to the quarantine table. Test that clean + quarantined = raw, and follow the quarantine per reason and per day: it is the source's data quality, as a number its owners can act on.

WITH raw_signups(signup_id, email, country) AS (VALUES
  (1, 'lea@example.com', 'FR'), (2, NULL, 'FR'),
  (3, 'tom.example.com', 'BE'), (4, 'mia@example.com', 'CH'))
SELECT signup_id,
       CASE WHEN email IS NULL THEN 'missing email'
            WHEN email NOT LIKE '%@%' THEN 'invalid email'
            WHEN country NOT IN ('FR', 'BE', 'DE') THEN 'unknown country'
       END AS reason
FROM raw_signups
ORDER BY signup_id;

signup_id	reason
1	NULL
2	missing email
3	invalid email
4	unknown country

Try it in Randori

Practise:Quarantine, don't drop