School Performance Analytics Pipeline

Build an ETL pipeline and dashboard for Colorado school data.

Introduction

30 Second Summary

Every year, the Colorado Department of Education rates schools based on student test scores, academic growth, and post-graduation outcomes. Behind those ratings is a rich dataset full of patterns waiting to be uncovered.

In this project, you will build a complete data analytics pipeline that extracts real Colorado school performance data, transforms it into a structured data warehouse, and displays interactive insights through a dashboard. The finished project lives on GitHub as a polished portfolio piece.

What You'll Build

A Python ETL pipeline that processes Colorado school performance data into a DuckDB analytical database and serves insights through an interactive Streamlit dashboard, all documented and pushed to GitHub.

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

  • A working ETL pipeline that extracts CDE performance data, models it in a star schema, and loads it into a DuckDB analytical database.
  • SQL queries that surface accountability insights like plan type distribution, indicator comparisons, and schools at risk of rating downgrades.
  • An interactive Streamlit dashboard with filters, KPI metric cards, and charts comparing how schools score across three core performance indicators (Academic Achievement, Academic Growth, and Postsecondary & Workforce Readiness).
  • Secret Mission: Add a Growth vs. Achievement scatter plot that reveals schools making significant academic progress despite low absolute test scores.

Are there any prerequisites?

Basic Python knowledge (variables, functions, loops) is all you need. Familiarity with SQL is helpful but not required.

Before We Start

You're about to build a data analytics pipeline that processes real school performance data from the Colorado Department of Education. The pipeline will ingest XLSX data, transform it into a star schema, and serve insights through an interactive dashboard.

Before diving into the data, you need a working Python environment with the right packages installed. In this step, you'll set up a PyCharm project, verify your tools, and install everything the pipeline needs.

In this step, get ready to:
  • Verify Python 3.11+ and Git are available on your machine.
  • Create a PyCharm project with a virtual environment.
  • Install project dependencies and set up the folder structure.
Open PyCharm and create a new project

PyCharm manages your Python code, virtual environment, and terminal in one window. You'll create a new project that lives on your Desktop.

  • Press Cmd+Space to open Spotlight search.
  • Type PyCharm and press Enter to open it.
  • On the Welcome screen, click New Project.
  • In the Location field, clear the default path and type:
~/Desktop/school-performance-pipeline
  • Under Python Interpreter, make sure Project venv is selected.
  • Confirm the Base interpreter dropdown shows a Python 3.11 or higher version.
  • Uncheck Create a main.py welcome script if it is checked.
  • Click Create.

What just happened?

PyCharm created a new folder called school-performance-pipeline on your Desktop. Inside it, PyCharm also created a venv folder containing your project's virtual environment.

A virtual environment isolates this project's packages from the rest of your system. Every package you install stays inside this project only.

Verify Python 3.11+ and Git

This project requires Python 3.11 or higher because pandas 3.0.3 only supports Python 3.11+. You also need Git to push your finished project to GitHub.

  • Open PyCharm's built-in terminal by clicking View in the top menu bar.
  • Select Tool Windows, then Terminal.
  • Check your Python version by running this command:
python3 --version

✔️ I see version 3.11 or higher

Your Python version is compatible. Move on to the Git check below.

ⓧ I see an older version

Your system's default Python is too old for this project. You need to install Python 3.11+ and then update PyCharm's interpreter.

The quickest path is to download the official macOS installer from python.org.

  • Visit python.org/downloads/macos and download the macOS 64-bit universal2 installer for Python 3.11 or newer.
  • Open the downloaded .pkg file and follow the installation prompts.
  • Back in PyCharm, click PyCharm in the menu bar.
  • Select Settings, then expand Project: school-performance-pipeline, then click Python Interpreter.
  • Click Add Interpreter, then select Add Local Interpreter.
  • Choose Virtualenv and select the newly installed Python 3.11+ as the Base interpreter.
  • Click OK and re-run python3 --version in the terminal to confirm it now shows 3.11 or higher.

ⓧ Command not found

Python is not installed on your system. Download it from the official website.

  • Visit python.org/downloads/macos and download the macOS 64-bit universal2 installer for Python 3.11 or newer.
  • Open the downloaded .pkg file and follow the installation prompts.
  • Close and reopen PyCharm's terminal, then re-run python3 --version to confirm it shows 3.11 or higher.

If the terminal still does not find Python after installation, update PyCharm's interpreter in Settings as described in the tab above.

  • Check that Git is available by running:
git --version

✔️ I see a version number

Git is ready. You're all set to continue.

ⓧ Command not found

Git is not installed. On macOS, the simplest way to get it is through Xcode Command Line Tools.

  • Install Xcode Command Line Tools by running:
xcode-select --install
  • A dialog will appear asking you to confirm the installation. Click Install and wait for it to complete.
  • After the installation finishes, run git --version again to confirm you see a version number.
Install dependencies and create the folder structure

Your pipeline uses five Python packages. DuckDB is the analytical database. Streamlit builds the dashboard. Plotly renders interactive charts.

  • Install all project dependencies by running this command in PyCharm's terminal:
pip install pandas==3.0.3 openpyxl==3.1.5 duckdb==1.5.2 streamlit==1.57.0 plotly>=5.0.0

What do these packages do?

  • pandas reads and transforms tabular data (DataFrames).
  • openpyxl is the engine pandas uses to read .xlsx Excel files.
  • duckdb is an analytical database that runs locally and integrates natively with pandas.
  • streamlit turns Python scripts into interactive web dashboards.
  • plotly creates interactive charts like bar charts and scatter plots.

Seeing install errors?

Make sure your terminal prompt starts with (venv). If it does not, your virtual environment is not active. Close the terminal tab and reopen it from View then Tool Windows then Terminal. PyCharm auto-activates the venv for new terminal sessions.

If you see permission errors, confirm you are running pip inside the project's venv and not system-wide.

Help me troubleshoot pip install errors in my PyCharm venv.

Now create the folder structure your project needs. The data/ folder will hold raw files and the DuckDB database. The src/ folder will hold your Python scripts.

  • Create the project directories and files by running these commands:
mkdir data src
touch README.md .gitignore
  • Confirm the folders and files exist by running:
ls
  • You should see data, src, README.md, .gitignore, and venv in the output.

Next, create requirements.txt so anyone cloning your repo can install the same packages.

  • In PyCharm's Project pane on the left, right-click the project root (school-performance-pipeline).
  • Select New, then File.
  • Name it requirements.txt and press Enter.
  • Paste the following content into the file:
pandas==3.0.3
openpyxl==3.1.5
duckdb==1.5.2
streamlit==1.57.0
plotly>=5.0.0

Why pin exact versions?

Pinning versions (==) ensures anyone running your project gets the same behavior. The >= on plotly allows any version 5.0 or higher, which is fine because Plotly's API is stable across minor releases.

  • Save requirements.txt with Cmd+S.

✔️ Awesome, I've got everything!

Great. Double check you've saved requirements.txt and your data/ and src/ folders are visible in PyCharm's Project pane.

ⓧ I'd like to double check the full code

Here is what requirements.txt should contain:

pandas==3.0.3
openpyxl==3.1.5
duckdb==1.5.2
streamlit==1.57.0
plotly>=5.0.0

Your project folder on the Desktop should look like this:

school-performance-pipeline/
├── data/
├── src/
├── venv/
├── .gitignore
├── README.md
└── requirements.txt

Missing folders in PyCharm's Project pane?

If you do not see data/ or src/ in the sidebar, right-click the project root and select Reload from Disk. PyCharm sometimes needs a moment to detect folders created via the terminal.

Help me fix my PyCharm project structure.

Your environment is ready. Next up, you'll download real CDE school performance data and start exploring what's inside.

Set Up Your Python Environment in PyCharm

Before writing any pipeline code, you need a properly configured Python environment. A virtual environment isolates this project's packages from the rest of your system so nothing conflicts.

In this step, you will verify your tools, create a PyCharm project with a virtual environment, install the five packages the pipeline needs, and set up the folder structure that separates raw data from source code.

In this step, get ready to:
  • Verify Python 3.11+ and Git are available in PyCharm's terminal.
  • Create a PyCharm project with a virtual environment and install all dependencies.
  • Build the project folder structure for data, source code, and documentation.
Verify Python and Git in PyCharm's terminal

PyCharm has a built-in terminal that runs commands inside your project. You will use it to confirm that Python and Git are ready before installing packages.

  • Open PyCharm by pressing Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type PyCharm and press Enter to open it.
  • Open PyCharm's terminal by pressing Option+F12 (macOS) or Alt+F12 (Windows).
  • Check your Python version by running this command:
python3 --version
  • You should see a version number of 3.11 or higher (e.g. Python 3.11.9).
  • Confirm Git is available by running:
git --version
  • You should see a version number like git version 2.39.5 or similar.

Why Python 3.11+?

This project pins pandas==3.0.3, which requires Python 3.11 or higher. DuckDB and Streamlit both require 3.10+, so 3.11 satisfies every dependency.

