Ingesting BODS for South Yorkshire: what it takes, and what it costs

Status: scoping. Written 2026-09-03. A plan for building BODS ingestion for South Yorkshire from nothing, with every figure measured — either on the system already running here, or by sampling BODS for a South Yorkshire bounding box while writing this. Where a number is national it says so. Where a number is projected forward it says which measurement it was projected from.

South Yorkshire is NPTG administrative area 370. That three-character prefix on an ATCO code is the only cheap way to cut a national feed down to one authority, and it appears in every stage below.

The plan is written greenfield — how you would build this from nothing — because it has to survive being handed to someone who has not seen this repository. The order below is the order it would be built in, and each stage names the thing that goes wrong if it is skipped.

South Yorkshire, in numbers

Measured today, 2026-09-03, unless dated otherwise.

	figure	source
Stops in the area	7,567	today's national export, sliced to 370
— of which tram/rail/ferry, no area code	149	matched by position, see the envelope trap below
Journeys calling at one	27,579	same, 1,350,938 stop times
Live vehicles in the box, right now	1,026	one GTFS-RT call
Operators reporting positions	37	one SIRI-VM call
Operators producing usable observations	13	Wed 2 Sep, this box
Vehicles matched to a journey, in a day	654	Wed 2 Sep
Journeys observed, in a day	6,560	Wed 2 Sep
Stop departures recorded, in a day	321,569	Wed 2 Sep

Two operators are 90% of it: First South Yorkshire (154,707 observations, 264 vehicles) and Stagecoach Yorkshire (135,197, 253). Then TM Travel, Stagecoach East Midlands, Globe Coaches and eight others with a handful of vehicles each. Any design that quietly assumes uniform operators will be wrong about South Yorkshire in both directions: two operators whose republishing habits dominate the whole dataset, and a long tail whose data quality nobody is watching.

The bounding box for the area is -1.90,53.25,-0.85,53.72. The stops themselves span -1.72,53.31 to -0.94,53.65; the box is deliberately wider so a bus crossing the boundary is seen approaching it.

What BODS publishes

Five datasets, one account, one API key. The key goes on every request as ?api_key= and nothing else authenticates.

Dataset	Endpoint (under data.bus-data.dft.gov.uk)	Format	National	South Yorkshire
Timetables	/timetable/download/gtfs-file/all/	GTFS zip	1.29 GB zipped, 7.52 GB unpacked, 1,570,883 trips, 60.4M stop times	25.1 MB, 27,579 trips, 1.35M stop times
Timetables	per-dataset TransXChange	TXC XML	~1,700 datasets	115 MB of XML, ~7 min to walk
Vehicle positions	/api/v1/gtfsrtdatafeed/	GTFS-RT protobuf	~8,325 vehicles in our boxes	1,026 vehicles, 154 KB per call
Vehicle positions	/api/v1/datafeed/	SIRI-VM XML	same coverage	1,025 vehicles, 1.21 MB per call
Disruptions	SIRI-SX	XML	~300 live situations, 10 publishers	a handful
Fares	NeTEx	XML zip	per operator	408,890 prices held here

Two things are not in BODS and are needed to make sense of it: NaPTAN (stop locations, naptan.api.dft.gov.uk/v1/access-nodes?dataFormat=csv, 435,439 stops, 97 MB) and NPTG (administrative areas, .../v1/nptg, 150 areas). Both are DfT, both are separate, both are free.

The national export is not a stable size. Today's is 1.29 GB and 1,570,883 trips. The export currently loaded on this box, 20260829_024950, was 1.59 GB and 1,948,776 trips — 24% more service in the same file five days earlier, because operators republish on their own schedules and datasets drop in and out between nightly regenerations. Size the download and the import against the larger figure, not the one you happened to measure.

The decision everything else follows from

A live bus is only meaningful against a timetable, and the join is trip_id. Get this wrong and every stage downstream is sound and useless.

Three properties of that key, all learned the hard way:

• GTFS trip ids are content hashes and survive a re-export. Route and service ids are per-export serials and do not. So a trip id can be stored; a route id cannot be stored across an import.
• The routing graph and the timetable must be built from the same download. Build them from two and the ids stop lining up: journeys still plan, buses still run, and nothing can connect them.
• Operators republish on their own schedule and every republish mints new ids. Stagecoach re-issues all 13 of its datasets daily, and Stagecoach Yorkshire alone is 42% of South Yorkshire's observed traffic. Measured here: five days after an import, 7.7% of buses in South Yorkshire were running a journey the timetable did not hold. A fortnight after an import, across our wider coverage, that reached 20.3%.

That is the argument for the refresh cadence in the next section. It is not a tidiness problem; left alone it silently deletes a fifth of the evidence.

Stage 1 — the timetable

Download the national GTFS export, cut it to area 370, load it.

Download. 1.29 GB in 65 seconds on this box. A truncated download is a valid file and an invalid zip, so test the zip opens before doing anything with it — the failure otherwise surfaces four stages later as a parse error. Read feed_version out of it and stop if it is already loaded; BODS's published file lags its own version stamp, and a 04:01 run was served the previous day's export. Poll at 05:00, not 03:00.

Slice. This is where South Yorkshire pays almost nothing. Measured today, on this box:

	national export	sliced to area 370	share
Zip on disk	1.29 GB	25.1 MB	1.9%
Unpacked	7.52 GB	190.1 MB	2.5%
Trips	1,570,883	27,579	1.8%
Stop times	60,355,032	1,350,938	2.2%
Shape points	43,196,456	1,318,599	3.1%
Stops	312,662	9,449	3.0%
Routes	13,619	352	2.6%
Operators	633	21	3.3%

The slice took 20 minutes, nearly all of it reading the national stop_times.txt and shapes.txt end to end — that cost is the national file's, not South Yorkshire's, and it does not shrink with the area. The output does: one authority is about 2% of the national feed, and the slice is small enough to keep 26 of them without thinking about it.

Keep the trips that call in the area and drop the rest, preserving referential integrity on the way out: routes for the trips kept, agencies for those routes, calendars for those services, and every stop those trips actually call at — including stops outside the area, because chopping the end off a cross-boundary journey leaves the router planning onto a stop that does not exist. That is why the slice holds 9,449 stops against the area's 7,567: the extra 1,882 are stops elsewhere that South Yorkshire journeys call at, and they have to come too.

The envelope trap. Tram, metro, rail and ferry interchanges carry no geographic area code; they get mode-based "national" 9xx codes instead. Match on the ATCO prefix alone and you silently drop every Supertram stop in Sheffield. They have to be picked up by position — 149 of them here — and the envelope has to be computed per area, never over several areas at once.

Load. 27,579 trips and 1,350,938 stop times, from today's export. On this box a 19.7-million-row import takes about 37 minutes including the swap, so South Yorkshire's share of that is a few minutes. See Databasing it for how it lands without taking the timetable down.

Size against the larger week, not this one: the timetable currently loaded here, imported from the 2026-08-29 export, holds 1,946,250 stop times at South Yorkshire stops — 44% more than today's slice. Both numbers are real; the national export moves that much between regenerations.

Cadence. Daily for the timetable, weekly for the routing graph. The timetable half causes no downtime and is where the staleness bites; the graph half restarts the router and takes two hours, so it stays on Sunday 04:00.

Stage 2 — vehicle positions

BODS publishes the same vehicles twice, in two formats, and they are not interchangeable. Both were sampled for the South Yorkshire box while writing this.

GTFS-RT is the primary. A protobuf snapshot, one bounding box per request. For South Yorkshire: 1,026 vehicles, 154,114 bytes, 0.7 seconds. Critically, BODS has already matched the journey — 94.4% of those vehicles carried a real GTFS trip_id that joins straight onto the table loaded in stage 1. Poll every 10–15 seconds. The feed is a full snapshot every time, so a missed poll costs resolution and nothing else — back off rather than retry hard.

One box for one authority is the whole point. A rectangle drawn around a region 250 km end to end is most of England and eight times the vehicles for the same answers: the same call against our wider northern box returns 8,325 vehicles and 1.15 MB.

SIRI-VM is a second reader, not a replacement. Same box, 1,025 vehicles, 1.21 MB — eight times the bytes for the same buses, because it is XML. It carries two things GTFS-RT does not:

• BlockRef — the operator's own number for the day's work a vehicle is rostered to, present on 70.0% of South Yorkshire vehicles. GTFS-RT carries no block at all, and the block ids in imported GTFS are content hashes a published block number can never match: 0 of 633. This feed is the only route to knowing which running board a bus is on.
• The operator's journey vocabulary — DatedVehicleJourneyRef (on 100% of vehicles here), the ticket machine's JourneyCode (67.5%), DriverRef. This is what franchised on-board equipment will report when BODS is no longer the source, so reading it now is how the replacement gets exercised daily rather than proved once.

Two traps in SIRI-VM, both measured in the South Yorkshire box today, both of which corrupt data silently:

• It never drops a vehicle. 21.8% of the South Yorkshire feed was more than 15 minutes old at the moment it was fetched, and some of the national tail is a day old. A row written without checking RecordedAtTime is a bus that stopped hours ago, presented as current. Filter on record age and log the share every poll so it stays a number rather than folklore.
• VehicleRef collides between operators. In this one South Yorkshire call there were 1,025 vehicles but only 999 distinct VehicleRef values — 26 collisions inside a single county. The key is OperatorRef + VehicleRef, never the ref alone.

How often a bus actually reports. Sampled over 30 minutes across South Yorkshire (2026-08-19): 769 buses, 12 operators, fleet-weighted mean gap 22 seconds, median 20. Operator means ranged from 20 s (Stagecoach Yorkshire, First Halifax) to 33 s (TM Travel). Polling at 10 seconds is already about twice as fast as the data changes; there is no case for going faster and a good case for spending the effort on stage 4 instead.

What neither feed carries: no Delay, on any vehicle nationally, and no next stop. Punctuality is not ingested; it is derived by matching a position to the schedule. That is stage 4, and it is most of the work.

Stage 3 — disruptions and fares

SIRI-SX, every 5 minutes. Roughly 300 live situations nationally from ten publishers, which is the coverage gap: most operators publish nothing. Situations attach to lines, operators and stops — there is no journey-level targeting, so "is my bus affected" cannot be answered from this feed, only "is my route affected".

NeTEx fares, on republish. Zonal fares mean the useful shape is zone → stops, not a price per pair.

Both are low-volume and neither is on the critical path. Build them last.

Databasing it

Three classes of table, with different rules. Conflating them is the most expensive mistake available here.

1. Present-state tables — one row per bus, overwritten

One row per live vehicle. For South Yorkshire that is about 1,000 rows, never more. Tiny tables that only ever hold now.

These are the ones that bite. Measured on this box today, at nine times South Yorkshire's vehicle count:

	live rows	heap	indexes	updates	HOT
vehicle_positions	9,171	240 MB	80 MB	1,650,129,143	66%
siri_vehicles	7,288	12 MB	8 MB	104,296,350	17%

240 MB of heap for 9,171 rows is about 26 KB per live row, against a true row width well under 1 KB. Autovacuum has run on it 44,262 times — roughly every 90 seconds, continuously, forever — and is still losing. An upsert every 10 seconds across 9,000 vehicles is 1.65 billion updates, and a third of them could not be HOT.

South Yorkshire alone is a ninth of that write rate, which makes it survivable rather than fixed. Building fresh, do one of:

• set a low fillfactor (70 or below) so HOT updates have room, and index only columns that do not change — the 17% HOT ratio above is an index sitting on a volatile column;
• or keep present-state out of the database entirely. It is 1,000 rows with a 10-second TTL, which is a cache, not a table. Everything durable is in class 2 anyway.

Budget a per-table autovacuum setting either way. The defaults assume a table written occasionally, and this one is written 8,640 times a day per row.

2. Append-only evidence — the thing reports are built from

stop_observations: one row per stop departed, unique on (service_date, trip_id, stop_sequence). For South Yorkshire, 274,078 rows a day averaged across a full week.

This is the table that decides whether the whole thing works, for two reasons.

It cannot be backfilled. These rows are the only record that a bus passed a stop at a time. Miss an hour and that hour is gone; no later import reconstructs it. So the poller stopping is a data-loss event, not an outage, and anything that restarts it — a timetable import, a deploy, a failover — has to be deliberate about when.

Its indexes are the difference between a report and a timeout. Measured here, the index set on this table is 51% of its total size — 3,919 MB of indexes against 3,763 MB of heap. Getting an index on observed_at wrong is what once made the control room serve nothing at all. Plan the index set from the queries, not from the columns, and re-check it: it is the cheapest storage lever in the whole design.

