Cross-Engine UDF Portability

The same spatial transformation almost never survives a change of warehouse untouched. A model that computes a buffered service area on PostGIS calls ST_Buffer on a projected geometry; the identical intent on BigQuery GIS runs against a spheroidal GEOGRAPHY where a metric buffer means something different, on Snowflake it needs TO_GEOGRAPHY wrappers and a different function-name casing, and on the DuckDB spatial extension it depends on which GEOS-backed functions the build exposes. The ST_* families look interchangeable and are not: names diverge (ST_DWithin vs ST_DWITHIN), type systems diverge (planar geometry vs spheroid-only GEOGRAPHY), casts diverge, and a handful of functions exist on one engine and simply do not on another. This page is part of the Advanced Spatial Macros & UDF Patterns collection, and it covers how to keep one dbt codebase compiling to correct SQL on every one of those engines instead of forking the project per warehouse.

The mechanism dbt gives you is adapter.dispatch: a single macro name that resolves to a per-adapter implementation at compile time, chosen by the active target. Wrapping each divergent spatial operation in a dispatched shim, backing those shims with a declared capability matrix, and proving parity with compile-and-diff tests turns “works on PostGIS, breaks on Snowflake” from a production incident into a caught-in-CI failure. The result is portability that is intentional and testable rather than accidental — the difference between one codebase that runs everywhere and four codebases that drift apart.

Prerequisites checklist

Before wrapping any spatial function in a dispatched macro, confirm the project can resolve and test across every target it claims to support:

  • dbt-core ≥ 1.7 so adapter.dispatch and namespace search_order behave consistently, plus every adapter you target installed: dbt-postgres against PostGIS ≥ 3.3, dbt-duckdb with the spatial extension, dbt-bigquery, and/or dbt-snowflake. The engine trade-offs behind this list are laid out in choosing the right spatial adapter.
  • A decision on geometry vs geography per engine, because portability is impossible without it: PostGIS and Snowflake support both, BigQuery is GEOGRAPHY-only, DuckDB is planar geometry-only. Pin the intended type per adapter before writing shims.
  • A canonical project SRID agreed up front, so every engine reprojects to the same frame at the staging boundary rather than each shim guessing.
  • One profile per target with connections wired through dbt’s env_var() pattern — never hardcode hosts or credentials — so dbt compile --target <name> can exercise each dialect in CI.
  • A macros namespace you control (your project name, or a package like spatial_utils) that the dispatch layer searches first, letting you override any packaged implementation.
yaml
# dbt_project.yml — project-wide config the portable shims consume
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '4326') }}"

# Search this project's macros before any installed package's,
# so a local override always wins over a shipped default.
dispatch:
  - macro_namespace: spatial_utils
    search_order: ['my_project', 'spatial_utils']

Architecture context

A portable spatial project has three layers stacked between the model author and the warehouse: the interface (one macro name per logical operation, e.g. spatial_buffer), the dispatch resolver (adapter.dispatch picking an implementation by target.type), and the per-adapter implementations (default__, postgres__, bigquery__, snowflake__, duckdb__). Model SQL only ever names the interface; the resolver and the implementations absorb every dialect difference. A capability matrix sits alongside as data — which engine supports which operation and type — so a shim can fail loudly at compile time when a model asks an engine for something it cannot do, rather than emitting SQL that errors at run time or, worse, returns silently wrong results.

