mailing_list example: scheduled OpenAddresses staging loader on GCP Cloud Run

[ihistand]

Goal

Complete the OpenAddresses story in examples/supabase_bigquery_mailing_list (652bf2f) with a scheduled ingestion path: a Python staging loader deployed on GCP Cloud Run Jobs, fired by Cloud Scheduler, feeding the example's existing type: "import" action.

Architecture (division of labor)

Cloud Scheduler (cron, e.g. weekly)
      │
      ▼
Cloud Run Job: load_openaddresses (Python container)
      │  download region/state from OpenAddresses.io → unzip → normalize filename
      ▼
gs://<staging-bucket>/openaddresses/<region>.csv       ← staging only; NO warehouse credentials
      │
      ▼
sqlanvil import action (location: gs://…, format: csv)  ← already in the example
      │  run on a SQLAnvil Cloud workflow cron (or local `sqlanvil run`)
      ▼
oa_ext.openaddresses_us → stg_addresses → addresses_cache → mailing_list

Key property: the Python job only stages files — it never holds database credentials. The warehouse load + downstream models + assertions run through the normal sqlanvil workflow with run history.

Deliverables

• examples/supabase_bigquery_mailing_list/loader/ — load_openaddresses.py (region/state arg, download from OpenAddresses, unzip, upload to GCS), Dockerfile, requirements.txt
• Deploy runbook in the loader README: gcloud builds submit → gcloud run jobs create → gcloud scheduler jobs create http (job execution via OIDC), incl. required IAM (storage.objectAdmin on the staging bucket, run.invoker for the scheduler SA)
• Example README: new "Scheduling the refresh" section — loader cron + SQLAnvil Cloud workflow cron pairing, and the storage: credentials entry for gs:// in .df-credentials.json
• Switch guidance for import.location from the bundled sample CSV to the staged gs:// URI (Cloud-compatible — hosted runs reject local paths)

Notes

• Deploy pattern mirrors the existing SQLAnvil Cloud runner (Cloud Run Jobs, image via Cloud Build), so no new platform concepts.
• Keep the loader minimal: stage the standard 11-column CSV as-is; normalization stays in stg_addresses (SQL), not Python.
• Adapted from a production BigQuery ingestion plan (staged load, clustered target) — here the sqlanvil import + Postgres composite indexes fill those roles.

[ihistand]

Related: #48 (Python script actions) would make the in-DAG local/CI variant of this loader one type: "python" action — this issue stays as the scheduled/hosted variant (user-owned Cloud Run + Cloud Scheduler), which is also the only shape that works on hosted SQLAnvil Cloud until the managed-execution question is decided.

[ihistand]

Update: 1.20.0 shipped python script actions (#48), which covers the in-DAG local/BYO-CI variant of this: the OpenAddresses loader now runs as a python: action inside the pipeline (examples/supabase_bigquery_mailing_list — script stages the CSV, import loads it, one sqlanvil run). A cron'd workflow (or any scheduler invoking the CLI) replaces the Cloud-Scheduler-plus-Cloud-Run pairing for users who run their own compute.

This issue stays open as the scheduled/hosted variant — running the loader on the user's own GCP Cloud Run where no always-on machine exists. Note hosted SQLAnvil Cloud rejects script actions at compile (user code doesn't execute on the shared runner), so user-owned Cloud Run remains the relevant deployment target here.