Beginner friendly4 weeks
Daily air quality ETL pipeline
Pull data from a public API every day, load it safely, and chart it.
Tools: Python, REST API, SQLite or PostgreSQL, Airflow or cron, Streamlit or Metabase
Why interviewers like it
It covers the whole job in a small space: fetch from an API, clean, store, schedule, monitor and show. It is also easy to demo live in a viva because the data is real and changes every day.
The problem
Air quality changes by hour and city, and the numbers sit behind an API. You will fetch the readings for a few cities every day, store them, make sure a rerun never creates duplicates, and show trends and bad-air days.
How the data moves
- Public API
- Python fetch
- Validate
- Database
- Scheduler
- Dashboard
The data
Open-Meteo offers a free air quality API that needs no key. OpenAQ is another public source. Read each service's terms of use and rate limits before you schedule anything.
Week by week
- Week 1Call the API for one city and print the JSON. Save a sample response in your repository.
- Week 2Parse the response, validate fields (no missing times, sensible numbers) and load to a table with a unique key on city and hour.
- Week 3Make reruns safe, add retries and logging, and schedule a daily run.
- Week 4Add a dashboard with a trend line and a list of days above a limit. Write the README.
Starter code
A load that is safe to run twice. Run it first, then grow it into the real project.
import json
import sqlite3
payload = json.loads("""
{"hourly": {"time": ["2024-03-01T00:00", "2024-03-01T01:00"], "pm2_5": [41.0, 38.5]}}
""")
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE air_quality (hour TEXT PRIMARY KEY, pm25 REAL)")
def load(payload):
rows = list(zip(payload["hourly"]["time"], payload["hourly"]["pm2_5"]))
db.executemany("INSERT OR REPLACE INTO air_quality VALUES (?, ?)", rows)
load(payload)
load(payload) # a rerun must not create duplicates
print(db.execute("SELECT COUNT(*), ROUND(AVG(pm25), 2) FROM air_quality").fetchone())(2, 39.75)
Two loads of the same data still give 2 rows, because the hour is the primary key.
What to hand in
- A scheduled script or an Airflow DAG
- A table with a unique key and a log of each run
- A dashboard with one trend chart
- A README explaining retries and what happens when the API is down
Viva and interview questions
Practise saying each answer out loud. Short and honest beats long and vague.
What does idempotent mean here?
Running the same load twice gives the same result as running it once. I used a primary key and INSERT OR REPLACE so a rerun cannot add duplicates.
What if the API is down or slow?
The script retries a few times with a growing wait, logs the failure, and the next scheduled run fills the gap because the load is idempotent.
How do you validate the data?
I check that the time and value exist, that the value is a number in a sensible range, and I count the rows loaded against the rows received.
Why a scheduler instead of running it by hand?
A scheduler runs it on time, records success or failure, retries and shows history. By hand you forget.
How would you add more cities?
Keep the cities in a config file, loop over them, and add the city to the primary key.
How to stand out
- Send yourself an alert when a run fails
- Store the raw JSON as well as the clean table
- Backfill the last 30 days with one command
Put it on your CV
Built a scheduled ETL pipeline that ingests hourly air quality data from a public API for multiple cities, with retries, logging and idempotent loads, and a dashboard of trends and threshold breaches.README outline
- What it does
- Architecture
- Setup and run
- Retries and reruns
- Data checks
- Limits and next steps