Don't see version 3.11 or higher?

If you see a version lower than 3.11, download the latest Python from python.org and install it. After installing, close and reopen PyCharm's terminal, then run python3 --version again.

On macOS, the system Python may resolve to an older version even after installing a newer one. Try running python3.11 --version or python3.12 --version to check if the newer binary is available under a versioned name.

If git --version shows "command not found", run xcode-select --install in Terminal to install the Xcode Command Line Tools, which includes Git.

Help me troubleshoot my Python or Git installation.

Create a PyCharm project and install dependencies

A virtual environment gives your project its own isolated set of packages. PyCharm creates one automatically when you start a new project.

  • In PyCharm, click File in the menu bar.
  • Click New Project.
  • In the New Project dialog, set the Location to your Desktop and name the project school-performance-pipeline.
  • Make sure Pure Python is selected as the project type.
  • Keep the default Project venv option selected. PyCharm will create a virtual environment in the project folder automatically.
  • Click Create.

What is a virtual environment?

A virtual environment is an isolated Python installation inside your project folder. Packages you install here stay separate from other projects on your machine. This prevents version conflicts between projects.

  • Once the project opens, open the terminal again with Option+F12 (macOS) or Alt+F12 (Windows).
  • You should see (venv) at the start of your terminal prompt. This confirms the virtual environment is active.
  • Install all project dependencies by running this command:
pip install pandas==3.0.3 openpyxl==3.1.5 duckdb==1.5.2 streamlit==1.57.0 plotly>=5.0.0

What are these packages?

  • pandas reads and transforms the raw Excel data.
  • openpyxl is the engine pandas uses to open .xlsx files.
  • duckdb is the analytical database that stores your star schema.
  • streamlit builds the interactive dashboard from Python.
  • plotly creates the grouped bar charts and scatter plots.
  • Wait for the installation to finish. You should see a "Successfully installed" message listing all five packages.

Seeing installation errors?

If you see a permissions error, make sure the (venv) prefix appears in your terminal prompt. If it does not, close the terminal tab, reopen it with Option+F12, and try the install command again.

If a specific package fails to download, check your internet connection and retry the command. PyCharm's terminal uses the same network your system does.

Help me fix my pip install errors.

Create the project folder structure

A clean folder structure keeps raw data separate from source code and documentation. This project uses two directories and three root-level files.

  • In PyCharm's Project pane on the left, right-click the project root (school-performance-pipeline).
  • Select New, then Directory.
  • Type data and press Enter.
  • Right-click the project root again, select New, then Directory.
  • Type src and press Enter.

What goes where?

  • data/ holds the raw CDE Excel file you will download in Step 2, plus the DuckDB database file the ETL pipeline creates.
  • src/ holds your Python scripts: etl_pipeline.py and dashboard.py.
  • Now create the root-level files. Right-click the project root, select New, then File.
  • Type requirements.txt and press Enter.
  • Paste the following into requirements.txt:
pandas==3.0.3
openpyxl==3.1.5
duckdb==1.5.2
streamlit==1.57.0
plotly>=5.0.0

What does this file do?

A requirements.txt file lists every package your project needs with exact versions pinned. Anyone cloning your repo can run pip install -r requirements.txt to recreate the same environment.

  • Save requirements.txt with Cmd+S (macOS) or Ctrl+S (Windows).
  • Right-click the project root again, select New, then File.
  • Type README.md and press Enter. Leave this file empty for now.
  • Right-click the project root one more time, select New, then File.
  • Type .gitignore and press Enter. Leave this file empty for now too. You will fill it in during Step 6.
  • Verify your folder structure in PyCharm's Project pane. You should see:
school-performance-pipeline/
├── data/
├── src/
├── .gitignore
├── README.md
└── requirements.txt

Don't see all the files?

If .gitignore is hidden, click the gear icon at the top of the Project pane and make sure Show Hidden Files is checked. Files starting with a dot are hidden by default on macOS.

Help me find my project files in PyCharm.

✔️ Awesome, I've got everything!

Great. Double check you have saved requirements.txt and that both data/ and src/ directories appear in the Project pane.

ⓧ I'd like to double check the full code

Here is the complete requirements.txt file. The other files (README.md, .gitignore) are empty at this point.

pandas==3.0.3
openpyxl==3.1.5
duckdb==1.5.2
streamlit==1.57.0
plotly>=5.0.0

Your environment is ready. Next up, you will download real Colorado school performance data and start exploring it with pandas.

Extract and Explore the CDE Performance Data

Your Python environment is ready. Now you need real data to feed into your pipeline.

The Colorado Department of Education publishes School Performance Framework data every year. This dataset breaks down each school's rating into three core performance indicators: Academic Achievement, Academic Growth, and Postsecondary & Workforce Readiness. In this step, you will download that file and explore its structure so you know exactly what your ETL pipeline will process.

In this step, get ready to:
  • Download the CDE 2025 Rating Calculation Data file.
  • Load the XLSX into a pandas DataFrame and explore its columns.
  • Map the CDE plan type thresholds to total framework points.
Download the CDE performance data file

The CDE publishes multiple data files each year. You need the one that contains indicator-level sub-scores, not just summary ratings.

  • Open your browser and navigate to the CDE data files page:
  • https://www.cde.state.co.us/schoolview/datafiles
  • Find and download the file called CDE 2025 District and School Rating Calculation Data (XLS).
  • Save the downloaded file into your project's data/ folder.
  • Rename the file to cde_2025_rating_calculation_data.xlsx so the pipeline can find it with a consistent filename.

Why this specific file?

The CDE publishes several files including "Preliminary School Ratings" and "Preliminary District Ratings." Those only contain summary plan type assignments.

The "Rating Calculation Data" file contains the indicator-level sub-scores (Achievement points earned, Growth points earned, PWR points earned) that let you analyze each indicator separately. This is what makes the Growth vs. Achievement scatter plot possible later.

  • Confirm the file exists by checking PyCharm's Project pane on the left. You should see cde_2025_rating_calculation_data.xlsx listed inside the data/ folder.

Don't see the file in PyCharm?

Make sure you saved the file directly into the data/ folder inside your project directory. If the download landed in your Downloads folder, drag it into the data/ folder using PyCharm's Project pane.

Need help finding the right file on the CDE website?

Load and explore the data in Python

Before building a full pipeline, you should explore the raw data interactively. PyCharm's Python Console lets you run commands line by line to inspect what you are working with.

  • Open PyCharm's Python Console by clicking Tools in the top menu bar, then selecting Python Console.
  • Load the XLSX file into a pandas DataFrame by running this code in the console:
import pandas as pd

# Read the CDE 2025 District and School Rating Calculation Data
raw_df = pd.read_excel(
    "data/cde_2025_rating_calculation_data.xlsx",
    engine="openpyxl"
)

print(f"Loaded {len(raw_df)} rows and {raw_df.shape[1]} columns")

What does this code do?

pd.read_excel() reads the XLSX file into a DataFrame, which is a table-like structure in pandas. The engine="openpyxl" parameter tells pandas to use the openpyxl library for reading the Excel format.

The print statement confirms how many rows (schools) and columns (data fields) you loaded. This is your first sanity check that the file read correctly.

  • You should see output showing the number of rows and columns loaded. The exact numbers depend on the current year's data file.
  • Explore the column names by running:
# List all column names in the dataset
print(raw_df.columns.tolist())
  • Preview the first few rows of data by running:
# Preview the first 5 rows to see the data structure
print(raw_df.head())

What columns should I look for?

The Rating Calculation Data file typically contains columns for school identification (school code, school name, district code, district name, school level) and indicator-level scores.

Look for columns related to these three CDE indicators:

  • Academic Achievement points earned and eligible (based on CMAS and PSAT scores).
  • Academic Growth points earned and eligible (based on Colorado Growth Model percentiles).
  • Postsecondary & Workforce Readiness (PWR) points earned and eligible (graduation rates, SAT scores, matriculation). This indicator applies to high schools only.

Seeing a FileNotFoundError?

Make sure the file is named exactly cde_2025_rating_calculation_data.xlsx and sits inside the data/ folder at your project root. Also confirm that PyCharm's working directory is set to your project root (check the bottom status bar).

Help me troubleshoot my file loading error in PyCharm's Python Console.

Examine the plan type thresholds

Each school earns points across the three indicators. The percentage of total points earned determines which plan type the CDE assigns.

  • Check how the plan type ratings are distributed in the data by running:
# Count schools per plan type rating
print(raw_df["plan_type_rating"].value_counts())

What are the CDE plan type thresholds?

The CDE classifies schools into four plan types based on the percentage of total framework points earned:

  • Performance: >= 60% of points earned.
  • Improvement: 47% to 59.9% of points earned.
  • Priority Improvement: 34% to 46.9% of points earned.
  • Turnaround: less than 34% of points earned.

These thresholds determine whether a school is thriving or flagged for intervention. Your pipeline will calculate these percentages and assign the classification for each school.

  • Verify that the total framework points columns exist by running:
# Check the total framework points columns
print(raw_df[["total_framework_points", "total_framework_points_eligible"]].describe())

What do these numbers tell me?

The total_framework_points column shows how many points each school earned across all indicators. The total_framework_points_eligible column shows the maximum possible points.

Dividing earned by eligible gives you the percentage. A school with 65 points earned out of 100 eligible would be at 65%, placing it in the Performance plan type.

  • You should see summary statistics including count, mean, min, and max for both columns. This confirms the data is numeric and ready for transformation.

Seeing a KeyError on column names?

The exact column names in your downloaded file may differ slightly from what is shown here. CDE updates column naming across years. Run raw_df.columns.tolist() again and look for columns containing words like "total," "framework," "points," or "plan type." You will map these to standardized names in the next step.

Help me find the right column names in my CDE data file.

You now have the raw CDE data loaded and you understand its structure. Next up, you will clean and transform this data into a star schema that makes it easy to query and visualize.

Transform the Data and Build a Star Schema

In the previous step, you explored the raw CDE data and identified the columns for each performance indicator. Now it's time to turn that messy spreadsheet into a structured analytical database.

A star schema separates your data into fact tables (the measurements) and dimension tables (the context). This design makes analytical queries fast and intuitive. You'll build a complete ETL pipeline that extracts, transforms, and loads the CDE data into DuckDB.

In this step, get ready to:
  • Clean and standardize the raw CDE data.
  • Design dimension and fact tables in a star schema.
  • Load everything into a DuckDB analytical database.
Extract and clean the raw CDE data

The CDE spreadsheet has column names with inconsistent casing and spaces. Before you can build a proper data model, you need to standardize everything into a clean, predictable format.

  • In PyCharm's Project pane, right-click the src/ folder.
  • Select New, then Python File.
  • Name the file etl_pipeline and press Enter.
  • Add the following code to start your ETL pipeline:
import pandas as pd
import duckdb
from pathlib import Path

# Ensure data directory exists
Path("data").mkdir(exist_ok=True)

# === EXTRACT ===
# Read the CDE 2025 District and School Rating Calculation Data
# Download from: https://www.cde.state.co.us/schoolview/datafiles
# This file contains indicator-level sub-scores (Achievement, Growth, PWR)
raw_df = pd.read_excel(
    "data/cde_2025_rating_calculation_data.xlsx",
    engine="openpyxl"
)

print(f"Loaded {len(raw_df)} rows and {raw_df.shape[1]} columns")
print(raw_df.columns.tolist())
print(raw_df.head())

# === TRANSFORM ===
# Standardize column names (lowercase, replace spaces with underscores)
raw_df.columns = [col.strip().lower().replace(" ", "_") for col in raw_df.columns]

What does this code do?

This is the Extract phase of your ETL pipeline. It reads the CDE XLSX file into a pandas DataFrame using the openpyxl engine (required for modern Excel files).

The column standardization line converts names like "School Name" to school_name. This makes every column name predictable for the rest of the pipeline.

  • Save src/etl_pipeline.py.
  • You won't run it yet. The next chunk adds the star schema tables this script needs to produce a working database.
Build the dimension tables

Dimension tables hold the descriptive context for your data. They answer "who" and "when" questions. Your star schema needs three: one for schools, one for districts, and one for the academic year.

  • In src/etl_pipeline.py, add the following code below the column standardization line:
# Create Dim_School
dim_school = raw_df[["school_code", "school_name", "school_level"]].drop_duplicates()
dim_school = dim_school.reset_index(drop=True)
dim_school.index.name = "school_key"
dim_school = dim_school.reset_index()

# Create Dim_District
dim_district = raw_df[["district_code", "district_name"]].drop_duplicates()
dim_district = dim_district.reset_index(drop=True)
dim_district.index.name = "district_key"
dim_district = dim_district.reset_index()

# Create Dim_Academic_Year
dim_year = pd.DataFrame({
    "year_key": [1],
    "academic_year": ["2024-2025"],
    "framework_version": ["2025 Preliminary"]
})

What does this code do?

  • dim_school extracts unique schools with their school_level (E/M/H). This lets you filter by elementary, middle, or high school later.
  • dim_district extracts unique districts so you can group schools by their district.
  • dim_year is a static table with the academic year and framework version. This makes your schema ready for multi-year analysis in the future.
  • Save src/etl_pipeline.py.
  • You won't see a change yet. The next chunk builds the fact table that references these dimensions.
Build the fact table with indicator calculations

The fact table holds the measurements. For CDE accountability, that means each school's points earned across the three core indicators: Academic Achievement, Academic Growth, and Postsecondary & Workforce Readiness (PWR).

  • In src/etl_pipeline.py, add the following code below the dim_year DataFrame:
# Create Fact_School_Performance with indicator-level breakdown
# CDE Plan Type Thresholds:
#   Performance: >= 60% of points earned
#   Improvement: 47% to 59.9%
#   Priority Improvement: 34% to 46.9%
#   Turnaround: < 34%

fact_performance = raw_df[[
    "school_code",
    "district_code",
    "plan_type_rating",
    "achievement_points_earned",
    "achievement_points_eligible",
    "growth_points_earned",
    "growth_points_eligible",
    "pwr_points_earned",
    "pwr_points_eligible",
    "total_framework_points",
    "total_framework_points_eligible",
]].copy()

# Calculate percentage earned for each indicator
fact_performance["achievement_pct"] = (
    fact_performance["achievement_points_earned"]
    / fact_performance["achievement_points_eligible"]
    * 100
).round(2)

fact_performance["growth_pct"] = (
    fact_performance["growth_points_earned"]
    / fact_performance["growth_points_eligible"]
    * 100
).round(2)

fact_performance["pwr_pct"] = (
    fact_performance["pwr_points_earned"]
    / fact_performance["pwr_points_eligible"]
    * 100
).round(2)

fact_performance["pct_points_earned"] = (
    fact_performance["total_framework_points"]
    / fact_performance["total_framework_points_eligible"]
    * 100
).round(2)

fact_performance["year_key"] = 1

What does this code do?

This builds the core of your data warehouse. Each row represents one school's performance across the CDE framework.

  • achievement_pct calculates what percentage of available Achievement points the school earned (CMAS/PSAT scores).
  • growth_pct calculates the Growth indicator percentage (Colorado Growth Model percentiles).
  • pwr_pct calculates Postsecondary & Workforce Readiness percentage. Elementary and middle schools will have NaN here because PWR only applies to high schools.
  • pct_points_earned is the overall percentage that determines the plan type (Performance >= 60%, Improvement 47-59.9%, Priority Improvement 34-46.9%, Turnaround < 34%).
  • Save src/etl_pipeline.py.
  • You won't see a change yet. The final chunk loads these DataFrames into DuckDB.
Load the tables into DuckDB

DuckDB can create tables directly from pandas DataFrame variables in the same Python session. This eliminates the need for an ORM or manual INSERT statements.

  • In src/etl_pipeline.py, add the following code at the bottom of the file:
# === LOAD ===
# Connect to DuckDB and create tables
con = duckdb.connect("data/school_performance.duckdb")

con.execute("DROP TABLE IF EXISTS dim_school")
con.execute("DROP TABLE IF EXISTS dim_district")
con.execute("DROP TABLE IF EXISTS dim_academic_year")
con.execute("DROP TABLE IF EXISTS fact_school_performance")

con.execute("CREATE TABLE dim_school AS SELECT * FROM dim_school")
con.execute("CREATE TABLE dim_district AS SELECT * FROM dim_district")
con.execute("CREATE TABLE dim_academic_year AS SELECT * FROM dim_year")
con.execute("CREATE TABLE fact_school_performance AS SELECT * FROM fact_performance")

# Verify load
print("\n=== Data Warehouse Summary ===")
print(f"dim_school: {con.execute('SELECT COUNT(*) FROM dim_school').fetchone()[0]} rows")
print(f"dim_district: {con.execute('SELECT COUNT(*) FROM dim_district').fetchone()[0]} rows")
print(f"fact_school_performance: {con.execute('SELECT COUNT(*) FROM fact_school_performance').fetchone()[0]} rows")

con.close()
print("\nETL pipeline complete! Database saved to data/school_performance.duckdb")

What does this code do?

  • duckdb.connect() creates (or opens) a database file at data/school_performance.duckdb.
  • The DROP TABLE IF EXISTS lines make the script re-runnable. You can execute it multiple times without errors from duplicate tables.
  • CREATE TABLE ... AS SELECT * FROM dim_school is DuckDB's native pandas integration. It reads the local Python variable dim_school directly and creates a database table from it.
  • The verify section prints row counts so you can confirm data loaded correctly.

Why DuckDB instead of SQLite?

DuckDB is purpose-built for analytical (OLAP) workloads. It handles columnar queries, aggregations, and joins far more efficiently than SQLite for this type of project.

It also has native pandas DataFrame integration, which means you can create tables directly from DataFrames without SQLAlchemy or manual INSERT statements. This is the modern choice for local analytical databases.

  • Save src/etl_pipeline.py.
  • Run the ETL pipeline in PyCharm's terminal by running:
python src/etl_pipeline.py
  • You should see the column names printed, a preview of the first few rows, and then the Data Warehouse Summary showing row counts for each table.

Seeing a KeyError or column not found?

  • The exact column names in the CDE file may differ from the ones used here. Check the printed column list in your terminal output and update the column references in the script to match your file.
  • Make sure the XLSX file is saved as data/cde_2025_rating_calculation_data.xlsx. The path is case-sensitive.
  • If you see a ModuleNotFoundError, confirm your venv is active in PyCharm's terminal. You should see (venv) at the start of your prompt.

Still stuck? Ask for help.

✔️ Awesome, I've got everything!

Great. Double check you've saved src/etl_pipeline.py and that you see row counts printed in your terminal.

ⓧ I'd like to double check the full code

Here is the complete src/etl_pipeline.py file at this point:

import pandas as pd
import duckdb
from pathlib import Path

Path("data").mkdir(exist_ok=True)

# === EXTRACT ===
raw_df = pd.read_excel(
    "data/cde_2025_rating_calculation_data.xlsx",
    engine="openpyxl"
)

print(f"Loaded {len(raw_df)} rows and {raw_df.shape[1]} columns")
print(raw_df.columns.tolist())
print(raw_df.head())

# === TRANSFORM ===
raw_df.columns = [col.strip().lower().replace(" ", "_") for col in raw_df.columns]

dim_school = raw_df[["school_code", "school_name", "school_level"]].drop_duplicates()
dim_school = dim_school.reset_index(drop=True)
dim_school.index.name = "school_key"
dim_school = dim_school.reset_index()

dim_district = raw_df[["district_code", "district_name"]].drop_duplicates()
dim_district = dim_district.reset_index(drop=True)
dim_district.index.name = "district_key"
dim_district = dim_district.reset_index()

dim_year = pd.DataFrame({
    "year_key": [1],
    "academic_year": ["2024-2025"],
    "framework_version": ["2025 Preliminary"]
})

fact_performance = raw_df[[
    "school_code",
    "district_code",
    "plan_type_rating",
    "achievement_points_earned",
    "achievement_points_eligible",
    "growth_points_earned",
    "growth_points_eligible",
    "pwr_points_earned",
    "pwr_points_eligible",
    "total_framework_points",
    "total_framework_points_eligible",
]].copy()

fact_performance["achievement_pct"] = (
    fact_performance["achievement_points_earned"]
    / fact_performance["achievement_points_eligible"]
    * 100
).round(2)

fact_performance["growth_pct"] = (
    fact_performance["growth_points_earned"]
    / fact_performance["growth_points_eligible"]
    * 100
).round(2)

fact_performance["pwr_pct"] = (
    fact_performance["pwr_points_earned"]
    / fact_performance["pwr_points_eligible"]
    * 100
).round(2)

fact_performance["pct_points_earned"] = (
    fact_performance["total_framework_points"]
    / fact_performance["total_framework_points_eligible"]
    * 100
).round(2)

fact_performance["year_key"] = 1

# === LOAD ===
con = duckdb.connect("data/school_performance.duckdb")

con.execute("DROP TABLE IF EXISTS dim_school")
con.execute("DROP TABLE IF EXISTS dim_district")
con.execute("DROP TABLE IF EXISTS dim_academic_year")
con.execute("DROP TABLE IF EXISTS fact_school_performance")

con.execute("CREATE TABLE dim_school AS SELECT * FROM dim_school")
con.execute("CREATE TABLE dim_district AS SELECT * FROM dim_district")
con.execute("CREATE TABLE dim_academic_year AS SELECT * FROM dim_year")
con.execute("CREATE TABLE fact_school_performance AS SELECT * FROM fact_performance")

print("\n=== Data Warehouse Summary ===")
print(f"dim_school: {con.execute('SELECT COUNT(*) FROM dim_school').fetchone()[0]} rows")
print(f"dim_district: {con.execute('SELECT COUNT(*) FROM dim_district').fetchone()[0]} rows")
print(f"fact_school_performance: {con.execute('SELECT COUNT(*) FROM fact_school_performance').fetchone()[0]} rows")

con.close()
print("\nETL pipeline complete! Database saved to data/school_performance.duckdb")

Your star schema is loaded and ready for queries. Next up, you'll write SQL against this data warehouse to surface accountability insights and identify schools near plan type boundaries.

Query Your Data Warehouse

Your star schema is loaded and ready. Data sitting in tables is useful for storage, but the real value comes from asking questions of it.

In this step, you'll write SQL queries against your DuckDB warehouse to answer real accountability questions. You'll surface which plan types dominate, how indicators compare, and which schools are at risk of dropping a rating tier.

In this step, get ready to:
  • Write aggregate SQL queries to compare CDE indicator averages across plan types.
  • Identify at-risk schools near plan type boundaries using JOIN queries.
  • Export query results to pandas DataFrames for visualization in the next step.
Query plan type distribution with indicator averages

The CDE assigns every school a plan type based on what percentage of total framework points they earned. Grouping schools by plan type and averaging each indicator score reveals whether Achievement, Growth, or PWR drives the overall rating.

  • In src/etl_pipeline.py, find the line that says con.close() near the bottom of the file.
  • Add the following query above that line to calculate plan type distribution with indicator averages:
# Query plan type distribution with average indicator scores
result = con.execute("""
    SELECT
        plan_type_rating,
        COUNT(*) as school_count,
        ROUND(AVG(achievement_pct), 1) as avg_achievement,
        ROUND(AVG(growth_pct), 1) as avg_growth,
        ROUND(AVG(pwr_pct), 1) as avg_pwr,
        ROUND(AVG(pct_points_earned), 1) as avg_overall
    FROM fact_school_performance
    GROUP BY plan_type_rating
    ORDER BY avg_overall DESC
""").fetchdf()
print(result)

What does this query do?

This SQL groups every school by its plan_type_rating (Performance, Improvement, Priority Improvement, or Turnaround) and calculates the average percentage of points earned for each CDE indicator.

  • COUNT(*) tells you how many schools fall into each plan type.
  • AVG(achievement_pct), AVG(growth_pct), and AVG(pwr_pct) reveal which indicator is strongest or weakest within each plan type.
  • .fetchdf() returns the query result as a pandas DataFrame, ready for visualization later.
  • Save src/etl_pipeline.py.
  • Run the script in PyCharm's terminal by pressing the green Run button or using the terminal. You should see a table printed with columns for plan_type_rating, school_count, avg_achievement, avg_growth, avg_pwr, and avg_overall.

Seeing an empty table or NaN values?

  • If the table is empty, your ETL pipeline from Step 3 may not have loaded data correctly. Re-run the full script from the top to rebuild the database.
  • NaN values in avg_pwr are expected for some plan types. Elementary and middle schools don't have PWR data, so their averages may show as null.

Why is my DuckDB query returning empty results?

Why does this matter for accountability?

This query answers a critical policy question: do Turnaround schools score low across all indicators, or is one specific indicator (like Achievement) dragging them down while Growth remains strong?

If Growth scores are comparable across plan types, it suggests that raw achievement scores (driven by poverty and demographics) are the primary differentiator. That is a key equity insight.

Identify at-risk schools near plan type boundaries

Schools hovering near the 47% threshold are at risk of dropping from Improvement to Priority Improvement status. Finding these schools requires joining the fact table with dimension tables to get school and district names alongside the scores.

  • In src/etl_pipeline.py, add the following query directly below print(result) and above con.close():
# Identify schools near the Priority Improvement boundary (45-47%)
at_risk = con.execute("""
    SELECT s.school_name, d.district_name, f.pct_points_earned, f.plan_type_rating
    FROM fact_school_performance f
    JOIN dim_school s ON f.school_code = s.school_code
    JOIN dim_district d ON f.district_code = d.district_code
    WHERE f.pct_points_earned BETWEEN 45 AND 47
    ORDER BY f.pct_points_earned ASC
    LIMIT 10
""").fetchdf()
print(at_risk)

What does this query do?

  • JOIN dim_school s ON f.school_code = s.school_code links the fact table to the school dimension so you can see school names instead of raw codes.
  • JOIN dim_district d ON f.district_code = d.district_code brings in district names for geographic context.
  • WHERE f.pct_points_earned BETWEEN 45 AND 47 filters to schools just below the 47% Improvement threshold. These schools are one or two percentage points away from dropping to Priority Improvement.
  • .fetchdf() exports the results as a pandas DataFrame stored in the at_risk variable, ready for your dashboard in the next step.
  • Save src/etl_pipeline.py.
  • Run the script again. Below the plan type distribution table, you should see a second table listing school names, district names, their overall percentage, and plan type for schools scoring between 45% and 47%.

Getting a column not found error?

  • Check that your JOIN conditions use the correct column names. The fact table uses school_code and district_code as foreign keys matching the dimension tables.
  • Make sure your new query is placed above con.close(). Queries run after the connection is closed will fail.

