Choosing between H3, S2 and geohash in dbt

This page decides which grid a dbt project should adopt: what each system is actually good at, which differences change results rather than aesthetics, and a short benchmark that answers the question on your own data instead of in the abstract.

When to use this approach

  • You are adding grid keys for the first time and the choice will be hard to reverse — every key, partition and incremental model inherits it.
  • A second team wants a different grid. Two grids in one warehouse means every cross-team join goes back through geometry, which defeats the purpose.
  • Your engine supports more than one. Where only one is available the decision is made; the trade-offs still matter for knowing what you have given up. Engine coverage is summarized in discrete global grid macros.

Prerequisites

  • A representative sample of your own data — the decision depends on the latitude band and the feature mix.
  • At least one grid implementation installed, for the benchmark.
  • Agreement on the primary workload: aggregation, proximity, partitioning, or interchange. They favour different grids.

Step-by-step instructions

1. Rule out the systems your workload cannot use

Three properties eliminate candidates faster than any benchmark.

text
Need prefix truncation (a coarse key derived from a fine one by string/bit ops)?
    → S2 or geohash. H3 resolutions do not nest, so a parent must be computed, not truncated.

Need uniform neighbour distance (proximity, k-rings, diffusion, routing heuristics)?
    → H3. Square and rectangular cells have edge neighbours and corner neighbours at
      different distances, which biases every "nearby" calculation.

Need zero dependencies (an interchange key any tool can parse)?
    → geohash. It is a base-32 string computable in ten lines anywhere.
Neighbour distance in a hexagonal grid compared with a square grid On the left, a hexagon with its six neighbours, each centre the same distance away, so a one-ring neighbourhood is close to a circle. On the right, a square with its eight neighbours: the four edge neighbours are one unit away while the four corner neighbours are about 1.41 units away, so a one-ring neighbourhood is a square that reaches further diagonally. Labels give the distances and note which shape biases proximity work. H3 · six neighbours, one distance all six centres at distance 1.00 a ring is nearly a circle S2 or geohash · eight neighbours, two distances edges at 1.00, corners at 1.41 a ring reaches further diagonally

2. Measure area distortion at your latitudes

Geohash cells are defined in degrees, so a cell’s ground area shrinks as latitude rises — dramatically. If your data spans a wide latitude range and you intend to aggregate by cell, that distortion is a bias in every count per unit area.

sql
-- analyses/cell_area_by_latitude.sql
with lats as (select generate_series(0, 70, 10) as lat)
select
    lat,
    round(st_area(st_transform(
        st_setsrid(st_makeenvelope(0, lat, 0.0439, lat + 0.0219), 4326), 3035)
    )::numeric / 1000000, 3) as geohash6_area_km2
from lats
text
 lat | geohash6_area_km2
-----+-------------------
   0 |             1.190
  30 |             1.031
  50 |             0.766
  70 |             0.408

Verify against the equivalent H3 or S2 cell at a comparable resolution, whose area varies by a factor well under two across the same range rather than by a factor of three.

Cell ground area against latitude for a degree-defined grid and a geodesic one Two curves plotted from the equator to seventy degrees latitude. The geohash curve falls steeply, losing about two thirds of its ground area by seventy degrees, because its cells are defined in degrees. The H3 curve stays nearly flat across the same range. An annotation notes that a count per cell is only a density if the cells have comparable areas. geohash 6 H3 r8 0° 35° 70° cell area (km²) A count per cell is a density only when the cells are comparable in size.

3. Benchmark the join you actually run

Abstract properties are settled; the remaining question is throughput on your data and engine.

sql
-- analyses/grid_join_benchmark.sql
{% set variants = [
    {'name': 'h3_r9',     'expr': "h3_cell"},
    {'name': 'geohash_7', 'expr': "st_geohash(geom, 7)"}
] %}

{% for v in variants %}
select
    '{{ v.name }}' as variant,
    count(*) as matched_rows,
    count(distinct {{ v.expr }}) as distinct_cells
