Warehouse-Native GIS Adapters

PostGIS is the reference implementation of spatial SQL, but a growing share of dbt geospatial projects never touch it: the warehouse already has a spatial type, the data already lives there, and moving hundreds of gigabytes of geometry into Postgres to run a point-in-polygon join is not a serious proposal. This topic covers what actually changes when the execution engine is BigQuery, Snowflake or Redshift rather than PostGIS — and it is more than a function-name mapping. You lose the index as a tuning lever, you gain a cost model measured in bytes scanned, and in BigQuery’s case you inherit spherical geometry semantics that quietly return different answers from planar PostGIS for the same inputs.

The decision of which engine to target belongs upstream of this page, in choosing the right spatial adapter. What follows assumes the choice is made — often by the platform team, not the analytics engineers — and concentrates on building a project that is correct and affordable on the engine you have.

Prerequisites checklist

• A dbt adapter matching the warehouse: dbt-bigquery ≥ 1.7, dbt-snowflake ≥ 1.7, or dbt-redshift ≥ 1.7. Spatial support is in the warehouse, not the adapter, so no spatial-specific plugin is required.
• Warehouse permissions to create clustered tables, since clustering replaces indexing as the physical tuning lever on every engine in this topic.
• A clear decision on the storage type — GEOGRAPHY everywhere, or GEOMETRY with a projected SRID where the engine supports it. Mixing them across models is the most expensive mistake available here.
• A cost guardrail before the first large model runs. A single careless spatial cross join on a consumption-priced warehouse is a memorable invoice; the controls are in spatial observability and cost control.
• A local engine for development, normally DuckDB, so iteration does not bill per query — see DuckDB spatial extension integration.

Architecture context: what moves when the engine changes

The dbt project structure is unchanged — staging validates, intermediate joins, marts serve. What changes is which layer carries the tuning, and which levers exist at all.

The consequence for dbt is concrete: the post-hook that creates a GiST index has no counterpart. Physical layout on these engines is declared in the model’s config() block and applied by the warehouse as it writes the table. That is better in one respect — it cannot drift out of sync with the model — and worse in another, because you cannot add it to an existing table without a rewrite.

Engine comparison

The rows that cause real incidents are the coordinate-system row and the distance-semantics row. BigQuery has no SRID: every GEOGRAPHY is WGS84 and every edge is a geodesic on a sphere. Load a dataset in a projected CRS and BigQuery will not warn you — it will interpret the easting and northing as longitude and latitude, and produce results that look like data rather than errors. The staging layer therefore has a harder job on BigQuery than on PostGIS, and the governance rules in CRS governance policy become load-bearing rather than advisory.

Configuration walkthrough

A BigQuery spatial model declares its physical layout inline; there is nothing to add afterwards.

-- models/marts/mart_zone_activity.sql
{{ config(
    materialized = 'table',
    partition_by = {'field': 'activity_date', 'data_type': 'date'},
    cluster_by = ['zone_geog']
) }}

select
    activity_date,
    zone_id,
    any_value(zone_geog) as zone_geog,
    count(*) as ping_count
from {{ ref('int_pings_zoned') }}
group by activity_date, zone_id

Clustering on a GEOGRAPHY column is meaningful: BigQuery orders the data by an S2 space-filling curve so that spatially near rows land in the same blocks, and a later spatial predicate reads fewer of them. Combine it with date partitioning and a spatial mart usually reads a small fraction of the table.

Snowflake expresses the same intent with a clustering key, and adds a genuinely different option — a search optimization service entry for point lookups:

{{ config(
    materialized = 'table',
    cluster_by = ['to_geography(zone_geog)'],
    post_hook = "alter table {{ this }} add search optimization on geo(zone_geog)"
) }}

Redshift’s lever is the sort key, declared through the adapter’s sort config, and it works on the same principle: co-locate rows that will be scanned together.

{{ config(materialized = 'table', sort = ['zone_id'], dist = 'zone_id') }}

Keep the profile itself boring. Everything spatial belongs in model configuration, so switching targets does not rewrite the project:

# profiles.yml
dbt_geospatial:
  target: bq_dev
  outputs:
    bq_dev:
      type: bigquery
      method: service-account
      project: "{{ env_var('GCP_PROJECT') }}"
      dataset: "{{ env_var('BQ_DATASET', 'analytics_dev') }}"
      location: EU
      threads: 8
      maximum_bytes_billed: 200000000000   # a hard stop before an expensive mistake

maximum_bytes_billed is the single most valuable line in a BigQuery spatial profile. A spatial join written without a bounding predicate will otherwise scan everything, and the first sign will be the bill.

Core implementation: writing SQL that survives an engine change

Function names overlap heavily but not completely, and the differences cluster in exactly the places spatial pipelines depend on. Wrap them once, in a macro, rather than in every model.

-- macros/spatial/geo_dwithin.sql
{% macro geo_dwithin(a, b, metres) %}
  {{ return(adapter.dispatch('geo_dwithin', 'spatial')(a, b, metres)) }}
{% endmacro %}

