A Data-Engineering Pipeline with Quality Monitoring for TRAI Crowdsourced Mobile-Speed Data

A Data-Engineering Pipeline with Quality Monitoring for TRAI Crowdsourced Mobile-Speed Data is an advanced M.Sc (IT) project that treats the regulator's public speed measurements the way a data team in industry would: as a monthly feed that must be landed, validated, modelled, monitored and published — automatically and repeatably.

TRAI's MySpeed app lets any mobile user in India run a speed test; the results are published as monthly downloadable files covering operator, licence service area (LSA), network technology, signal strength and download/upload speed. The data is large, messy and self-selecting — exactly the kind of input that breaks naive scripts. This project builds a pipeline that ingests each monthly file into an immutable landing zone, runs a rule-based validation stage (schema, ranges, duplicates, referential integrity), loads clean rows into a PostgreSQL star schema, and quarantines rejected rows with the rule that rejected them.

The pipeline is orchestrated by an Apache Airflow DAG supporting incremental loads and full monthly backfills, containerised with Docker, and tested on every push by GitHub Actions. A Streamlit dashboard answers the analytical questions — operator comparisons within an LSA, 4G/5G median trends month over month, and signal-versus-throughput behaviour — and every headline number is cross-checked in an independent R notebook, so the quality-monitoring story is part of the evidence.

The evaluation is measured, not asserted: ingestion throughput, rejection rates per rule, warehouse query latency (p50/p95) against direct CSV scans, and backfill runtime — reported with the commands that reproduce them.

TRAI's MySpeed programme produces one of the largest public collections of real user network experience in India, yet the monthly files are published as raw exports that cannot be queried, joined or trusted without substantial cleaning. Averages quoted in the press are computed by hand from single files, with no validation of malformed rows, no deduplication, and no record of what was thrown away.

There is a need for a reproducible data-engineering pipeline that (a) ingests every monthly file into an immutable landing zone, (b) applies explicit, auditable validation rules and quarantines rejects with reasons, (c) loads a dimensional model optimised for the real analytical questions (operator × LSA × technology × month), (d) runs on a schedule with idempotent incremental loads and backfills, and (e) measures itself — throughput, rejection rates, query latency — so the report can state the pipeline's behaviour with numbers instead of adjectives. Without such a pipeline, the dataset's analytical value is lost within days of each release, and no result produced from it can be reproduced.

Abstract

TRAI publishes monthly crowdsourced mobile-speed measurements from its MySpeed app — millions of rows describing what Indian users actually experience on each operator and technology. The data is public but raw: inconsistent headers, malformed rows, duplicate submissions and strong self-selection bias. This project designs and builds an end-to-end data-engineering pipeline that turns those files into a validated PostgreSQL warehouse and an analytical dashboard, with data-quality monitoring as a first-class component rather than an afterthought, and evaluates the pipeline itself with measured performance numbers.

Introduction

Regulators, operators and journalists quote average network speeds, but the underlying crowdsourced measurements are rarely analysed with engineering discipline. The interesting questions — does operator A really beat operator B in your LSA, are 5G medians rising faster than the marketing claims, does signal strength explain throughput — require months of files joined consistently and cleaned honestly. That is a data-engineering problem before it is an analytics problem, and it is precisely the gap between what the syllabus teaches (databases, data mining, cloud) and what industry expects (pipelines, tests, monitoring).

Existing system and gap

Typical student treatments of public datasets load a single CSV into a notebook, plot an average and stop. They have no schema discipline, no validation, no incremental processing, and no way to reproduce last month's number after the code changes. Industry practice — landing zones, dimensional warehouses, orchestration, CI and data-quality reports — is described in textbooks but almost never built by students end to end on real Indian public data. The gap this project fills: a complete, tested, reproducible pipeline on a genuinely messy national dataset, with the pipeline's own performance and rejection behaviour measured and reported.

Proposed system

• Landing zone: each monthly TRAI file archived unmodified with checksums and a manifest.
• Validation stage: declarative rules — column schema, value ranges (speeds, latency, signal), duplicate keys, LSA/operator code whitelists — routing clean rows forward and rejects to a quarantine table with rule identifiers.
• Warehouse: a PostgreSQL star schema (fact_speed_test with date, operator, LSA and network dimensions) loaded incrementally per month, with idempotent, restartable loads.
• Orchestration & CI: an Airflow DAG for nightly incremental loads and monthly backfills; Docker images; GitHub Actions running pytest and lint on every push.
• Analytics: a Streamlit dashboard (operator-by-LSA comparison, technology trends, signal-vs-throughput scatter) plus an independent R notebook that recomputes every headline figure from the landing zone as a cross-check.

Feasibility study

