ETL Pipeline

What is an ETL Pipeline?

Extract, transform, load — what data pipelines do, who builds them, and the World Bank data used in this course.

Extract: CSV Files

Reading CSVs with pandas, handling mixed data types, skipping metadata rows, and a first look at data quality.

Extract: JSON & XML

Reading JSON with pandas and the json module, JSON orientations, and parsing XML with ElementTree.

Extract: SQL Databases & APIs

Querying SQLite with pandas, SQLAlchemy engines, and pulling data from web APIs like the World Bank's.

Transform: Combining Data

Stacking data with concat, joining with merge, and cleaning keys so two sources can match.

Transform: Data Types, Dates & Encodings

Fixing data types, converting text to numbers, parsing dates, and reading files with unusual encodings.

Transform: Missing & Duplicate Data

Finding gaps, reshaping wide data with melt, filling with group means and forward/back fill, and removing duplicates.

Transform: Dummy Variables & Text Cleaning

Cleaning categorical text with replace and regular expressions, then one-hot encoding with get_dummies.

Transform: Finding & Removing Outliers

Box plots and the Tukey rule, one- vs two-dimensional outliers, and removing aggregate rows that masquerade as outliers.

Transform: Scaling & Feature Engineering

Normalization and standardization, storing scaling parameters, and creating new features like GDP per capita.

Load: Files & Databases

Writing cleaned data to CSV, JSON, and SQLite, using multiple tables, and verifying the load.

Building the ETL Pipeline

Packaging extract, transform, and load into functions and one runnable module, with logging, validation, and scheduling.