A foreign key pointing into the void is quick to describe — a country_code with no matching entry in the reference data — and surprisingly easy to check incorrectly. The most intuitive phrasing is, of all things, the most dangerous: NOT IN (SELECT …) reads like plain English but silently collapses the moment the reference column holds a single NULL. The … Read more
GROUP BY … HAVING count(*) > 1 may be the most-typed SQL snippet in the world — and yet it trips up in two places almost nobody has on their radar: composite keys and NULL. When it comes to missing values in a unique column, SQL Server and Postgres disagree — one allows exactly one NULL, the other any … Read more
A range check that runs green is no proof of clean data. Anyone who writes WHERE age < 0 OR age > 120 to find implausible ages silently misses every row where age has no value at all — because in SQL, a comparison with NULL is neither true nor false, but unknown. That very missing required value later breaks the load into the strictly … Read more
Bad data gives no warning. An age of 200 years, a duplicate customer number, a country code that doesn’t exist — in the source system nobody notices. Only when the ETL run tries to push the rows into the strictly modelled target layer does the load break: on a CHECK, on a UNIQUE index, on a foreign key. Checking … Read more
