Build a Weather Data Pipeline with dbt

Build an ELT pipeline with Docker, PostgreSQL, and dbt to transform live weather data.

Introduction

30 Second Summary

Every weather app on your phone relies on the same basic idea. Raw data flows in from an API, gets cleaned and transformed, and comes out the other side as something useful.

In this project, you will build a complete ELT data pipeline that pulls real weather data from a live API into a PostgreSQL database running inside a Docker container. dbt then transforms the raw data into clean, analytics-ready tables.

What You'll Build

Picture filling your database with live weather data in one command, transforming and testing it with another, and pulling up clean daily summaries for five cities ranked by temperature.

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

  • A live pipeline where you run one Python script to pull real weather data from the Open-Meteo API for five global cities and watch rows fill up your database.
  • A Dockerized PostgreSQL database you start and stop with a single command. No local database install needed.
  • A single dbt build command that transforms raw data into daily weather summaries with city-level temperature rankings, catching data quality issues automatically before they reach your analytics.
  • Secret Mission: Build a weather alerts mart that flags extreme temperature days using window functions, then generate a multi-city temperature comparison chart with pandas and matplotlib.

Are there any prerequisites?

No prior experience with Docker, dbt, or data pipelines is needed. This project walks you through setting up every tool from scratch.

Before We Start

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

Set Up Docker and dbt

Before writing any pipeline code, you need two foundational tools: a database to store your weather data and a transformation engine to shape it into analytics-ready tables.

In this step, you will install Docker Desktop to run PostgreSQL inside a container. You will also install dbt (data build tool), the industry-standard transformation tool that turns raw data into clean, tested models.

In this step, get ready to:
  • Install Docker Desktop and start a containerized PostgreSQL database.
  • Set up a Python virtual environment and install dbt-postgres.
  • Initialize a dbt project and confirm it connects to your database.
Install Docker Desktop

Docker packages software into containers, isolated environments that run the same way on every machine. Instead of installing PostgreSQL directly, you will run it inside a Docker container.

  • Press the Windows key to open your search bar.
  • Type PowerShell and press Enter to open it.
  • Check if Docker is already installed by running this command:
docker --version

✔️ I see a version number

Docker is already installed. Skip ahead to the next substep.

ⓧ Command not found

  • Download the Docker Desktop installer from the official page:
  • Docker Desktop for Windows
  • Double-click Docker Desktop Installer.exe to run the installer.
  • When the installer asks which installation mode you prefer, choose per-user installation. This does not require administrator privileges.
  • On the Configuration page, make sure Use WSL 2 instead of Hyper-V is checked. This is the default and recommended backend.
  • Click Ok to finish the installation.

What to expect during installation

The installer may need to enable WSL 2 on your machine for the first time. This is a one-time setup that requires administrator privileges. If prompted by a User Account Control (UAC) dialog, click Yes to allow it.

If the installer asks you to reboot, go ahead and restart your computer. After rebooting, Docker Desktop will launch automatically.

  • After installation completes, press the Windows key, type Docker Desktop, and press Enter to launch it.
  • Wait for the Docker engine to start. You will see a green whale icon in your system tray when it is ready.
  • Return to your PowerShell window and verify the installation by running:
docker --version

You should see a version number printed in the output.

Still seeing command not found?

Make sure Docker Desktop is fully running (green whale icon in your system tray). Try closing and reopening your PowerShell window. Docker adds itself to your PATH during installation, but an already-open terminal may not see the change.

Help me troubleshoot Docker Desktop installation on Windows

Create your project folder and start PostgreSQL

Every pipeline needs a home. You will create a project folder and then define a PostgreSQL container inside it using a Docker Compose file.

  • 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

Why Docker instead of local PostgreSQL?

You already have PostgreSQL installed locally, but this project uses a Dockerized version instead. Docker gives you a clean, isolated database that starts and stops with a single command. There is no risk of conflicting with your existing local data or settings.

Containerization is also a core data engineering skill. Knowing how to manage databases in Docker is something hiring managers look for on resumes.

  • Create a file called docker-compose.yml in your weather-pipeline folder and paste the following content:
services:
  postgres:
    image: postgres:16
    container_name: weather_postgres
    environment:
      POSTGRES_USER: pipeline_user
      POSTGRES_PASSWORD: pipeline_pass
      POSTGRES_DB: weather_db
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U pipeline_user -d weather_db"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  pgdata:

What does this file do?

This file tells Docker Compose to start a PostgreSQL 16 container named weather_postgres. The environment section creates a database called weather_db with a user pipeline_user.

The ports section maps port 5432 inside the container to the same port on your machine. The volumes section stores database data in a named volume so it persists even if the container restarts.

