Parameterizing spatial macros with dbt vars

This page gives a spatial macro a parameter surface that is small, validated at compile time, and impossible to misread: units in the names, defaults in project vars, overrides per environment, and a compile-time guard that refuses a value the macro cannot honour.

When to use this approach

  • The same macro is called with different tolerances or radii. Hard-coded numbers scattered across models are the state this replaces.
  • CI and production need different values. A simplification tolerance suited to a sampled dataset is wrong for production, and the difference belongs in configuration rather than in an if.
  • A macro is growing arguments. Every added argument is a chance for a caller to pass the wrong one; the validation here catches that at compile time. The macro-authoring basics are in building custom spatial macros.

Prerequisites

  • dbt 1.5+ and a working macro namespace with adapter.dispatch.
  • Project vars already in use for the canonical SRID, since most spatial parameters relate to it.
  • A test project or CI target where a compile failure is cheap to observe.

Step-by-step instructions

1. Put the unit in the parameter name

The single highest-value convention in spatial macro design costs nothing: never name a parameter distance, tolerance or radius on its own.

sql
-- macros/spatial/buffer_metres.sql
{% macro buffer_metres(geom, radius_metres, srid_metric=none) %}
  {%- set metric_srid = srid_metric or var('metric_srid') -%}
  st_transform(
      st_buffer(st_transform({{ geom }}, {{ metric_srid }}), {{ radius_metres }}),
      {{ var('canonical_srid') }}
  )
{% endmacro %}
yaml
# dbt_project.yml
vars:
  canonical_srid: 4326
  metric_srid: 25832          # UTM 32N — the plane buffers are computed in
  proximity_radius_metres: 500
  simplify_tolerance_metres: 20

A caller writing {{ buffer_metres('geom', var('proximity_radius_metres')) }} cannot accidentally pass degrees, because both the parameter and the var say metres. The degrees-versus-metres failure this prevents is described in avoiding Cartesian blowups in spatial joins.

Verify the macro produces the SQL you expect on each target:

bash
dbt compile --select int_depot_catchments
grep -n "st_buffer" target/compiled/dbt_geospatial/models/intermediate/int_depot_catchments.sql
The same call site, with and without units in the names Two call sites shown as code. The first passes a bare number to a parameter called radius, so nothing at the call site reveals whether the value is degrees or metres and a review cannot catch a mistake. The second passes a var named proximity_radius_metres to a parameter named radius_metres, so a wrong unit is visible in the line itself. A note observes that the cost of the convention is zero. unreviewable {{ buffer(geom, 500) }} 500 what? the call site cannot say reviewable {{ buffer_metres(geom, var('proximity_radius_metres')) }} a wrong unit is visible here The convention costs nothing and removes an entire class of silent error.

2. Validate parameters at compile time

A macro that accepts nonsense produces SQL that fails at run time, or worse, succeeds. Raise a compiler error instead — it costs milliseconds and fires before anything touches the warehouse.

sql
-- macros/spatial/_validate.sql
{% macro require_positive_number(name, value) %}
  {%- if value is not number or value <= 0 -%}
    {{ exceptions.raise_compiler_error(
        name ~ " must be a positive number, got: " ~ (value | string)) }}
  {%- endif -%}
{% endmacro %}

{% macro require_known_srid(name, srid) %}
  {%- if srid is not number or srid < 1024 -%}
    {{ exceptions.raise_compiler_error(
        name ~ " must be a numeric EPSG code, got: " ~ (srid | string)) }}
  {%- endif -%}
{% endmacro %}
sql
{% macro buffer_metres(geom, radius_metres, srid_metric=none) %}
  {%- set metric_srid = srid_metric or var('metric_srid') -%}
  {{ require_positive_number('radius_metres', radius_metres) }}
  {{ require_known_srid('metric_srid', metric_srid) }}
  st_transform(st_buffer(st_transform({{ geom }}, {{ metric_srid }}), {{ radius_metres }}),
               {{ var('canonical_srid') }})
{% endmacro %}

Verify the guards fire:

bash
dbt compile --select int_depot_catchments --vars '{proximity_radius_metres: -5}'
# Expect: Compilation Error — radius_metres must be a positive number, got: -5

dbt compile --select int_depot_catchments --vars '{metric_srid: "utm32"}'
# Expect: Compilation Error — metric_srid must be a numeric EPSG code

3. Layer defaults so each environment overrides only what it must

yaml
# dbt_project.yml — the project default
vars:
  simplify_tolerance_metres: 20
yaml
# a CI-only override, in the CI invocation rather than the project file
# dbt build --target ci --vars '{simplify_tolerance_metres: 200}'
sql
-- The macro reads the var, so no model changes between environments
{{ simplify_metres('geom', var('simplify_tolerance_metres')) }}

Three levels are enough: a project default that is correct for production, an explicit override for CI, and an argument for the rare model that genuinely differs. A fourth level — per-model config that shadows the var — is where projects lose track of which value applied.

Verify which value a given run used, by logging it once:

sql
{% macro log_spatial_params() %}
  {{ log("spatial params: canonical_srid=" ~ var('canonical_srid')
       ~ " metric_srid=" ~ var('metric_srid')
       ~ " simplify_tolerance_metres=" ~ var('simplify_tolerance_metres'), info=True) }}
{% endmacro %}
yaml
on-run-start:
  - "{{ log_spatial_params() }}"
