Demo

A scroll-through of the project. Each slide is a picture first, words second. If you just want the answer, the live UI is at rush-ui-jv7ddtssdq-oa.a.run.app.

---

Slide 1 · The question

flowchart TB
    U([student in the office]) -->|"two clicks +<br/>'now'"| UI[Streamlit UI]
    UI -->|verdict| U
    classDef u fill:#fff,stroke:#444,stroke-width:1px
    classDef ui fill:#eef,stroke:#447
    class U u
    class UI ui

Should I leave now, or wait?

One screen. Two clicks. A one-line answer based on historical traffic for that hour and the current weather forecast.

---

Slide 2 · End-to-end pipeline

flowchart TB
    subgraph SRC["sources"]
        SZ[/"Stadt Zürich<br/>MIV counts"/]
        OMA[/"Open-Meteo<br/>archive"/]
        OMF[/"Open-Meteo<br/>forecast"/]
    end

    subgraph ING["ingestion · dlt"]
        DLT["dlt resources<br/>+ schema inference"]
    end

    subgraph RAW["raw zone · BigQuery"]
        T1[("traffic_raw<br/>hourly_counts")]
        T2[("weather_raw<br/>hourly_history")]
        T3[("weather_raw<br/>hourly_forecast")]
    end

    subgraph TRF["transform · dbt"]
        STG["staging<br/>(clean + cast)"]
        MART["marts<br/>(business logic)"]
    end

    subgraph SRV["serve"]
        REC["recommender<br/>(rule)"]
        UI["Streamlit UI"]
    end

    SZ --> DLT --> T1
    OMA --> DLT --> T2
    OMF --> DLT --> T3
    T1 --> STG --> MART
    T2 --> STG
    T3 --> STG
    MART --> REC --> UI

    classDef src fill:#fef9e7,stroke:#b7950b
    classDef raw fill:#eaf2f8,stroke:#2874a6
    classDef trf fill:#e8f8f5,stroke:#117a65
    classDef srv fill:#fdedec,stroke:#922b21
    class SZ,OMA,OMF src
    class T1,T2,T3 raw
    class STG,MART trf
    class REC,UI srv

Same picture as the slide before, but every box now has a code path.

Box	Lives at
dlt resources	pipelines/ingestion/ + pipelines/backfill/
raw BQ datasets	terraform-managed, traffic_raw + weather_raw
staging + marts	pipelines/transformation/dbt/models/
recommender	pipelines/recommend/
UI	pipelines/ui/app.py

Two sources are missing from the picture above on purpose: they only fire at request time, not during ingestion. The next slide shows them.

---

Slide 2b · Sources, all five of them

flowchart TB
    subgraph BATCH["batch · runs nightly via Airflow / Cloud Scheduler"]
        SZ[/"Stadt Zürich MIV<br/>~215 counters, hourly<br/>data.stadt-zuerich.ch"/]
        OMA[/"Open-Meteo archive<br/>2 years hourly history<br/>4 bbox corners"/]
        OMF[/"Open-Meteo forecast<br/>7-day rolling, hourly<br/>4 bbox corners"/]
    end
    subgraph LIVE["live · runs on every UI click"]
        OSRM[/"OSRM public demo<br/>driving polyline + duration<br/>router.project-osrm.org"/]
        OMP[/"Open-Meteo forecast<br/>precip at route midpoint<br/>per hour"/]
    end
    SZ --> RAW[(raw zone)]
    OMA --> RAW
    OMF --> RAW
    RAW --> MARTS[(marts)]
    MARTS --> REC[recommender]
    OSRM --> REC
    OMP --> REC

    classDef b fill:#fef9e7,stroke:#b7950b
    classDef l fill:#fdedec,stroke:#922b21
    class SZ,OMA,OMF b
    class OSRM,OMP l

