ELT Pipeline with DuckDB and dbt

Build a local ELT pipeline that extracts UK grid data into DuckDB.

Introduction

30 Second Summary

Every time you flip a light switch, electricity flows from a mix of sources. Gas, wind, solar, nuclear. Data engineers build pipelines that capture this kind of real-world data and reshape it so analysts can answer questions about it.

In this project, you will build an ELT pipeline that extracts live electricity generation data from the UK National Grid's public API, loads it into a DuckDB database, and transforms it into analytics-ready tables using dbt.

What You'll Build

A local ELT pipeline that pulls real-time electricity generation and carbon intensity data from the UK Carbon Intensity API, loads it into DuckDB, and uses dbt to transform the raw data into a clean star schema with fact and dimension tables.

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

  • A Python extraction script that pulls live data from a public API and loads it into DuckDB.
  • A dbt project with staging models, dimension tables, and a fact table arranged in a star schema.
  • Data quality tests that validate your pipeline's output using dbt's built-in testing framework.
  • A GitHub repository showcasing your end-to-end data engineering skills as a portfolio piece.
  • Secret Mission: Build a dbt macro that calculates renewable energy percentages and flags periods where renewables exceed 50% of total generation.

Are there any prerequisites?

You should be comfortable with basic Python (writing scripts, using pip) and basic SQL (SELECT, JOIN, GROUP BY). No prior experience with DuckDB or dbt is needed.

Set Up Your Environment

Before you can build a pipeline, you need the right tools installed and a clean workspace to develop in.

In this step, you will install Git, create a project folder, set up a Python virtual environment, and install the packages your pipeline needs.

In this step, get ready to:
  • Verify Git is installed on your machine.
  • Create a project folder with a Python virtual environment.
  • Install the required Python packages and initialize a Git repository.
Verify Git is installed

Git tracks every change you make to your code. You will use it throughout this project to commit your progress and eventually push to GitHub.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or cmd (Windows) and press Enter to open it.
  • Check if Git is installed by running this command:
git --version

You should see output like git version 2.54.0 (your version number may differ).

✔️ I see a version number

Git is ready to go. Move on to the next substep.

ⓧ Command not found

You need to install Git. Follow the instructions for your operating system below.

macOS

  • Install Git by running this command, which triggers the Xcode Command Line Tools installer:
xcode-select --install
  • A dialog will appear asking you to install the tools. Click Install and wait for it to finish.
  • After installation completes, verify by running git --version again.

Windows

  • Download the Git installer from https://git-scm.com/download/win.
  • Run the downloaded installer and accept the default settings on each screen.
  • After installation finishes, close and reopen your terminal.
  • Verify by running git --version again.
Create your project folder and virtual environment

A virtual environment isolates your project's packages from the rest of your system. This prevents version conflicts between different Python projects.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir elt-pipeline
cd elt-pipeline
  • Create a Python virtual environment inside this folder 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 the virtual environment is active.

Don't see (.venv) in your prompt?

On Windows, you may need to allow script execution first. Run Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned in PowerShell, then try the activate command again.

On macOS, make sure you typed source before the path. Without it, the command will not activate the environment.

Help me activate my Python virtual environment.

Install packages and initialize Git

Your pipeline needs four Python packages. Two handle the extraction and storage of data. Two handle the transformation layer.

  • Install all four packages by running this command:
pip install requests duckdb dbt-core dbt-duckdb

What are these packages?

  • `requests` lets Python call web APIs and download data.
  • `duckdb` is a fast, local analytical database. It acts as the storage layer of your pipeline.
  • `dbt-core` is the transformation framework that turns raw data into clean, tested models.
  • `dbt-duckdb` is the adapter that connects dbt to your DuckDB database.

The install may take a minute. When it finishes, you should see a line that says Successfully installed followed by a list of package names.

Seeing an error during pip install?

Make sure you see (.venv) in your terminal prompt. If it is missing, your virtual environment is not active. Go back and run the activate command from the previous substep.

If you see a permissions error, do not use sudo. Instead, confirm you are inside the elt-pipeline folder and that the venv is active.

Help me fix my pip install error.

  • Initialize a Git repository in your project folder by running:
git init

You should see a message that says Initialized empty Git repository followed by the path to your folder.

Why initialize Git now?

Starting version control at the beginning of a project means every change you make is trackable. In data engineering, this is essential for auditing pipeline changes and collaborating with teammates.

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

In the Explorer sidebar on the left, you should see the .venv folder listed. This confirms VS Code is open to the correct project directory.

Your environment is ready. Next up, you will write a Python script that calls a real API and loads electricity data into DuckDB.

Extract and Load Data

In Step 1, you set up your project environment with all the packages you need. Now it's time to write the core of your pipeline: the code that pulls real electricity data from an external source and stores it locally.

The UK National Grid publishes a free Carbon Intensity API that reports how much carbon each unit of electricity produces, and which fuel types (gas, wind, solar, nuclear, etc.) are generating power at any given moment. Your Python script will call this API, parse the responses, and load everything into DuckDB tables ready for transformation.

In this step, get ready to:
  • Write a Python script that extracts data from the Carbon Intensity API.
  • Flatten nested JSON responses into tabular format and load them into DuckDB.
  • Run the script and verify data landed correctly with SQL queries.
Call the Carbon Intensity API

The first part of any ELT pipeline is extraction. You need functions that call the API and return raw data your script can work with.

  • In VS Code, create a new file called extract_load.py in your project root folder (the same folder that contains .venv/).
  • Add the imports, API base URL constant, and both extraction functions by pasting this code:
import requests
import duckdb
from datetime import datetime, timedelta


API_BASE_URL = "https://api.carbonintensity.org.uk"


def extract_intensity_data(date_from: str, date_to: str) -> list[dict]:
    """Extract carbon intensity data for a date range."""
    url = f"{API_BASE_URL}/intensity/{date_from}/{date_to}"
    headers = {"Accept": "application/json"}

    response = requests.get(url, headers=headers)
    response.raise_for_status()

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


def extract_generation_mix(date_from: str, date_to: str) -> list[dict]:
    """Extract generation mix data for a date range."""
    url = f"{API_BASE_URL}/generation/{date_from}/{date_to}"
    headers = {"Accept": "application/json"}

    response = requests.get(url, headers=headers)
    response.raise_for_status()

    data = response.json()["data"]
    print(f"Extracted {len(data)} generation mix records")
    return data

What does this code do?

API_BASE_URL stores the base address of the Carbon Intensity API so you don't repeat it in every function.

extract_intensity_data calls the /intensity/{from}/{to} endpoint, which returns half-hourly carbon intensity forecasts and actuals for a date range. extract_generation_mix calls /generation/{from}/{to}, which returns the percentage of electricity generated by each fuel type (gas, wind, solar, etc.) per half-hour period.