One dispatched spatial macro resolving to four warehouse dialects under a capability matrix A single dbt macro call, spatial_buffer of geom and 500 metres, enters an adapter.dispatch resolver that branches on target.type. The resolver selects one of four per-adapter implementations. PostGIS emits ST_Buffer on a projected geometry. DuckDB emits ST_Buffer on planar geometry. BigQuery emits ST_BUFFER on spheroidal geography. Snowflake emits ST_BUFFER wrapping the input in TO_GEOGRAPHY. A capability matrix panel below records which engine supports geometry, geography, planar buffer, and spheroidal buffer, and the resolver consults it to raise a compiler error when an engine cannot satisfy the requested operation. One interface {{ spatial_buffer( 'geom', 500) }} adapter.dispatch branch on target.type postgres__ · projected geometry ST_Buffer(geom, 500) duckdb__ · planar geometry ST_Buffer(geom, 500) bigquery__ · spheroid geography ST_BUFFER(geom, 500) snowflake__ · cast to geography ST_BUFFER(TO_GEOGRAPHY(geom), 500) Capability matrix — the resolver consults it before emitting SQL geometry geography planar buffer spheroid buffer postgres yes yes yes yes duckdb yes no yes no bigquery no yes no yes snowflake: both types, geography-first

Configuration walkthrough

Two pieces of configuration make dispatch predictable. The first is the dispatch block in dbt_project.yml (shown above), which fixes the namespace search order so your project’s overrides always win over a package’s defaults. The second is a single source-of-truth macro that returns the capability matrix, so every shim reads its support facts from one place instead of re-encoding them inline.

sql
-- macros/spatial/spatial_capabilities.sql
-- One authoritative map of what each adapter can do.
-- Shims read this instead of hardcoding per-engine branches.
{% macro spatial_capabilities() %}
  {% do return({
    'postgres':  {'geometry': true,  'geography': true,  'planar_buffer': true,  'spheroid_buffer': true},
    'duckdb':    {'geometry': true,  'geography': false, 'planar_buffer': true,  'spheroid_buffer': false},
    'bigquery':  {'geometry': false, 'geography': true,  'planar_buffer': false, 'spheroid_buffer': true},
    'snowflake': {'geometry': true,  'geography': true,  'planar_buffer': true,  'spheroid_buffer': true}
  }) %}
{% endmacro %}

A small helper turns that map into a compile-time guard any shim can call before it emits SQL. An unsupported request stops the build with a clear message instead of producing a query that fails deep in the warehouse — or returns wrong numbers:

sql
-- macros/spatial/require_capability.sql
{% macro require_capability(feature) %}
  {%- set caps = spatial_capabilities() -%}
  {%- set engine = target.type -%}
  {%- if engine not in caps -%}
    {{ exceptions.raise_compiler_error("No spatial capability profile for target: " ~ engine) }}
  {%- elif not caps[engine].get(feature, false) -%}
    {{ exceptions.raise_compiler_error(
        "Target '" ~ engine ~ "' does not support '" ~ feature ~ "'. "
        ~ "Pick a supported engine or a portable alternative.") }}
  {%- endif -%}
{% endmacro %}

Core implementation

The dispatched interface

Each portable operation is one macro whose body is a single adapter.dispatch call. The macro name model authors call is spatial_buffer; dbt resolves it to postgres__spatial_buffer, bigquery__spatial_buffer, and so on, based on target.type, falling back to default__spatial_buffer when no adapter-specific variant exists. The end-to-end mechanics of writing these variants — including namespace ordering and per-target compile checks — are the subject of dispatching spatial macros across warehouses.

sql
-- macros/spatial/spatial_buffer.sql
{% macro spatial_buffer(geom, distance_m) %}
  {{ return(adapter.dispatch('spatial_buffer', 'spatial_utils')(geom, distance_m)) }}
{% endmacro %}

{% macro default__spatial_buffer(geom, distance_m) %}
  {{ exceptions.raise_compiler_error(
      "spatial_buffer has no implementation for " ~ target.type) }}
{% endmacro %}

Per-adapter implementations

Each variant encodes exactly one engine’s dialect: function-name casing, type handling, and the cast (if any) that makes the distance mean metres. The PostGIS and DuckDB variants operate on projected geometry where a buffer distance is in the CRS’s own units; the BigQuery and Snowflake variants operate on GEOGRAPHY where the buffer is spheroidal in metres. Each variant calls require_capability first so an unsupported combination never reaches the compiled SQL.

sql
-- macros/spatial/spatial_buffer.sql (continued)

