Snowflake GEOGRAPHY vs PostGIS geometry in dbt

This page compares the two spatial type systems a dbt project meets when it moves between Snowflake and PostGIS, shows which column type to declare for which workload, and gives the parity tests that prove the two engines agree before you rely on either.

When to use this approach

• You are porting a PostGIS dbt project to Snowflake and need to know which models will return different numbers rather than merely different SQL.
• You are starting on Snowflake and must pick a column type. Snowflake offers both GEOGRAPHY and GEOMETRY, and the choice determines your units, your edge semantics, and whether reprojection is available at all.
• You run both engines — PostGIS for serving, Snowflake for analytics — and need the two to agree within a stated tolerance. The wider engine comparison is in warehouse-native GIS adapters.

Prerequisites

• dbt-snowflake ≥ 1.7 and a role with CREATE TABLE on the target schema.
• A warehouse sized for the comparison queries; parity tests scan both sides fully.
• PostGIS 3.1+ on the other side, with the same source data loaded from the same file.
• dbt-utils for the comparison tests.

Step-by-step instructions

1. Decide the column type before writing a model

The decision is not stylistic. It fixes the unit system and the meaning of every edge in your data.

The row that decides most projects is reprojection. GEOGRAPHY cannot reproject, so if a downstream consumer needs a national grid projection, either store GEOMETRY with that SRID or compute the projected values upstream. The row that causes most bugs is the distance unit: a PostGIS model written against geometry in EPSG:4326 measures in degrees, and the same numeric literal on Snowflake GEOGRAPHY means metres.

2. Write the staging model against the chosen type

-- models/staging/stg_zones.sql   (Snowflake, GEOGRAPHY variant)
{{ config(materialized = 'table', cluster_by = ['zone_id']) }}

select
    zone_id,
    zone_name,
    try_to_geography(geom_wkt) as zone_geog
from {{ source('ops', 'zones_raw') }}
where try_to_geography(geom_wkt) is not null

TRY_TO_GEOGRAPHY is the counterpart of PostGIS’s validate-then-quarantine step: it returns null instead of raising, so one malformed row cannot abort the build. Count the nulls and report them rather than dropping them silently — the pattern is the same one used in quarantining invalid geometries in staging.

Verify that parsing did not lose rows:

select
    count(*) as raw_rows,
    count(try_to_geography(geom_wkt)) as parsed_rows,
    count(*) - count(try_to_geography(geom_wkt)) as rejected
from {{ source('ops', 'zones_raw') }};

3. Express distance predicates through a macro

The same model must not mean two different radii on two engines. Wrap it once.

-- macros/spatial/within_metres.sql
{% macro within_metres(a, b, metres) %}
  {{ return(adapter.dispatch('within_metres', 'spatial')(a, b, metres)) }}
{% endmacro %}

{% macro snowflake__within_metres(a, b, metres) %}
  st_dwithin({{ a }}, {{ b }}, {{ metres }})
{% endmacro %}

{% macro postgres__within_metres(a, b, metres) %}
  st_dwithin({{ a }}::geography, {{ b }}::geography, {{ metres }})
{% endmacro %}

Verify the macro compiles to the intended SQL on each target:

dbt compile --select int_nearby_zones --target snowflake_dev
dbt compile --select int_nearby_zones --target postgres_dev
grep -n "st_dwithin" target/compiled/dbt_geospatial/models/intermediate/int_nearby_zones.sql

4. Prove the two engines agree

Parity is a measurement, not an assumption. Export a fixed sample from both engines and compare with a tolerance that reflects the geodesic-versus-planar difference.

-- models/intermediate/int_engine_area_parity.sql
select
    s.zone_id,
    s.area_sqm       as snowflake_area,
    p.area_sqm       as postgis_area,
    abs(s.area_sqm - p.area_sqm) / nullif(s.area_sqm, 0) as relative_diff
from {{ ref('stg_zone_areas_snowflake') }} as s
join {{ source('parity', 'zone_areas_postgis') }} as p using (zone_id)

models:
  - name: int_engine_area_parity
    columns:
      - name: relative_diff
        tests:
          - dbt_utils.accepted_range:
              max_value: 0.01

A one per cent tolerance is generous for city-scale polygons and too tight for country-scale ones with long east–west edges. Set it from the data: measure the observed spread first, then set the threshold just above it, so the test catches a regression rather than the known difference.

5. Cluster, and confirm the clustering is real

{{ config(
    materialized = 'table',
    cluster_by = ['zone_id', 'activity_date']
) }}

Verify with Snowflake’s own clustering metrics rather than trusting the config:

select system$clustering_information('analytics.mart_zone_daily_activity', '(zone_id, activity_date)');
-- Expect a low average_depth; a high value means the table needs reclustering

The clustering check closes the loop on the port: everything else in this sequence is about numbers agreeing, and this one is about the physical layout actually existing. A declared clustering key that Snowflake has not yet applied looks identical in the model file and behaves like an unclustered table in every query.

Configuration reference

Gotchas & edge cases

• GEOGRAPHY silently accepts projected coordinates the same way BigQuery does, and produces areas that are wrong by orders of magnitude. Add a range test.
• PostGIS geography and Snowflake GEOGRAPHY are close but not identical on edge cases such as antimeridian crossing; test rather than assume.
• ST_AREA on a GEOMETRY in SRID 4326 returns square degrees on both engines — a number with no physical meaning that nonetheless looks plausible in a dashboard.
• Clustering keys with high cardinality cost more than they save. A geography column clusters usefully; a raw point identifier does not.
• Warehouse size changes plan shape, not correctness. If a spatial join gets faster on a larger warehouse but returns different counts, the difference is in the SQL, not the compute.

FAQ

Related

• Warehouse-Native GIS Adapters — the engine class this page sits in.
• Geometry vs Geography Type Trade-offs — the same distinction on PostGIS.
• Running dbt Spatial Models on BigQuery GIS — the geography-only engine taken end to end.

Up: Part of Warehouse-Native GIS Adapters.