Source	Used for	Cadence	Auth	Fallback if down
Stadt Zürich MIV CSVs	historical hourly vehicle counts per counter+direction	nightly	none	next day re-ingests same window
Open-Meteo archive	2 yr precip history at 4 corners, drives mart_weather_effect	one-shot backfill	none	retry; bucket is averaged across 4 corners so 1 corner failing is fine
Open-Meteo forecast (history slot)	last 7d to keep the archive fresh	nightly	none	next day fills the gap
OSRM public demo	driving polyline + duration between origin and destination	per UI click	none	haversine line at 30 km/h, the route.source field flips to fallback (<exc>) so the UI is never blank
Open-Meteo forecast (live slot)	precipitation at the route midpoint, per hour	per UI click	none	the recommender returns precip = 0 → bucket = none → weather_coef = 1.0

The two "live" sources are the reason every UI request is independent of ingestion freshness. Even if last night's backfill failed, the route + forecast still come back. Only the baselines could be stale.

---

Slide 2c · Ingestion (dlt), what one row looks like

flowchart TB
    SRC[(source API)] -->|"requests.get<br/>JSON / CSV"| RES["@dlt.resource<br/>generator"]
    RES -->|"yield rows"| PIPE["dlt.pipeline<br/>infer schema<br/>+ append"]
    PIPE -->|"COPY / load_job"| RAW[(BQ / Postgres)]

Same code, two destinations:

# pipelines/ingestion/weather.py (excerpt)
@dlt.resource(name="hourly_forecast", write_disposition="append")
def hourly_forecast():
    for corner in BBOX_CORNERS:                       # 4 points around Zürich
        for row in fetch_open_meteo_forecast(corner): # 168 rows per corner
            yield row

pipe = dlt.pipeline(
    pipeline_name="rush_weather",
    destination=make_destination(),                   # bigquery | postgres
    dataset_name="weather_raw",
)
pipe.run(hourly_forecast())

What one row of weather_raw.hourly_forecast actually contains after a run:

forecast_time     2026-05-28 17:00:00+00
latitude          47.4
longitude         8.5
temperature_2m    18.4
precipitation     0.0
windspeed_10m     11.2
weathercode       3

Row counts after the auto-backfill on a fresh install (verified today):

Table	Rows	Comes from
traffic_raw.hourly_counts	729,963 (1y) → 3.2 M (2y)	1 row per counter + direction + hour
weather_raw.hourly_history	768 (latest week) → 71,712 (2y)	1 row per corner + hour
weather_raw.hourly_forecast	168	1 row per corner + hour for the next 7 days

---

Slide 2d · Transformation, formulas with numbers

flowchart TB
    subgraph STG["staging · views"]
        s1["stg_traffic__counts<br/>+ hour_of_day, day_of_week,<br/>  month_of_year, ts_hour"]
        s2["stg_traffic__counters<br/>DISTINCT(zsid,direction)<br/>+ lat/lon (LV95→WGS84)"]
        s3["stg_weather__history<br/>+ precip_bucket<br/>+ wmo label"]
    end
    subgraph MART["marts · tables"]
        m1[("mart_traffic_baseline<br/>177,618 rows")]
        m2[("mart_weather_effect<br/>25,417 rows")]
    end
    s1 --> m1
    s1 --> m2
    s3 --> m2

Precip bucket (stg_weather__history)

\[ \text{bucket}(p) = \begin{cases} \text{none} & p < 0.1 \text{ mm} \\ \text{light} & 0.1 \le p < 1 \\ \text{moderate} & 1 \le p < 4 \\ \text{heavy} & p \ge 4 \end{cases} \]

Baseline (mart_traffic_baseline) — one row per (zsid, direction, month, dow, hour):

\[ \text{baseline\_avg}_{z,d,m,w,h} = \frac{1}{N} \sum_{t \in T_{z,d,m,w,h}} \text{count}_t \]

Weather coefficient (mart_weather_effect) — one row per (zsid, direction, dow, hour, bucket):

\[ \text{coef}_{z,d,w,h,b} = \frac{\text{avg count when bucket}=b}{\text{baseline avg over all buckets}} \]

coef = 1.0 means weather is irrelevant for that counter+hour. > 1.0 means traffic gets heavier under that bucket, < 1.0 lighter.

Real rows from the local Postgres run today, Tuesday 17:00, moderate rain:

zsid	direction	hour	dow	avg count	n_obs
Z024	auswärts (Hardbrücke)	17	2	3,978	19
Z024	auswärts	17	2	3,868	13