The healthcheck runs pg_isready every 5 seconds to confirm PostgreSQL is accepting connections.

Port 5432 already in use?

Your local PostgreSQL installation may already be using port 5432. If docker compose up fails with a port conflict, stop your local PostgreSQL service first. On Windows, open Services (search for it in the Start menu), find the PostgreSQL service, right-click, and select Stop.

  • Save docker-compose.yml.
  • Start your PostgreSQL container by running this command:
docker compose up -d

The first run downloads the PostgreSQL 16 image, which may take a minute or two. The -d flag runs the container in the background so your terminal stays free.

  • Confirm the container is running by running:
docker compose ps

You should see weather_postgres listed with a status of running and port 5432 mapped.

Container not running?

Make sure Docker Desktop is fully started (green whale icon in the system tray). If you see a port conflict error, stop your local PostgreSQL service as described in the tip above.

Help me troubleshoot docker compose up failing

✔️ Awesome, I've got everything!

Great. Double check you have saved docker-compose.yml and your container is running.

ⓧ I'd like to double check the full code

Here is the complete docker-compose.yml for comparison:

services:
  postgres:
    image: postgres:16
    container_name: weather_postgres
    environment:
      POSTGRES_USER: pipeline_user
      POSTGRES_PASSWORD: pipeline_pass
      POSTGRES_DB: weather_db
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U pipeline_user -d weather_db"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  pgdata:
Install dbt-postgres and connect it to PostgreSQL

With your database running, you need a transformation tool to shape the raw data it will hold. dbt lets you write SQL models that clean, join, and aggregate data, then tests that catch problems before bad data reaches your reports.

  • Check your Python version by running:
python --version

✔️ I see version 3.11 or higher

Your Python version is compatible. Continue to the next instruction below.

ⓧ I see a version lower than 3.11

This project requires Python 3.11 or higher because several packages (including pandas) need it. On Windows, the easiest way to install a newer version alongside your current one is:

  • Download the latest Python installer from python.org/downloads.
  • Run the installer and make sure to check Add python.exe to PATH at the bottom of the first screen.
  • Close and reopen PowerShell, then run python --version again to confirm it shows 3.11 or higher.

Why does the version matter?

Later in this project you will use pandas and matplotlib for data analysis. Both libraries require Python 3.11 or higher in their latest versions.

ⓧ Command not found

Python is not installed on your machine. Install it before continuing:

  • Download the latest Python installer from python.org/downloads.
  • Run the installer and make sure to check Add python.exe to PATH at the bottom of the first screen.
  • Close and reopen PowerShell, then run python --version to confirm a 3.11+ version is installed.
  • Create a virtual environment inside your weather-pipeline folder by running:
python -m venv venv
  • Activate the virtual environment by running:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

Your terminal prompt should now show a (venv) prefix. This confirms the virtual environment is active.

  • Install dbt-postgres by running:
pip install dbt-postgres==1.11.0
  • Verify dbt is installed by running:
dbt --version

You should see version information for dbt-core and the dbt-postgres adapter.

dbt command not found?

Make sure your virtual environment is active. You should see (venv) in your terminal prompt. If it disappeared, re-activate it using the activation command above.

Help me troubleshoot dbt installation

The next command creates your dbt project. It is interactive and will prompt you for database connection details. Here are the values to enter at each prompt:

  • At the Which database would you like to use? prompt, enter 1 for postgres.
  • At the host prompt, enter localhost.
  • At the port prompt, press Enter to accept the default 5432.
  • At the user prompt, enter pipeline_user.
  • At the pass prompt, enter pipeline_pass. The text will be hidden as you type.
  • At the dbname prompt, enter weather_db.
  • At the schema prompt, enter weather_transform.
  • At the threads prompt, press Enter to accept the default 1.
  • Initialize your dbt project by running:
dbt init weather_transform

What did dbt init create?

This command created a weather_transform/ directory with a starter dbt project structure including dbt_project.yml, model folders, and example files.

It also created a connection profile at ~/.dbt/profiles.yml using the values you entered. This profile tells dbt how to reach your Dockerized PostgreSQL database.

  • Move into the new project folder by running:
cd weather_transform
  • Delete the example models that dbt generated. You will write your own models in later steps.

macOS

rm -r models/example

Windows

Remove-Item models/example -Recurse
  • Confirm the example folder is gone by running:
ls models

You should see an empty output or just the models directory with no example subfolder listed.

Now verify that dbt_project.yml was generated correctly. Open it in your text editor and confirm name is set to weather_transform and profile is set to weather_transform.

✔️ Awesome, I've got everything!

Great. Make sure you have saved dbt_project.yml and that you are inside the weather_transform directory.

ⓧ I'd like to double check the full code

Your dbt_project.yml should look like this:

name: weather_transform
version: "1.0.0"

profile: weather_transform

model-paths: ["models"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]

clean-targets:
  - target
  - dbt_packages

Before you run the final check, what do you expect dbt debug to report? Will all connection checks pass, or will something fail?

  • Test your dbt connection by running:
dbt debug

All checks should pass with OK status. This confirms dbt can find your project configuration and connect to the Dockerized PostgreSQL database.

Seeing a connection error?

Make sure your PostgreSQL container is still running. Switch back to the weather-pipeline folder and run docker compose ps to check. If the container stopped, restart it with docker compose up -d.

Double check that your profiles.yml values match the credentials in docker-compose.yml. The host should be localhost, the user pipeline_user, and the database weather_db.

Help me troubleshoot dbt debug connection failure

Your environment is locked in. You have a running PostgreSQL database inside Docker and a dbt project wired up to it. Next up, you will write a Python script that pulls live weather data from an API and loads it into your database.

Extract Weather Data and Load It Into PostgreSQL

Your database is running inside Docker and dbt is connected. The infrastructure is ready, but the database is empty.

Before you can transform anything, you need real data. In this step, you will build the "E" and "L" of your ELT pipeline. You will write a Python script that pulls live weather data from a free API, then loads every row into your Dockerized PostgreSQL database.

In this step, get ready to:
  • Fetch hourly weather data from the Open-Meteo API for five global cities.
  • Load all the data into a raw PostgreSQL table inside your Docker container.
  • Verify your data by querying the database directly.
Create the extraction script

The Open-Meteo API is a free weather API that returns hourly forecasts and recent historical data. It requires no API key and no signup.

  • Make sure your terminal is in the weather-pipeline folder (the project root, not the weather_transform subfolder). Navigate there by running:
cd ~/Desktop/weather-pipeline
  • Create a new file called extract_load.py in this folder and add the imports and configuration by copying this code:
import requests
import psycopg2

# Database connection settings for Dockerized PostgreSQL
DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "dbname": "weather_db",
    "user": "pipeline_user",
    "password": "pipeline_pass",
}

# Five global cities to fetch weather data for
CITIES = [
    {"name": "London", "latitude": 51.5074, "longitude": -0.1278},
    {"name": "New York", "latitude": 40.7128, "longitude": -74.0060},
    {"name": "Tokyo", "latitude": 35.6762, "longitude": 139.6503},
    {"name": "Sydney", "latitude": -33.8688, "longitude": 151.2093},
    {"name": "Berlin", "latitude": 52.5200, "longitude": 13.4050},
]

# Weather variables to request from the API
HOURLY_VARIABLES = "temperature_2m,relative_humidity_2m,wind_speed_10m,precipitation"
API_URL = "https://api.open-meteo.com/v1/forecast"

What does this code do?

DB_CONFIG holds the connection settings that match your docker-compose.yml environment variables. This is how your Python script talks to the PostgreSQL container running on localhost:5432.

CITIES lists five cities with their GPS coordinates. The API uses latitude and longitude to return location-specific weather data. HOURLY_VARIABLES tells the API which weather measurements to include in the response.

  • Below the API_URL line, add the fetch_weather function that calls the Open-Meteo API and parses the response:
def fetch_weather(city):
    # Build the API request parameters
    params = {
        "latitude": city["latitude"],
        "longitude": city["longitude"],
        "hourly": HOURLY_VARIABLES,
        "past_days": 7,
        "forecast_days": 7,
        "timezone": "auto",
    }
    response = requests.get(API_URL, params=params)
    response.raise_for_status()
    data = response.json()

    # Parse each hourly timestamp into a row
    rows = []
    hourly = data["hourly"]
    times = hourly["time"]
    for i in range(len(times)):
        rows.append((
            city["name"],
            city["latitude"],
            city["longitude"],
            times[i],
            hourly["temperature_2m"][i],
            hourly["relative_humidity_2m"][i],
            hourly["wind_speed_10m"][i],
            hourly["precipitation"][i],
        ))
    return rows

What does this function do?

  • past_days=7 requests a full week of recent historical data. Combined with forecast_days=7, you get 14 days of hourly data (about 336 rows per city).
  • The API returns arrays of timestamps and weather values. The for loop zips them together into tuples, one per hour.
  • raise_for_status() stops the script immediately if the API returns an error, so you catch problems early.
  • Save extract_load.py.
  • Test that the API is reachable and your code works by running this command:
python -c "from extract_load import fetch_weather, CITIES; print(len(fetch_weather(CITIES[0])), 'rows fetched for London')"

You should see a number around 336 followed by rows fetched for London. The exact count depends on how many forecast hours have data at the time you run it.

