Avoiding Cartesian blowups in spatial joins

This page shows how to detect a spatial join that has started producing more rows than it should, trace which of the four usual causes is responsible, and add the dbt tests that make the same mistake fail the build next time instead of quietly inflating a metric.

When to use this approach

  • A model’s row count grew without a corresponding change in its inputs. That is the signature of fanout, and it is worth ten minutes of investigation before anyone trusts the mart again.
  • A build that used to finish started running for hours. Row multiplication and slow builds are usually the same incident seen from two angles; the tuning in tuning ST_Intersects joins with bounding-box prefilters treats the symptom, the grain policy here treats the cause.
  • You are adding a spatial join to a project that has none of these tests yet. Add them with the first join rather than after the first incident.

Prerequisites

  • dbt-utils for equal_rowcount and expression_is_true.
  • A declared grain for every model that contains a spatial join — written down in the model’s description, not merely assumed.
  • Row counts from a known-good build to compare against; without a baseline, “too many rows” is an opinion.
  • Access to run singular tests in CI, so a failing grain assertion blocks a merge.

Step-by-step instructions

1. Measure the fanout factor, not the row count

An absolute count tells you nothing on its own. What you want is rows out divided by rows in, per input key.

sql
-- analyses/fanout_probe.sql
select
    count(*)                                         as output_rows,
    count(distinct ping_id)                          as input_keys,
    round(count(*)::numeric / nullif(count(distinct ping_id), 0), 3) as fanout_factor,
    max(matches)                                     as worst_key
from (
    select ping_id, count(*) as matches
    from {{ ref('int_pings_zoned') }}
    group by ping_id
) per_key

A fanout_factor of 1.000 means the join preserved the grain. Anything above it is the number you have to explain — and worst_key tells you whether the excess is spread thinly across everything (overlapping zones) or concentrated in a few keys (one pathological geometry).

Reading the shape of fanout: spread thinly versus concentrated in a few keys Two distributions of matches per input key. On the left, a broad low bar chart where almost every key has two or three matches, which indicates systematically overlapping polygons. On the right, a chart where nearly all keys have one match but a handful spike to hundreds, which indicates one pathological geometry such as an unclipped world extent. spread thinly · overlapping zones every key has 2–3 matches fix: a grain policy with a tie-break concentrated · one bad geometry one key matches 400 rows fix: find and clip that geometry

2. Rule out the four usual causes in order

sql
-- 1. Overlapping polygons: does the polygon set self-intersect?
select count(*) as overlapping_pairs
from {{ ref('stg_zones') }} a
join {{ ref('stg_zones') }} b
  on a.zone_id < b.zone_id
 and st_intersects(a.geom, b.geom)
 and st_area(st_intersection(a.geom, b.geom)) > 0;

-- 2. Subdivided pieces: is one logical zone stored as many rows?
select count(*) as pieces, count(distinct zone_id) as zones
from {{ ref('stg_zones') }};

-- 3. A geometry covering the whole extent (an unclipped world polygon, a bad import)
select zone_id, st_area(geom) as area
from {{ ref('stg_zones') }}
order by area desc
limit 5;

-- 4. A proximity radius that is wider than intended, often a units mistake
select {{ var('proximity_metres') }} as configured_radius;

Cause 4 deserves a note, because it is the one people misdiagnose. ST_DWithin on a geography column takes metres; on a geometry column in EPSG:4326 it takes degrees. A radius of 500 meant as metres becomes 500 degrees — the entire planet — and every row matches every row. The type distinction behind this is covered in geometry vs geography type trade-offs.

A 500-unit radius in degrees against the same radius in metres A single query point with three concentric radii drawn to scale on a schematic map. The intended 500 metre radius is a small circle covering a block. The 500 degree radius that results from applying the same literal to a geometry column in EPSG 4326 covers the entire frame and beyond, so every candidate row matches. A label records the resulting match counts of four and eleven million. 500 m — intended 500 degrees — what the literal meant matches per query point geography, 500 m 4 geometry 4326, 500 11,204,880 the whole table, for every row Name the variable with its unit and the mismatch becomes visible at the call site.

Verify which cause is live by re-running the fanout probe from step 1 after neutralizing each candidate — for instance, restricting the join to a single zone, or dropping the largest geometry.

3. Bound the join in the model itself

Detection is not prevention. Two guards, both cheap, stop a blowup from reaching the warehouse at all.

sql
-- models/intermediate/int_pings_zoned.sql
{{ config(materialized = 'table') }}

with zones as (
    select zone_id, zone_name, zone_priority, geom
    from {{ ref('stg_zones') }}
    -- Guard 1: refuse absurd geometry rather than joining against it
    where st_area(geom) < {{ var('max_zone_area_sqm') }}
),

