Build a Weather Analytics ETL Pipeline

Build an ETL pipeline that pulls weather data and serves an interactive dashboard.

Introduction

30 Second Summary

Every weather app you check, every climate report you read, and every flight delay alert you receive starts with the same invisible process: raw data gets pulled from a source, cleaned up, and stored somewhere useful. That process is called an ETL pipeline (Extract, Transform, Load).

In this project, you will build a complete ETL pipeline that pulls real weather data from a live API, transforms it into analysis-ready tables, stores it in a database, and serves it through an interactive dashboard.

What You'll Build

When you finish, you will run one command to refresh your data pipeline, then launch a dashboard where you can compare temperatures, rainfall, and wind speeds across five major cities and watch the charts update as you change filters.

By the end of this project, you'll have:

  • A modular ETL pipeline you can run with a single command to extract weather data from the Open-Meteo API, clean it, and load it into a SQLite database.
  • An interactive Streamlit dashboard showing KPIs (average temperature, rainiest city, windiest day) and multi-city comparison charts that update live as you filter by city or date range.
  • A reusable project structure with separate extract, transform, and load modules that mirrors how real data teams organize production pipelines.
  • Secret Mission: Add a data quality report page that flags missing values and detects outliers in your weather data.

Are there any prerequisites?

This project assumes you have basic Python knowledge (variables, functions, imports) and a general understanding of SQL queries (SELECT, FROM, WHERE). You do not need prior experience with pandas or Streamlit.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Taking a moment to articulate the goal helps you stay focused as you wire together the different stages of the pipeline.

Set Up Your Project Environment

Every data pipeline needs a solid foundation before you can start pulling live data and transforming it. Without the right folder structure, your ETL modules, dashboard code, and data files will pile up in a messy heap.

In this step, you will create a clean project layout that separates extraction logic from dashboard code. You will also set up a Python virtual environment and install the three packages that power this pipeline: pandas, Streamlit, and requests.

In this step, get ready to:
  • Create the project folder structure with separate directories for ETL modules, the dashboard, and data storage.
  • Create a virtual environment and install pandas, streamlit, and requests.
  • Verify the installation by running streamlit hello and confirming the demo app launches.
Create the project folder structure

A well-organized project keeps your pipeline code, dashboard code, and output data in separate directories. This mirrors how real data teams structure repositories.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Move to your Desktop and create the project folder by running these commands:
cd ~/Desktop
mkdir weather-etl-pipeline
cd weather-etl-pipeline
  • Create the subdirectories and an empty __init__.py file (which marks etl/ as a Python package) by running:

macOS

mkdir etl dashboard data
touch etl/__init__.py

Windows

mkdir etl, dashboard, data
New-Item etl/__init__.py
  • Confirm the files and folders exist by running:
ls

You should see etl, dashboard, and data listed in the output.

Why an __init__.py file?

An empty __init__.py tells Python that the etl/ folder is a package. Later, your pipeline orchestrator will import modules from this package using from etl.extract import ....

Without it, Python would not recognize the folder as importable.

Don't see all three folders?

Make sure you ran mkdir from inside the weather-etl-pipeline folder, not from your Desktop. Run pwd to check your current location.

Help me figure out why my project folders weren't created.

Create a virtual environment and install packages

A virtual environment keeps this project's packages isolated from your system Python. This prevents version conflicts with other projects on your machine.

  • Create the virtual environment by running:

macOS

python3 -m venv venv

Windows

python -m venv venv
  • Activate the virtual environment by running:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

You should see (venv) appear at the beginning of your terminal prompt. This confirms you are working inside the virtual environment.

What is a virtual environment?

Think of it as a sealed box for your project's dependencies. Packages you install here stay here and do not interfere with other Python projects.

When you are done working, you can run deactivate to leave the environment. For now, keep it active.

  • Install the three packages this project needs by running:
pip install pandas streamlit requests

You will see pip downloading and installing each package along with its dependencies. The output ends with a success message listing the installed packages.

What are these packages for?

  • pandas handles data manipulation and transformation (DataFrames, cleaning, aggregation).
  • streamlit lets you build interactive dashboards in pure Python with no HTML or CSS.
  • requests makes HTTP calls to the weather API to pull raw data.

Seeing installation errors?

If pip reports that a package requires a newer Python version, check your version by running python3 --version (macOS) or python --version (Windows). This project requires Python 3.10 or higher.

Make sure you see (venv) in your prompt before running pip install. Without it, packages install globally instead of in your project environment.

Help me fix my pip install errors.

Verify the installation

Streamlit ships with a built-in demo app you can use to confirm everything installed correctly.

Before you run the next command, what do you think will happen? Will it print output in the terminal, or do something else?

  • Run the Streamlit demo by entering:
streamlit hello

A new browser tab opens automatically showing the Streamlit demo app with animated charts and sample widgets. This proves pandas, streamlit, and their dependencies are all working correctly.

  • Return to your terminal and press Ctrl+C to stop the demo server.

Browser didn't open?

Look at the terminal output for a line starting with Local URL:. Copy that URL and paste it into your browser manually.

If you see a "command not found" error, your virtual environment may not be active. Check for (venv) in your prompt and re-activate if needed.

Help me get streamlit hello working.

  • Open your project in VS Code. Press Cmd+Space (macOS) or the Windows key (Windows) to open search.
  • Type VS Code and press Enter to open it.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop and select the weather-etl-pipeline folder, then click Open.

In the Explorer sidebar on the left, you should see the etl, dashboard, and data folders along with the venv directory.

Your project environment is ready to go. Next up, you will write the extraction module that pulls real weather data from a live API.

Extract Weather Data from the API

In the previous step you set up the project environment and confirmed everything works. Now it's time to give your pipeline something to process.

Every ETL pipeline starts with extraction. You need a reliable data source that provides structured, queryable data without needing an API key or account signup. The Open-Meteo Historical Weather API is perfect for this. It serves real weather data for any location on Earth with a simple HTTP request.

In this step, get ready to:
  • Write the extraction module that pulls 30 days of daily weather data for 5 major cities.
  • Parse JSON API responses into a single pandas DataFrame.
  • Run the extraction and confirm 150 rows of raw data saved to CSV.
Set up the city configuration and API endpoint

The extraction module needs to know which cities to query and which weather variables to request. You will define these as constants at the top of the file so they are easy to change later.

  • In VS Code, create a new file called extract.py inside the etl/ folder.
  • Add the imports and city configuration by pasting this code:
import requests
import pandas as pd
from datetime import date, timedelta

# Cities and their coordinates for the API
CITIES = {
    "New York": {"latitude": 40.7128, "longitude": -74.0060},
    "London": {"latitude": 51.5074, "longitude": -0.1278},
    "Tokyo": {"latitude": 35.6762, "longitude": 139.6503},
    "Sydney": {"latitude": -33.8688, "longitude": 151.2093},
    "Paris": {"latitude": 48.8566, "longitude": 2.3522},
}

# Open-Meteo Historical Weather API endpoint
API_URL = "https://archive-api.open-meteo.com/v1/archive"

# Daily weather variables to request from the API
DAILY_VARIABLES = "temperature_2m_max,temperature_2m_min,precipitation_sum,wind_speed_10m_max"

What does this code do?

CITIES is a dictionary mapping city names to their latitude/longitude coordinates. The API needs coordinates, not city names, to look up weather data.

API_URL points to the Open-Meteo Historical Weather Archive, which serves ERA5 reanalysis data going back to 1940. DAILY_VARIABLES lists the four daily measurements you want: max temperature, min temperature, total precipitation, and max wind speed.

Write the extraction function

The extraction function loops over each city, calls the API, and collects the responses. The Open-Meteo API returns JSON where daily contains a time array and parallel arrays for each requested variable.

  • Below the DAILY_VARIABLES line, add the first part of the extraction function that calculates the date range and calls the API:
def extract_weather_data(days_back=30):
    """Extract historical weather data for all cities."""
    # Calculate date range (offset end by 6 days for data availability)
    end = date.today() - timedelta(days=6)
    start = end - timedelta(days=days_back - 1)

    all_data = []

    for city_name, coords in CITIES.items():
        # Build the request parameters for this city
        params = {
            "latitude": coords["latitude"],
            "longitude": coords["longitude"],
            "start_date": start.isoformat(),
            "end_date": end.isoformat(),
            "daily": DAILY_VARIABLES,
            "timezone": "auto",
        }

        # Call the Open-Meteo API
        response = requests.get(API_URL, params=params)
        response.raise_for_status()
        data = response.json()

What does this code do?

The function calculates a 30-day window ending 6 days before today. The 6-day offset exists because the Historical Weather Archive uses ERA5 reanalysis data, which has a processing delay of about 5 days.

For each city, it sends a GET request with the city's coordinates, the date range, and the list of daily variables. Setting timezone to "auto" lets the API resolve the local time zone automatically.

  • Directly below the data = response.json() line (still inside the for loop), add the code that parses each response into a DataFrame:
        # Parse response arrays into a DataFrame row per day
        daily = data["daily"]
        df = pd.DataFrame({
            "date": daily["time"],
            "latitude": coords["latitude"],
            "longitude": coords["longitude"],
            "city": city_name,
            "temperature_2m_max": daily["temperature_2m_max"],
            "temperature_2m_min": daily["temperature_2m_min"],
            "precipitation_sum": daily["precipitation_sum"],
            "wind_speed_10m_max": daily["wind_speed_10m_max"],
        })
        all_data.append(df)
        print(f"  Extracted {len(df)} days for {city_name}")

    # Combine all city DataFrames into a single result
    result = pd.concat(all_data, ignore_index=True)
    return result

