Build an ETL Pipeline with Python

Extract GitHub API data, transform it, and load it into SQLite.

Introduction

30 Second Summary

Every time you see a trending list or a popularity ranking online, there is a data pipeline behind the scenes pulling raw information, cleaning it up, and storing it somewhere queryable. This pattern is called ETL (Extract-Transform-Load), one of the most fundamental skills in data engineering.

In this project, you will build a Python ETL pipeline that pulls live trending repository data from the GitHub API. Your pipeline will clean and enrich the raw data, then load it into a local SQLite database you can query for insights.

What You'll Build

Run a single command and watch your pipeline pull live data from GitHub, clean it, compute popularity scores, and load everything into a database you can immediately query to discover which languages are trending and which repos are the most popular.

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

A working ETL pipeline that you run with a single command to extract, transform, and load live GitHub data into a SQLite database.
Analytical SQL queries that reveal the top repos by popularity, the most common programming languages, and average popularity scores across your dataset.
A printed data quality report that tracks how many records were extracted, how many had issues, and how many were successfully loaded.
Secret Mission: Add pagination to pull multiple pages of results and generate a summary report as a CSV export.

Are there any prerequisites?

You should be comfortable with basic Python concepts like variables, functions, loops, and dictionaries. Basic SQL knowledge (SELECT, WHERE, JOIN) is also helpful, but no cloud accounts or API keys are required.

Before We Start

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

Set Up Your Project and Extract Raw Data

Every data pipeline starts at the same place: getting data. Before you can clean, enrich, or analyze anything, you need a working connection to a data source and a script that reliably pulls raw data from it.

In this step, you will create your project folder, install two Python packages, and write an extraction function that pulls live trending repository data from the GitHub Search API.

In this step, get ready to:

Create the project folder and install Python packages.
Write an extraction function that calls the GitHub Search API.
Run the script and verify raw data prints to the terminal.

Create the project folder and install packages

Open the terminal in VS Code by pressing Cmd+` (macOS) or Ctrl+`.

Move to your Desktop by running this command:

cd ~/Desktop

Create your project folder and move into it by running these commands:

mkdir github-etl
cd github-etl

Create the three Python files your pipeline will use by running this command:

macOS

touch extract.py transform.py load.py

On Linux too

This same touch command works on Linux.

Windows

New-Item -ItemType "File" -Path "extract.py", "transform.py", "load.py"

Confirm the files exist by running:

ls

You should see extract.py, transform.py, and load.py in the output.

Open the github-etl folder in VS Code by clicking File in the top menu bar.
Click Open Folder.
Select the github-etl folder on your Desktop, then click Open.

Why open the folder?

Opening the folder in VS Code makes all three files visible in the Explorer sidebar. You can click any file name there to open it in the editor.

Now check that your Python version is compatible with the packages you are about to install.

Check your Python version by running this command in the terminal:

python --version

✔️ I see Python 3.10 or higher

Your Python version is compatible. Move on to the next instruction.

ⓧ I see an older version

The packages this project uses (requests==2.34.2 and pandas==2.3.3) require Python 3.10 or higher.

Download and install the latest Python from python.org/downloads.
On macOS, the system default may shadow your new install. After installing, use python3 in place of python (and pip3 in place of pip) throughout this project.
Close and reopen your terminal, then run the version check again to confirm the update.

ⓧ Command not found

On macOS, the official Python installer registers the python3 command instead of python.

Try running python3 --version instead.
If that shows 3.10 or higher, use python3 in place of python (and pip3 in place of pip) throughout this project.
If neither command is found, download and install Python from python.org/downloads. After installing, close and reopen your terminal, then run the version check again.

Install the two packages your pipeline needs by running this command:

pip install requests==2.34.2 pandas==2.3.3

What are these packages?

requests is a Python library for making HTTP calls to APIs. You will use it to fetch data from GitHub.

pandas is a data manipulation library that makes it easy to clean, filter, and transform tabular data. You will use it in the transform step.

You should see Successfully installed followed by the package names at the end of the output.

Seeing 'pip: command not found'?

If you are on macOS or Linux, try pip3 instead of pip. Use whichever version matches your python or python3 command.

help me fix a pip command not found error when installing requests and pandas

Write the extraction function

The GitHub Search Repositories API lets you search for public repos using filters like creation date, star count, and language. Your extraction function will call this API and pull back the raw JSON data.

