dbt Star Schema Tutorial | Austin Bikeshare

Data Mart Design

🎯Business Goal

Enable historical analysis of Austin B-cycle bike trips with accurate station context at the time of each trip.

Business Questions This Enables:

•How did station closures affect ridership patterns?
•What was the average trip duration when a station had 15 docks vs 10 docks?
•Which subscriber types used stations before vs after status changes?

Key Challenge

Station attributes (status, dock count) change over time, but source data only shows the current state. We need to preserve historical versions to analyze trips accurately.

Platform Architecture

Data flows from BigQuery public data through dbt layers to the final mart

Source Layer

BigQuery Public Data

bikeshare_trips

Raw trip events

bikeshare_stations

Station metadata

Staging Layer

austin_bikeshare_demo_staging

stg_bikeshare_trips

Cleaned trips with date parts

Snapshot Layer

austin_bikeshare_demo_snapshots

snap_bikeshare_stations

SCD2 station history

Marts Layer

austin_bikeshare_demo_marts

dim_stations_scd

Station dimension with versions

fct_trips_with_station_history

Trip facts with historical context

Entity Relationship Diagram

Visual representation of the star schema: sources → dimensions → facts

Primary Key

Foreign Key

Source

Dimension

Fact

Trip Data Path

bikeshare_trips

source

Raw trip records from BigQuery public dataset. Each row is one bike rental.

trip_idINTEGER

subscriber_typeSTRING

start_timeTIMESTAMP

start_station_idINTEGER

end_station_idINTEGER

duration_minutesINTEGER

stg_bikeshare_trips

fct_trips_with_station_history

fact

Trip facts joined with historical station data. Station attributes are AS OF trip date.

trip_idINTEGER

trip_dateDATE

subscriber_typeSTRING

start_station_idINTEGER

start_station_status_at_tripSTRING

start_station_docks_at_tripINTEGER

duration_minutesINTEGER

start_hourINTEGER

day_of_weekINTEGER

Station Data Path (with SCD2 history)

bikeshare_stations

source

Station metadata. Contains current state only - no history.

station_idINTEGER

nameSTRING

statusSTRING

addressSTRING

number_of_docksINTEGER

modified_dateDATE

snap_bikeshare_stations

dim_stations_scd

dimension

Station dimension with full version history (SCD Type 2). Multiple rows per station.

station_version_idSTRING

station_idINTEGER

station_nameSTRING

statusSTRING

dbt_valid_fromTIMESTAMP

dbt_valid_toTIMESTAMP

is_currentBOOLEAN

version_numberINTEGER

→ fct_trips

KPI Measures (from Fact Table)

trip_count

COUNT(*)

Total number of trips

avg_duration

AVG(duration_minutes)

Average trip length in minutes

peak_hour_trips

COUNT(*) WHERE start_hour BETWEEN 7 AND 9

Morning rush hour trips

subscriber_ratio

COUNT(subscriber_type=Annual) / COUNT(*)

Percentage of annual subscribers

Data Flow Summary

bikeshare_tripsstg_bikeshare_tripsfct_trips_with_station_history

bikeshare_stationssnap_bikeshare_stationsdim_stations_scdfct_trips_with_station_history

Source Data

Raw tables from BigQuery public dataset

bikeshare_trips

Source

Each row = one bike rental

trip_id	start_time	start_station_id	subscriber_type	duration_minutes
9900001	2019-06-15 09:30:00	2575	Annual	12
9900002	2021-02-20 14:15:00	2712	Walk Up	8
9900003	2020-08-10 11:00:00	2575	Local365	25

Simulated data for illustration

bikeshare_stations

Source

Current snapshot only - no history!

station_id	name	status	number_of_docks	modified_date
2575	5th & Bowie	active	15	2020-06-15
2712	Toomey Rd @ S. Lamar	closed	13	2021-01-10

Simulated data for illustration

Final Data Mart: fct_trips_with_station_history

Trip facts enriched with historically accurate station attributes

Column Origins:

From bikeshare_trips

From bikeshare_stations

SQL calculated

dbt auto-generated

trip_idtrip_datesubscriber_typestart_station_idstart_station_namestart_station_status_at_tripstart_station_docks_at_tripduration_minutesstart_hourday_of_weekdbt_valid_fromdbt_valid_to

Final fact table with historically accurate station attributes

trip_id	trip_date	subscriber_type	start_station_id	start_station_name	start_station_status_at_trip	start_station_docks_at_trip
9900001	2019-06-15	Annual	2575	5th & Bowie	active	15
9900002	2021-02-20	Walk Up	2712	Toomey Rd @ S. Lamar	closed	13
9900003	2020-08-10	Local365	2575	5th & Bowie	active	15

Simulated data for illustration

Learn dbt Data Warehouse Design

Data Mart Design

KPI Measures (from Fact Table)

Data Flow Summary