What does this code do?

The API response contains daily["time"] (an array of date strings) and parallel arrays for each variable. You assemble these into a pandas DataFrame with one row per day per city.

After the loop finishes, pd.concat stacks all five city DataFrames into a single 150-row DataFrame (5 cities x 30 days).

  • At the bottom of extract.py (outside the function), add the runner block that saves the results to CSV:
if __name__ == "__main__":
    import os
    os.makedirs("data", exist_ok=True)
    print("Extracting weather data...")
    df = extract_weather_data()
    df.to_csv("data/raw_weather.csv", index=False)
    print(f"Saved {len(df)} rows to data/raw_weather.csv")

What does the runner block do?

The if __name__ == "__main__" guard means this code only runs when you execute the file directly. It ensures the data/ directory exists, calls the extraction function, and writes the resulting DataFrame to data/raw_weather.csv.

  • Save etl/extract.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file before moving on.

ⓧ I'd like to double check the full code

Your etl/extract.py should contain these sections in order. Scroll up to confirm each is present:

  • Imports (requests, pandas, date, timedelta).
  • CITIES dictionary with 5 city entries (New York, London, Tokyo, Sydney, Paris).
  • API_URL set to "https://archive-api.open-meteo.com/v1/archive".
  • DAILY_VARIABLES string with four comma-separated variable names.
  • extract_weather_data() function with the date range calculation, API loop, DataFrame construction, and pd.concat return.
  • if __name__ == "__main__" runner block that saves to data/raw_weather.csv.
Run the extraction and inspect the output

The module is complete. Time to run it and see real weather data flow in from the API.

  • Before you run this, what do you think the output will show? How many rows should appear if you're pulling 30 days for 5 cities?
  • Run the extraction module from the project root by entering this command in your terminal:
python etl/extract.py

You should see output like this:

Extracting weather data...
  Extracted 30 days for New York
  Extracted 30 days for London
  Extracted 30 days for Tokyo
  Extracted 30 days for Sydney
  Extracted 30 days for Paris
Saved 150 rows to data/raw_weather.csv

The API returned 30 days of data for each of the 5 cities, giving you 150 total rows of raw weather data.

Seeing a connection error?

  • Make sure you have an active internet connection. The script calls the Open-Meteo API over HTTPS.
  • If you see ModuleNotFoundError: No module named 'requests', your virtual environment may not be active. Activate it and try again.
  • If you see ModuleNotFoundError: No module named 'etl', make sure you are running the command from the project root folder (the one containing the etl/ directory).

Help me debug my extraction script

  • Confirm the CSV file was created by running:
ls data/

You should see raw_weather.csv listed in the output.

Your extraction module is pulling live weather data from the Open-Meteo API and saving it locally. Next up, you will try loading this raw data directly into a database to see why transformation matters.

Load Raw Data Directly into SQLite

You have raw weather data sitting in a CSV file. The next logical step is to get it into a database so a dashboard can query it.

The quickest approach is to dump the CSV straight into SQLite with zero transformation. This is the "naive" approach, and you are about to see exactly why real pipelines need a Transform step between Extract and Load.

In this step, get ready to:
  • Write a load script that dumps raw CSV data directly into a SQLite table.
  • Query the table and identify the problems that make the raw data unfit for a dashboard.
Write the load script

This script will read your raw CSV and push every row into a SQLite table without changing anything. No cleaning, no renaming, no aggregation.

  • Create a new file called load_raw.py inside your etl/ folder.
  • Add the load function by pasting the following code:
import pandas as pd
import sqlite3
import os


def load_raw_to_db():
    """Load raw CSV directly into SQLite without transformation."""
    os.makedirs("data", exist_ok=True)
    df = pd.read_csv("data/raw_weather.csv")

    conn = sqlite3.connect("data/weather.db")
    df.to_sql("raw_weather", conn, if_exists="replace", index=False)
    conn.close()

    print(f"Loaded {len(df)} rows into raw_weather table")
    return len(df)

What does this code do?

This function reads your raw CSV into a pandas DataFrame, then uses to_sql() to write every row directly into a SQLite table called raw_weather.

The if_exists="replace" argument means each run drops and recreates the table. This keeps the script idempotent so you can re-run safely.

Now add the runner block that will also query the table so you can see what the raw data looks like once loaded.

  • Below the load_raw_to_db() function, add this __main__ block:
