Partitioning Geospatial Tables with H3 and dbt

This page shows you how to turn an Uber H3 hexagonal cell index into the physical partition and cluster key for a large geometry table in dbt, so the warehouse prunes whole regions of data before any ST_* predicate runs.

When to use this approach

A grid partition key earns its keep once a table is large enough that unqualified geometry scans dominate query cost. Reach for H3 cell partitioning when:

• Your table has passed tens of millions of rows and queries are spatially local. Dashboards and joins that touch one metro area should never scan a continent. H3 partitioning is the physical-layout half of the broader scaling discipline in handling large geospatial datasets; pair it with incremental materialization for large geometry tables to bound rebuild cost too.
• You want an equal-area, uniform partition key. Partitioning by country or admin region produces wildly skewed partitions — one dense city cell can dwarf a rural nation. H3 hexagons are near-uniform in area at a fixed resolution, so partitions stay balanced.
• Your join is a proximity or containment test. A shared cell key lets you replace part of an exact predicate with a cheap equality pre-filter, the two-phase pattern detailed in optimizing proximity joins. If you only need bounding-box incremental filters, incremental spatial models with bounding-box filters may be enough on its own.

If your data is globally uniform point density and your engine already clusters natively (BigQuery, Snowflake), a Z-order or QuadKey clustering key can be simpler; H3 wins when you also need the cell as a join and aggregation key, not just storage locality.

Prerequisites

• dbt Core >= 1.7 for current partition_by / cluster_by config semantics and on_schema_change handling.
• An H3 binding for your engine: the h3 PostgreSQL extension (h3-pg) alongside PostGIS >= 3.3, or the DuckDB h3 community extension alongside the DuckDB spatial extension for CI. BigQuery exposes jslibs.h3.* UDFs; Snowflake has H3 built in (H3_LATLNG_TO_CELL).
• A canonical SRID already enforced in staging. H3 assignment reads lon/lat, so geometries must be normalized to EPSG:4326 before the cell is computed — see spatial reference system management.
• Warehouse grants to CREATE tables and indexes on the target schema.
• A chosen resolution wired through a project var so dense and sparse marts can share one transformation:

# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_SPATIAL_CANONICAL_SRID', '4326') }}"
  h3_resolution: 8   # ~0.46 km^2 hexagons; raise for denser data, lower for sparser

Step-by-step instructions

The flow below is what you are building: the cell index is computed once in staging, becomes the physical partition/cluster key in the intermediate layer, and is supplied as a literal set at query time so the planner discards non-matching partitions before touching geometry.

1. Assign the H3 cell in staging

Compute the cell exactly once, immediately after CRS normalization, and stamp it as a plain string column. Deriving it downstream re-runs the lon/lat math on every model and invites drift in resolution.

-- models/staging/stg_events_h3.sql
{{ config(materialized='view') }}

WITH normalized AS (
    SELECT
        event_id,
        event_timestamp,
        ST_Transform(geom, {{ var('canonical_srid') }}) AS geom
    FROM {{ source('gis', 'events_raw') }}
    WHERE geom IS NOT NULL AND ST_IsValid(geom)
)

SELECT
    event_id,
    event_timestamp,
    geom,
    -- lon/lat order: H3 takes (lat, lng)
    h3_latlng_to_cell(ST_Y(geom), ST_X(geom), {{ var('h3_resolution') }}) AS h3_index
FROM normalized

Verify every row got a cell and the resolution is uniform:

SELECT
    count(*) FILTER (WHERE h3_index IS NULL) AS missing_cell,
    count(DISTINCT h3_get_resolution(h3_index)) AS distinct_resolutions
FROM {{ ref('stg_events_h3') }};
-- expect missing_cell = 0 and distinct_resolutions = 1

2. Partition and cluster the incremental model by the cell

Make h3_index the partition key and lead the cluster_by columns with it so co-located rows share a physical block. On PostGIS, where there is no native partition_by, use the cell to build a GiST index and (optionally) declarative table partitions in a post_hook; on BigQuery and Snowflake the config maps to native clustering.

-- models/intermediate/int_events_partitioned.sql
{{ config(
    materialized='incremental',
    unique_key='event_id',
    partition_by={"field": "h3_index", "data_type": "string"},
    cluster_by=["h3_index", "event_timestamp"],
    post_hook="CREATE INDEX IF NOT EXISTS {{ this.name }}_geom_gist ON {{ this }} USING GIST (geom)"
) }}