joined as (
    select
        p.ping_id,
        z.zone_id,
        row_number() over (
            partition by p.ping_id
            order by z.zone_priority asc, z.zone_id asc
        ) as match_rank
    from {{ ref('stg_trip_pings') }} as p
    join zones as z
      on st_intersects(p.geom, z.geom)
)

-- Guard 2: keep the declared grain, and keep the rank so fanout stays observable
select ping_id, zone_id
from joined
where match_rank = 1

Ranking rather than distinct on has a diagnostic advantage: during development you can select max(match_rank) to see how much fanout the join would have produced, without ever materializing it.

Verify the guard is doing its job and not silently discarding real matches:

sql
select count(*) as zones_rejected_by_area_guard
from {{ ref('stg_zones') }}
where st_area(geom) >= {{ var('max_zone_area_sqm') }};
-- Expect 0 in steady state; a non-zero result is an import problem to investigate, not a threshold to raise

4. Turn the checks into tests that block a merge

yaml
# models/intermediate/schema.yml
models:
  - name: int_pings_zoned
    description: One row per ping, highest-priority matching zone. Grain asserted below.
    tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('stg_trip_pings')
      - dbt_utils.expression_is_true:
          expression: "count(*) = count(distinct ping_id)"
    columns:
      - name: ping_id
        tests: [unique, not_null]

Add a singular test for the pathological-geometry case, since it is the one that reappears with every new data delivery:

sql
-- tests/assert_no_oversized_zone_geometry.sql
select zone_id, st_area(geom) as area_sqm
from {{ ref('stg_zones') }}
where st_area(geom) >= {{ var('max_zone_area_sqm') }}

Verify in CI by seeding a deliberately oversized fixture and confirming the build goes red — the fixture pattern is in seeding geometry fixtures for dbt tests, and the workflow that runs it on every pull request is in running spatial tests in GitHub Actions.

Where each guard stops a fanout incident along the path from source to dashboard A left-to-right path from a source delivery through staging, the join model, the mart and finally a dashboard. Three guards are attached: an area guard and a source test in staging, a grain rank filter in the join model, and a row-count test between the join model and the mart. A note underneath states that without these, the first signal is a wrong number on the dashboard. source delivery new boundary file staging area guard · source test join model match_rank = 1 mart equal_rowcount dashboard too late Each guard costs seconds to run; the incident it prevents costs a day of reconciliation. Without them, the first signal is a number nobody can explain.

Configuration reference

Setting Where Suggested value Notes
max_zone_area_sqm dbt_project.yml vars 10× the largest legitimate zone Catches unclipped or world-extent imports before the join
proximity_metres vars task-specific Only meaningful with a geography column or a projected CRS
equal_rowcount severity schema YAML error Warn-level grain tests are ignored in practice
match_rank cutoff join model 1 Keep the column during development to observe would-be fanout
store_failures test config true Persists the offending keys so an incident can be traced after the fact

Gotchas & edge cases

  • equal_rowcount is wrong for joins that legitimately fan out. Use a bounded-fanout singular test instead, with a ceiling drawn from the data rather than from optimism.
  • A blowup can hide behind a group by. If the mart aggregates, the row count looks stable while every sum() doubles. Test the intermediate model, not only the mart.
  • Degrees-versus-metres mistakes survive code review. They look like ordinary numbers. Name the variable with its unit (proximity_metres, never radius) so the mismatch is visible at the call site.
  • Subdivision is a deliberate, benign fanout. Do not fix it by removing subdivision; fix it by deduplicating downstream, or the join gets slow again.
  • Self-intersecting input polygons multiply matches invisibly. Validate in staging first, following testing geometry validity with dbt generic tests.

FAQ

What fanout factor should I treat as acceptable?

Whatever the model’s declared grain says, and no more. For a one-row-per-input model the only acceptable value is exactly 1.0; for an intentionally fanning join, set the ceiling from observed data with headroom — if the busiest key legitimately matches six zones, a ceiling of eight catches a runaway without firing on normal variation.

Why not just add distinct everywhere and move on?

Because distinct hides the cause while paying its cost. The join still forms every duplicate pair — the expensive part — and then discards them, so the build stays slow. It also masks a genuine data problem, such as overlapping boundaries that someone should be told about. Deduplicate deliberately, with a tie-break you can explain.

How do I catch this in CI rather than in production?

Seed a fixture that reproduces each cause — two overlapping zones, one oversized polygon — tag them so they never build in production, and assert that the grain tests fail on them. A gate that has never gone red is not a gate; the CI setup is described in DuckDB as a lightweight CI validator.

Our blowup only happens in production, never locally. Why?

Local samples usually contain a small extent and a clean subset of boundaries, so overlaps and pathological geometries are absent. Sample by key rather than by extent — take every zone and a random slice of points — and the local dataset starts reproducing production’s join behaviour.

Up: Part of Spatial Joins & Predicate Tuning.