if __name__ == "__main__":
    load_raw_to_db()
    conn = sqlite3.connect("data/weather.db")
    rows = conn.execute("SELECT * FROM raw_weather LIMIT 5").fetchall()
    print("\nFirst 5 rows (notice coordinates instead of city names in query):")
    for row in rows:
        print(row)
    conn.close()

What does the runner block do?

The __main__ block calls your load function, then opens a fresh connection and queries the first 5 rows. It prints them as raw tuples so you can see exactly what ended up in the database.

  • Save etl/load_raw.py.

✔️ Awesome, I've got everything!

Great. Make sure you have saved the file before running it in the next substep.

ⓧ I'd like to double check the full code

import pandas as pd
import sqlite3
import os


def load_raw_to_db():
    """Load raw CSV directly into SQLite without transformation."""
    os.makedirs("data", exist_ok=True)
    df = pd.read_csv("data/raw_weather.csv")

    conn = sqlite3.connect("data/weather.db")
    df.to_sql("raw_weather", conn, if_exists="replace", index=False)
    conn.close()

    print(f"Loaded {len(df)} rows into raw_weather table")
    return len(df)


if __name__ == "__main__":
    load_raw_to_db()
    conn = sqlite3.connect("data/weather.db")
    rows = conn.execute("SELECT * FROM raw_weather LIMIT 5").fetchall()
    print("\nFirst 5 rows (notice coordinates instead of city names in query):")
    for row in rows:
        print(row)
    conn.close()
Run the script and observe the problems

You have extracted data and written a loader. Time to see what happens when you skip the "T" in ETL.

  • Before you run this, think about what the query output will look like. Will the rows be easy to read and dashboard-ready, or will something feel off?
  • Run the load script by executing this command in your terminal:
python etl/load_raw.py

You will see output like this:

Loaded 150 rows into raw_weather table

First 5 rows (notice coordinates instead of city names in query):
('2026-07-02', 40.7128, -74.006, 'New York', 28.5, 20.1, 0.0, 15.2)
('2026-07-03', 40.7128, -74.006, 'New York', 30.1, 21.3, 2.4, 18.7)
('2026-07-04', 40.7128, -74.006, 'New York', 29.8, 19.8, None, 12.1)
('2026-07-05', 40.7128, -74.006, 'New York', 31.2, 22.0, 0.0, 14.5)
('2026-07-06', 40.7128, -74.006, 'New York', 27.9, 18.6, 5.1, 22.3)

This is the designed shortfall. The data loaded successfully, but look at what a dashboard would have to work with.

What is wrong with this data?

  • Coordinate noise: every row carries latitude and longitude numbers that are useless for chart labels or filters.
  • Raw date strings: the dates are plain text like '2026-07-02' instead of proper datetime objects. Filtering by date range in a dashboard requires parsed dates.
  • Null values: notice the None in the precipitation column. Nulls will break chart rendering and KPI calculations.
  • No aggregation layer: there is no summary table with averages per city. The dashboard would have to compute these on every page load.

These are the exact problems that a proper Transform step solves. In the next step, you will build transformations that produce clean, dashboard-ready tables.

Seeing a "No such file or directory" error?

  • Make sure you ran python etl/extract.py in Step 2 so that data/raw_weather.csv exists.
  • Check that your terminal is at the project root folder (the one containing the etl/ directory).

My script can't find raw_weather.csv. How do I fix this?

You have just experienced firsthand why the Transform step exists. Raw data loaded into a database is still raw data. Next, you will build proper transformations that turn these messy rows into clean, query-friendly tables your dashboard can use directly.

Transform and Load Clean Data

In the previous step, you saw the problem firsthand. Querying raw data gave you coordinate numbers where city names should be, plus null values scattered through the weather columns.

Now you'll build proper transformations that fix these issues and produce analysis-ready tables. The transform module will handle null values, parse dates, compute new columns, and create a city summary aggregation. Then a pipeline orchestrator will chain extract, transform, and load into a single command.

In this step, get ready to:
  • Write the transform module with null handling, date parsing, and city summary aggregation.
  • Write the pipeline orchestrator that chains all ETL stages together.
  • Run the pipeline and verify clean data in the database.
Write the transform module

The transform module has two jobs. First, it cleans the raw data by handling nulls and parsing dates. Second, it creates an aggregated summary table that collapses 30 days of data per city into a single row of averages and totals.

  • Create a new file called etl/transform.py in your etl/ folder.
  • Add the import and the transform_weather_data function by pasting the following code:
import pandas as pd