Seeing a ModuleNotFoundError?

  • If you see ModuleNotFoundError: No module named 'requests', make sure your virtual environment is activated. The requests library was installed as a dependency of dbt-postgres.
  • If you see ModuleNotFoundError: No module named 'extract_load', make sure your terminal is in the weather-pipeline folder where extract_load.py lives.
  • If you see a connection timeout, check your internet connection. The Open-Meteo API requires no key, but it does need network access.

help me debug this ModuleNotFoundError or connection issue with extract_load.py

Build the database loading functions

The extraction half is working. Now you need two functions to create a table in PostgreSQL and insert the weather rows. Both requests and psycopg2 are already installed as dependencies of dbt-postgres.

  • In extract_load.py, add the create_table and load_data functions below the fetch_weather function:
def create_table(conn):
    # Create the raw_weather schema and observations table
    with conn.cursor() as cur:
        cur.execute("CREATE SCHEMA IF NOT EXISTS raw_weather;")
        cur.execute("DROP TABLE IF EXISTS raw_weather.weather_observations;")
        cur.execute("""
            CREATE TABLE raw_weather.weather_observations (
                city_name TEXT,
                latitude DOUBLE PRECISION,
                longitude DOUBLE PRECISION,
                timestamp TEXT,
                temperature_2m DOUBLE PRECISION,
                relative_humidity_2m DOUBLE PRECISION,
                wind_speed_10m DOUBLE PRECISION,
                precipitation DOUBLE PRECISION
            );
        """)
    conn.commit()


def load_data(conn, rows):
    # Insert all weather rows into the table
    with conn.cursor() as cur:
        insert_query = """
            INSERT INTO raw_weather.weather_observations
            (city_name, latitude, longitude, timestamp,
             temperature_2m, relative_humidity_2m, wind_speed_10m, precipitation)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s);
        """
        cur.executemany(insert_query, rows)
    conn.commit()

What do these functions do?

  • create_table creates a raw_weather schema to keep raw data separate from the transformed data that dbt will create later. It drops and recreates the table each run so you always start fresh.
  • load_data uses executemany to insert all rows in one call. Each %s placeholder maps to one column in the tuple.
  • Now add the main function and the entry point at the very bottom of extract_load.py:
def main():
    # Fetch weather data for all cities
    all_rows = []
    for city in CITIES:
        print(f"Fetching weather data for {city['name']}...")
        rows = fetch_weather(city)
        all_rows.extend(rows)
        print(f"  Got {len(rows)} hourly records")

    print(f"\nTotal records: {len(all_rows)}")

    # Connect to PostgreSQL and load the data
    conn = psycopg2.connect(**DB_CONFIG)
    try:
        create_table(conn)
        load_data(conn, all_rows)
        print("Data loaded successfully into raw_weather.weather_observations")
    finally:
        conn.close()


if __name__ == "__main__":
    main()

What does the main function do?

main() orchestrates the full pipeline. It loops through all five cities, fetches their weather data, then opens a single database connection to create the table and load every row. The try/finally block ensures the database connection closes even if something goes wrong.

The if __name__ == "__main__" guard means main() only runs when you execute the script directly. It won't run if another file imports a function from this module.

  • Save extract_load.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before running it in the next substep.

ⓧ I'd like to double check the full code

Your extract_load.py has four sections. Scroll up and confirm each one is present, in this order:

  • Imports and configuration (DB_CONFIG, CITIES, HOURLY_VARIABLES, API_URL).
  • The fetch_weather function.
  • The create_table and load_data functions.
  • The main function and if __name__ == "__main__" block.
Run the pipeline and verify your data

Your script is complete. Time to run it and see real weather data flow into your database.

Before you run this, think about how many total rows you expect. You have 5 cities, each with 14 days of hourly data. What number would you predict?

  • Run the extraction and loading script:
python extract_load.py

You should see output like this, with each city reporting around 336 hourly records:

Fetching weather data for London...
  Got 336 hourly records
Fetching weather data for New York...
  Got 336 hourly records
Fetching weather data for Tokyo...
  Got 336 hourly records
Fetching weather data for Sydney...
  Got 336 hourly records
Fetching weather data for Berlin...
  Got 336 hourly records

Total records: 1680
Data loaded successfully into raw_weather.weather_observations

Script failing to connect to the database?

  • Make sure your Docker container is running. Run docker compose ps and confirm weather_postgres shows a status of running.
  • If you have a local PostgreSQL installation already using port 5432, stop it first. Two services cannot share the same port.
  • Check that the DB_CONFIG values in your script match your docker-compose.yml environment variables exactly.

help me debug this database connection error in my extract_load.py script

Now verify the data landed in your database. You can run SQL directly against the PostgreSQL container using docker exec.

  • Query the row count per city by running:
