Amid the COVID-19 pandemic, the Hong Kong Government (HKG) strove to achieve Dynamic Zero Infection by introducing vaccine passports to encourage vaccination among the public. Furthermore, the HKG exercised its power under the Prevention and Control of Disease Ordinance (Chapter 599) to require individuals who had been present at specified premises to undergo a COVID-19 nucleic acid test.
To effectively monitor the COVID situation, we built an end-to-end pipeline solution that gathered data and created a dashboard. This dashboard allowed the public to understand the status of the pandemic and alerted them to potential outbreaks in their neighbourhoods.
First Published: 25 August 2022
Last Updated: 26 June 2026
- 1 - Why We Visualise Compulsory Test Frequency
- 2 - How Many Times Have You Been Selected
- 3 - Behind the Scenes
Earlier, we came across an intriguing post on HKGolden discussing the nuisances caused by the Compulsory Testing Notice (CTN) and the desire for a Dragon Tiger Billboard (also known as 龍虎榜 in Chinese or ranking billboard in English), which ranks the buildings that appeared most frequently on the CTN.
Unfortunately, there was no official publication providing such a ranking. The CTN was presented in PDF format, making it challenging to grasp the status of each location. Inspired by this idea, we initiated a project to create a dashboard that conveniently visualises the frequency of specified premises being listed on the CTN.
a Golden Son had been tested 3 times within a month.
If you resided in Hong Kong in 2022, it was likely that you were asked to undergo a COVID test. However, did you know how many times you were officially requested to take a test?
Simply visit the Compulsory COVID Testing Monitor on Tableau Public, and you can find the most recently affected buildings.
Warning
The dashboard is no longer being updated, and the last recorded entry for the CTN was on December 23, 2022.
You can host the data pipeline using Docker Compose, which spins up an Apache Airflow cluster to orchestrate the ETL process. We use the Adobe PDF Extract API in the pipeline, which requires API credentials. You can create one for free by following their instructions.
Caution
The pipeline is deprecated because Dynamic Zero Infection had already been achieved 👌🏻.
The architecture is quite straightforward. Every day, the Centre for Health Protection released a CTN in PDF format, with tables detailing all specified locations.
For the extraction of these tables, we utilised the Adobe PDF Extract API, which captured tables from PDFs more accurately than other open-source tools. To enrich the dataset, we supplemented the addresses with spatial information using the Hong Kong Address Parser to access the Office of the Government Chief Information Officer's APIs.
The ETL process is orchestrated by Apache Airflow, which schedules a daily DAG to automate the entire workflow. The processed data is consolidated using Pandas into an Excel file, which serves as the data source for the dashboard. The dashboard is crafted in Tableau and published on Tableau Public for the general public to review.
This was created as a personal hobby project and learning exercise.