Dispatching Spatial Macros Across Warehouses

This page walks through implementing adapter.dispatch for a single spatial function in dbt — writing the default__ and postgres__ / bigquery__ / snowflake__ / duckdb__ variants, fixing the namespace search order, and compile-testing every target so one macro call emits correct SQL on all four warehouses.

When to use this approach

Reach for adapter.dispatch — rather than an inline {% if target.type %} branch — when any of these hold:

• One logical spatial operation must run on more than one engine. A validity sweep or reprojection wrapper that ships on PostGIS in production but validates on the DuckDB spatial extension in CI needs a clean per-adapter split. This is the concrete build step behind the broader cross-engine UDF portability strategy.
• You want other engineers — or an installed package — to override just one engine’s implementation. Dispatch lets a Snowflake variant be replaced without touching the PostGIS one, which an {% if %} chain cannot do cleanly. The framework it plugs into is building custom spatial macros.
• The operation has real dialect divergence — function-name casing, a TO_GEOGRAPHY cast, a type the engine lacks. For a distance predicate specifically, the worked example is writing reusable ST_DWithin macros in dbt.

Prerequisites

• dbt Core 1.7+ so adapter.dispatch and namespace search_order behave consistently.
• At least two configured targets to make dispatch worth it: for example dbt-postgres against PostGIS 3.x plus dbt-duckdb with the spatial extension, and optionally dbt-bigquery / dbt-snowflake.
• A macros namespace you control — this guide uses spatial_utils, which can be your project name or a package name.
• Connection secrets wired through dbt’s env_var() pattern, one output per target in profiles.yml.
• A canonical project SRID chosen up front so each variant reprojects to the same frame.

# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '4326') }}"

Step-by-step instructions

1. Declare the dispatch namespace and search order

Before writing any variant, tell dbt which namespaces to search and in what order. Listing your project first means a local override always beats a packaged default. Skip this and dbt still dispatches, but you lose deterministic override precedence the moment a package ships the same macro name.

# dbt_project.yml
dispatch:
  - macro_namespace: spatial_utils
    search_order: ['my_project', 'spatial_utils']

Verify the config parses and the namespace is recognized:

dbt parse
# A clean parse with no "dispatch" warnings means the namespace and
# search_order are well-formed.

2. Write the interface macro and its default

The interface is the only name model authors call. Its entire body is one adapter.dispatch call that resolves the implementation by the active adapter. The default__ variant is the fallback for any engine without a specific implementation — for spatial work, make it fail loudly rather than guess.

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

{% macro default__spatial_centroid(geom) %}
  {{ exceptions.raise_compiler_error(
      "spatial_centroid has no implementation for target: " ~ target.type) }}
{% endmacro %}

The second argument to adapter.dispatch — 'spatial_utils' — is the macro namespace, and it must match the macro_namespace you declared in step 1.

Verify the interface resolves to the default when no variant exists yet:

dbt compile --select some_model_using_spatial_centroid --target duckdb
# Expect a compiler error naming "duckdb" — proof dispatch reached default__.

3. Implement the per-adapter variants

Add one macro per engine, suffixed with the adapter name dbt reports as target.type. Each encodes exactly that engine’s dialect. ST_Centroid is broadly available, but casing and type handling still diverge: PostGIS and DuckDB operate on planar geometry; BigQuery is GEOGRAPHY-only with uppercase functions; Snowflake wraps the input in TO_GEOGRAPHY for spheroidal semantics.

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

{% macro postgres__spatial_centroid(geom) %}
  ST_Centroid({{ geom }})
{% endmacro %}

{% macro duckdb__spatial_centroid(geom) %}
  ST_Centroid({{ geom }})
{% endmacro %}

{% macro bigquery__spatial_centroid(geom) %}
  ST_CENTROID({{ geom }})
{% endmacro %}

{% macro snowflake__spatial_centroid(geom) %}
  ST_CENTROID(TO_GEOGRAPHY({{ geom }}))
{% endmacro %}

Reference the interface — never a specific variant — from model SQL so the model stays adapter-agnostic:

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

SELECT
  region_id,
  {{ spatial_centroid('geom') }} AS center
