ELT Pipeline: EIA Energy API to S3 with dbt

Build an ELT pipeline extracting U.S. energy data into an S3 data lake with dbt.

Introduction

30 Second Summary

Every month, millions of electricity bills land in mailboxes across the U.S. Behind those numbers is a massive dataset tracking prices, sales, and customer counts across every state and sector.

In this project, you will build a complete ELT pipeline that extracts real U.S. energy data from a government API, loads it into a cloud data lake, and transforms it into analytical models you can query on demand.

What You'll Build

A data pipeline that pulls electricity retail sales data from the EIA Open Data API, stores it as Parquet files in Amazon S3, and transforms it using dbt models running on Amazon Athena.

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

  • A Python extraction script that pulls live data from the EIA API and writes partitioned Parquet files to S3.
  • An S3 data lake with raw data queryable through Athena, plus dbt staging and marts models that transform raw records into analytical tables.
  • A fully tested pipeline with dbt data quality tests and auto-generated documentation viewable in your browser.
  • Secret Mission: Build an incremental model that detects month-over-month electricity price spike anomalies by state.

Are there any prerequisites?

You need an AWS account with the AWS CLI configured, and Python 3.9+ installed on your machine. Step 1 walks you through everything else, including registering for a free EIA API key.

Before We Start

Before We Start...

Set Up Your Tools and Register for the EIA API

Before you can build the pipeline, you need the right tools installed and access to the data source.

In this step, you will install dbt (the transformation tool), register for a free EIA API key (the data source), and create the S3 bucket that will serve as your data lake.

In this step, get ready to:
  • Install dbt-core and the dbt-athena adapter.
  • Register for an EIA API key and test it with a live API call.
  • Create an S3 bucket and configure the Amazon Athena query results location.
Install dbt-core and the dbt-athena adapter

dbt (data build tool) is the transformation engine for your pipeline. The dbt-athena adapter lets dbt send SQL to Amazon Athena, so your transformations run directly against data stored in S3.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.
  • Install dbt-core and the Athena adapter by running this command:
python -m pip install dbt-core dbt-athena

What does this command do?

This installs two packages. dbt-core is the main dbt engine that handles model compilation, testing, and documentation. dbt-athena is the adapter that connects dbt to Amazon Athena so your SQL models run as Athena queries.

  • Verify the installation by running:
dbt --version

You should see output listing dbt-core (version 1.11.x or similar) and dbt-athena listed as an installed adapter.

Command not found after installing?

If your terminal says dbt: command not found, the install location may not be on your system PATH. Try closing and reopening your terminal, then run dbt --version again.

If it still fails, run python -m dbt --version instead to confirm the package is installed.

Help me troubleshoot my dbt installation. I ran python -m pip install dbt-core dbt-athena but dbt --version is not working.

Register for an EIA API key and test it

The U.S. Energy Information Administration (EIA) publishes detailed electricity data through a free API. You need an API key to authenticate your requests.

  • Open the EIA API registration page in your browser:

https://www.eia.gov/opendata/register.php

  • Fill in the First Name, Last Name, and Email fields.
  • Select Student (or the category that best describes you) from the dropdown.
  • Click Register.
  • Check your email inbox for a message from the EIA containing your API key.

What is an API key?

An API key is a unique string that identifies you when making requests to a web service. The EIA uses it to monitor usage and keep the service available for everyone. Registration is free and the key is emailed instantly.

Now test that your key works by making a quick API call.

  • Test your API key by running this command in your terminal, replacing your-eia-api-key-here with the key from your email:
curl "https://api.eia.gov/v2/electricity/retail-sales/data/?api_key=your-eia-api-key-here&length=1"

You should see a JSON response containing a response object with a data array that has one record. That record includes fields like period, stateid, price, and revenue.

Seeing an error or empty response?

If you see API_KEY_INVALID, double-check that you pasted the full key from your email with no extra spaces.

If the command hangs or times out, make sure you are connected to the internet. If you did not receive the API key email, check your spam folder and make sure developer@eia.gov is not blocked.

Help me troubleshoot my EIA API key test. I ran the curl command but did not get a valid JSON response with data.

Save your API key somewhere safe

Copy your API key into a text file or note-taking app. You will need it again in the next step when you write the extraction script.

Create the S3 bucket and configure Athena

Your pipeline needs a central storage location for raw data, transformed tables, and Athena query results. A single S3 bucket will hold all of these, organized into separate prefixes (folders).

  • Create your data lake bucket by running this command, replacing the bucket name with a globally unique name that includes your name (for example, nextwork-eia-datalake-jane):
aws s3 mb s3://[[S3_BUCKET="your-eia-datalake-bucket-here"]] --region us-east-1

Why does the bucket name need to be unique?

S3 bucket names are globally unique across all AWS accounts. If someone else already has a bucket with that name, the command fails. Adding your name to the bucket name avoids this problem.

  • Verify the bucket was created by running:
aws s3 ls

You should see your new bucket name in the list of S3 buckets.

Bucket creation failed?

If you see BucketAlreadyExists, someone else owns that name. Try a different bucket name with more specific identifiers.

If you see IllegalLocationConstraintException, make sure you included --region us-east-1 in the command.

Help me troubleshoot my S3 bucket creation. I ran aws s3 mb but got an error.

Now you need to tell Amazon Athena where to store its query results. Athena is a serverless query engine that reads data directly from S3, but it needs a separate location to write its output files.

  • Open the Amazon Athena console in your browser:

https://console.aws.amazon.com/athena/

  • If this is your first time visiting Athena, click Explore the query editor.
  • Make sure the AWS region in the top-right corner of the console is set to US East (N. Virginia) us-east-1.
  • You will see a banner prompting you to set up a query result location. Click Edit settings.
  • In the Location of query result field, enter the path to your S3 bucket with the athena-results prefix:
s3://[[S3_BUCKET="your-eia-datalake-bucket-here"]]/athena-results/
  • Click Save.

Why does Athena need a separate results location?

Every time you run a query, Athena writes the output as a CSV file to S3. The athena-results/ prefix keeps these output files separate from your raw data and dbt-managed tables.

  • Click Editor in the navigation bar to switch back to the query editor.

You should now see the Athena query editor with no error banners about a missing results location.

Still seeing a results location warning?

Make sure your path starts with s3:// and ends with a trailing /. Also confirm you are in the us-east-1 region, matching where you created the bucket.

Help me configure my Athena query results location. I entered the S3 path but still see a warning in the Athena console.

Your tools are installed, your API key is working, and your AWS resources are ready. Next up, you will write the Python extraction script that pulls live energy data from the EIA API and loads it into your S3 data lake.

Extract Energy Data from the EIA API

Your tools are installed and your S3 bucket is ready to receive data. Now it's time to write the Python script that pulls real U.S. energy data from the EIA API.

The U.S. Energy Information Administration publishes monthly electricity retail sales data for every state. Your script will call their API, paginate through all available records, and store the results as Parquet files in your S3 data lake.