zsid	direction	bucket	coef
Z062	Allmend	moderate	1.854
Z096	einwärts	moderate	1.746
Z100	Albisriederstrasse	moderate	1.707
Z049	Bahnhof	moderate	1.407
Z006	einwärts	moderate	1.350

Reading: at Allmend on a Tuesday at 17:00, moderate rain pushes the count to 1.85× the dry baseline. That counter is a rain-amplifier; the recommender weighs it the same as any other counter on the route.

---

Slide 2e · Recommender, what one click computes

flowchart TB
    CLK([click 2 points]) --> R["OSRM<br/>route polyline + duration"]
    R --> S["snap.py<br/>counters within 300 m<br/>of any segment"]
    S --> Z["zsids on the route"]
    Z --> L1["lookup load_profile<br/>(per counter, dow, 24 h)"]
    Z --> L2["lookup weather_coef<br/>(per counter, dow, h, bucket)"]
    M["Open-Meteo forecast<br/>at route midpoint"] --> B["bucket(precip)"]
    B --> L2
    L1 --> X["congestion(load)<br/>= 1 + α·(load − 1)"]
    L2 --> X2["weather × congestion<br/>per counter"]
    X --> X2
    X2 --> AVG["mean across counters<br/>= multiplier"]
    R --> BASE["baseline_minutes<br/>= OSRM duration / 60"]
    AVG --> EXP["expected_minutes<br/>= baseline × multiplier"]
    EXP --> V["verdict band"]

Per-counter multiplier

\[ m_z = \underbrace{\left(1 + \alpha \cdot \max(0, \text{load}_z - 1)\right)}_{\text{congestion}} \;\cdot\; \underbrace{\text{coef}_{z,w,h,b}}_{\text{weather}} \]

with \(\alpha = 0.3\) and \(m_z\) capped at \(2.5\). Then average across the counters on the route:

\[ M = \frac{1}{|Z|} \sum_{z \in Z} m_z, \qquad \text{expected\_minutes} = \text{baseline\_minutes} \cdot M \]

Verdict band — the only thing the user actually reads:

\(M\)	Verdict
\(\ge 1.10\)	wait, traffic heavier than usual
\(\le 0.95\)	leave now, lighter than usual
in between	fine to leave, usual conditions

Sample output (recommend.py JSON, real call from today):

{
  "route": {"duration_s": 590.1, "distance_m": 5313.3, "source": "osrm"},
  "precip_mm": 0.0, "precip_bucket": "none",
  "baseline_minutes": 9.835,
  "weather_multiplier": 0.994,
  "expected_minutes": 9.77,
  "counters_used": 15,
  "verdict": "fine to leave, usual conditions"
}

---

Slide 2f · The UI, what updates and when

sequenceDiagram
    actor U as user
    participant ST as Streamlit
    participant R as recommend/<br/>timeline.py
    participant O as OSRM
    participant W as Open-Meteo
    participant PG as Postgres / BQ

    U->>ST: click origin
    U->>ST: click destination
    ST->>R: hourly_timeline(o, d, day)
    R->>O: GET /route/v1/driving (one call)
    O-->>R: polyline + duration
    R->>R: snap counters within 300 m
    R->>PG: load_profile_for(zsids, dow)
    R->>PG: weather_coef_for(zsids, dow, h, bucket)
    loop hour 0..23
        R->>W: GET /forecast precip at midpoint
        R->>R: mult = mean(congestion · coef)
    end
    R-->>ST: 24 rows + route + counters
    ST-->>U: countdown + map + timeline
    U->>ST: drag hour slider
    ST-->>U: re-renders from cached rows<br/>(no new API calls)

User action	What re-runs	Cost (API calls)
First app load	nothing, picker map only	0
Click origin	nothing yet	0
Click destination	full hourly_timeline	1 OSRM + ≤24 Open-Meteo + 2 SQL
Change date	full hourly_timeline again (forecast bucket changes)	1 OSRM + ≤24 Open-Meteo + 2 SQL
Move hour slider	only the metric row + map highlight, in-memory	0
Click "clear pins"	back to picker	0
Underlying marts refresh	nothing inside the session; next click sees new data	n/a