Three layers of parameter resolution, and the fourth that causes confusion A stack showing precedence. The project var is the base default, correct for production. A command-line override for CI sits above it. An explicit macro argument for one exceptional model sits above that. A fourth layer, per-model config shadowing the same name, is drawn crossed out with a note that it makes the effective value unknowable from the call site. 1 · project var — the production default vars: simplify_tolerance_metres: 20 2 · run override — CI and one-off runs --vars '{simplify_tolerance_metres: 200}' 3 · explicit argument — the exception simplify_metres('geom', 5) a fourth layer that shadows the same name makes the effective value unknowable

4. Keep the argument surface small

A macro with eight optional arguments is a dialect, and every caller becomes a place the dialect can be misused. Two rules keep it small.

sql
-- Prefer: one macro per intent
{% macro simplify_metres(geom, tolerance_metres) %}…{% endmacro %}
{% macro simplify_for_zoom(geom, zoom) %}…{% endmacro %}

-- Avoid: one macro with a mode switch
{% macro simplify(geom, tolerance=none, zoom=none, preserve_topology=true,
                  metric_srid=none, round_to=none, keep_collapsed=false) %}…{% endmacro %}

The first rule is that a boolean argument that changes the meaning of another argument should be two macros instead. The second is that anything derivable — the metric SRID from the project, the tolerance from the zoom — should be derived rather than passed.

Verify the surface stays small by counting arguments across the macro directory:

bash
grep -rhoE "\{%\s*macro\s+[a-z_]+\([^)]*\)" macros/ \
  | awk -F'[(),]' '{print NF-2, $0}' | sort -rn | head
# Anything above four arguments deserves a second look

5. Test the macro, not only the models that call it

sql
-- tests/assert_buffer_metres_radius.sql
with control as (
    select st_setsrid(st_makepoint(13.4050, 52.5200), 4326) as p
),
buffered as (
    select {{ buffer_metres('p', 500) }} as b from control
)
select
    round(sqrt(st_area(b::geography) / pi())::numeric, 0) as effective_radius_m
from buffered
where abs(sqrt(st_area(b::geography) / pi()) - 500) > 5

Measuring the buffer’s effective radius from its area is the honest test: it verifies that the macro’s transform-buffer-transform sandwich really produced 500 metres on the ground, which is precisely what a units mistake would break.

Verify the test fails when the metric SRID is wrong:

bash
dbt test --select assert_buffer_metres_radius --vars '{metric_srid: 4326}'
# Expect a failure — buffering in degrees does not produce a 500 m radius
Where a bad parameter value is caught, and what each point of failure costs A timeline from writing a value to seeing its consequence. A compile-time guard catches it in milliseconds at no cost. A database error catches it after the query starts, costing the run so far. A test catches it after the model builds, costing a build. Nothing catching it means the wrong number reaches a dashboard, costing a reconciliation. The four points are drawn with increasing cost. compile guard milliseconds costs nothing database error mid-run costs the run so far a failing test after the build costs a build cycle nobody weeks later costs a reconciliation Validation is worth writing because it moves the failure to the leftmost point on this line.

Configuration reference

Convention Example Why
unit suffix on names radius_metres, tolerance_metres Makes a unit mistake visible at the call site
var for every default var('metric_srid') One place to change, one place to audit
compile-time validation raise_compiler_error Fails in milliseconds, before touching the warehouse
none default plus var fallback srid_metric=none Lets a caller override without repeating the default
one macro per intent simplify_metres / simplify_for_zoom Avoids mode switches that change argument meaning
macro-level test effective radius from area Tests the macro rather than a model that happens to call it

Gotchas & edge cases

  • var() inside a macro reads the invoking project’s vars. In a package, that is the consumer’s project — which is usually what you want, and occasionally a surprise. Document which vars a shared macro expects.
  • Jinja numbers are not SQL numbers. A var supplied on the command line arrives as a string unless quoted correctly; the is not number check catches it.
  • Defaults that differ between environments hide differences. If CI silently uses a different tolerance, CI is not testing production’s behaviour. Log the effective values, as in step 3.
  • Validation macros must emit nothing. A validation macro that accidentally returns whitespace inserts it into the SQL; use {%- -%} trimming consistently.
  • A macro that reads vars is not pure. Two calls in one model can produce different SQL if a var changes mid-run, which is rare but confusing; treat vars as run-scoped constants.

FAQ

Should parameters be macro arguments or project vars?

Vars for anything with one project-wide correct value — the canonical SRID, the metric SRID, the standard tolerance. Arguments for anything a single call site legitimately varies. The test is whether two call sites in the same project should ever disagree: if not, it is a var.

How much validation is too much?

Validate types and ranges, not semantics. Checking that a radius is a positive number is cheap and catches real mistakes; checking that it is “reasonable for the dataset” encodes an assumption that will be wrong for someone. When in doubt, validate what makes the generated SQL invalid.

Can I default a var inside the macro instead of the project?

var('name', default) works and is tempting, but it scatters defaults across macros, so nobody can answer “what tolerance does this project use?” without grepping. Declare defaults in dbt_project.yml and let the macro read them without a fallback, so a missing var fails loudly.

How do I keep parameters consistent across a package boundary?

Export a single macro that returns the resolved parameter set, and have every other macro call it. Consumers then override one place, and the package has one documented contract rather than a var namespace they must reverse-engineer.

Up: Part of Building Custom Spatial Macros.