Tracking a Day of Sydney Trains

Transport for NSW publishes two open data feeds for Sydney’s trains: the timetable, and a live feed showing where services currently are and how they are tracking against that timetable. Over a single weekday I collected the live feed, built a small pipeline to turn it into clean, analysis-ready tables, and applied a set of data-governance practices to it — quality testing, a data classification, and documentation.

This post covers two things: how the pipeline works, in enough detail to follow or reproduce, and what a day of the data shows about how punctual the network was.

• Why I built this
• The system at a glance
• Inputs: collecting a live feed
• Understanding the data
• Processes: the dbt pipeline
• The governance layer
• Analysis: how the train network performed
• Scope, limits, and what more could be done
• What I learned

If you’d rather skip the data engineering and the governance and go straight to a short analysis of the cleaned data, jump to the analysis.

Why I built this

I’ve built data pipelines for several years, across workforce analytics, healthcare, and financial-services research, but governance has usually been a by-product of the engineering rather than the focus. This project was a chance to apply a governance framework (DAMA-DMBOK) to a real dataset directly, for the first time.

There is a market reason too. In a separate analysis of data-job ads, the fastest-growing skills for data engineers between 2021 and 2026 were dbt, CI/CD, and data governance. This project exercises the first and third of those directly.

The system at a glance

The data arrives in a standard format called GTFS (General Transit Feed Specification), which transit agencies around the world use to publish schedules and live service information. Transport for NSW publishes two GTFS feeds for Sydney Trains: a static feed (the timetable) and a realtime feed (live trip updates). The pipeline collects both, transforms and tests them, and produces a set of analysis and governance outputs.

The whole thing runs locally on Python, DuckDB, and dbt, without a cloud platform.

Inputs: collecting a live feed

The static feed is the published timetable: every scheduled service, the stops it makes, and the scheduled time at each stop. The realtime feed works differently. Each time you request it, it returns the current state of every train that is active on the network at that instant. For each active train it lists the upcoming stops, with predicted arrival and departure times expressed as a delay against the timetable.

That delay is the single thing this project measures. Throughout, a delay is the difference between when a train reaches a stop and when the timetable said it should: positive means late, negative means early. Everything downstream — the models, the tests, the charts — is built to turn the feed into a reliable version of that one number, per train, per stop.

Two properties of the realtime feed shaped how I collected it.

The first is that a train only appears in the feed while it is running — from shortly before it departs its first stop until shortly after it reaches its last. A service that ran at 6am is simply absent from a request made at 2pm.

The second matters more for measuring delays. As a train passes each stop, that stop is removed from the train’s list of upcoming stops in later requests. The feed only ever describes what is still ahead of each train, so it never reports a confirmed, actual arrival time — every value is a prediction of an arrival that hasn’t happened yet. That leaves one way to estimate what actually happened at a stop: take the last prediction recorded for that stop before it dropped out of the feed. Because that prediction was made in the minute or so before the train arrived, it is the closest available estimate of the real delay. I label those values estimated_actual. A stop that was still in the feed when collection stopped never got that final reading, so it stays a prediction.

Three collection decisions follow from this:

• Request the feed often enough to catch every stop before it drops. The shortest gap between consecutive stops on a Sydney service is about two minutes, so I requested the feed every 60 seconds. That is frequent enough that no stop disappears between requests.
• Save each request immediately. Every request (a snapshot) is written to the database and committed before the next one, so a failure partway through a long run keeps everything collected up to that point.
• Stop the run when few trains are still moving. Any train still mid-journey when collection stops has upcoming stops that never received a final reading, so they remain predictions rather than estimated actuals. Running the collector continuously and stopping it in the early hours, when very few services are active, keeps that group as small as possible.

The collector ran for 36 hours and recorded 8.26 million rows across 2,159 requests, with no gaps between them. For the analysis I used one full weekday from within that window — a Friday — chosen so that every service both started and finished inside the collection period. As a result, almost every delay reading for that day is a settled estimated_actual rather than an open prediction.

Understanding the data

A few properties of the data are worth setting out before the modelling, because they shaped how it was built.