Click on extract.py in the Explorer sidebar to open it in the editor.
Copy the following code into extract.py:

import requests


# Pull trending repos from GitHub's search API
def extract_repos():
    url = "https://api.github.com/search/repositories"
    # Search for repos created in the last 30 days, sorted by most stars
    params = {
        "q": "created:>2026-07-19",
        "sort": "stars",
        "order": "desc",
        "per_page": 30
    }

    # Send the GET request and check for errors
    response = requests.get(url, params=params)
    response.raise_for_status()

    # Parse the JSON and extract the list of repositories
    data = response.json()
    repos = data["items"]

    print(f"Extracted {len(repos)} repositories from GitHub API")
    return repos

What does this code do?

extract_repos() sends a GET request to the GitHub Search Repositories endpoint. The params dictionary tells the API to find repos created in the last 30 days, sorted by star count (most stars first), returning 30 results per page.

response.raise_for_status() checks for HTTP errors (like a 403 rate limit). If the request succeeds, the function parses the JSON response and returns the items list, which contains one dictionary per repository.

Adjust the date to today

The date 2026-07-19 in the query represents roughly 30 days ago. If you are following this project on a different date, change it to a date approximately 30 days before today in YYYY-MM-DD format.

Now add the code that runs the function when you execute the script from the terminal.

At the bottom of extract.py, leave a blank line after the function, then paste this code:

if __name__ == "__main__":
    repos = extract_repos()
    # Print the first 5 repos as a quick preview
    for repo in repos[:5]:
        print(f"  {repo['full_name']} - {repo['stargazers_count']} stars")

What does the main block do?

The if __name__ == "__main__" guard runs the code inside it only when you execute the file directly (python extract.py). When another file imports extract_repos() later, this block is skipped. It prints the first 5 repos so you can confirm the API call works.

Save extract.py by pressing Cmd+S (macOS) or Ctrl+S (Windows).

✔️ Awesome, I've got everything!

Great. Make sure you have saved extract.py before moving on.

ⓧ I'd like to double check the full code

Here is the complete extract.py file. Compare it against what you have:

import requests


def extract_repos():
    url = "https://api.github.com/search/repositories"
    params = {
        "q": "created:>2026-07-19",
        "sort": "stars",
        "order": "desc",
        "per_page": 30
    }

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

    data = response.json()
    repos = data["items"]

    print(f"Extracted {len(repos)} repositories from GitHub API")
    return repos


if __name__ == "__main__":
    repos = extract_repos()
    for repo in repos[:5]:
        print(f"  {repo['full_name']} - {repo['stargazers_count']} stars")

Run the extraction script

Before you run this, what do you think the API will return? How many results do you expect, and what kind of repos will show up at the top?

Run your extraction script by running this command in the terminal:

python extract.py

You should see output showing 30 extracted repositories, followed by a preview of the top 5 repos with their names and star counts.

What should I see?

The first line prints Extracted 30 repositories from GitHub API. Below it, you will see 5 lines showing repo names and star counts. The exact repos depend on what is trending on GitHub right now.

Seeing an error?

If you see ModuleNotFoundError: No module named 'requests', the pip install command may have installed to a different Python version than the one running your script. Try python3 extract.py instead, or re-install with pip3 install requests==2.34.2.

If you see a 403 error, you may have hit the GitHub API rate limit (10 search requests per minute for unauthenticated requests). Wait a minute and try again.

help me debug an error when running my extract.py script that calls the GitHub Search API

Your extraction layer is working. You have live data flowing from the GitHub API into your script. Next up, you will clean and transform that raw data so it is ready to load into a database.

Transform the Raw Data

In Step 1, you pulled live repository data from the GitHub API. Your extraction function works, and you can see 30 repos printed to your terminal.

The problem is that raw API data is messy. Descriptions can be null, languages can be missing, and there is no computed metric to rank repositories meaningfully. Loading this directly into a database would give you unreliable data that is hard to query.

In this step, get ready to:

Build a transform function that converts raw JSON into a clean pandas DataFrame with only the columns you need.
Clean the data by handling null values, filling missing fields, and computing a popularity score.
Run the transform and verify the output with a printed data quality report.

Build the transform function

The GitHub API returns over 30 fields per repository, but your pipeline only needs eight of them. The first job of the transform step is to narrow down this raw data into a focused DataFrame.