Both functions use raise_for_status() to immediately throw an error if the API returns a non-200 response, so you catch problems early.

Why no API key?

The UK Carbon Intensity API is completely free and requires no authentication. It is maintained by National Grid ESO and licensed under CC BY 4.0.

You cannot run this file yet because it has no main() function. The next two chunks add the remaining pieces.

Flatten the responses and load into DuckDB

The generation mix endpoint returns nested JSON. Each half-hour period contains a list of 9 fuel types with their percentages. Before loading into a database table, you need to flatten that nested structure into individual rows.

  • Below the extract_generation_mix function, add the flattening function:
def flatten_generation_mix(data: list[dict]) -> list[dict]:
    """Flatten nested generation mix data into rows."""
    rows = []
    for record in data:
        period_from = record["from"]
        period_to = record["to"]
        for fuel in record["generationmix"]:
            rows.append({
                "period_from": period_from,
                "period_to": period_to,
                "fuel_type": fuel["fuel"],
                "percentage": fuel["perc"],
            })
    return rows

What does this code do?

The API returns each period as one object containing a generationmix array of 9 fuel-type entries. This function loops through each period and each fuel type within it, producing one flat row per fuel type per period.

For example, one API period becomes 9 rows in your table (one for gas, one for wind, one for solar, and so on).

You won't see a change yet. The next chunk adds the function that actually writes data to DuckDB.

  • Below flatten_generation_mix, add the loading function:
def load_to_duckdb(
    intensity_data: list[dict],
    generation_rows: list[dict],
    db_path: str = "grid_analytics.duckdb",
):
    """Load extracted data into DuckDB tables."""
    con = duckdb.connect(db_path)

    con.execute("DROP TABLE IF EXISTS raw_intensity")
    con.execute("""
        CREATE TABLE raw_intensity (
            period_from VARCHAR,
            period_to VARCHAR,
            forecast INTEGER,
            actual INTEGER,
            intensity_index VARCHAR
        )
    """)

    for record in intensity_data:
        con.execute(
            "INSERT INTO raw_intensity VALUES (?, ?, ?, ?, ?)",
            [
                record["from"],
                record["to"],
                record["intensity"]["forecast"],
                record["intensity"].get("actual"),
                record["intensity"]["index"],
            ],
        )

    con.execute("DROP TABLE IF EXISTS raw_generation_mix")
    con.execute("""
        CREATE TABLE raw_generation_mix (
            period_from VARCHAR,
            period_to VARCHAR,
            fuel_type VARCHAR,
            percentage DOUBLE
        )
    """)

    for row in generation_rows:
        con.execute(
            "INSERT INTO raw_generation_mix VALUES (?, ?, ?, ?)",
            [row["period_from"], row["period_to"], row["fuel_type"], row["percentage"]],
        )

    intensity_count = con.execute("SELECT COUNT(*) FROM raw_intensity").fetchone()[0]
    gen_count = con.execute("SELECT COUNT(*) FROM raw_generation_mix").fetchone()[0]
    print(f"Loaded {intensity_count} rows into raw_intensity")
    print(f"Loaded {gen_count} rows into raw_generation_mix")

    con.close()

What does this code do?

This function connects to a local DuckDB database file called grid_analytics.duckdb. DuckDB creates this file automatically if it does not exist yet.

It creates two tables: raw_intensity (with columns for forecast, actual, and intensity index) and raw_generation_mix (with columns for fuel type and percentage). The DROP TABLE IF EXISTS ensures you start fresh each time you run the script.

After inserting all rows, it prints the row counts so you can confirm the load worked.

You still cannot run the file. The final piece is the main() function that ties everything together.

  • At the bottom of extract_load.py, add the orchestration logic:
def main():
    now = datetime.utcnow()
    date_from = (now - timedelta(days=2)).strftime("%Y-%m-%dT%H:%MZ")
    date_to = now.strftime("%Y-%m-%dT%H:%MZ")

    print(f"Extracting data from {date_from} to {date_to}")

    intensity_data = extract_intensity_data(date_from, date_to)
    generation_data = extract_generation_mix(date_from, date_to)

    generation_rows = flatten_generation_mix(generation_data)

    load_to_duckdb(intensity_data, generation_rows)
    print("Extract and Load complete!")


if __name__ == "__main__":
    main()

What does this code do?

main() calculates a 2-day date range ending at the current time in ISO 8601 format (e.g. 2026-05-11T14:30Z). It then calls each function in order: extract intensity, extract generation mix, flatten, and load.

The if __name__ == "__main__" guard means main() only runs when you execute the file directly, not when you import it from another script.

  • Save extract_load.py.
Run the script and verify your data

Your extraction script is complete. Time to run it and confirm that real data lands in your DuckDB database.

  • Make sure your virtual environment is still active (you should see (.venv) in your terminal prompt).
  • Run the extraction script:
python extract_load.py

What should I see?

Your terminal should print output similar to this:

Extracting data from 2026-05-11T14:30Z to 2026-05-13T14:30Z
Extracted 96 intensity records
Extracted 96 generation mix records
Loaded 96 rows into raw_intensity
Loaded 864 rows into raw_generation_mix
Extract and Load complete!

Your exact numbers will differ depending on when you run the script. Two days of data produces roughly 96 half-hour periods for intensity and around 864 generation mix rows (96 periods multiplied by 9 fuel types).

Seeing a connection error?

If you see a ConnectionError or timeout, check that you have an active internet connection. The Carbon Intensity API is a public endpoint that requires no authentication.

If you see ModuleNotFoundError: No module named 'requests', your virtual environment is not active. Run source .venv/bin/activate (macOS/Linux) or .venv\Scripts\activate (Windows) and try again.

help me debug my extract_load.py script

Now verify the data by querying DuckDB directly.

  • Open a Python interactive shell by running:
python
  • Run these queries to check both tables:
import duckdb
con = duckdb.connect('grid_analytics.duckdb')
print(con.execute('SELECT COUNT(*) FROM raw_intensity').fetchone())
print(con.execute('SELECT COUNT(*) FROM raw_generation_mix').fetchone())
print(con.execute('SELECT * FROM raw_intensity LIMIT 3').fetchdf())
print(con.execute('SELECT * FROM raw_generation_mix LIMIT 3').fetchdf())
con.close()
exit()

What should I see?

The first two print statements show tuple counts like (96,) and (864,). The LIMIT 3 queries display a small table preview showing real timestamps, intensity values, and fuel type percentages from the UK grid.

Seeing 'Table does not exist'?

Make sure you are running the python command from the same folder where extract_load.py lives. The grid_analytics.duckdb file is created in whatever directory you ran the script from.

help me find my DuckDB database file

