Home / Projects / Enterprise Spend Analysis Pipeline

Data Engineering · Analytics Engineering

Enterprise Spend Analysis Pipeline

A controlled analytics pipeline with governed reporting and operational freshness monitoring.

View architecture ↓ View GitHub ↗
Role Data Engineering · Analytics Engineering
Tools Python · MySQL · dbt · Airflow · Power BI · Grafana · Streamlit
Skills Modelling · ETL · orchestration · validation · monitoring
Scale 5,000 expenses · 20,000 events · 125 vendors
Outcome 17 models · 73 tests · 6/6 freshness rules

My approach

Separate the data boundaries

I kept real DEFRA profiling separate from the fictional corporate analytics path. This prevents synthetic findings from being presented as public-data facts.

Govern the transformation

I used stable keys, MySQL controls, dbt models, tests, and reporting marts to keep business logic traceable.

Test and observe the complete path

I used Airflow, reconciliation, freshness checks, and repeat loads before reporting. The Power BI monitoring page, Grafana, and Streamlit read the same governed monitoring views.

The problem

Trust must survive every transformation.

Spend data is useful only when users can trust its source, transformation, and totals.

Data quality

Duplicate, incomplete, or inconsistent records can change financial totals.

Pipeline reliability

Repeated loads must not create duplicate records.

Operational visibility

A failed or stale load must be visible before users act on the reporting output.

Explore the implementation concepts: dbt modelling → Airflow orchestration → Data freshness →

How the system works

One controlled path to reporting and monitoring.

Power BI business pages read governed marts. The Power BI monitoring page, Grafana, and Streamlit read separate freshness audit views.

Design decisions

The design protects meaning.

Each decision controls a specific risk in the path from source data to reporting.

01

Why dbt marts feed Power BI

Business logic stays in the governed data layer. Power BI does not recreate the same logic.

02

Why duplicate records are flagged

A duplicate can contain valid business information. The pipeline does not remove it without evidence.

04

Why freshness is separate from business date

Historical data can be valid even when its business date is old. The monitor tests load recency and period completeness as separate controls. Explore data freshness →

Concepts used

Explore the controls behind the system.

The case study shows where each concept sits. The Concept Library explains how it works.

Decision evidence

Question → visual → decision.

Business and monitoring views each have one decision job. Every screenshot is evidence, not decoration.

01 · Portfolio control

Where is spend concentrated?

Why this visual exists. The KPI row, time trend, currency mix, and ranked bars separate scale, movement, and concentration.

Enterprise spend executive overview with spend, contract compliance, monthly trend, department, currency, and vendor visuals.
Finding

£1.16bn spans 5,000 expenses. Contract compliance is 73.20%, and October has the highest monthly spend at £115.55m.

Interpretation

The overview identifies where to investigate. It does not prove vendor risk or control failure by itself.

Decision

Move from the portfolio view to contract and vendor controls before setting an intervention.

02 · Contract control

Which control gap needs priority?

Why this visual exists. Exposure by department and category is shown with vendor risk, compliance, and a review watchlist.

Contract and vendor performance dashboard with non-compliant spend, high-risk spend, category exposure, vendor performance, and a watchlist.
Finding

£305.92m is non-compliant spend. No active matching contract is the largest cause at £116.95m. Seven high-risk vendors account for £44.35m.

Interpretation

Non-compliance is not one problem. Contract coverage and high-risk vendor performance require different controls.

Decision

Prioritise contract matching and coverage, then review the seven high-risk vendors separately.

03 · Operations

Where does approval fail?

Why this visual exists. Duration, SLA breaches, priority, and department are combined to separate slow flow from service failure.

Approval and operational performance dashboard with cycle time, SLA breaches, monthly trend, department matrix, and heatmap.
Finding

Procurement has the longest mean approval cycle at 64.04 hours. Research has the highest SLA breach rate at 42.04%.

Interpretation

The departments show different failure patterns. Mean duration and breach rate are not interchangeable.

Decision

Investigate workflow delay in Procurement and SLA control in Research. Do not apply one response to both.

04 · Reporting readiness

Is the latest recorded output ready for reporting?

Why this visual exists. The Power BI monitoring page places load recency, rule coverage, recent runs, and rule-level evidence beside the governed business report.

Power BI data freshness and pipeline health page showing a passing status, six passed rules, zero failures, load age, recent runs, severity coverage, and rule evidence.
Finding

The latest recorded check passed all six freshness rules with zero failures and a 0.00-hour load age.

Interpretation

The page confirms reporting readiness at the recorded check time. It is an imported audit snapshot, not a live service guarantee.

Decision

Confirm the six control results before using the business pages. Use Grafana when near-real-time operational refresh is required.

05 · Pipeline health

Is the latest pipeline output safe to use?

Why this visual exists. Grafana combines overall status, load age, rule results, recent runs, and failure history in one operational view.

Grafana data freshness dashboard showing a healthy pipeline, six passed checks, zero failed checks, load age, recent runs, and rule results.
Finding

The first live setup check detected an operational load age of 243.662 hours. After a controlled reload, the latest run passed all six rules with zero failures.

Interpretation

The monitor detects stale delivery without treating the fixed historical business period as a live-feed failure.

Decision

Pause use when a critical rule fails. Refresh the controlled load, rerun the checks, and confirm recovery before reporting continues.

06 · Rule evidence

Can an analyst inspect the same controls without Grafana?

Why this visual exists. Streamlit provides a lightweight Python view of the same governed MySQL monitoring contract.

Streamlit data freshness dashboard showing current status, six rule results, observed and expected values, and monitoring-run history.
Finding

The application query returned PASS, six passed rules, zero failed rules, and six rule-level evidence rows.

Interpretation

Grafana and Streamlit do not calculate freshness independently. Both read four shared, read-only MySQL views.

Decision

Use Grafana for continuous local operations and Streamlit for transparent inspection and demonstration.

Source: original Power BI, Grafana, and Streamlit screenshots from the project repository. Figures use the project dataset and evidence boundaries stated above.

Evidence

Controls make the result usable.

The case study must show both the analytical result and the checks that support it.

73

dbt tests check model quality.

24 / 24

Reconciliation checks compare records and totals.

6 / 6

Freshness rules passed after failure detection and controlled recovery.

Implementation proof · Python

Verify the rerun, not only the first load.

The loader runs twice and compares row counts. It stops the pipeline if a rerun changes the result.

View implementation on GitHub ↗ 
first_counts = load_once(connection, project_root)
report = {"first_run_counts": first_counts}
if args.verify_idempotency:
    second_counts = load_once(connection, project_root)
    report["second_run_counts"] = second_counts
    report["idempotency_verified"] = first_counts == second_counts
    if not report["idempotency_verified"]:
        raise RuntimeError("Row counts changed on rerun")