COVID-19 Data ETL Case Study

Overview

From two public datasets to one dependable daily workflow.

The goal was to combine COVID-19 data from The New York Times and Johns Hopkins, clean and reconcile the records, persist the results, and publish them for analysis. The entire workload runs without dedicated servers and is scheduled automatically.

DailyScheduled execution through EventBridge

RecoverableState tracking retries previously failed data

ObservableSNS reports success, failure, and affected rows

Architecture

The design evolved with the implementation.

I started by mapping the expected flow and refined it as testing exposed better ways to separate transformation, persistence, recovery, and reporting concerns.

Final AWS architecture for the serverless ETL pipeline — Final architecture connecting ingestion, processing, storage, recovery, and analytics services.

View the initial architecture

Pipeline

Extract, transform, validate, and load.

01

Schedule and extract

EventBridge starts the workflow each day at 12:15 UTC. A Python Lambda function downloads both datasets into Pandas dataframes.

02

Transform

The pipeline converts date fields, joins records by date, filters for US data, and replaces missing numeric values before loading.

03

Validate and load

Field formats and non-negative values are validated before Boto3 writes the transformed data to DynamoDB without overwriting existing partition keys.

04

Publish for analytics

Processed data is written to S3, cataloged by AWS Glue, queried through Athena, and consumed by QuickSight.

Reliability

Failures became part of the design.

A separate DynamoDB table records the status of each run. If processing fails, the next invocation handles the older data first and then moves to the latest dataset. SNS notifications report the status, number of updated rows, and the reason and row associated with a failure.

SNS email notification from the ETL process — Operational notifications report the result of each run.

Python unit tests for the ETL functions — Unit tests protect the transformation and loading functions.

Serverless Framework configuration for the ETL infrastructure — The Serverless Framework packages dependencies and defines repeatable AWS infrastructure.

Dashboard

Making the processed data useful.

QuickSight could not consume DynamoDB directly, so the reporting path was redesigned. The load process also writes processed records to S3; Glue catalogs them, Athena makes them queryable, and QuickSight turns the result into a dashboard.

QuickSight dashboard presenting the processed COVID-19 data — The final QuickSight dashboard built from the Athena dataset.

Lessons

What this project reinforced.

Designing failure recovery early makes scheduled workloads easier to trust.
Keeping transformation logic independent from storage makes it easier to reuse and test.
Analytics requirements can reshape the storage path, so consumers should influence architecture decisions.
Infrastructure as code makes a multi-service build repeatable and easier to evolve.