Warehouse and Lakehouse Spatial Validation

For a growing number of organisations the authoritative copy of the spatial data is not in a spatial database at all — it is a partitioned table in BigQuery, a Snowflake share, or a directory of GeoParquet files queried by DuckDB. Exporting all of it to PostGIS every night to run validation is the wrong shape of solution: it is slow, it costs egress, and it creates a second version of the data that will eventually disagree with the first. This topic covers running the checks where the data already sits: what predicate coverage each engine offers, how the geometry and geography type distinction changes rule semantics, how to write rules that prune partitions, and how to keep the result contract identical to the rest of the pipeline described in Spatial Validation Tooling and Frameworks.

Prerequisites

• An engine with spatial support: DuckDB 0.10+ with the spatial extension, BigQuery GIS, Snowflake with geospatial types, or Databricks with Sedona. Versions matter — spatial function coverage has moved quickly in all of them.
• Data in a queryable layout: partitioned Parquet or GeoParquet, a native table, or an external table over object storage. The validation strategy depends far more on the layout than on the engine.
• A canonical CRS decision made in Coordinate Reference System Precision Standards. Warehouse geography types generally assume WGS 84; projected work needs the geometry type and explicit handling.
• A cost model. In a serverless warehouse, a badly written rule is a bill rather than a slow query, and the difference only becomes visible after the fact.
• The shared result contract — feature identifier, rule identifier, severity, message — so the findings table joins the same reporting as every other stage.

Core Concepts & Architecture

The first thing to establish is predicate coverage, because it varies more than the marketing suggests. Every engine has ST_Intersects, ST_Contains and ST_Distance. Fewer have ST_IsValid, and fewer still have a repair function. DuckDB’s spatial extension wraps GEOS and therefore offers a near-PostGIS surface; BigQuery deliberately omits validity checking because its geography type cannot represent an invalid geometry — it rejects one at load time instead; Snowflake sits in between and has its own validation semantics on ingest. A rule set ported without checking coverage first fails halfway through, having produced partial results that look complete.

The second is the geometry versus geography distinction, which is a semantic decision rather than a performance one. A geography type computes on a spheroid: distances are geodesic, areas are correct at continental scale, and edges between vertices follow great-circle arcs. A geometry type computes on a plane, so edges are straight lines in the projected coordinate system and areas are whatever the projection makes them. Most cadastral, engineering and utility rules are defined in a projected system and expect planar semantics — a boundary between two vertices is a straight line on the ground, not a great circle. Running those rules against a geography type silently changes their meaning, usually by tiny amounts that only show up as sliver differences.

The third is where the geometry is decoded. Columnar formats store geometry as WKB, and decoding it is the expensive part of most warehouse spatial queries. Every engine can skip that work if you help it: a bounding-box column, a clustering key, or the GeoParquet bbox covering metadata lets the planner prune row groups before decoding anything. A rule that filters on a bounding box first and a precise predicate second — the two-phase filter pattern from Validation Pipeline Architecture — is not just faster in a warehouse, it is often the difference between a query that runs and one that is cancelled.

Finally, the unit of work is a partition, not a file or a feature. Warehouse-native validation works best when each rule is expressible as SQL over one partition, so incremental runs, retries and backfills all operate at partition granularity — the same model as the asset-based orchestration described in Scheduling Spatial Validation with Dagster Assets.

Designing for Scale

Push filters down and decode late. A rule written as WHERE NOT ST_IsValid(geom) decodes every geometry in the table; the same rule with a partition predicate and a bounding-box prefilter may decode a few percent of them. Where the engine supports it, keep minx, miny, maxx, maxy as ordinary numeric columns — they cost a few bytes per row and unlock pruning that no amount of query tuning can otherwise achieve.

Cluster on the dimension your rules filter on. That is usually a temporal column for incremental runs and a spatial key — a geohash prefix or an H3 cell — for topology rules that compare neighbours. A table clustered on load timestamp makes nightly incremental validation cheap; a table clustered on a spatial key makes self-joins cheap; one table cannot be optimal for both, which is a genuine trade-off worth deciding explicitly.

Self-joins deserve particular care. A pairwise topology rule is O(n²) without a spatial prefilter, and warehouse engines will happily attempt it. Restrict the join to candidate pairs sharing a spatial key, then apply the precise predicate:

-- Overlap detection restricted to parcels sharing an H3 cell — the candidate set is small.
WITH keyed AS (
  SELECT parcel_id, geom, h3_cell
  FROM parcels
  WHERE load_date = CURRENT_DATE()
)
SELECT a.parcel_id AS a_id, b.parcel_id AS b_id,
       ST_Area(ST_Intersection(a.geom, b.geom)) AS overlap_area
FROM keyed a
JOIN keyed b
  ON a.h3_cell = b.h3_cell            -- candidate pairs only
 AND a.parcel_id < b.parcel_id        -- each pair once