In VS Code, open transform.py and paste the following code:

import pandas as pd
from extract import extract_repos


def transform_repos(raw_repos):
    # Convert raw repo list to a DataFrame
    df = pd.DataFrame(raw_repos)

    # Keep only the columns we need
    columns_to_keep = [
        "id", "full_name", "description", "language",
        "stargazers_count", "forks_count", "created_at", "html_url"
    ]
    df = df[columns_to_keep]

What does this code do?

The import at the top pulls in your extract_repos function from Step 1. This is how the transform step gets its raw data.

pd.DataFrame(raw_repos) converts the list of dictionaries from the API into a tabular structure that pandas can manipulate. The columns_to_keep list filters out the 20+ fields you do not need, keeping only the eight that matter for analysis.

Save transform.py.

Clean the data and add a popularity score

Your DataFrame has the right columns, but some rows have missing descriptions or null languages. You also need a way to rank repos that goes beyond raw star counts.

In transform.py, add the following code directly below the df = df[columns_to_keep] line, inside the transform_repos function:

    # Track data quality metrics before cleaning
    total_extracted = len(df)
    null_descriptions = df["description"].isna().sum()

    # Drop repos with no description
    df = df.dropna(subset=["description"])

    # Fill missing languages with a placeholder
    df["language"] = df["language"].fillna("Unknown")

    # Compute a combined popularity metric
    df["popularity_score"] = df["stargazers_count"] + df["forks_count"] * 2

    # Print a summary of what the transform did
    print(f"\n--- Transform Report ---")
    print(f"Records extracted:     {total_extracted}")
    print(f"Null descriptions:     {null_descriptions}")
    print(f"Records after cleaning: {len(df)}")

    return df

What does this cleaning code do?

dropna(subset=["description"]) removes any row where the description is null. Repos without descriptions are not useful for analysis.
fillna("Unknown") replaces missing language values with the string "Unknown" instead of dropping the row. A repo without a detected language is still worth keeping.
popularity_score combines stars and forks into a single ranking metric. Forks are weighted double because forking signals deeper engagement than starring.

Now add the entry point that calls both extract_repos() and transform_repos() so you can run the file directly.

At the bottom of transform.py, below the function, paste this code:

if __name__ == "__main__":
    raw = extract_repos()
    cleaned = transform_repos(raw)
    print(f"\nTop 5 by popularity score:")
    print(cleaned.nlargest(5, "popularity_score")[["full_name", "language", "popularity_score"]].to_string(index=False))

What does this main block do?

The if __name__ == "__main__" guard lets you run transform.py directly as a script. It calls the extract function to get raw data, feeds it into the transform function, and then prints the top 5 repos ranked by your new popularity_score.

Save transform.py.

Run the transform and verify the output

Your transform function is complete. Before you run it, think about what you expect to see. The extract step pulled 30 repos. Will all 30 survive the cleaning step, or will some get dropped?

Run the transform script by running this command in your terminal:

python transform.py

You should see three things printed to the terminal. First, the extraction message showing 30 repositories fetched. Second, a Transform Report showing the total records extracted, how many had null descriptions, and how many remain after cleaning. Third, a table of the top 5 repos ranked by popularity_score.

What should I see?

The Transform Report will show a number for "Null descriptions." If this number is greater than 0, that means your cleaning step successfully caught and removed rows that would have caused problems in the database.

The top 5 table should show full_name, language, and popularity_score columns. The scores should be higher than the raw star counts because forks are weighted double in the formula.

Seeing an error?

ModuleNotFoundError: No module named 'pandas' means pandas is not installed. Run pip install pandas==2.3.3 and try again.
ImportError: cannot import name 'extract_repos' means your terminal is not inside the github-etl folder, or extract.py was not saved. Check that both files are in the same folder.
If you see a KeyError on a column name, double-check that the strings in columns_to_keep match the API field names exactly, including underscores.

Help me debug my transform.py errors

✔️ Awesome, I've got everything!

Great. Double check you have saved transform.py.

ⓧ I'd like to double check the full code

Scroll up and confirm each section is present in your transform.py file, in this order:

The two import lines at the top (pandas and extract_repos).
The transform_repos function: DataFrame creation, columns_to_keep selection, cleaning operations (dropna, fillna), popularity_score calculation, Transform Report print statements, and return df.
The if __name__ == "__main__" block calling extract, transform, and printing the top 5.

