TfL Analytics Mart

An end-to-end data engineering portfolio project built on public TfL Tube line status data.

Status: v1 complete. The project ingests TfL API data, preserves raw JSON, loads snapshots into BigQuery, transforms them with dbt, runs on a GitHub Actions schedule and then displays a simple Data Studio table.

---

Architecture

TfL Unified API
      |
      v
Python ingestion script
      |
      +-- local raw JSON
      +-- Azure Blob Storage
      +-- BigQuery raw table
              |
              v
dbt staging model
      |
      v
dbt fact model
      |
      v
dbt marts
      |
      v
Analysis SQL + Data Studio table

In this project:

• Azure Blob Storage is the raw file archive.
• BigQuery is the analytical warehouse.
• dbt reshapes the raw JSON into analysis-ready models.
• GitHub Actions runs tests and refreshes the pipeline on a schedule.

---

What It Does

fetch_tfl.py calls the TfL Unified API endpoint for London Underground line statuses:

https://api.tfl.gov.uk/Line/Mode/tube/Status

Each run:

1. Fetches the current Tube line statuses.
2. Validates the response shape.
3. Saves a timestamped raw JSON file locally under data/raw/tfl/.
4. Uploads the same raw file to the private Azure Blob container raw-tfl.
5. Appends the snapshot to BigQuery.

The raw BigQuery table is:

tfl-analytics-mart.raw_tfl.line-status-snapshots

Each row in the raw table represents one API snapshot. The nested data column contains the full TfL response for that fetch.

---

dbt Models

The dbt project lives in:

tfl_analytics/

Current model chain:

source('raw_tfl', 'line_status_snapshots')
        |
        v
stg_tfl_line_statuses
        |
        v
fct_line_status_snapshots
        |
        +-- mart_line_status_counts
        +-- mart_daily_line_reliability

Models are built in BigQuery under:

tfl-analytics-mart.dbt_cameron

Model	Grain	Purpose
stg_tfl_line_statuses	One row per Tube line per API snapshot	Flattens the raw nested JSON
fct_line_status_snapshots	One row per Tube line per API snapshot	Adds status flags for analysis
mart_line_status_counts	One row per line and status	Counts observed statuses by line
mart_daily_line_reliability	One row per line per day	Calculates daily Good Service and disruption percentages

Reliability figures are based on collected snapshots, not exact TfL uptime.

---

BI Output

A simple Data Studio table is connected to the dbt mart layer. It shows that the transformed data can be consumed by a BI tool.

This is intentionally a lightweight BI output instead of a full dashboard. The main purpose of this project is the data engineering flow: ingestion, raw preservation, warehouse loading, dbt modelling plus scheduled automation.

---

Analysis Queries

Example SQL analysis queries live in:

analysis/

Query	Question
top_disrupted_lines.sql	Which lines had the highest disrupted snapshot percentage?
worst_daily_reliability.sql	Which lines had the lowest daily Good Service percentage?
latest_line_status.sql	What is the latest collected status for each line?

These queries use the dbt-built models and do not create new warehouse objects.

---

Automation

The project has two GitHub Actions workflows:

Workflow	Trigger	Purpose
CI	Push, pull request, or manual run	Installs dependencies and runs Python tests
TfL Pipeline	Manual run or every 2 hours	Runs ingestion, loads BigQuery, runs dbt, and runs dbt tests

The scheduled pipeline uses:

0 */2 * * *

GitHub cron schedules run in UTC.

Cloud credentials are stored as GitHub repository secrets:

GCP_SERVICE_ACCOUNT_KEY
AZURE_CREDENTIALS

---

Setup

Create and activate a virtual environment:

python -m venv .venv
source .venv/bin/activate

Install dependencies:

python -m pip install -r requirements.txt

For local cloud runs, authenticate with Azure and Google Cloud:

az login
gcloud auth application-default login

The local user needs permission to upload to the Azure Blob container and append rows to the BigQuery raw table.

---

Run Locally

Run the ingestion script:

python fetch_tfl.py

Run dbt from inside the dbt project:

cd tfl_analytics
dbt run
dbt test

---

Tests

Run the Python test suite:

python -m pytest -v

The Python tests use fake data. They do not call the real TfL API, upload to Azure, or load BigQuery.

---

Stack

Layer	Tool	Status
Ingestion	Python + requests	Done
Testing	pytest	Done
Raw storage	Azure Blob Storage	Done
Warehouse	BigQuery	Done
Transformation	dbt Core + dbt-bigquery	Done
Orchestration	GitHub Actions	Done
BI output	Data Studio	Simple table