In this step, get ready to:
  • Write Python functions that call the EIA API v2 and handle pagination.
  • Transform the raw API response into clean, typed data using Pandas.
  • Upload partitioned Parquet files to S3 and verify the results.
Install the Python packages for extraction

The extraction script uses several packages that aren't included with Python by default. You need requests for API calls, pandas for data processing, pyarrow for writing Parquet files, and boto3 for uploading to S3.

  • Install all four packages by running this command:
pip install requests pandas pyarrow boto3
  • Wait for the installation to finish. You should see Successfully installed followed by the package names in your terminal output.

Seeing a permission error?

Try running the command with pip install --user instead. If you use a virtual environment, make sure it's activated first.

Help me fix my pip install error

Now you need a project folder to hold the extraction script.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir eia-energy-pipeline
cd eia-energy-pipeline
  • Confirm the folder was created by running:
ls

The folder should be empty for now. You'll create your first file next.

Write the API extraction functions

The EIA API v2 returns a maximum of 5,000 records per request. Your script needs two functions: one to make a single API call, and another to loop through the full dataset by paginating.

  • Create a file called extract_eia_data.py inside your eia-energy-pipeline folder, and add the imports and configuration block:
import requests
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq
import boto3
import io
import os

# Configuration
API_KEY = "your-eia-api-key-here"
BASE_URL = "https://api.eia.gov/v2/electricity/retail-sales/data/"
S3_BUCKET = "[[EIA_BUCKET=\"your-eia-datalake-bucket-here\"]]"
S3_PREFIX = "raw/eia_retail_sales"

What does this code do?

The imports bring in all the libraries you installed. requests handles HTTP calls to the API. pandas processes the data as a table. pyarrow writes Parquet files. boto3 uploads to S3.

The configuration section sets your API key, the API endpoint URL, and the S3 bucket and prefix where files will be stored.

  • Replace your-eia-api-key-here with the EIA API key you received in Step 1.
  • Replace the S3_BUCKET value with the name of the S3 bucket you created in Step 1.
  • Save extract_eia_data.py. You won't see output yet. The functions you add next will use these settings.

Now add the function that makes a single API request. The EIA API expects query parameters specifying which data columns you want (price, revenue, sales, customers), the frequency, and pagination controls.

  • In extract_eia_data.py, add the fetch_eia_data function below your configuration block:
def fetch_eia_data(api_key, offset=0, length=5000):
    """Fetch electricity retail sales data from EIA API v2."""
    params = {
        "api_key": api_key,
        "frequency": "monthly",
        "data[0]": "price",
        "data[1]": "revenue",
        "data[2]": "sales",
        "data[3]": "customers",
        "sort[0][column]": "period",
        "sort[0][direction]": "desc",
        "offset": offset,
        "length": length,
    }
    response = requests.get(BASE_URL, params=params)
    response.raise_for_status()
    return response.json()

What does this function do?

  • The params dictionary tells the API to return monthly data with four metrics: price, revenue, sales, and customers.
  • offset and length control pagination. Each request returns up to 5,000 rows, so you need multiple calls to get the full dataset.
  • raise_for_status() throws an error if the API returns a failure code, so problems surface immediately.

One API call only returns one page of results. You need a second function that loops through all pages until every record is collected.

  • Add the fetch_all_data function below fetch_eia_data:
def fetch_all_data(api_key):
    """Paginate through all available data."""
    all_records = []
    offset = 0
    length = 5000

    while True:
        print(f"Fetching records starting at offset {offset}...")
        result = fetch_eia_data(api_key, offset=offset, length=length)
        records = result["response"]["data"]

        if not records:
            break

        all_records.extend(records)
        total = int(result["response"]["total"])
        offset += length

        if offset >= total:
            break

    print(f"Fetched {len(all_records)} total records.")
    return all_records

What does this function do?

  • The while True loop keeps calling fetch_eia_data with increasing offsets until all records are fetched.
  • total comes from the API response itself. The loop stops once the offset exceeds the total number of available records.
  • The print statements give you progress updates in the terminal while the script runs. This is helpful because the full extraction takes a few minutes.
  • Save extract_eia_data.py. You won't see output yet. The data processing functions you add next will use the records these functions fetch.
Transform the data and write Parquet files to S3

The EIA API returns all data values as strings. Before storing the data, you need to cast numeric columns to proper types. You'll also partition the data by year so Athena can scan only the years it needs when you query later.

Why Parquet instead of CSV?

Parquet is a columnar storage format. Athena only reads the columns needed per query, which reduces the amount of data scanned. Since Athena charges per TB scanned, Parquet files directly lower your costs.

Parquet also preserves data types and compresses much better than CSV. It's the standard format for modern data lakes.

  • In extract_eia_data.py, add the process_data function below fetch_all_data:
def process_data(records):
    """Convert API records to a cleaned DataFrame."""
    df = pd.DataFrame(records)

    # Cast string values to numeric
    for col in ["price", "revenue", "sales", "customers"]:
        if col in df.columns:
            df[col] = pd.to_numeric(df[col], errors="coerce")

    # Extract year from period for partitioning
    df["year"] = df["period"].str[:4]

    # Rename columns for clarity
    df = df.rename(columns={
        "stateid": "state_id",
        "stateDescription": "state_name",
        "sectorid": "sector_id",
        "sectorName": "sector_name",
    })

    return df

What does this function do?

  • pd.to_numeric(df[col], errors="coerce") converts the string values from the API into actual numbers. The "coerce" option turns any unparseable values into NaN instead of crashing.
  • df["period"].str[:4] extracts the year from dates like 2024-06. This becomes the partition key so Athena can skip irrelevant years.
  • The column renames turn API field names like stateid into cleaner names like state_id that are easier to work with in SQL.
  • Save extract_eia_data.py. You won't see output yet. The next function uses this cleaned data to write files to S3.

The last helper function takes the cleaned DataFrame and writes it to S3 as Parquet files, one per year. This Hive-style partitioning (year=YYYY/ prefixes) is a best practice for Athena because it lets queries skip entire partitions they don't need.

  • Add the upload_partitioned_parquet function below process_data:
def upload_partitioned_parquet(df, bucket, prefix):
    """Write DataFrame as partitioned Parquet files to S3."""
    s3 = boto3.client("s3")

    for year, year_df in df.groupby("year"):
        # Remove the partition column from the data itself
        write_df = year_df.drop(columns=["year"])
        table = pa.Table.from_pandas(write_df, preserve_index=False)

        buffer = io.BytesIO()
        pq.write_table(table, buffer)
        buffer.seek(0)

        key = f"{prefix}/year={year}/data.parquet"
        s3.put_object(Bucket=bucket, Key=key, Body=buffer.getvalue())
        print(f"Uploaded s3://{bucket}/{key} ({len(year_df)} rows)")