Your raw data is now clean, enriched, and ready for storage. Next up, you will load it into a SQLite database so you can query it with SQL.

Load Data into SQLite and Hit a Problem

Your transform step produces clean, enriched data. Right now that data only exists in memory, so the moment your script ends, everything disappears.

You need to persist it somewhere queryable. In this step, you'll load your cleaned DataFrame into a SQLite database and write your first query against real data. Then you'll discover a problem that every production pipeline has to solve.

In this step, get ready to:

Write a load function that saves your DataFrame to a SQLite database.
Query the database to verify the data loaded correctly.
Discover what happens when you run the pipeline a second time.

Write the load function in load.py

SQLite is a database engine that stores everything in a single file. Python includes the sqlite3 module in its standard library, so there is nothing extra to install.

Open load.py in VS Code and paste the following code:

import sqlite3
import pandas as pd
from extract import extract_repos
from transform import transform_repos


def load_repos(df, db_name="github_trending.db"):
    # Connect to SQLite database (creates the file if it doesn't exist)
    conn = sqlite3.connect(db_name)

    # Write the cleaned DataFrame to the repositories table
    df.to_sql("repositories", conn, if_exists="append", index=False)

    print(f"\n--- Load Report ---")
    print(f"Loaded {len(df)} repos into {db_name}")

    conn.close()


if __name__ == "__main__":
    raw = extract_repos()
    cleaned = transform_repos(raw)
    load_repos(cleaned)

What does this code do?

sqlite3.connect("github_trending.db") opens a connection to a database file. If the file does not exist yet, SQLite creates it automatically.

df.to_sql("repositories", conn, if_exists="append", index=False) uses pandas to write every row from the DataFrame into a table called repositories. The if_exists="append" flag tells pandas to add rows to the table if it already exists.

Save load.py.

Add a verification query

Loading data into a database is only useful if you can confirm it arrived correctly. A quick SQL query after the insert proves the load worked.

In load.py, find the line that says conn.close().
Add the following code directly above conn.close():

    # Query top 5 repos to verify the data loaded correctly
    cursor = conn.cursor()
    cursor.execute("""
        SELECT full_name, language, popularity_score
        FROM repositories
        ORDER BY popularity_score DESC
        LIMIT 5
    """)
    print(f"\nTop 5 repos in database:")
    for row in cursor.fetchall():
        print(f"  {row[0]} ({row[1]}) - score: {row[2]}")

What does this query do?

This runs a SQL SELECT query that grabs the top 5 repositories ranked by popularity_score. Seeing real repo names and scores in the output proves the data made it from the API through your transform and into the database.

Save load.py.
Run the load script by running this command in your terminal:

python load.py

You should see three things in your terminal: the extraction report from the API call, the transform report showing cleaned records, and finally the load report with the top 5 repos listed by popularity score.

Seeing an error?

If you see ModuleNotFoundError: No module named 'extract', make sure your terminal is inside the github-etl folder where all three Python files live.
If the API call fails with a connection error, check your internet connection and try again.
If you see an indentation error, make sure the verification query block is indented at the same level as the other code inside load_repos().

Help me debug an error when running load.py in my ETL pipeline.

✔️ Awesome, I've got everything!

Great. Make sure you have saved load.py before moving on.

ⓧ I'd like to double check the full code

Scroll up and confirm these two code blocks are present in your load.py, in this order:

The imports, load_repos() function with to_sql(), and the if __name__ == "__main__" block.
The verification query block (starting with cursor = conn.cursor()) placed directly above conn.close().

Run it again and discover the problem

Your pipeline loaded data successfully. In the real world, pipelines run on a schedule. What happens if this pipeline runs twice on the same data?

Before you run the next command, take a guess: do you think running load.py a second time will skip the existing data, overwrite it, or create duplicates?

Run the load script again by running this command:

python load.py

The output looks identical to the first run. It says "Loaded X repos" again and the top 5 query looks the same. Everything seems fine, right?

Check how many total rows are in your database by running this command:

python -c "import sqlite3; print('Total rows:', sqlite3.connect('github_trending.db').execute('SELECT COUNT(*) FROM repositories').fetchone()[0])"

You should see roughly double the number of repos you loaded the first time. If you loaded 28 repos initially, you now have around 56 rows in the database. Every record was inserted a second time.