docker exec -it weather_postgres psql -U pipeline_user -d weather_db -c "SELECT city_name, COUNT(*) FROM raw_weather.weather_observations GROUP BY city_name;"

You should see a table with all five cities, each showing a count around 336.

What is this command doing?

docker exec -it weather_postgres runs a command inside your running PostgreSQL container. The psql tool then executes the SQL query with the -c flag and prints the result straight to your terminal.

This means you can query your Dockerized database without installing psql on your own machine.

Your database now has real weather data from five cities around the world. Next up, you will use dbt to transform this raw data into clean, analysis-ready tables.

Build dbt Staging Models

Your raw weather data is sitting in PostgreSQL, but it is not ready for analytics.

Column names like temperature_2m and wind_speed_10m are API shorthand that downstream consumers will not understand. Timestamps are stored as plain text strings. Some hourly slots might have missing values. Staging models are your first line of defense in a dbt project. They clean and standardize raw data before it reaches anyone downstream. If you come from a security background, think of it as sanitizing inputs before they enter your system.

In this step, get ready to:
  • Create a dbt source definition pointing to your raw weather data.
  • Build a staging model that cleans and renames columns into business-friendly names.
  • Add data quality tests and discover a real data integrity issue.
Create the dbt source definition and staging model

Before dbt can transform your raw data, it needs to know where that data lives. A source definition tells dbt which schema and table to read from. Then a staging model applies the first round of cleaning on top of that source.

  • Create the staging directory inside your dbt project by running this command:
mkdir -p weather_transform/models/staging
  • Confirm the directory was created by running:
ls weather_transform/models

You should see staging listed in the output.

  • Create a new file called schema.yml inside weather_transform/models/staging/ and paste this source definition:
version: 2

sources:
  - name: raw_weather
    schema: raw_weather
    tables:
      - name: weather_observations

What does this YAML do?

The sources block tells dbt where your raw data lives. name: raw_weather creates a reference name, schema: raw_weather points to the PostgreSQL schema, and tables lists the table within it.

This means your staging model can reference this table with {{ source('raw_weather', 'weather_observations') }} instead of hardcoding the full table path.

  • Create a new file called stg_weather_observations.sql in the same weather_transform/models/staging/ directory and paste this SQL:
with source as (
    select * from {{ source('raw_weather', 'weather_observations') }}
),

cleaned as (
    select
        city_name,
        latitude,
        longitude,
        cast(timestamp as timestamp) as observation_timestamp,
        cast(timestamp as date) as observation_date,
        temperature_2m as temperature_celsius,
        relative_humidity_2m as humidity_percent,
        wind_speed_10m as wind_speed_kmh,
        precipitation as precipitation_mm,
        city_name || '_' || timestamp as city_timestamp_id
    from source
)

select * from cleaned

What does this staging model do?

The model uses a CTE (Common Table Expression) pattern. The first CTE pulls everything from the raw source. The second CTE does the actual cleaning.

  • API names like temperature_2m become human-readable names like temperature_celsius.
  • The raw timestamp text is cast to proper timestamp and date types.
  • A city_timestamp_id concatenation creates a composite identifier for each row.
  • Save both files, then navigate into the dbt project directory by running:
cd weather_transform
  • Run dbt to create the staging view by running:
dbt run

You should see output that includes a line like OK created sql view model followed by [OK in X.XXs]. The summary line at the bottom should read PASS=1 with zero errors.

  • Verify the staging view contains clean data by querying it with this command:
docker exec -it weather_postgres psql -U pipeline_user -d weather_db -c "SELECT city_name, observation_timestamp, temperature_celsius, humidity_percent FROM weather_transform.stg_weather_observations LIMIT 5;"

You should see a table with five rows showing renamed, properly typed columns. The observation_timestamp column now shows real timestamp values instead of plain text strings.

Not seeing any rows?

  • Make sure the Docker container is still running. Run docker compose ps from the weather-pipeline folder to check.
  • Confirm you ran extract_load.py successfully in the previous step. Raw data must exist before dbt can transform it.
  • Check that schema.yml has schema: raw_weather (matching the schema your extraction script created).

Help me debug my dbt staging model

Add data quality tests and run them

Your staging model looks clean, but how do you know the data itself is trustworthy? In security, you would not push untested code to production. The same principle applies to data.

dbt's generic tests let you define assertions about your data right inside your YAML schema file. If any assertion fails, dbt tells you exactly which column has the problem.

  • Open weather_transform/models/staging/schema.yml and add the following models block below the existing sources section:
models:
  - name: stg_weather_observations
    columns:
      - name: city_name
        tests:
          - not_null
      - name: observation_timestamp
        tests:
          - not_null
      - name: temperature_celsius
        tests:
          - not_null
      - name: city_timestamp_id
        tests:
          - unique

