Writing H3 index macros for dbt models

This page builds the four H3 macros a spatial dbt project actually uses — cell for a point, cells covering a polygon, k-ring expansion, and cell boundary geometry — with adapter dispatch so the same models compile on PostGIS, DuckDB and Snowflake, and with the tests that stop a resolution change from silently rewriting every key.

When to use this approach

  • More than one model needs a cell id. The moment the second model calls the raw function, the spelling difference between engines becomes a maintenance problem.
  • You develop on DuckDB and run on something else. The function names differ; the macro is what keeps one model file valid for both.
  • You expect the resolution to change. It always does, and a project var plus a macro turns that into one edit instead of a search-and-replace across models.

The wider design context — why grid keys help and where they mislead — is in discrete global grid macros.

Prerequisites

  • An H3 implementation per target: the h3-pg extension on PostGIS, the h3 community extension on DuckDB, native H3_* functions on Snowflake.
  • dbt 1.5+ for adapter.dispatch with a macro namespace.
  • A project var for resolution, so no model contains a bare integer.
  • A handful of known points with known cell ids, for the pinning test in step 4.

Step-by-step instructions

1. Create the dispatch namespace

yaml
# dbt_project.yml
dispatch:
  - macro_namespace: spatial
    search_order: ['dbt_geospatial', 'spatial_utils']

vars:
  grid_resolution: 9
  grid_resolution_coarse: 6
sql
-- macros/grid/h3_cell.sql
{% macro h3_cell(geom, resolution=none) %}
  {%- set res = resolution if resolution is not none else var('grid_resolution') -%}
  {{ return(adapter.dispatch('h3_cell', 'spatial')(geom, res)) }}
{% endmacro %}

{% macro default__h3_cell(geom, res) %}
  {{ exceptions.raise_compiler_error("h3_cell has no implementation for " ~ target.type) }}
{% endmacro %}

{% macro postgres__h3_cell(geom, res) %}
  h3_lat_lng_to_cell({{ geom }}::point, {{ res }})::text
{% endmacro %}

{% macro duckdb__h3_cell(geom, res) %}
  h3_latlng_to_cell_string(st_y({{ geom }}), st_x({{ geom }}), {{ res }})
{% endmacro %}

{% macro snowflake__h3_cell(geom, res) %}
  h3_point_to_cell_string({{ geom }}, {{ res }})
{% endmacro %}

The default__ implementation raising a compiler error rather than guessing is deliberate: a project that silently produces no cell id on an unsupported adapter fails later, in data, where it is far more expensive to notice.

Verify it compiles on each target:

bash
for t in postgres_dev duckdb_dev snowflake_dev; do
  dbt compile --select stg_trip_pings --target "$t" >/dev/null && echo "$t ok"
done
One macro call resolving to three different engine functions A single model calls the h3_cell macro. Dispatch resolves it by adapter type to three implementations: on PostGIS a lat-lng-to-cell call taking a point, on DuckDB a call taking separate latitude and longitude arguments in the opposite order, and on Snowflake a point-to-cell call. A fourth branch, the default, raises a compiler error rather than returning nothing. The differing argument orders are highlighted as the reason the wrapper exists. the model writes {{ h3_cell('geom') }} h3_lat_lng_to_cell(geom::point, 9) h3_latlng_to_cell(ST_Y, ST_X, 9) h3_point_to_cell_string(geom, 9) raise_compiler_error(...) PostGIS DuckDB Snowflake anything else note the argument order difference

2. Add the polygon covering

sql
-- macros/grid/h3_polygon_cells.sql
{% macro h3_polygon_cells(geom, resolution=none) %}
  {%- set res = resolution if resolution is not none else var('grid_resolution') -%}
  {{ return(adapter.dispatch('h3_polygon_cells', 'spatial')(geom, res)) }}
{% endmacro %}

{% macro postgres__h3_polygon_cells(geom, res) %}
  h3_polygon_to_cells({{ geom }}, {{ res }})
{% endmacro %}

{% macro duckdb__h3_polygon_cells(geom, res) %}
  unnest(h3_polygon_wkt_to_cells_string(st_astext({{ geom }}), {{ res }}))
{% endmacro %}

The DuckDB implementation returns an array and needs unnest; the PostGIS one returns a set. Hiding that difference is most of the macro’s value, since it means the calling model can use one shape:

sql
-- models/staging/stg_zone_cells.sql
select
    z.zone_id,
    cells.h3_cell
from {{ ref('stg_zones') }} as z,
     lateral (select {{ h3_polygon_cells('z.geom') }} as h3_cell) as cells

Verify the covering is complete by checking that every zone produced at least one cell:

sql
select count(*) as zones_without_cells
from {{ ref('stg_zones') }} z
left join {{ ref('stg_zone_cells') }} c using (zone_id)
where c.zone_id is null;
-- Expect 0; a small polygon at a coarse resolution can legitimately produce none, which is a resolution problem

3. Add k-ring expansion and cell boundaries

sql
{% macro h3_k_ring(cell, k) %}
  {{ return(adapter.dispatch('h3_k_ring', 'spatial')(cell, k)) }}
{% endmacro %}

{% macro postgres__h3_k_ring(cell, k) %}
  h3_grid_disk({{ cell }}::h3index, {{ k }})
{% endmacro %}