Help me debug my DuckDB JOIN query

Why JOINs matter in a star schema

The star schema separates facts (numeric measurements) from dimensions (descriptive context). The fact table stores school_code and district_code as compact keys. JOINs reconnect those keys to human-readable names when you need them for reporting.

This pattern keeps the fact table lean for fast aggregations while dimension tables store the descriptive detail. DuckDB handles these joins efficiently because it is optimized for analytical workloads.

✔️ Awesome, I've got everything!

Great. Double check you've saved src/etl_pipeline.py and that both query tables printed successfully in your terminal.

ⓧ I'd like to double check the full code

Here is the complete src/etl_pipeline.py file including both new queries:

import pandas as pd
import duckdb
from pathlib import Path

Path("data").mkdir(exist_ok=True)

# === EXTRACT ===
raw_df = pd.read_excel(
    "data/cde_2025_rating_calculation_data.xlsx",
    engine="openpyxl"
)

print(f"Loaded {len(raw_df)} rows and {raw_df.shape[1]} columns")
print(raw_df.columns.tolist())
print(raw_df.head())

# === TRANSFORM ===
raw_df.columns = [col.strip().lower().replace(" ", "_") for col in raw_df.columns]

dim_school = raw_df[["school_code", "school_name", "school_level"]].drop_duplicates()
dim_school = dim_school.reset_index(drop=True)
dim_school.index.name = "school_key"
dim_school = dim_school.reset_index()

dim_district = raw_df[["district_code", "district_name"]].drop_duplicates()
dim_district = dim_district.reset_index(drop=True)
dim_district.index.name = "district_key"
dim_district = dim_district.reset_index()

dim_year = pd.DataFrame({
    "year_key": [1],
    "academic_year": ["2024-2025"],
    "framework_version": ["2025 Preliminary"]
})

fact_performance = raw_df[[
    "school_code",
    "district_code",
    "plan_type_rating",
    "achievement_points_earned",
    "achievement_points_eligible",
    "growth_points_earned",
    "growth_points_eligible",
    "pwr_points_earned",
    "pwr_points_eligible",
    "total_framework_points",
    "total_framework_points_eligible",
]].copy()

fact_performance["achievement_pct"] = (
    fact_performance["achievement_points_earned"]
    / fact_performance["achievement_points_eligible"]
    * 100
).round(2)

fact_performance["growth_pct"] = (
    fact_performance["growth_points_earned"]
    / fact_performance["growth_points_eligible"]
    * 100
).round(2)

fact_performance["pwr_pct"] = (
    fact_performance["pwr_points_earned"]
    / fact_performance["pwr_points_eligible"]
    * 100
).round(2)

fact_performance["pct_points_earned"] = (
    fact_performance["total_framework_points"]
    / fact_performance["total_framework_points_eligible"]
    * 100
).round(2)

fact_performance["year_key"] = 1

# === LOAD ===
con = duckdb.connect("data/school_performance.duckdb")

con.execute("DROP TABLE IF EXISTS dim_school")
con.execute("DROP TABLE IF EXISTS dim_district")
con.execute("DROP TABLE IF EXISTS dim_academic_year")
con.execute("DROP TABLE IF EXISTS fact_school_performance")

con.execute("CREATE TABLE dim_school AS SELECT * FROM dim_school")
con.execute("CREATE TABLE dim_district AS SELECT * FROM dim_district")
con.execute("CREATE TABLE dim_academic_year AS SELECT * FROM dim_year")
con.execute("CREATE TABLE fact_school_performance AS SELECT * FROM fact_performance")

print("\n=== Data Warehouse Summary ===")
print(f"dim_school: {con.execute('SELECT COUNT(*) FROM dim_school').fetchone()[0]} rows")
print(f"dim_district: {con.execute('SELECT COUNT(*) FROM dim_district').fetchone()[0]} rows")
print(f"fact_school_performance: {con.execute('SELECT COUNT(*) FROM fact_school_performance').fetchone()[0]} rows")

result = con.execute("""
    SELECT
        plan_type_rating,
        COUNT(*) as school_count,
        ROUND(AVG(achievement_pct), 1) as avg_achievement,
        ROUND(AVG(growth_pct), 1) as avg_growth,
        ROUND(AVG(pwr_pct), 1) as avg_pwr,
        ROUND(AVG(pct_points_earned), 1) as avg_overall
    FROM fact_school_performance
    GROUP BY plan_type_rating
    ORDER BY avg_overall DESC
""").fetchdf()
print(result)

at_risk = con.execute("""
    SELECT s.school_name, d.district_name, f.pct_points_earned, f.plan_type_rating
    FROM fact_school_performance f
    JOIN dim_school s ON f.school_code = s.school_code
    JOIN dim_district d ON f.district_code = d.district_code
    WHERE f.pct_points_earned BETWEEN 45 AND 47
    ORDER BY f.pct_points_earned ASC
    LIMIT 10
""").fetchdf()
print(at_risk)

con.close()
print("\nETL pipeline complete! Database saved to data/school_performance.duckdb")

Your data warehouse is answering real policy questions. Next up, you'll turn these query results into an interactive Streamlit dashboard where anyone can explore the data visually.

Build the Streamlit Dashboard

Your data warehouse is loaded with indicator-level performance data and your SQL queries surface real accountability insights. Now you need a way for someone to explore that data without writing SQL.

In this step, you will build an interactive dashboard using Streamlit. The dashboard connects directly to your DuckDB database and lets users filter by district, plan type, and school level to see KPI metrics, charts, and a detailed school table.

In this step, get ready to:
  • Create a Streamlit app that connects to your DuckDB database and displays the CDE framework header.
  • Add interactive sidebar filters and KPI metric cards for each performance indicator.
  • Build a plan type distribution chart, a grouped indicator comparison chart, and a filterable school details table.
Create the Streamlit app with a database connection

Streamlit turns a plain Python script into a web app. Every time you save the file, Streamlit detects the change and reruns the script so you see updates instantly.

  • In PyCharm's Project pane, right-click the src/ folder.
  • Select New then Python File.
  • Name the file dashboard and press Enter.
  • Set up the page configuration and CDE framework header by pasting this code into src/dashboard.py:
import streamlit as st
import duckdb
import pandas as pd
import plotly.express as px

st.set_page_config(page_title="CO School Performance Dashboard", layout="wide")
st.title("Colorado School Performance Framework Dashboard")
st.caption("Data source: Colorado Department of Education, 2025 Preliminary Ratings")
st.markdown("""
**CDE Performance Indicators:** Academic Achievement (CMAS/PSAT scores) |
Academic Growth (Colorado Growth Model) | Postsecondary & Workforce Readiness (graduation, SAT, matriculation)

**Plan Type Thresholds:** Performance >= 60% | Improvement 47-59.9% | Priority Improvement 34-46.9% | Turnaround < 34%
""")

What does this code do?

  • st.set_page_config sets the browser tab title and uses the full page width for your charts.
  • st.title, st.caption, and st.markdown render the dashboard header with the CDE framework reference (indicators and plan type thresholds).
  • The imports bring in Plotly Express for the grouped bar chart you will add later.
  • Add the database connection below the markdown block by pasting this code:
# Connect to DuckDB
@st.cache_resource
def get_connection():
    return duckdb.connect("data/school_performance.duckdb", read_only=True)

con = get_connection()

Why use @st.cache_resource?

Streamlit reruns your entire script from top to bottom every time a user interacts with a widget. Without caching, a new database connection would open on every single click.

The @st.cache_resource decorator tells Streamlit to create the connection once and reuse it across reruns. The read_only=True flag prevents the dashboard from accidentally modifying your data warehouse.

  • Save src/dashboard.py.
  • Launch the dashboard by running this command in PyCharm's terminal:
streamlit run src/dashboard.py
  • Your browser opens to http://localhost:8501. You should see the dashboard title, caption, and the CDE framework reference text.
  • In the top-right corner of the app, click Always rerun so the dashboard auto-updates every time you save.

Don't see the dashboard?

  • Make sure your virtual environment is active in the terminal (you should see (venv) in the prompt).
  • Check that you are running the command from your project root folder, not from inside src/.
  • If the port is already in use, stop any previous Streamlit process with Ctrl+C and rerun.

Why isn't my Streamlit dashboard loading in the browser?

Add sidebar filters and KPI metric cards

Filters let users narrow the data to specific districts, plan types, or school levels without editing SQL. Each filter selection dynamically rebuilds the queries that power the KPI cards and charts.

  • Add the three sidebar filter widgets below con = get_connection() by pasting this code:
# === SIDEBAR FILTERS ===
st.sidebar.header("Filters")

# District filter
districts = con.execute(
    "SELECT DISTINCT district_name FROM dim_district ORDER BY district_name"
).fetchdf()["district_name"].tolist()

selected_districts = st.sidebar.multiselect(
    "Select District(s)",
    options=districts,
    default=[]
)