What does this function do?

  • df.groupby("year") splits the data so each year gets its own Parquet file.
  • The year column is dropped before writing because the year is already encoded in the S3 path (year=2024/data.parquet). Storing it twice would be redundant.
  • pa.Table.from_pandas() converts the Pandas DataFrame into a PyArrow table, which is then serialized to the Parquet format in an in-memory buffer before uploading.
  • s3.put_object() uploads each buffer directly to S3. The boto3 client uses the AWS credentials you already configured with the AWS CLI.
  • Save extract_eia_data.py. All four helper functions are in place. The next step ties them together with a main execution block.
Run the extraction script and verify S3

The main block is the entry point that orchestrates everything. It calls the functions you just wrote in sequence: fetch all records, process them, and upload to S3.

  • Add the main execution block at the bottom of extract_eia_data.py:
if __name__ == "__main__":
    print("Starting EIA data extraction...")
    records = fetch_all_data(API_KEY)
    df = process_data(records)
    print(f"Processed {len(df)} records across {df['year'].nunique()} years.")
    upload_partitioned_parquet(df, S3_BUCKET, S3_PREFIX)
    print("Extraction complete!")

What does this code do?

  • if __name__ == "__main__": makes this block run only when you execute the script directly. It won't run if another script imports your functions.
  • The three function calls happen in order: fetch the records, process them into a DataFrame, then upload the partitioned Parquet files to S3.
  • Save extract_eia_data.py.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before running it.

ⓧ I'd like to double check the full code

Here's the complete extract_eia_data.py file. Compare yours against it to make sure everything matches.

import requests
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq
import boto3
import io
import os

# Configuration
API_KEY = "your-eia-api-key-here"
BASE_URL = "https://api.eia.gov/v2/electricity/retail-sales/data/"
S3_BUCKET = "[[EIA_BUCKET=\"your-eia-datalake-bucket-here\"]]"
S3_PREFIX = "raw/eia_retail_sales"

def fetch_eia_data(api_key, offset=0, length=5000):
    """Fetch electricity retail sales data from EIA API v2."""
    params = {
        "api_key": api_key,
        "frequency": "monthly",
        "data[0]": "price",
        "data[1]": "revenue",
        "data[2]": "sales",
        "data[3]": "customers",
        "sort[0][column]": "period",
        "sort[0][direction]": "desc",
        "offset": offset,
        "length": length,
    }
    response = requests.get(BASE_URL, params=params)
    response.raise_for_status()
    return response.json()

def fetch_all_data(api_key):
    """Paginate through all available data."""
    all_records = []
    offset = 0
    length = 5000

    while True:
        print(f"Fetching records starting at offset {offset}...")
        result = fetch_eia_data(api_key, offset=offset, length=length)
        records = result["response"]["data"]

        if not records:
            break

        all_records.extend(records)
        total = int(result["response"]["total"])
        offset += length

        if offset >= total:
            break

    print(f"Fetched {len(all_records)} total records.")
    return all_records

def process_data(records):
    """Convert API records to a cleaned DataFrame."""
    df = pd.DataFrame(records)

    # Cast string values to numeric
    for col in ["price", "revenue", "sales", "customers"]:
        if col in df.columns:
            df[col] = pd.to_numeric(df[col], errors="coerce")

    # Extract year from period for partitioning
    df["year"] = df["period"].str[:4]

    # Rename columns for clarity
    df = df.rename(columns={
        "stateid": "state_id",
        "stateDescription": "state_name",
        "sectorid": "sector_id",
        "sectorName": "sector_name",
    })

    return df

def upload_partitioned_parquet(df, bucket, prefix):
    """Write DataFrame as partitioned Parquet files to S3."""
    s3 = boto3.client("s3")

    for year, year_df in df.groupby("year"):
        # Remove the partition column from the data itself
        write_df = year_df.drop(columns=["year"])
        table = pa.Table.from_pandas(write_df, preserve_index=False)

        buffer = io.BytesIO()
        pq.write_table(table, buffer)
        buffer.seek(0)

        key = f"{prefix}/year={year}/data.parquet"
        s3.put_object(Bucket=bucket, Key=key, Body=buffer.getvalue())
        print(f"Uploaded s3://{bucket}/{key} ({len(year_df)} rows)")

if __name__ == "__main__":
    print("Starting EIA data extraction...")
    records = fetch_all_data(API_KEY)
    df = process_data(records)
    print(f"Processed {len(df)} records across {df['year'].nunique()} years.")
    upload_partitioned_parquet(df, S3_BUCKET, S3_PREFIX)
    print("Extraction complete!")

Time to run the script and watch it extract the full dataset from the EIA API.

  • Make sure your terminal is in the eia-energy-pipeline folder (the one containing extract_eia_data.py).
  • Run the extraction script by running this command:
python extract_eia_data.py

What should I see?

The script prints progress as it paginates through the API. You'll see messages like:

Starting EIA data extraction...
Fetching records starting at offset 0...
Fetching records starting at offset 5000...
Fetching records starting at offset 10000...
...

This may take a few minutes depending on your internet connection. The script finishes with an Extraction complete! message and a count of uploaded files.

Seeing an error when running the script?

  • If you see 403 Forbidden or Invalid API Key, double check that you pasted your EIA API key correctly in the API_KEY variable. Make sure there are no extra spaces or missing characters.
  • If you see NoCredentialsError, your AWS CLI is not configured. Run aws configure and enter your AWS access key and secret key.
  • If you see NoSuchBucket, the S3_BUCKET value in your script doesn't match the bucket name you created in Step 1.

Help me debug my EIA extraction script error

Once the script finishes, verify that your Parquet files landed in S3 with the correct partition structure.

  • List the partitions in your S3 bucket by running this command:
aws s3 ls s3://[[EIA_BUCKET="your-eia-datalake-bucket-here"]]/raw/eia_retail_sales/

You should see a list of folders named year=2001/, year=2002/, and so on through the most recent year of data. Each folder contains a single data.parquet file.

  • Confirm one of the Parquet files exists by checking inside a single partition:
aws s3 ls s3://[[EIA_BUCKET="your-eia-datalake-bucket-here"]]/raw/eia_retail_sales/year=2024/

You should see data.parquet listed with a file size in the output.

Not seeing any partitions?

  • Make sure the bucket name in the aws s3 ls command exactly matches the S3_BUCKET value in your script.
  • Check that the script printed Uploaded s3://... messages. If it printed Fetched 0 total records, your API key may be invalid or the API may be temporarily unavailable.

Help me figure out why my S3 bucket is empty after running the extraction

Your raw energy data is now sitting in S3 as clean, partitioned Parquet files. Next up, you'll create a table in Athena so you can query this data using SQL.

Set Up Athena and Create the Raw Data Table

Your S3 data lake now has real energy data from the EIA API sitting as Parquet files. Right now, those files are just objects in a bucket with no way to query them.

To run SQL against that data, you need Amazon Athena. Athena is a serverless query engine that reads directly from S3. There is no database server to provision or manage.