def transform_weather_data(df):
    """Clean and transform raw weather data."""
    print("Transforming data...")

    df = df.copy()

    df["date"] = pd.to_datetime(df["date"])

    df["temperature_2m_max"] = df["temperature_2m_max"].ffill()
    df["temperature_2m_min"] = df["temperature_2m_min"].ffill()
    df["precipitation_sum"] = df["precipitation_sum"].fillna(0)
    df["wind_speed_10m_max"] = df["wind_speed_10m_max"].ffill()

    df["temperature_avg"] = (df["temperature_2m_max"] + df["temperature_2m_min"]) / 2

    print(f"  Cleaned {len(df)} rows")
    return df

What does this code do?

  • df.copy() creates a copy so we don't accidentally modify the original DataFrame.
  • pd.to_datetime() converts date strings into proper datetime objects that charts and filters can understand.
  • .ffill() (forward fill) replaces null temperature and wind values with the previous day's reading. This is a common strategy for sensor data gaps.
  • .fillna(0) replaces null precipitation with zero, because a missing rain measurement most likely means no rain fell.
  • temperature_avg is a new computed column: the midpoint between the day's max and min temperature.
  • Below the transform_weather_data function, add the create_city_summary function:

def create_city_summary(df):
    """Create aggregated city summary table."""
    summary = df.groupby("city").agg(
        avg_temperature=("temperature_avg", "mean"),
        total_precipitation=("precipitation_sum", "sum"),
        max_wind_speed=("wind_speed_10m_max", "max"),
        days_with_rain=("precipitation_sum", lambda x: (x > 0).sum()),
    ).reset_index()

    summary["avg_temperature"] = summary["avg_temperature"].round(1)
    summary["total_precipitation"] = summary["total_precipitation"].round(1)
    summary["max_wind_speed"] = summary["max_wind_speed"].round(1)

    print(f"  Created summary for {len(summary)} cities")
    return summary

What does this code do?

  • groupby("city") splits the 150 rows into groups of 30 (one per city).
  • .agg() applies a different aggregation to each column: mean temperature, total precipitation, max wind speed, and a count of rainy days.
  • .round(1) keeps values tidy for display. Nobody needs 14 decimal places of rainfall.
  • Save etl/transform.py.
  • Test that the module loads and runs correctly by running this command:
python -c "from etl.transform import transform_weather_data, create_city_summary; import pandas as pd; df = pd.read_csv('data/raw_weather.csv'); clean = transform_weather_data(df); print(clean[['city', 'temperature_avg']].head(10))"

You should see 10 rows with readable city names and a computed temperature_avg column. The "Transforming data..." message confirms the function ran.

Seeing an ImportError?

  • Make sure you are running the command from your project root folder (the one containing the etl/ directory), not from inside etl/ itself.
  • Confirm etl/__init__.py exists. This empty file tells Python that etl/ is a package.
  • Check that your virtual environment is still active. You should see a prefix like (venv) in your terminal prompt.

Still stuck? Help me debug an ImportError when importing from etl.transform

✔️ Awesome, I've got everything!

Great. Make sure you have saved etl/transform.py before moving on.

ⓧ I'd like to double check the full code

Your etl/transform.py should contain these two sections in order:

  • The import pandas as pd line at the top, followed by the transform_weather_data function (handles null filling, date parsing, and temperature_avg calculation).
  • The create_city_summary function (groups by city, aggregates stats, rounds values).

Scroll up and confirm both chunks are present in your file, in that order.

Write the pipeline orchestrator

The pipeline orchestrator is the single entry point that chains extract, transform, and load together. It calls the functions you have already written, logs progress at each stage, and writes clean data into SQLite.

  • Create a new file called etl/pipeline.py in your etl/ folder.
  • Add the imports and the first half of the run_pipeline function (through the Extract stage) by pasting this code:
import sqlite3
import os
from etl.extract import extract_weather_data
from etl.transform import transform_weather_data, create_city_summary


def run_pipeline():
    """Run the full ETL pipeline."""
    os.makedirs("data", exist_ok=True)

    print("=" * 50)
    print("WEATHER ETL PIPELINE")
    print("=" * 50)

    # Extract
    print("\n[1/3] EXTRACT")
    raw_df = extract_weather_data()
    raw_df.to_csv("data/raw_weather.csv", index=False)
    print(f"  Total extracted: {len(raw_df)} rows")

What does this code do?

  • The imports pull in your extraction and transformation functions from the other modules. This is the modular pattern: each file owns one responsibility.
  • os.makedirs("data", exist_ok=True) ensures the output directory exists even on a fresh run.
  • The Extract stage calls extract_weather_data() and saves the raw CSV as a backup before any transformation happens.
  • Add the Transform stage, Load stage, and the __main__ block below the code you just pasted:

    # Transform
    print("\n[2/3] TRANSFORM")
    clean_df = transform_weather_data(raw_df)
    summary_df = create_city_summary(clean_df)

    # Load
    print("\n[3/3] LOAD")
    conn = sqlite3.connect("data/weather.db")
    clean_df.to_sql("daily_weather", conn, if_exists="replace", index=False)
    summary_df.to_sql("city_summary", conn, if_exists="replace", index=False)
    conn.close()

    print(f"  Loaded daily_weather: {len(clean_df)} rows")
    print(f"  Loaded city_summary: {len(summary_df)} rows")

    print("\n" + "=" * 50)
    print("PIPELINE COMPLETE")
    print("=" * 50)

    return {"daily_rows": len(clean_df), "summary_rows": len(summary_df)}