A trip_id does not identify a single journey. The obvious assumption is that a trip_id identifies one train journey; it does not. The same id (for example, the 07:15 Central to Hornsby service) is reused on every day that service runs. Across just two days of collection, 518 trip_ids appeared on both days. To identify one specific run you need a composite key: the trip_id together with the service date it ran on. This had a practical consequence. An early version of the pipeline deduplicated on trip_id and stop alone, and a uniqueness test passed — but only because deduplication forces that key to be unique. It was quietly merging two days’ runs of the same service into one row. Adding the service date to the key fixed it, and it was a useful reminder to check that a test measures the thing you intend, not just that it passes.

A “service day” is not a calendar day. Trains run past midnight, so transit schedules group a day of service from one quiet point in the early hours to the next, rather than at midnight. A train that departs on Friday at 11:50pm and arrives on Saturday at 12:40am belongs to Friday’s service day. This is why timetable times can read 24:40:00 instead of 00:40:00: the 24 signals that the arrival is still part of the previous day’s service. A service date, then, records which timetable day a run belongs to, not the calendar date it happens to fall on.

The realtime feed omits the service date. The feed has a field for it (start_date), but Transport for NSW leaves it empty in every row — the field is optional in the GTFS specification. Rather than depend on a field the provider doesn’t populate, the pipeline derives the service date itself from each request’s timestamp, and records the omission as a known limitation of the source.

Processes: the dbt pipeline

dbt is a tool for transforming data with SQL. Instead of writing scripts that create and fill tables step by step, you write a SELECT query describing each table you want (a model), and dbt creates the tables, works out the order to build them in, runs tests against them, and generates documentation.

The transformation runs in three layers:

1. Staging — one model per raw table, doing only basic cleaning: renaming columns, fixing data types, and handling the timetable’s after-midnight times (like 25:10:00).
2. Intermediate — the main logic, and where the feed’s quirks are absorbed. This is where the many snapshots of each train-at-a-stop are reduced to a single row (the last one recorded) and labelled estimated_actual or prediction, using the rule from the previous section. It’s also where the fixes for the quirks live: applying the composite trip_id + service_date key so that runs of the same service on different days aren’t merged, and using the service date derived from the request timestamp in place of the empty one in the feed. The point of this layer is to hand the mart a clean, unambiguous version of the data.
3. Mart — the tables an analyst would actually query, with the delay figures joined to readable station and line names.

dbt determines the build order itself. Inside a model, you refer to another model with ref('name') rather than a hard-coded table name, and dbt reads those references to build a dependency graph and run the models in the right order. The same references generate the lineage graph and data dictionary in the documentation, which is produced directly from the code.

The governance layer

Everything above is data engineering — collecting and shaping data. The governance layer is what makes the result trustworthy, understandable, and accountable. It isn’t a separate stage bolted on at the end: it attaches to the same dbt models the pipeline already builds, and generates its artifacts from them.

The layer has four parts.

Data quality, mapped to DAMA-DMBOK. Each quality check is a dbt test — a small SQL query that searches for rows breaking a rule. If the query finds any, the test fails; if it finds none, it passes. (A passing test returns nothing, which is worth remembering when reading the results below.) Rather than write these checks ad-hoc, I mapped each one to a dimension of data quality defined by DAMA-DMBOK, a widely used data-management framework:

On the full dataset, 37 checks passed, 4 raised warnings, and none failed. Two of the warnings are worth noting. The consistency check flagged a single implausible value — a predicted delay of about 13 hours. And the referential-integrity check — does every realtime trip_id exist in the timetable? — surfaced the most significant issue in the project. The timetable is republished every night, and the identifiers it assigns to services change with each republish. I downloaded it once, so it matched the Friday realtime feed for 98.3% of trips but the Saturday feed for only about 12%: by Saturday, the live feed was quoting the identifiers from a newer timetable that my copy didn’t contain. This is why the analysis is limited to the Friday, and why a production version would need to store each day’s timetable and match the realtime feed to the version that was current at the time.

Privacy — a threshold assessment. The first step of a Privacy Impact Assessment is to determine whether the data contains any personal information. I worked through every table against the definition of personal information in the Australian Privacy Act, cross-checked against the GDPR, and concluded that all of it is public. The borderline cases were the ones worth documenting: a vehicle identifier could in principle identify a driver, but only if combined with crew rosters, and the field is empty in this feed in any case; accessibility flags describe stations, not individuals. Working through why there is no personal information, table by table, is a normal and legitimate part of privacy practice, not a shortcut around it.