In this step, get ready to:
  • Create a database in Athena to organize your raw data.
  • Register your Parquet files as an external table with the correct schema.
  • Run a test query to confirm Athena can read your energy data.
Create a database in Athena
  • Open the AWS Management Console in your browser.
  • Type Athena in the search bar at the top and select Athena from the results.
  • Click Query editor in the left sidebar if it is not already open.
  • In the left panel, confirm the Data source dropdown shows AwsDataCatalog.

Why Athena instead of Redshift?

Athena is fully serverless. You do not need to provision a cluster, manage storage, or pay for idle time. You only pay $5 per TB of data scanned.

For a portfolio project with a small dataset, Athena costs effectively $0. It also demonstrates that you understand cost-efficient data lakehouse architecture.

  • Create the database for your raw data by pasting this query into the Athena query editor and clicking Run:
CREATE DATABASE IF NOT EXISTS eia_raw;

What does this query do?

eia_raw is the database that will hold your raw energy data table. Athena stores this metadata in the Glue Data Catalog, which acts as a central schema registry for all your data lake tables.

  • After the query completes, click the refresh icon next to Database in the left panel.
  • Select eia_raw from the Database dropdown.

Don't see eia_raw in the dropdown?

  • Make sure your Data source is set to AwsDataCatalog, not another source.
  • Check that the query output shows Query successful. If you see an error, look for a typo in the database name.
  • Help me troubleshoot my Athena CREATE DATABASE query.
Register your Parquet data as an external table

Your Parquet files are in S3, but Athena does not know they exist yet. You need to create an external table that tells Athena the column names, data types, file format, and S3 location of your data.

What is an external table?

An external table is a schema definition that points to data stored outside the database engine. The data stays in S3. Athena reads it on demand when you run a query, so there is no data to import or duplicate.

  • Clear the query editor, then paste this SQL and click Run:
CREATE EXTERNAL TABLE IF NOT EXISTS eia_raw.retail_sales (
    period string,
    state_id string,
    state_name string,
    sector_id string,
    sector_name string,
    price double,
    revenue double,
    sales double,
    customers bigint
)
PARTITIONED BY (year string)
STORED AS PARQUET
LOCATION 's3://[[S3_BUCKET="your-eia-datalake-bucket-here"]]/raw/eia_retail_sales/'

What does this query do?

  • The column definitions (period, state_id, price, etc.) match the columns your Python extraction script writes to Parquet.
  • PARTITIONED BY (year string) tells Athena the data is split into year-based folders. Queries filtering by year only scan the relevant partition, reducing cost.
  • LOCATION points to the S3 prefix where your Parquet files live. The trailing slash is required.

Athena now knows the table schema, but it has not scanned S3 to discover which year partitions exist yet.

  • Load the partitions by pasting this query and clicking Run:
MSCK REPAIR TABLE eia_raw.retail_sales;

What does MSCK REPAIR TABLE do?

This command scans the S3 location for Hive-style partition folders (like year=2020/, year=2021/) and registers each one in the Glue Data Catalog. Without this step, Athena would return zero rows.

  • After the query completes, expand Tables in the left panel under the eia_raw database.
  • You should see retail_sales listed with its columns visible underneath.

Don't see the retail_sales table?

  • Check that you selected eia_raw in the database dropdown before running the CREATE EXTERNAL TABLE query.
  • Verify the LOCATION path ends with a trailing slash and matches your actual S3 bucket name and prefix.
  • Help me troubleshoot my Athena external table.
Query the raw data to confirm everything works

The table and partitions are registered. Time to run your first real query against the energy data.

  • Pull a sample of your energy data by pasting this query and clicking Run:
SELECT *
FROM eia_raw.retail_sales
LIMIT 10;

What should I see?

The Results panel should show 10 rows of energy data with columns like period, state_name, sector_name, price, and sales. Each row represents one month of electricity retail sales data for a specific state and sector.

  • Confirm the Results panel shows rows with actual data values in the price and sales columns, not all nulls.

Seeing zero rows or all null values?

  • Zero rows usually means partitions were not loaded. Run MSCK REPAIR TABLE eia_raw.retail_sales again and retry your query.
  • All null values for numeric columns may mean the column types in your CREATE TABLE do not match the Parquet file. Double check that price and revenue are double and customers is bigint.
  • Help me debug my Athena query results.

Your raw data is now queryable in Athena. Next up, you will initialize a dbt project that connects to Athena and transforms this raw data into clean analytical models.

Initialize Your dbt Project and Connect to Athena

In the previous step, you confirmed that Athena can query your raw energy data straight from S3. Now it's time to bring dbt into the picture.

dbt is a transformation framework that lets you write SQL models to shape raw data into clean, analytical tables. In this step, you'll scaffold a dbt project and wire it up to Athena so it can run those models against your data lake.

In this step, get ready to:
  • Scaffold a new dbt project using dbt init.
  • Configure the project file and create an Athena connection profile.
  • Verify the connection with dbt debug.
Scaffold your dbt project

The dbt init command creates a ready-made project folder with all the directories dbt needs. It saves you from building the structure by hand.

  • Open your terminal and make sure you are in the same directory where your extract_eia_data.py file lives.
  • Initialize a new dbt project by running this command:
dbt init eia_energy_pipeline

dbt init will ask you some questions

When prompted to choose a database adapter, select the number that corresponds to athena. You may also be asked for a threads value. Enter 4.

  • Confirm the project folder was created by running:
ls eia_energy_pipeline

You should see folders like models/, analyses/, macros/, and a dbt_project.yml file.

Command not found?

Make sure you installed dbt-core and dbt-athena in Step 1. If you used a virtual environment, confirm it is activated.

Help me troubleshoot why dbt init is not recognized in my terminal.

  • Move into the new project directory and clean up the example files that dbt generated by running these commands:
cd eia_energy_pipeline
rm -r models/example/

Why delete the example folder?

dbt scaffolds a models/example/ directory with sample SQL files. You'll replace these with your own staging and marts models, so removing them now keeps the project clean.

  • Create the two model directories your project will use by running:
mkdir -p models/staging models/marts
  • Verify the model directories exist by running:
ls models/

You should see staging and marts listed.

Now it's time to configure the project file so dbt knows how to organize your models.

  • Open dbt_project.yml in your text editor.
  • Replace the entire contents of the file with the following:
name: eia_energy_pipeline
version: "1.0.0"

profile: eia_energy_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:
  eia_energy_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table

What does this configuration do?

The name and profile fields link this project to the connection profile you'll create next. The path fields tell dbt where to find your models, tests, seeds, and macros.