Why did this happen?

The to_sql() method with if_exists="append" blindly appends rows every time it runs. It has no concept of "this row already exists." Each run dumps the full dataset again.

This is a real problem in data engineering. A pipeline that creates duplicates on re-run is not production-ready. The technical term for a pipeline that handles re-runs safely is idempotent. Running it once or running it ten times should produce the same result in the database.

Your data made it into a real database, and you surfaced a critical flaw. Next up, you'll fix the duplicate problem and wire the full extract-transform-load flow into a single reusable script.

Fix Duplicates and Build the Full Pipeline

In the last step, you loaded cleaned data into SQLite and confirmed it worked. But you also spotted a problem: running the load a second time leads to duplicate rows or errors.

A production pipeline needs to be idempotent, meaning you can run it multiple times and get the same result. Your load.py already handles this with INSERT OR IGNORE. Now you need a single orchestrator script that ties extract, transform, and load together into one command.

In this step, get ready to:

Understand how INSERT OR IGNORE prevents duplicate data in your database.
Create pipeline.py to orchestrate the full ETL flow with error handling.
Run the pipeline twice and confirm idempotent loading works.

Understand how your load function handles duplicates

Your load.py already contains the fix for the duplicate problem. The key is the combination of two things: a PRIMARY KEY constraint on the id column and the INSERT OR IGNORE statement.

Open load.py in VS Code and find the CREATE TABLE statement.
Notice the id INTEGER PRIMARY KEY line. This tells SQLite that every row must have a unique id.
Now find the INSERT OR IGNORE statement below it.

What does INSERT OR IGNORE do?

When SQLite tries to insert a row and the id already exists in the table, the PRIMARY KEY constraint would normally cause an error. INSERT OR IGNORE tells SQLite to silently skip that row instead of crashing.

This makes your load function idempotent. Run it once and you get your data. Run it ten more times and nothing changes. That is exactly how production pipelines should behave.

The code also tracks how many rows were new versus skipped using cursor.rowcount. When a row is inserted, rowcount is greater than 0. When it is ignored, rowcount is 0.

Confirm your load.py returns both counts at the end of the function by finding this line:

    return new_count, skip_count

Your pipeline orchestrator will use these return values to print a final summary report.

Create the pipeline orchestrator

Right now, running the ETL pipeline means executing extract.py, transform.py, and load.py separately. A proper pipeline has one entry point that calls all three stages in order.

Create a new file called pipeline.py in your github-etl folder by running:

touch pipeline.py

Confirm the file exists by running:

ls

You should see pipeline.py alongside extract.py, transform.py, load.py, and github_trending.db in the output.

Open pipeline.py in the VS Code editor and add the imports and the first half of the run_pipeline() function by copying this code:

import sys
from extract import extract_repos
from transform import transform_repos
from load import load_repos


def run_pipeline():
    # Print a banner for the pipeline
    print("=" * 50)
    print("  GitHub Trending Repos ETL Pipeline")
    print("=" * 50)

    try:
        # Step 1: Extract raw data from GitHub
        print("\n[1/3] Extracting data from GitHub API...")
        raw_repos = extract_repos()

        # Step 2: Transform and clean the data
        print("\n[2/3] Transforming data...")
        cleaned_df = transform_repos(raw_repos)

        # Step 3: Load cleaned data into SQLite
        print("\n[3/3] Loading data into SQLite...")
        new_count, skip_count = load_repos(cleaned_df)

What does this code do?

The imports bring in each function from the modules you already built: extract_repos(), transform_repos(), and load_repos().
run_pipeline() calls them in the correct order: extract first, then transform, then load.
The try: block wraps the entire flow so that if the API call fails or any step throws an error, the pipeline catches it gracefully instead of crashing with a traceback.
new_count, skip_count = load_repos(cleaned_df) captures the return values from your load function so the summary report can show them.

Now add the summary report, error handling, and main guard below the code you just pasted in pipeline.py:

        # Print final summary
        print("\n" + "=" * 50)
        print("  Pipeline Complete!")
        print(f"  {new_count} new records loaded")
        print(f"  {skip_count} duplicates skipped")
        print("=" * 50)

    except Exception as e:
        # Stop the pipeline if something goes wrong
        print(f"\nPipeline failed: {e}")
        sys.exit(1)


if __name__ == "__main__":
    run_pipeline()