# Plan type filter
plan_types = con.execute(
    "SELECT DISTINCT plan_type_rating FROM fact_school_performance WHERE plan_type_rating IS NOT NULL ORDER BY plan_type_rating"
).fetchdf()["plan_type_rating"].tolist()

selected_plan_types = st.sidebar.multiselect(
    "Select Plan Type",
    options=plan_types,
    default=plan_types
)

# School level filter
levels = con.execute(
    "SELECT DISTINCT school_level FROM dim_school WHERE school_level IS NOT NULL ORDER BY school_level"
).fetchdf()["school_level"].tolist()

selected_levels = st.sidebar.multiselect(
    "Select School Level",
    options=levels,
    default=levels
)

What does this code do?

  • Each st.sidebar.multiselect queries your dimension or fact table for distinct values and renders a dropdown in the sidebar.
  • District defaults to empty (all districts shown). Plan type and school level default to all options selected, so the learner sees full data on first load.
  • DuckDB's .fetchdf() returns a pandas DataFrame, and .tolist() converts the column into a plain Python list for the widget.
  • Now add the dynamic WHERE clause builder and KPI metric cards below the filters:
# === BUILD FILTERED QUERY ===
where_clauses = []
if selected_districts:
    district_list = ", ".join([f"'{d}'" for d in selected_districts])
    where_clauses.append(f"d.district_name IN ({district_list})")
if selected_plan_types:
    plan_list = ", ".join([f"'{p}'" for p in selected_plan_types])
    where_clauses.append(f"f.plan_type_rating IN ({plan_list})")
if selected_levels:
    level_list = ", ".join([f"'{l}'" for l in selected_levels])
    where_clauses.append(f"s.school_level IN ({level_list})")

where_sql = "WHERE " + " AND ".join(where_clauses) if where_clauses else ""

# === KPI METRICS ===
kpi_query = f"""
    SELECT
        COUNT(*) as total_schools,
        ROUND(AVG(f.pct_points_earned), 1) as avg_overall,
        ROUND(AVG(f.achievement_pct), 1) as avg_achievement,
        ROUND(AVG(f.growth_pct), 1) as avg_growth,
        ROUND(AVG(f.pwr_pct), 1) as avg_pwr
    FROM fact_school_performance f
    JOIN dim_district d ON f.district_code = d.district_code
    JOIN dim_school s ON f.school_code = s.school_code
    {where_sql}
"""
kpis = con.execute(kpi_query).fetchdf()

col1, col2, col3, col4, col5 = st.columns(5)
col1.metric("Total Schools", int(kpis["total_schools"].iloc[0]))
col2.metric("Avg Overall %", f"{kpis['avg_overall'].iloc[0]}%")
col3.metric("Avg Achievement", f"{kpis['avg_achievement'].iloc[0]}%")
col4.metric("Avg Growth", f"{kpis['avg_growth'].iloc[0]}%")
col5.metric("Avg PWR", f"{kpis['avg_pwr'].iloc[0]}%")

How does the dynamic WHERE clause work?

The code checks each filter variable. If the user selected values, it appends a SQL condition to the where_clauses list. If no filters are active, where_sql stays as an empty string and the query returns all rows.

The st.columns(5) call creates five equal-width columns. Each column displays one st.metric card showing the filtered average for that CDE indicator.

  • Save src/dashboard.py.
  • Your browser should auto-refresh. You should see a sidebar on the left with three filter dropdowns and five metric cards across the top showing Total Schools, Avg Overall %, Avg Achievement, Avg Growth, and Avg PWR.

Seeing an error about missing columns?

  • Make sure you ran src/etl_pipeline.py successfully before launching the dashboard. The DuckDB database must already contain the fact_school_performance table with columns like achievement_pct and growth_pct.
  • Check that no extra spaces crept into the f-string query. The {where_sql} variable must be on its own line inside the triple-quoted string.

Help me debug a column-not-found error in my Streamlit dashboard query.

Build the charts and data table

The metrics give a high-level summary, but charts reveal patterns. You will add a plan type distribution bar chart, a grouped indicator comparison, and a sortable school details table.

  • Add the plan type distribution bar chart below the KPI metrics section:
# === PLAN TYPE DISTRIBUTION ===
st.subheader("Plan Type Distribution")

plan_dist_query = f"""
    SELECT f.plan_type_rating, COUNT(*) as count
    FROM fact_school_performance f
    JOIN dim_district d ON f.district_code = d.district_code
    JOIN dim_school s ON f.school_code = s.school_code
    {where_sql}
    GROUP BY f.plan_type_rating
    ORDER BY count DESC
"""
plan_dist = con.execute(plan_dist_query).fetchdf()
st.bar_chart(plan_dist, x="plan_type_rating", y="count")

What does this code do?

This counts how many schools fall into each plan type (Performance, Improvement, Priority Improvement, Turnaround) after applying the sidebar filters. The st.bar_chart call renders a native Streamlit bar chart using the plan type as the x-axis.

  • Add the grouped indicator comparison chart next:
# === INDICATOR COMPARISON BY PLAN TYPE ===
st.subheader("Average Indicator Scores by Plan Type")

indicator_query = f"""
    SELECT
        f.plan_type_rating,
        ROUND(AVG(f.achievement_pct), 1) as achievement,
        ROUND(AVG(f.growth_pct), 1) as growth,
        ROUND(AVG(f.pwr_pct), 1) as pwr
    FROM fact_school_performance f
    JOIN dim_district d ON f.district_code = d.district_code
    JOIN dim_school s ON f.school_code = s.school_code
    {where_sql}
    GROUP BY f.plan_type_rating
    ORDER BY achievement DESC
"""
indicator_df = con.execute(indicator_query).fetchdf()

# Reshape for grouped bar chart
indicator_melted = indicator_df.melt(
    id_vars=["plan_type_rating"],
    value_vars=["achievement", "growth", "pwr"],
    var_name="Indicator",
    value_name="Avg % Earned"
)

fig_indicators = px.bar(
    indicator_melted,
    x="plan_type_rating",
    y="Avg % Earned",
    color="Indicator",
    barmode="group",
    labels={"plan_type_rating": "Plan Type", "Avg % Earned": "Average % Points Earned"},
    title="CDE Indicator Breakdown by Plan Type"
)
st.plotly_chart(fig_indicators, use_container_width=True)

Why melt the DataFrame?

The query returns one row per plan type with separate columns for achievement, growth, and pwr. Plotly's grouped bar chart needs a single "Indicator" column to assign colors.

The .melt() call reshapes the wide DataFrame into long format. Each original column name becomes a value in the "Indicator" column. This lets px.bar group bars by indicator and color them differently.

  • Finally, add the school details data table:
# === DATA TABLE ===
st.subheader("School Details")

detail_query = f"""
    SELECT
        s.school_name,
        d.district_name,
        s.school_level,
        f.plan_type_rating,
        f.achievement_pct,
        f.growth_pct,
        f.pwr_pct,
        f.pct_points_earned
    FROM fact_school_performance f
    JOIN dim_school s ON f.school_code = s.school_code
    JOIN dim_district d ON f.district_code = d.district_code
    {where_sql}
    ORDER BY f.pct_points_earned DESC
    LIMIT 100
"""
details = con.execute(detail_query).fetchdf()
st.dataframe(details, use_container_width=True)

What does this code do?

This joins the fact table back to the dimension tables to show human-readable school and district names. The results are ordered by overall percentage earned (highest first) and limited to 100 rows for performance.

The st.dataframe widget renders a sortable, scrollable table that users can click to sort by any column.

  • Save src/dashboard.py.
  • Your browser should auto-refresh. You should see the plan type distribution bar chart, the grouped indicator comparison chart below it, and a school details table at the bottom.
  • Try selecting a single district in the sidebar. All metrics, charts, and the table should update to reflect only that district's schools.

Charts not updating when you change filters?

  • Make sure you clicked Always rerun in the top-right corner of the Streamlit app. Without this, you have to manually click Rerun after each save.
  • Confirm that the where_sql variable is referenced inside each query's f-string. If a query is missing {where_sql}, that section ignores the filters.

Why aren't my Streamlit filters changing the chart data?

✔️ Awesome, I've got everything!

Great. Double check you have saved src/dashboard.py and all five sections are rendering in your browser.

ⓧ I'd like to double check the full code

Here is the complete src/dashboard.py file:

import streamlit as st
import duckdb
import pandas as pd
import plotly.express as px

st.set_page_config(page_title="CO School Performance Dashboard", layout="wide")
st.title("Colorado School Performance Framework Dashboard")
st.caption("Data source: Colorado Department of Education, 2025 Preliminary Ratings")
st.markdown("""
**CDE Performance Indicators:** Academic Achievement (CMAS/PSAT scores) |
Academic Growth (Colorado Growth Model) | Postsecondary & Workforce Readiness (graduation, SAT, matriculation)

**Plan Type Thresholds:** Performance >= 60% | Improvement 47-59.9% | Priority Improvement 34-46.9% | Turnaround < 34%
""")

