New York City · Citi Bike · May 2025 – April 2026
Does the weather change how New York rides bikes?
CityPulse lands every Citi Bike trip and every hour of the city's weather and air quality in Google Cloud, proves nothing was lost on the way, and compares each rainy hour with the same hour when it was dry. One year, one city, one question — answered.
AThe answer, block by block
Each number compares like with like: an hour of rain against the same hour, on the same kind of day, in the same month, when it was dry — so neither the season nor the rush hour can pass for weather.
trips in an hour of drizzle (under 1 mm). A quarter of the riders stay off the bike.
trips in an hour of steady rain (1–4 mm). Nearly half the riders stay off the bike.
trips on a day with 5 cm of snow or more (
trips for every degree a dry day feels warmer than usual for its month.
on a dry day with gusts of 55–70 km/h (
on a "moderate" air-quality day: it does not keep riders home — those are often warm, sunny days.
The city's pulse trips per day, May 2025 – April 2026
Want your own cut? The BI page filters every number by month, kind of day, rider and bike, down to a single station.
Careful associations, not proof of cause: a rainy hour is also darker and often colder. Trips under a minute or over three hours are left out; members are annual subscribers, casual riders buy single rides or day passes.
RRain empties the streets — by the hour
For every hour, CityPulse asks: how many trips would this hour have had if it were dry? The answer is the average of dry hours with the same month, the same kind of day and the same hour. Each dot is how far the real count fell from it.
Rain trips vs the same hour when dry
A drizzle is already enough to lose a quarter of the riders; steady rain halves them. Casual riders react more than members, who still have to get to work. Heavy rain looks milder, but only
The rush hour, washed out average trips per hour, dry vs wet
On workdays the 8 a.m. and 5–6 p.m. peaks are commuters; rain cuts both by 40–50%. Plain averages of every wet and dry hour (snow and the blizzard closure left out; wet hours shown where there are at least five), not matched by month like the dots above.
SSnow, wind and air quality
These are judged by the day, against dry days of the same month and kind (snow), or calm days (wind) and good-air days (air quality) — each condition has its own reference band, never including the days being measured.
Snow trips vs dry days of the same month and kind
Wind dry days, by the day's strongest gust
Air quality dry days, US AQI
Snow is the strongest weather effect of all, though heavy-snow days are few (counts in the tooltips). Wind, surprisingly, barely matters. "Moderate" air is usually ozone on warm, sunny days, so warmth is mixed into that comparison.
WWarmer days, more riders
On dry, non-holiday days, each day is compared with its own month: how much warmer did it feel than usual, and how many more trips than usual did it have? The slope is the answer, per degree.
The whole season at once trips per dry day, by felt temperature
The curve mixes everything warm months bring — longer days, holidays, tourists. The tiles above hold the season still, which is why they are the answer and the curve is only the picture.
4A year, day by day
Every square is one New York day, brighter when more people rode. Summer weekends glow, winter fades, and one square in February is outlined: the day the city shut the bikes down.
Trips per day by month
Mean temperature °C, by month
BCase file: the day with zero trips
Around the storm trips per day
Monday 23 February 2026, in a city that averages over 120,000 a day.
- A dbt test flagged it: a whole day with no trips at all. Bug or reality?
- Not a lost file — February's archive reconciled to the row, from the raw CSV to BigQuery. The zero was real.
- The source: a blizzard. Citi Bike shut the whole system from 22 Feb 20:00 to 24 Feb 00:00 (Streetsblog).
- The window now lives in a sourced table,
known_service_closures, and every effect and baseline leaves those hours out — a closed system must not pass for "snow keeps riders home".
A test that fails on real data is still doing its job. The fix was knowledge, not code: one sourced row in a table.
7The pipeline, as a subway map
Two lines run into the lake, change at the manifest and ride one trunk line to these charts. Every stop checks the one before it: rows are counted on the raw bytes and the count must survive every transfer.
DDaily line
Airflow asks Open-Meteo for each finished New York day — in GMT, two UTC days at a time — and cuts the local day itself, so the days the clocks change have 23 and 25 hours.
MMonthly line
Each month's Citi Bike archive holds several CSV files. Every one is streamed into Parquet; parsed rows must equal the lines counted on the raw bytes before anything is published.
TTrunk line
A load replaces exactly one BigQuery partition and records its count; when new data lands, Airflow's assets trigger dbt, which rebuilds the marts and runs every test.
Orchestrated by Apache Airflow 3.3 from a versioned image; infrastructure by Terraform in two Google Cloud projects (dev and prod). Python 3.13 · pyarrow · httpx · BigQuery · dbt 1.12 · Observable Framework.
DDecisions worth explaining
Seventeen decision records explain every choice; these six carry the most weight.
Prepare, then publish
Each period is built and checked locally before anything is overwritten; the manifest, written last, is the success marker. A failed re-run leaves the last good data alone.
True UTC, New York days
Instants are stored in UTC, days are New York days. An API's "local day" turned out to be one fixed offset — wrong twice a year — so the cut is ours.
Expected vs actual
An effect is actual trips against what like-for-like good weather predicts. Ratios of monthly averages exploded near 0 °C; this does not.
One partition, one load
Each period replaces exactly its own BigQuery partition, after its file counts are checked, and leaves an audit row. Loading twice gives the same table.
Archives stay at the source
Bronze keeps a record of each Citi Bike archive — URL, size, ETag, CRC-32 of each file — not a copy: the source is permanent and the record proves what was read.
Built from code, least privilege
Two projects by Terraform, one service account with no IAM rights, keyless CI through Workload Identity Federation, budgets and daily query quotas.
QQuality
Version 1 (March 2026) passed all its tests while 60% of the trips were missing: every row that arrived was consistent. Version 2 counts at the source, so it can see the rows that never arrived.
What is checked
- Nothing lost: rows counted on the raw files, reconciled through Silver, the load and a dbt integrity test per day and month.
- Time: tests on the days the clocks change; rain moved to the hour it fell in.
- The maths: dbt unit tests pin the baselines and the effects on hand-made rows.
- Every change: CI runs the Python suite, Terraform validation, dbt against dev (keyless) and the site build.
What is not
- No always-on scheduler: Airflow runs on the operator's machine when needed — a cost choice, not an oversight.
- No alerting: a failed run shows in Airflow, nobody is paged.
- Frozen data: the year is fixed; the pipeline can extend it, the site does not refresh itself.
LLimits, stated plainly
- Associations, not causes. A rainy hour is also darker and often colder; the comparison holds the hour, the day and the month still, not everything.
- Some bands are thin. Heavy rain:
hours. Heavy snow: days. Gales: one day. The tooltips give every count. - One point for the whole city. Weather and air quality come from one coordinate (City Hall, Manhattan); a shower over Queens is not a shower over the Bronx.
- One year. May 2025 to April 2026 — every season once. Another year could move the numbers.
- Air quality is tangled with warmth. "Moderate" days are mostly ozone on sunny days; the +
% is warmth as much as air. - Some trips are left out. Under a minute (false starts) or over three hours (mostly bikes not docked properly).
NRun it yourself
The offline part needs only uv and Python 3.12+; the cloud part, a Google Cloud project, Terraform and Docker. make help lists everything.
# offline: into ./.lake make setup && make test uv run citypulse ingest weather --from 2025-03-09 # 23 hours: the clocks went forward uv run citypulse ingest trips --from 2025-01 # 2,124,475 trips, about a minute# your own GCP project make bootstrap ENV=dev BILLING_ACCOUNT=… ORG_ID=… # project, billing, €5 budget alert make apply ENV=dev && make airflow-up ENV=dev make transform ENV=dev # dbt build: models + every test make destroy ENV=dev # tear it all down
About €0 a month: free tiers, no always-on compute, budget alerts and daily query quotas as a hard stop.
Explore the BI page The repository The guide The decisions