What do these tests check?

  • not_null on city_name and observation_timestamp ensures every row has a city and a time. These are required fields.
  • not_null on temperature_celsius asserts that every row has a temperature reading.
  • unique on city_timestamp_id ensures no duplicate rows exist for the same city at the same time.
  • Save schema.yml.

✔️ Awesome, I've got everything!

Great. Double check you have saved schema.yml with both the sources and models sections.

ⓧ I'd like to double check the full code

Here is the complete schema.yml file. Make sure yours matches:

version: 2

sources:
  - name: raw_weather
    schema: raw_weather
    tables:
      - name: weather_observations

models:
  - name: stg_weather_observations
    columns:
      - name: city_name
        tests:
          - not_null
      - name: observation_timestamp
        tests:
          - not_null
      - name: temperature_celsius
        tests:
          - not_null
      - name: city_timestamp_id
        tests:
          - unique

Before you run the tests, take a moment to predict. You defined a not_null test on temperature_celsius. Do you think every single hourly weather reading from the Open-Meteo API actually has a temperature value?

  • Run the dbt tests by running:
dbt test
Observe the test failure

Look at the output. You should see some tests pass and one test fail. The failing test is not_null on temperature_celsius.

The summary line shows something like PASS=3 WARN=0 ERROR=1 with a failure message reading Got X results, configured to fail if != 0. Those X results are rows where temperature_celsius is null.

Why does this happen?

The Open-Meteo API returns forecast data for future hours that have not occurred yet. Some trailing forecast slots do not have all variables populated, so temperature_2m comes back as null for those rows. Your staging model faithfully passed those nulls through because it has no filter to exclude them.

This is exactly how data quality monitoring works. You define the assertion (no nulls in temperature), the test catches the violation, and now you have a decision to make about how to handle it. Just like triaging a security alert.

Why not fix it now?

In a real data pipeline, you would investigate the failure, understand the root cause, then apply a targeted fix. That is what the next step is for. Rushing to patch without understanding the problem is how bad data slips through.

All four tests passed?

If your API call happened to land during a window where all forecast slots were populated, you might see all tests pass. This is rare but possible. Check your raw data for nulls by running:

docker exec -it weather_postgres psql -U pipeline_user -d weather_db -c "SELECT COUNT(*) FROM raw_weather.weather_observations WHERE temperature_2m IS NULL;"

If the count is 0, you got lucky. The next step still applies because you should always guard against this scenario.

Help me understand my dbt test results

Your staging model is working and your tests just surfaced a real data quality issue. In the next step, you will fix the root cause in your staging model and then build analytics-ready mart models on top of the clean data.

Build Mart Models and Fix Data Quality

In the previous step, you built a staging model that cleans and renames your raw weather data. You also discovered a real problem: dbt tests revealed null temperature values hiding in the raw data from the Open-Meteo API.

The test failure proves your raw data has gaps. Just like triaging a security alert, you now need to fix the root cause in staging and then build reliable analytics on top. In this step, you will fix the data quality issue and then create mart models that give stakeholders aggregated, trustworthy data.

In this step, get ready to:
  • Fix the staging model to filter out null temperature values and pass all dbt tests.
  • Build a mart model that aggregates daily weather summaries with city-level temperature rankings.
  • Add automated tests to the mart and verify the full pipeline with dbt build.
Fix the staging model to filter null temperatures

Your not_null test on temperature_celsius failed because the Open-Meteo API returns null for some trailing forecast hours. The fix belongs in the staging layer, where you filter out incomplete rows before they reach anything downstream.

  • In stg_weather_observations.sql, scroll to the bottom of the cleaned CTE.
  • Find this line:
    from source
)
  • Add a WHERE clause between from source and the closing ), so the end of the CTE now looks like this:
    from source
    where temperature_2m is not null
)

Why filter on temperature_2m instead of temperature_celsius?

The WHERE clause runs against the raw source column temperature_2m, not the renamed alias temperature_celsius. Column aliases defined in the SELECT list are not available in the WHERE clause of the same query.

Before you run the tests, do you think all of them will pass now that you have added the filter?

  • Save stg_weather_observations.sql.
  • Verify the fix by running this command from the weather_transform directory:
dbt test

Every test now shows PASS. The summary line reads something like PASS=4 WARN=0 ERROR=0 SKIP=0 with zero errors.

Still seeing a test failure?

Make sure the WHERE clause references the raw column name temperature_2m, not the alias temperature_celsius. Also check that the line sits between from source and the closing ) of the cleaned CTE.

Help me fix my failing dbt not_null test on temperature_celsius

Build the daily weather summary mart

