A lot gets written about data quality, and very little gets measured. The German-language practitioner’s standard reference alone lists sixty possible quality criteria — from timeliness to reliability —, and even the lean models still arrive at six to fifteen dimensions. Yet the core of the matter is surprisingly tangible: a data error caught during … Read more
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
A single value that won’t convert — a 25.5 in an integer column, an empty string, a date like 20240230 — and the ETL run aborts mid-import. Anyone who loads text data from upstream systems knows it: the delivery doesn’t honour the agreed interface, and a bare CONVERT throws an exception instead of cleanly logging the offending value. This article describes … Read more
A date from a CSV file lands as text in the database — and suddenly the 2nd of November turns into the 11th of February. These silent misinterpretations are the classic pitfall of type conversion in SQL Server. Anyone who knows CAST, CONVERT, TRY_CAST and TRY_CONVERT together with the style parameter avoids them. The essentials up front: Prerequisite: SQL Server with SSMS; … Read more
The row counts match — table by table, source against target, all green. And yet the migration isn’t finished. In one column, NULL values have turned into empty strings; in another, the detour through a CSV file rounded the last decimal of an amount; and a handful of accented characters collapsed into question marks. Same count is not … 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
A single date string that cannot be parsed, and the entire ETL run aborts. The design pattern for ETL process architecture presented here prevents exactly that: bad data is isolated, not passed along. TL;DR — what this article covers: Prerequisite. Basic familiarity with ETL processes. This is a conceptual article — not a step-by-step tutorial. Root of … Read more
If you’ve ever imported a CSV column with mixed date formats into a datetime column, you know: data quality starts with type conversion. SQL Server leaves you alone with style codes the moment the format strays from the documented ones — TRY_CONVERT handles the documented formats, anything else needs a function of your own. What you’ll take away: Prerequisites: SQL Server 2017+ (for TRY_CONVERT styles 23/126), PostgreSQL 12+ for the … Read more