3. Derived rollups — small, kept forever

punctuality_daily, journey_times, first_last_daily, lost_mileage_daily. Written nightly, never pruned. For South Yorkshire these total about 302 MB a month — see the next section for the breakdown.

The rollups are what make deleting class 2 safe, and the order matters. Here the chain is 03:10 journey times → 03:20 punctuality → 03:35 first/last → 03:50 lost mileage, and the 03:20 job prunes the raw observations the 03:10 job reads. Two rules fall out of that:

• the rollup that reads raw data runs before the one that deletes it;
• the pruner refuses to prune any day it has not already rolled up, rather than trusting the schedule. A rollup that failed silently plus a pruner that ran on time is unrecoverable data loss on a timer.

Never count a class 2 table for a lifetime figure. stop_observations is pruned at 35 days, so a count of it is "the last 35 days" wearing a total's label — true only by luck until the first prune. Lifetime figures come from the rollups.

Bulk reload without downtime

The timetable is replaced wholesale while the live layer is matching buses against it. Truncate-and-load takes the timetable down for the length of the import, and the poller writes nothing during that window — see above about what append-only means.

Load into a shadow schema and swap:

1. CREATE SCHEMA tt_load, create empty copies of every tt_* table there;
2. put the shadow schema first on search_path, so the loader's unqualified names hit the copies and the same code serves both paths;
3. COPY into them, then build the live tables' indexes on the shadow copies — the index definitions are read from the live tables, so there is no second list to keep in step;
4. rename, under a short lock timeout (5 s) with retries (8 × 15 s). The swap itself is milliseconds; the patience is for a long-running reader.

Then restart the position poller, which caches which trip ids it could not resolve and would otherwise keep treating the newly imported journeys as unknown. This is the only restart the daily refresh causes.

Budget for the peak, not the steady state: during the swap both copies of the timetable exist at once.

Storage, month by month

Everything here is South Yorkshire only, projected from a full measured week (Mon 24 – Sun 30 August 2026) and the measured bytes-per-row of each table on this box. Bytes per row is heap plus indexes, taken from the live tables, so it already includes index overhead and real-world bloat rather than a theoretical row width.

What a week of South Yorkshire actually weighs

table	rows in the week	bytes/row	MB/week	MB/month
stop_observations (raw)	1,918,548	370	677	2,945
journey_times (kept)	37,099	1,760	62	271
punctuality_daily (kept)	17,417	313	5.2	22.6
first_last_daily (kept)	4,964	330	1.6	6.8
lost_mileage_daily (kept)	1,467	253	0.35	1.5

Two numbers carry the whole section:

• raw evidence: 2.88 GB a month, and it stops growing only when you prune it;
• kept rollups: 302 MB a month, and they never stop growing, by design.

A weekday is about 2.4× a Sunday, so a bank-holiday-heavy month is meaningfully smaller than a term-time one. The projection uses the measured mix.

journey_times is 90% of the kept growth on its own, because each row carries the whole journey as arrays — stop ids, scheduled and actual offsets, dwell times. That is the design: it is what makes a per-journey report a single row read rather than a join across a million observations. It costs 1.76 KB a journey and it is worth it.

The database, month by month

Three retention policies for the raw table, everything else identical. The timetable is fixed and replaced rather than accumulated: 1.35–1.95 million stop times at the measured 193 bytes a row is 250–360 MB, and the table below carries it at a round 0.4 GB so the figure is the ceiling rather than the average. Double it briefly during the shadow-schema swap.

month	raw @ 35 days	raw @ 12 months	raw kept forever	rollups	total @ 35 d	total @ 12 mo	total, forever
1	2.9	2.9	2.9	0.3	3.6	3.6	3.6
2	3.3	5.8	5.8	0.6	4.3	6.7	6.7
3	3.3	8.6	8.6	0.9	4.6	9.9	9.9
6	3.3	17.3	17.3	1.8	5.5	19.4	19.4
12	3.3	34.5	34.5	3.5	7.2	38.5	38.5
18	3.3	34.5	51.8	5.3	9.0	40.2	57.5
24	3.3	34.5	69.0	7.1	10.8	42.0	76.5
36	3.3	34.5	103.5	10.6	14.3	45.5	114.5
60	3.3	34.5	172.6	17.7	21.4	52.6	190.6

