Enrich Supabase with BigQuery public data

import { Aside } from ‘@astrojs/starlight/components’;

Your operational data lives in Supabase. Some of the most useful reference data doesn’t — it lives in Google’s BigQuery public datasets: US ZIP/postal boundaries, census demographics, weather, currency rates, and more, all free to query. SQLAnvil lets you join the two in place and write the enriched result back into Supabase — without standing up a separate ETL pipeline to copy those datasets around.

Why you’d want this

Geospatial enrichment — map customer ZIPs to coordinates, compute distances, resolve postal boundaries (e.g. “customers within 25 miles of a store”).
Reference/lookup data — currency exchange rates, public holidays, census demographics, NAICS codes — data you shouldn’t have to maintain a copy of.
One source of truth — query the public dataset live (always current) instead of forking it into your own schema and keeping it in sync.

How it works

SQLAnvil’s wrapper() action declares a live BigQuery Foreign Data Wrapper — one call sets up the connection and exposes the BigQuery table as a ref()-able foreign table. From there it’s plain Supabase/Postgres SQL:

bigquery-public-data            (live FDW query)
        │
        ▼
foreign table  ──►  staging view  ──►  cached table (materialized in Supabase)
                                              │
your operational tables ──────────────────►  join + transform  ──►  enriched result

A typical pipeline:

Declare the bridge — wrapper({ provider: "bigquery", foreignTables: [...] }) in a .js file.
Materialize once — SELECT * FROM ${ref("the_foreign_table")} into a Supabase table, so downstream joins don’t re-hit BigQuery on every run.
Join + enrich — join the cached reference data with your operational tables and land the result back in a Supabase schema your app reads from.

See the Foreign Data Wrappers guide for the full mechanics — provider presets, credentials, and the compiled DDL.

Worked example

The supabase_bigquery_mailing_list example does exactly this end to end: it builds a proximity mailing list of customers who purchased in the last year and live within a radius of a target ZIP, by joining Supabase orders with Google’s bigquery-public-data.geo_us_boundaries.zip_codes over a live FDW and computing distance with PostGIS. Clone it, point it at your Supabase project, and run.

Getting started

npm install -g @sqlanvil/cli
sqlanvil init my-project --warehouse supabase

Then follow the Foreign Data Wrappers guide to add the BigQuery bridge and store your service-account key in Vault.