WHERE ST_Intersects(a.geom, b.geom)
  AND NOT ST_Touches(a.geom, b.geom)
  AND ST_Area(ST_Intersection(a.geom, b.geom)) > 0.5;

The a.parcel_id < b.parcel_id line is easy to omit and doubles every count when it is missing. Parcels straddling a cell boundary need the neighbouring-cell join added, which is the warehouse version of the partition-overlap problem discussed in Batch Processing Large Spatial Datasets.

Rule Evaluation Strategies

Warehouse rules divide neatly into three shapes.

Column rules need no geometry at all — nulls, domains, ranges, referential integrity. Run them first and run them everywhere; they are nearly free and they eliminate rows before any geometry is decoded.

Per-geometry rules decode one geometry at a time: validity, geometry type, coordinate bounds, vertex count, area plausibility. These scale linearly and parallelise across partitions without coordination.

Pairwise rules need two geometries and therefore a join. They are the expensive ones and the ones that must be spatially keyed.

Expressing all three in one SQL pattern keeps the results uniform:

-- One findings row per violation, identical contract across rule shapes.
CREATE OR REPLACE TABLE qa.findings_2026_08_06 AS
SELECT parcel_id AS feature_id, 'ATTR_NULL_001' AS rule_id, 'blocker' AS severity,
       'parcel_id is null' AS message
FROM parcels WHERE parcel_id IS NULL
UNION ALL
SELECT parcel_id, 'GEOM_VALID_001', 'blocker', 'geometry fails OGC validity'
FROM parcels WHERE NOT ST_IsValid(geom)
UNION ALL
SELECT a_id, 'TOPO_OVERLAP_001', 'blocker',
       'overlaps ' || b_id || ' by ' || CAST(ROUND(overlap_area, 2) AS VARCHAR) || ' m2'
FROM overlaps;

A single findings table per run, with the same columns as the findings produced by the GeoPandas and PostGIS stages, is what allows one dashboard and one severity model to cover a pipeline that spans three engines.

Error Handling & Remediation

Detection and repair usually separate in a warehouse. Detection is cheap and expressible in SQL; repair often is not, because the engine lacks a ST_MakeValid equivalent or because the authoritative copy is immutable by design — a lakehouse table with time travel should not be edited in place by a validator.

The workable pattern is: detect in the warehouse, write findings to a results table, and route the affected feature identifiers to whichever system owns repair. Where the engine does support repair and the table is writable, apply the same guards used elsewhere — snapshot the original, bound the area change, and write an audit row, as set out in Automated Geometry Remediation.

Rejected rows need somewhere to go that is not a deleted row. A quarantine table with the original record, the rule identifier and the run identifier plays the same role as a dead-letter queue in a streaming design, and it has the advantage of being queryable by the people who need to fix the data.

Observability, Lineage and Cost

Warehouse validation has one signal the other engines do not: bytes scanned. Track it per rule. A rule whose scan volume grows month over month is scanning history it should be pruning, and the finding usually arrives as an invoice rather than as an alert.

Lineage is straightforward here because the engine already records it. Query history gives you the exact SQL, the run time and the bytes processed; adding the rule-set version as a query label or comment ties those records to the rules that produced them. That gives auditors the reproducibility described in Observability and Lineage for Validation without building anything new.

Keep the findings table partitioned by run date and retain it. Trend analysis over findings — defect rate per layer per week — is the input to the scorecard described in Spatial Data Quality Metrics and Reporting, and it is much easier to build when the history is already a table in the same warehouse as the business data.

Best Practices and Anti-Patterns

• Do verify predicate coverage before porting a rule set; the gaps are not where you expect.
• Do state geometry or geography semantics per rule set, in writing.
• Do keep bounding-box columns and cluster on what your rules filter on.
• Do restrict pairwise joins with a spatial key and an ordering predicate.
• Do write findings with the same contract as every other engine in the pipeline.
• Don’t export the whole table to a spatial database nightly just to run checks that could run in place.
• Don’t mix geography and geometry in one comparison; the result is silently wrong rather than an error.
• Don’t let a rule scan the full history on every run.
• Don’t repair in place in an immutable table — write findings and let the owning system decide.
• Don’t assume ST_IsValid exists; some engines validate on ingest and offer nothing afterwards.

Frequently Asked Questions

Related

• Validating Geometries in DuckDB Spatial — local and lakehouse validation over Parquet with GEOS-backed predicates
• Running Spatial Quality Checks in BigQuery GIS — geography semantics, clustering and cost control
• Validating Spatial Data in Snowflake — ingest-time validation and warehouse sizing for spatial joins
• Comparing Spatial Validation Engines — the wider engine selection frame
• Validating GeoParquet Schemas with PyArrow — the storage contract underneath a lakehouse table

Back to Spatial Validation Tooling and Frameworks