Masking Precise Coordinates for Privacy

This page shows you how to lower the precision of sensitive location data in a dbt mart — with ST_SnapToGrid, coordinate rounding, H3 aggregation, or jitter — and expose the precise geometry only to roles entitled to see it.

When to use this approach

Coordinate masking is the right control when a record’s location is the sensitive attribute and you still need location-shaped analytics. Reach for it when:

• Exact coordinates re-identify a person or asset. A home address, a patient’s residence, or a device’s overnight location is disclosive at full precision. Generalizing the point keeps the map useful while breaking the link to an individual. This sits alongside access control in data security and scoping rules — scoping decides who sees a row, masking decides how precisely they see it.
• Different roles need different precision. Analysts get grid-snapped or aggregated points; a fraud or operations team with a lawful basis gets full precision. That is a role-based masking decision, complementary to the topology-based filtering in implementing row-level security for geospatial data.
• You are already aggregating to a grid at scale. If a mart rolls points up to H3 cells for performance, that same aggregation is a privacy control — the technique overlaps with partitioning geospatial tables with H3 and dbt.

If the whole row must be hidden from a role, masking is the wrong layer — filter it out with scoping instead. Masking assumes the row is visible but its precision must be reduced.

Prerequisites

• dbt Core >= 1.6 so var() defaults and target-aware conditionals resolve consistently across environments.
• A spatial engine: PostGIS >= 3.1 (ST_SnapToGrid, ST_MakePoint), the DuckDB spatial extension for CI, or BigQuery / Snowflake with their native masking policies for the warehouse-side layer.
• A canonical SRID enforced in staging. Grid size and rounding precision are meaningless unless every geometry shares one coordinate frame — normalize first via spatial reference system management.
• A defined precision policy per role, wired through var() so the same models deploy unchanged from staging to production:

# dbt_project.yml
vars:
  canonical_srid: 4326
  mask_grid_degrees: 0.01     # ~1.1 km grid at the equator; tune to your risk model
  mask_min_cell_count: 5      # k-anonymity threshold for aggregated marts

Step-by-step instructions

The four techniques trade privacy strength against analytic utility differently. The chart maps each to what it preserves and what it destroys, so you can pick per column rather than applying one blunt rule everywhere.

1. Snap coordinates to a fixed grid

ST_SnapToGrid collapses every point onto the nearest node of a lattice, so any two locations within the same cell become indistinguishable. It is deterministic — the same input always yields the same masked point — which keeps joins and dedup stable.

-- models/marts/mart_customer_locations_masked.sql
{{ config(materialized='table') }}

SELECT
    customer_id,
    ST_SnapToGrid(geom, {{ var('mask_grid_degrees') }}) AS geom_masked,
    region_code
FROM {{ ref('stg_customer_locations') }}

Verify that many precise points now share a masked location (indistinguishability):

SELECT ST_AsText(geom_masked) AS cell, count(*) AS points
FROM {{ ref('mart_customer_locations_masked') }}
GROUP BY geom_masked HAVING count(*) > 1
ORDER BY points DESC LIMIT 10;
-- cells with a single point are still potentially identifying — see step 4

2. Round coordinates as a lightweight alternative

When you cannot rebuild geometry but can post-process lon/lat, rounding truncates precision directly. Two decimal places on EPSG:4326 is roughly a kilometre. Rebuild the point from rounded ordinates so the output stays a valid geometry with its SRID intact.

SELECT
    customer_id,
    ST_SetSRID(
      ST_MakePoint(
        ROUND(ST_X(geom)::numeric, 2),
        ROUND(ST_Y(geom)::numeric, 2)
      ),
      {{ var('canonical_srid') }}
    ) AS geom_rounded
FROM {{ ref('stg_customer_locations') }}

Verify the rounding actually reduced precision and preserved the SRID:

SELECT
    count(DISTINCT ST_AsText(geom))         AS distinct_precise,
    count(DISTINCT ST_AsText(geom_rounded)) AS distinct_rounded,
    count(DISTINCT ST_SRID(geom_rounded))   AS srids
FROM {{ ref('mart_customer_locations_masked') }};
-- distinct_rounded should be far smaller; srids must equal 1

3. Aggregate to H3 cells with a k-anonymity floor

The strongest control emits no individual coordinate at all. Roll points up to an H3 cell and suppress any cell with fewer than k members, so no output can be traced to a single record.

-- models/marts/mart_density_by_cell.sql
{{ config(materialized='table', cluster_by=['h3_index']) }}

WITH celled AS (
    SELECT
        h3_latlng_to_cell(ST_Y(geom), ST_X(geom), 8) AS h3_index
    FROM {{ ref('stg_customer_locations') }}
)
SELECT
    h3_index,
    h3_cell_to_boundary_wkt(h3_index) AS cell_boundary,
    count(*)                          AS member_count
FROM celled
GROUP BY h3_index
HAVING count(*) >= {{ var('mask_min_cell_count') }}   -- suppress small cells

Verify no cell below the threshold leaked through:

SELECT min(member_count) AS smallest_published_cell
FROM {{ ref('mart_density_by_cell') }};
-- must be >= var('mask_min_cell_count')

4. Apply jitter only as a complement, never alone

Jitter offsets each point by a small random vector. It hides an exact position in a single snapshot but is defeated by averaging repeated observations, so use it to blur a grid-snapped point, not as the sole control.

SELECT
    customer_id,
    ST_Translate(
      ST_SnapToGrid(geom, {{ var('mask_grid_degrees') }}),   -- deterministic base
      (random() - 0.5) * {{ var('mask_grid_degrees') }},     -- dx within one cell
      (random() - 0.5) * {{ var('mask_grid_degrees') }}      -- dy within one cell
    ) AS geom_jittered
FROM {{ ref('stg_customer_locations') }}

Verify the jitter stays bounded (no point drifts more than a cell from its snapped base):

SELECT max(ST_Distance(geom_jittered, ST_SnapToGrid(geom, {{ var('mask_grid_degrees') }}))) AS max_drift
FROM {{ ref('mart_customer_locations_masked') }};
-- max_drift should not exceed the grid size

5. Expose precision by role with a masking macro

Centralize the policy in one macro so every model masks identically and an entitled role can be granted precise geometry in exactly one place. Drive the decision from a var set per run or per environment.

-- macros/mask_geometry.sql
{% macro mask_geometry(geom_col, role=none) %}
  {% set active_role = role or var('viewer_role', 'analyst') %}
  {% if active_role == 'privileged' %}
    {{ geom_col }}
  {% else %}
    ST_SnapToGrid({{ geom_col }}, {{ var('mask_grid_degrees') }})
  {% endif %}
{% endmacro %}

-- models/marts/mart_locations_by_role.sql
{{ config(materialized='table') }}

SELECT
    customer_id,
    {{ mask_geometry('geom', var('viewer_role', 'analyst')) }} AS geom,
    region_code
FROM {{ ref('stg_customer_locations') }}

Verify the unprivileged build never emits full-precision points:

dbt run --select mart_locations_by_role --vars '{viewer_role: analyst}'
# then confirm no coordinate carries more precision than the grid:

SELECT count(*) AS overprecise
FROM {{ ref('mart_locations_by_role') }}
WHERE ST_X(geom) <> ROUND(ST_X(geom)::numeric, 2)::float8;
-- expect 0 for the analyst build; back this with warehouse column masking for ad-hoc access

Configuration reference

Gotchas & edge cases

• A single-member grid cell still identifies. Snapping does not help when one point sits alone in its cell — the masked location still points at one record. Combine snapping with a k-anonymity suppression (step 3) or a minimum-density check before publishing.
• Jitter alone is reversible by averaging. Repeated observations of the same entity with independent random offsets average back to the true point. Never rely on jitter as the only control; anchor it on a deterministic snap.
• Masking in a shared intermediate model leaks upstream. If the precise geometry flows through an intermediate table any analyst can read, masking at the mart is too late. Mask at the boundary where the data first becomes broadly readable, mirroring where the scope gate sits in data security and scoping rules.
• Grid units follow the SRID. ST_SnapToGrid(geom, 0.01) is ~1 km in degrees but 1 cm in a metric SRID. Confirm the CRS before choosing the grid size, or the mask silently does almost nothing.
• Rounding can create invalid geometry for non-points. Rounding polygon vertices can collapse or self-intersect rings. Round only point coordinates; generalize polygons with ST_SnapToGrid or ST_Simplify and re-validate with ST_IsValid.
• dbt-side masking does not stop ad-hoc SQL. A privileged analyst querying the precise staging table bypasses the mart mask entirely. Back the dbt layer with warehouse column-masking policies (Snowflake dynamic data masking, BigQuery data policies) for defense in depth, the same layering argued in implementing row-level security for geospatial data.

FAQ

Related

• Data Security & Scoping Rules — decide who sees a row; masking decides how precisely they see it.
• Implementing Row-Level Security for Geospatial Data — topology-based filtering that pairs with role-based masking.
• Partitioning Geospatial Tables with H3 and dbt — the same H3 aggregation used here as a scaling and privacy control.

Up: Part of Data Security & Scoping Rules.