June 21, 2019 at 12:00 am
Comments posted to this topic are about the item Practice Those Scripts
June 21, 2019 at 7:15 am
No system or approach is infallible. I'll give an example of my latest mistake.
I write tests for the outcomes that are mandated by requirements. I also write tests to check for what is mechanically necessary that is not covered by the business story. These tests have made delivery of 180+ data pipelines quick and more-or-less a BAU (Business As Usual) process rather than full on development.
All well and good.
The problem was that I was looking for an outcome for the story I was satisfying and not testing for a negative outcome if my change did more than it should.
I had a test that confirmed that the high watermark for a data warehouse load was set to a value. What I didn't do was check to make sure that ONLY that high watermark was affected.
The query I wrote was
UPDATE dw_config.HighWaterMark
SET HighWaterMarkValue = 12345678 -- This represents midnight on 9th May 2010
WHERE LoadJobKey = 1234;
Works exactly as it should in every IDE known to humankind.
The deployment software we use records the different queries that are in the script and to do that it pivots them up onto a single line so the comment intended to make things clear for a peer review looks like the following
UPDATE dw_config.HighWateMark SET HighWaterMarkValue = 12345678 -- This represents midnight on 9th May 2010 WHERE LoadJobKey = 1234;
Yep. Update in a data warehouse without a WHERE clause. Had I built a test that looked at before/after on the dw_config.HighWaterMark table I would have spotted the problem. In pre-production environments there is nothing like the volume of data in the production one so all that happened in lower environments is that it reloaded the data available. In a production environment it tried to load historical data from the long term store going back years!
It could have been worse. The software strips out any lines beginning with something it doesn't recognise. A line beginning a block comment gets ignored so pity the poor sod whose script was something like the following.
/*
ALTER TABLE Mission_Critical_Schema.Huge_Important_Fact_Table DROP COLUMN Binky;
ALTER TABLE Mission_Critical_Schema.Huge_Important_Fact_Table
ADD Librarian VARCHAR(50) DEFAULT('Oook');
*/
The software stripped out the open/close comment blocks and the commented out code was no-longer commented out. Fortunately this was trapped in the development environment shortly after the previous production incident.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply