Build a Weather Data Pipeline

Build a Python ETL pipeline that pulls live weather data into SQLite.

Introduction

30 Second Summary

Every weather app on your phone follows the same pattern: pull raw data from a source, clean it up, and store it somewhere useful. In data engineering, this pattern has a name: ETL (Extract, Transform, Load).

In this project, you will build an ETL pipeline from scratch using Python. Your pipeline will pull live weather forecasts from the internet, transform them into daily summaries, and store everything in a local database you can query with SQL.

What You'll Build

Run one command, watch live weather data flow in from the Open-Meteo API, and then query your database to find out which day this week will be the hottest.

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

  • A single python pipeline.py command that pulls live forecast data from the internet and loads clean daily summaries into a database automatically.
  • A SQLite database you built from scratch, filled with 7 days of structured weather data you can open and query anytime.
  • SQL queries that answer real questions about your data, like which day will be the hottest or rainiest this week.
  • Secret Mission: extend your pipeline to handle multiple cities and add data quality validation that catches bad records before they reach the database.

Do I need any prior experience?

No data engineering experience is needed. You will need Python and a code editor (like VS Code) already installed on your machine. Step 1 covers everything else.

Before We Start

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

Set Up Your Project and Install Dependencies

Before writing any pipeline code, you need a dedicated workspace and the one external library your script depends on.

In this step, you will create a project folder on your Desktop, install the requests library for making HTTP calls, and confirm everything runs with a simple test script.

In this step, get ready to:
  • Create a weather-pipeline project folder on your Desktop.
  • Install the requests library for making HTTP calls to the weather API.
  • Create and run pipeline.py to confirm Python works in your project.
Create your project folder
  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Open the integrated terminal by pressing Ctrl+` (the backtick key, top-left of your keyboard).

What is a terminal?

A terminal is a text-based interface where you type commands to interact with your computer. Instead of clicking through menus, you type short commands to create folders, install tools, and run scripts.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir weather-pipeline
cd weather-pipeline
  • Now open this folder in VS Code so it becomes your workspace. Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the weather-pipeline folder, and click Open.

Trust the authors prompt

VS Code may ask if you trust the authors of the files in this folder. Click Yes, I trust the authors to continue.

  • Open the integrated terminal again in your new workspace by pressing Ctrl+`.
  • Confirm you are in the right folder by running:
pwd

You should see a path ending in weather-pipeline.

Not seeing weather-pipeline in the path?

Make sure you selected the correct folder when you clicked Open Folder. Close VS Code, reopen it, and repeat the File then Open Folder step, selecting weather-pipeline from your Desktop.

Help me fix this

Install the requests library

Your pipeline will call a weather API over the internet. The requests library makes HTTP calls simple in Python.

  • Install requests by running this command in your terminal:
pip install requests==2.34.2

You should see pip downloading and installing the package, ending with a success message.

What is pip?

pip is Python's package manager. It downloads and installs libraries from the internet so you can use them in your code.

The ==2.34.2 part pins the exact version, so your project behaves the same way every time.

Seeing 'pip: command not found'?

On macOS, try pip3 instead of pip. If that also fails, your Python installation may not have added pip to your system path. Reinstall Python from python.org and ensure you check the box that says Add Python to PATH during installation.

Help me fix this

Create pipeline.py and test your setup

Now you will create the Python file that will eventually hold your entire pipeline. A simple print statement proves Python can run code in this folder.

  • In the VS Code Explorer sidebar (left panel), click the New File icon next to the folder name.
  • Name the file pipeline.py and press Enter.
  • Type the following code into pipeline.py:
print("Hello from pipeline.py")

What does this code do?

print() sends text to the terminal. If you see the message when you run the script, it proves Python is working correctly in your project folder.

  • Save the file by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Run your script by entering this command in the terminal:
python pipeline.py

You should see Hello from pipeline.py printed in the terminal.

Seeing 'python: command not found'?

On macOS, try python3 pipeline.py instead. If you use python3 here, use python3 for all commands in this project.

Help me fix this

✔️ Awesome, I've got everything!

Great. Double check you have saved pipeline.py and move on to the final verification below.

ⓧ I'd like to double check the full code

Your pipeline.py should contain exactly this:

print("Hello from pipeline.py")

Before you run this, what do you think will happen when Python tries to import the requests library you just installed?

  • Confirm the requests library is importable by running:
python -c "import requests; print(requests.__version__)"

You should see 2.34.2 printed in the terminal. This confirms Python can find and use the library your pipeline needs.

Seeing an import error?

If you see a ModuleNotFoundError, the install may have targeted a different Python version than the one you are running. Try pip3 install requests==2.34.2 and then rerun the check with python3 -c "import requests; print(requests.__version__)".

Help me fix this

Your workspace is ready. You have a project folder, the requests library installed, and a working Python script. Next up, you will use that library to pull live weather data from the internet.

Extract Weather Data from the API

Your project folder is ready and the requests library is installed. Now it is time to pull live data from the internet.

The first stage of any data pipeline is extraction. You need raw data before you can clean it, reshape it, or store it. In this step, you will call a public weather API and see live forecast data print to your terminal.

In this step, get ready to:
  • Write a Python function that calls the Open-Meteo weather API.
  • Parse the JSON response and confirm real data arrived.
  • Add error handling so the pipeline fails gracefully.
Write the extract function

The Open-Meteo API provides free weather forecast data with no sign-up or API key required. You send it coordinates and tell it which weather variables you want. It returns JSON containing hourly measurements for the next 7 days.

  • In pipeline.py, select all the existing code (the test print statement) and replace it with this:
import requests
import sqlite3
from datetime import datetime


def extract():
    """Extract 7-day hourly weather forecast from Open-Meteo API."""
    url = "https://api.open-meteo.com/v1/forecast"
    params = {
        "latitude": 40.71,
        "longitude": -74.01,
        "hourly": "temperature_2m,relative_humidity_2m,precipitation",
        "timezone": "auto",
        "forecast_days": 7,
    }

    response = requests.get(url, params=params)

    if response.status_code != 200:
        print(f"Error: API returned status code {response.status_code}")
        return None

    data = response.json()
    print(f"Extracted {len(data['hourly']['time'])} hourly records")
    return data

What does this code do?

  • url points to the Open-Meteo forecast endpoint. No API key needed.
  • params tells the API what you want: coordinates for New York City, three hourly weather variables (temperature, humidity, precipitation), the local timezone, and 7 days of forecast.
  • requests.get() sends an HTTP request and returns the response.
  • The if response.status_code != 200 block catches API errors. If something goes wrong, the function prints a message and returns None instead of crashing.
  • response.json() parses the raw response text into a Python dictionary you can work with.
  • Save pipeline.py. The function is defined but needs a caller to actually run. You will add that next.
Run the extract function

A function definition does nothing until something calls it. Python's if __name__ == "__main__" guard is the standard way to say "run this code when the file is executed directly."

  • Add this block at the very bottom of pipeline.py, below the extract() function:
if __name__ == "__main__":
    print("=== Weather Data Pipeline ===\n")
    raw_data = extract()

What does this code do?

  • if __name__ == "__main__" makes the enclosed code run only when you execute the file directly with python pipeline.py.
  • It calls extract() and stores the returned data in raw_data. Later steps will pass this variable through the rest of the pipeline.
  • Save pipeline.py.

Before you run this, what do you think the terminal will print? How many hourly records would 7 days produce?

  • Run your pipeline by executing this command in your terminal:
python pipeline.py

You should see output like this:

=== Weather Data Pipeline ===

Extracted 168 hourly records

168 records is 7 days multiplied by 24 hours per day. Your function just pulled live weather forecast data from the internet.

