Running dbt spatial models on BigQuery GIS

This page sets up a dbt project that builds spatial models on BigQuery GIS: a profile with a hard cost ceiling, a staging layer that refuses coordinates BigQuery would misinterpret, marts clustered on their geography column, and the two tests that catch the mistakes unique to a spherical, SRID-free engine.

When to use this approach

  • Your data already lives in BigQuery. Exporting geometry to PostGIS to run a join, then loading the result back, costs more in egress and orchestration than the join saves.
  • Your workload is analytical rather than transactional. BigQuery GIS is excellent at scanning and aggregating large spatial datasets and has no equivalent of an indexed point lookup, so a low-latency “which zone is this one point in” service belongs elsewhere — see choosing the right spatial adapter.
  • You can accept WGS84 with geodesic edges. There is no SRID and no planar mode. If an analysis requires a specific projection, compute it upstream; the trade-offs are laid out in warehouse-native GIS adapters.
Which spatial workloads fit BigQuery GIS and which belong on another engine Two columns. The good-fit column lists large-scale aggregation over geography, nightly batch joins of points to zones, and analysis where geodesic metres are the natural unit. The poor-fit column lists single-point low-latency lookups, workloads requiring a specific projected coordinate system, and anything depending on ST_Transform, none of which BigQuery provides. good fit aggregating billions of points over zones nightly batch point-in-polygon assignment in a DAG distances and areas wanted in geodesic metres poor fit low-latency single-point lookups for an API analysis pinned to a specific projected CRS pipelines that reproject mid-DAG

Prerequisites

  • dbt-bigquery ≥ 1.7 and a service account with roles/bigquery.dataEditor on the target dataset and roles/bigquery.jobUser on the project.
  • A dataset in the same location as the source data; a cross-region spatial join fails rather than running slowly.
  • Source geometry as WKT, GeoJSON or an existing GEOGRAPHY column. Shapefiles must be converted before load.
  • dbt-utils for the generic tests used below.
  • A budget alert on the project. maximum_bytes_billed protects a single query; an alert protects the month.

Step-by-step instructions

1. Write a profile with a cost ceiling

yaml
# profiles.yml
dbt_geospatial:
  target: bq_dev
  outputs:
    bq_dev:
      type: bigquery
      method: service-account
      keyfile: "{{ env_var('GOOGLE_APPLICATION_CREDENTIALS') }}"
      project: "{{ env_var('GCP_PROJECT') }}"
      dataset: "{{ env_var('BQ_DATASET', 'analytics_dev') }}"
      location: "{{ env_var('BQ_LOCATION', 'EU') }}"
      threads: 8
      priority: interactive
      maximum_bytes_billed: 200000000000
      job_execution_timeout_seconds: 1200
      job_retries: 1

Verify the connection and the ceiling both take effect:

bash
dbt debug --target bq_dev
# Expect: Connection test: [OK connection ok]

dbt run-operation execute_sql --args '{sql: "select st_geogpoint(13.4, 52.5) as p"}'
# Expect a single POINT(13.4 52.5) row — GEOGRAPHY constructors are available without any extension

2. Stage geometry so mis-projected input cannot enter

BigQuery accepts any pair of numbers as longitude and latitude. The staging layer is where that has to be caught, because nothing downstream will notice.

sql
-- models/staging/stg_zones.sql
{{ config(materialized = 'table', cluster_by = ['zone_geog']) }}

with parsed as (
    select
        zone_id,
        zone_name,
        zone_priority,
        safe.st_geogfromtext(geom_wkt, make_valid => true) as zone_geog
    from {{ source('ops', 'zones_raw') }}
),

bounded as (
    select *
    from parsed
    where zone_geog is not null
      and st_x(st_centroid(zone_geog)) between -180 and 180
      and st_y(st_centroid(zone_geog)) between -90 and 90
)

select * from bounded

Three deliberate choices are packed into that model. SAFE. turns a parse error into a null instead of failing the build, so one bad row cannot stop a nightly run. make_valid => true repairs the self-intersections that PostGIS would need ST_MakeValid for. And the centroid range filter rejects eastings and northings, which fall far outside the degree ranges and would otherwise be accepted silently.

Verify that nothing was silently dropped:

sql
select
    (select count(*) from {{ source('ops', 'zones_raw') }}) as raw_rows,
    (select count(*) from {{ ref('stg_zones') }}) as staged_rows;
-- Any gap is a parse failure or an out-of-range coordinate; investigate before proceeding
The BigQuery staging gate: three filters between raw text and a trusted GEOGRAPHY column Raw well-known-text rows enter from the left and pass through three gates in sequence. The first is a safe parse that sends unparseable rows to a quarantine branch. The second repairs self-intersections during construction. The third rejects coordinates outside the valid degree ranges, which is how projected eastings and northings are caught. Rows that survive all three become the trusted staging table. zones_raw geom_wkt 1 · SAFE parse SAFE.ST_GEOGFROMTEXT bad text → NULL 2 · repair on build make_valid => true self-intersections closed 3 · degree range ±180 / ±90 catches eastings stg_zones trusted quarantined rows — counted, reported, never joined

3. Cluster the marts on their geography column

sql
-- models/marts/mart_zone_daily_activity.sql
{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {'field': 'activity_date', 'data_type': 'date'},
    cluster_by = ['zone_id'],
    require_partition_filter = true
) }}

select
    date(p.observed_at) as activity_date,
    z.zone_id,
    count(*) as ping_count,
    count(distinct p.trip_id) as trip_count