With staging clean, you can now build a mart model. Marts are the analytics-ready tables that stakeholders actually query. Your mart will aggregate hourly observations into daily summaries per city and rank cities by temperature each day.

  • Create a marts directory inside models/ by running this command:
mkdir models/marts
  • Create a new file called mart_daily_weather_summary.sql inside models/marts/ and paste this code:
{{ config(materialized='table') }}

with daily_aggregates as (
    select
        city_name,
        observation_date,
        round(avg(temperature_celsius)::numeric, 1) as avg_temperature,
        round(min(temperature_celsius)::numeric, 1) as min_temperature,
        round(max(temperature_celsius)::numeric, 1) as max_temperature,
        round(sum(precipitation_mm)::numeric, 2) as total_precipitation_mm,
        round(avg(humidity_percent)::numeric, 1) as avg_humidity_percent,
        round(avg(wind_speed_kmh)::numeric, 1) as avg_wind_speed_kmh,
        count(*) as observation_count
    from {{ ref('stg_weather_observations') }}
    group by city_name, observation_date
),

ranked as (
    select
        *,
        rank() over (
            partition by observation_date
            order by avg_temperature desc
        ) as temperature_rank
    from daily_aggregates
)

select * from ranked
order by observation_date, temperature_rank

What does this model do?

config(materialized='table') tells dbt to create a physical table instead of a view. This is ideal for mart models that stakeholders query repeatedly because the aggregation only runs once, not every time someone reads the data.

The first CTE (daily_aggregates) groups hourly rows by city and date, producing daily averages, min/max temperatures, total precipitation, and an observation count. The second CTE (ranked) applies a RANK() window function that ranks cities from hottest to coolest for each day.

  • Save mart_daily_weather_summary.sql.
  • Run both models (staging and the new mart) by running:
dbt run

You should see two lines with OK next to them. One for the staging view and one for the new mart table.

Seeing a compilation error?

Check that ref('stg_weather_observations') matches the exact filename of your staging model (without the .sql extension). Also make sure the file is inside models/marts/, not directly in models/.

Help me debug my dbt run error for the mart model

Awesome, I've got everything!

Great. Double check you have saved mart_daily_weather_summary.sql inside the models/marts/ directory.

I'd like to double check the full code

{{ config(materialized='table') }}

with daily_aggregates as (
    select
        city_name,
        observation_date,
        round(avg(temperature_celsius)::numeric, 1) as avg_temperature,
        round(min(temperature_celsius)::numeric, 1) as min_temperature,
        round(max(temperature_celsius)::numeric, 1) as max_temperature,
        round(sum(precipitation_mm)::numeric, 2) as total_precipitation_mm,
        round(avg(humidity_percent)::numeric, 1) as avg_humidity_percent,
        round(avg(wind_speed_kmh)::numeric, 1) as avg_wind_speed_kmh,
        count(*) as observation_count
    from {{ ref('stg_weather_observations') }}
    group by city_name, observation_date
),

ranked as (
    select
        *,
        rank() over (
            partition by observation_date
            order by avg_temperature desc
        ) as temperature_rank
    from daily_aggregates
)

select * from ranked
order by observation_date, temperature_rank
Add tests and verify the full pipeline

The mart model runs, but you need automated tests to catch issues before bad data reaches anyone downstream. You will add a schema file for the marts layer with tests on key columns.

  • Create a new file called schema.yml inside models/marts/ and paste this configuration:
version: 2

models:
  - name: mart_daily_weather_summary
    columns:
      - name: city_name
        tests:
          - not_null
          - accepted_values:
              values: ["London", "New York", "Tokyo", "Sydney", "Berlin"]
      - name: observation_date
        tests:
          - not_null
      - name: avg_temperature
        tests:
          - not_null
      - name: temperature_rank
        tests:
          - not_null

What do these tests check?

not_null ensures key columns are never empty. If your staging filter missed a null, these tests catch it before anyone queries the mart.

accepted_values on city_name confirms only your five target cities appear in the table. If a typo or unexpected city name creeps into the data, the test flags it immediately.

  • Save schema.yml.

Before you run the next command, think about how many models and tests dbt needs to process across your entire project. How many do you expect?

  • Run both models and all tests in dependency order by running:
dbt build

What does dbt build do?

dbt build runs models and tests together in dependency order. It creates the staging view first, runs staging tests, then creates the mart table, and finally runs the mart tests. If any test fails, dbt stops before building anything that depends on the failing model.

The summary line should read ERROR=0 with all items showing PASS or OK. You have two models and multiple tests all completing successfully.

Seeing an accepted_values failure?

Double check the city names in schema.yml match exactly what your extract_load.py script inserted. The values are case-sensitive. They must be London, New York, Tokyo, Sydney, and Berlin.