All figures GB. At 35 days the database is flat at roughly 7 GB after the first year and grows only by the 0.3 GB a month of rollups. At 12 months it settles at about 42 GB. Kept forever it passes 100 GB in the third year.

The retention decision is a contract question, not a technical one. 35 days is what a punctuality dashboard needs. A contract that requires stop-level evidence to be produced 12 months later needs the 12-month column, and at that size the raw table should be partitioned by service date, so pruning is a partition drop rather than a delete-and-vacuum, and old partitions can sit on cheaper storage.

Files, not database — and the archive that builds up

artefact	size	how many kept	total
National GTFS export, during a refresh	1.29 GB	1, transient	1.29 GB
South Yorkshire slice	25.1 MB	1 live	25.1 MB
Timetable vintage archive	25.1 MB each	26 weekly	0.64 GB
OSM extract for the area	28 MB	1	28 MB
Routing graph, South Yorkshire only	107 MB	2 (live + staging)	214 MB

The vintage archive is the one that builds up quietly and is worth being deliberate about. Keeping the weekly slice for 26 weeks means a bug found today can be replayed against the timetable that was actually live when it happened. The archive is bounded — it stops growing at 26 weeks — but the rule that makes it safe is that archiving must never be the reason a refresh fails: if keeping this week's slice would take the disk below a floor, drop the oldest first, and if that is not enough, skip the archive with a warning rather than aborting the run. A missing vintage costs a replay nobody may ever need; a failed refresh costs a stale timetable, which is worse.

For scale: the seven-area archive running here is 317 MB per vintage and 8.1 GB across 26 weeks. South Yorkshire alone is 25 MB per vintage and 0.64 GB for the full half-year — small enough that the interesting question is not whether to keep 26, but whether to keep more.

If you archive the raw feed as well

Optional, and separate from everything above: keeping the actual BODS payloads, so a matching bug can be re-run against the bytes that were really received rather than the conclusions drawn from them. Measured, for the South Yorkshire box, including the gzip ratio of a real payload:

feed	per call	calls/day	raw/day	gzipped/day	gzipped/month	gzipped/year
GTFS-RT (10 s)	154 KB	8,640	1.24 GB	0.60 GB (2.1×)	18.4 GB	221 GB
SIRI-VM (20 s)	1.21 MB	4,320	4.99 GB	0.37 GB (13.3×)	11.4 GB	137 GB

XML compresses thirteen times; protobuf, already binary, only twice. So the verbose feed is the cheaper one to archive, which is the opposite of what the per-call sizes suggest — worth checking before deciding to keep only the small one. Together that is about 30 GB a month compressed, which dwarfs the database. If it is kept, it belongs on object storage with a lifecycle rule, not on the database disk, and it should be an explicit decision with an end date rather than something a poller starts doing by default.

Doing this in a Microsoft environment

Nothing in the design depends on being on a Linux box under a desk. The mapping below is what a South Yorkshire deployment looks like in Azure, with the two places where the Azure-specific detail actually changes a decision called out.

This section is not measured — it is a design, and the prices are list prices rather than a quote. Everything above it is measured.

The shape

