Case study 06 / 06
API to SQL ETL Pipeline
An idempotent, containerised ETL pipeline from a live REST API to SQL.
Extracts user profiles from a live REST API, flattens deeply nested JSON, standardises field formatting and loads clean records into a SQLite warehouse.
- Type
- Batch ETL pipeline
- Source
- RandomUser.me API
- Runs in
- Docker
- Milestone
- My first data engineering project
01
The problem
Raw data from external APIs is rarely ready to use. It arrives nested, inconsistently formatted and full of fields nobody needs.
This project takes on a common early data engineering task: turning an unreliable external source into records a database and its users can rely on, on a repeatable basis rather than as a one-off script.
02
What I built
A containerised pipeline that pulls user profiles from a live public API, cleans and standardises them with Python, and loads them into a local SQL warehouse with an idempotent load.
01
Extract
Pulls 10 raw user profiles per run from the RandomUser.me API and saves the untouched payload to a landing zone.
02
Transform
Flattens the nested JSON, drops fields that aren't needed, lowercases emails and capitalises names.
03
Load
Writes to a users table in SQLite with UPSERT, so a rerun updates records instead of duplicating them.
04
One entry point
A single main.py runs the three steps in order, inside a Docker container.
03
How it works
- 1
Request
extract.py calls the API over HTTP and writes raw_users.json to the data folder.
- 2
Clean
transform.py reads the raw file and produces flat, standardised records.
- 3
Upsert
load.py writes them to SQLite with ON CONFLICT DO UPDATE.
- 4
Persist
A volume mount keeps the database file on the host after the container exits.
04
Tech stack and why
Python
Extraction and transformation logic.
Requests
HTTP calls to the API.
SQLite
A lightweight relational database for the local warehouse.
Docker
Runs the same way on any machine.
05
Challenges and what I learned
Messy nested JSON
The API returns nested objects, so names and other fields have to be pulled out and flattened before they fit a relational table.
Safe reruns
Running the pipeline twice shouldn't create duplicates or crash. UPSERT makes every run idempotent, the same pattern production pipelines rely on.
What I learned
- Keeping the raw data in its own landing zone, apart from processed records, makes every run easy to inspect and repeat.
- Idempotency matters from the very first pipeline, not only in production.
What this is and isn't
A learning project, not a production system. Next on the list: scheduling with Airflow, PostgreSQL in place of SQLite, unit tests for the transform step and structured logging.
06
Links
See API to SQL ETL Pipeline for yourself.
Have something like this in mind?
Have a project, role or research opportunity in mind? Let's talk.