Student Data Warehouse & Academic Analytics Pipeline

An end-to-end data engineering project: four simulated university source systems producing deliberately messy extracts, a Python ETL pipeline that cleans, validates and quarantines them, a normalized PostgreSQL warehouse with full referential integrity, and an analytics layer of SQL views feeding Power BI.

Measured on the default scale:

Source rows ingested	1,347,592 across 15 extracts
Rows loaded into core	1,329,778 across 16 tables
Rows quarantined	121,488 (9.0%), each retained with its payload
Defects deliberately injected	380,362 across 59 defect classes
Quality checks	54 rules across 7 quality dimensions
End-to-end runtime	~30 seconds
Constraints in core	45 CHECK · 20 FK · 25 UNIQUE · 16 PK

---

The idea

Universities keep student data in systems that were never designed to talk to each other — an SIS, an LMS, an examination system, a placement database. The extracts they produce disagree about spelling, date formats, and which records exist.

This project takes that as the starting condition rather than assuming clean inputs. The synthetic generator injects 380,362 realistic defects — gender in 21 spellings, packages written as "23.77 L", dates in six formats, orphaned foreign keys, near-duplicate rows differing only in casing — and then records exactly what it broke. The pipeline's quality report is scored against that ground truth, so it can answer the question most quality dashboards cannot:

not "what did we find?", but "what did we miss?"

---

Architecture

