Yesterday it emerged that the UK government had some issues with their COVID data due to an Excel issue.
I thought this would be a fun one to share thoughts about what advice you’d offer in this situation?
How would you test it? Would you have used the same tooling?
I helped define and test similar data capture systems in HMG (utilities regulation) some 25 years ago, and we had a particular emphasis on end-to-end testing because our senior management concentrated very sharply on data accuracy and security. Individual data points had to be discrete, unique and to survive data scrape from the capture system into our master database. Our data schema had been designed as a paper system and our first five years concentrated on digitising that data. We collected a large amount of data annually from utility companies and used this to take decisions on utility bills that would apply nationally.
(Oh, and we outgrew Excel inside three years in terms of the amount of data we were requesting [though Microsoft code bloat was also an issue]; but it was a year’s development to create a capture system that still looked like a familiar spreadsheet but actually used database architecture.)
My later private sector experience in end-to-end testing of business processes following the upgrade of one of the component apps of the data pipeline would focus me very much on the interface between different apps. And anyone in public sector IT (where the small-p political stakes are high and could seriously impact people in so many ways) who doesn’t carry out some sort of risk analysis is just storing up trouble for themselves. “What’s the worst thing that could happen?” ought to be a daily challenge to any decision.
It’s like making POST requests but not validating they are actually in the DB.
Funny nobody noticed for 16.000 records … . Insane how it still happens these days but yea more common then we think.
So yea what would I do?
Write a validation that the record has been added 
Keep asking “are you sure this is the right solution?” until they were so convinced Excel was a bad idea
@progrockmicky: There’s nothing wrong with Excel as a data capture solution, especially when you’ve only been given six weeks to develop a “world-beating” app. But it does help if you understand how it works…
Think about it…
Why would then even insert on column and not by rows? That is just weird!
TL;DR: I wouldn’t. There’s more than one way this might have happened. Well, or not happened.
That word ‘would’ invites to some … fantasising. — So, had I been on the team right from the beginning, I may have accepted exploring the problem space with Excel (or any other spreadsheet).
Taking risk into account (in COVID-19 cases, that would be the death of patients that may have been prevented), we’d have gotten into answering one question: Can the solution deal with the worst case? - Let’s assume the worst case is that every person in the country gets infected (Yes, unlikely. Yes, in that case we wouldn’t need that data collection anymore…), the system would need do deal with ≈70.000.000 cases (according to wikipedia).
At that point, I would have strongly recommended against using any spreadsheet, and turning to a data base instead.
At that point I imagine 3 possible paths:
I’d really like to see more details about this. That said: Given the information I have and the importance of a working solution, I can imagine that this is considered malpractice.
In order of likeliness, the paths would probably be 2, 3, 1.
If PHE follow usual UK public sector practice, at least your concerns could be registered in your exit interview, though the findings from that would most likely get buried in the bureaucracy. Then again, I was never high enough in the civil service to know what happens to exit interview notes.
As I said in my first post, faced with a similar situation twenty-five years ago, we eventually adopted a database solution (MS-Access!). But that position evolved over time. The question this whole affair raises to me is to what extent PHE’s IT team are at the current cutting edge of the industry. Given that they are a public sector organisation, I suspect that they do not have access to new kit, the latest thinking on software architectures, the time to go on training courses, or are in a position to recruit the brightest and best as their salary rates will not match those on offer in the wider industry. But this is the elephant in the room; it’s far easier to shoot the messenger. And hence your option 2.
There is if it’s for national use that informs policy decisions, especially in life or death scenarios!
In which case, the political masters have to put up with a lead time to MVP of more than six weeks (which was what was promised way back when the UK’s lockdown first started).
I still find it telling that the bug was actually in the interface between 2 components of the system, although the excel component was at fault (technically you could design around the columns limit by adding sheets for each week number), the fault was really for me, between two parts. And that’s where many bugs can be discovered through static analysis?