PNAD COVID-19

INTRODUCTION

This project explores how demographic, geographic, and social factors influenced the severity and spread of COVID-19 in Brazil during 2020. The objective was to process and analyze national-scale public data to identify trends related to symptoms, risk levels, and healthcare access. The analysis was driven by the development of a complete ETL pipeline in PySpark, structured to scale and adapt across different data tables and time periods.

SOURCES

The primary dataset used in this project comes from the PNAD COVID-19 microdata, provided by the Brazilian Institute of Geography and Statistics (IBGE). This survey was conducted monthly between May and November 2020 and contains over 1.8 million interviews. It includes detailed information on individuals’ demographic profiles (age, gender, race/ethnicity), symptoms experienced, health conditions, and whether or not they sought medical assistance. The data files are published in CSV format, accompanied by poorly structured dictionaries that required careful parsing and normalization.

For more information please access: https://www.ibge.gov.br/estatisticas/sociais/saude/27946-divulgacao-semanal-pnadcovid1.html?=&t=downloads

DATA MODELING

The analytical model was designed using a dimensional approach. Key dimension tables include:

dim_person: Stores individual demographic characteristics such as gender, age, race, education level, and household role.
dim_location: Contains location-related data, such as state, household type, and urban/rural classification.
dim_date: Describes time-based information including month, year, and formatted period identifiers.
fact_interviews: Central fact table that aggregates interview data, including symptom count, severity level, health plan coverage, hospitalization, and medical care sought.

EXTRACT, TRANSFORM, VALIDATE AND LOAD

The ETL pipeline was built with modularity in mind. A generic ETLProcessor class manages the full process for each table, and transformation logic is passed through configurable functions. Data is extracted from the IBGE website for each selected month, then cached locally to avoid redundant downloads. The transformation phase includes type casting, column renaming, ID-to-label mapping based on the survey dictionaries, and new feature creation (such as symptom severity levels and healthcare behavior indicators). Finally, the data is written in partitioned CSV format, one file per table, ready to be loaded into Power BI.

Validation steps were added to the pipeline to detect missing values, duplicated records, and schema mismatches. This ensures high data quality before reporting.

DASHBOARD

The cleaned and transformed data was loaded into Power BI for visualization. Dashboards were created to display the evolution of symptom cases over time, the geographic and demographic distribution of high-risk individuals, and access to healthcare services. Key insights include the increased vulnerability of Indigenous populations to severe cases, the relationship between age and hospitalization, and the higher symptom prevalence in the North and Northeast regions of Brazil.

Page updated

Google Sites

Report abuse