if __name__ == "__main__":
    run_pipeline()

What does this code do?

  • The Transform stage passes the raw DataFrame through your two transform functions. clean_df holds the row-level data. summary_df holds the city-level aggregation.
  • The Load stage writes both DataFrames into SQLite tables. if_exists="replace" means every pipeline run refreshes the data from scratch.
  • if __name__ == "__main__" lets you run this file directly from the terminal as your pipeline's single entry point.
  • Save etl/pipeline.py.

✔️ Awesome, I've got everything!

Great. Make sure you have saved etl/pipeline.py before running it.

ⓧ I'd like to double check the full code

Your etl/pipeline.py should contain these sections in order:

  • Imports (sqlite3, os, plus the functions from etl.extract and etl.transform).
  • The run_pipeline() function definition with the Extract section (prints banner, calls extract_weather_data(), saves CSV).
  • The Transform section (calls transform_weather_data and create_city_summary).
  • The Load section (writes both DataFrames to SQLite, prints confirmation, returns row counts).
  • The if __name__ == "__main__" block that calls run_pipeline().

Scroll up and confirm all sections are present in your file, in that order.

Run the full pipeline and verify clean data

Everything is wired together. Time to run the pipeline end-to-end and confirm the database now has clean, readable data.

  • Before you run this, predict: will you see city names or coordinate numbers in the output?
  • Run the full pipeline by executing this command from your project root:
python -m etl.pipeline

You should see output like this, confirming all three stages completed:

==================================================
WEATHER ETL PIPELINE
==================================================

[1/3] EXTRACT
  Extracted 30 days for New York
  Extracted 30 days for London
  Extracted 30 days for Tokyo
  Extracted 30 days for Sydney
  Extracted 30 days for Paris
  Total extracted: 150 rows

[2/3] TRANSFORM
Transforming data...
  Cleaned 150 rows
  Created summary for 5 cities

[3/3] LOAD
  Loaded daily_weather: 150 rows
  Loaded city_summary: 5 rows

==================================================
PIPELINE COMPLETE
==================================================

What should I see?

The output logs each stage clearly. 150 rows in daily_weather (30 days times 5 cities) and 5 rows in city_summary (one per city). If any API call fails, you will see an error during the Extract stage.

  • Now query the city_summary table to confirm you have readable city names and computed averages:
python -c "import sqlite3; conn = sqlite3.connect('data/weather.db'); rows = conn.execute('SELECT city, avg_temperature FROM city_summary').fetchall(); [print(r) for r in rows]; conn.close()"

You should see five tuples with city names (New York, London, Tokyo, Sydney, Paris) and their average temperatures. No more coordinate numbers.

Seeing a ModuleNotFoundError?

  • Make sure you run the command from your project root folder (the one containing the etl/ directory). Running from inside etl/ breaks package imports.
  • Confirm etl/__init__.py exists. Without it, Python does not recognize etl/ as a package.
  • Check that your virtual environment is active. If pandas or requests is not found, reactivate with source venv/bin/activate (macOS) or venv\Scripts\activate (Windows).

Still stuck? Help me debug a ModuleNotFoundError when running python -m etl.pipeline

Your ETL pipeline is working end-to-end. A single command now extracts fresh weather data, cleans it, and loads it into two analysis-ready tables. Next up, you'll build an interactive dashboard that puts this data on screen.

Build the Interactive Dashboard

Your ETL pipeline built clean, queryable tables in SQLite. But nobody can see that data until you put a visual layer on top of it.

In this step, you will build a Streamlit dashboard that reads directly from your database and displays KPIs, filters, and charts. This is the kind of deliverable you can demo in a job interview.

In this step, get ready to:
  • Connect the dashboard to your SQLite database with cached data loading.
  • Add sidebar filters and three KPI metric cards.
  • Add a temperature trend line chart and a precipitation bar chart.
Create the dashboard and connect to your database

Streamlit apps are plain Python scripts. When you run one, Streamlit re-executes the entire script top-to-bottom every time a user interacts with a widget. Caching prevents the database query from running on every single interaction.

  • Create the file dashboard/app.py by pasting the following code:
import streamlit as st
import pandas as pd
import sqlite3

# Configure the page to use the full browser width
st.set_page_config(page_title="Weather Analytics Dashboard", layout="wide")