{% macro default__geo_dwithin(a, b, metres) %}
  st_dwithin({{ a }}, {{ b }}, {{ metres }})
{% endmacro %}

{% macro bigquery__geo_dwithin(a, b, metres) %}
  st_dwithin({{ a }}, {{ b }}, {{ metres }})
{% endmacro %}

{% macro postgres__geo_dwithin(a, b, metres) %}
  st_dwithin({{ a }}::geography, {{ b }}::geography, {{ metres }})
{% endmacro %}

The cast in the Postgres implementation is the whole point of the macro: on PostGIS a geometry column in EPSG:4326 measures ST_DWithin in degrees, while BigQuery’s GEOGRAPHY always measures in metres. Without the cast, the same model silently uses two different radii on two engines. The dispatch mechanism itself is covered in cross-engine UDF portability.

Three more differences worth wrapping the same way:

• Validity. PostGIS has ST_IsValid and ST_MakeValid; BigQuery has neither, because its GEOGRAPHY constructor rejects invalid input at load time. On BigQuery the equivalent check runs at ingestion, using SAFE.ST_GEOGFROMTEXT and a null test.
• Geometry construction from text. ST_GeomFromText on PostGIS, ST_GEOGFROMTEXT on BigQuery, TO_GEOGRAPHY on Snowflake — same intent, three spellings, and different behaviour on malformed input.
• Area and length units. Geodesic square metres on BigQuery regardless of location; on PostGIS, whatever the SRID’s units are, which for 4326 is square degrees and is almost never what anyone wants.

Validation and testing

The tests that matter on warehouse engines differ from the PostGIS set, because the failure modes differ.

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"
              config:
                where: "zone_geog is not null"

An area range test does on BigQuery what an SRID assertion does on PostGIS: it catches coordinates that were interpreted as longitude and latitude when they were actually eastings and northings. Projected coordinates read as degrees produce areas that are absurd by many orders of magnitude, so a loose range with the right units catches the whole class of mistake.

Add a null-after-parse test at the load boundary, since BigQuery’s safe constructors return null rather than raising:

-- tests/assert_geography_parsed.sql
select count(*) as unparsed
from {{ ref('stg_zones') }}
where zone_geog is null
having count(*) > 0

Finally, guard the cost. A dbt test can assert against the warehouse’s own metadata:

-- tests/assert_spatial_model_bytes_bounded.sql
select job_id, total_bytes_processed
from `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time > timestamp_sub(current_timestamp(), interval 1 day)
  and statement_type = 'CREATE_TABLE_AS_SELECT'
  and total_bytes_processed > 5e11

One more test earns its place on any engine without an SRID column: a bounding-extent assertion per source. Every real dataset has a known footprint — a city, a country, a service territory — and a row outside it is either a data error or a coordinate-order mistake, the classic latitude-and-longitude swap that produces a point in the Indian Ocean. Expressing the expected extent as a constant in the test makes both failures loud on the day they arrive rather than on the day someone opens a map.

-- tests/assert_zones_within_expected_extent.sql
select zone_id
from {{ ref('stg_zones') }}
where not st_within(
    zone_geog,
    st_geogfromtext('POLYGON((5.8 47.2, 15.1 47.2, 15.1 55.1, 5.8 55.1, 5.8 47.2))')
)

A note on Databricks, which sits slightly outside this comparison: its spatial support has historically come from Apache Sedona or the built-in H3 functions rather than from a native geometry type, so a dbt project targeting it looks closer to the grid-key approach than to the type-driven approach used on the three engines above. The pipeline shape is the same — validate, join, serve — but the portable layer has to be the grid key rather than the geometry column, because the geometry representation itself differs.

Advanced patterns

Join on grid keys, not geometry, when the engine has no index. An S2 or H3 cell id turns the first pass of a spatial join into an equality join the warehouse can hash and prune, with geometry used only to resolve boundary cells. On BigQuery this routinely turns a query that scans a whole table into one that scans a fraction of it; the macro layer is in discrete global grid macros.

Materialize the expensive geometry once and carry keys downstream. Bytes scanned is the cost driver, and geometry columns are wide. A mart that stores zone_id and joins the shape only when a map needs it is cheaper on every query than one that carries geometry everywhere — the serving patterns in serving spatial data to consumers develop this.

Let the warehouse do the clustering, but verify it happened. Clustering is declared, not enforced; a table written by a path that ignores the config is silently unclustered. Query the information schema after the build and assert the clustering columns are what the model declared.

Keep DuckDB as the development engine, but never as the correctness oracle. DuckDB uses planar geometry, so a model that passes locally can still be wrong on BigQuery’s spherical edges. Run parity checks on a small, representative extent against the real engine before release.

Troubleshooting

FAQ

Related

• Running dbt Spatial Models on BigQuery GIS — profile, clustering and cost controls end to end.
• Snowflake GEOGRAPHY vs PostGIS Geometry in dbt — where the two type systems disagree.
• Migrating PostGIS SQL to BigQuery GIS Functions — the function-by-function port, including what has no equivalent.
• Choosing the Right Spatial Adapter — making the engine decision in the first place.

Up: Part of Core Fundamentals & Architecture for dbt Geospatial.