Validating Spatial Data in Snowflake

Snowflake gives spatial validation two things the other warehouse engines do not combine: a genuine planar GEOMETRY type with an SRID, and a GEOGRAPHY type for spheroidal work — with an error rather than a silent conversion if you mix them. That makes it a comfortable home for rules originally written against PostGIS, provided you set the SRID explicitly, gate the ingest so unparseable rows are captured rather than lost, and size the compute to the shape of each rule. This guide covers all three, plus the search optimization that makes pairwise topology affordable, following the in-place pattern from Warehouse and Lakehouse Spatial Validation.

Prerequisites

• A Snowflake account with a warehouse you can resize, and privileges to enable search optimization if you intend to run topology rules.
• A decision on spatial type per dataset, and an SRID for every GEOMETRY column. Snowflake defaults the SRID to 0, which means “unknown planar” and quietly permits comparisons between datasets in different systems.
• A staging layer. Ingest gating needs somewhere to land raw text before it becomes a typed column.
• The shared findings contract used by the rest of the pipeline, so reporting stays uniform across engines.
• Access to SNOWFLAKE.ACCOUNT_USAGE or INFORMATION_SCHEMA.QUERY_HISTORY for credit attribution.

Step-by-Step Procedure

Step 1 — Set the type and the SRID deliberately

-- Planar cadastral work: GEOMETRY with an explicit SRID.
CREATE OR REPLACE TABLE core.parcels (
  parcel_id     STRING NOT NULL,
  zoning_code   STRING,
  assessed_value NUMBER(12, 2),
  load_date     DATE,
  geom          GEOMETRY            -- SRID applied on write, see below
);

-- Session defaults that make the semantics explicit rather than implicit.
ALTER SESSION SET GEOMETRY_OUTPUT_FORMAT = 'EWKT';   -- shows the SRID in output
ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT = 'EWKT';

-- The SRID must be attached when the value is constructed.
SELECT ST_SETSRID(TO_GEOMETRY('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'), 27700) AS geom;

Verification: query ST_SRID(geom) over a sample of the table. Any row returning 0 is a value constructed without an SRID, and a join between SRID 0 and SRID 27700 raises an error — which is helpful, but only once the correctly tagged side exists.

Step 2 — Gate the ingest so bad rows are captured

-- Parse into staging with TRY_ so a single bad row does not fail the load.
CREATE OR REPLACE TABLE staging.parcels_typed AS
SELECT
  parcel_id,
  zoning_code,
  assessed_value,
  raw_wkt,
  ST_SETSRID(TRY_TO_GEOMETRY(raw_wkt), 27700) AS geom
FROM staging.parcels_raw;

-- Everything that failed to parse becomes a finding, not a silent NULL.
CREATE OR REPLACE TABLE qa.parse_rejects AS
SELECT parcel_id, raw_wkt, CURRENT_DATE() AS run_date,
       'GEOM_PARSE_001' AS rule_id, 'blocker' AS severity,
       'TRY_TO_GEOMETRY returned NULL — unparseable WKT' AS message
FROM staging.parcels_typed
WHERE geom IS NULL AND raw_wkt IS NOT NULL;

-- Only parseable rows are promoted.
INSERT INTO core.parcels
SELECT parcel_id, zoning_code, assessed_value, CURRENT_DATE(), geom
FROM staging.parcels_typed
WHERE geom IS NOT NULL;

Verification: count qa.parse_rejects after a load and compare against the staging row count. The gap between raw rows and promoted rows must equal the reject count; if it does not, rows are being lost somewhere between the two statements.

Step 3 — Attribute and per-geometry rules

-- Column rules never touch the geometry column, and cost accordingly.
CREATE OR REPLACE TEMPORARY TABLE attr_findings AS
SELECT parcel_id AS feature_id, 'ATTR_NULL_001' AS rule_id, 'blocker' AS severity,
       'parcel_id is null' AS message
FROM core.parcels WHERE load_date = :run_date AND parcel_id IS NULL
UNION ALL
SELECT parcel_id, 'ATTR_DOMAIN_001', 'blocker',
       'zoning_code ' || COALESCE(zoning_code, '<null>') || ' not in the controlled list'
FROM core.parcels
WHERE load_date = :run_date AND (zoning_code IS NULL
  OR zoning_code NOT IN ('R1', 'R2', 'C1', 'C2', 'M1'));

-- Geometry rules: validity, type, SRID and plausibility in one pass.
CREATE OR REPLACE TEMPORARY TABLE geom_findings AS
WITH checked AS (
  SELECT parcel_id, geom,
         ST_ISVALID(geom)     AS is_valid,
         ST_ASGEOJSON(geom):type::STRING AS gtype,
         ST_SRID(geom)        AS srid,
         ST_AREA(geom)        AS area_m2,
         ST_NPOINTS(geom)     AS vertices
  FROM core.parcels
  WHERE load_date = :run_date
)
SELECT parcel_id, 'GEOM_VALID_001', 'blocker', 'fails OGC validity' FROM checked
WHERE NOT is_valid
UNION ALL
SELECT parcel_id, 'GEOM_SRID_001', 'blocker',
       'SRID is ' || srid || ', expected 27700' FROM checked WHERE srid <> 27700
UNION ALL
SELECT parcel_id, 'GEOM_TYPE_001', 'blocker',
       'expected Polygon or MultiPolygon, found ' || gtype FROM checked
WHERE gtype NOT IN ('Polygon', 'MultiPolygon')
UNION ALL
SELECT parcel_id, 'GEOM_AREA_001', 'warning',
       'implausible parcel area: ' || ROUND(area_m2, 1) || ' m2' FROM checked