{% macro postgres__spatial_buffer(geom, distance_m) %}
  {{ require_capability('planar_buffer') }}
  ST_Buffer({{ geom }}, {{ distance_m }})
{% endmacro %}

{% macro duckdb__spatial_buffer(geom, distance_m) %}
  {{ require_capability('planar_buffer') }}
  ST_Buffer({{ geom }}, {{ distance_m }})
{% endmacro %}

{% macro bigquery__spatial_buffer(geom, distance_m) %}
  {{ require_capability('spheroid_buffer') }}
  ST_BUFFER({{ geom }}, {{ distance_m }})
{% endmacro %}

{% macro snowflake__spatial_buffer(geom, distance_m) %}
  {{ require_capability('spheroid_buffer') }}
  ST_BUFFER(TO_GEOGRAPHY({{ geom }}), {{ distance_m }})
{% endmacro %}

The load-bearing detail is that the model never sees any of this. A mart references the interface and stays adapter-agnostic:

sql
-- models/marts/mart_service_areas.sql
{{ config(materialized='table') }}

SELECT
  region_id,
  {{ spatial_buffer('geom', 500) }} AS service_area
FROM {{ ref('stg_regions') }}
WHERE geom IS NOT NULL

Feature detection over silent fallback

The temptation on any missing capability is a silent fallback — emit something “close enough” on the engine that cannot do the real thing. Resist it for spatial work: a planar buffer computed as if it were spheroidal, or a geography operation quietly downgraded to geometry, produces plausible numbers that are wrong. Feature detection through require_capability makes the pipeline refuse rather than approximate. When an operation genuinely has a portable equivalent — for example expressing a radius filter through the portable ST_DWithin predicate rather than an engine-specific KNN idiom — encode that equivalent explicitly in the shim, as covered in writing reusable ST_DWithin macros in dbt. The dispatch framework these shims plug into is developed in full in building custom spatial macros.

Validation and testing

Portability that is not tested is portability that has already broken on some engine you have not compiled against lately. Two layers of testing keep the shims honest: compile parity (the same interface produces the expected dialect on each target) and result parity (the same input produces the same answer within tolerance, where the operation is defined on that engine).

Compile parity needs no running warehouse — dbt renders the SQL per target and you assert the dialect landed:

bash
# Render the model against each configured target and inspect the output.
for tgt in postgres duckdb bigquery snowflake; do
  dbt compile --select mart_service_areas --target "$tgt"
  echo "== $tgt =="
  grep -o 'ST_[A-Za-z_]*' target/compiled/**/mart_service_areas.sql | sort -u
done
# Expect ST_Buffer for postgres/duckdb, ST_BUFFER (+ TO_GEOGRAPHY on snowflake)
# for the geography engines.

Result parity is best expressed as a dbt test that runs on whichever engine CI is using. Seed a small fixture with a known-answer geometry, run the shim, and assert the output falls in an expected band — buffers on projected vs spheroidal engines will differ slightly, so assert a tolerance, not equality:

yaml
# models/marts/_marts.yml
version: 2
models:
  - name: mart_service_areas
    columns:
      - name: service_area
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "ST_IsValid(service_area)"
          - dbt_utils.expression_is_true:
              # a 500 m buffer of a point should cover between ~0.7 and ~0.9 km²
              expression: "ST_Area(service_area::geography) BETWEEN 700000 AND 900000"
              config:
                where: "region_id = 'fixture_point'"

Run the whole gate in CI against the DuckDB spatial extension for speed, and periodically against the production adapter to catch dialect drift. Structuring that fast pre-merge validation is covered in choosing the right spatial adapter; the reprojection contract every shim assumes is the job of the upstream geometry transformation pipeline.

Advanced patterns

Capability-driven model selection. Some operations have no equivalent on an engine at all — there is no honest planar buffer on BigQuery’s spheroid-only GEOGRAPHY. Rather than fail the whole run, gate the model itself: skip or route it based on the capability map, so a project targeting BigQuery simply does not build the planar-only marts.