✔️ Awesome, I've got everything!

Great. Make sure you have saved extract_load.py and that grid_analytics.duckdb exists in your project root.

ⓧ I'd like to double check the full code

Here is the complete extract_load.py file. Compare it against yours and fix any differences.

import requests
import duckdb
from datetime import datetime, timedelta


API_BASE_URL = "https://api.carbonintensity.org.uk"


def extract_intensity_data(date_from: str, date_to: str) -> list[dict]:
    """Extract carbon intensity data for a date range."""
    url = f"{API_BASE_URL}/intensity/{date_from}/{date_to}"
    headers = {"Accept": "application/json"}

    response = requests.get(url, headers=headers)
    response.raise_for_status()

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


def extract_generation_mix(date_from: str, date_to: str) -> list[dict]:
    """Extract generation mix data for a date range."""
    url = f"{API_BASE_URL}/generation/{date_from}/{date_to}"
    headers = {"Accept": "application/json"}

    response = requests.get(url, headers=headers)
    response.raise_for_status()

    data = response.json()["data"]
    print(f"Extracted {len(data)} generation mix records")
    return data


def flatten_generation_mix(data: list[dict]) -> list[dict]:
    """Flatten nested generation mix data into rows."""
    rows = []
    for record in data:
        period_from = record["from"]
        period_to = record["to"]
        for fuel in record["generationmix"]:
            rows.append({
                "period_from": period_from,
                "period_to": period_to,
                "fuel_type": fuel["fuel"],
                "percentage": fuel["perc"],
            })
    return rows


def load_to_duckdb(
    intensity_data: list[dict],
    generation_rows: list[dict],
    db_path: str = "grid_analytics.duckdb",
):
    """Load extracted data into DuckDB tables."""
    con = duckdb.connect(db_path)

    con.execute("DROP TABLE IF EXISTS raw_intensity")
    con.execute("""
        CREATE TABLE raw_intensity (
            period_from VARCHAR,
            period_to VARCHAR,
            forecast INTEGER,
            actual INTEGER,
            intensity_index VARCHAR
        )
    """)

    for record in intensity_data:
        con.execute(
            "INSERT INTO raw_intensity VALUES (?, ?, ?, ?, ?)",
            [
                record["from"],
                record["to"],
                record["intensity"]["forecast"],
                record["intensity"].get("actual"),
                record["intensity"]["index"],
            ],
        )

    con.execute("DROP TABLE IF EXISTS raw_generation_mix")
    con.execute("""
        CREATE TABLE raw_generation_mix (
            period_from VARCHAR,
            period_to VARCHAR,
            fuel_type VARCHAR,
            percentage DOUBLE
        )
    """)

    for row in generation_rows:
        con.execute(
            "INSERT INTO raw_generation_mix VALUES (?, ?, ?, ?)",
            [row["period_from"], row["period_to"], row["fuel_type"], row["percentage"]],
        )

    intensity_count = con.execute("SELECT COUNT(*) FROM raw_intensity").fetchone()[0]
    gen_count = con.execute("SELECT COUNT(*) FROM raw_generation_mix").fetchone()[0]
    print(f"Loaded {intensity_count} rows into raw_intensity")
    print(f"Loaded {gen_count} rows into raw_generation_mix")

    con.close()


def main():
    now = datetime.utcnow()
    date_from = (now - timedelta(days=2)).strftime("%Y-%m-%dT%H:%MZ")
    date_to = now.strftime("%Y-%m-%dT%H:%MZ")

    print(f"Extracting data from {date_from} to {date_to}")

    intensity_data = extract_intensity_data(date_from, date_to)
    generation_data = extract_generation_mix(date_from, date_to)

    generation_rows = flatten_generation_mix(generation_data)

    load_to_duckdb(intensity_data, generation_rows)
    print("Extract and Load complete!")


if __name__ == "__main__":
    main()

Your raw data is now sitting in DuckDB, ready to be transformed. Next up, you will initialize a dbt project and connect it to this database so you can build clean, tested data models on top of the raw tables.

Initialize and Configure Your dbt Project

You have raw data sitting in DuckDB. The next challenge is turning that raw data into clean, well-structured tables that analysts can query confidently.

This is where dbt (data build tool) comes in. dbt lets you write SQL transformations that are version-controlled, tested, and documented. In this step, you will scaffold a dbt project, connect it to your DuckDB database, and tell dbt where your raw tables live.

In this step, get ready to:
  • Scaffold a dbt project and configure its connection profile.
  • Define your raw tables as dbt sources.
  • Confirm dbt can connect to DuckDB with dbt debug.
Scaffold a new dbt project with dbt init

The dbt init command creates a project folder with all the directories and configuration files dbt needs to run.

  • In your terminal, make sure you are in your project root folder (where extract_load.py lives) and your virtual environment is activated.
  • Run this command to scaffold your dbt project:
dbt init grid_pipeline

What will dbt init ask you?

dbt init is interactive. It will ask you which database adapter you want to use. Select duckdb from the list (since you installed dbt-duckdb, it will appear as an option).

It may also ask for connection details like the database path. You can accept defaults or type any value for now. You will overwrite the generated profiles.yml with the correct settings in the next few steps.

After the command finishes, you should see a new grid_pipeline/ folder in your project root.

  • Confirm the folder was created by running:
ls grid_pipeline

You should see files including dbt_project.yml and folders like models/ and macros/.

Don't see the grid_pipeline folder?

Make sure you ran the command from your project root folder, not from inside a subfolder. Also confirm your virtual environment is active (you should see (.venv) or similar in your terminal prompt).

help me troubleshoot why dbt init did not create my grid_pipeline folder

Configure profiles.yml and dbt_project.yml

dbt needs two configuration files to work. The profiles.yml file tells dbt how to connect to your database. The dbt_project.yml file defines project-level settings like where models live and how they should be materialized.

  • Move into the grid_pipeline folder by running:
cd grid_pipeline
  • Create a profiles.yml file inside the grid_pipeline/ folder. Open this file in VS Code and paste the following configuration:
grid_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: '../grid_analytics.duckdb'
      threads: 4

What does this configuration do?

  • grid_pipeline: is the profile name. It must match the profile value in dbt_project.yml.
  • type: duckdb tells dbt to use the DuckDB adapter.
  • path: '../grid_analytics.duckdb' points to your database file one directory up. The path is relative to where you run dbt commands (inside grid_pipeline/).
  • threads: 4 controls how many models dbt builds in parallel.
  • Save profiles.yml.

Now update dbt_project.yml to configure how your models are materialized.

  • Open dbt_project.yml in VS Code.
  • Replace its entire contents with the following:
name: 'grid_pipeline'
version: '1.0.0'

profile: 'grid_pipeline'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:
  - "target"
  - "dbt_packages"