What does this code do?

The summary block prints a final report showing how many records were loaded and how many duplicates were skipped. This gives you a clear confirmation every time you run the pipeline.
except Exception as e: catches any error that happens during the pipeline. Instead of a confusing traceback, you get a clean "Pipeline failed" message.
sys.exit(1) exits with a non-zero code, which signals to any outer system (like a scheduler) that the run failed.
if __name__ == "__main__": ensures the pipeline only runs when you execute the file directly, not when it is imported by another script.

Save pipeline.py.

✔️ Awesome, I've got everything!

Great. Make sure you have saved pipeline.py before moving on.

ⓧ I'd like to double check the full code

Scroll up and confirm your pipeline.py contains both chunks in this order:

Chunk 1: The import lines, the run_pipeline() function definition with the banner, and the try: block calling extract_repos(), transform_repos(), and load_repos().
Chunk 2: The summary print statements, the except Exception as e: block, and the if __name__ == "__main__": guard at the bottom.

Make sure the indentation inside the try: block uses 8 spaces (4 for the function, 4 for the try). Save the file.

Seeing a red underline on the imports?

Make sure extract.py, transform.py, and load.py are all in the same github-etl folder as pipeline.py. Python can only import files from the same directory.
Check that each import matches the exact function name: extract_repos, transform_repos, and load_repos.

Help me fix my pipeline.py imports

Run the pipeline twice and verify no duplicates

This is the moment of truth. You will run your full pipeline, then run it again to prove the idempotent loading actually works.

First, delete your existing database so you start clean. Run:

rm github_trending.db

Run your full pipeline for the first time by running:

python pipeline.py

You should see the banner, then the extract, transform, and load reports printing in sequence. The final summary should show all records as newly inserted and zero duplicates skipped.

Seeing "Pipeline failed" or a traceback?

If the error mentions a connection or timeout, the GitHub API might be temporarily unreachable. Wait a minute and try again.
If you see an ImportError, make sure your terminal is inside the github-etl folder. Run pwd to check.
If you see a KeyError, the API response shape may have changed. Check that extract.py accesses data["items"] correctly.

Help me debug my pipeline.py run

Before you run the pipeline a second time, what do you think will happen? Will you see the same number of new records, or something different?

Run the pipeline again by running:

python pipeline.py

This time, the Load Report should show 0 new repos inserted and all records counted as duplicates skipped. The final summary confirms: 0 new records loaded and a non-zero number of duplicates skipped.

Why is this the correct behavior?

The pipeline extracted the same repositories from GitHub (they have not changed in the last few seconds). When it tried to insert them, SQLite saw that every id already existed and silently skipped each one. No crash, no duplicates, no data corruption.

Verify there are no duplicate IDs in your database by running this query:

python -c "import sqlite3; c = sqlite3.connect('github_trending.db'); dupes = c.execute('SELECT id, COUNT(*) FROM repositories GROUP BY id HAVING COUNT(*) > 1').fetchall(); print('Duplicates found:', dupes if dupes else 'None'); c.close()"

You should see Duplicates found: None. That confirms your pipeline is idempotent.

Seeing duplicates in the output?

Check that your CREATE TABLE statement in load.py has id INTEGER PRIMARY KEY as the first column. Without the PRIMARY KEY constraint, INSERT OR IGNORE has nothing to check against.
If you changed the table schema after the first run, delete github_trending.db and run python pipeline.py again to recreate the table with the correct schema.

Help me fix duplicate rows in my database

Your ETL pipeline is now production-ready. It extracts, transforms, loads, and handles re-runs gracefully. Next up, you will query the database to pull out real insights from the data your pipeline collected.

Query Your Data for Insights

Your pipeline extracts, transforms, and loads data into github_trending.db without duplicates. But data sitting in a database does nothing until you ask it questions.

In this step, you will write SQL queries that surface real insights from your pipeline output. These are the kinds of analytical queries that data engineering roles expect you to write on the job.

In this step, get ready to:

Create queries.py to connect to your database and run analytical SQL queries.
Write queries to find the top repos, language distribution, and average popularity by language.
Print formatted reports that prove your pipeline produces reliable, queryable data.

Create the queries file and find your top repos

You will start by creating a new file that connects to your database and runs the first query: finding the top 10 repositories ranked by popularity score.