• Technical: PostgreSQL, Airflow, Docker and Streamlit are open source; one year of data is a few million rows — comfortably handled by a 16 GB laptop. The team has completed DBMS, data-mining-lab and cloud coursework that the stack directly extends.
• Economic: zero licence cost; everything runs locally (Docker) or on a free-tier VM for the demo.
• Operational: the pipeline is self-contained; the only external dependency is the TRAI download itself, which is archived in the landing zone so the demo never depends on the portal being up.

In scope

• TRAI MySpeed monthly mobile-data exports (a rolling 12+ months) as the sole source.
• Batch ingestion and validation; monthly incremental loads plus on-demand backfills.
• Star-schema warehouse in PostgreSQL, Airflow orchestration, Docker packaging, GitHub Actions CI, Streamlit dashboard, R cross-check notebook.
• Measured pipeline evaluation and a written dissertation.

Out of scope

• Fixed-broadband exports and the app-side collection process — consumed as published, not modified.
• Streaming ingestion (the source is monthly files; a batch design is the honest choice).
• Statistical modelling beyond descriptive analytics and simple trend decomposition — prediction is future scope.
• A production deployment with authentication/multi-tenancy; the demo runs locally or on a single VM.

Process model: the project follows an incremental (iterative) engineering model — each zone of the pipeline is delivered as a working increment, reviewed against the guide, and hardened by the next increment. This suits a two-member team over 24 weeks because a thin end-to-end slice exists by week 8 and every later change is verified by an already-running test harness.

Work division (team of 2): Member A owns landing/validation and CI; Member B owns the warehouse, orchestration and dashboard; both co-own the design document and the measured evaluation, and each member can demo every stage — the viva asks individuals, not teams.

Testing strategy: pytest unit tests for every validation rule (with malicious fixtures), integration tests running the loader against a disposable PostgreSQL container, idempotency tests (double-load = single-load), and dashboard acceptance checks against known months.

The pipeline is organised as a directed flow through three zones, orchestrated end to end; every box is a tested unit.

flowchart LR
  M["TRAI MySpeed monthly downloads"] --> L["Landing zone: immutable files + checksums + manifest"]
  L --> V["Validation: schema, ranges, duplicates, code whitelists"]
  V -->|"clean rows"| T["Conform & load: star schema"]
  V -->|"rejects"| Q["Quarantine table + data-quality report"]
  T --> D[("PostgreSQL warehouse")]
  D --> A["Analytics marts: operator x LSA x month, technology trends"]
  A --> S["Streamlit dashboard"]
  A --> R["R cross-check notebook (independent recomputation)"]
  F["Airflow DAG: incremental + backfill"] -.orchestrates.-> V
  F -.orchestrates.-> T
  C["GitHub Actions: pytest + lint on every push"] -.builds & tests image.-> F

erDiagram
  DIM_DATE ||--o{ FACT_SPEED_TEST : keys
  DIM_OPERATOR ||--o{ FACT_SPEED_TEST : keys
  DIM_LSA ||--o{ FACT_SPEED_TEST : keys
  DIM_NETWORK ||--o{ FACT_SPEED_TEST : keys
  DQ_RESULT ||--o{ QUARANTINE_ROW : "groups rejects"

  FACT_SPEED_TEST {
    int test_id PK
    int date_id FK
    int operator_id FK
    int lsa_id FK
    int network_id FK
    real download_mbps
    real upload_mbps
    real latency_ms
    real signal_dbm
  }
  DIM_DATE {
    int date_id PK
    date cal_date
    string month_key
  }
  DIM_OPERATOR {
    int operator_id PK
    string operator_name
  }
  DIM_LSA {
    int lsa_id PK
    string lsa_name
  }
  DIM_NETWORK {
    int network_id PK
    string technology
  }
  DQ_RESULT {
    int run_id PK
    int rule_id
    string rule_name
    int rows_rejected
  }
  QUARANTINE_ROW {
    int row_id PK
    int run_id FK
    int rule_id
    string raw_payload
  }

Three decisions are worth defending in the viva. First, the landing zone is immutable — every analytical number can be recomputed from the original bytes, which is what makes the R cross-check meaningful. Second, validation precedes the warehouse: the fact table only ever contains rows that passed declared rules, and the DQ report records how many did not and why, so data quality is measured rather than assumed. Third, the warehouse is a star schema, not a normalised OLTP schema — the dashboard's questions (operator × LSA × month medians) are dimensional by nature, and the measured query-latency comparison against raw CSV scans is the evidence.

• Add a forecasting module (per-LSA monthly median speeds with prediction intervals) once twelve-plus months of validated history exist.
• Join TRAI's quarterly Performance Indicator Reports to compare crowdsourced measurements against drive-test figures.
• Replace batch loads with Change-Data-Capture-style incremental processing when the publisher offers finer-grained releases.
• Package the warehouse and dashboard as a hosted demo with read-only credentials and usage analytics.
• Generalise the validation engine into a configuration-driven library reusable for other government open-data feeds.