st.title("Weather Analytics Dashboard")


# Cache the database query so it only runs once
@st.cache_data
def load_data():
    conn = sqlite3.connect("data/weather.db")
    daily = pd.read_sql("SELECT * FROM daily_weather", conn)
    summary = pd.read_sql("SELECT * FROM city_summary", conn)
    conn.close()
    daily["date"] = pd.to_datetime(daily["date"])
    return daily, summary


daily_df, summary_df = load_data()

What does this code do?

The @st.cache_data decorator tells Streamlit to run load_data() once and cache the result. On subsequent interactions (clicking a filter, changing a date), Streamlit returns the cached DataFrames instead of re-querying the database.

The layout="wide" setting uses the full browser width, giving your charts and KPIs room to breathe.

  • Save dashboard/app.py.
  • Launch the dashboard by running this command from your project root:
streamlit run dashboard/app.py

Your browser opens automatically. You should see the title "Weather Analytics Dashboard" on a blank page.

What is st.cache_data?

Every time you interact with a Streamlit widget, the entire script re-runs from top to bottom. Without caching, that means a fresh database query on every click. The @st.cache_data decorator stores the function's return value so subsequent re-runs skip the expensive work and return a cached copy.

Browser didn't open?

Streamlit prints a local URL in the terminal (usually http://localhost:8501). Copy and paste it into your browser manually.

If you see a "ModuleNotFoundError", make sure your virtual environment is still active. Help me fix my Streamlit import error

Add sidebar filters and KPI metrics

The dashboard needs interactive controls so users can slice the data by city and date range. These filters power the KPI cards that summarize the current view.

  • In dashboard/app.py, add the following code below the daily_df, summary_df = load_data() line:
# Sidebar filters
st.sidebar.header("Filters")
cities = st.sidebar.multiselect(
    "Select Cities",
    options=daily_df["city"].unique().tolist(),
    default=daily_df["city"].unique().tolist(),
)
date_range = st.sidebar.date_input(
    "Date Range",
    value=(daily_df["date"].min().date(), daily_df["date"].max().date()),
    min_value=daily_df["date"].min().date(),
    max_value=daily_df["date"].max().date(),
)

# Apply filters
if len(date_range) == 2:
    start_date, end_date = date_range
    filtered = daily_df[
        (daily_df["city"].isin(cities))
        & (daily_df["date"].dt.date >= start_date)
        & (daily_df["date"].dt.date <= end_date)
    ]
else:
    filtered = daily_df[daily_df["city"].isin(cities)]

What does this code do?

  • st.sidebar.multiselect creates a dropdown where users pick one or more cities. All five are selected by default.
  • st.sidebar.date_input adds a date range picker bounded by the data's actual date range.
  • The filtered DataFrame applies both filters. Every element below this point will use filtered instead of the full dataset.
  • Save the file and check your browser. You should see a sidebar on the left with a city multiselect and a date range picker.

Now add the three KPI metric cards below the filter code you just pasted:

# KPI metrics
col1, col2, col3 = st.columns(3)

with col1:
    avg_temp = filtered["temperature_avg"].mean()
    st.metric("Avg Temperature", f"{avg_temp:.1f} C")

with col2:
    if not filtered.empty:
        rainiest = filtered.groupby("city")["precipitation_sum"].sum().idxmax()
        st.metric("Rainiest City", rainiest)
    else:
        st.metric("Rainiest City", "N/A")

with col3:
    if not filtered.empty:
        windiest_row = filtered.loc[filtered["wind_speed_10m_max"].idxmax()]
        st.metric("Windiest Day", f"{windiest_row['wind_speed_10m_max']:.0f} km/h ({windiest_row['city']})")
    else:
        st.metric("Windiest Day", "N/A")

What do these metrics show?

  • Avg Temperature calculates the mean of temperature_avg across all filtered rows.
  • Rainiest City groups by city, sums precipitation, and picks the city with the highest total.
  • Windiest Day finds the single row with the highest wind speed and displays the value alongside the city name.
  • The if not filtered.empty checks prevent crashes when a user deselects all cities.
  • Save dashboard/app.py and check your browser.

You should see three metric cards at the top of the page showing a temperature value, a city name, and a wind speed value.

Seeing a NameError for filtered?

Make sure the sidebar filter code (with the filtered = daily_df[...] assignment) appears above the KPI code. Streamlit runs top-to-bottom, so filtered must be defined before it is used.

Help me fix my NameError in the dashboard

Add comparison charts

KPI cards give a snapshot. Charts let users compare trends across cities over time.

  • In dashboard/app.py, add the following code below the KPI metrics section:
# Charts
st.subheader("Temperature Trends")
if not filtered.empty:
    temp_chart = filtered.pivot_table(
        index="date", columns="city", values="temperature_avg"
    )
    st.line_chart(temp_chart)

st.subheader("Total Precipitation by City")
if not filtered.empty:
    precip_data = filtered.groupby("city")["precipitation_sum"].sum().reset_index()
    precip_data.columns = ["City", "Total Precipitation (mm)"]
    st.bar_chart(precip_data.set_index("City"))

What do these charts show?

  • The pivot_table reshapes the data so each city becomes its own column. st.line_chart then plots one line per city on the same time axis.
  • The bar chart groups total precipitation by city. st.bar_chart expects the index to be the category axis, so we set the city as the index.
  • Save dashboard/app.py.

Before you check the browser, what do you expect to see below the KPI cards?

Refresh your browser. You should see a multi-line temperature chart with one coloured line per city, followed by a bar chart comparing total precipitation across the five cities.

  • Try deselecting a city in the sidebar multiselect. The charts and KPIs update immediately to reflect only the selected cities.

Charts not appearing?

Make sure the chart code is below the filtered assignment. If you accidentally placed it inside one of the with col blocks, the indentation will hide it inside a column.

Help me debug my missing Streamlit charts

✔️ Awesome, I've got everything!

Great. Double check you have saved dashboard/app.py and that the dashboard is running in your browser with all three sections visible: KPI cards, temperature line chart, and precipitation bar chart.

ⓧ I'd like to double check the full code

Your dashboard/app.py should contain these sections in order. Scroll through your file and confirm each block is present:

  • Imports (streamlit, pandas, sqlite3) and st.set_page_config call.
  • The @st.cache_data decorated load_data() function and the daily_df, summary_df = load_data() call.
  • Sidebar filters: st.sidebar.multiselect and st.sidebar.date_input, plus the filter-apply logic creating filtered.
  • KPI metrics: three st.metric calls inside st.columns(3).
  • Charts: a st.line_chart for temperature trends and a st.bar_chart for precipitation by city.

Your pipeline's data is now visible and interactive. Anyone can explore temperature trends, compare rainfall across cities, and filter by date. Next up, you will take this further with a Secret Mission that adds data quality monitoring to your dashboard.

Secret mission

Add a Data Quality Report Page

Production pipelines need monitoring. Can you add a Data Quality page that flags missing values and outliers? This proves to employers you think beyond "make it work" and consider data reliability.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally with no paid APIs or cloud services, so there are no ongoing costs.

Resources you used:

  • data/weather.db - SQLite database with your cleaned weather data (local, no cost).
  • data/raw_weather.csv - Raw extracted weather data file (local, no cost).
  • Virtual environment with pandas, streamlit, and requests packages installed.
  • Project files in etl/ and dashboard/ directories.

Keep everything running

No action needed. Choose this if you are still actively building or want to experiment with different cities, date ranges, or chart types.

  • Your project folder stays on your Desktop with all files intact.
  • You can re-run python etl/pipeline.py any time to refresh the database with the latest weather data.
  • Launch the dashboard again with streamlit run dashboard/app.py whenever you want to explore the data.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files stay on disk so you can pick up where you left off.

  • Stop the Streamlit server by pressing Ctrl+C in the terminal where it is running.
  • Deactivate your virtual environment by running:
deactivate

When you come back, reactivate the virtual environment and relaunch the dashboard.

Delete - I don't want to use this again

Remove all project files and start fresh. This deletes your database, CSV data, ETL scripts, dashboard code, and virtual environment.

  • Stop the Streamlit server by pressing Ctrl+C in the terminal where it is running.
  • Deactivate your virtual environment by running:
deactivate
  • Navigate to your Desktop and delete the entire project folder.

On macOS/Linux:

cd ~/Desktop
rm -rf weather-etl-pipeline

On Windows:

cd ~\Desktop
rmdir /s /q weather-etl-pipeline
  • Confirm the folder is gone by running ls (macOS/Linux) or dir (Windows) on your Desktop. The project folder should no longer appear.

Nice Work!

Nice Work!

You just built a complete data pipeline from scratch. Real weather data flows from a live API, through transformation logic, into a database, and out through an interactive dashboard you can demo to anyone.

You've learned how to:

  • Build a modular ETL pipeline that extracts live weather data from the Open-Meteo API, transforms it with pandas, and loads clean tables into a SQLite database.
  • Experience why data transformation matters by loading raw data first, seeing the problems (coordinates instead of city names, nulls breaking charts), and then building the fix.
  • Create an interactive Streamlit dashboard with KPI metrics, sidebar filters, and multi-city comparison charts that query your database directly.
  • Add a data quality monitoring page with column completeness checks and IQR-based outlier detection (Secret Mission).

Ready to quiz yourself?