sql
{{ config(
    enabled = (target.type in ['postgres', 'duckdb', 'snowflake'])
) }}

Shim composition. Portable operations compose. A spatial_buffer shim and a spatial_transform shim can be nested so a model expresses “reproject to metric, then buffer” once, and every engine gets its correct spelling of both steps. Keep composition inside macros, not model SQL, so the model stays a description of intent.

A portable geography path. When metric accuracy matters on every engine, standardize on the geography domain where it exists and reproject-then-measure where it does not. The shim for a distance or area then has two shapes — a native spheroidal call on geography engines, and a ST_Transform-to-metric-SRID-then-planar call on geometry-only engines — reconciled behind one interface so the mart never chooses.

Version skew inside one engine. Function availability shifts across engine versions, not just across engines: a ST_* added in PostGIS 3.4 is absent on 3.2. Extend the capability matrix with a version dimension when you support a range, and detect it with a run_query probe (SELECT PostGIS_Version()) in an on-run-start hook so an under-provisioned target fails at startup rather than mid-build.

Troubleshooting

Symptom Root cause Fix
function st_buffer(...) does not exist on one engine only Dialect casing or a function that engine lacks; the interface emitted the wrong variant Confirm the <engine>__ variant exists and dispatch search_order names your project; add the variant or a capability guard
Compiles everywhere but areas/distances differ by orders of magnitude Planar geometry result compared against spheroidal GEOGRAPHY result Standardize the measurement domain per operation; assert with a tolerance band, and reproject to a metric SRID before planar math
Dispatch always picks default__ and raises the “no implementation” error macro_namespace in the dispatch(...) call does not match the dispatch block, or search_order omits your project Align the namespace string in every shim with dbt_project.yml; list your project first in search_order
Model errors at run time on BigQuery with a cast/type message A geometry-typed input reached a GEOGRAPHY-only engine Add a require_capability('geometry') guard and reproject/cast in staging; declare the per-engine type explicitly
A package override is ignored The package namespace is searched before your project Put your project first in the search_order for that macro_namespace
TO_GEOGRAPHY silently reinterprets projected coordinates as lon/lat on Snowflake A projected GEOMETRY column was wrapped for a spheroidal call Standardize storage type per engine; only wrap columns that are truly lon/lat, and reproject others first

FAQ

Do I need a separate macro file per adapter?

No. adapter.dispatch resolves by macro name suffix, so all variants of one operation — default__, postgres__, bigquery__, snowflake__, duckdb__ — can live in a single .sql file next to the interface macro. Group by operation, not by engine, so everything about spatial_buffer is in one place and a new engine means adding one macro, not editing four files.

How is this different from just branching on target.type inside one macro?

A single macro with an {% if target.type == ... %} chain works for one or two functions but does not scale: the capability logic, the dialect logic, and the fallback logic all tangle in one body, and a package cannot override just the Snowflake branch. adapter.dispatch separates the interface from the implementations, lets each engine’s variant be overridden independently, and lets installed packages ship defaults you can selectively replace via search_order.

What should a shim do when an engine cannot perform the operation at all?

Fail at compile time with raise_compiler_error, via a capability guard, rather than emit an approximation. A planar buffer faked on a spheroid-only engine returns numbers that look reasonable and are wrong, which is far more dangerous than a build that stops. If a genuine portable equivalent exists, encode it explicitly; if it does not, gate the model off for that target with config(enabled=...).

How do I test portability without standing up four warehouses?

Compile parity needs no warehouse: dbt compile --target <name> renders SQL for each adapter and you diff the generated ST_* calls. Run that for every configured target in CI. Layer result parity on top only where you have a live connection — typically the DuckDB spatial extension for fast pre-merge checks and the production adapter on a schedule — and assert tolerances, not exact equality, because spheroidal and planar engines legitimately differ.

↑ Back to Advanced Spatial Macros & UDF Patterns

Explore this section