# Connect to DuckDB
@st.cache_resource
def get_connection():
    return duckdb.connect("data/school_performance.duckdb", read_only=True)

con = get_connection()

# === SIDEBAR FILTERS ===
st.sidebar.header("Filters")

# District filter
districts = con.execute(
    "SELECT DISTINCT district_name FROM dim_district ORDER BY district_name"
).fetchdf()["district_name"].tolist()

selected_districts = st.sidebar.multiselect(
    "Select District(s)",
    options=districts,
    default=[]
)

# Plan type filter
plan_types = con.execute(
    "SELECT DISTINCT plan_type_rating FROM fact_school_performance WHERE plan_type_rating IS NOT NULL ORDER BY plan_type_rating"
).fetchdf()["plan_type_rating"].tolist()

selected_plan_types = st.sidebar.multiselect(
    "Select Plan Type",
    options=plan_types,
    default=plan_types
)

# School level filter
levels = con.execute(
    "SELECT DISTINCT school_level FROM dim_school WHERE school_level IS NOT NULL ORDER BY school_level"
).fetchdf()["school_level"].tolist()

selected_levels = st.sidebar.multiselect(
    "Select School Level",
    options=levels,
    default=levels
)

# === BUILD FILTERED QUERY ===
where_clauses = []
if selected_districts:
    district_list = ", ".join([f"'{d}'" for d in selected_districts])
    where_clauses.append(f"d.district_name IN ({district_list})")
if selected_plan_types:
    plan_list = ", ".join([f"'{p}'" for p in selected_plan_types])
    where_clauses.append(f"f.plan_type_rating IN ({plan_list})")
if selected_levels:
    level_list = ", ".join([f"'{l}'" for l in selected_levels])
    where_clauses.append(f"s.school_level IN ({level_list})")

where_sql = "WHERE " + " AND ".join(where_clauses) if where_clauses else ""

# === KPI METRICS ===
kpi_query = f"""
    SELECT
        COUNT(*) as total_schools,
        ROUND(AVG(f.pct_points_earned), 1) as avg_overall,
        ROUND(AVG(f.achievement_pct), 1) as avg_achievement,
        ROUND(AVG(f.growth_pct), 1) as avg_growth,
        ROUND(AVG(f.pwr_pct), 1) as avg_pwr
    FROM fact_school_performance f
    JOIN dim_district d ON f.district_code = d.district_code
    JOIN dim_school s ON f.school_code = s.school_code
    {where_sql}
"""
kpis = con.execute(kpi_query).fetchdf()

col1, col2, col3, col4, col5 = st.columns(5)
col1.metric("Total Schools", int(kpis["total_schools"].iloc[0]))
col2.metric("Avg Overall %", f"{kpis['avg_overall'].iloc[0]}%")
col3.metric("Avg Achievement", f"{kpis['avg_achievement'].iloc[0]}%")
col4.metric("Avg Growth", f"{kpis['avg_growth'].iloc[0]}%")
col5.metric("Avg PWR", f"{kpis['avg_pwr'].iloc[0]}%")

# === PLAN TYPE DISTRIBUTION ===
st.subheader("Plan Type Distribution")

plan_dist_query = f"""
    SELECT f.plan_type_rating, COUNT(*) as count
    FROM fact_school_performance f
    JOIN dim_district d ON f.district_code = d.district_code
    JOIN dim_school s ON f.school_code = s.school_code
    {where_sql}
    GROUP BY f.plan_type_rating
    ORDER BY count DESC
"""
plan_dist = con.execute(plan_dist_query).fetchdf()
st.bar_chart(plan_dist, x="plan_type_rating", y="count")

# === INDICATOR COMPARISON BY PLAN TYPE ===
st.subheader("Average Indicator Scores by Plan Type")

indicator_query = f"""
    SELECT
        f.plan_type_rating,
        ROUND(AVG(f.achievement_pct), 1) as achievement,
        ROUND(AVG(f.growth_pct), 1) as growth,
        ROUND(AVG(f.pwr_pct), 1) as pwr
    FROM fact_school_performance f
    JOIN dim_district d ON f.district_code = d.district_code
    JOIN dim_school s ON f.school_code = s.school_code
    {where_sql}
    GROUP BY f.plan_type_rating
    ORDER BY achievement DESC
"""
indicator_df = con.execute(indicator_query).fetchdf()

# Reshape for grouped bar chart
indicator_melted = indicator_df.melt(
    id_vars=["plan_type_rating"],
    value_vars=["achievement", "growth", "pwr"],
    var_name="Indicator",
    value_name="Avg % Earned"
)

fig_indicators = px.bar(
    indicator_melted,
    x="plan_type_rating",
    y="Avg % Earned",
    color="Indicator",
    barmode="group",
    labels={"plan_type_rating": "Plan Type", "Avg % Earned": "Average % Points Earned"},
    title="CDE Indicator Breakdown by Plan Type"
)
st.plotly_chart(fig_indicators, use_container_width=True)

# === DATA TABLE ===
st.subheader("School Details")

detail_query = f"""
    SELECT
        s.school_name,
        d.district_name,
        s.school_level,
        f.plan_type_rating,
        f.achievement_pct,
        f.growth_pct,
        f.pwr_pct,
        f.pct_points_earned
    FROM fact_school_performance f
    JOIN dim_school s ON f.school_code = s.school_code
    JOIN dim_district d ON f.district_code = d.district_code
    {where_sql}
    ORDER BY f.pct_points_earned DESC
    LIMIT 100
"""
details = con.execute(detail_query).fetchdf()
st.dataframe(details, use_container_width=True)

Your interactive dashboard is live. Next, you will document the project with a professional README and push everything to GitHub.

Document and Push to GitHub

Your pipeline extracts CDE data, transforms it into a star schema, and serves it through an interactive dashboard. Now it is time to make this project portfolio-ready.

Professional documentation turns a personal script into something other engineers can understand, set up, and contribute to. In this step, you will write a README explaining the CDE framework and project architecture, configure Git to ignore generated files, and push everything to GitHub.

In this step, get ready to:
  • Write a professional README.md documenting the project, CDE framework, and setup instructions.
  • Populate the .gitignore to exclude data files, virtual environments, and IDE config.
  • Initialize Git, commit all files, and push to a public GitHub repository.
Write the README.md

A good README answers three questions for anyone landing on your repository: what does this project do, how do I run it, and how is the data structured.

  • In PyCharm's Project pane, double-click README.md to open it in the editor.
  • Replace the empty file contents with the following documentation:
# Colorado School Performance Framework Analytics Pipeline

An end-to-end data analytics pipeline that ingests Colorado Department of Education (CDE) School Performance Framework data, models it in a star schema data warehouse, and serves interactive insights through a Streamlit dashboard.

## CDE Performance Framework Overview

The Colorado Department of Education evaluates schools and districts on three core performance indicators:

| Indicator | What It Measures | Assessments |
|-----------|-----------------|-------------|
| **Academic Achievement** | Mean scale scores on state assessments | E/M: CMAS ELA, Math, Science. HS: PSAT EBRW, Math + CMAS Science |
| **Academic Growth** | Year-over-year student improvement | Colorado Growth Model percentiles |
| **Postsecondary & Workforce Readiness** | High school outcomes | Graduation rates, dropout rates, SAT scores, college matriculation |

Points accumulated across these indicators determine the plan type:

| Plan Type | Points Threshold |
|-----------|------------------|
| Performance | >= 60% of points earned |
| Improvement | 47% to 59.9% |
| Priority Improvement | 34% to 46.9% |
| Turnaround | < 34% |

## Architecture

```
[CDE XLSX Files] --> [ETL Pipeline (Python/pandas)] --> [DuckDB Star Schema] --> [Streamlit Dashboard]
     Extract              Transform & Load                Data Warehouse            Visualization
```

### Star Schema Design

- **Fact Table**: `fact_school_performance`
- **Dimension Tables**: `dim_school`, `dim_district`, `dim_academic_year`

## Tech Stack

| Tool | Purpose | Version |
|------|---------|--------|
| Python | Runtime | 3.11+ |
| pandas | Data extraction and transformation | 3.0.3 |
| openpyxl | XLSX file reading engine | 3.1.5 |
| DuckDB | Analytical data warehouse | 1.5.2 |
| Streamlit | Interactive dashboard | 1.57.0 |
| Plotly | Chart visualizations | 6.x |

## Data Source

**Colorado Department of Education** - State Accountability Data Files
- URL: https://www.cde.state.co.us/schoolview/datafiles

## Setup

```bash
git clone https://github.com/YOUR_USERNAME/school-performance-pipeline.git
cd school-performance-pipeline
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt
```

## Usage

```bash
python src/etl_pipeline.py
streamlit run src/dashboard.py
```

## Project Structure

```
school-performance-pipeline/
├── README.md
├── requirements.txt
├── .gitignore
├── data/
│   └── .gitkeep
└── src/
    ├── etl_pipeline.py
    └── dashboard.py
```