models:
  grid_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table

Why views for staging and tables for marts?

Staging models are lightweight cleaning layers. Materializing them as views means they don't take up extra storage. They just run the SQL on the fly when queried.

Mart models (dimensions and facts) are queried frequently by analysts. Materializing them as tables pre-computes the results for faster reads.

  • Save dbt_project.yml.
  • Create the model directories your project needs by running these commands from inside grid_pipeline/:
mkdir -p models/staging
mkdir -p models/marts
  • Confirm the directories exist by running:
ls models

You should see staging and marts listed.

Seeing a 'No such file or directory' error?

Make sure you are inside the grid_pipeline/ folder when running these commands. Run pwd to check your current location.

help me figure out why mkdir is failing when I try to create my dbt model directories

Define your raw tables as dbt sources

dbt needs to know about tables that exist outside its own models. You define these as sources in a YAML file so dbt can reference them with the {{ source() }} function.

  • Create a new file called schema.yml inside models/staging/.
  • Open schema.yml in VS Code and paste the following:
version: 2

sources:
  - name: raw
    schema: main
    tables:
      - name: raw_intensity
        description: Raw carbon intensity data from UK Carbon Intensity API
      - name: raw_generation_mix
        description: Raw generation mix data from UK Carbon Intensity API

models:
  - name: stg_intensity
    description: Cleaned carbon intensity data with proper types and derived fields
    columns:
      - name: period_start
        description: Start of the half-hour settlement period (UTC)
        tests:
          - not_null
      - name: forecast_intensity
        description: Forecasted carbon intensity in gCO2/kWh
        tests:
          - not_null
      - name: intensity_index
        description: Qualitative intensity band
        tests:
          - accepted_values:
              values: ['very low', 'low', 'moderate', 'high', 'very high']

  - name: stg_generation_mix
    description: Cleaned generation mix with one row per fuel type per period
    columns:
      - name: period_start
        description: Start of the half-hour settlement period (UTC)
        tests:
          - not_null
      - name: fuel_type
        description: Type of fuel used for generation
        tests:
          - not_null
          - accepted_values:
              values: ['gas', 'coal', 'nuclear', 'biomass', 'hydro', 'imports', 'solar', 'wind', 'other']
      - name: generation_percentage
        description: Percentage of total generation from this fuel type
        tests:
          - not_null

What does this file do?

The sources section registers raw_intensity and raw_generation_mix as tables dbt knows about. The schema: main tells dbt these tables live in DuckDB's default schema.

The models section pre-defines the staging models you will build in the next step. It also declares data quality tests (not_null, accepted_values) that dbt will run to validate your transformed data.

  • Save schema.yml.

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files before moving on.

ⓧ I'd like to double check the full code

Here is profiles.yml:

grid_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: '../grid_analytics.duckdb'
      threads: 4

Here is dbt_project.yml:

name: 'grid_pipeline'
version: '1.0.0'

profile: 'grid_pipeline'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:
  - "target"
  - "dbt_packages"

models:
  grid_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table

Here is models/staging/schema.yml:

version: 2

sources:
  - name: raw
    schema: main
    tables:
      - name: raw_intensity
        description: Raw carbon intensity data from UK Carbon Intensity API
      - name: raw_generation_mix
        description: Raw generation mix data from UK Carbon Intensity API

models:
  - name: stg_intensity
    description: Cleaned carbon intensity data with proper types and derived fields
    columns:
      - name: period_start
        description: Start of the half-hour settlement period (UTC)
        tests:
          - not_null
      - name: forecast_intensity
        description: Forecasted carbon intensity in gCO2/kWh
        tests:
          - not_null
      - name: intensity_index
        description: Qualitative intensity band
        tests:
          - accepted_values:
              values: ['very low', 'low', 'moderate', 'high', 'very high']

  - name: stg_generation_mix
    description: Cleaned generation mix with one row per fuel type per period
    columns:
      - name: period_start
        description: Start of the half-hour settlement period (UTC)
        tests:
          - not_null
      - name: fuel_type
        description: Type of fuel used for generation
        tests:
          - not_null
          - accepted_values:
              values: ['gas', 'coal', 'nuclear', 'biomass', 'hydro', 'imports', 'solar', 'wind', 'other']
      - name: generation_percentage
        description: Percentage of total generation from this fuel type
        tests:
          - not_null
Confirm dbt can connect to DuckDB

Before building any models, verify that dbt can find your profile and successfully connect to the database.

  • Make sure you are inside the grid_pipeline/ folder.
  • Run the following command to test your connection:
dbt debug

What does dbt debug do?

dbt debug checks that dbt can find your dbt_project.yml, load your profile, and open a connection to the database. If anything is misconfigured, it reports exactly which check failed.

You should see output ending with All checks passed! confirming that dbt connected to your DuckDB database.

Tell dbt where to find profiles.yml

By default, dbt looks for profiles.yml in ~/.dbt/. Since your file is inside the grid_pipeline/ folder, you may need to tell dbt where to find it by adding the --profiles-dir flag:

dbt debug --profiles-dir .