WHERE area_m2 < 5 OR area_m2 > 5000000;

Verification: run the geometry pass on an XSMALL warehouse and then on a LARGE one and compare elapsed time and credits. Per-row scan work usually shows a poor return on the larger warehouse, which is the evidence you need to size the suite properly.

Step 4 — Enable search optimization, then run topology

-- Geospatial search optimization: the closest thing to a spatial index here.
ALTER TABLE core.parcels ADD SEARCH OPTIMIZATION ON GEO(geom);

-- Confirm it has finished building before benchmarking anything.
SHOW TABLES LIKE 'PARCELS' IN SCHEMA core;   -- inspect search_optimization_progress

CREATE OR REPLACE TEMPORARY TABLE topo_findings AS
WITH day AS (
  SELECT parcel_id, geom FROM core.parcels WHERE load_date = :run_date
)
SELECT a.parcel_id AS feature_id, 'TOPO_OVERLAP_001' AS rule_id, 'blocker' AS severity,
       'overlaps ' || b.parcel_id || ' by ' ||
       ROUND(ST_AREA(ST_INTERSECTION(a.geom, b.geom)), 2) || ' m2' AS message
FROM day a
JOIN day b
  ON a.parcel_id < b.parcel_id
 AND ST_INTERSECTS(a.geom, b.geom)
WHERE NOT ST_TOUCHES(a.geom, b.geom)
  AND ST_AREA(ST_INTERSECTION(a.geom, b.geom)) > 0.5;

Verification: check the query profile for partitions scanned versus total. With search optimization active and the predicate expressed as ST_INTERSECTS — not as a buffered distance — pruning should be visible. If the scanned fraction is close to one, the predicate is not in a prunable form.

Step 5 — Publish findings and attribute the credits

CREATE TABLE IF NOT EXISTS qa.findings (
  run_date DATE, feature_id STRING, rule_id STRING, severity STRING, message STRING
) CLUSTER BY (run_date, rule_id);

INSERT INTO qa.findings
SELECT :run_date, feature_id, rule_id, severity, message FROM attr_findings
UNION ALL SELECT :run_date, feature_id, rule_id, severity, message FROM geom_findings
UNION ALL SELECT :run_date, feature_id, rule_id, severity, message FROM topo_findings
UNION ALL SELECT run_date, parcel_id, rule_id, severity, message FROM qa.parse_rejects
WHERE run_date = :run_date;

-- What the run cost, attributed by the query tag set at session start.
SELECT query_tag,
       SUM(credits_used_cloud_services) AS cloud_credits,
       SUM(total_elapsed_time) / 1000 AS seconds,
       COUNT(*) AS statements
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time > DATEADD(day, -1, CURRENT_TIMESTAMP())
  AND query_tag LIKE 'spatial-qa%'
GROUP BY query_tag ORDER BY cloud_credits DESC;

Verification: set ALTER SESSION SET QUERY_TAG = 'spatial-qa/2026.08.1' at the start of every run so the attribution query works. Without a tag, validation credits disappear into the general warehouse spend and nobody can answer whether the suite is worth its cost.

Interpreting Results

The SRID findings tend to dominate a first run and then vanish permanently, which is the healthy pattern: it is a loader defect, fixed once. Parse rejects behave differently — they track upstream data quality and belong on the trend chart described in Building a Spatial Data Quality Scorecard.

Gotchas & Edge Cases

SRID 0 compares happily with SRID 0. Two datasets in different projected systems, both loaded without an SRID, will join without complaint and produce confidently wrong distances. The type system only protects you once the SRIDs are set.

GEOGRAPHY and GEOMETRY are not interchangeable in function signatures. Some functions exist for one and not the other, and the error message is about an unknown function rather than about the type. Check availability before porting a rule.

Search optimization costs credits continuously. It maintains its structures as data changes, so enabling it on a high-churn table you rarely join is pure cost. Enable per table, measure, and remove it where the join never materialised.

Micro-partition pruning depends on clustering. A table loaded in random order has micro-partitions whose bounding boxes overlap heavily, and no amount of search optimization fully compensates. Loading spatially sorted data — by geohash or H3 cell — improves pruning for every subsequent spatial query.

ST_AREA on GEOMETRY returns units of the SRID. For a projected system that is square metres; for a geographic SRID it is square degrees, which is meaningless as an area. A plausibility rule with metre thresholds against a geographic SRID flags everything.

Time travel keeps the old geometry. Repairing in place in Snowflake is reversible, which is useful, but it also means a “fixed” table still contains the defect for the retention period. That is an advantage for auditing and a consideration for anyone reasoning about storage.

When to Escalate

• Rules requiring geometry repair — Snowflake’s repair surface is narrower than PostGIS’s. Detect here, and route repairs to the system that owns the editable copy, following Automated Geometry Remediation.
• Credit growth that survives tuning means the suite has outgrown full-partition runs. Move to change-driven validation triggered by streams and tasks rather than a nightly full pass.
• Persistent parse rejects from one supplier are a contract issue rather than a pipeline issue; the rows are not in the warehouse at all, so no downstream check can find them.
• Cross-engine disagreement on validity or area is almost always the type and SRID configuration. Confirm both sides before treating it as a defect, and record the authoritative computation in the metadata.

Frequently Asked Questions

Related

• Warehouse and Lakehouse Spatial Validation — the shared in-place model
• Running Spatial Quality Checks in BigQuery GIS — the same rules under spheroidal semantics
• Comparing Spatial Validation Engines — how to evaluate an engine before porting a rule set

Back to Warehouse and Lakehouse Spatial Validation