Google Health Ingestion

One of four sources feeding api_warehouse’s personal data platform. This one replaced an older Fitbit-based integration once Fitbit’s API was deprecated, moving onto Google’s own OAuth infrastructure — and in the process surfaced the gnarliest date-handling bugs anywhere in the warehouse.

Endpoints

Four endpoints — steps, distance, total_calories, active_minutes — each POSTing to v4/users/me/dataTypes/{type}/dataPoints:dailyRollUp on health.googleapis.com, with the response unwrapped at rollupDataPoints. Unlike the other sources, there are no static config params at all: every request body is built at runtime by the pipeline, since a rollup query needs an explicit date range.

Backfill, chunking, and an undocumented limit

Each metric keeps its own watermark, refetched with a one-day overlap — dailyRollUp’s range is closed-open, so a day that was still trickling in device data when last fetched would otherwise never get revisited. Initial backfill reaches back three years.

Google enforces a maximum query duration per request that isn’t documented anywhere — it was discovered via INVALID_ROLLUP_QUERY_DURATION 400 responses: 90 days for steps/distance, 14 days for total_calories/active_minutes. A multi-year backfill is chunked into a sequence of per-window request bodies and executed one after another rather than as a single sweep.

Auth

Interactive setup only — this never runs inside Cloud Run. Google’s Health API requires registering a fixed redirect URI (https://www.google.com) with nothing actually listening behind it, so the one-time auth script has you copy the code parameter straight out of the browser’s address bar and paste it into the terminal. It exchanges that code for tokens scoped to googlehealth.activity_and_fitness.readonly, and an optional flag pushes the resulting refresh token directly into Secret Manager. Ongoing refresh in production is handled the same way as the OAuth sources, persisting a rotated token back to the database.

The date-handling bug (a three-migration story)

• The tables were first created with civilStartTime_year/month/day as plain integer columns.
• Every dailyRollUp call started 400ing, because Google’s CivilDateTime actually nests as {date: {year, month, day}, time: <optional>} — not the flat {year, month, day} the schema assumed. Since the tables had never actually been populated, this was a pure schema fix: the three int columns were dropped for one civilStartTime_date JSONB column, with a computed date column pulling the parts back out via ->>.
• That fix then tripped a second, more subtle issue: Postgres silently drops a column-scoped UNIQUE constraint when its column is dropped. The rebuilt date column had lost its uniqueness constraint, which broke the storage layer’s ON CONFLICT (date) upsert with an InvalidColumnReference error. A follow-up migration re-added the four unique constraints.

dbt modelling

Staging models for steps, distance, and total_calories are trivial renames. stg_active_minutes is not: activeMinutesRollupByActivityLevel comes back as a JSONB array of per-intensity-level entries (light/moderate/vigorous), unnested via jsonb_array_elements and summed into one daily total — flagged in a comment as a best-effort field mapping, unverified against a real payload at the time. The mart, fct_daily_activity, full-outer-unions all four staging tables’ dates before left-joining the metrics on — since each metric is fetched independently, they can desync mid-backfill and a plain join would silently drop days.

Engineering notes

The whole date-handling saga is a good example of a schema built from an API’s documentation turning out to not match the API’s actual payloads — and of Postgres’s DDL side effects (constraints tied to a dropped column silently disappearing) being just as easy to miss as the original bug.