The models section is where the real customization happens. Staging models are materialized as views (lightweight queries that don't store data). Marts models are materialized as tables (physical data stored in S3 for fast reads).

  • Save dbt_project.yml.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file before moving on.

ⓧ I'd like to double check the full code

Here is the complete dbt_project.yml for comparison.

name: eia_energy_pipeline
version: "1.0.0"

profile: eia_energy_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:
  eia_energy_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table
Create the connection profile for Athena

dbt needs to know how to connect to your data warehouse. It looks for connection details in a file called profiles.yml, which lives in a hidden .dbt folder in your home directory.

Where is profiles.yml?

On macOS and Linux, the file lives at ~/.dbt/profiles.yml. On Windows, it lives at C:\Users\<username>\.dbt\profiles.yml.

The dbt init command you ran earlier may have already created this directory and a starter file. If the file already exists, you'll replace its contents.

  • Open ~/.dbt/profiles.yml in your text editor. If the file does not exist, create it.
  • Replace the entire contents of the file with the following:
eia_energy_pipeline:
  target: dev
  outputs:
    dev:
      type: athena
      s3_staging_dir: s3://[[S3_BUCKET="your-eia-datalake-bucket-here"]]/athena-results/
      s3_data_dir: s3://[[S3_BUCKET="your-eia-datalake-bucket-here"]]/dbt-data/
      s3_data_naming: schema_table_unique
      region_name: us-east-1
      database: awsdatacatalog
      schema: eia_transformed
      threads: 4
      num_retries: 3

What do these connection settings mean?

  • s3_staging_dir is where Athena writes temporary query results. This is the same location you configured in the Athena console earlier.
  • s3_data_dir is where dbt stores the physical data for table-materialized models.
  • database is set to awsdatacatalog because Athena uses the Glue Data Catalog as its metadata store.
  • schema is set to eia_transformed. dbt will create this database in Glue automatically when you run your first model.

How does dbt authenticate with AWS?

The dbt-athena adapter uses the same credentials as your AWS CLI. Since your CLI is already configured, dbt picks up those credentials automatically. No extra keys or passwords needed in this file.

  • Replace your-eia-datalake-bucket-here with the actual name of the S3 bucket you created earlier.
  • If your S3 bucket is in a region other than us-east-1, update the region_name value to match your bucket's region.
  • Save profiles.yml.

Can't find the .dbt folder?

The .dbt folder is hidden by default. On macOS, press Cmd+Shift+. in Finder to show hidden files. On Windows, enable Show hidden items in File Explorer's View tab.

If the folder truly does not exist, create it by running:

mkdir -p ~/.dbt

Help me find or create the .dbt directory for my profiles.yml file.

✔️ Awesome, I've got everything!

Great. Make sure you've saved the file before moving on.

ⓧ I'd like to double check the full code

Here is the complete profiles.yml for comparison. Remember to substitute your own bucket name.

eia_energy_pipeline:
  target: dev
  outputs:
    dev:
      type: athena
      s3_staging_dir: s3://[[S3_BUCKET="your-eia-datalake-bucket-here"]]/athena-results/
      s3_data_dir: s3://[[S3_BUCKET="your-eia-datalake-bucket-here"]]/dbt-data/
      s3_data_naming: schema_table_unique
      region_name: us-east-1
      database: awsdatacatalog
      schema: eia_transformed
      threads: 4
      num_retries: 3
Verify your connection with dbt debug

Before building any models, you want to confirm that dbt can actually reach Athena. The dbt debug command checks your project file, profiles file, and database connection in one go.

  • Make sure your terminal is inside the eia_energy_pipeline project directory.
  • Test the connection by running this command:
dbt debug

What should I see?

dbt runs through a checklist of configuration tests. Look for a series of green OK lines and a final message that says All checks passed! at the bottom.

The checks confirm that your dbt_project.yml is valid, your profiles.yml is valid, and dbt can connect to Athena successfully.

Seeing a connection error?

  • Check that the profile value in dbt_project.yml matches the top-level key in profiles.yml. Both must be eia_energy_pipeline.
  • Make sure your s3_staging_dir URL ends with a trailing slash (/).
  • Verify your AWS CLI credentials are still active by running aws sts get-caller-identity. If this fails, reconfigure your CLI.
  • Confirm the region_name in profiles.yml matches the region where your S3 bucket lives.

Help me debug my dbt connection to Athena.

Your dbt project is scaffolded and talking to Athena. Next up, you'll write the SQL models that transform raw energy data into clean, analytical tables.

Build dbt Staging and Marts Models

Your dbt project is connected to Athena and ready to transform data.

In dbt, models are organized into layers. The staging layer cleans and standardizes raw data. The marts layer builds business-friendly analytical tables on top of staging. This separation keeps your pipeline modular and easy to maintain.

In this step, get ready to:
  • Create source definitions and a staging model that cleans raw EIA data.
  • Build two marts models that aggregate and rank electricity data by state.
  • Run dbt run to materialize all models in Athena and verify the results.
Create source definitions and a staging model

Before dbt can reference your raw Athena table, it needs a source definition. This is a YAML file that tells dbt where your raw data lives and what columns it has.

  • Create the staging and marts directories inside your models/ folder by running this command from your eia_energy_pipeline project directory:
mkdir -p models/staging models/marts
  • Confirm the directories exist by running:
ls models/

You should see staging and marts listed in the output.

  • Create a new file called _eia__sources.yml inside models/staging/ and paste the following source definition:
version: 2

sources:
  - name: eia_raw
    database: awsdatacatalog
    schema: eia_raw
    tables:
      - name: retail_sales
        description: "Raw EIA electricity retail sales data loaded from the API as partitioned Parquet in S3."
        columns:
          - name: period
            description: "Month of the data point in YYYY-MM format."
          - name: state_id
            description: "Two-letter state code."
          - name: state_name
            description: "Full state name."
          - name: sector_id
            description: "Sector code (RES, COM, IND, TRA, OTH, ALL)."
          - name: sector_name
            description: "Full sector name."
          - name: price
            description: "Average price in cents per kilowatthour."
          - name: revenue
            description: "Revenue in million dollars."
          - name: sales
            description: "Sales in million kilowatthours."
          - name: customers
            description: "Number of customers."

What does this file do?

The database and schema fields point dbt to the eia_raw database you created in Athena. The tables section maps to your retail_sales external table.

Column descriptions become part of dbt's auto-generated documentation later. They also help teammates understand what each field means without checking the API docs.

  • Save _eia__sources.yml.

You won't see any visible change yet. The staging model in the next chunk uses this source definition.

Now you can build the staging model. This model reads from the raw source and cleans the data by renaming columns, casting types, and filtering out nulls.

  • Create a new file called stg_eia__retail_sales.sql inside models/staging/ and paste the following SQL:
with source as (

    select * from {{ source('eia_raw', 'retail_sales') }}

),

cleaned as (

    select
        period,
        state_id,
        state_name,
        sector_id,
        lower(sector_name) as sector_name,
        cast(price as double) as price_cents_per_kwh,
        cast(revenue as double) as revenue_millions_usd,
        cast(sales as double) as sales_millions_kwh,
        cast(customers as bigint) as customer_count,
        substr(period, 1, 4) as year,
        substr(period, 6, 2) as month

    from source
    where state_id is not null
      and period is not null

)

select * from cleaned

What does this model do?

  • {{ source('eia_raw', 'retail_sales') }} is dbt's way of referencing the raw table you defined in the source YAML. dbt resolves this to the actual Athena table at runtime.
  • The cast() calls convert string-typed API values into proper numeric types. The EIA API returns all data values as strings.
  • Column names like price_cents_per_kwh include units, making downstream models self-documenting.
  • The year and month columns are extracted from the period string. This makes it easy to filter and group by time in the marts layer.
  • Save stg_eia__retail_sales.sql.

You won't see output yet. You will verify the staging model works when you run dbt run later in this step.

Why a staging layer instead of querying raw data directly?

Without a staging layer, every downstream model would repeat the same type casting and column renaming. If the API schema changes, you would need to update every model that touches raw data.

Staging models give you a single place to handle raw data quirks. Every model downstream reads clean, consistently named columns.

✔️ Awesome, I've got everything!

Make sure both files are saved inside models/staging/. Next up, you will build the marts models.

ⓧ I'd like to double check the full code

Here is the complete _eia__sources.yml:

version: 2

sources:
  - name: eia_raw
    database: awsdatacatalog
    schema: eia_raw
    tables:
      - name: retail_sales
        description: "Raw EIA electricity retail sales data loaded from the API as partitioned Parquet in S3."
        columns:
          - name: period
            description: "Month of the data point in YYYY-MM format."
          - name: state_id
            description: "Two-letter state code."
          - name: state_name
            description: "Full state name."
          - name: sector_id
            description: "Sector code (RES, COM, IND, TRA, OTH, ALL)."
          - name: sector_name
            description: "Full sector name."
          - name: price
            description: "Average price in cents per kilowatthour."
          - name: revenue
            description: "Revenue in million dollars."
          - name: sales
            description: "Sales in million kilowatthours."
          - name: customers
            description: "Number of customers."

And the complete stg_eia__retail_sales.sql:

with source as (

    select * from {{ source('eia_raw', 'retail_sales') }}

),

cleaned as (

    select
        period,
        state_id,
        state_name,
        sector_id,
        lower(sector_name) as sector_name,
        cast(price as double) as price_cents_per_kwh,
        cast(revenue as double) as revenue_millions_usd,
        cast(sales as double) as sales_millions_kwh,
        cast(customers as bigint) as customer_count,
        substr(period, 1, 4) as year,
        substr(period, 6, 2) as month

    from source
    where state_id is not null
      and period is not null

)

select * from cleaned
Create two marts models for state-level analysis

The staging layer gives you clean data. Now you will build the marts layer, where the real analytical value lives.

You will create two models. The first aggregates electricity metrics annually by state and sector. The second ranks states by residential electricity price for the most recent year.

  • Create a new file called state_electricity_summary.sql inside models/marts/ and paste the following SQL:
with staging as (

    select * from {{ ref('stg_eia__retail_sales') }}
    where sector_id != 'ALL'

),

annual_summary as (

    select
        state_id,
        state_name,
        sector_id,
        sector_name,
        year,
        round(avg(price_cents_per_kwh), 2) as avg_price_cents_per_kwh,
        round(sum(revenue_millions_usd), 2) as total_revenue_millions_usd,
        round(sum(sales_millions_kwh), 2) as total_sales_millions_kwh,
        max(customer_count) as max_monthly_customers

    from staging
    group by
        state_id,
        state_name,
        sector_id,
        sector_name,
        year

)

select * from annual_summary

What does this model do?

  • {{ ref('stg_eia__retail_sales') }} references your staging model by name. dbt uses ref() to build a dependency graph so models run in the correct order.
  • The where sector_id != 'ALL' filter excludes the EIA's pre-aggregated rollup rows. Without this filter, you would double-count when summing across sectors.
  • The model groups monthly data into annual summaries, producing one row per state, sector, and year.
  • Save state_electricity_summary.sql.

You won't see output yet. The next model completes the marts layer, and then you will run everything together.

  • Create a new file called residential_price_rankings.sql inside models/marts/ and paste the following SQL:
with latest_year as (

    select max(year) as max_year
    from {{ ref('stg_eia__retail_sales') }}
    where sector_id = 'RES'

),

residential_prices as (

    select
        s.state_id,
        s.state_name,
        s.year,
        round(avg(s.price_cents_per_kwh), 2) as avg_residential_price,
        round(sum(s.sales_millions_kwh), 2) as total_residential_sales,
        max(s.customer_count) as residential_customers

    from {{ ref('stg_eia__retail_sales') }} s
    cross join latest_year ly
    where s.sector_id = 'RES'
      and s.year = ly.max_year
      and s.state_id not in ('US', 'DC')
    group by
        s.state_id,
        s.state_name,
        s.year

)

select
    *,
    row_number() over (order by avg_residential_price desc) as price_rank

from residential_prices
order by price_rank

What does this model do?

  • The latest_year CTE dynamically finds the most recent year with residential data. This means the model stays up to date as new data arrives without any code changes.
  • The cross join filters every row to the most recent year. The not in ('US', 'DC') clause excludes the national rollup row and D.C. to compare states only.
  • row_number() over (order by avg_residential_price desc) assigns rank 1 to the most expensive state. This lets you instantly see which states have the highest electricity prices.
  • Save residential_price_rankings.sql.

Both marts models are ready. You will verify them in the next substep when you run dbt run.

✔️ Awesome, I've got everything!

Make sure both files are saved inside models/marts/. Time to materialize everything in Athena.

ⓧ I'd like to double check the full code

Here is the complete state_electricity_summary.sql:

with staging as (

    select * from {{ ref('stg_eia__retail_sales') }}
    where sector_id != 'ALL'

),

annual_summary as (

    select
        state_id,
        state_name,
        sector_id,
        sector_name,
        year,
        round(avg(price_cents_per_kwh), 2) as avg_price_cents_per_kwh,
        round(sum(revenue_millions_usd), 2) as total_revenue_millions_usd,
        round(sum(sales_millions_kwh), 2) as total_sales_millions_kwh,
        max(customer_count) as max_monthly_customers

    from staging
    group by
        state_id,
        state_name,
        sector_id,
        sector_name,
        year

)

select * from annual_summary

And the complete residential_price_rankings.sql:

with latest_year as (

    select max(year) as max_year
    from {{ ref('stg_eia__retail_sales') }}
    where sector_id = 'RES'

),

residential_prices as (

    select
        s.state_id,
        s.state_name,
        s.year,
        round(avg(s.price_cents_per_kwh), 2) as avg_residential_price,
        round(sum(s.sales_millions_kwh), 2) as total_residential_sales,
        max(s.customer_count) as residential_customers

    from {{ ref('stg_eia__retail_sales') }} s
    cross join latest_year ly
    where s.sector_id = 'RES'
      and s.year = ly.max_year
      and s.state_id not in ('US', 'DC')
    group by
        s.state_id,
        s.state_name,
        s.year

)

select
    *,
    row_number() over (order by avg_residential_price desc) as price_rank

from residential_prices
order by price_rank
Run dbt run and verify results in Athena

All three models are written. Time to let dbt compile and execute them against Athena.

  • Materialize all your models in Athena by running this command from your eia_energy_pipeline project directory:
dbt run

What does dbt run do?

dbt reads your model files, compiles the Jinja templates (like {{ ref() }} and {{ source() }}) into raw SQL, and executes them in Athena in dependency order.

Staging models materialize as views (fast, no data copy). Marts models materialize as tables (stored as Parquet in your S3 bucket under the dbt-data/ prefix). These settings come from your dbt_project.yml configuration.

You should see output showing 3 models completed successfully. It looks something like this:

Completed successfully

Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

Seeing errors in the dbt run output?

  • If you see a Compilation Error: source eia_raw not found, double check that your _eia__sources.yml file is saved inside models/staging/ (not in the project root).
  • If Athena returns a TABLE_NOT_FOUND error, verify that your schema: eia_raw in the source YAML matches the database name you created in the Athena console.
  • If you see a connection error, run dbt debug to check your Athena connection is still valid.

Help me debug my dbt run errors

Now verify the results directly in Athena to see your transformed data.

  • Open the Athena console in your browser.
  • In the left sidebar, change the Database dropdown to eia_transformed.

You should see three tables listed: stg_eia__retail_sales (view), state_electricity_summary (table), and residential_price_rankings (table).

  • Run this query in the Athena query editor to see the top 5 most expensive states for residential electricity:
SELECT state_id, state_name, avg_residential_price, price_rank
FROM eia_transformed.residential_price_rankings
WHERE price_rank <= 5
ORDER BY price_rank;

You should see 5 rows with state names, prices, and rankings. Hawaii typically appears near the top due to its island geography and fuel import costs.

Query returning zero rows?

  • Make sure the Database dropdown is set to eia_transformed, not eia_raw.
  • If the table does not exist, confirm dbt run completed with PASS=3. Re-run it if needed.

Help me troubleshoot my Athena query results

Your dbt models are live and queryable in Athena. Next up, you will add data quality tests and auto-generated documentation to make this pipeline production-ready.

Add Data Quality Tests and Documentation

Your dbt models are live in Athena, transforming raw energy data into analytical tables.

Models without tests are just SQL files you hope are correct. In production data engineering, every model needs automated checks that catch data problems before they reach dashboards or reports. dbt has a built-in testing framework that lets you define these checks in YAML, plus a documentation generator that turns your project into a browsable website.

In this step, get ready to:
  • Add schema tests to your staging and marts models.
  • Generate and browse auto-generated dbt documentation.
  • Run your full pipeline end-to-end with dbt build.
Add schema tests to your staging and marts models

dbt schema tests are assertions you declare in YAML files next to your models. When you run dbt test, dbt compiles each test into a SQL query and runs it against your data. If any query returns rows, the test fails.

What are dbt schema tests?

dbt ships with four built-in generic tests: not_null (column has no null values), unique (column has no duplicate values), accepted_values (column only contains values from a list), and relationships (column references a valid row in another model).

You define them in YAML files alongside column descriptions. This keeps your tests and documentation in one place.

  • Create a new file called _staging__models.yml inside your models/staging/ folder.
  • Copy the following YAML into the file:
version: 2

models:
  - name: stg_eia__retail_sales
    description: "Cleaned and typed EIA electricity retail sales data."
    columns:
      - name: period
        description: "Month of data in YYYY-MM format."
        tests:
          - not_null
      - name: state_id
        description: "Two-letter state code."
        tests:
          - not_null
      - name: sector_id
        description: "Sector code."
        tests:
          - not_null
          - accepted_values:
              values: ["RES", "COM", "IND", "TRA", "OTH", "ALL"]
      - name: price_cents_per_kwh
        description: "Average electricity price in cents per kilowatthour."
      - name: revenue_millions_usd
        description: "Revenue from sales in million dollars."
      - name: sales_millions_kwh
        description: "Electricity sold in million kilowatthours."
      - name: customer_count
        description: "Number of ultimate customers."

What do these tests check?

  • not_null on period, state_id, and sector_id ensures that the three key identifier columns always have values. Null values here would mean broken joins downstream.
  • accepted_values on sector_id checks that only the six valid EIA sector codes appear. If the API ever sends a new or malformed sector code, this test catches it.
  • Save _staging__models.yml.

Seeing YAML parsing errors?

YAML is whitespace-sensitive. Make sure each indentation level uses exactly two spaces, not tabs. The tests: key should be indented under the column name, and each test item should be indented one more level with a dash.

help me fix a YAML indentation error in my dbt schema file

✔️ Awesome, I've got everything!

Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: stg_eia__retail_sales
    description: "Cleaned and typed EIA electricity retail sales data."
    columns:
      - name: period
        description: "Month of data in YYYY-MM format."
        tests:
          - not_null
      - name: state_id
        description: "Two-letter state code."
        tests:
          - not_null
      - name: sector_id
        description: "Sector code."
        tests:
          - not_null
          - accepted_values:
              values: ["RES", "COM", "IND", "TRA", "OTH", "ALL"]
      - name: price_cents_per_kwh
        description: "Average electricity price in cents per kilowatthour."
      - name: revenue_millions_usd
        description: "Revenue from sales in million dollars."
      - name: sales_millions_kwh
        description: "Electricity sold in million kilowatthours."
      - name: customer_count
        description: "Number of ultimate customers."

Now add tests and descriptions for your marts models.

  • Create a new file called _marts__models.yml inside your models/marts/ folder.
  • Copy the following YAML into the file:
version: 2

models:
  - name: state_electricity_summary
    description: "Annual electricity metrics aggregated by state and sector. Excludes the ALL sector rollup to avoid double-counting."
    columns:
      - name: state_id
        description: "Two-letter state code."
        tests:
          - not_null
      - name: year
        description: "Calendar year."
        tests:
          - not_null
      - name: avg_price_cents_per_kwh
        description: "Average electricity price for the year in cents per kWh."

  - name: residential_price_rankings
    description: "States ranked by average residential electricity price for the most recent year of data."
    columns:
      - name: state_id
        description: "Two-letter state code."
        tests:
          - not_null
          - unique
      - name: price_rank
        description: "Rank by residential price, 1 = most expensive."
        tests:
          - not_null

What do these marts tests check?

  • not_null on state_id and year in state_electricity_summary verifies that every aggregated row has its grouping keys intact.
  • unique on state_id in residential_price_rankings ensures each state appears exactly once. Duplicates would mean the ranking logic has a bug.
  • not_null on price_rank catches any state that failed to get a rank assigned by the window function.
  • Save _marts__models.yml.

Not sure about the file names?

The underscore prefix (like _staging__models.yml) is a dbt convention. It tells dbt that this YAML file contains configuration, not a model to compile. Make sure the file name starts with an underscore and uses the double-underscore separator.

help me with dbt YAML file naming conventions

✔️ Awesome, I've got everything!

Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: state_electricity_summary
    description: "Annual electricity metrics aggregated by state and sector. Excludes the ALL sector rollup to avoid double-counting."
    columns:
      - name: state_id
        description: "Two-letter state code."
        tests:
          - not_null
      - name: year
        description: "Calendar year."
        tests:
          - not_null
      - name: avg_price_cents_per_kwh
        description: "Average electricity price for the year in cents per kWh."

  - name: residential_price_rankings
    description: "States ranked by average residential electricity price for the most recent year of data."
    columns:
      - name: state_id
        description: "Two-letter state code."
        tests:
          - not_null
          - unique
      - name: price_rank
        description: "Rank by residential price, 1 = most expensive."
        tests:
          - not_null
Generate and browse your dbt documentation

Every description field you just added to your YAML files serves double duty. dbt uses these descriptions to auto-generate a browsable documentation website for your entire project. It maps out every model, source, column, and test in one place.

  • Generate the documentation artifacts by running this command from your dbt project directory:
dbt docs generate

You should see output ending with a line that says dbt has cataloged your project's resources.

  • Launch the documentation site in your browser by running:
dbt docs serve

What does dbt docs serve do?

This starts a local web server and opens your browser to the documentation site. You can explore your models, see column descriptions, view test definitions, and browse a visual lineage graph showing how your sources, staging, and marts models connect.

Your browser should open automatically to the dbt documentation site. You should see your project name eia_energy_pipeline in the left sidebar with your sources and models listed.

  • Click on stg_eia__retail_sales in the sidebar to see its column descriptions and tests.
  • Press Ctrl+C in your terminal to stop the docs server when you are done exploring.

Browser didn't open automatically?

Check your terminal output for a URL (usually http://localhost:8080). Copy and paste it into your browser manually. If the port is in use, dbt will try the next available port and show it in the output.

help me get the dbt docs site to open in my browser

Run dbt test and dbt build to verify the full pipeline

You have models and tests defined. Time to run them and confirm everything passes.

  • Execute all your schema tests by running:
dbt test

You should see each test listed with a PASS status. The output ends with a summary like Completed successfully and the total number of tests that passed.

What does dbt test do behind the scenes?

For each test you defined in YAML, dbt compiles a SQL query and runs it against Athena. A not_null test becomes a SELECT that counts null values in that column. If the count is zero, the test passes.

Seeing test failures?

If a not_null test fails, it means your raw data contains null values in that column. Check that your staging model's WHERE clause in stg_eia__retail_sales.sql filters out null rows correctly.

If an accepted_values test fails, the API returned a sector code that is not in your allowed list. Check the error output to see which unexpected value appeared.

help me debug failing dbt tests

Now run the full pipeline. The dbt build command runs your models AND tests together in dependency order. This is the single command that proves your entire pipeline works end-to-end.

  • Run the full pipeline end-to-end by running:
dbt build

You should see each model materialize followed by its tests, all with PASS status. The final summary shows the total count of models and tests that succeeded.

Why use dbt build instead of dbt run and dbt test separately?

dbt build is the production-grade command. It materializes each model, runs its tests immediately, and only proceeds to downstream models if all tests pass. This prevents bad data from cascading through your pipeline.

Build failed on a specific model?

Check the error message for the model name and error type. A common issue is an Athena timeout on large queries. Try running dbt build --select model_name to isolate the failing model and see its full error output.

help me debug a dbt build failure

Your ELT pipeline is now tested and documented. You have extraction, loading, transformation, testing, and documentation all working together. Next up is an optional challenge to push your pipeline even further.

Secret mission

Detect Price Spike Anomalies

Your pipeline extracts, loads, and transforms energy data. But can it detect when something unusual happens? Build an incremental dbt model that flags month-over-month electricity price spikes by state.

Before you go

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses S3 free tier storage, serverless Athena queries on a small dataset, and the free Glue Data Catalog tier. Ongoing costs are effectively $0 while your data stays within free tier limits.

Cost warning

S3 free tier (5 GB standard storage) expires after 12 months. After that, you will be charged for stored objects. Athena charges $5 per TB of data scanned per query, but your small dataset costs fractions of a cent per query.

If you are no longer using these resources, delete them to avoid any unexpected charges.

Resources you used:

  • An S3 bucket containing raw Parquet files and dbt-managed transformed tables.
  • An Athena database (eia_raw) with a CREATE EXTERNAL TABLE pointing to your S3 data.
  • A dbt-managed schema (eia_transformed) with staging and marts models materialized in Athena.
  • Glue Data Catalog entries for all databases and tables.
  • A local dbt project folder and Python extraction script on your machine.
  • A free EIA API key registered to your email.

Keep everything running

No action needed. Choose this if you are still actively building on your pipeline or plan to extend it soon.

  • Your S3 data persists at no cost within free tier limits (5 GB for 12 months).
  • Athena is serverless and only charges when you run queries. There are no idle costs.
  • Glue Data Catalog entries remain free for the first 1 million objects per month.
  • Your local dbt project and extraction script stay on your machine and cost nothing.

Pause - I'll come back to this later

Since Athena is serverless and S3 charges only for storage, there is nothing to "pause" in the traditional sense. Your resources sit idle at near-zero cost.

  • Athena incurs no idle costs. You are only billed when you run a query.
  • S3 storage stays within free tier as long as your total account usage is under 5 GB.
  • Your local dbt project and Python script remain on your machine, ready to pick up where you left off.
  • If your S3 free tier has expired, consider deleting the bucket to avoid storage charges while you are away.

Delete - I don't want to use this again

Remove all AWS resources and local project files to start completely fresh.

  • Delete your S3 bucket and all its contents by running this command in your terminal:
aws s3 rb s3://[[S3_BUCKET="your-eia-datalake-bucket-name"]] --force
  • Drop the raw data database in Athena. Open the Athena query editor in the AWS console and run:
DROP DATABASE eia_raw CASCADE;
  • Drop the dbt-managed transformed database in Athena:
DROP DATABASE eia_transformed CASCADE;
  • The Glue Data Catalog entries are removed automatically when you drop the databases.
  • Delete your local project folder from your Desktop to remove the dbt project and extraction script.
  • Your EIA API key remains active on your account. You can revoke it from the EIA website if you no longer need it.

Mission accomplished

Nice Work!

You just built a real-world ELT pipeline on AWS. You extracted live energy data from a government API, loaded it into a cloud data lake, and transformed it into analytical models using dbt and Amazon Athena.

You've learned how to:

  • Extract data from the EIA API v2 using Python and load it into a partitioned S3 data lake as Parquet files.
  • Build a dbt project with staging and marts layers that transform raw energy data into analytical models using serverless Athena queries.
  • Add data quality tests and auto-generated documentation to validate and document your entire pipeline end-to-end.
  • Secret Mission: Built an incremental model that detects month-over-month electricity price spike anomalies by state.

Ready to quiz yourself?