Excel: A data pipeline that lost thousands of Covid-19 test results

On Monday 5th October 2020, numerous UK newspapers began reporting on a major error that had occurred within a vital Covid-19 data pipeline.

The pipeline in question was connected to various government dashboards used for monitoring testing capacity, infection rates and other metrics.

Importantly, it was also connected to the NHS’s test-and-trace system.

The error caused tens of thousands of testing records to be lost and more than 15,000 positive cases of COVID-19 infections to not be included in daily statistics.

As a result, thousands of people who had come in contact with infected individuals were not alerted or instructed to self-isolate, potentially furthering the spread of the virus.

Reports were quick to blame Public Health England (PHE), the government body responsible for the collection of testing data at that time.

But the blame was targeted specifically at PHE's use of one particular tool which had been used as part of an ETL process: Excel.

The drawbacks of using older tools.

It was reported the error was caused by an older version of Microsoft Excel, XLS, used during a data loading procedure.

Allegedly, the loading process involved opening incoming CSV files within Excel, before loading the columnar data to the central PHE database. 

During this process, larger CSV files (eclipsing the 65,536 row limit of XLS) appear to have been truncated, meaning thousands of rows could have potentially been lost during each load process.

In response to the error, an expert at the University of Cambridge suggested:

"Even a high-school computing student would know that better alternatives to XLS exist."

"When you need to do something more serious, you build something bespoke that works - there's dozens of other things you could do. But you wouldn't use XLS."

Blaming Excel does not paint the entire picture.

Before focusing on the technical issues which occurred in Excel, it is important to first contextualise the situation within which PHE found themselves.

Towards the beginning of the pandemic, partnerships with third-party labs were established (separate from PHE and NHS labs), in order to meet the rising demands for increased testing capacity across the UK.

These partnerships included universities, research institutes and companies such as Amazon and Boots.

This meant that existing PHE pipelines would not be relied on for complete data collection and either a solution that could be implemented alongside PHE's existing systems was required, or a completely new system would have to be developed.

Regardless of approach, any solution would likely have needed to respond to the following constraints:

⚠️     Time constraints

Any new pipelines or software would need to be developed then deployed within a very narrow time frame to start dealing with the inflow of testing data.

⚠️     Data constraints

Each lab would likely be using its own software to record test results; so a common data format for submission would need to be agreed (hence why CSV was likely chosen).

⚠️     User constraints

Any tools or software that needed to be used by PHE users and commercial partners would need to require almost no new training - again due to time constraints.

Excel was the problem but not the cause.

In regards to the limitations of XLS, the following is true:

• The 65,536 row limitation should have been acknowledged.

• And even newer versions of Excel (2007- onwards) could have at least been considered, given they are are capable of handling up to 1,048,576 rows.

However, an investigation into the error concluded that despite it's limitations XLS could have been used as part of a temporary solution while a more purpose-built system was put into development.

It would appear the root cause of the error had less to do with the choice to use XLS but rather the manner in which it had been used to load data.

Rather than implementing an automated process, it is understood that CSV files were being opened manually in Excel by a PHE user, before being loaded into the central database. 

Based on a simulation of the likely process that occurred, it can be assumed that either:

• The user would have been issued an on-screen warning of data truncation, yet still clicked 'ok' regardless.

• Or a developer who created a related VBA extension chose to suppress all messages shown from the VBA interpreter and the user was then not warned of possible data loss.

In both scenarios, this would be a case of human error.

What does the data pipeline need to do?

After identifying the causes of the error, I began the process of designing a data pipeline that would have avoided a similar situation from occurring.

The first step in this process involved conducting a requirements analysis. 

i.e. Determining the list of specific requirements that need to be met by the data pipeline.

How will the pipeline address requirements?

The next step involved determining the logical design for the data pipeline.

i.e. Plotting the physical flow of data.

This was done by creating a system diagram; outlining each of the various components.

This diagram would then serve as a point of reference when building out the pipeline.

Testing a new system.

Once happy with the overall structure of the pipeline, I needed a way of testing it was fit for purpose.

This required simulating the inflow of CSV files from the web service before loading them into the data lake.

Obviously I would not be able to get my hands on real Covid-19 testing data, so this meant having to write a Python script that could generate fake data in a similar format and volume as what was likely received by PHE.

This would involve understanding the formatting of specific data, for example:

• NHS Number
  10 numeric digits in length. The tenth digit is a check digit used to confirm its validity.

• (UK) Postcode
  Five to seven alphanumeric characters that can follow a number of different patterns.

The following is the resulting script written for generating the placeholder medical data.

The Python library Faker was used, which is useful for generating random addresses accoding to a specific locational format.

If you would like to try running this code and see a very simple recreation of the UK Covid Dashboard UI, you can have a go here.

Building a data pipeline, especially on the scale witnessed during the Covid-19 pandemic, presents significant difficulties and complexities. 

Hindsight allows for a critical examination of mistakes, but addressing errors in real-time demands a high level of skill under pressure. 

Despite encountering challenges, Public Health England (PHE) successfully assembled the most comprehensive and granular data platform across EU countries, providing valuable insights during the pandemic. 

The platform, despite facing publicized errors, served millions of end users efficiently and the achievement stands as a testament to the expertise of the team responsible for developing the diverse pipelines contributing to the UK Covid dashboard. 

Acknowledging the complexity and scale of the endeavor, the reported error is a minor setback considering the overall success of the project. 

This case study, utilized as a learning exercise, underscores the importance of evaluating challenges in building pipelines of such magnitude and is not likely to come close to providing a realistic solution that accommodates the various facets and challenges that were faced at the time.

For an analysis of some of the challenges faced, Pouria Hadjibagheri (MBE), the tech lead on the UK Covid-19 Dashboard, provides valuable insights in the following presentation, available here.

Sources