SELECT
    event_id,
    event_timestamp,
    h3_index,
    geom
FROM {{ ref('stg_events_h3') }}
{% if is_incremental() %}
  WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM {{ this }})
{% endif %}

Verify rows sharing a cell are physically close (small block range per cell means good clustering):

-- PostGIS: ctid page spread per cell should be tight after CLUSTER/ANALYZE
SELECT h3_index, count(*) AS rows, count(DISTINCT (ctid::text::point)[0]) AS pages
FROM {{ ref('int_events_partitioned') }}
GROUP BY h3_index ORDER BY rows DESC LIMIT 5;

3. Derive the candidate cell set for a query

Never scan the whole table and filter geometry. Instead, resolve the area of interest to a small set of cells first — the exact cell of a point plus its ring of neighbours — and pass that set as the partition filter.

-- resolve an area of interest (a point + 1-ring) to a cell set
WITH aoi AS (
    SELECT h3_grid_disk(
        h3_latlng_to_cell(:lat, :lng, {{ var('h3_resolution') }}),
        1
    ) AS cells
)
SELECT e.event_id, e.geom
FROM {{ ref('int_events_partitioned') }} e, aoi
WHERE e.h3_index = ANY(aoi.cells)      -- partition prune happens here
  AND ST_DWithin(e.geom::geography, ST_MakePoint(:lng, :lat)::geography, 2000);

Verify the planner prunes partitions instead of scanning them all:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM int_events_partitioned WHERE h3_index = ANY(ARRAY['8828308281fffff']);
-- expect the scan to touch only the matching partition/blocks, not the full relation

4. Aggregate to the cell for coarse marts

For dashboards that do not need individual geometries, roll rows up to their cell. The aggregate is dramatically smaller, needs no geometry at read time, and doubles as a privacy control — the same generalization used in masking precise coordinates for privacy.

-- models/marts/mart_events_by_cell.sql
{{ config(materialized='table', cluster_by=['h3_index']) }}

SELECT
    h3_index,
    h3_cell_to_boundary_wkt(h3_index) AS cell_boundary,
    count(*)                          AS event_count
FROM {{ ref('int_events_partitioned') }}
GROUP BY h3_index

Verify the aggregate is complete and no cell was lost:

SELECT
    (SELECT count(DISTINCT h3_index) FROM {{ ref('int_events_partitioned') }}) AS source_cells,
    (SELECT count(*) FROM {{ ref('mart_events_by_cell') }})                    AS mart_cells;
-- the two counts must match

Configuration reference

Gotchas & edge cases

• Resolution is a permanent physical decision. Changing h3_resolution re-keys every row and forces a full rebuild — the cell strings differ entirely between resolutions. Treat a resolution change as a schema event and record it under versioning spatial schemas in dbt.
• Lat/lng argument order. h3_latlng_to_cell takes latitude first. Passing ST_X (longitude) before ST_Y silently places every point in the wrong hemisphere and destroys locality without erroring.
• Polygons need a covering, not a single cell. A point maps to one cell, but a polygon spans many. Assign polygons with h3_polygon_to_cells (a covering set) and either explode rows per cell or key on the centroid cell — pick deliberately, because the two answer different questions.
• Boundary features fall in a neighbour cell. A point near a cell edge whose true match sits across the border is missed if you query only the exact cell. Always expand the area of interest with h3_grid_disk(cell, 1) before filtering, then let the exact predicate decide.
• S2 and QuadKey are not interchangeable keys. If a downstream system already keys on S2 tokens or QuadKeys, do not re-key to H3 for its own sake; a mismatched grid forces a re-cover on every join. Standardize one grid across the platform.
• Too-fine a resolution explodes partition count. Thousands of tiny partitions add metadata and planning overhead that can outweigh the prune. Tune resolution to roughly one partition per query working set, not per row.

FAQ

Related

• Handling Large Geospatial Datasets — the full scaling discipline this partition strategy is one control within.
• Incremental Materialization for Large Geometry Tables — bound rebuild cost once the table is partitioned.
• Optimizing Proximity Joins — use the shared cell key as the cheap first phase of a two-phase join.

Up: Part of Handling Large Geospatial Datasets.