what runs here	Azure equivalent	note
Position poller (systemd, always on)	Container Apps app, min replicas 1, scale-to-zero off — or a single Linux VM	not Functions; see below
SIRI poller	same
Nightly rollup chain (systemd timers)	Container Apps Jobs, cron schedule, same image	ordering stays in code, not in the orchestrator
Weekly timetable + graph refresh	Container Apps Job, or a VM started and deallocated	RAM-bound, see below
PostgreSQL	Azure Database for PostgreSQL Flexible Server	keep Postgres; see below
data/vintages/*.zip, exports, graph	Blob Storage + lifecycle rules	Cool at 30 days, Archive at 180
.env	Key Vault, referenced as a container secret	the BODS API key is the only credential
journalctl	Log Analytics	the alert that matters is silence, not errors
Reporting site	App Service or Container Apps	Power BI can read the rollups directly

Why not Functions

The obvious Azure answer to "poll an API every 10 seconds" is a timer-triggered Function, and it is the wrong one here for two reasons.

The poller carries state between polls: the set of trip ids it could not resolve, and each vehicle's last position, which is precisely what turns a stream of positions into a stop departure. A stateless invocation would re-read that from the database 8,640 times a day, or lose it.

And a timer trigger's guarantee is "at least once, roughly then". That is fine for a report and wrong for the only record that a bus passed a stop. Class 2 data cannot be backfilled, so the scheduler needs to be something you can watch and restart deliberately, not something that quietly skips.

An always-on container — one replica, restart-on-failure — is the same thing the systemd unit is, and costs about the same as the smallest VM.

Why keep Postgres, and what porting would actually cost

Azure Database for PostgreSQL Flexible Server is a first-party Azure PaaS in UK South. Choosing it is choosing Microsoft; it is not an exception to a Microsoft estate. Porting to Azure SQL instead would mean:

• journey_times is arrays — stop ids, offsets, dwell times, one row per journey. In Azure SQL that becomes JSON columns or five child tables. It is the core report table and the arrays are the design.
• The bulk swap is a schema rename. SQL Server can do it (ALTER SCHEMA … TRANSFER, or partition switching), but it is a rewrite of the one procedure most likely to take the timetable down when it goes wrong.
• Every write is an upsert (ON CONFLICT). MERGE is the SQL Server equivalent and has well-known concurrency traps under exactly this kind of concurrent-writer load.

None of that buys anything. Recommend Flexible Server.

Fabric / OneLake: the gold tables are 302 MB a month for South Yorkshire. A lakehouse for 300 MB a month is overhead, not architecture. Power BI can DirectQuery the rollups as they are. If OneLake is wanted anyway — for joining against non-transport data, which is a real reason — export the four rollup tables nightly as parquet at the end of the existing chain. That is an integration, not a re-platform.

The Azure detail that costs real money: IOPS come from the disk, not the vCore

This is the one place where the Azure shape changes the design.

The present-state table is rewritten every 10 seconds per vehicle — 8,640 writes a day per row — with autovacuum running continuously behind it. On Flexible Server with Premium SSD (the P-tier), IOPS are a step function of disk size: you end up buying hundreds of gigabytes you do not need in order to get the IOPS you do. South Yorkshire at 35-day retention is a 7 GB database. Size that disk for capacity and the write path stalls at 08:00 on a Monday.

Two ways out, and they compose:

1. Use Premium SSD v2, which decouples them — provision the GiB you need and the IOPS separately. Measured meter rates below.
2. Better: do what the storage section already says and keep present-state out of the database. It is 1,000 rows with a 10-second TTL. Azure Cache for Redis, or just process memory, and the IOPS problem disappears along with the 240 MB of bloat.

High availability, and the thing it is actually for

Zone-redundant HA roughly doubles the compute line, and on most systems that is hard to justify. Here it is not, because a failover window is not downtime, it is stop observations that no later import can reconstruct.

But there is a cheaper answer that covers more cases: have the poller spool to local disk when the database is unreachable and drain on reconnect. That covers failover, deploys, the daily import restart and a network blip, and it is a code change rather than an Azure line item. Do that first; buy HA second.

Backups are not the archive. Point-in-time restore gives 7–35 days and exists to recover from a mistake. The 26-week vintage archive exists to replay a fixed bug against the timetable that was live at the time. They are different retentions for different purposes and both are needed.

Ingress is free and egress is not. Pulling roughly 6.2 GB a day of feed into Azure costs nothing. Sending report data out to desktops does. Keep aggregation server-side; this is another argument for the rollups.

Indicative monthly cost

UK South, pay-as-you-go list prices in GBP, read from the Azure retail prices API on 2026-09-03. An EA or CSP agreement will be lower, and reserved instances lower again on the two always-on lines.

line	SKU	£/month
Database compute	PostgreSQL Flexible Server, General Purpose D2ds_v5 (2 vCore, 8 GiB)	110.74
Database storage	Premium SSD v2, 64 GiB @ £0.0979/GiB	6.27
Database IOPS	provisioned, £0.0184 each per month — model this line carefully	see note
Backup beyond the free allowance	£0.0736/GB	~0 at this size
Poller compute	1 × D2s_v5 Linux VM (or equivalent container)	59.64
Weekly refresh + graph build	E4s_v5 (4 vCPU, 32 GiB), ~13 hours a month, deallocated between runs	~2.83
Vintage + export archive	Blob Cool LRS, £0.0077/GB — ~10 GB	0.08
Baseline total		~£180 + IOPS

Notes on that table, because a cost estimate that hides its assumptions is worse than none:

• The IOPS line is the one to model, and it is the one figure here I have not measured. Premium SSD v2 is documented as including a free baseline of IOPS and throughput — confirm the number against your own tenant before budgeting on it. Whether the workload needs more than the baseline depends entirely on whether present-state stays in Postgres: moving it out is the difference between paying nothing here and provisioning thousands of IOPS at £0.0184 each per month.
• The database compute line assumes a box comparable to this one (2 vCore, 8 GiB). It is enough. Burstable B2ms is £81.69/month and will do for the poller-only workload but not for the nightly rollups, which are the CPU peak.
• If the deliverable is reporting only and not journey planning, the graph build and OSM extract drop out entirely — no E4s line, no 107 MB graph, no two-hour Sunday job. That is the single biggest scope lever in this document and it should be settled before anything is provisioned.
• Zone-redundant HA roughly doubles the database compute line.
• Egress, Log Analytics ingestion and Container Apps consumption meters are not in the table; they are small here but not zero.

What Microsoft does not change

The graph build is a single-node, memory-bound job. It has been OOM-killed twice on a 7.8 GB box. That is a VM with 16 GB or more for a few hours a week, not a container with a memory limit, and no amount of platform changes it.

Sequencing

1. NaPTAN + NPTG. Nothing else can be cut to area 370 without them.
2. Timetable import, national file, filtered to 370, into tt_*. At this point you can answer "what is scheduled" and nothing else.
3. GTFS-RT poller writing present-state only. Now the map works.
4. Position-to-schedule matching, writing stop_observations. This is the big one and it is derived, not ingested: no feed carries a delay.
5. The nightly rollups and the pruner, in that order, before the raw table gets big enough to make the decision for you.
6. Automate the refresh — daily timetable, weekly graph. Steps 2–5 all work fine on a hand-run import for about a fortnight, which is exactly long enough to forget it is manual.
7. SIRI-VM second reader, for blocks and the operator's own vocabulary.
8. SIRI-SX and NeTEx.

Steps 1–5 are the system. 6 is what keeps it true. 7–8 are additive and can land any time after.

What BODS will not give you

Worth stating in the scope so it is not discovered in month three:

• No passenger counts. SIRI 2.0 has no element that can express a number of people, so no operator could supply one through it. The one load field is a three-value word on 1.4% of vehicles nationally. This arrives from on-board APC or ticketing, or not at all.
• No delay, and no next stop, on any vehicle nationally. Punctuality is derived or it does not exist.
• No accessibility at stop level. Unpopulated in GTFS and NaPTAN, 0.9% in OpenStreetMap.
• No journey-level disruption targeting, and only ten SX publishers.
• A stale tail on SIRI-VM — 21.8% in South Yorkshire today.
• Colliding vehicle references — 26 within South Yorkshire alone, in a single call.
• Coverage is whatever you sliced. Area 370 is a choice; widening it is a re-import for reporting and a hardware question for the routing graph.

Open questions for this scope

• Retention. 35 days of raw observations keeps the database at 7 GB. A contract that needs stop-level evidence at 12 months is 42 GB and should be partitioned by service date from the start. That decision is cheap now and expensive in month nine.
• Reporting only, or journey planning too? See the cost note above. It changes the hardware, the weekly job, and roughly a third of the build.
• Raw feed archive: yes or no, and for how long? 30 GB a month compressed. It is the only thing that lets a matching bug be re-run against what was actually received, and it is also the largest single line in the storage budget.
• The two-operator concentration. First South Yorkshire and Stagecoach Yorkshire are 90% of the observed data. Any quality measure, alert threshold or availability target should be stated per operator, or it will describe those two and say nothing about the other eleven.
• TransXChange direct as well as GTFS: it carries block structure and the registration detail the GTFS conversion drops. South Yorkshire alone is 115 MB of XML and about seven minutes to walk, which is affordable — currently read weekly here for the cross-walk only, and nothing on the live path reads it.