Architecture
Pulls eleven feeds into BigQuery.Every morning at 6 AM.
Every morning at 6, nine Cloud Run jobs and Google's Ads transfer copy ten marketing sources into BigQuery raw. Described staging and marts views join ad spend, calls and Monday leads to the patients they became, and the warehouse feeds a 6:30 email, a 6:45 health check and the Claude connector.
3.5 hours
from the design spec at 12:35 PM on Aug 4 to the first sync job running in the cloud
9 of 9
daily direct sources pulled on Oct 29, read from the warehouse's own freshness view
3,334 of 3,383
lead to patient matches made by the link staff already keep in Monday
8 of 8
mornings the 6:30 email has gone out since it started on Oct 22
New York --:-- simulated
Hover a part to trace what it touches. Click it for its notes.
- Built by me
- Managed by Google
- Outside system
- Main data path
- On demand
- Credentials cross
When it breaks
What each failure does, and what it does not do.
-
A source misses its morning
ops-daily fails when a source's newest row is older than its limit, 1 to 4 days by source. The ERROR line emails an alert within the hour.
src/connectors/ops/main.py:5
-
A number jumps overnight
Spend, calls and leads are each compared with the 28 days before. A z score above 3 joins the same failure.
src/connectors/ops/main.py:127
-
A tab is not ready
The email waits up to an hour for the pulls. A tab whose query fails says so, and the other tabs still send.
src/connectors/daily_email/main.py:487
What I tuned
Small choices that keep it fast and cheap.
-
Managed where it exists
Google Ads, the biggest source, arrives through Google's own Data Transfer. There is no connector of mine to keep alive for it.
deploy/google_ads_transfer.sh
-
Reread, then merge
Each pull rereads 4 to 35 days, because platforms restate recent days, and merges on a stable key. A missed morning heals on the next run.
src/common/bq.py:8
-
Descriptions live in the SQL
Every view and column description sits in its SQL file, so each redeploy writes them again. All 657 columns are described.
sql/marts/00_data_guide.sql
Guards
Rules the code enforces, not rules people remember.
-
Blocked TapClicks views
Call, form, review, comment and lead views are refused in code before the job reads its credentials. Editing the view list cannot turn them on.
src/connectors/tapclicks/main.py:62
-
No names in the workbook
The email reads BigQuery only. Names, phone numbers, emails and record IDs stay in BigQuery, and landing page links are cut at the query string.
src/connectors/daily_email/main.py:1
-
One send per morning
email-daily allows a single retry, since a retry after a good send would mail it twice. It runs as its own account with read access to BigQuery.
deploy/setup_daily_email.sh:46