“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
A PL/pgSQL function without a volatility marker is VOLATILE by default. That sounds harmless but has a real cost: the planner calls the function again for every row, never pre-computes it once, and excludes it from every functional index. The damage is invisible — until the same query suddenly takes seconds instead of milliseconds. Good PL/pgSQL function conventions start … Read more
Creating a table takes thirty seconds – being annoyed about its name, its key, and its column types then takes two years. Postgres table conventions take that annoyance off the table up front: consistent names, a predictable primary key, data types without surprises, and audit columns that explain every row. Without them, a schema drifts further apart … Read more
An AI writes you a stored procedure in seconds. It compiles, it runs – and it looks like a stranger wrote it. The next artifact looks different again. Generated SQL is technically correct but stylistically arbitrary – and without an enforced convention, a generated collection drifts apart just like one five developers write by hand. The obvious … Read more
A stored procedure, a migration script, a complex report — Claude Code writes them in seconds. That’s the easy part. The hard part starts afterwards: generated SQL that belongs to no one drifts apart just like hand-written code — only faster, because the AI produces hundreds of lines on demand. AI-assisted SQL development only pays off when the … 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