from {{ ref('stg_trip_pings') }} as p
join {{ ref('stg_zones') }} as z
  on st_dwithin(p.ping_geog, z.zone_geog, 0)
{% if is_incremental() %}
where date(p.observed_at) >= date_sub(current_date(), interval 3 day)
{% endif %}
group by activity_date, zone_id

ST_DWithin(a, b, 0) is the BigQuery-idiomatic spelling of an intersection test in a join; ST_Intersects also works and both are accelerated by the same S2 machinery. require_partition_filter is the guard that stops a downstream analyst from scanning the whole history by accident.

Verify the physical layout landed as declared:

sql
select table_name, ddl
from `analytics_dev`.INFORMATION_SCHEMA.TABLES
where table_name = 'mart_zone_daily_activity';
-- Expect PARTITION BY activity_date and CLUSTER BY zone_id in the DDL

4. Add the two tests that matter on a spherical engine

yaml
# models/staging/schema.yml
models:
  - name: stg_zones
    columns:
      - name: zone_geog
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "st_area(zone_geog) between 1000 and 5000000000"
      - name: zone_id
        tests: [unique, not_null]

The area range is a units assertion in disguise: square metres for a real administrative zone fall in a predictable band, while mis-projected coordinates read as degrees produce values that miss it by orders of magnitude. Add a parity test for anything that also runs on a planar engine in CI:

sql
-- tests/assert_area_parity_with_planar.sql
select
    zone_id,
    abs(bq_area - planar_area) / nullif(bq_area, 0) as relative_diff
from {{ ref('int_zone_area_comparison') }}
where abs(bq_area - planar_area) / nullif(bq_area, 0) > 0.02

Verify the tests fail when they should by adding a fixture whose coordinates are in a projected CRS, following seeding geometry fixtures for dbt tests.

5. Watch what the build actually cost

sql
-- analyses/spatial_model_cost.sql
select
    regexp_extract(query, r'`[^`]+\.([a-z_]+)`') as model_hint,
    round(sum(total_bytes_processed) / pow(10, 12), 3) as tb_processed,
    count(*) as jobs
from `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time > timestamp_sub(current_timestamp(), interval 7 day)
  and statement_type = 'CREATE_TABLE_AS_SELECT'
group by model_hint
order by tb_processed desc
limit 20

Verify that the models at the top of that list are the ones you expect to be expensive. A staging model in the top three is a sign that a filter is missing, not that the data is large.

Where BigQuery cost accumulates across a spatial dbt build A stacked view of one nightly build. Staging models account for a small share of bytes processed, the spatial join in the intermediate layer accounts for the large majority, and the marts a moderate share. Annotations mark the join as the tuning target and note that a required partition filter prevents ad-hoc queries from adding an unbounded fourth block. bytes processed by layer, one nightly build staging 0.4 TB intermediate spatial join 3.9 TB marts 0.8 TB tune here first — 74% of the build grid-key prefilter, then the geography predicate require_partition_filter keeps ad-hoc queries from adding an unbounded fourth block

Configuration reference

Setting Where Accepted values Spatial note
maximum_bytes_billed profile output integer bytes The one setting that turns a runaway spatial join into a failed job instead of an invoice
location profile output EU, US, region name Must match the source data’s location; spatial joins cannot cross regions
cluster_by model config up to 4 columns A GEOGRAPHY column is a valid clustering key and is ordered along an S2 curve
partition_by model config date, timestamp, integer range Geography cannot be a partition column; pair a date partition with geography clustering
require_partition_filter model config true / false Stops unbounded scans of a large spatial mart
make_valid ST_GEOGFROMTEXT argument true / false Replaces the ST_MakeValid step PostGIS projects use
job_execution_timeout_seconds profile output integer A spatial cross join hits the timeout long before it finishes; treat that as the signal

Gotchas & edge cases

  • ST_GeogFromText without SAFE. fails the whole build on one malformed row. Use the safe form and count nulls, or one bad delivery blocks the nightly run.
  • BigQuery orients polygon rings by the smaller area. A polygon covering more than half the globe must be constructed with an explicit orientation, or it inverts — rare, but catastrophic when it happens to a “rest of world” catch-all shape.
  • ST_Area returns square metres always. Code ported from PostGIS that divides by a conversion factor will be wrong by that factor.
  • Long straight edges are not straight. A boundary defined by two distant points follows a great circle, so points near it can be assigned differently from a planar engine. Densify before comparing, as noted in warehouse-native GIS adapters.
  • Clustering is only applied on write. Adding cluster_by to an existing model does nothing until the table is rebuilt; a full refresh is required.

FAQ

Why does ST_DWithin(a, b, 0) appear instead of ST_Intersects?

Both work and both are accelerated. The zero-distance ST_DWithin form is idiomatic in BigQuery because it makes the tolerance explicit, and because widening it later — to catch geometry that nearly touches after a simplification step — is a one-character change rather than a rewrite.

How do I handle data that is genuinely in a projected CRS?

Reproject before loading. BigQuery cannot do it: there is no ST_Transform and no SRID to transform between. Convert in the ingestion tool, in DuckDB, or in PostGIS, and load WGS84. The staging range filter above exists precisely to catch the cases where someone forgets.

Can I still develop locally without billing every iteration?

Yes — build against DuckDB with the same models, using dispatched macros for the handful of functions that differ, and reserve BigQuery for integration runs. Remember that DuckDB is planar, so it validates SQL and shape, not spherical results; keep a small parity test that runs against BigQuery before release.

What replaces ANALYZE and index maintenance?

Nothing needs replacing — BigQuery maintains its own metadata and re-clusters tables in the background. The maintenance you do owe is different: watch bytes processed per model over time, because a query that quietly stops pruning is the equivalent of an index that stopped being used.

Up: Part of Warehouse-Native GIS Adapters.