data/raw/*.csv          15 extracts from 4 simulated source systems
      │                 (deliberately dirty, defects logged to ground truth)
      ▼
  staging.*             all TEXT, unconstrained, + lineage columns
      │                 nothing is rejected here — bad rows must be measurable
      ▼
  transform             standardise · parse · deduplicate · derive
      │                 (never decides acceptance)
      ▼
  validate              54 rules · BLOCKING rejects, WARNING reports
      │                 rejects → quality.rejected_record with JSONB payload
      ▼
  core.*                3NF · 16 tables · fully constrained
      │
      ▼
  analytics.*           14 views — the only thing BI ever touches
      │
      ▼
  Power BI

Four schemas, each with one job:

Schema	Contents	Constrained	Role
staging	15 tables, all TEXT	No	Land extracts verbatim
core	16 tables, 3NF	Fully	Production model
analytics	14 views	—	Read model for SQL and BI
quality	4 tables	Fully	Run lineage, rules, results, quarantine

---

Quick start

Requires PostgreSQL 14+ and Python 3.10+.

# 1. Database
createdb university_dw

# 2. Environment
cp .env.example .env          # then fill in your credentials
python -m venv .venv
.venv/bin/pip install -r requirements.txt

# 3. Generate the dirty source extracts (~20s)
.venv/bin/python -m etl.generate_data --scale default

# 4. Build the warehouse (~30s)
.venv/bin/python -m etl.run_pipeline --scale default

# 5. Explore
psql -d university_dw -f sql/analytics.sql

Optional:

.venv/bin/python -m scripts.benchmark_indexes     # measure index impact
.venv/bin/python -m scripts.generate_er_diagram   # regenerate the ER diagram

Three scale profiles are available — small (~34k rows, for a fast smoke test), default (~1.35M, what every number in the docs was measured on), and large (~5M, for stress-testing the index strategy).

---

What's here

├── etl/
│   ├── generate_data.py   synthetic universe + logged defect injection
│   ├── dirty.py           the defect injectors themselves
│   ├── extract.py         raw CSV → staging, with lineage
│   ├── transform.py       standardise, parse, deduplicate, derive
│   ├── validate.py        rule engine; accept/reject/quarantine
│   ├── load.py            surrogate-key resolution → core
│   └── run_pipeline.py    orchestrator
├── sql/
│   ├── schema.sql         4 schemas, 35 tables, 106 core constraints, rule catalogue
│   ├── indexes.sql        index strategy — including what was NOT indexed
│   ├── views.sql          14 analytics views
│   └── analytics.sql      29 analytical queries
├── quality/
│   └── quality_checks.py  data-quality report generator
├── scripts/
│   ├── benchmark_indexes.py    measured before/after index impact
│   └── generate_er_diagram.py  ER diagram from the live schema
├── docs/
│   ├── data_model.md           normalization decisions and trade-offs
│   ├── optimization_report.md  measured query timings
│   ├── interview_notes.md      talking points, grounded in the code
│   └── er_diagram/             auto-generated Mermaid ERD
└── dashboard/
    └── README.md          Power BI connection, model, and DAX

---

Design decisions worth reading about

Each of these is a real trade-off, documented where it was made.

Staging is untyped on purpose. If staging.exam_scores.marks_obtained were NUMERIC, the row containing "48.5 marks" would be rejected during COPY and the only trace would be a line number in a log. Landing everything as TEXT puts the bad row in the database where it can be counted, reported and returned to the source team. → docs/data_model.md

Transform and validate are separate. Transform standardizes and never judges; validate decides. That separation lets the report distinguish "the source sent garbage" from "we threw the row away" — different conversations with different teams.

Not every bad value costs you the row. A malformed email fails a CHECK, but the student is still a real student — the address is nulled and the record survives. Row-level rejection is reserved for rows that cannot be represented at all. → etl/load.py::quarantine_values

Surrogate keys, natural keys retained. All FKs are integers; source IDs are kept UNIQUE for lineage. Costs a key-resolution pass in the loader, buys key stability and much narrower indexes on a 655k-row fact table.

Students have no dept_sk. Department is reachable via program. Storing it on the student would be a transitive dependency (3NF violation) and would let a student sit in a department their program doesn't belong to.

One index measurably didn't help, and the docs say so. A covering index on attendance produces an index-only scan for selective queries but is correctly ignored by the planner for full-table aggregates — 48.3ms vs 44.8ms, no material difference. It is justified by the point-lookup path and costs 20MB. → docs/optimization_report.md

---

Sample results

Placement rate by department, from sql/analytics.sql §3.1:

dept	graduates	interned	offered	placed	rate
CSE	516	242	432	408	79.1%
MAT	518	261	379	361	69.7%
ECE	549	261	392	370	67.4%
BIO	552	266	306	288	52.2%
CIV	186	121	96	93	50.0%
PHY	195	121	97	94	48.2%

Attendance genuinely predicts performance — Pearson r = 0.44 across 2,119 students, which is the range real academic data shows:

attendance band	students	avg course %
90–100%	1,211	65.9
80–90%	835	58.1
70–80%	71	51.2

---

Limitations

Stated plainly, because they'd come up anyway.

• No .pbix in the repo. Power BI Desktop is Windows-only; this was built and verified on Linux. The connection procedure, semantic model, relationships and DAX are all in dashboard/README.md and the modelling work is done in sql/views.sql — but the report itself is unbuilt and untested.
• Synthetic data. Realistic in shape and defect profile, but generated. The correlations are ones I designed in (deliberately: attendance/grade r ≈ 0.44, not the 0.93 the first version produced, which was an obvious tell).
• Full rebuild per run. Correct at 1.3M rows, wrong at 1.3B. Incremental loading, partitioning and orchestration are discussed in docs/interview_notes.md.
• No unit tests. Verified end-to-end and against injected ground truth, but transform.py's primitives are pure functions that deserve unit tests.
• Rejected facts cascade. An orphaned enrollment orphans its scores and attendance. A production warehouse would insert inferred dimension members and backfill instead of dropping the facts.

---

Stack

Python 3.12 · pandas · NumPy · PostgreSQL 16 · SQLAlchemy 2 · psycopg 3 · Power BI

No Docker, Airflow, Spark or cloud services. At this data size they would be decoration; the point at which each becomes necessary is written up in docs/interview_notes.md.