Metadata and lineage. Every model and column carries a plain-English description and a classification, written next to the SQL. Because that information lives in the code, dbt generates a data dictionary and lineage graph that stay in step with the pipeline as it changes — you can browse the actual output, every table and column, at that link.

Ownership and known risks. A short policy note records who would be responsible for the data, how changes to the source would be managed (the timetable has no version number, which is recorded as an accepted risk), and a register of the project’s known limitations.

Analysis: how the train network performed

All the figures below are for one weekday — Friday 18 September 2026 — and use only estimated-actual readings. As a reminder from the collection section, that means: for each stop, the last delay recorded before the train passed it, which is the closest estimate of what actually happened, as opposed to a prediction for a stop the train hadn’t yet reached. That leaves 51,111 stop-level arrivals — each one a train calling at a station — across 3,656 trips and 321 stations. They describe that one day, not the network in general.

The network ran well by the usual yardstick: 97.5% of arrivals were within five minutes of the timetable, and trains left their first stop on time. Look more closely, though, and about a fifth of arrivals — 20.8% — were more than 30 seconds late. Those small slips are the interesting part, because of where they come from.

It builds up over the course of a journey. Average delay roughly doubled between the first tenth of a trip and the last, from about 0.47 to 1.01 minutes — small in absolute terms, but a steady increase from start to finish.

The busiest hours were not the latest. The morning peak was the most punctual window and the evening peak was close behind, while the middle of the day was worst. The faint line shows the number of trips running each hour: the hours with the most services had the least delay, so the pattern is not explained by how busy the network was.

The lines most likely to run at least one late stop were the four long-distance intercity lines (Southern Highlands, Central Coast & Newcastle, South Coast, and Blue Mountains), followed by T2. This is not simply because longer trips have more stops at which to fall behind: the Southern Highlands line leads with only 8.7 stops per trip on average, while several suburban lines with far more stops sit lower down. The distances these services cover, often on long sections of track shared with other trains, matter more than the number of stops.

The stations with the highest share of late arrivals were on the south-western and inner-western edges of the network, led by Leppington, the terminus at the western end of the T2 line, where trains arrive having accumulated delay across the whole run. Even there, about 89% of arrivals were still within five minutes.

Scope, limits, and what more could be done

This is a one-day sample, and its limits are worth stating plainly.

• One weekday only. There is no weekend comparison; that would be a natural next step.
• Estimates, not confirmed actuals. As explained earlier, every delay is the last prediction before a stop left the feed. The confidence column records which readings are settled estimates and which are still open predictions.
• Knock-on delays are mostly not measurable here. The clearest way one late train delays others is by holding up the next service that reuses its train or crew, but that connection can’t be traced, because the feed’s vehicle identifier is empty. What can be measured sets an upper bound: 91.5% of trains still left their first stop within a minute of schedule, so little delay was being passed on to departures on this day.
• A local, single-machine stack. Databricks, Unity Catalogue, and Fivetran are not part of this; the same governance work would apply on any of them.

Extending this to a longer run would not require changing the models: trip identity, the derived service date, and the marts are all built per run, and the one-day scope is applied as a filter rather than assumed in the models. Two parts would need reworking first. The timetable would need to be stored and versioned per day, because its identifiers change nightly. And the estimated_actual/prediction rule, which currently compares each stop’s last reading against the end of a single collection run, would need a definition that works for a feed that never stops.

What I learned

The learning was spread across the whole stack rather than concentrated in one place. Most of the engineering effort came from the feed itself: working out that stops drop out of it as trains pass them (which drives every decision downstream), then handling the awkward parts in SQL — identifiers that aren’t unique across days, a service-date field the provider leaves empty, timetable times that run past 24:00:00. Querying the API reliably enough to run unattended for a day and a half was its own small exercise, and dbt gave the transformations a structure and a place to attach tests.

The governance work sat on top of that: deciding how to classify the data and why, and writing down the risks and the reasoning behind each decision. The lesson that stuck was that a passing test proves nothing unless it checks the right thing — something that only became visible because two days of data broke a key I had assumed was sound. Mapping the tests to a named framework made them easier to explain and to reason about as a set, rather than as a pile of separate checks.

The full project — the collector, the dbt models, the tests, the governance documents, and the scripts that generated the charts — is on GitHub. It runs on a single machine against a public feed, so it is reasonable to reproduce with another city’s transit data, provided that city publishes GTFS.