Seeing 'Could not find profile' or connection errors?

  • Check that the profile value in dbt_project.yml matches the top-level key in profiles.yml (both should be grid_pipeline).
  • Verify that grid_analytics.duckdb exists one folder up from where you are running dbt. Run ls ../*.duckdb to confirm.
  • Make sure your virtual environment is still active (dbt-duckdb must be installed in it).

help me debug why dbt debug is failing to connect to my DuckDB database

Your dbt project is scaffolded and connected. Next up, you will write the actual SQL models that transform your raw data into a clean star schema.

Build Staging and Mart Models

Your dbt project is wired up to your DuckDB database and knows where your raw tables live.

Now it is time to write the SQL models that transform that raw data into something analysts can actually query. You will build four models: two staging models that clean and type-cast the raw data, a dimension table that classifies fuel types, and a fact table that joins everything together into a star schema.

In this step, get ready to:
  • Create staging models that clean and type-cast the raw data.
  • Create a dimension table and a fact table that implement a star schema design.
  • Run dbt run to build all models and verify them with SQL queries.
Create staging models that clean raw data

Staging models are the first transformation layer in a dbt project. They sit between raw source tables and your analytics-ready models, handling type-casting, renaming, and basic filtering so that downstream models always work with clean, consistent data.

Why do we need staging models?

Your raw tables store everything as VARCHAR because that is how the API data was loaded. Staging models cast those strings to proper timestamps, filter out null rows, and give columns clear names.

If you skip this layer, every downstream model has to repeat the same casting and cleaning logic. Staging models do it once so the rest of your pipeline stays simple.

  • In VS Code, open the grid_pipeline/models/staging/ folder in the Explorer sidebar.
  • Create a new file called stg_intensity.sql and paste in the following code:
with source as (
    select * from {{ source('raw', 'raw_intensity') }}
),

cleaned as (
    select
        cast(period_from as timestamp) as period_start,
        cast(period_to as timestamp) as period_end,
        forecast as forecast_intensity,
        actual as actual_intensity,
        intensity_index,
        cast(period_from as date) as intensity_date,
        extract(hour from cast(period_from as timestamp)) as hour_of_day
    from source
    where period_from is not null
)

select * from cleaned

What does this model do?

  • source('raw', 'raw_intensity') pulls from the raw table you defined in schema.yml last step.
  • cast(period_from as timestamp) converts the raw VARCHAR strings into proper timestamps that DuckDB can do date math on.
  • The model derives two useful fields: intensity_date for grouping by day and hour_of_day for time-of-day analysis.
  • The where period_from is not null filter drops any incomplete records from the API response.
  • Save stg_intensity.sql.
  • Create another new file in the same staging/ folder called stg_generation_mix.sql and paste in the following code:
with source as (
    select * from {{ source('raw', 'raw_generation_mix') }}
),

cleaned as (
    select
        cast(period_from as timestamp) as period_start,
        cast(period_to as timestamp) as period_end,
        lower(trim(fuel_type)) as fuel_type,
        round(percentage, 2) as generation_percentage,
        cast(period_from as date) as generation_date
    from source
    where period_from is not null
      and fuel_type is not null
)

select * from cleaned

What does this model do?

  • lower(trim(fuel_type)) normalizes fuel type strings so that 'Gas', 'gas', and ' gas ' all become gas.
  • round(percentage, 2) standardizes the decimal precision so percentages display consistently.
  • The model filters out rows where fuel_type is null, since a generation record without a fuel type is meaningless.
  • Save stg_generation_mix.sql.

You will not see any output from these models yet. The dbt run command at the end of this step compiles and executes all models at once.

✔️ Awesome, I've got everything!

Great. Make sure both files are saved and move on to the mart models.

ⓧ I'd like to double check the full code

Here is stg_intensity.sql:

with source as (
    select * from {{ source('raw', 'raw_intensity') }}
),

cleaned as (
    select
        cast(period_from as timestamp) as period_start,
        cast(period_to as timestamp) as period_end,
        forecast as forecast_intensity,
        actual as actual_intensity,
        intensity_index,
        cast(period_from as date) as intensity_date,
        extract(hour from cast(period_from as timestamp)) as hour_of_day
    from source
    where period_from is not null
)

select * from cleaned

Here is stg_generation_mix.sql:

with source as (
    select * from {{ source('raw', 'raw_generation_mix') }}
),

cleaned as (
    select
        cast(period_from as timestamp) as period_start,
        cast(period_to as timestamp) as period_end,
        lower(trim(fuel_type)) as fuel_type,
        round(percentage, 2) as generation_percentage,
        cast(period_from as date) as generation_date
    from source
    where period_from is not null
      and fuel_type is not null
)

select * from cleaned
Create dimension and fact tables for the star schema

Staging models give you clean data. The next layer organizes that data into a structure optimized for analytics queries. You will use a star schema: a central fact table (measurements that happened) surrounded by dimension tables (the context around those measurements).

What is a star schema?

A star schema separates data into two types of tables. Fact tables store events or measurements (like electricity generation percentages for each half-hour period). Dimension tables store descriptive attributes about those events (like what category a fuel type belongs to).

This separation means analysts can filter and group by dimensions without duplicating data. It is the most common modeling pattern in analytics warehouses.

  • In VS Code, open the grid_pipeline/models/marts/ folder in the Explorer sidebar.
  • Create a new file called dim_fuel_type.sql and paste in the following code:
with fuel_types as (
    select distinct fuel_type
    from {{ ref('stg_generation_mix') }}
),

enriched as (
    select
        fuel_type,
        case
            when fuel_type in ('wind', 'solar', 'hydro', 'nuclear', 'biomass')
                then true
            else false
        end as is_low_carbon,
        case
            when fuel_type in ('wind', 'solar', 'hydro')
                then 'renewable'
            when fuel_type in ('nuclear', 'biomass')
                then 'low_carbon'
            when fuel_type in ('gas', 'coal')
                then 'fossil'
            else 'other'
        end as fuel_category
    from fuel_types
)

select * from enriched

What does this dimension table do?

  • ref('stg_generation_mix') tells dbt this model depends on the staging model you just created. dbt uses these references to determine build order.
  • is_low_carbon flags fuel types that produce little or no carbon emissions (wind, solar, hydro, nuclear, biomass).
  • fuel_category groups fuels into broader categories: renewable, low_carbon, fossil, or other. Analysts can use this to quickly filter queries.
  • Save dim_fuel_type.sql.
  • Create another new file in the marts/ folder called fct_generation.sql and paste in the following code:
with generation as (
    select
        period_start,
        period_end,
        generation_date,
        fuel_type,
        generation_percentage
    from {{ ref('stg_generation_mix') }}
),

intensity as (
    select
        period_start,
        forecast_intensity,
        actual_intensity,
        intensity_index,
        hour_of_day
    from {{ ref('stg_intensity') }}
),

joined as (
    select
        g.period_start,
        g.period_end,
        g.generation_date,
        g.fuel_type,
        g.generation_percentage,
        i.forecast_intensity,
        i.actual_intensity,
        i.intensity_index,
        i.hour_of_day
    from generation g
    left join intensity i
        on g.period_start = i.period_start
)

select * from joined

What does this fact table do?

  • This model joins the generation mix data with the carbon intensity data on period_start. Each row tells you: for this half-hour period, this fuel type produced this percentage of electricity, and the overall carbon intensity was this value.
  • The left join ensures you keep all generation rows even if the intensity API returned incomplete data for some periods.
  • The final result is a single table that an analyst can query to answer questions like "Which fuel type generated the most electricity when carbon intensity was high?"
  • Save fct_generation.sql.
  • Create one more file in the marts/ folder called schema.yml and paste in the following code:
version: 2

models:
  - name: dim_fuel_type
    description: Dimension table for fuel types with low-carbon classification
    columns:
      - name: fuel_type
        description: Primary key - name of fuel type
        tests:
          - unique
          - not_null

  - name: fct_generation
    description: Fact table combining generation mix with carbon intensity per period
    columns:
      - name: period_start
        description: Start of half-hour settlement period
        tests:
          - not_null
      - name: fuel_type
        description: Foreign key to dim_fuel_type
        tests:
          - not_null
          - relationships:
              to: ref('dim_fuel_type')
              field: fuel_type

What does this schema file do?

  • The unique and not_null tests on dim_fuel_type.fuel_type ensure this column works as a reliable primary key.
  • The relationships test on fct_generation.fuel_type checks referential integrity. Every fuel type in the fact table must exist in the dimension table.
  • Save schema.yml.

✔️ Awesome, I've got everything!

Great. Make sure all three mart files are saved before running dbt.

ⓧ I'd like to double check the full code

Here is dim_fuel_type.sql:

with fuel_types as (
    select distinct fuel_type
    from {{ ref('stg_generation_mix') }}
),

enriched as (
    select
        fuel_type,
        case
            when fuel_type in ('wind', 'solar', 'hydro', 'nuclear', 'biomass')
                then true
            else false
        end as is_low_carbon,
        case
            when fuel_type in ('wind', 'solar', 'hydro')
                then 'renewable'
            when fuel_type in ('nuclear', 'biomass')
                then 'low_carbon'
            when fuel_type in ('gas', 'coal')
                then 'fossil'
            else 'other'
        end as fuel_category
    from fuel_types
)

select * from enriched

Here is fct_generation.sql:

with generation as (
    select
        period_start,
        period_end,
        generation_date,
        fuel_type,
        generation_percentage
    from {{ ref('stg_generation_mix') }}
),

intensity as (
    select
        period_start,
        forecast_intensity,
        actual_intensity,
        intensity_index,
        hour_of_day
    from {{ ref('stg_intensity') }}
),

joined as (
    select
        g.period_start,
        g.period_end,
        g.generation_date,
        g.fuel_type,
        g.generation_percentage,
        i.forecast_intensity,
        i.actual_intensity,
        i.intensity_index,
        i.hour_of_day
    from generation g
    left join intensity i
        on g.period_start = i.period_start
)

select * from joined

Here is schema.yml:

version: 2

models:
  - name: dim_fuel_type
    description: Dimension table for fuel types with low-carbon classification
    columns:
      - name: fuel_type
        description: Primary key - name of fuel type
        tests:
          - unique
          - not_null

  - name: fct_generation
    description: Fact table combining generation mix with carbon intensity per period
    columns:
      - name: period_start
        description: Start of half-hour settlement period
        tests:
          - not_null
      - name: fuel_type
        description: Foreign key to dim_fuel_type
        tests:
          - not_null
          - relationships:
              to: ref('dim_fuel_type')
              field: fuel_type
Run dbt to build all models and verify the results

All four model files are in place. Time to compile and execute them against your DuckDB database.

  • Make sure your terminal is inside the grid_pipeline/ directory (where dbt_project.yml lives).
  • Build all models by running:
dbt run

You should see output showing each model being built. The staging models appear as CREATE VIEW (since they are materialized as views) and the mart models appear as CREATE TABLE (since they are materialized as tables). All 4 models should show a success status.

Seeing a compilation error?

  • Check that your staging SQL files are in grid_pipeline/models/staging/ and your mart SQL files are in grid_pipeline/models/marts/. dbt only looks in the directories defined in model-paths.
  • If you see a "source not found" error, confirm that schema.yml in the staging folder still has the sources: section defining raw_intensity and raw_generation_mix.
  • If you see a "relation does not exist" error for the raw tables, re-run python extract_load.py from the project root to reload the data.

Help me debug my dbt run error

Now verify the data landed correctly by querying DuckDB directly. Run this from your project root directory (one level above grid_pipeline/):

  • Move back to the project root by running:
cd ..
  • Query the dimension table to see your fuel type classifications by running:
python -c "import duckdb; con = duckdb.connect('grid_analytics.duckdb'); print(con.execute('SELECT * FROM dim_fuel_type').fetchdf())"

You should see a table with 9 fuel types, each with an is_low_carbon flag and a fuel_category value.

  • Query the fact table to see joined generation and intensity data by running:
python -c "import duckdb; con = duckdb.connect('grid_analytics.duckdb'); print(con.execute('SELECT * FROM fct_generation LIMIT 5').fetchdf())"

You should see rows containing period_start, fuel_type, generation_percentage, and forecast_intensity columns all populated with real values.

Why materialized as views vs tables?

Your dbt_project.yml sets staging models to +materialized: view and marts to +materialized: table. Views are lightweight references that always reflect the latest raw data. Tables are physical copies that query faster but need rebuilding when source data changes.

This is a common pattern: staging as views (cheap, always current), marts as tables (fast for analysts to query).

Your data models are built and queryable. Next up, you will add automated data quality tests that catch problems before they reach analysts.

Add Data Quality Tests and Documentation

Your models are built and your star schema is in place. But how do you know the data flowing through your pipeline is actually correct?

In production, bad data slips through silently and corrupts downstream dashboards. dbt solves this with built-in data quality tests that run against your models and catch problems before they reach analysts. In this step, you'll run the tests already defined in your schema files, then generate browsable documentation for your entire project.

In this step, get ready to:
  • Run dbt tests to validate your data quality.
  • Generate and browse dbt documentation.
  • View your model lineage graph.
Review your test definitions

When you created your schema.yml files in the previous step, you already defined data quality tests alongside your model descriptions. These tests live as YAML declarations and dbt knows how to compile them into SQL queries that check your data.

  • Open grid_pipeline/models/staging/schema.yml in VS Code.
  • Notice the tests key underneath each column definition.

Here's what the staging schema defines for the stg_intensity model:

      - name: period_start
        description: Start of the half-hour settlement period (UTC)
        tests:
          - not_null
      - name: forecast_intensity
        description: Forecasted carbon intensity in gCO2/kWh
        tests:
          - not_null
      - name: intensity_index
        description: Qualitative intensity band
        tests:
          - accepted_values:
              values: ['very low', 'low', 'moderate', 'high', 'very high']

What do these test types mean?

  • not_null checks that a column has no missing values. If any row has a NULL, the test fails.
  • unique checks that every value in the column appears only once. This is how you validate primary keys.
  • accepted_values checks that every value in the column belongs to a known set. This catches typos or unexpected data from the API.
  • relationships checks referential integrity between two tables. Every value in the tested column must exist in the referenced table's column.
  • Now open grid_pipeline/models/marts/schema.yml to see the mart-layer tests.

The marts schema defines a relationship test on fct_generation:

      - name: fuel_type
        description: Foreign key to dim_fuel_type
        tests:
          - not_null
          - relationships:
              to: ref('dim_fuel_type')
              field: fuel_type

Why test relationships?

The relationships test on fct_generation.fuel_type ensures every fuel type in the fact table has a matching entry in dim_fuel_type. If the API introduces a new fuel type you haven't accounted for, this test will catch it immediately.

Run dbt test

Now that you understand what each test checks, let's execute them all at once.

  • Make sure your terminal is in the grid_pipeline directory.
  • Run all data quality tests by running this command:
dbt test

You should see output showing each test being compiled and executed. All tests should show a Pass status.

What does dbt test do?

dbt compiles each test definition from your schema.yml files into a SQL query. For example, a not_null test becomes a SELECT that counts rows where the column is NULL. If the count is zero, the test passes.

The accepted_values test queries for any values NOT in your defined list. The relationships test does a left join and checks for orphaned records. Each test is a real SQL assertion against your data.

Seeing test failures?

  • If an accepted_values test fails on fuel_type, the API may have returned an unexpected fuel type. Re-run your extract script to pull fresh data and then run dbt run before testing again.
  • If a not_null test fails, check that your staging model's WHERE clause filters out NULL rows correctly.
  • Make sure you are running dbt test from inside the grid_pipeline directory, not the project root.

Help me debug my failing dbt test

Generate and view dbt documentation

dbt can auto-generate a documentation website from your model descriptions, column definitions, and test results. This gives you a browsable reference with a lineage graph showing how data flows from source to mart.

  • Generate the documentation catalog by running this command:
dbt docs generate

You should see output confirming the catalog was generated. This creates catalog.json and manifest.json in your target/ folder.

  • Launch the documentation site locally by running this command:
dbt docs serve

Your browser should open automatically to http://localhost:8080 showing the documentation site.

  • Click on any model name (like fct_generation) to see its description, columns, and test definitions.
  • Click the lineage graph icon in the bottom-right corner to see how data flows from your raw sources through staging to the marts.

What is the lineage graph?

The lineage graph is a visual map of your entire pipeline. It shows how each model depends on others. You can trace data from raw_intensity through stg_intensity into fct_generation. This is one of dbt's most powerful features for understanding complex pipelines.

  • Press Ctrl+C in your terminal to stop the documentation server when you're done browsing.

Browser didn't open?

  • If the browser didn't open automatically, manually navigate to http://localhost:8080 in your browser.
  • If port 8080 is already in use, stop the other process or close the terminal and try again.

Help me get dbt docs serve working

Your pipeline now has automated quality checks and professional documentation. Next up, you'll push everything to GitHub as a portfolio piece.

Push to GitHub

Your pipeline is complete, tested, and documented locally. The final piece is making it visible to the world.

A GitHub repository turns your local project into a shareable portfolio piece. Hiring managers can clone it, read your README, and run your pipeline themselves.

In this step, get ready to:
  • Create a .gitignore to exclude generated files from version control.
  • Write a README.md that explains the project for interviewers.
  • Push everything to a new GitHub repository.
Create a .gitignore file

Your project folder contains files that should never be committed to Git. The DuckDB database file is regenerated by your script. The target/ and dbt_packages/ folders are generated by dbt. Your virtual environment folder holds installed packages that others will reinstall from scratch.

  • Create a new file called .gitignore in your project root (the same folder as extract_load.py) and paste the following:
# Python
__pycache__/
*.pyc
.venv/
venv/

# dbt
target/
dbt_packages/
logs/

# DuckDB
*.duckdb
*.duckdb.wal

# OS
.DS_Store
Thumbs.db

# IDE
.vscode/

What does this file do?

A .gitignore file tells Git which files and folders to skip when tracking changes. Anything listed here will not appear in your GitHub repository.

  • *.duckdb and *.duckdb.wal exclude the database file because anyone can regenerate it by running your extraction script.
  • target/ and dbt_packages/ exclude dbt's compiled SQL output and downloaded packages.
  • .venv/ excludes your virtual environment because others will create their own from your dependency list.
  • Save .gitignore.
  • Confirm the file exists by running:
ls .gitignore

You should see .gitignore printed in the output.

Don't see the file?

Make sure you created .gitignore (with the leading dot) in your project root folder, not inside grid_pipeline/. On Windows, File Explorer may hide files that start with a dot. Use ls in your terminal instead.

Help me troubleshoot my .gitignore

Write a README.md for your project

A good README is the front door of your portfolio project. It tells an interviewer what the project does, how to run it, and where to focus their attention.

  • Create a new file called README.md in your project root and paste the following:
# UK Grid ELT Pipeline

An ELT (Extract, Load, Transform) pipeline that pulls real-time electricity generation and carbon intensity data from the UK Carbon Intensity API, loads it into DuckDB, and transforms it into an analytics-ready star schema using dbt.

## Architecture

1. **Extract** - Python script calls the UK Carbon Intensity API for generation mix and intensity data
2. **Load** - Raw JSON responses are flattened and loaded into DuckDB tables
3. **Transform** - dbt models clean, type-cast, and reshape data into a star schema (staging to marts)

## Prerequisites

- Python 3.9+
- Git

## Setup

```bash
git clone <this-repo-url>
cd elt-pipeline-duckdb-dbt
python -m venv .venv
source .venv/bin/activate  # or .venv\Scripts\activate on Windows
pip install requests duckdb dbt-core dbt-duckdb
```

## How to Run

```bash
# 1. Extract and load data from the Carbon Intensity API
python extract_load.py

# 2. Run dbt transformations
cd grid_pipeline
dbt run
dbt test
```

## Project Structure

```
├── extract_load.py          # Python extraction and loading script
├── profiles.yml             # dbt connection configuration
├── grid_pipeline/
│   ├── dbt_project.yml
│   └── models/
│       ├── staging/         # Data cleaning and type casting
│       │   ├── stg_intensity.sql
│       │   ├── stg_generation_mix.sql
│       │   └── schema.yml
│       └── marts/           # Star schema (facts and dimensions)
│           ├── dim_fuel_type.sql
│           ├── fct_generation.sql
│           └── schema.yml
```

## What to Look At

- **extract_load.py** - Demonstrates REST API interaction, JSON flattening, and DuckDB loading
- **grid_pipeline/models/** - Shows dbt modeling patterns: staging for cleaning, marts for star schema
- **schema.yml files** - Data quality tests including not_null, accepted_values, and referential integrity

What makes a good portfolio README?

Interviewers spend about 30 seconds scanning a README before deciding whether to dig deeper. The key sections are: what it does (one sentence), how to run it (copy-paste commands), and where to focus (point them at your best work).

The "What to Look At" section is especially valuable. It saves a reviewer from guessing which files demonstrate your skills.

  • Save README.md.
Push your project to GitHub

With your files ready, it is time to create a GitHub repository and push your code.

  • Open your browser and go to github.com.
  • Click the + icon in the top right corner, then select New repository.
  • Enter elt-pipeline-duckdb-dbt as the repository name.
  • Select Public for visibility.
  • Leave all other options unchecked (no README, no .gitignore, no license). You already have these locally.
  • Click Create repository.

Why leave everything unchecked?

GitHub can auto-generate a README and .gitignore, but you already created better versions locally. Adding them on GitHub would cause a merge conflict on your first push.

GitHub shows a setup page with commands after you create the repository. You will use the commands below instead.

  • Back in your terminal, make sure you are in your project root (the folder containing extract_load.py).
  • Stage all your files by running:
git add .
  • Commit your files with a descriptive message by running:
git commit -m "Initial commit: ELT pipeline with Python, DuckDB, and dbt"
  • Connect your local repository to GitHub by running:
git remote add origin [[GITHUB_REPO_URL="https://github.com/your-username/elt-pipeline-duckdb-dbt.git"]]
  • Rename your branch to main and push by running:
git branch -M main
git push -u origin main

What do these commands do?

  • git add . stages every file that is not excluded by .gitignore.
  • git commit saves a snapshot of all staged files with your message.
  • git remote add origin links your local repository to the GitHub repository you just created.
  • git branch -M main renames your current branch to main (GitHub's default branch name).
  • git push -u origin main uploads everything to GitHub.
  • Refresh your GitHub repository page in the browser.

You should see all your project files listed: extract_load.py, grid_pipeline/, profiles.yml, .gitignore, and README.md. The .duckdb file should NOT appear because your .gitignore excluded it.

Push rejected or authentication error?

If you see an authentication error, GitHub requires a Personal Access Token (PAT) instead of your password. Go to Settings then Developer settings then Personal access tokens on GitHub to create one. Use the token as your password when prompted.

If the push is rejected because the remote already contains files, you may have accidentally added a README or .gitignore on GitHub. Delete the GitHub repository, recreate it with all options unchecked, and try again.

Help me troubleshoot my git push

✔️ Awesome, I've got everything!

Great. Double check you have saved all your files and your GitHub repo shows extract_load.py, grid_pipeline/, profiles.yml, .gitignore, and README.md.

ⓧ I'd like to double check the full code

Here is the complete .gitignore file:

# Python
__pycache__/
*.pyc
.venv/
venv/

# dbt
target/
dbt_packages/
logs/

# DuckDB
*.duckdb
*.duckdb.wal

# OS
.DS_Store
Thumbs.db

# IDE
.vscode/

Here is the complete README.md file:

# UK Grid ELT Pipeline

An ELT (Extract, Load, Transform) pipeline that pulls real-time electricity generation and carbon intensity data from the UK Carbon Intensity API, loads it into DuckDB, and transforms it into an analytics-ready star schema using dbt.

## Architecture

1. **Extract** - Python script calls the UK Carbon Intensity API for generation mix and intensity data
2. **Load** - Raw JSON responses are flattened and loaded into DuckDB tables
3. **Transform** - dbt models clean, type-cast, and reshape data into a star schema (staging to marts)

## Prerequisites

- Python 3.9+
- Git

## Setup

```bash
git clone <this-repo-url>
cd elt-pipeline-duckdb-dbt
python -m venv .venv
source .venv/bin/activate  # or .venv\Scripts\activate on Windows
pip install requests duckdb dbt-core dbt-duckdb
```

## How to Run

```bash
# 1. Extract and load data from the Carbon Intensity API
python extract_load.py

# 2. Run dbt transformations
cd grid_pipeline
dbt run
dbt test
```

## Project Structure

```
├── extract_load.py          # Python extraction and loading script
├── profiles.yml             # dbt connection configuration
├── grid_pipeline/
│   ├── dbt_project.yml
│   └── models/
│       ├── staging/         # Data cleaning and type casting
│       │   ├── stg_intensity.sql
│       │   ├── stg_generation_mix.sql
│       │   └── schema.yml
│       └── marts/           # Star schema (facts and dimensions)
│           ├── dim_fuel_type.sql
│           ├── fct_generation.sql
│           └── schema.yml
```

## What to Look At

- **extract_load.py** - Demonstrates REST API interaction, JSON flattening, and DuckDB loading
- **grid_pipeline/models/** - Shows dbt modeling patterns: staging for cleaning, marts for star schema
- **schema.yml files** - Data quality tests including not_null, accepted_values, and referential integrity

Your ELT pipeline is now live on GitHub for anyone to see. Next up, you will tackle the Secret Mission and build a reusable dbt macro for renewable energy analysis.

Secret mission

Calculate Renewable Energy Percentages

Your pipeline can already answer questions about individual fuel types. But energy analysts care about the bigger picture: what percentage of electricity came from renewable sources during each half-hour period? Build a reusable dbt macro and a new fact model that flags majority-renewable periods.

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, so there are no ongoing costs.

Resources you used:

  • Project folder on your Desktop (contains extract_load.py, grid_pipeline/, .venv/, and grid_analytics.duckdb).
  • dbt profiles configuration at ~/.dbt/profiles.yml.
  • GitHub repository.
  • Git (Windows users who installed it in Step 1).

Keep everything running

No action needed. Choose this if you plan to keep building on the pipeline or want to re-run the extraction script with different date ranges.

  • Your project folder, virtual environment, and DuckDB database all stay on disk.
  • Your GitHub repository remains public (or private) as configured.
  • Git remains installed and available for other projects.

Pause - I'll come back to this later

Free up disk space from cached build files while keeping your source code intact.

  • Delete the dbt build artifacts by removing the target/ and dbt_packages/ folders inside grid_pipeline/. You can regenerate them with dbt run later.
  • Optionally delete the grid_analytics.duckdb file. You can recreate it by running extract_load.py again.
  • Keep the .venv/ folder so your dependencies are ready when you return.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is irreversible.

  • Delete the project folder. On macOS/Linux, run:
rm -rf ~/Desktop/uk-carbon-pipeline

On Windows, run:

Remove-Item -Recurse -Force ~\Desktop\uk-carbon-pipeline
  • Delete the dbt profiles configuration. On macOS/Linux:
rm ~/.dbt/profiles.yml

On Windows:

Remove-Item ~\.dbt\profiles.yml
  • Delete your GitHub repository by navigating to your repository page, clicking Settings, scrolling to the Danger Zone section, and clicking Delete this repository. Confirm by typing the repository name when prompted.
  • Uninstall Git (Windows only). Open Settings, select Apps, then Installed apps. Find Git in the list and click Uninstall.

That's a Wrap!

That's a Wrap!

Nice work! You just built a production-style ELT pipeline from scratch, extracting live electricity data from the UK National Grid and transforming it into analytics-ready tables.

Here's what you accomplished:

  • Extracted live data from the UK Carbon Intensity API and loaded it into DuckDB, an analytical database that mirrors how production data warehouses work.
  • Transformed raw data into a star schema using dbt, building staging models, a dimension table, and a fact table.
  • Added data quality tests that validate your pipeline's output and pushed the entire project to GitHub as a portfolio piece.
  • Built a reusable dbt macro that calculates renewable energy percentages and flags periods where renewables exceed 50% of generation.

Ready to quiz yourself?