Help me debug my dbt build test failures

Now confirm the mart table contains the data you expect. Connect to PostgreSQL inside your Docker container by running:

  • Open a PostgreSQL shell by running:
docker exec -it weather_postgres psql -U pipeline_user -d weather_db
  • Query the mart table to see daily summaries with city rankings by running:
SELECT city_name, observation_date, avg_temperature, temperature_rank
FROM weather_transform.mart_daily_weather_summary
ORDER BY observation_date DESC, temperature_rank
LIMIT 10;

You should see rows with daily averages per city and a temperature_rank column where rank 1 is the hottest city for that day.

  • Exit the PostgreSQL shell by typing:
\q

Query returns zero rows?

Make sure you ran dbt build (not just dbt test) so the mart table was actually created. Also confirm the query references weather_transform.mart_daily_weather_summary with the correct schema prefix.

Help me figure out why my mart table query returns no rows

Awesome, I've got everything!

Great. Double check you have saved schema.yml inside the models/marts/ directory.

I'd like to double check the full code

version: 2

models:
  - name: mart_daily_weather_summary
    columns:
      - name: city_name
        tests:
          - not_null
          - accepted_values:
              values: ["London", "New York", "Tokyo", "Sydney", "Berlin"]
      - name: observation_date
        tests:
          - not_null
      - name: avg_temperature
        tests:
          - not_null
      - name: temperature_rank
        tests:
          - not_null

Your pipeline now extracts raw weather data, cleans it through a staging layer, filters out nulls, and aggregates it into a daily summary mart with city rankings. Next up, you will tackle the Secret Mission: building a weather alerts mart that flags extreme temperature swings and producing a visual comparison chart.

Secret mission

Build a Weather Alerts Mart and Analysis Report

Your pipeline summarizes weather data, but real data pipelines go further. They surface anomalies and trigger actions. In this secret mission, you will build a weather alerts mart that flags extreme temperature swings using LAG() and rolling average window functions, then create a Python analysis script that produces a multi-city temperature chart and prints alerts to the terminal.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • Docker container weather_postgres running PostgreSQL 16 on port 5432.
  • Docker volume pgdata storing your weather database data.
  • Project folder weather-pipeline containing Python scripts, dbt project, virtual environment, and generated chart.
  • dbt profile configuration at ~/.dbt/profiles.yml.
  • Docker Desktop application.

Keep everything running

No action needed. Choose this if you are still actively experimenting with your pipeline or want to add more cities and weather variables.

  • Your Docker container uses minimal system resources while running.
  • Docker Desktop remains installed on your machine.
  • Your dbt profile at ~/.dbt/profiles.yml stays available for future dbt projects.

Pause - I'll come back to this later

Shut down the running container to free up system resources while keeping your data and files intact.

  • In your terminal, navigate to the weather-pipeline folder and stop the container by running:
docker compose stop
  • Your database data is preserved in the pgdata volume.
  • When you are ready to resume, navigate back to the weather-pipeline folder and run:
docker compose start

Delete - I don't want to use this again

Remove all project resources and start fresh.

  • In your terminal, navigate to the weather-pipeline folder and stop the container and remove the database volume by running:
docker compose down -v
  • Move up one level so you can delete the project folder by running:
cd ..
  • Delete the weather-pipeline folder. On macOS or Linux, run:
rm -rf weather-pipeline
  • On Windows (PowerShell), run:
Remove-Item -Recurse -Force weather-pipeline
  • If you have no other dbt projects, delete the dbt profile at ~/.dbt/profiles.yml.
  • To uninstall Docker Desktop on Windows, open Settings, select Apps, then Apps & features. Find Docker Desktop and click Uninstall.

What about my local PostgreSQL?

This project used a Dockerized PostgreSQL container, not your locally installed PostgreSQL. Removing Docker does not affect your existing local PostgreSQL installation.

Nice Work!

Nice Work!

You just built a complete weather data pipeline from scratch. From pulling live data off an API to transforming it with dbt, catching data quality issues, and producing a visual chart, you now have a working ELT pipeline you can demo and talk through in interviews.

What you learned:

  • Built a complete ELT pipeline that extracts live weather data from the Open-Meteo API for five global cities and loads it into a Dockerized PostgreSQL database you can start and stop with a single command.
  • Created a dbt project with staging models that clean raw data and mart models that produce daily weather summaries using CTE aggregations and window function rankings.
  • Detected and fixed a real data quality issue using dbt tests, catching null temperature values before they reached your analytics layer.
  • Secret Mission: Built a weather alerts mart using LAG() and rolling average window functions to flag temperature anomalies, then produced a multi-city temperature comparison chart with pandas and matplotlib.

Ready to quiz yourself?