from {{ ref('stg_trip_pings') }} p
join {{ ref('stg_zone_cells_' ~ v.name) }} z on {{ v.expr }} = z.cell
{% if not loop.last %}union all{% endif %}
{% endfor %}

Run each variant three times, take the median, and record the covering table’s row count alongside — a grid that joins slightly faster but needs twice the covering rows is usually the worse choice overall.

Verify that both variants return the same matched rows against a geometry-only baseline. A faster grid that loses boundary features is not faster, it is wrong.

4. Commit, and make the choice visible

yaml
# dbt_project.yml
vars:
  grid_system: h3
  grid_resolution: 9
  grid_resolution_coarse: 6

Record the reasoning where the next person will look — in the topic’s model description rather than in a wiki that will drift:

yaml
models:
  - name: stg_trip_pings
    description: >
      Ping-level staging. Grid: H3 r9 (var grid_system). Chosen for uniform
      neighbour distance, since proximity aggregation is the primary workload
      and hexagon rings approximate a radius without diagonal bias. S2 was the
      runner-up; rejected because prefix truncation is not needed here and
      BigQuery is not a target. Changing the grid or resolution is a breaking
      change: every key, partition and incremental unique key derives from it.
Which grid each primary workload points to Four workloads mapped to a recommended grid. Proximity and neighbourhood aggregation point to H3 for uniform neighbour distance. Hierarchical rollups needing prefix truncation point to S2. Interchange with external tools points to geohash. Running natively on BigQuery points to S2 because it is the built-in system. A note records that mixing two grids in one warehouse forces cross-team joins back through geometry. primary workload grid it points to proximity, k-rings, neighbourhood aggregation H3 — uniform neighbour distance hierarchical rollups by prefix truncation S2 — strict nesting interchange with tools you do not control geohash — no dependency at all running natively on BigQuery S2 — the built-in option

Configuration reference

Consideration H3 S2 Geohash
Typical id form 15-character hex string or int64 int64 base-32 string
Resolution scale 0–15, ~7× area per step 0–30, 4× area per step length 1–12, alternating 8×/4×
Parent from child function call bit shift string truncation
Ring neighbours 6, equidistant 8, two distances 8, two distances
Area stability by latitude good good poor
Library maturity in SQL engines high and growing high on BigQuery universal, trivial

Gotchas & edge cases

  • H3 resolutions do not nest exactly. A parent cell does not contain its children’s full area, so “roll up r9 to r6 by taking the parent” is approximate. Where exact hierarchical containment is required, S2 is the honest choice.
  • Geohash cells are not square. They alternate between 8:1 and 4:1 subdivisions, so a length-6 cell and a length-7 cell have very different aspect ratios — a surprise when rendering bins.
  • S2 cell ids are signed 64-bit and can be negative in some encodings; string-typed columns avoid a class of client bugs.
  • Two grids means no shared keys. If another team standardized already, matching them usually beats being right in isolation.
  • Benchmarks on a city extent mislead for continent-scale data, because area distortion and covering size both scale with the latitude range.

FAQ

Can I store more than one grid key?

Technically yes, and occasionally it is right — a geohash for interchange alongside H3 for computation, for instance. But each key is a column that every transformation must keep correct, and two keys mean two chances for a join to use the wrong one. Add the second only when an external consumer requires it.

Does the grid choice affect correctness or only performance?

Both, in different ways. Performance is the obvious axis. Correctness enters through aggregation: counts per cell are counts per unit area only if cells have comparable areas, so a geohash heatmap spanning many latitudes is systematically biased toward the poles in a way an H3 one is not.

What resolution corresponds between the systems?

Roughly: H3 r8 ≈ S2 level 12 ≈ geohash 6 at mid-latitudes, and H3 r9 ≈ S2 level 13 ≈ geohash 7. These are approximations that drift with latitude, so calibrate on your own extent rather than treating them as conversions.

Is it worth switching an existing project?

Only for a specific, measured problem — proximity results biased by square cells, or a rollup that needs true nesting. The migration touches every key, partition and incremental model, and the tests in writing H3 index macros for dbt models exist partly to make the scale of that change visible before it starts.

Up: Part of Discrete Global Grid Macros.