Build a Nonprofit Data Pipeline

Transform messy nonprofit data into funder-ready analytics with dbt and DuckDB.

Introduction

30 Second Summary

Nonprofits change lives, but proving it to funders means pulling data from multiple systems that never agree with each other. When a foundation asks "what's your cost-per-placement?", the answer shouldn't require a week of spreadsheet wrangling.

In this project, you will build a complete data pipeline using dbt and DuckDB that transforms messy, multi-source nonprofit data into clean funder-ready analytics and a Power BI dashboard.

What You'll Build

You'll type one command and watch four messy data exports collapse into a polished funder dashboard that answers "how much did each placement cost us?" in seconds.

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

  • A working pipeline where a single command transforms raw, inconsistent data into clean analytical tables with full data lineage tracking.
  • A funder report mart you can query to instantly answer "what's our cost-per-placement for each grant?" without writing complex joins.
  • A funder-ready dashboard in Power BI showing cohort employment rates, average starting salaries, and grant ROI metrics you can present directly to stakeholders.
  • Secret Mission: Add automated data quality tests and generate a browsable data catalog with lineage tracking.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, JOIN, GROUP BY, CASE WHEN) and have basic familiarity with a command line. No prior experience with dbt or DuckDB is needed.

Before We Start

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

Set Up Your Environment

Before you can transform any data, you need a local environment that can run dbt transformations against a DuckDB warehouse. Everything in this project runs on your machine. No cloud accounts, no infrastructure costs.

In this step, you will install Python, set up an isolated virtual environment, install the dbt-duckdb adapter (which brings dbt-core and DuckDB along with it), and configure a dbt project from scratch.

In this step, get ready to:
  • Install Python and create a virtual environment.
  • Install dbt-duckdb with all its dependencies.
  • Create the project folder structure and configuration files, then verify the connection.
Install Python and dbt-duckdb

The dbt-duckdb adapter requires Python 3.9 or newer. Let's confirm you have a compatible version installed.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Check your Python version by running this command:
python3 --version

✔️ I see version 3.9 or higher

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

ⓧ I see a version lower than 3.9

You have Python installed but it's too old for this project. You need 3.9 or newer.

On macOS, the system Python is often outdated. Install a current version using Homebrew:

  • Install the latest Python by running:
brew install python3
  • Close and reopen your terminal, then run python3 --version again to confirm it now reports 3.9 or higher.

On Windows, download the latest installer from python.org. During installation, check the box that says Add Python to PATH. After installing, close and reopen PowerShell, then run python3 --version again.

ⓧ Command not found

Python is not installed on your system yet.

