Data Model and Join Keys

The Snowflake listing is designed around a small set of shared identifiers, audit context fields, and spatial outputs.

Core join keys

Key	Use
SiteGuid	Stable place identifier used across almost every dataset
AuditGuid	Stable audit identifier used to group records into one audit
PlanId	Georeferenced plan identifier used where mapped observations rely on a plan

Common context fields

These fields appear across much of the listing:

Field	Meaning
SiteName	Human-readable site name
AuditDateLocal	Local audit date
EventLocalHour	Local event hour where available
TimeOfDay	Time bucket such as 7-8 or 8-9
Latitude, Longitude	Representative point for the row
GeometryWkt	WKT geometry suitable for TRY_TO_GEOGRAPHY()
GeometryGeoJson	GeoJSON geometry representation
GeometryMethod	Method used to derive geometry
GeometryIsApproximate	Flag for derived or approximate geometry

Dataset grain

Table	Typical grain
SITE_METADATA	One row per site
AUDIT_METADATA	One row per audit
PLAN_METADATA	One row per plan
MOVEMENT_COUNTS	One row per count record or category within an audit shift
DWELL_OBSERVATIONS	One row per observed stay event
TRACE_PATHS	One row per observed movement trace
SITE_INVENTORY	One row per mapped inventory item
FACADE_QUALITY	One row per facade drawing or trace set
INTERCEPT_RESPONSES	One row per intercept response
OBSERVATIONS	One row per diary or observation entry

Standard join patterns

Site-level analysis

Join on SiteGuid when you want to compare datasets at the place level:

SELECT
  s.SiteName,
  COUNT(DISTINCT a.AuditGuid) AS audit_count
FROM LISTING.SITE_METADATA s
LEFT JOIN LISTING.AUDIT_METADATA a
  ON s.SiteGuid = a.SiteGuid
GROUP BY 1;

Audit-level analysis

Join on AuditGuid when you want all records from a specific audit:

SELECT
  a.SiteName,
  a.AuditDateLocal,
  SUM(c.Number) AS total_people
FROM LISTING.AUDIT_METADATA a
JOIN LISTING.MOVEMENT_COUNTS c
  ON a.AuditGuid = c.AuditGuid
GROUP BY 1, 2;

Plan-based spatial analysis

Use PlanId when mapped observations depend on a plan image:

SELECT
  p.PlanId,
  p.PlanType,
  COUNT(*) AS stay_points
FROM LISTING.PLAN_METADATA p
JOIN LISTING.DWELL_OBSERVATIONS d
  ON p.PlanId = d.PlanId
GROUP BY 1, 2;

Geospatial pattern

For direct spatial work in Snowflake, use:

TRY_TO_GEOGRAPHY(GeometryWkt)

This is the cleanest common path across the listing.

Important interpretation note

TRACE_PATHS and FACADE_QUALITY should be understood as derived geometry built from SVG source paths and georeferenced plans. They are analytically useful, but they are not native GIS capture.