Moving data sounds like the easiest phase of a migration: the table is there, pour the data in, done. Until the first larger table takes a whole afternoon — because it runs row by row via INSERT instead of in one sweep via COPY. Anyone who needs to load data into PostgreSQL has four routes to choose from, and the … Read more
A SQL Server to PostgreSQL schema migration looks finished the moment the CREATE TABLE script runs without an error. That is exactly when the real trouble begins: the table is there, the data is loaded — and the first INSERT that should hand out a new ID collides with an existing key. The reason is not a typo but a change … Read more
A migration from SQL Server to PostgreSQL rarely fails at actually copying the data. It fails at datetime, where the choice between timestamp and timestamptz is anything but cosmetic, at bit, which is not a boolean, and at money, which you’d be better off not touching in PostgreSQL at all. The SQL Server to PostgreSQL data type mapping decides whether the data arrives cleanly — … 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
“We do ETL.” You hear it in almost every data team — and it is often wrong. If every transformation runs as a stored procedure after the raw data has already been loaded, unchanged, into a staging table, then what you have is really ELT. The label hides what is actually happening. The difference between ETL vs. ELT is … Read more
If you write a stored procedure, you write it for someone who doesn’t know it — usually for yourself, 18 months later, at 11 p.m., while an ETL run is stuck. Readability isn’t cosmetics, it’s debugging time. PostgreSQL forces almost nothing on you: names are free, indentation doesn’t matter, a RAISE EXCEPTION swallows any string you assemble inline. That’s exactly … 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. The technology examples are … Read more
If you’ve ever imported a CSV column with mixed date formats into a datetime column, you know: Type conversion is where bad date values either surface or slip silently into the pipeline. SQL Server leaves you alone with style codes the moment the format strays from the documented ones — TRY_CONVERT covers the documented style formats, anything else needs a parsing strategy of your own. … Read more
Anyone who has ever taken over a yes/no column from a legacy export knows the pattern: the source delivers ‘J’, ‘ON’, or a plain ‘x’, and TRY_CONVERT(bit, N’J’) answers the German notation with NULL. Out of the box, SQL Server understands only integer strings and the literals ‘true’/’false’ at the bit target — every other yes/no notation needs an explicit mapping. At a glance: Prerequisite: TRY_CONVERT has … Read more
If you have ever imported a series of measurements where every empty cell landed in the target table as 0, you know the trap: TRY_CONVERT(float, ”) does not return NULL, it returns 0. The average across that column is wrong afterwards, and nothing about the result gives it away. At a glance: Prerequisite: TRY_CONVERT has existed since SQL Server 2012. The safe … Read more
