End-to-End Data Pipeline Case Study

Regional Physics Outreach Simulation

This project engineering initiative simulates a regional physics outreach programme spanning 80 schools. It models how socioeconomic indices, geography, and module topics impact delivery costs and student engagement.

THE GOAL: Diagnose budget variances and operational bottlenecks across regional school districts.

01 / The Pipeline Architecture

01 Python Data Synthesis

02 Excel & Power Query ETL & Cleansing

03 Power BI Star Schema & Storytelling

02 / Technical Competencies Applied

Python

Randomised Synthetic Data Programmatic Bias Relational Datasets Pandas NumPy

ETL Layer

Data Cleansing Text Transformation Pivot Tables M-code Power Query ETL Pipelines

Power BI

Star Schema Relational Modelling DAX Metrics Time-Intelligence Interactive Dashboards

Phase 1

Programmatic Data Synthesis (Python)

Instead of static data, I used Python to generate three relational tables (booking_system.csv, invoices_receipts.csv, digitized_feedback.csv). To test pipeline resilience, I programmatically injected real-world anomalies, including data structural chaos and orphan records.

📈 Built-In Data Trends & Biases

Geographic & Deprivation Bias: Programmed a rule where schools further from campus (>50 miles) automatically score lower on the deprivation index (representing remote, underfunded areas) and book significantly fewer sessions.
Cost Escalation Trend: Implemented a time-based multiplier causing operational costs to rise by up to 40% later in the academic year to simulate end-of-year budget strains and staff overtime.
Domain Specific Constraints: Configured “Quantum Mechanics” to carry a mandatory 40% equipment maintenance cost premium and forced “Astrophysics” to simulate high staff-hour drains.

data_synthesis.py

import pandas as pd
import numpy as np
import random

# 1. GENERATE DIMENSIONAL DATA: Schools with geographic & wealth bias
schools_data = []
for i in range(1, 81):
    distance = random.uniform(2.0, 95.0)
    deprivation = random.randint(1, 4) if distance > 50 else random.randint(5, 10)
    schools_data.append({
        "School_ID": f"SCH-{1000 + i}",
        "Deprivation_Index_Score": deprivation,
        "Distance_Miles": round(distance, 1),
        "Booking_Status": "Completed" if random.random() > 0.1 else "Cancelled"
    })
df_schools = pd.DataFrame(schools_data)

# 2. GENERATE TRANSACTION DATA: Financial lines with operational trends
financials_data = []
feedback_data = []

for idx, school in df_schools.iterrows():
    num_bookings = random.randint(1, 2) if school["Distance_Miles"] > 50 else random.randint(3, 6)
    if school["Booking_Status"] != "Completed":
        num_bookings = 1
        
    for b in range(num_bookings):
        event_id = f"EVT-200{idx}-{b}"
        base_budget = random.uniform(150.0, 300.0)
        domain = random.choice(["Quantum Mechanics", "Mechanics", "Astrophysics"])
        
        for exp in ["Travel", "Materials", "Catering", "Equipment Maintenance"]:
            financials_data.append({
                "Transaction_ID": f"INV-2026-{random.randint(10000, 99999)}",
                "Event_ID": event_id,
                "School_ID": school["School_ID"],
                "Physics_Domain": domain,
                "Expense_Type": exp,
                "Actual_Amount": round(base_budget * 0.2, 2)
            })
            
        if school["Booking_Status"] == "Completed":
            feedback_data.append({
                "Feedback_ID": f"FDB-{9000 + len(feedback_data)}",
                "Event_ID": event_id,
                "Attendance_Count": random.randint(25, 90),
                "Enjoyment_Score": random.randint(4, 5) if domain != "Mechanics" else random.randint(2, 4),
                "Qualitative_Comments": random.choice(["AMAZING!", "Mind-blown", "Interesting"])
            })

df_financials = pd.DataFrame(financials_data)
df_feedback = pd.DataFrame(feedback_data)

# 3. INJECT DATA MESSINESS (Downstream ETL traps)
df_financials["Physics_Domain"] = df_financials["Physics_Domain"].apply(
    lambda x: f" {x.lower()} " if random.random() > 0.85 else x
)
null_indices = df_feedback.sample(frac=0.05).index
df_feedback.loc[null_indices, "Event_ID"] = None

# 4. EXPORT RECONCILED DATASETS
df_schools.to_csv("booking_system.csv", index=False)
df_financials.to_csv("invoices_receipts.csv", index=False)
df_feedback.to_csv("digitized_feedback.csv", index=False)

⚠️ Injected Data Quality Issues (The ETL Trap)