FROM {{ ref('stg_regions') }}
WHERE geom IS NOT NULL

Verify each variant is selected on its own target by compiling and inspecting the rendered SQL:

dbt compile --select mart_region_centers --target postgres
grep -o 'ST_[A-Za-z_]*\|TO_GEOGRAPHY' \
  target/compiled/**/mart_region_centers.sql
# postgres -> ST_Centroid ; bigquery -> ST_CENTROID ;
# snowflake -> ST_CENTROID + TO_GEOGRAPHY ; duckdb -> ST_Centroid

4. Compile-test every target in one pass

Portability is only real if every target is exercised. Loop dbt compile over each configured target and assert the expected dialect landed. This needs no running warehouse for the SQL-rendering step — dbt compiles against the adapter’s grammar without connecting for a pure compile of a non-introspective model.

#!/usr/bin/env bash
set -euo pipefail
for tgt in postgres duckdb bigquery snowflake; do
  echo "== compiling for $tgt =="
  dbt compile --select mart_region_centers --target "$tgt"
  grep -o 'ST_[A-Za-z_]*\|TO_GEOGRAPHY' \
    target/compiled/**/mart_region_centers.sql | sort -u
done

Verify the output shows the correct per-engine spelling for each target, and that no target fell through to the default__ compiler error. Wire this loop into CI so a new engine or a renamed function is caught before merge.

5. Add a capability guard for engines that lack the operation

Some operations do not exist on some engines. Rather than emit SQL that errors at run time, read a capability map and stop at compile time with a clear message. Store the map in one macro so every dispatched function shares one source of truth.

-- macros/spatial/require_capability.sql
{% macro require_capability(feature) %}
  {%- set caps = {
    'postgres':  {'spheroid_area': true},
    'duckdb':    {'spheroid_area': false},
    'bigquery':  {'spheroid_area': true},
    'snowflake': {'spheroid_area': true}
  } -%}
  {%- if not caps.get(target.type, {}).get(feature, false) -%}
    {{ exceptions.raise_compiler_error(
        "Target '" ~ target.type ~ "' does not support '" ~ feature ~ "'.") }}
  {%- endif -%}
{% endmacro %}

Verify the guard fires on an unsupported target and stays silent on a supported one:

dbt compile --select model_using_spheroid_area --target duckdb
# Expect a compiler error naming duckdb and spheroid_area.
dbt compile --select model_using_spheroid_area --target postgres
# Expect a clean compile.

Configuration reference

Gotchas & edge cases

• Namespace mismatch silently falls to default__. If the string in adapter.dispatch('fn', 'spatial_utils') does not match the macro_namespace in dbt_project.yml, dbt cannot find your variants and uses the default — which then raises. Keep the namespace string identical everywhere.
• target.type is the adapter name, not a warehouse brand. Redshift reports redshift, not postgres, even though it shares much of the PostGIS-adjacent dialect. Add an explicit redshift__ variant or alias it; do not assume it inherits postgres__.
• Search order decides package overrides. With a package’s namespace listed before your project, the package’s variant wins. Put your project first when you intend to override, as covered in choosing the right spatial adapter for engine-specific defaults.
• TO_GEOGRAPHY on already-projected columns. Wrapping a projected GEOMETRY in TO_GEOGRAPHY on Snowflake reinterprets its coordinates as lon/lat and silently corrupts results. Only wrap true lon/lat columns; reproject others in staging first, per the geometry transformation pipeline contract.
• Introspective macros need a live connection to compile. A pure string-rendering variant compiles offline, but any variant that calls run_query or adapter.get_columns_in_relation will try to connect. Keep dispatched spatial shims free of introspection so the compile-test loop stays warehouse-free.

FAQ

Related

• Cross-Engine UDF Portability — the capability matrix and parity-testing strategy this dispatch pattern serves.
• Building Custom Spatial Macros — the macro framework and parameterization vocabulary behind these shims.
• Writing Reusable ST_DWithin Macros in dbt — a dispatched distance predicate with per-engine geography handling.

Up: Part of Cross-Engine UDF Portability.