MyInsights

Raw public-transport route and stop data is rarely fit for analysis as-is: source-specific column names, coordinates buried inside geometry fields, and — the sharpest issue — route records that looked distinct by ID but described the same real-world route. MyInsights was built to turn that raw data into a validated relational dataset before any dashboard work started, rather than pointing a chart library at the raw files.

The pipeline is a conventional ETL split into separate scripts — extract, transform, load and a MySQL loader, orchestrated by a single run_etl.py. Transform renames source columns into database-friendly names, splits embedded coordinates into latitude/longitude fields, and deduplicates records before anything touches a database. The cleaned output is written to processed CSVs as an inspectable checkpoint, then bulk-loaded into a MySQL schema of routes, stops and a deliberately empty trips table, with SQL queries used to validate the load independently of the Python code that produced it.

Business keys over source IDs for route deduplication

route_id looked unique and passed a drop_duplicates(subset=['route_id']) check with zero hits, but the database still held records with different IDs and identical route_number/route_name pairs. Deduplication was switched to the business key (route_number + route_name), which is what actually defines a route in this domain, and route IDs were reassigned sequentially afterward rather than inherited from the source.

A processed-CSV checkpoint before MySQL

Loading straight from raw data into MySQL would make debugging bad transformations harder to isolate. Writing cleaned routes.csv/stops.csv first gives an inspectable, reproducible intermediate stage between the Python transformation logic and the database.

An unpopulated trips table instead of simulated trip data

A trips table was designed up front — it's the schema needed for demand, revenue and utilisation analysis later — but the source data contained no real trip records. Rather than fabricating passenger or fare data to make the table look used, it was left empty and documented as a future extension point.

Streamlit as the planned dashboard layer

Keeping the future dashboard in Python (Streamlit) rather than a separate frontend stack keeps it close to the pandas/MySQL pipeline that already exists, so the interface layer can be added without introducing a second language or a second data-access path.

Separated ETL stages

extract.py, transform.py, load.py and load_to_mysql.py each own one responsibility, coordinated by run_etl.py. That separation is what made it possible to isolate the deduplication bug to the transform stage rather than debugging the pipeline as one block.

SQL as an independent check on the Python pipeline

Row counts and GROUP BY/HAVING duplicate-group queries were run directly against MySQL after loading, rather than trusting the pandas logic that produced the data — a second, independent pass over the same question.

The first duplicate check, keyed on route_id, reported zero duplicates. Manual inspection of the loaded table told a different story: multiple rows with different IDs but identical route_number and route_name. The fix was conceptual, not just code — the uniqueness rule had to reflect what a route actually is in this domain (route_number + route_name), not what the source system happened to assign as an identifier. That change took the routes table from its original duplicated state down to 47 unique routes; stop deduplication, where stop_id was already a legitimate uniqueness key, needed only a straightforward drop_duplicates and removed a single duplicate record from 954.

The ETL pipeline is complete and validated: 47 unique routes and 954 unique stops, cleaned and loaded into a relational MySQL schema with primary/foreign keys, backed by SQL checks confirming both the counts and the absence of remaining duplicate groups. The trips table and the Streamlit dashboard layer are designed but intentionally not yet built — the project is presented as a completed data-engineering foundation rather than a finished analytics application.

A technically unique ID isn't necessarily a business-unique record

route_id passed its own uniqueness check while still describing duplicate real-world routes. Deduplication logic has to encode what uniqueness actually means in the domain, not just what the source system labels as an identifier.

ETL correctness is discovered, not assumed

The pipeline only became correct after a build → run → inspect → find the ID assumption was wrong → change the transform → reload → revalidate cycle. Treating the first working run as the correct one would have shipped a silently duplicated dataset.

Don't fabricate data to fill a schema

The trips table exists because the schema needs it eventually, not because there was trip data to put in it. Leaving it empty and documented was the more honest choice than simulating passenger counts to make the analytics layer look further along than it is.