Extract. Transform. Load.
A simple enough concept, you would have thought. Except those guys (the over-paid third-party consultants) want me to do the “T” part in the source database. A source database that we know to be flaky, that we know to be fragile, that we know to be unsupported when directly updating the back-end data, that we know to be a festering pile of … well, you get the picture. And, in this particular instance, it’s one where the source field in question isn’t kept up-to-date by the client application, so it’s a job I’ll have to keep doing for the rest of the life of this system. (The schema has over time proven so fragile that adding a trigger is not an option.)
Surely it’s a simple enough transformation to build into their scripts? The basic logic, in the form of a SELECT statement:
SELECT ..., CASE WHEN Country_Code = 'USA' AND ( (State IS NULL) OR (State = '') ) THEN County ELSE State END AS State, ...
It’s not rocket science as an extract/transform. Why, therefore, do I find my mailbox filled to bursting with emails about it?