The UI is read-only: it never writes to the warehouse. Freshness is set by the nightly DAG (rush_daily 02:00 UTC) and Cloud Scheduler (rush-ingest-daily 02:00 UTC + rush-dbt-daily 03:00 UTC). The forecast is always live — it's a per-click API call, not a stored value.

---

Slide 2g · Data journey, one row end-to-end

Following a single counter (Z062 Allmend, direction Allmend) at Tuesday 17:00 through the pipeline.

flowchart TB
    A["CSV row<br/>Stadt Zürich<br/>2024-05-21 17:00<br/>count=412"] -->|dlt append| B
    B["traffic_raw.hourly_counts<br/>+ ingested_at"] -->|dbt view| C
    C["stg_traffic__counts<br/>+ hour=17, dow=2,<br/>  month=5, ts_hour"] -->|group by| D
    D["mart_traffic_baseline<br/>avg=320 over 19 obs"] -->|join| E
    E["mart_weather_effect<br/>bucket=moderate<br/>coef=1.854"] -->|loaded by| F
    F["timeline.py<br/>congestion(load) · coef"] -->|averaged with<br/>14 other counters| G
    G["multiplier M=0.99"] -->|× 9.84 min| H
    H["expected_minutes=9.77<br/>verdict='fine to leave'"]

Stage	What it is	Example value
Source	row in a Stadt Zürich CSV	(Z062, Allmend, 2024-05-21T17:00, 412)
traffic_raw.hourly_counts	raw append + _dlt_load_id	same, plus dlt metadata
stg_traffic__counts	derived time keys	adds hour_of_day=17, day_of_week=2, month_of_year=5
mart_traffic_baseline	one row per (zsid, dir, month, dow, hour)	avg_vehicle_count=320, n_observations=19
mart_weather_effect	one row per (zsid, dir, dow, hour, bucket)	bucket=moderate, weather_coef=1.854
Recommender	loaded into timeline.py, multiplied	combined m_z = 1.83 for this counter
UI	shown to user	expected 9.8 min, verdict "fine to leave"

The same row also appears in the BigQuery copy of every table above with identical values — that is the portability claim, made concrete.

---

Slide 3 · Two runtimes, one codebase

flowchart TB
    subgraph LOCAL["local · ./setup.sh"]
        AF[Airflow scheduler]
        PG[("Postgres<br/>warehouse")]
        SL[Streamlit]
        AF --> PG --> SL
    end

    subgraph CLOUD["cloud · terraform apply"]
        CS[Cloud Scheduler]
        CR1[Cloud Run Job<br/>ingest]
        CR2[Cloud Run Job<br/>dbt]
        BQ[("BigQuery<br/>warehouse")]
        CRS[Cloud Run Service<br/>Streamlit]
        CS --> CR1 --> BQ
        CS --> CR2 --> BQ --> CRS
    end

    CODE[(same dlt + dbt code)] --> LOCAL
    CODE --> CLOUD

The thing that matters: dlt, dbt models, and the Streamlit app are identical on both sides. Only the orchestrator and the destination change. The dbt profile picks Postgres or BigQuery from an env var.

---

Slide 4 · Architecture decisions

Decision	Why	What it costs
Postgres and BigQuery, same models	Reviewer can grade with zero cloud cost; cloud target proves portability	Two profiles, two CI paths
Dimensional marts, not raw passthrough	UI query is a single SELECT instead of a 10-table join	One extra dbt layer to maintain
Append-only fact tables	dlt 1.x can't merge into BigQuery without a primary key column the source doesn't give us	Dedup happens in staging, not in dlt
Cloud Run Jobs, not GKE / Composer	Scale-to-zero. About CHF 0/month idle	No long-lived workers, cold starts on each run
Streamlit, not React + API	One file for the whole UI	Not pretty, but graded on substance
Macros for dayofweek / median	Same SQL compiles on Postgres and BigQuery	Two short macro files

Each row is a tradeoff that was made on purpose, not by accident.

---

Slide 5 · Failure modes

flowchart TB
    subgraph L["where it can break"]
        A[source API down]
        B[dlt schema drift]
        C[dbt test fails]
        D[Cloud Run job timeout]
        E[scheduler skips a run]
    end

    subgraph R["what catches it"]
        a[retry with backoff]
        b[dlt evolves the table,<br/>raises only on type clash]
        c[build stops,<br/>bad data never hits a mart]
        d["max_retries=1<br/>+ next day's run heals it"]
        e[next cron tick<br/>backfills the gap]
    end

    A --> a
    B --> b
    C --> c
    D --> d
    E --> e