What does this README cover?

  • The CDE framework tables explain the three performance indicators and plan type thresholds so readers understand the domain.
  • The architecture diagram shows the ETL flow from raw XLSX through DuckDB to the Streamlit dashboard.
  • The Setup and Usage sections give anyone the exact commands needed to reproduce your results locally.
  • Save README.md.
Populate the .gitignore

The .gitignore file tells Git which files to exclude from version control. You want to keep the raw data file and DuckDB database out of your repository because they are large, regenerable, and user-specific.

  • In PyCharm's Project pane, double-click .gitignore to open it.
  • Replace the empty file with these exclusion rules:
# Virtual environment
venv/
.venv/

# PyCharm / JetBrains
.idea/

# Data files
data/*.xlsx
data/*.duckdb

# Python
__pycache__/
*.py[cod]
*$py.class
*.egg-info/
dist/
build/

# OS
.DS_Store

Why exclude the data files?

The .xlsx file is user-downloaded and potentially large. The .duckdb file is a binary database that your ETL script regenerates on every run.

Keeping regenerable artifacts out of Git reduces repository size and avoids merge conflicts on binary files.

  • Save .gitignore.
  • Create a file called .gitkeep inside the data/ folder so Git tracks the empty directory.
  • Create the placeholder file by running this command in PyCharm's terminal:
touch data/.gitkeep

Why create a .gitkeep?

Git does not track empty directories. Since .gitignore excludes all .xlsx and .duckdb files from data/, the directory would vanish from the repository without .gitkeep as a placeholder.

✔️ Awesome, I've got everything!

Great. Double check you have saved both files before moving on.

ⓧ I'd like to double check the full code

Here is the complete README.md:

# Colorado School Performance Framework Analytics Pipeline

An end-to-end data analytics pipeline that ingests Colorado Department of Education (CDE) School Performance Framework data, models it in a star schema data warehouse, and serves interactive insights through a Streamlit dashboard.

## CDE Performance Framework Overview

The Colorado Department of Education evaluates schools and districts on three core performance indicators:

| Indicator | What It Measures | Assessments |
|-----------|-----------------|-------------|
| **Academic Achievement** | Mean scale scores on state assessments | E/M: CMAS ELA, Math, Science. HS: PSAT EBRW, Math + CMAS Science |
| **Academic Growth** | Year-over-year student improvement | Colorado Growth Model percentiles |
| **Postsecondary & Workforce Readiness** | High school outcomes | Graduation rates, dropout rates, SAT scores, college matriculation |

Points accumulated across these indicators determine the plan type:

| Plan Type | Points Threshold |
|-----------|------------------|
| Performance | >= 60% of points earned |
| Improvement | 47% to 59.9% |
| Priority Improvement | 34% to 46.9% |
| Turnaround | < 34% |

## Architecture

```
[CDE XLSX Files] --> [ETL Pipeline (Python/pandas)] --> [DuckDB Star Schema] --> [Streamlit Dashboard]
     Extract              Transform & Load                Data Warehouse            Visualization
```

### Star Schema Design

- **Fact Table**: `fact_school_performance`
- **Dimension Tables**: `dim_school`, `dim_district`, `dim_academic_year`

## Tech Stack

| Tool | Purpose | Version |
|------|---------|--------|
| Python | Runtime | 3.11+ |
| pandas | Data extraction and transformation | 3.0.3 |
| openpyxl | XLSX file reading engine | 3.1.5 |
| DuckDB | Analytical data warehouse | 1.5.2 |
| Streamlit | Interactive dashboard | 1.57.0 |
| Plotly | Chart visualizations | 6.x |

## Data Source

**Colorado Department of Education** - State Accountability Data Files
- URL: https://www.cde.state.co.us/schoolview/datafiles

## Setup

```bash
git clone https://github.com/YOUR_USERNAME/school-performance-pipeline.git
cd school-performance-pipeline
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt
```

## Usage

```bash
python src/etl_pipeline.py
streamlit run src/dashboard.py
```

## Project Structure

```
school-performance-pipeline/
├── README.md
├── requirements.txt
├── .gitignore
├── data/
│   └── .gitkeep
└── src/
    ├── etl_pipeline.py
    └── dashboard.py
```

And the complete .gitignore:

# Virtual environment
venv/
.venv/

# PyCharm / JetBrains
.idea/

# Data files
data/*.xlsx
data/*.duckdb

# Python
__pycache__/
*.py[cod]
*$py.class
*.egg-info/
dist/
build/

# OS
.DS_Store
Initialize Git and push to GitHub

PyCharm has built-in Git and GitHub integration. You can initialize a repository, commit, and push without leaving the IDE.

  • In PyCharm's top menu bar, click VCS.
  • Click Enable Version Control Integration.
  • In the dialog that appears, select Git from the dropdown and click OK.

What just happened?

PyCharm ran git init in your project root. The VCS menu has now changed to Git in the menu bar, confirming that your project is under version control.

Next, commit all your project files.

  • Press Cmd+K to open the Commit tool window.
  • In the file list, check the box next to Unversioned Files to select all project files.
  • PyCharm will ask if you want to add these files to Git. Click Add.
  • In the commit message field, type Initial commit: ETL pipeline, dashboard, and documentation.
  • Click Commit.

Check the committed files

You should see README.md, requirements.txt, .gitignore, data/.gitkeep, src/etl_pipeline.py, and src/dashboard.py in the list. The venv/, .idea/, and data/*.duckdb files should NOT appear because your .gitignore excludes them.

Now share the project on GitHub.

  • In the top menu bar, click Git.
  • Click GitHub, then Share Project on GitHub.
  • If prompted to log in, click Log In via GitHub and authorize PyCharm in your browser.
  • In the Share Project on GitHub dialog, set the repository name to school-performance-pipeline.
  • Leave the Private checkbox unchecked so the repo is public.
  • Click Share.

What does Share do?

PyCharm creates a new repository on your GitHub account, adds it as the origin remote, and pushes all committed files in one step.

  • Open your browser and navigate to your new GitHub repository at github.com/your-username/school-performance-pipeline.
  • Verify that the README renders with the architecture diagram, the CDE indicator table, and the project structure tree.
  • Confirm that data/ only contains .gitkeep and that no .xlsx or .duckdb files appear.

README not rendering correctly?

  • Make sure your README.md file uses exactly README.md as the filename (case-sensitive). GitHub only auto-renders files named README.md or readme.md.
  • If tables look broken, check that the pipe characters (|) and dashes (---) in the header separator row are intact.
  • help me debug my README rendering on GitHub

Secret mission

Growth vs. Achievement Gap Analysis

Your dashboard shows plan types and indicator averages, but it hides the most important equity insight in the data. Some schools are making tremendous growth year-over-year despite low absolute test scores. Add a scatter plot that reveals these hidden high-performers.

Clean Up Your Resources

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:

  • Local project folder school-performance-pipeline/ on your Desktop (contains source code, virtual environment, data files, and DuckDB database).
  • GitHub repository school-performance-pipeline.

Keep everything running

No action needed. Choose this if you're still actively building or want to revisit the dashboard later.

  • Your project folder stays on the Desktop with all files intact.
  • Your GitHub repository remains public for portfolio visitors.
  • To relaunch the dashboard later, open PyCharm, activate your virtual environment, and run streamlit run src/dashboard.py.

Pause - I'll come back to this later

Stop any running processes to free up memory. Your files stay on disk for next time.

  • If the Streamlit dashboard is still running in your terminal, press Ctrl+C to stop it.
  • Close the PyCharm project (your files remain saved on disk).
  • Your GitHub repository stays live. No further action needed.

Delete - I don't want to use this again

Remove all project resources from your machine and GitHub.

  • If the Streamlit dashboard is still running, press Ctrl+C in the terminal to stop it.
  • Delete the local project folder by running this command in your terminal:
rm -rf ~/Desktop/school-performance-pipeline
  • To delete the GitHub repository, go to your repository page on GitHub.
  • Click Settings (the tab at the top of the repository page).
  • Scroll to the bottom of the page to the Danger Zone section.
  • Click Delete this repository and follow the confirmation prompts.

Nice Work!

Nice Work!

You just built an end-to-end data analytics pipeline from scratch. Starting with a raw CDE spreadsheet, you extracted, transformed, and loaded real school performance data into a star schema, queried it for accountability insights, and delivered those insights through an interactive dashboard.

You've learned how to:

  • Build a Python ETL pipeline that extracts real-world CDE accountability data across three core performance indicators (Academic Achievement, Academic Growth, and Postsecondary & Workforce Readiness) and loads it into a DuckDB star schema with plan type classification.
  • Write SQL queries against a star schema to surface accountability insights like indicator comparisons, district-level patterns, and schools near plan type boundaries.
  • Build an interactive Streamlit dashboard with KPI metric cards, plan type distribution charts, and filterable school details, then document and push the complete project to GitHub as a portfolio piece.
  • Secret Mission: Created a Growth vs. Achievement scatter plot that reveals hidden performance patterns masked by raw achievement scores, highlighting schools making significant progress despite low absolute test scores.

Ready to quiz yourself?