{% macro h3_cell_boundary(cell) %}
  {{ return(adapter.dispatch('h3_cell_boundary', 'spatial')(cell)) }}
{% endmacro %}

{% macro postgres__h3_cell_boundary(cell) %}
  h3_cell_to_boundary_geometry({{ cell }}::h3index)
{% endmacro %}

k-ring turns a proximity question into a set membership test, which is the pattern that makes grid keys worth having on engines with no spatial index:

sql
-- "everything within roughly 1 km" at resolution 9 ≈ k = 3
select p.ping_id, d.depot_id
from {{ ref('stg_depots') }} d
join lateral (select {{ h3_k_ring('d.h3_cell', 3) }} as h3_cell) rings on true
join {{ ref('stg_trip_pings') }} p using (h3_cell)

Verify the ring radius matches the distance you meant, on your own latitude:

sql
select
    max(st_distance(d.geom::geography, p.geom::geography)) as max_metres
from ... -- the join above
-- Compare against the intended radius; k-ring distance is approximate by construction
A k-ring approximating a distance radius, and where the approximation shows A centre hexagon with two rings of neighbours drawn around it, overlaid with a true circle of the intended radius. The hexagon coverage extends beyond the circle at the ring corners and falls short of it between them, so a k-ring is a jagged approximation of a circle. A note records that this is acceptable for candidate selection and not for a contractual distance. k = 1 ring against the true radius what the mismatch means at the ring corners covers beyond the radius between the corners falls short of it Use a k-ring to select candidates, then filter with a real distance if the radius is contractual.

4. Pin the cell ids with a test

Grid keys are only stable if the resolution and the library agree between runs. Pin them.

sql
-- tests/assert_known_h3_cells.sql
with expected (label, lon, lat, resolution, cell) as (
    values
        ('brandenburg_gate', 13.377704, 52.516275, 9, '891f1d4894bffff'),
        ('trafalgar_square',  -0.128069, 51.508039, 9, '89195da4d3bffff')
),
actual as (
    select
        e.label,
        e.cell as expected_cell,
        {{ h3_cell("st_setsrid(st_makepoint(e.lon, e.lat), 4326)", "e.resolution") }} as actual_cell
    from expected e
)
select * from actual where expected_cell is distinct from actual_cell

Verify the test fails when the resolution var changes, which proves it is actually pinning something:

bash
dbt test --select assert_known_h3_cells --vars '{grid_resolution: 8}'
# Expect a failure — a different resolution must produce different ids
What a change to the resolution variable does to every downstream key A resolution change from nine to eight is shown propagating: every cell id in staging changes, so every join key changes, so every incremental model's unique key no longer matches its stored rows, and every partition boundary moves. The pinning test is drawn intercepting the change at the staging step, marked as the only place the change is cheap to notice. grid_resolution 9 → 8 every cell id changes in staging every join key matches nothing stored incremental keys and partitions silently diverge pinning test fails here, immediately A resolution change is a breaking change. The test is what makes it look like one.

Configuration reference

Macro Arguments Returns Note
h3_cell geometry, optional resolution one cell id Defaults to var('grid_resolution')
h3_polygon_cells polygon, optional resolution set of cell ids Set on PostGIS, array on DuckDB — the macro hides it
h3_k_ring cell, k set of cell ids Approximates a radius; k×cell edge length
h3_cell_boundary cell polygon geometry For rendering and for exact tests on edge cells
grid_resolution project var integer 0–15 One value for the whole project
dispatch search_order dbt_project.yml package list Lets a downstream project override an implementation

Gotchas & edge cases

  • Cell ids are 64-bit integers, often handled as strings. Mixing the two representations across models produces joins that silently match nothing; pick one, and say which in the macro name.
  • ::h3index casts are PostGIS-specific. Keep every cast inside its adapter implementation, never in a model.
  • A resolution change rewrites every key. Treat it as a breaking change: full refresh downstream, and a note in the model description.
  • The pinning test needs known-good ids. Generate them once from the library you trust and commit them as literals; deriving them from the same macro under test would prove nothing.
  • Coverings can be empty for small polygons at coarse resolutions, and a left join will then quietly drop those zones. Test for zones with no cells.

FAQ

Should the macros return integers or strings?

Strings, unless you are storing hundreds of millions of rows and the eight-byte integer form materially changes storage. Strings survive JSON round-trips, appear correctly in logs and dashboards, and avoid the signed-integer surprises that 64-bit ids produce in some clients. Whichever you choose, encode it in the macro name so a mixed-representation join is visible in review.

How do I keep DuckDB and PostGIS producing identical cell ids?

Both implement the same H3 specification, so identical input produces identical output — the risk is not the algorithm but the input. Ensure both receive coordinates in EPSG:4326 with longitude and latitude the right way round; the argument-order difference visible in the dispatch diagram is exactly where that goes wrong. The pinning test catches it on the first run.

Is it worth wrapping functions I only call once?

Not immediately, but the threshold is lower than it looks: the second model, or the first time someone runs the project on another adapter. Wrapping is cheap, and the alternative — finding every call site later — is what makes engine migrations expensive, as the port in migrating PostGIS SQL to BigQuery GIS functions shows.

Can I use these macros in tests as well as models?

Yes, and you should — a test that hard-codes the raw function call will break on the adapter the models still compile for. The pinning test above is written through the macro deliberately, so it exercises the same code path the models use.

Up: Part of Discrete Global Grid Macros.