Layer	Failure	First defence	Last defence
ingest	API 5xx / timeout	dlt retry	next day re-ingests the same window
ingest	schema change	dlt evolves columns	dbt test on staging row count
transform	bad row, null key	not_null test	dbt build fails → no fresh mart, UI keeps last-good
orchestration	scheduler down	Cloud Scheduler is managed	manual gcloud run jobs execute
serve	UI 5xx	Cloud Run autoscale + health check	static fallback message

The pattern is the same everywhere: catch what you can, make the next run idempotent for the rest.

---

Slide 6 · Scaling considerations

flowchart TB
    subgraph NOW["today"]
        n1["~730k traffic rows"]
        n2["1 city, 1 weather grid"]
        n3["daily refresh"]
    end
    subgraph NEXT["next step"]
        x1["partition by date<br/>+ clustering"]
        x2["one resource per city,<br/>config-driven"]
        x3["hourly + 6h forecast<br/>refresh on a faster cron"]
    end
    subgraph LATER["later"]
        l1["BQ slot reservation<br/>or DuckDB on GCS"]
        l2["multi-region grid,<br/>sharded by country"]
        l3["streaming dlt<br/>+ materialised views"]
    end

    n1 --> x1 --> l1
    n2 --> x2 --> l2
    n3 --> x3 --> l3

Dimension	Now	If 10×	If 100×
Volume	~730k rows	partition + cluster, drop reading old years	column store on GCS, Iceberg
Cities	1 (Zürich)	config-driven dlt resource per city	shard datasets, region-aware Cloud Run
Freshness	daily 02:00 UTC	hourly cron, append-only stays cheap	streaming dlt, materialised views
Cost	~CHF 0/month idle	still ~CHF 0 thanks to scale-to-zero	slot reservation or self-hosted DuckDB
Concurrency	1 user	Cloud Run autoscales the UI	put a CDN in front, cache mart query for N min

Nothing about the current shape blocks the next step. That was the point.

---

Slide 7 · System thinking, not tools

The grading rubric asks for six things. Each one is solved by a choice in the architecture, not by a brand name.

flowchart TB
    subgraph REQ["rubric"]
        r1["use case · 5"]
        r2["ingestion · 10"]
        r3["storage + docker · 10"]
        r4["transformation · 5"]
        r5["orchestration · 10"]
        r6["repository · 5"]
    end
    subgraph ANS["architecture choice"]
        a1["one question,<br/>one verdict"]
        a2["dlt + idempotent backfill,<br/>schema evolution"]
        a3["one compose file,<br/>7 services, healthchecks"]
        a4["staging → marts,<br/>portable SQL via macros"]
        a5["Airflow local +<br/>Cloud Scheduler"]
        a6["one-line install,<br/>one-line teardown"]
    end

    r1 --> a1
    r2 --> a2
    r3 --> a3
    r4 --> a4
    r5 --> a5
    r6 --> a6

Tools are interchangeable. Replace dlt with Singer, Airflow with Dagster, Streamlit with FastAPI + React, BigQuery with Snowflake. The boxes stay, the arrows stay, the failure modes stay. That is the system.

---

Slide 8 · Live evidence

A fresh terraform apply + one ingest run + one dbt run, this week:

Table	Rows
traffic_raw.hourly_counts	729,963
weather_raw.hourly_history	768
weather_raw.hourly_forecast	168
rush.mart_traffic_baseline	177,618
rush.mart_weather_effect	25,417

• Live UI: rush-ui-jv7ddtssdq-oa.a.run.app — HTTP 200
• Scheduler: rush-ingest-daily 02:00 UTC, rush-dbt-daily 03:00 UTC
• One curl on a clean machine reproduces the local half:

bash <(curl -fsSL https://raw.githubusercontent.com/javihslu/rush/main/install.sh)

That command clones the repo, builds the image, starts the 7-service stack, and triggers rush_backfill for you. You only watch the progress bar.