Seeing an error?

  • If you see ModuleNotFoundError: No module named 'requests', the library is not installed in the Python environment your terminal is using. Run pip install requests==2.34.2 again.
  • If you see SyntaxError, double-check that the params dictionary has commas after every line and a closing }.
  • If you see the API error message (Error: API returned status code...), check that you have an internet connection.

Help me troubleshoot my pipeline error.

What is the data structure you received?

The Open-Meteo API returns a JSON object with an hourly key. Inside it, you get parallel arrays: a time array of ISO timestamps, a temperature_2m array of temperatures, a relative_humidity_2m array of percentages, and a precipitation array of millimeters.

Each index across these arrays represents the same hour. So time[0] and temperature_2m[0] belong together. In the next step, you will group these by date to produce daily summaries.

✔️ Awesome, I've got everything!

Great. Double check you have saved pipeline.py and it prints 168 hourly records when you run it.

ⓧ I'd like to double check the full code

Here is the complete pipeline.py at the end of this step:

import requests
import sqlite3
from datetime import datetime


def extract():
    """Extract 7-day hourly weather forecast from Open-Meteo API."""
    url = "https://api.open-meteo.com/v1/forecast"
    params = {
        "latitude": 40.71,
        "longitude": -74.01,
        "hourly": "temperature_2m,relative_humidity_2m,precipitation",
        "timezone": "auto",
        "forecast_days": 7,
    }

    response = requests.get(url, params=params)

    if response.status_code != 200:
        print(f"Error: API returned status code {response.status_code}")
        return None

    data = response.json()
    print(f"Extracted {len(data['hourly']['time'])} hourly records")
    return data


if __name__ == "__main__":
    print("=== Weather Data Pipeline ===\n")
    raw_data = extract()

Your pipeline can now reach out to the internet and grab live weather data. The problem is that 168 raw hourly records are hard to make sense of. Next, you will transform this messy data into clean daily summaries.

Transform Raw Data into Daily Summaries

In the previous step, you pulled live weather data from the Open-Meteo API. If you look at what printed, it's 168 hourly data points across 7 days. That's a wall of numbers no one can make sense of at a glance.

Real data pipelines always include a transformation step that reshapes raw data into something useful. In this step, you'll write a transform() function that converts those 168 hourly readings into 7 clean daily summaries.

In this step, get ready to:
  • Group 168 hourly records into daily buckets by splitting timestamps.
  • Calculate summary statistics (high/low temp, average humidity, total precipitation) for each day.
  • Print a formatted table to confirm the transformation produces clean output.
Write a function to group hourly data by date

The API returns timestamps in ISO format like 2026-08-10T14:00. Everything before the T is the date. You can split each timestamp on that character to group all 24 hourly readings into their respective day.

  • In pipeline.py, find the if __name__ == "__main__": block at the bottom of the file.
  • Add the following function above that block (below the extract() function) by pasting this code:
def transform(raw_data):
    """Transform hourly data into daily summaries."""
    # Return early if extraction failed
    if raw_data is None:
        return []

    # Pull out the arrays from the API response
    hourly = raw_data["hourly"]
    times = hourly["time"]
    temperatures = hourly["temperature_2m"]
    humidity = hourly["relative_humidity_2m"]
    precipitation = hourly["precipitation"]

    # Group each hourly reading into its date bucket
    daily_data = {}
    for i in range(len(times)):
        date = times[i].split("T")[0]

        if date not in daily_data:
            daily_data[date] = {
                "temperatures": [],
                "humidity": [],
                "precipitation": [],
            }

        daily_data[date]["temperatures"].append(temperatures[i])
        daily_data[date]["humidity"].append(humidity[i])
        daily_data[date]["precipitation"].append(precipitation[i])

What does this code do?

  • if raw_data is None: return [] safely handles the case where the API call failed in the extract step.
  • The five variable assignments unpack the nested JSON response into flat Python lists you can loop through.
  • times[i].split("T")[0] takes a timestamp like 2026-08-10T14:00 and keeps only 2026-08-10. Each date becomes a key in the daily_data dictionary, collecting all hourly values for that day.
Calculate daily summaries and print the results