Referential Integrity Gaps: Stripped out Event_ID values from exactly 5% of the feedback records to simulate manual data entry omissions.

Phase 2

Excel & Power Query (ETL & Data Cleansing)

With the raw, messy CSV files generated, I built an automated Power Query pipeline to transform the data into an enterprise-ready format.

› 1. Centralized Data Sourcing

Action: Connected Power Query directly to the three synthesized local CSV files using dynamic file directory parameters.
Purpose: Avoided copy-pasting raw logs by establishing an automated file-refresh channel that re-imports new data inputs instantly.

› 2. Structural Separation

Action: Isolated dimensional tracking rules across separate schema pipelines instead of appending everything into a single massive, flat file.
Purpose: Prepares data for relational operations without losing source context, enabling Power BI to easily link transactional logs with background details.

Outreach_Data_Transformation_Layer.xlsx

› 3. Normalization and Reducing Redundancy

Action: Structured sheets to reduce data entry fields for each event by splitting transactions and feedback from master school attributes.
Purpose: Shifting historical files into a relational star schema layout removes duplication, reduces storage overhead, and speeds up calculation engines.

› 4. Optimization for Ingestion

Action: Applied data cleansing operations: trimmed leading/trailing spaces, standardized inconsistent text casing, and handled null relational keys.
Purpose: Consolidating text patterns and structural gaps early prevents analytical report errors and avoids repeating transformations later.

Phase 3

[YOUR MAIN SECTION TITLE]

[Your introductory paragraph describing the overall objective, technology stack, and focus area of this specific phase.]

[Heading for Left Card]

[A brief sub-heading or secondary description paragraph guiding the reader.]

[Bullet Concept 1]: [Detailed explanation of your architectural decision or design step.]
[Bullet Concept 2]: [Detailed explanation of your architectural decision or design step.]
[Bullet Concept 3]: [Detailed explanation of your architectural decision or design step.]

[Interactive Canvas or Box Title]

[Heading for Left Card – Technical Logic]

[A brief description explaining your syntax choices, calculation constraints, or optimization rules.]

[Logic Rule 1]: [Explanation of how you implemented a specific rule or formula package.]
[Logic Rule 2]: [Explanation of how you implemented a specific rule or formula package.]

[script_filename.ext]

// 1. Write comments like this to guide users
Formula_Name_A = FUNCTION_NAME(table_name[column_name])

// 2. Complex calculation line placeholder
Formula_Name_B = 
NESTED_FUNCTION(
    [Formula_Name_A],
    table_name[attribute_name],
    0
)

Phase 4

Data Storytelling & Insights (Power BI Dashboard)

The final layer converts the data into actionable insights for university stakeholders. The dashboard focuses on three core pillars: Financial Performance & Strain, Operational Accessibility & Equity, and Qualitative Impact (Student Engagement).

Live Physics Outreach Performance Dashboard Canvas

› Dashboard Analytical Framework

DAX Metrics: Created robust measures tracking total actual spend vs budget allocation, highlighting the exact moments where the 40% cost escalation breached budget envelopes.
Visuals: Line charts showing cost trajectory over time and domain-specific cost distribution (proving the high overhead of Quantum Mechanics).

› Visualising Data with Power BI

1. Gauge Chart: Budgetary Threshold Tracking

What it describes: This visual tracks gross expenditure against total financial envelopes, displaying a total spend of £62.54K against an allocated budget baseline of £57.70K.
Strategic value: It serves as an immediate executive health check, clearly flagging budget overruns and operational deficits to program directors at a glance.

2. Matrix Table: Financial Breakdown & Cost Efficiency

What it describes: This matrix cross-references physics domains against quarterly data to contrast total operational spending alongside the “Cost per Head” delivery metric.
Strategic value: It isolates exactly where financial strain occurs, revealing that high-overhead modules like Quantum Mechanics spike delivery costs up to £13.5 per student.

3. Dual-Axis Combo Chart: Cumulative Operational Trends

What it describes: This multi-axis visual maps total monthly financial outlays alongside “Total Minds Sparked” (student attendance counts) across the calendar year.
Strategic value: It bridges financial costs directly with community impact, allowing stakeholders to easily monitor seasonal project surges and overall program performance.

4. Horizontal Bar Chart: Impact by Physics Domain

What it describes: This chart ranks the core educational subjects by cross-referencing qualitative feedback scores against student survey responses.
Strategic value: It highlights student engagement levels, proving that high-cost topics like Quantum Mechanics and Astrophysics generate the highest programmatic satisfaction.

Visuals Matrix