Public Transit EDW data model
Data Vault 2.0 raw vault for public transit: hubs (rider/route/stop/vehicle/trip), links (rider-trip fare tap, trip-route, route-stop, trip-vehicle), and satellites (rider, route, stop, vehicle details + trip-status effectivity + fare transactions). Hash keys and load metadata are generated by the DDL layer.
Public transit enterprise data warehouse modeled as a Data Vault 2.0 raw vault: rider, route, stop, vehicle, and trip hubs; a non-historized rider fare-tap link plus trip, route, and vehicle operational links; and descriptive, effectivity, and fare-transaction satellites — built for integrating automated fare collection, vehicle location, and scheduling systems with full historization and the rider-data lineage (GDPR) a transit authority must retain. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 99
- columns
- 15
- relationships
- 6
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Non-historized fare-tap link
Fare taps are immutable point-in-time events linking a rider, trip, and stop; the link is insert-only and carries the fare measure directly — the canonical Data Vault transactional link for high-volume AFC data.
Effectivity satellite on the trip
A trip moves through scheduled, in-progress, completed, and detoured states; an effectivity satellite records the valid period of each status for as-of schedule-adherence reporting.
Fare transactions split into their own satellite
Stored-value account movements change far more frequently than rider profile attributes; a separate high-rate-of-change satellite keeps history compact and reconciliation efficient.
GDPR-driven rider PII handling
National-id full-masking, email hashing, and per-attribute classification satisfy GDPR on rider records, while load_date + record_source provide the lineage to honour access and erasure requests.
Tables in the Public Transit EDW model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Hubs (5)
hub_rider
Grain: one row per unique hub_rider business key
| Column | Type | Keys |
|---|---|---|
| rider_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_route
Grain: one row per unique hub_route business key
| Column | Type | Keys |
|---|---|---|
| route_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_stop
Grain: one row per unique hub_stop business key
| Column | Type | Keys |
|---|---|---|
| stop_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_vehicle
Grain: one row per unique hub_vehicle business key
| Column | Type | Keys |
|---|---|---|
| vehicle_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_trip
Grain: one row per unique hub_trip business key
| Column | Type | Keys |
|---|---|---|
| trip_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_rider_trip_tap
Grain: one row per rider fare tap (non-historized link)
Transactional (non-historized) link — insert-only fare tap events.
| Column | Type | Keys |
|---|---|---|
| rider_hk | binary | |
| trip_hk | binary | |
| stop_hk | binary | |
| tap_event_id | varchar | BK |
| tap_ts | timestamp | |
| tap_type | varchar | |
| fare_product | varchar | |
| fare_amount | decimal | |
| payment_media | varchar | |
| load_date | timestamp | |
| record_source | varchar |
lnk_trip_route
Grain: one row per lnk_trip_route relationship
Which route a trip runs along.
| Column | Type | Keys |
|---|---|---|
| trip_hk | binary | |
| route_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_route_stop
Grain: one row per lnk_route_stop relationship
Which stops a route serves.
| Column | Type | Keys |
|---|---|---|
| route_hk | binary | |
| stop_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_trip_vehicle
Grain: one row per lnk_trip_vehicle relationship
Which vehicle operates a trip.
| Column | Type | Keys |
|---|---|---|
| trip_hk | binary | |
| vehicle_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_rider_details
Grain: one row per rider_hk per change
Rider descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| rider_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| national_id | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| rider_category | varchar | |
| concession_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_route_details
Grain: one row per route_hk per change
Route descriptive history.
| Column | Type | Keys |
|---|---|---|
| route_hk | binary | PK |
| load_date | timestamp | PK |
| route_name | varchar | |
| route_type | varchar | |
| route_color | varchar | |
| operating_agency | varchar | |
| is_express | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_stop_details
Grain: one row per stop_hk per change
Stop descriptive history.
| Column | Type | Keys |
|---|---|---|
| stop_hk | binary | PK |
| load_date | timestamp | PK |
| stop_name | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| zone_id | varchar | |
| wheelchair_accessible | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_vehicle_details
Grain: one row per vehicle_hk per change
Vehicle descriptive history.
| Column | Type | Keys |
|---|---|---|
| vehicle_hk | binary | PK |
| load_date | timestamp | PK |
| vehicle_type | varchar | |
| capacity | integer | |
| fuel_type | varchar | |
| year_built | smallint | |
| wheelchair_accessible | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_trip_status
Grain: one row per trip status per effectivity change
Effectivity satellite tracking trip operation status over time.
| Column | Type | Keys |
|---|---|---|
| trip_hk | binary | PK |
| load_date | timestamp | PK |
| status_code | varchar | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| schedule_adherence_seconds | integer | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_fare_transaction
Grain: one row per rider account fare transaction (high-rate-of-change satellite)
Fare-transaction satellite — rider stored-value account movements.
| Column | Type | Keys |
|---|---|---|
| rider_hk | binary | PK |
| load_date | timestamp | PK |
| transaction_ref | varchar | |
| transaction_ts | timestamp | |
| transaction_type | varchar | |
| amount | decimal | |
| balance_after | decimal | |
| settlement_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_fare_product
Grain: one row per fare product
| Column | Type | Keys |
|---|---|---|
| fare_product_code | varchar | BK |
| fare_product_name | varchar | |
| rider_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_rider_trip_tap.rider_hk → hub_rider (many-to-one)
- lnk_rider_trip_tap.trip_hk → hub_trip (many-to-one)
- lnk_rider_trip_tap.stop_hk → hub_stop (many-to-one)
- lnk_trip_route.trip_hk → hub_trip (many-to-one)
- lnk_trip_route.route_hk → hub_route (many-to-one)
- lnk_route_stop.route_hk → hub_route (many-to-one)
- lnk_route_stop.stop_hk → hub_stop (many-to-one)
- lnk_trip_vehicle.trip_hk → hub_trip (many-to-one)
- lnk_trip_vehicle.vehicle_hk → hub_vehicle (many-to-one)
- sat_rider_details.rider_hk → hub_rider (many-to-one)
- sat_route_details.route_hk → hub_route (many-to-one)
- sat_stop_details.stop_hk → hub_stop (many-to-one)
- sat_vehicle_details.vehicle_hk → hub_vehicle (many-to-one)
- sat_trip_status.trip_hk → hub_trip (many-to-one)
- sat_fare_transaction.rider_hk → hub_rider (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.