Now that hourly readings are grouped by date, you can calculate one summary per day. For each date bucket, you need the highest temperature, lowest temperature, average humidity, and total precipitation.

  • Directly below the code you just pasted (still inside the transform() function), add this code:
    # Calculate one summary row per day
    daily_summaries = []
    for date, values in daily_data.items():
        summary = {
            "date": date,
            "high_temp": max(values["temperatures"]),
            "low_temp": min(values["temperatures"]),
            "avg_humidity": round(
                sum(values["humidity"]) / len(values["humidity"]), 1
            ),
            "total_precipitation": round(sum(values["precipitation"]), 2),
        }
        daily_summaries.append(summary)

    # Print a formatted table so you can see the results
    print(f"Transformed into {len(daily_summaries)} daily summaries:")
    print(f"{'Date':<12} {'High':>6} {'Low':>6} {'Humidity':>8} {'Precip':>8}")
    print("-" * 44)
    for s in daily_summaries:
        print(
            f"{s['date']:<12} {s['high_temp']:>5.1f}C {s['low_temp']:>5.1f}C "
            f"{s['avg_humidity']:>7.1f}% {s['total_precipitation']:>7.2f}mm"
        )

    return daily_summaries

What does this code do?

  • max() and min() find the hottest and coldest hours in each day. These become your high and low temperatures.
  • round(sum(...) / len(...), 1) calculates the average humidity and rounds to 1 decimal place for readability.
  • The formatted print block uses Python f-string alignment (<12 for left-align, >6 for right-align) to produce a clean table. This proves your transformation worked before you load anything into a database.
Wire up the transform step and run the pipeline

Your function is complete, but the main block at the bottom of the file doesn't call it yet. You need to pass the extracted data into your new function.

  • In the if __name__ == "__main__": block at the bottom of pipeline.py, find this line:
    raw_data = extract()
  • Add this line directly below it:
    daily_summaries = transform(raw_data)
  • Save pipeline.py.

Before you run this, think about what you expect to see. The extract step printed 168 hourly records last time. What should the transform step print now?

  • Run the pipeline by executing this command:
python pipeline.py

Your terminal now prints the extraction count followed by a clean table of 7 daily summaries. Each row shows the date, high and low temperatures, average humidity, and total precipitation.

What should I see?

The output starts with the extraction line you saw in Step 2 ("Extracted 168 hourly records"), then shows "Transformed into 7 daily summaries:" followed by a table with columns for Date, High, Low, Humidity, and Precip. Your actual numbers will differ because the API returns live forecast data.

Seeing a NameError or IndentationError?

  • If you see NameError: name 'daily_data' is not defined, the second code chunk is not indented inside the transform() function. Every line after def transform(raw_data): must be indented by 4 spaces.
  • If you see IndentationError, make sure there is no gap or blank line between the grouping code and the calculation code. They should flow continuously inside the same function.
  • If you see NameError: name 'transform' is not defined, check that your transform() function is defined above the if __name__ block, not inside it.

Help me fix my transform function

✔️ Awesome, I've got everything!

Great. Double check you've saved pipeline.py and you see 7 daily summaries printed in your terminal.

ⓧ I'd like to double check the full code

Here is the full pipeline.py as it should look at the end of this step. Scroll through and compare section by section.

Your file has three sections in order:

  • The imports and extract() function (from Step 2).
  • The transform(raw_data) function you just added (grouping loop, then calculation loop, then print block, then return daily_summaries).
  • The if __name__ == "__main__": block calling extract() then transform(raw_data).
import requests
import sqlite3
from datetime import datetime


def extract():
    """Extract 7-day hourly weather forecast from Open-Meteo API."""
    url = "https://api.open-meteo.com/v1/forecast"
    params = {
        "latitude": 40.71,
        "longitude": -74.01,
        "hourly": "temperature_2m,relative_humidity_2m,precipitation",
        "timezone": "auto",
        "forecast_days": 7,
    }

    response = requests.get(url, params=params)

    if response.status_code != 200:
        print(f"Error: API returned status code {response.status_code}")
        return None

    data = response.json()
    print(f"Extracted {len(data['hourly']['time'])} hourly records")
    return data