Create a new file called queries.py in your github-etl folder by running this command in your terminal:

touch queries.py

Open queries.py in VS Code by clicking on it in the Explorer sidebar.
Add the imports, database connection, and your first analytical query by pasting the following code:

import sqlite3
import pandas as pd


def run_queries(db_name="github_trending.db"):
    # Connect to the SQLite database
    conn = sqlite3.connect(db_name)

    # Print report header
    print("=" * 50)
    print("  GitHub Trending Repos - Analysis")
    print("=" * 50)

    # Query 1: Top 10 repos ranked by popularity score
    print("\n--- Top 10 Repositories by Popularity Score ---")
    top_repos = pd.read_sql_query("""
        SELECT full_name, language, stargazers_count, forks_count, popularity_score
        FROM repositories
        ORDER BY popularity_score DESC
        LIMIT 10
    """, conn)
    print(top_repos.to_string(index=False))

    conn.close()


if __name__ == "__main__":
    run_queries()

What does this code do?

run_queries() connects to github_trending.db using sqlite3, the same library your load step used.
pd.read_sql_query() runs a raw SQL query and returns the results directly as a pandas DataFrame. This gives you formatted table output for free.
to_string(index=False) prints the DataFrame as a clean, aligned table without row numbers.

Save queries.py.
Run your first query by running this command in the terminal:

python queries.py

You should see a formatted table showing the top 10 repositories ordered by their popularity_score, with columns for name, language, stars, forks, and score.

Seeing an error?

If you see an error about the table not existing, make sure you ran pipeline.py at least once in a previous step to populate the database.
Check that github_trending.db exists in your github-etl folder. Run ls to confirm.
Why is my queries.py not finding the database?

Add the language distribution queries

The top 10 list shows individual repos, but it does not tell you which programming languages dominate the trending charts. Two more queries will answer that.

In queries.py, find the line that says conn.close().
Add the following two queries directly above conn.close():

    # Query 2: How many repos per language
    print("\n--- Repository Count by Language ---")
    lang_count = pd.read_sql_query("""
        SELECT language, COUNT(*) as repo_count
        FROM repositories
        GROUP BY language
        ORDER BY repo_count DESC
    """, conn)
    print(lang_count.to_string(index=False))

    # Query 3: Average popularity per language (only languages with 2+ repos)
    print("\n--- Average Popularity Score by Language ---")
    avg_pop = pd.read_sql_query("""
        SELECT language, ROUND(AVG(popularity_score), 1) as avg_popularity,
               COUNT(*) as repo_count
        FROM repositories
        GROUP BY language
        HAVING repo_count >= 2
        ORDER BY avg_popularity DESC
    """, conn)
    print(avg_pop.to_string(index=False))

What do these queries do?

Query 2 uses GROUP BY language to count how many trending repos belong to each language. This reveals which languages dominate the trending charts.
Query 3 calculates the average popularity_score for each language. The HAVING repo_count >= 2 clause filters out languages with only one repo, since a single data point is not a meaningful average.

Save queries.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved queries.py before you run it.

ⓧ I'd like to double check the full code

Your queries.py is longer than 30 lines, so scroll through and confirm each section is present, in this order:

Imports: import sqlite3 and import pandas as pd at the top.
Function definition: def run_queries(db_name="github_trending.db") with a connection and report header.
Query 1: Top 10 repos by popularity_score with ORDER BY popularity_score DESC LIMIT 10.
Query 2: Repo count grouped by language with GROUP BY language.
Query 3: Average popularity per language with HAVING repo_count >= 2.
Cleanup: conn.close() at the end of the function, plus the if __name__ == "__main__": guard at the bottom.

Before you run the full analysis, take a guess: which programming language do you think will have the most trending repos?

Run the full analysis by running this command:

python queries.py

You should see three formatted reports printed to the terminal: the top 10 repos by popularity, a language distribution count, and average popularity scores grouped by language.

Do the results make sense?

Look at the language distribution. Languages like Python, TypeScript, and Rust tend to dominate trending charts because they have large, active open-source communities.

Check the average popularity column. A language with fewer repos but a high average score means its trending projects are exceptionally popular. A language with many repos but a lower average means it has broad activity but less standout hits.

Seeing empty results or errors?