On macOS, install Python using Homebrew:

  • Install Homebrew (if you don't have it) and then Python by running:
brew install python3

On Windows, download the latest installer from python.org. During installation, check the box that says Add Python to PATH.

  • After installing, close and reopen your terminal, then run python3 --version to confirm it reports 3.9 or higher.

Now create your project folder on the Desktop and set up an isolated Python environment inside it.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir basta-data-pipeline
cd basta-data-pipeline
  • Create a virtual environment by running:
python3 -m venv venv

What is a virtual environment?

A virtual environment is an isolated copy of Python that keeps this project's packages separate from your system-wide installation. Everything you install inside it stays contained in the venv folder.

  • Activate the virtual environment by running this command:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

You should see (venv) appear at the beginning of your terminal prompt. This confirms the virtual environment is active.

  • Install the dbt-duckdb adapter by running:
pip3 install dbt-duckdb

What does this install?

The dbt-duckdb package pulls in dbt-core (the transformation framework) and duckdb (the local analytical database) as dependencies. One install gives you the entire stack.

The installation will print several lines as it downloads packages. When it finishes, you should see a line containing Successfully installed followed by a list of package names.

Installation failed?

On Windows, you may see an error about Microsoft Visual C++ Redistributable. Download and install it from the Microsoft website, then try the install command again.

If you see a permissions error, make sure your virtual environment is activated (look for (venv) in your prompt).

Help me fix my dbt-duckdb installation error.

Create the project structure and configuration files

A dbt project needs a specific folder layout and two configuration files to work. The dbt_project.yml tells dbt about your project structure. The profiles.yml tells dbt how to connect to your database.

  • Create the model directories by running:
mkdir -p models/staging models/marts tests
  • Confirm the folders were created by running:
ls models

You should see staging and marts listed in the output.

Now create the two configuration files. You will use a text editor to write them.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the basta-data-pipeline folder, and click Open.
  • Create a new file by clicking File then New File.
  • Save it immediately as dbt_project.yml in the root of your basta-data-pipeline folder.
  • Paste the following configuration into dbt_project.yml:
name: basta_data_pipeline
version: '1.0.0'
config-version: 2
profile: basta_data_pipeline

model-paths: ["models"]
test-paths: ["tests"]
target-path: "target"
clean-targets:
  - "target"
  - "dbt_packages"

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

What does this configuration do?

  • name identifies your project. It must match the profile name in profiles.yml.
  • model-paths tells dbt where to look for your SQL transformation files.
  • The models block sets staging models to materialize as views (lightweight, always up to date) and marts as tables (pre-computed for fast queries).
  • Save dbt_project.yml.
  • Create another new file and save it as profiles.yml in the same root folder.
  • Paste the following configuration into profiles.yml:
basta_data_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ./basta_pipeline.duckdb
      schema: main
      threads: 4

What does this configuration do?

  • type: duckdb tells dbt to use the DuckDB adapter you installed.
  • path: ./basta_pipeline.duckdb points to the database file that DuckDB will auto-create when you first run dbt.
  • threads: 4 lets dbt run up to 4 models in parallel for faster builds.
  • Save profiles.yml.

Why is profiles.yml in the project folder?

dbt checks the current working directory for profiles.yml before falling back to ~/.dbt/. Keeping it in the project root means your connection config travels with your project and you never have to worry about a global config file.

✔️ Awesome, I've got everything!

Great. Double check that you have saved both files.

ⓧ I'd like to double check the full code

Scroll up and confirm you have these two files in the root of your basta-data-pipeline folder:

  • dbt_project.yml (the project configuration block above).
  • profiles.yml (the connection configuration block above).

Make sure the profile value in dbt_project.yml matches the top-level key in profiles.yml. Both should be basta_data_pipeline.

Verify the connection with dbt debug

Everything is configured. Time to check that dbt can find your project and connect to DuckDB.

Before you run this, what do you think will happen? Will dbt connect successfully, or will it report an error?

  • Switch back to your terminal (make sure you are still in the basta-data-pipeline folder with (venv) active) and run:
dbt debug

You should see dbt check your configuration, find the profiles file, and confirm the DuckDB connection. The last line reads:

All checks passed!

Seeing a connection error or missing profile?

  • Make sure your terminal is inside the basta-data-pipeline folder. Run pwd (macOS) or cd (Windows) to check.
  • Confirm that profiles.yml is saved in the project root (same folder as dbt_project.yml), not inside models/ or another subfolder.
  • Check that the profile: value in dbt_project.yml exactly matches the top-level key in profiles.yml (both should be basta_data_pipeline).
  • Make sure your virtual environment is active. Look for (venv) in your prompt. If it's missing, re-activate it with the activation command from earlier.

Help me fix my dbt debug error.

Your data engineering environment is ready. You have Python, dbt, and DuckDB all working together locally. Next up, you will load some messy nonprofit data into DuckDB and see exactly why raw data cannot be trusted for reporting.

Ingest Raw Data and See the Problem

Your dbt project is connected and ready to transform data. But transform what data, exactly?

Real nonprofit data comes from multiple systems: a student management database, a program tracker, an employer placement log, and a grants spreadsheet. Each system exports data with its own quirks. In this step, you will load messy, realistic data into your DuckDB warehouse and see firsthand why raw data cannot be trusted for stakeholder reporting.

In this step, get ready to:
  • Create a Python script that generates messy CSV data from four simulated source systems and loads it into DuckDB.
  • Run a verification query that surfaces duplicates, inconsistent values, and missing fields in the raw data.
Generate messy raw data

A nonprofit like Basta exports data from separate systems that were never designed to talk to each other. You will simulate this by writing a Python script that creates CSV files with intentional quality problems: duplicate student records, inconsistent status labels, and missing fields.

  • In your text editor, create a new file called generate_raw_data.py in your project root (the same folder that holds dbt_project.yml).
  • Paste the following code into the file to set up imports and generate the students CSV:
import duckdb
import csv
import os

os.makedirs('raw_data', exist_ok=True)

# --- Raw Students: includes duplicates and inconsistent first_gen_status ---
students_header = ['student_id', 'first_name', 'last_name', 'cohort', 'enrollment_date', 'campus', 'first_gen_status', 'graduation_date']
students_rows = [
    ['STU001', 'Maria', 'Garcia', 'Spring 2024', '2024-01-15', 'Brooklyn', 'Yes', '2024-05-20'],
    ['STU002', 'James', 'Chen', 'Spring 2024', '2024-01-15', 'Brooklyn', 'Y', '2024-05-20'],
    ['STU003', 'Aisha', 'Johnson', 'Spring 2024', '2024-01-20', 'Brooklyn', 'TRUE', '2024-05-20'],
    ['STU004', 'Carlos', 'Rivera', 'Spring 2024', '2024-01-20', 'Manhattan', 'Yes', '2024-05-20'],
    ['STU005', 'Priya', 'Patel', 'Fall 2024', '2024-08-25', 'Brooklyn', '1', '2024-12-15'],
    ['STU006', 'David', 'Kim', 'Fall 2024', '2024-08-25', 'Manhattan', 'Yes', '2024-12-15'],
    ['STU007', 'Fatima', 'Ali', 'Fall 2024', '2024-08-30', 'Brooklyn', 'Y', '2024-12-15'],
    ['STU008', 'Marcus', 'Williams', 'Fall 2024', '2024-09-01', 'Manhattan', 'TRUE', '2024-12-15'],
    ['STU009', 'Sofia', 'Martinez', 'Spring 2025', '2025-01-13', 'Brooklyn', 'Yes', ''],
    ['STU010', 'Tyler', 'Brown', 'Spring 2025', '2025-01-13', 'Manhattan', 'Y', ''],
    ['STU011', 'Zara', 'Ahmed', 'Spring 2025', '2025-01-15', 'Brooklyn', '1', ''],
    ['STU012', 'Jordan', 'Lee', 'Spring 2025', '2025-01-15', 'Manhattan', 'Yes', ''],
    ['STU003', 'Aisha', 'Johnson', 'Spring 2024', '2024-01-20', 'Brooklyn', 'TRUE', '2024-05-20'],
    ['STU007', 'Fatima', 'Ali', 'Fall 2024', '2024-08-30', 'Brooklyn', 'Y', '2024-12-15'],
    ['STU001', 'Maria', 'Garcia', 'Spring 2024', '2024-01-15', 'Brooklyn', 'Yes', '2024-05-20'],
]

with open('raw_data/raw_students.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(students_header)
    writer.writerows(students_rows)

What does this code do?

This section creates a raw_data/ folder and writes a students CSV with 15 rows. Notice the last 3 rows are intentional duplicates of STU001, STU003, and STU007. The first_gen_status column uses inconsistent values: Yes, Y, TRUE, and 1 all mean the same thing.

This simulates what happens when a student management system exports the same record multiple times and different staff enter the same field value differently.

  • Below the students section, add the program participation CSV generation:
# --- Raw Program Participation: inconsistent program names, null end dates ---
participation_header = ['participation_id', 'student_id', 'program_name', 'start_date', 'end_date', 'completion_status', 'milestones_completed', 'total_milestones']
participation_rows = [
    ['P001', 'STU001', 'Career Prep', '2024-02-01', '2024-04-30', 'Completed', '8', '8'],
    ['P002', 'STU002', 'career prep', '2024-02-01', '2024-04-30', 'Completed', '7', '8'],
    ['P003', 'STU003', 'Career Prep Program', '2024-02-01', '2024-04-15', 'Completed', '8', '8'],
    ['P004', 'STU004', 'Career Prep', '2024-02-01', '2024-04-30', 'Completed', '6', '8'],
    ['P005', 'STU005', 'career prep', '2024-09-15', '2024-11-30', 'Completed', '8', '8'],
    ['P006', 'STU006', 'Career Prep', '2024-09-15', '2024-11-30', 'Completed', '7', '8'],
    ['P007', 'STU007', 'Career Prep Program', '2024-09-15', '', 'In Progress', '5', '8'],
    ['P008', 'STU008', 'Career Prep', '2024-09-15', '2024-11-30', 'Completed', '8', '8'],
    ['P009', 'STU009', 'career prep', '2025-02-01', '', 'In Progress', '4', '8'],
    ['P010', 'STU010', 'Career Prep', '2025-02-01', '', 'In Progress', '3', '8'],
    ['P011', 'STU011', 'Career Prep Program', '2025-02-01', '', 'In Progress', '5', '8'],
    ['P012', 'STU012', 'Career Prep', '2025-02-01', '', 'In Progress', '2', '8'],
    ['P013', 'STU001', 'Employer Connect', '2024-03-15', '2024-05-01', 'Completed', '4', '4'],
    ['P014', 'STU005', 'Employer Connect', '2024-10-15', '2024-12-01', 'Completed', '4', '4'],
    ['P015', 'STU003', 'Employer Connect', '2024-03-15', '2024-05-01', 'Completed', '3', '4'],
]

with open('raw_data/raw_program_participation.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(participation_header)
    writer.writerows(participation_rows)

What does this section do?

This generates the program participation export. The same program appears as "Career Prep", "career prep", and "Career Prep Program" depending on which staff member entered it. Some end_date fields are empty strings because those students are still enrolled.

  • Save generate_raw_data.py and run it to confirm the first two CSVs are created:
python generate_raw_data.py
  • Verify the CSV files were created by running:
ls raw_data/

You should see raw_students.csv and raw_program_participation.csv listed.

  • Back in generate_raw_data.py, add the employment outcomes section below the participation code:
# --- Raw Employment Outcomes: inconsistent status, nulls, duplicates ---
outcomes_header = ['outcome_id', 'student_id', 'employer_name', 'job_title', 'starting_salary', 'employment_status', 'offer_date', 'start_date', 'grant_id']
outcomes_rows = [
    ['EMP001', 'STU001', 'Bloomberg LP', 'Business Analyst', '72000', 'Employed', '2024-05-25', '2024-06-15', 'GRT001'],
    ['EMP002', 'STU002', 'Citi', 'Operations Associate', '68000', 'employed', '2024-06-01', '2024-07-01', 'GRT001'],
    ['EMP003', 'STU003', 'DonorsChoose', 'Data Coordinator', '62000', 'Placed', '2024-05-30', '2024-06-20', 'GRT001'],
    ['EMP004', 'STU004', 'Accenture', 'Consulting Analyst', '70000', 'hired', '2024-06-10', '2024-07-15', 'GRT001'],
    ['EMP005', 'STU005', 'Google', 'Program Coordinator', '85000', 'Employed', '2024-12-20', '2025-01-15', 'GRT002'],
    ['EMP006', 'STU006', 'BNY Mellon', 'Financial Analyst', '74000', 'Placed', '2025-01-05', '2025-02-01', 'GRT002'],
    ['EMP007', 'STU008', 'Blackstone', 'Operations Analyst', '78000', 'Employed', '2025-01-10', '2025-02-10', 'GRT002'],
    ['EMP008', 'STU007', '', '', '', 'Seeking', '', '', 'GRT002'],
    ['EMP009', 'STU009', '', '', '', 'Seeking', '', '', 'GRT003'],
    ['EMP010', 'STU010', '', '', '', 'seeking', '', '', 'GRT003'],
    ['EMP011', 'STU001', 'Bloomberg LP', 'Business Analyst', '72000', 'Employed', '2024-05-25', '2024-06-15', 'GRT001'],
]

with open('raw_data/raw_employment_outcomes.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(outcomes_header)
    writer.writerows(outcomes_rows)

What quality problems are hiding here?

Look at the employment_status column: Employed, employed, Placed, hired, Seeking, seeking. Six different values that really represent two states (employed vs. seeking). EMP011 is a duplicate of EMP001. Several rows have empty strings for employer, salary, and dates.

If a funder asks "how many students did we place?" this data would give a wrong answer because a GROUP BY on employment_status would split "Employed" and "Placed" into separate counts.

  • Add the grants data and the DuckDB loading section below the employment outcomes code:
# --- Raw Grants ---
grants_header = ['grant_id', 'funder_name', 'grant_amount', 'grant_start_date', 'grant_end_date', 'target_cohorts']
grants_rows = [
    ['GRT001', 'Robin Hood Foundation', '150000', '2024-01-01', '2024-12-31', 'Spring 2024'],
    ['GRT002', 'Bill & Melinda Gates Foundation', '250000', '2024-07-01', '2025-06-30', 'Fall 2024'],
    ['GRT003', 'Carnegie Corporation of New York', '200000', '2025-01-01', '2025-12-31', 'Spring 2025'],
]

with open('raw_data/raw_grants.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(grants_header)
    writer.writerows(grants_rows)
  • Save the file, then run it again to generate all four CSVs:
python generate_raw_data.py
  • Confirm all four CSV files exist by running:
ls raw_data/

You should now see raw_students.csv, raw_program_participation.csv, raw_employment_outcomes.csv, and raw_grants.csv listed.

  • Now add the final section to generate_raw_data.py that loads all CSVs into the DuckDB database:
# --- Load all CSVs into DuckDB ---
con = duckdb.connect('basta_pipeline.duckdb')

con.execute("DROP TABLE IF EXISTS raw_students")
con.execute("DROP TABLE IF EXISTS raw_program_participation")
con.execute("DROP TABLE IF EXISTS raw_employment_outcomes")
con.execute("DROP TABLE IF EXISTS raw_grants")

con.execute("CREATE TABLE raw_students AS SELECT * FROM read_csv_auto('raw_data/raw_students.csv')")
con.execute("CREATE TABLE raw_program_participation AS SELECT * FROM read_csv_auto('raw_data/raw_program_participation.csv')")
con.execute("CREATE TABLE raw_employment_outcomes AS SELECT * FROM read_csv_auto('raw_data/raw_employment_outcomes.csv')")
con.execute("CREATE TABLE raw_grants AS SELECT * FROM read_csv_auto('raw_data/raw_grants.csv')")

print("Raw data loaded into basta_pipeline.duckdb")
print(f"  raw_students: {con.execute('SELECT COUNT(*) FROM raw_students').fetchone()[0]} rows")
print(f"  raw_program_participation: {con.execute('SELECT COUNT(*) FROM raw_program_participation').fetchone()[0]} rows")
print(f"  raw_employment_outcomes: {con.execute('SELECT COUNT(*) FROM raw_employment_outcomes').fetchone()[0]} rows")
print(f"  raw_grants: {con.execute('SELECT COUNT(*) FROM raw_grants').fetchone()[0]} rows")

con.close()

What does the DuckDB loading section do?

This connects to your basta_pipeline.duckdb database file (the same one dbt is configured to use in profiles.yml). It drops any existing raw tables so the script is safe to re-run, then creates fresh tables using read_csv_auto() which auto-detects column types from the CSV files.

The print statements confirm how many rows were loaded into each table.

  • Save generate_raw_data.py and run the complete script:
python generate_raw_data.py

You should see output confirming all four tables are loaded with their row counts: 15 students, 15 participation records, 11 employment outcomes, and 3 grants.

Seeing a ModuleNotFoundError for duckdb?

Make sure your virtual environment is still activated. You should see its name in parentheses at the start of your terminal prompt. If not, activate it again and re-run the script.

Help me fix this ModuleNotFoundError

✔️ Awesome, I've got everything!

Great. Double check you have saved generate_raw_data.py and your terminal output shows all four tables loaded.

ⓧ I'd like to double check the full code

Your generate_raw_data.py should contain five sections in this order. Scroll through your file and confirm each is present:

  • Imports and os.makedirs at the top.
  • Students section: students_header, students_rows (15 entries), and CSV write.
  • Participation section: participation_header, participation_rows (15 entries), and CSV write.
  • Outcomes section: outcomes_header, outcomes_rows (11 entries), and CSV write.
  • Grants section: grants_header, grants_rows (3 entries), and CSV write.
  • DuckDB loading section: duckdb.connect, DROP/CREATE statements, print statements, and con.close().
Surface the data quality problems

The raw tables are loaded, but are they trustworthy? Imagine the Programs team asking "What is our placement rate?" or the Development team asking "What is our cost-per-placement for the Robin Hood grant?" You will now query the raw data and see why those questions cannot be answered reliably yet.

  • Create a new file called query_raw.py in your project root and paste the following code:
import duckdb

con = duckdb.connect('basta_pipeline.duckdb', read_only=True)

print("=" * 60)
print("DATA QUALITY ISSUES IN RAW DATA")
print("=" * 60)

print("\n1. DUPLICATE STUDENTS:")
print("-" * 40)
result = con.execute("""
    SELECT student_id, COUNT(*) as occurrences
    FROM raw_students
    GROUP BY student_id
    HAVING COUNT(*) > 1
""").fetchall()
for row in result:
    print(f"  {row[0]}: appears {row[1]} times")

print(f"\n  Total rows: {con.execute('SELECT COUNT(*) FROM raw_students').fetchone()[0]}")
print(f"  Unique students: {con.execute('SELECT COUNT(DISTINCT student_id) FROM raw_students').fetchone()[0]}")

What does this query reveal?

The GROUP BY with HAVING COUNT(*) > 1 finds student IDs that appear more than once. The raw export has 15 rows but only 12 unique students. Any count or aggregation on this table would be inflated by 25%.

  • Save query_raw.py and run it to see the duplicates:
python query_raw.py

You should see STU001, STU003, and STU007 each appearing multiple times, with 15 total rows but only 12 unique students.

  • Back in query_raw.py, add the following code below the duplicates section to check for inconsistent status values:
print("\n2. INCONSISTENT EMPLOYMENT STATUS:")
print("-" * 40)
result = con.execute("""
    SELECT employment_status, COUNT(*) as count
    FROM raw_employment_outcomes
    GROUP BY employment_status
    ORDER BY count DESC
""").fetchall()
for row in result:
    print(f"  '{row[0]}': {row[1]} records")

print("\n3. INCONSISTENT FIRST-GEN STATUS:")
print("-" * 40)
result = con.execute("""
    SELECT first_gen_status, COUNT(*) as count
    FROM raw_students
    GROUP BY first_gen_status
    ORDER BY count DESC
""").fetchall()
for row in result:
    print(f"  '{row[0]}': {row[1]} records")
  • Add the final section to check for null and empty values, then close the connection:
print("\n4. NULL/EMPTY VALUES:")
print("-" * 40)
result = con.execute("""
    SELECT
        COUNT(*) FILTER (WHERE employer_name = '') as missing_employer,
        COUNT(*) FILTER (WHERE starting_salary IS NULL OR starting_salary = '') as missing_salary,
        COUNT(*) FILTER (WHERE offer_date = '') as missing_offer_date
    FROM raw_employment_outcomes
""").fetchone()
print(f"  Missing employer: {result[0]} records")
print(f"  Missing salary: {result[1]} records")
print(f"  Missing offer date: {result[2]} records")

print("\n" + "=" * 60)
print("These issues make direct reporting unreliable.")
print("A funder asking 'what is our placement rate?' would get")
print("wrong numbers from this raw data.")
print("=" * 60)

con.close()

What does this section check?

The employment status query will show values like Employed, employed, Placed, hired, Seeking, and seeking. All scattered across different capitalisations and synonyms. The null check counts how many outcome records are missing employer, salary, and offer date information.

This is the data the Development team would be reporting from today. Every inconsistency is a number that would be wrong in a funder report.

Before you run the full verification script, think about this: if a funder asks "what is our placement rate?" and you query this raw data, do you think you would get an accurate answer?

  • Save query_raw.py and run the complete script:
python query_raw.py

You should see four sections of output: duplicate student IDs (STU001, STU003, STU007), inconsistent employment status values (Employed, employed, Placed, hired, Seeking, seeking), inconsistent first-gen status values (Yes, Y, TRUE, 1), and missing fields (3 records with no employer, salary, or offer date).

Why can't we report from this data?

A simple query like SELECT COUNT(*) WHERE employment_status = 'Employed' would miss anyone recorded as "employed", "Placed", or "hired". The placement rate would be wrong. The duplicates would inflate student counts. Null salary records would skew average salary calculations.

This is exactly the problem that dbt staging models solve. In the next step, you will write SQL transformations that clean, deduplicate, and standardize all of this into a trustworthy foundation.

Seeing an error about the table not existing?

Make sure you ran generate_raw_data.py successfully before running query_raw.py. The query script reads from tables that the generation script creates. Check that basta_pipeline.duckdb exists in your project root.

Help me troubleshoot this table not found error

✔️ Awesome, I've got everything!

Great. Double check you have saved query_raw.py and your output shows all four quality issue categories.

ⓧ I'd like to double check the full code

Your query_raw.py should contain these sections in order. Scroll through your file and confirm each is present:

  • Import and connection: duckdb.connect('basta_pipeline.duckdb', read_only=True).
  • Section 1: Duplicate students query with GROUP BY and HAVING.
  • Section 2: Employment status query with GROUP BY on employment_status.
  • Section 3: First-gen status query with GROUP BY on first_gen_status.
  • Section 4: NULL/empty values check using COUNT(*) FILTER.
  • Closing message and con.close().

You have loaded the raw data and proven it cannot be trusted for reporting. The duplicates inflate counts, the inconsistent values fragment aggregations, and the missing fields skew averages. Next up, you will build dbt staging models that clean every one of these problems into a reliable foundation.

Build the Staging Layer

You now have raw data in your warehouse and a clear picture of the quality problems: duplicates, inconsistent values, and nulls everywhere. If the Development team pulled a report from this data today, the numbers would be wrong.

The staging layer fixes each source individually. Each staging model cleans exactly one raw table into a consistent, trustworthy format without mixing business logic in. Once the staging layer is built, every downstream query can trust that student IDs are unique, statuses are consistent, and dates are properly typed.

In this step, get ready to:
  • Declare raw tables as dbt sources so models can reference them.
  • Write staging models that deduplicate, standardize, and clean each source table.
  • Run dbt to build the staging layer and verify the transformations work.
Declare sources and build the students model

Before dbt can read from raw tables, you need to declare them as sources. A source declaration tells dbt where raw data lives so your models can reference it with the {{ source() }} function instead of hardcoding table names.

  • Create a new file called sources.yml inside your models/staging/ folder and paste the following:
version: 2

sources:
  - name: raw
    schema: main
    tables:
      - name: raw_students
        description: Student records exported from the student management system. Contains duplicates and inconsistent field values.
      - name: raw_program_participation
        description: Program enrollment and milestone tracking. Inconsistent program naming across systems.
      - name: raw_employment_outcomes
        description: Employment placement records. Inconsistent status values and duplicate entries.
      - name: raw_grants
        description: Grant and funder information from the development team.

What does this file do?

The sources.yml file tells dbt that four tables already exist in the main schema of your DuckDB database. Your staging models will use {{ source('raw', 'raw_students') }} to reference them instead of writing SELECT * FROM raw_students directly.

This indirection means if a table name or schema ever changes, you update one place (this YAML) rather than every model that reads from it.

  • Save sources.yml.

Now you can write the first staging model. The students table has the most severe problem: duplicate rows for the same student. The model needs to deduplicate by student_id and standardize the inconsistent first_gen_status values (Yes, Y, TRUE, 1) into a single boolean.

  • Create a new file called stg_students.sql inside models/staging/ and paste this first section:
with source as (
    select * from {{ source('raw', 'raw_students') }}
),

deduplicated as (
    select
        *,
        row_number() over (partition by student_id order by enrollment_date) as row_num
    from source
),

What does the deduplication logic do?

  • The source CTE pulls all rows from raw_students using the source reference you declared in sources.yml.
  • The deduplicated CTE assigns a row number to each record within the same student_id. Later, you filter to row_num = 1 to keep only the first occurrence.
  • Directly below what you just pasted (still in stg_students.sql), add the cleaning logic:

cleaned as (
    select
        student_id,
        first_name,
        last_name,
        cohort,
        cast(enrollment_date as date) as enrollment_date,
        campus,
        case
            when lower(first_gen_status) in ('yes', 'y', 'true', '1') then true
            else false
        end as is_first_gen,
        case
            when graduation_date = '' then null
            else cast(graduation_date as date)
        end as graduation_date
    from deduplicated
    where row_num = 1
)

select * from cleaned

What does the cleaning logic do?

  • The CASE on first_gen_status maps all the messy variants (Yes, Y, TRUE, 1) to a single true boolean called is_first_gen.
  • Empty graduation_date strings become proper NULL values. All other dates are cast to the DATE type.
  • The WHERE row_num = 1 filter keeps only one record per student, eliminating the duplicates you saw in Step 2.
  • Save stg_students.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved both sources.yml and stg_students.sql in your models/staging/ folder.

ⓧ I'd like to double check the full code

Your stg_students.sql was built in two chunks. Scroll up and confirm you have all three CTEs in order:

  • source (pulls from the raw table).
  • deduplicated (assigns row numbers by student_id).
  • cleaned (standardizes fields and filters to row_num = 1).
  • A final select * from cleaned at the bottom.

Let's verify that dbt can compile and run your first staging model.

  • Run your first staging model by executing this command:
dbt run --select stg_students

You should see output showing 1 model completed successfully, with a line like 1 of 1 OK and a Completed successfully summary.

Seeing a compilation error?

  • Check that sources.yml is in the same folder (models/staging/) as stg_students.sql. dbt needs to find the source declaration to resolve {{ source('raw', 'raw_students') }}.
  • Make sure the source name in your YAML (name: raw) matches what the model references (source('raw', ...)).
  • Confirm your virtual environment is still active. If you see command not found, reactivate it.

Help me debug my dbt staging model

Write the remaining staging models

With the pattern established, you can now build staging models for the other three raw tables. Each follows the same structure: pull from the source, clean the data, output consistent results.

  • Create a new file called stg_program_participation.sql inside models/staging/ and paste the following:
with source as (
    select * from {{ source('raw', 'raw_program_participation') }}
),

cleaned as (
    select
        participation_id,
        student_id,
        case
            when lower(program_name) like '%career prep%' then 'Career Prep'
            when lower(program_name) like '%employer connect%' then 'Employer Connect'
            else initcap(program_name)
        end as program_name,
        cast(start_date as date) as start_date,
        case
            when end_date = '' then null
            else cast(end_date as date)
        end as end_date,
        lower(completion_status) as completion_status,
        cast(milestones_completed as integer) as milestones_completed,
        cast(total_milestones as integer) as total_milestones
    from source
)

select * from cleaned

What does this model do?

  • The CASE on program_name maps all variants (Career Prep, career prep, Career Prep Program) to a single canonical name.
  • Empty end_date strings become NULL (programs still in progress have no end date).
  • The completion_status is lowercased for consistency.
  • Save stg_program_participation.sql.
  • Create another new file called stg_grants.sql inside models/staging/ and paste the following:
with source as (
    select * from {{ source('raw', 'raw_grants') }}
),

cleaned as (
    select
        grant_id,
        funder_name,
        cast(grant_amount as integer) as grant_amount,
        cast(grant_start_date as date) as grant_start_date,
        cast(grant_end_date as date) as grant_end_date,
        target_cohorts
    from source
)

select * from cleaned

What does this model do?

The grants source is the cleanest of the four tables. This model casts the grant_amount from a string to an integer and converts the date strings to proper DATE types so downstream models can do date arithmetic.

  • Save stg_grants.sql.

Let's verify these two models compile before writing the most complex one.

  • Run the two new models by executing:
dbt run --select stg_program_participation stg_grants

You should see 2 models complete successfully.

Now for the most important staging model. The employment outcomes table has both duplicates AND the inconsistent status values (Employed, employed, Placed, hired) that make placement reporting unreliable.

  • Create a new file called stg_employment_outcomes.sql inside models/staging/ and paste this first section:
with source as (
    select * from {{ source('raw', 'raw_employment_outcomes') }}
),

deduplicated as (
    select
        *,
        row_number() over (partition by student_id, employer_name order by outcome_id) as row_num
    from source
),
  • Directly below that (still in stg_employment_outcomes.sql), add the cleaning logic:

cleaned as (
    select
        outcome_id,
        student_id,
        case when employer_name = '' then null else employer_name end as employer_name,
        case when job_title = '' then null else job_title end as job_title,
        case
            when starting_salary = '' then null
            else cast(starting_salary as integer)
        end as starting_salary,
        case
            when lower(employment_status) in ('employed', 'placed', 'hired') then 'employed'
            when lower(employment_status) = 'seeking' then 'seeking'
            else 'not_applicable'
        end as employment_status,
        case when offer_date = '' then null else cast(offer_date as date) end as offer_date,
        case when start_date = '' then null else cast(start_date as date) end as start_date,
        grant_id
    from deduplicated
    where row_num = 1
)

select * from cleaned

What does the status mapping do?

This is the transformation that fixes the core reporting problem from Step 2. The raw data had Employed, employed, Placed, and hired all meaning the same thing.

  • All four variants map to a single 'employed' value.
  • Seeking (and seeking) maps to 'seeking'.
  • Anything else becomes 'not_applicable'.

Now when a funder asks "what's our placement rate?", you can count where employment_status = 'employed' and trust the number.

  • Save stg_employment_outcomes.sql.

✔️ Awesome, I've got everything!

Great. Confirm you have saved all four staging SQL files in models/staging/.

ⓧ I'd like to double check the full code

Your stg_employment_outcomes.sql was built in two chunks. Scroll up and confirm you have:

  • source CTE pulling from raw_employment_outcomes.
  • deduplicated CTE partitioning by student_id, employer_name.
  • cleaned CTE with the status mapping (employed/placed/hired to 'employed') and WHERE row_num = 1.
  • A final select * from cleaned at the bottom.
Run the full staging layer and verify the output

All four staging models are written. Before you run the full layer, think about what you expect to happen.

When you run the staging layer, do you think dbt will create new tables or views? How many models should complete?

  • Build all staging models at once by running:
dbt run --select staging

You should see 4 models complete successfully. Because your dbt_project.yml sets +materialized: view for the staging folder, dbt creates views (not tables) for each model.

Why views instead of tables?

Views are lightweight references that always reflect the latest raw data. If the raw tables get refreshed with new exports, the staging views automatically show the cleaned version without needing to re-run dbt run.

Marts (which you build in the next step) use tables instead because they pre-compute expensive aggregations that should only rebuild when you explicitly run the pipeline.

Now let's verify that the most critical transformation actually worked. The employment status mapping was the biggest data quality problem.

  • Preview the cleaned employment outcomes by running:
dbt show --select stg_employment_outcomes --limit 10

You should see an ASCII table printed in your terminal. Look at the employment_status column. Every value is now one of: employed, seeking, or not_applicable. No more Employed, Placed, hired, or Seeking variants.

Seeing inconsistent values still?

  • Confirm your CASE statement uses lower(employment_status) in the comparison. Without lower(), the pattern match is case-sensitive and misses variants like Placed.
  • Check that you have the WHERE row_num = 1 filter in the cleaned CTE. Without it, duplicate records still appear.
  • If you see a compilation error, make sure the comma after the deduplicated CTE is present. Each CTE except the last must end with a comma.

Help me debug my stg_employment_outcomes model

Your staging layer is complete. Four raw, messy source tables are now four clean, trustworthy views. Next up, you will combine these staged tables into stakeholder-ready mart models that pre-compute the answers the Programs and Development teams actually need.

Build Stakeholder Marts

Your staging layer now produces clean, deduplicated data from every source system. But answering a real business question still requires writing complex multi-table joins and aggregations from scratch every time someone needs a number.

In this step, you will build mart models that pre-compute the answers each team needs. The Programs team gets cohort-level outcomes. The Development team gets grant-level ROI metrics. Anyone can query a mart directly without writing SQL from scratch.

In this step, get ready to:
  • Write a program outcomes mart that produces one row per cohort with placement rates and milestone metrics.
  • Write a funder report mart that produces one row per grant with cost-per-placement and ROI metrics.
  • Run the full pipeline and verify both marts produce clean, aggregated results.
Write the program outcomes mart

The Programs team needs to know how each cohort performed. That means joining students, program participation, and employment outcomes into a single table with one row per cohort.

  • Create a new file called mart_program_outcomes.sql inside your models/marts/ folder.
  • Add the source CTEs and cohort participation aggregation by pasting this code:
-- Mart: one row per cohort with program and employment metrics
with students as (
    select * from {{ ref('stg_students') }}
),

participation as (
    select * from {{ ref('stg_program_participation') }}
),

outcomes as (
    select * from {{ ref('stg_employment_outcomes') }}
),

-- Aggregate program participation by cohort
cohort_participation as (
    select
        s.cohort,
        count(distinct s.student_id) as total_students,
        count(distinct case when p.completion_status = 'completed' then p.participation_id end) as programs_completed,
        round(avg(p.milestones_completed), 1) as avg_milestones_completed,
        round(avg(p.total_milestones), 1) as avg_total_milestones
    from students s
    left join participation p on s.student_id = p.student_id
    group by s.cohort
),

What does this code do?

The first three CTEs (students, participation, outcomes) pull from the staging views you built in the previous step using {{ ref() }}. This tells dbt the dependency order so it builds staging before marts.

The cohort_participation CTE groups by cohort and counts how many programs were completed and the average milestone progress across all students.

  • Directly below the code you just added (after the trailing comma), add the employment outcomes CTE and the final SELECT:
-- Aggregate employment outcomes by cohort
cohort_outcomes as (
    select
        s.cohort,
        count(distinct case when o.employment_status = 'employed' then s.student_id end) as students_placed,
        round(avg(case when o.employment_status = 'employed' then o.starting_salary end), 0) as avg_starting_salary,
        round(avg(
            case
                when o.employment_status = 'employed' and o.offer_date is not null and s.graduation_date is not null
                then datediff('day', s.graduation_date, o.offer_date)
            end
        ), 0) as avg_days_to_employment
    from students s
    left join outcomes o on s.student_id = o.student_id
    group by s.cohort
)

-- Final output: one row per cohort with all metrics
select
    cp.cohort,
    cp.total_students,
    co.students_placed,
    round(co.students_placed::decimal / cp.total_students, 2) as placement_rate,
    cp.avg_milestones_completed,
    cp.avg_total_milestones,
    co.avg_starting_salary,
    co.avg_days_to_employment
from cohort_participation cp
left join cohort_outcomes co on cp.cohort = co.cohort
order by cp.cohort

What does this code do?

The cohort_outcomes CTE counts placed students and calculates average starting salary and days-to-employment for each cohort. It only includes students whose employment_status is employed (the cleaned value from staging).

The final SELECT joins both CTEs on cohort and computes placement_rate as the fraction of students who landed jobs. This gives the Programs team a single table to answer questions like "how did Spring 2024 compare to Fall 2024?"

  • Save models/marts/mart_program_outcomes.sql.
  • Verify this mart builds on its own by running:
dbt run --select mart_program_outcomes

You should see output confirming 1 model completed successfully, with mart_program_outcomes materialized as a table.

Seeing a compilation error?

  • Check that the trailing comma after the cohort_participation CTE is present. Without it, dbt cannot parse the next CTE.
  • Make sure there is NO comma after the closing ) of cohort_outcomes. The last CTE before select must not have a trailing comma.
  • Confirm the file is saved in models/marts/ (not models/staging/).

Why isn't my mart_program_outcomes model building?

✔️ Awesome, I've got everything!

Great. Double check you have saved your file and the run completed successfully.

ⓧ I'd like to double check the full code

Your mart_program_outcomes.sql file should contain both chunks you pasted above, in order:

  • The with keyword and three source CTEs (students, participation, outcomes), followed by the cohort_participation CTE ending with a comma.
  • The cohort_outcomes CTE (no trailing comma), followed by the final select joining both CTEs and ending with order by cp.cohort.
Write the funder report mart

The Development team needs to report grant ROI to funders. This mart produces one row per grant showing how many students were supported, how many got jobs, and what the cost-per-placement was.

  • Create a new file called mart_funder_report.sql inside models/marts/.
  • Add the source CTEs and grant outcomes aggregation by pasting this code:
-- Mart: one row per grant with funder ROI metrics
with grants as (
    select * from {{ ref('stg_grants') }}
),

outcomes as (
    select * from {{ ref('stg_employment_outcomes') }}
),

-- Aggregate employment outcomes per grant
grant_outcomes as (
    select
        g.grant_id,
        g.funder_name,
        g.grant_amount,
        g.grant_start_date,
        g.grant_end_date,
        g.target_cohorts,
        count(distinct o.student_id) as students_supported,
        count(distinct case when o.employment_status = 'employed' then o.student_id end) as students_placed,
        round(avg(case when o.employment_status = 'employed' then o.starting_salary end), 0) as avg_starting_salary
    from grants g
    left join outcomes o on g.grant_id = o.grant_id
    group by
        g.grant_id,
        g.funder_name,
        g.grant_amount,
        g.grant_start_date,
        g.grant_end_date,
        g.target_cohorts
)

What does this code do?

This mart joins stg_grants with stg_employment_outcomes on grant_id. Each employment outcome record is linked to the grant that funded it.

The grant_outcomes CTE counts distinct students per grant and separately counts only those with employment_status = 'employed' to get placements.

  • Directly below the code you just added, add the final SELECT that computes placement rate and cost-per-placement:
-- Final output: one row per grant with ROI metrics
select
    grant_id,
    funder_name,
    grant_amount,
    grant_start_date,
    grant_end_date,
    target_cohorts,
    students_supported,
    students_placed,
    case
        when students_supported > 0
        then round(students_placed::decimal / students_supported, 2)
        else 0
    end as placement_rate,
    avg_starting_salary,
    case
        when students_placed > 0
        then round(grant_amount::decimal / students_placed, 0)
        else null
    end as cost_per_placement
from grant_outcomes
order by grant_start_date

What does this code do?

The placement_rate divides placed students by total students supported under that grant. The cost_per_placement divides the grant amount by the number of placed students.

These are the exact numbers a funder like Robin Hood Foundation wants to see. Instead of writing this join every time, anyone on the team can query mart_funder_report directly.

  • Save models/marts/mart_funder_report.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before running the full pipeline.

ⓧ I'd like to double check the full code

Your mart_funder_report.sql file should contain both chunks you pasted above, in order:

  • The with keyword with two source CTEs (grants, outcomes), followed by the grant_outcomes CTE ending with a closing ) and no comma.
  • The final select computing placement_rate and cost_per_placement, ending with order by grant_start_date.
Run the full pipeline and verify both marts

Both mart files are saved. Now you will run the entire pipeline (staging views + mart tables) in one command and check the funder report output.

  • Build all models in the project by running:
dbt run

You should see 6 models complete successfully: 4 staging views and 2 mart tables.

Why run the full pipeline?

Running dbt run without --select builds everything in dependency order. dbt reads the {{ ref() }} calls in your models and builds staging first, then marts. This is how you would rebuild the full pipeline in production after new data arrives.

Before you preview the funder report, what do you expect to see? Think about how many grants exist in the raw data and what columns the mart should produce.

  • Preview the funder report mart by running:
dbt show --select mart_funder_report --limit 10

You should see a table with one row per grant (3 rows total) showing columns for grant_id, funder_name, students_supported, students_placed, placement_rate, avg_starting_salary, and cost_per_placement.

What should I see?

The Robin Hood Foundation grant (GRT001) should show 4 students supported, 4 placed, with a placement rate of 1.0 and a cost-per-placement of 37500. The Carnegie Corporation grant (GRT003) should show students in seeking status with 0 placements and a null cost-per-placement.

Compare this to the messy raw data you saw in Step 2. The inconsistent status values (Employed, employed, Placed, hired) are now unified under employed, duplicates are gone, and the funder can see their ROI in a single row.

Seeing 0 rows or unexpected nulls?

  • If all values are null, check that your staging models ran first. Run dbt run (without --select) to rebuild everything in the correct order.
  • If students_supported is 0, check that the join in mart_funder_report.sql uses g.grant_id = o.grant_id (not student_id).
  • Make sure generate_raw_data.py was run previously. The raw tables must exist in basta_pipeline.duckdb for the pipeline to work.

Why is my dbt funder report mart returning unexpected results?

Your pipeline now transforms raw, messy data into stakeholder-ready analytics in a single command. Next up, you will export these marts and build a visual funder dashboard in Power BI.

Build a Funder Dashboard in Power BI

Your mart tables now hold clean, pre-computed answers to the questions funders actually ask. The Development team can query them directly, but what they really need is a visual report they can share in a funder meeting.

In this step, you will export your mart data as CSV files and build a Power BI dashboard that shows program impact and grant ROI at a glance.

In this step, get ready to:
  • Export mart tables to CSV files that Power BI can import.
  • Build a funder-ready dashboard with placement rates, salary data, and cost-per-placement metrics.
Export mart data to CSV

Power BI Desktop imports local CSV files easily, so the simplest way to connect your pipeline output to a dashboard is to export each mart table as its own CSV.

  • Create a new file called export_for_powerbi.py in your project root (the same folder as generate_raw_data.py) and paste the following code:
import duckdb
import os

os.makedirs('exports', exist_ok=True)

con = duckdb.connect('basta_pipeline.duckdb', read_only=True)

con.execute("""
    COPY (SELECT * FROM mart_program_outcomes)
    TO 'exports/program_outcomes.csv' (HEADER, DELIMITER ',')
""")
print("Exported: exports/program_outcomes.csv")

con.execute("""
    COPY (SELECT * FROM mart_funder_report)
    TO 'exports/funder_report.csv' (HEADER, DELIMITER ',')
""")
print("Exported: exports/funder_report.csv")

con.close()
print("\nDone! Import these CSV files into Power BI Desktop.")

What does this code do?

  • os.makedirs('exports', exist_ok=True) creates an exports/ folder if it does not already exist.
  • The script opens the DuckDB database in read-only mode so it cannot accidentally alter your pipeline data.
  • Each COPY statement selects all rows from a mart table and writes them to a CSV file with headers.
  • Save export_for_powerbi.py.
  • Run the export script:
python export_for_powerbi.py

You should see output confirming both files were exported:

Exported: exports/program_outcomes.csv
Exported: exports/funder_report.csv

Done! Import these CSV files into Power BI Desktop.
  • Confirm the files exist by running:
ls exports/

You should see funder_report.csv and program_outcomes.csv listed.

Don't see the exports folder?

  • Make sure your terminal is in the project root folder (the same folder that contains basta_pipeline.duckdb).
  • Check that your virtual environment is still activated. If not, activate it and re-run the script.
  • help me debug why export_for_powerbi.py did not create the exports folder

✔️ Awesome, I've got everything!

Great. Make sure you have saved export_for_powerbi.py and both CSV files are in the exports/ folder.

ⓧ I'd like to double check the full code

import duckdb
import os

os.makedirs('exports', exist_ok=True)

con = duckdb.connect('basta_pipeline.duckdb', read_only=True)

con.execute("""
    COPY (SELECT * FROM mart_program_outcomes)
    TO 'exports/program_outcomes.csv' (HEADER, DELIMITER ',')
""")
print("Exported: exports/program_outcomes.csv")

con.execute("""
    COPY (SELECT * FROM mart_funder_report)
    TO 'exports/funder_report.csv' (HEADER, DELIMITER ',')
""")
print("Exported: exports/funder_report.csv")

con.close()
print("\nDone! Import these CSV files into Power BI Desktop.")
Import data into Power BI Desktop

With the CSV files ready, you can now bring them into Power BI as data sources for your dashboard.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • If a welcome screen appears, close it to reach the blank canvas.
  • On the Home tab in the ribbon, click Get data.
  • Select Text/CSV from the list of data sources.
  • Navigate to your project's exports/ folder and select program_outcomes.csv.
  • Click Open.
  • In the preview window that appears, click Load to import the data directly.

You should see program_outcomes appear in the Fields pane on the right side of the screen.

  • Repeat the import for the second file: click Get data on the Home tab again.
  • Select Text/CSV again.
  • Navigate to exports/ and select funder_report.csv.
  • Click Open, then click Load.

Both program_outcomes and funder_report now appear in the Fields pane. You can expand each one to see its columns.

Don't see both tables in the Fields pane?

  • Make sure you clicked Load (not Transform Data) in the preview window.
  • If you accidentally clicked Transform Data, you can click Close & Apply on the Home tab to load the data into the model.
  • help me get my CSV files loaded into Power BI Desktop
Build the funder dashboard visuals

With data loaded, you can now create the three visuals that tell the funder story: how many students were placed, how placement varies by cohort, and what each grant achieved.

  • Make sure you are on the Report view (the canvas icon in the left sidebar).
  • In the Visualizations pane on the right, click the Card visual (a rectangle with a large number icon).
  • From the Fields pane, expand funder_report and drag students_placed into the Card's data field.
  • The Card now displays the total number of students placed across all grants.

Why a Card visual?

Cards show a single headline number. Funders want to see the top-line impact immediately: "How many students got jobs?" The Card answers that in one glance.

  • Click on an empty area of the canvas to deselect the Card.
  • In the Visualizations pane, click the Clustered bar chart icon.
  • From Fields, expand program_outcomes and drag cohort to the Y-axis well.
  • Drag placement_rate to the X-axis well.
  • You should see a bar for each cohort (Spring 2024, Fall 2024, Spring 2025) with their respective placement rates.

Now add the final visual that breaks down each grant's performance.

  • Click on an empty area of the canvas to deselect the bar chart.
  • In the Visualizations pane, click the Table visual icon.
  • From Fields, expand funder_report and drag the following fields into the table's Columns well, one at a time:
  • funder_name.
  • grant_amount.
  • students_placed.
  • cost_per_placement.

The Table visual now shows one row per funder with their grant amount, how many students they helped place, and the cost per placement.

What does cost per placement tell a funder?

Cost per placement divides the total grant amount by the number of students who secured employment. A lower number means the program placed students more efficiently with that funder's money. This is the metric funders use to evaluate return on investment.

Before you look at the finished dashboard, what three things should a funder be able to answer from this single page?

Look at your completed dashboard. You should see a Card with total students placed, a bar chart comparing placement rates across cohorts, and a table showing each funder's grant amount and cost per placement.

Visuals showing unexpected values?

  • If the Card shows a sum that seems too high, click the value in the Fields well and change the aggregation from Sum to Sum (it should already be Sum for a count field). Ensure you dragged students_placed from funder_report, not program_outcomes.
  • If the bar chart shows no bars, confirm you placed cohort on the Y-axis and placement_rate on the X-axis (not the other way around).
  • help me fix my Power BI visuals that show wrong data

Secret mission

Add Data Quality Tests and Generate a Data Catalog

Your pipeline transforms data reliably, but how do you know it stays reliable? A pipeline without tests breaks silently. A pipeline without documentation forces every new team member to reverse-engineer the logic. In this mission, you will add automated data quality tests and generate a browsable data catalog with full lineage tracking.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • Local DuckDB database file (basta_pipeline.duckdb) containing all raw tables, staging views, and mart tables.
  • Exported CSV files in the exports/ folder.
  • Raw data CSV files in the raw_data/ folder.
  • Python virtual environment inside the project folder.
  • The dbt docs serve process (if you completed the Secret Mission).

Keep everything running

No action needed. Choose this if you plan to keep experimenting with the pipeline or want to rebuild it later.

  • The .duckdb file and your project folder persist on your machine at no cost.
  • You can re-run dbt run anytime to rebuild all models from the raw data.
  • To restart the data catalog, run dbt docs generate then dbt docs serve from inside your project folder with the virtual environment activated.

Pause - I'll come back to this later

Stop running processes and deactivate the virtual environment to free up terminal resources. Your files stay on disk.

  • If dbt docs serve is still running, switch to that terminal and press Ctrl+C to stop it.
  • Deactivate your virtual environment by running:
deactivate
  • Your project folder and all files remain on your Desktop. To resume later, navigate to the project folder, activate the virtual environment, and run dbt run to rebuild.

Delete - I don't want to use this again

Remove all project files and start fresh. This permanently deletes everything you built.

  • If dbt docs serve is still running, switch to that terminal and press Ctrl+C to stop it.
  • Deactivate your virtual environment by running:
deactivate
  • Delete the entire project folder from your Desktop. This removes basta_pipeline.duckdb, all Python scripts, dbt models, raw data, exports, and the virtual environment.

On macOS or Linux, run:

cd ~/Desktop
rm -rf basta_data_pipeline

On Windows, run:

cd $HOME\Desktop
Remove-Item -Recurse -Force basta_data_pipeline
  • If you created a Power BI report file, delete it separately from wherever you saved it on your machine.

Nice Work!

Nice Work!

You just built a complete nonprofit data pipeline from scratch. Raw, messy exports from multiple systems are now clean, tested, and powering a funder-ready dashboard.

You've learned how to:

  • Build a local ELT pipeline using Python and DuckDB that ingests multi-source nonprofit data, deduplicates records, and standardizes inconsistent field values into a single warehouse.
  • Create a dbt project with staging and mart layers that transform raw data into stakeholder-specific analytics, including cost-per-placement and placement rates by cohort.
  • Connect pipeline output to Power BI and build a funder report dashboard showing program impact metrics that a Development team can share directly with grant-makers.
  • Secret Mission: Added automated data quality tests and generated a browsable data catalog with lineage tracking.

Ready to quiz yourself?