def transform(raw_data):
    """Transform hourly data into daily summaries."""
    if raw_data is None:
        return []

    hourly = raw_data["hourly"]
    times = hourly["time"]
    temperatures = hourly["temperature_2m"]
    humidity = hourly["relative_humidity_2m"]
    precipitation = hourly["precipitation"]

    daily_data = {}
    for i in range(len(times)):
        date = times[i].split("T")[0]

        if date not in daily_data:
            daily_data[date] = {
                "temperatures": [],
                "humidity": [],
                "precipitation": [],
            }

        daily_data[date]["temperatures"].append(temperatures[i])
        daily_data[date]["humidity"].append(humidity[i])
        daily_data[date]["precipitation"].append(precipitation[i])
    daily_summaries = []
    for date, values in daily_data.items():
        summary = {
            "date": date,
            "high_temp": max(values["temperatures"]),
            "low_temp": min(values["temperatures"]),
            "avg_humidity": round(
                sum(values["humidity"]) / len(values["humidity"]), 1
            ),
            "total_precipitation": round(sum(values["precipitation"]), 2),
        }
        daily_summaries.append(summary)

    print(f"Transformed into {len(daily_summaries)} daily summaries:")
    print(f"{'Date':<12} {'High':>6} {'Low':>6} {'Humidity':>8} {'Precip':>8}")
    print("-" * 44)
    for s in daily_summaries:
        print(
            f"{s['date']:<12} {s['high_temp']:>5.1f}C {s['low_temp']:>5.1f}C "
            f"{s['avg_humidity']:>7.1f}% {s['total_precipitation']:>7.2f}mm"
        )

    return daily_summaries
if __name__ == "__main__":
    print("=== Weather Data Pipeline ===\n")
    raw_data = extract()
    daily_summaries = transform(raw_data)

Look at the difference between what printed in Step 2 and what you see now. Raw hourly numbers became a readable summary that anyone could glance at and understand. That's the power of the Transform step in a data pipeline.

Your data is clean and structured, but it disappears the moment the script ends. Next up, you'll load it into a SQLite database so it persists across runs and you can query it with SQL.

Load Data into SQLite

Right now your pipeline prints daily summaries to the terminal, then the data vanishes when the script ends. That means every time you want to analyze the data, you have to re-run the extraction and hope the API gives the same results.

In this step, you will add the "L" in ETL by writing a load() function that stores your transformed summaries in a SQLite database. After this step, your data survives across runs and you can query it with SQL.

In this step, get ready to:
  • Write a load() function that creates a SQLite database and table.
  • Insert daily summary rows using parameterized SQL queries.
  • Wire extract, transform, and load together in a main() block for end-to-end execution.
Write the load() function with database and table creation

Your load() function needs to connect to a database file and ensure a table exists to receive the data. SQLite stores everything in a single .db file, so there is no server to install.

  • In pipeline.py, find the if __name__ == "__main__": block at the bottom of the file.
  • Add the following load() function directly above that block:
def load(daily_summaries):
    """Load daily summaries into SQLite database."""
    if not daily_summaries:
        print("No data to load.")
        return

    con = sqlite3.connect("weather.db")
    cur = con.cursor()

    cur.execute("""
        CREATE TABLE IF NOT EXISTS daily_weather (
            date TEXT PRIMARY KEY,
            high_temp REAL,
            low_temp REAL,
            avg_humidity REAL,
            total_precipitation REAL
        )
    """)

What does this code do?

This is the first half of your load function. It handles the database setup before any data is inserted.

  • if not daily_summaries exits early if the transform step returned nothing, preventing empty database writes.
  • sqlite3.connect("weather.db") creates the database file if it does not exist, or opens it if it does.
  • CREATE TABLE IF NOT EXISTS ensures the table is ready without crashing on reruns when the table already exists.
  • TEXT PRIMARY KEY on the date column means each date can only appear once, which prevents duplicate rows.
  • Save pipeline.py.
  • Run the script to confirm no syntax errors were introduced:
python pipeline.py

You should see the same extract and transform output as before. The load() function exists but is not called yet, so no database is created.

Seeing a SyntaxError or IndentationError?

  • Make sure the def load(daily_summaries): line starts at column 0 (no leading spaces). It should be at the same indentation level as def transform.
  • Check that the triple-quoted SQL string ends with """ followed by a closing ) on the same line.
  • Confirm the function is placed above if __name__ == "__main__":, not inside it.

Why is my pipeline.py throwing a SyntaxError after adding the load function?

Insert rows with executemany and parameterized queries

The table is ready. Now you need to insert all 7 daily summary rows into it. INSERT OR REPLACE means the pipeline can be rerun safely. If a row for the same date already exists, it gets updated instead of causing a duplicate-key error.

  • In pipeline.py, add the following code directly below the cur.execute("""...CREATE TABLE...""") block you just wrote, still inside the load() function:
    cur.executemany(
        """
        INSERT OR REPLACE INTO daily_weather
        (date, high_temp, low_temp, avg_humidity, total_precipitation)
        VALUES (?, ?, ?, ?, ?)
        """,
        [
            (
                s["date"],
                s["high_temp"],
                s["low_temp"],
                s["avg_humidity"],
                s["total_precipitation"],
            )
            for s in daily_summaries
        ],
    )

    con.commit()
    print(f"\nLoaded {len(daily_summaries)} rows into the database.")
    con.close()

What does this code do?

  • executemany() runs the same INSERT statement once per item in the list, inserting all 7 rows in one call.
  • The ? placeholders are parameterized queries. Python fills in the actual values safely, preventing SQL injection.
  • INSERT OR REPLACE handles reruns. If you run the pipeline twice in one day, rows get updated instead of duplicated.
  • con.commit() saves the changes to disk. Without it, the inserts stay in memory and are lost when the connection closes.
  • con.close() releases the database file so other programs can access it.
  • Save pipeline.py.

Seeing an IndentationError on the executemany line?

  • The cur.executemany( line must be indented one level (4 spaces) inside load(). It sits at the same indentation as cur.execute( above it.
  • Make sure con.commit(), print(...), and con.close() are all at the same 4-space indent level inside the function.

Help me fix an indentation error in my load() function's executemany block.

Wire extract, transform, and load together in main

Your load() function is complete but never called. The final piece is updating the main() block so the pipeline runs all three steps in sequence: extract, transform, load.

  • In pipeline.py, scroll to the bottom and find the existing if __name__ == "__main__": block.
  • Find this code:
if __name__ == "__main__":
    print("=== Weather Data Pipeline ===\n")
    raw_data = extract()
    daily_summaries = transform(raw_data)
  • Replace the entire block with the following, which adds the load() call and a completion message:
if __name__ == "__main__":
    print("=== Weather Data Pipeline ===\n")

    raw_data = extract()
    daily_summaries = transform(raw_data)
    load(daily_summaries)

    print("\n=== Pipeline Complete ===")

What changed?

  • load(daily_summaries) passes the transformed data into your new function, which writes it to weather.db.
  • The === Pipeline Complete === message prints at the very end, confirming all three stages finished without errors.
  • Save pipeline.py.

Before you run this, what do you expect to see in the terminal output that was not there before?

  • Run the full pipeline:
python pipeline.py

You should see the familiar extract and transform output, followed by a new line: Loaded 7 rows into the database. and === Pipeline Complete === at the end.

  • Confirm the database file was created by running:
ls

You should see weather.db listed alongside pipeline.py in your project folder.

Don't see weather.db or the loaded message?

  • Check that load(daily_summaries) appears inside the if __name__ block, indented with 4 spaces.
  • Make sure con.commit() is present inside your load() function. Without it, the data stays in memory and never writes to disk.
  • Verify you are running the script from the weather-pipeline folder. The weather.db file is created in whatever directory your terminal is currently in.

My pipeline runs but I don't see weather.db being created. Help me debug.

Why INSERT OR REPLACE instead of plain INSERT?

Plain INSERT would crash with a constraint error the second time you run the pipeline, because the date column is a primary key and the same dates would already exist.

INSERT OR REPLACE makes the pipeline idempotent. You can rerun it as many times as you want and always end up with exactly 7 current rows, not 14 or 21.

✔️ Awesome, I've got everything!

Great. Double check you have saved pipeline.py and that weather.db appears in your project folder.

ⓧ I'd like to double check the full code

Your pipeline.py should contain these sections in order. Scroll up and confirm each one is present:

  • Imports (requests, sqlite3, datetime).
  • The extract() function that calls the Open-Meteo API.
  • The transform(raw_data) function that groups hourly data into daily summaries.
  • The load(daily_summaries) function with CREATE TABLE IF NOT EXISTS, executemany() using INSERT OR REPLACE, con.commit(), and con.close().
  • The if __name__ == "__main__": block calling extract(), transform(), and load() in sequence, ending with === Pipeline Complete ===.

Your data now survives beyond a single script run. Next up, you will write SQL queries against your database to answer real questions about the weather forecast.

Query Your Data with SQL

Your pipeline now extracts live weather data, transforms it into daily summaries, and stores everything in a SQLite database. That is real progress. But a pipeline that loads data and then stops is like a library with no catalogue.

In this step, you will write SQL queries that prove your data is correct, queryable, and useful. This completes the pipeline loop and gives you practice with SQL, the language every data engineering role requires.

In this step, get ready to:
  • Write a query function that runs three analytical SQL queries against your weather database.
  • Print readable answers: the hottest day, the rainiest day, and the average high temperature.
  • Wire the query step into your main pipeline so it runs automatically after every load.
Write the query function

Your query function connects to weather.db and asks three questions of your data using SQL. Each query uses ORDER BY and AVG() to find patterns in your 7 days of weather data.

  • In pipeline.py, add this function above the if __name__ == "__main__": block:
def query():
    """Run analytical queries against the database."""
    con = sqlite3.connect("weather.db")
    cur = con.cursor()

    # Hottest day
    cur.execute(
        "SELECT date, high_temp FROM daily_weather ORDER BY high_temp DESC LIMIT 1"
    )
    row = cur.fetchone()
    print(f"\nHottest day: {row[0]} at {row[1]}C")

    # Rainiest day
    cur.execute(
        "SELECT date, total_precipitation FROM daily_weather ORDER BY total_precipitation DESC LIMIT 1"
    )
    row = cur.fetchone()
    print(f"Rainiest day: {row[0]} with {row[1]}mm")

    # Average high temperature
    cur.execute("SELECT ROUND(AVG(high_temp), 1) FROM daily_weather")
    row = cur.fetchone()
    print(f"Average high temperature: {row[0]}C")

    con.close()

What do these SQL queries do?

  • ORDER BY high_temp DESC LIMIT 1 sorts all rows by temperature from highest to lowest, then grabs only the top row. That gives you the hottest day.
  • The same pattern with total_precipitation finds the rainiest day.
  • ROUND(AVG(high_temp), 1) calculates the mean of all high temperatures and rounds to 1 decimal place.
  • fetchone() retrieves a single result row from each query, which is all you need when your SQL already narrows results to one row.
Add the query step to your pipeline

The function exists but your pipeline does not call it yet. You need to wire it into the main block so it runs automatically after every load.

  • In pipeline.py, scroll down to the if __name__ == "__main__": block.
  • Find this line:
    load(daily_summaries)

    print("\n=== Pipeline Complete ===")
  • Add query() on a new line between load(daily_summaries) and the final print, so it looks like this:
    load(daily_summaries)
    query()

    print("\n=== Pipeline Complete ===")
Run the complete pipeline

Before you run this, what do you expect to see after the "Loaded 7 rows" message? Think about what your three queries ask for.

  • Save pipeline.py.
  • Run your pipeline by entering this command in the terminal:
python pipeline.py

After the extraction, transformation, and load messages, you should see three new lines of output with your query results. Something like:

Hottest day: 2026-08-12 at 34.5C
Rainiest day: 2026-08-14 with 12.3mm
Average high temperature: 31.2C

Why are my numbers different?

Your exact values depend on when you run the pipeline. The Open-Meteo API returns a live 7-day forecast, so the dates and temperatures change daily. As long as you see three lines with a date, a temperature, and a precipitation value, your pipeline is working correctly.

Seeing an error instead of query results?

  • If you see NameError: name 'query' is not defined, make sure the query() function is defined above the if __name__ block, not inside it.
  • If you see no such table: daily_weather, delete weather.db and re-run the pipeline. The load step will recreate it.
  • If the query results show None values, check that query() is called after load(daily_summaries), not before it.

Help me debug my query() function in my weather pipeline.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full code

Your pipeline.py is now too long to show in a single block. Scroll up through your file and confirm each section is present, in this order:

  • Imports at the top: requests, sqlite3, datetime.
  • extract() function that calls the Open-Meteo API.
  • transform(raw_data) function that groups hourly data into daily summaries.
  • load(daily_summaries) function that writes to weather.db.
  • query() function with three SQL queries (hottest day, rainiest day, average high).
  • if __name__ == "__main__": block calling extract(), transform(), load(), then query().

Your ETL pipeline is complete. With a single command, you extract live data from the internet, transform it into clean summaries, load it into a database, and query it for insights. Next up, the Secret Mission challenges you to extend this pipeline to handle multiple cities and add data quality validation.

Secret mission

Multi-City Pipeline with Data Quality Checks

Your pipeline works for one city. Real data pipelines handle multiple data sources and validate data quality before loading anything into production. Can you extend your pipeline to pull weather data for three cities and add validation that catches bad records before they reach your database?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your machine, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • weather-pipeline folder on your Desktop (contains pipeline.py and weather.db).
  • requests library installed via pip.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the pipeline further.

  • Your weather-pipeline folder stays on your Desktop.
  • The weather.db file is a small SQLite database (a few KB). It takes up negligible disk space.
  • The requests library remains installed in your Python environment for other projects to use.

Pause - I'll come back to this later

There are no running processes to stop. Your pipeline only runs when you execute it manually.

  • Nothing is running in the background. The script executes once and exits.
  • Your files and database will be right where you left them when you come back.
  • To pick up later, open a terminal in your weather-pipeline folder and run python pipeline.py again.

Delete - I don't want to use this again

Remove all project files and the installed library to start fresh.

  • Delete the project folder by running this command in your terminal:

macOS

cd ~/Desktop
rm -rf weather-pipeline

Windows

cd ~\Desktop
rmdir /s /q weather-pipeline
  • Uninstall the requests library by running:
pip uninstall requests
  • Type y when prompted to confirm the removal.
  • That covers everything. No accounts to close, no cloud services to shut down.

Nice Work!

Nice Work!

Nice work! You just built a complete ETL pipeline from scratch using Python. You can now extract live data from the internet, transform it into something useful, and store it in a queryable database.

You learned how to:

  • Built a working ETL pipeline that extracts live weather data from the Open-Meteo API, transforms raw hourly records into clean daily summaries, and loads them into a database with a single command.
  • Wrote Python data transformations that group, aggregate, and reshape messy JSON into structured daily summaries with high/low temperatures, average humidity, and total precipitation.
  • Created and queried a SQLite database using parameterized SQL, proving your data is correct by answering real questions like "which day is the hottest this week?".
  • Secret Mission: Extended the pipeline to handle multiple cities and added data quality validation that catches and logs bad records before they reach the database.

Ready to quiz yourself?