If Query 3 returns no rows, your database may not have any language with 2 or more repos. Run pipeline.py again to repopulate the data.
Check that the new queries are indented inside the run_queries() function. Python uses indentation to determine what belongs to a function.
Make sure the new queries are above conn.close(). Querying a closed connection will raise an error.
Why are my SQL queries in queries.py returning errors or empty results?

Your ETL pipeline is complete from end to end. You can extract live data, clean it, load it into a real database, and query it for insights. Next up, a secret mission that takes your pipeline further.

Secret mission

Add Pagination and CSV Export

Your pipeline pulls 30 repos from one page of the GitHub API. Real pipelines rarely stop at one page. Can you add pagination to pull hundreds of records, handle rate limits, and export a summary report as a CSV?

Clean Up Your Resources

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

Resources you used:

github-etl project folder (contains extract.py, transform.py, load.py, pipeline.py, queries.py).
github_trending.db SQLite database file (inside the project folder).
summary_report.csv export file (inside the project folder).
Pip packages: requests and pandas.

Keep everything running

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

Your github-etl folder and all its files stay on your Desktop.
The requests and pandas packages remain installed in your Python environment.
The github_trending.db database stays queryable. You can re-run python queries.py any time.

Pause - I'll come back to this later

There are no running processes to shut down. Your files and database are already saved to disk and ready whenever you come back.

Close the VS Code window for the github-etl project.
When you are ready to resume, reopen the github-etl folder in VS Code and pick up where you left off.

Delete - I don't want to use this again

Remove all project files, the database, and the installed packages to start fresh.

Delete the project folder by running the command for your operating system.

macOS

rm -rf ~/Desktop/github-etl

Windows

rmdir /s /q %USERPROFILE%\Desktop\github-etl

This removes all Python files, the github_trending.db database, and the summary_report.csv file.

Uninstall the pip packages by running:

pip uninstall requests pandas -y

The -y flag skips the confirmation prompt so both packages are removed immediately.

Nice Work!

You just built a complete ETL pipeline in Python that pulls live data from the GitHub API, cleans it, loads it into a database, and answers real questions about trending repositories.

You've learned how to:

Build a Python ETL pipeline that extracts live data from the GitHub Search API, transforms it with pandas, and loads it into a SQLite database.
Clean raw API data by handling null values, filling missing fields, computing a popularity score, and making the pipeline idempotent so re-runs skip duplicates instead of crashing.
Write analytical SQL queries against your pipeline output to surface insights like top repositories by popularity, language distribution, and average scores per language.
Secret Mission: Added pagination to scale data extraction across multiple API pages with rate limiting, and exported a CSV summary report for stakeholders.

Ready to quiz yourself?

Build an ETL Pipeline with Python

Introduction

30 Second Summary

What You'll Build

Are there any prerequisites?

Before We Start

Set Up Your Project and Extract Raw Data

In this step, get ready to:

Create the project folder and install packages

macOS

On Linux too

Windows

Why open the folder?

✔️ I see Python 3.10 or higher

ⓧ I see an older version

ⓧ Command not found

What are these packages?

Seeing 'pip: command not found'?

Write the extraction function

What does this code do?

Adjust the date to today

What does the __main__ block do?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Run the extraction script

What should I see?

Seeing an error?

Transform the Raw Data

In this step, get ready to:

Build the transform function

What does this code do?

Clean the data and add a popularity score

What does this cleaning code do?

What does this main block do?

Run the transform and verify the output

What should I see?

Seeing an error?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Load Data into SQLite and Hit a Problem

In this step, get ready to:

Write the load function in load.py

What does this code do?

Add a verification query

What does this query do?

Seeing an error?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Run it again and discover the problem

Why did this happen?

Fix Duplicates and Build the Full Pipeline

In this step, get ready to:

Understand how your load function handles duplicates

What does INSERT OR IGNORE do?

Create the pipeline orchestrator

What does this code do?

What does this code do?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Seeing a red underline on the imports?

Run the pipeline twice and verify no duplicates

Seeing "Pipeline failed" or a traceback?

Why is this the correct behavior?

Seeing duplicates in the output?

Query Your Data for Insights

In this step, get ready to:

Create the queries file and find your top repos

What does this code do?

Seeing an error?

Add the language distribution queries

What do these queries do?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Do the results make sense?

Seeing empty results or errors?

Secret mission

Add Pagination and CSV Export

Clean Up Your Resources

Clean Up Your Resources

Keep everything running

What does the main block do?