snowflake-labs/retail-catchment

Deploy Retail Catchment Demo

Deploy the Retail Catchment Analysis demo that visualizes trade areas, competitors, and address density using OpenRouteService isochrones and Overture Maps data. Dashboard pages are served via the shared React Demo Dashboard app.

Configuration

| Parameter | Default | Description | |-----------|---------|-------------| | DATABASE | FLEET_INTELLIGENCE | Target database for all objects | | SCHEMA | RETAIL_CATCHMENT | Schema for retail analysis tables | | WAREHOUSE | ROUTING_ANALYTICS | Warehouse for queries and data loading |

Prerequisites

• OpenRouteService Native App installed (e.g., OPENROUTESERVICE_NATIVE_APP)
• A role with privileges listed in the Required Privileges section below
• snow CLI installed and configured

Required Privileges

| Privilege | Scope | Reason | |-----------|-------|--------| | CREATE DATABASE | Account | Creates FLEET_INTELLIGENCE database | | CREATE WAREHOUSE | Account | Creates ROUTING_ANALYTICS warehouse | | IMPORT SHARE | Account | Acquires OVERTURE_MAPS__PLACES and OVERTURE_MAPS__ADDRESSES from Marketplace | | USAGE ON DATABASE FLEET_INTELLIGENCE | Database | Uses the setup database | | CREATE SCHEMA | Database (FLEET_INTELLIGENCE) | Creates RETAIL_CATCHMENT schema | | CREATE TABLE | Schema (FLEET_INTELLIGENCE.RETAIL_CATCHMENT) | Creates CONFIG, RETAIL_POIS, CITIES_BY_STATE, REGIONAL_ADDRESSES, REGION_CONFIG | | USAGE ON DATABASE OVERTURE_MAPS__PLACES | Database | Reads Marketplace POI data | | USAGE ON DATABASE OVERTURE_MAPS__ADDRESSES | Database | Reads Marketplace address data | | USAGE ON DATABASE OPENROUTESERVICE_NATIVE_APP | Database | Calls ORS isochrone functions |

Note: ACCOUNTADMIN is NOT required. Create a custom role with the above privileges, or use any role that has them.

Error Logging

Follow the Error Logging convention in AGENTS.md. Log file prefix: retail-catchment.

Execution Rules

1. One statement per snowflake_sql_execute tool call.
2. Always use fully qualified object names.
3. Never use SET session variables.
4. Verify row counts after each CTAS.
5. All CREATE statements must include a COMMENT tracking tag.

Workflow

Quick deploy: Run references/seed-data.sql via snow sql -f to execute Steps 4-5 in one shot. Edit the SET variables at the top to customize for your region.

Full SQL with step-by-step explanations: references/sql-pipeline.md All CREATE statements in the referenced SQL include COMMENT tracking tags per AGENTS.md convention ("origin":"sf_sit-is-fleet","name":"oss-retail-catchment").

Step 1: Set Query Tag for Tracking

Pre-check: If data already exists, skip to Step 6. Run:

SELECT COUNT(*) AS cnt FROM FLEET_INTELLIGENCE.RETAIL_CATCHMENT.RETAIL_POIS;

If cnt > 0, the data pipeline has already run. Skip to Step 6 (Verify) as needed.

Goal: Set session query tag for attribution tracking.

See references/sql-pipeline.md Step 1.

Step 2: Verify OpenRouteService Installation

Goal: Confirm OpenRouteService Native App is installed and services are running.

1. Check ORS application exists
2. Verify ORS_SERVICE and ROUTING_GATEWAY_SERVICE are RUNNING
3. Resume suspended services if needed

STOP if ORS is not installed. Direct user to build-routing-solution skill.

See references/sql-pipeline.md Step 2.

Step 3: Get Carto Overture Datasets from Marketplace

Goal: Acquire Overture Maps Places and Addresses datasets for POI and density data.

1. Get Overture Maps Places (POI data)
2. Get Overture Maps Addresses (for H3 density)
3. Verify both datasets are accessible

See references/sql-pipeline.md Step 3.

Step 4: Create Database, Schema, Warehouse, and CONFIG

Goal: Set up the demo database, schema, warehouse, and CONFIG table.

See references/sql-pipeline.md Step 4.

Output: Database FLEET_INTELLIGENCE, schema RETAIL_CATCHMENT created with CONFIG table.

Step 5: Create Optimized Data Tables

Goal: Create pre-filtered, performance-optimized tables from Overture Maps marketplace data.

Important: Step 5 uses SQL session variables (SET REGION_KEY, SET BBOX_*). Execute all Step 5 sub-steps in a single session (e.g., via snow sql -f) or prepend the SET statements to each sub-step's SQL block when using snowflake_sql_execute.

1. Set region key and bounding box configuration (customize for target region)
2. Create and populate filtered POI table (RETAIL_POIS)
3. Create and populate pre-aggregated cities table (CITIES_BY_STATE)
4. Create and populate addresses table (REGIONAL_ADDRESSES)
5. Store region configuration (REGION_CONFIG)
6. Add search optimization and clustering
7. Verify tables have data

STOP if any table has 0 rows. Check bounding box config and Marketplace access.

See references/sql-pipeline.md Step 5.

Step 6: Verify

Goal: Confirm data tables exist and have rows.

See references/sql-pipeline.md Step 8.

Dashboard Schema Contract

The React Demo Dashboard page queries these exact tables and columns. If the pipeline changes column names, the React page must be updated to match.

CONFIG

| Column | Type | Used By | |--------|------|---------| | VEHICLE_TYPE | VARCHAR | Global vehicle type selector | | REGION | VARCHAR | Global region selector (updated by server on region switch) |

RETAIL_POIS

| Column | Type | Used By | |--------|------|---------| | REGION | VARCHAR | RetailCatchment (region filter) | | POI_ID | VARCHAR | RetailCatchment (store selection, competitor filter) | | POI_NAME | VARCHAR | RetailCatchment (store dropdown, metrics) | | BASIC_CATEGORY | VARCHAR | RetailCatchment (category filter, competitor breakdown) | | CITY | VARCHAR | RetailCatchment (city filter) | | GEOMETRY | GEOGRAPHY | RetailCatchment (ST_X/ST_Y for map, ST_WITHIN for competitors) |

CITIES_BY_STATE

| Column | Type | Used By | |--------|------|---------| | REGION | VARCHAR | RetailCatchment (region filter) | | CITY | VARCHAR | RetailCatchment (city dropdown) |

REGIONAL_ADDRESSES

| Column | Type | Used By | |--------|------|---------| | REGION | VARCHAR | RetailCatchment (region filter) | | GEOMETRY | GEOGRAPHY | RetailCatchment (H3 density, ST_WITHIN catchment filter) |

---

Features

The deployed app provides:

• Isochrone Analysis: Travel-time based catchment zones (1-60 min)
• Competitor Mapping: Find competitors within catchment areas
• H3 Address Density: Visualize residential density using hexagonal grid
• Smart Location Recommendation: AI-powered optimal new location suggestions
• Market Analysis: Synthetic footfall, population density, income data

Stopping Points

• ✋ Step 2: Verify ORS is installed before proceeding
• ✋ Step 3: Confirm marketplace data is accessible
• ✋ Step 5: Verify optimized tables have data before registering

Troubleshooting

| Issue | Solution | |-------|----------| | "No stores found" | Verify Overture Maps Places dataset is accessible | | Isochrone fails | Check ORS services are RUNNING | | Dashboard shows no data | Verify RETAIL_POIS table has rows; check column BASIC_CATEGORY, CITY exist | | RETAIL_POIS table empty | Check bounding box config and Overture Maps Places access | | REGIONAL_ADDRESSES table empty | Check bounding box config and Overture Maps Addresses access | | "Object does not exist" on table | Ensure Step 5 completed successfully before Step 6 |

Output

Deployed resources:

• Database: FLEET_INTELLIGENCE
• Schema: FLEET_INTELLIGENCE.RETAIL_CATCHMENT
• Warehouse: ROUTING_ANALYTICS
• Tables: CONFIG, RETAIL_POIS, CITIES_BY_STATE, REGIONAL_ADDRESSES, REGION_CONFIG

Cleanup

To remove all objects created by this skill:

DROP TABLE IF EXISTS FLEET_INTELLIGENCE.RETAIL_CATCHMENT.CONFIG;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.RETAIL_CATCHMENT.REGION_CONFIG;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.RETAIL_CATCHMENT.REGIONAL_ADDRESSES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.RETAIL_CATCHMENT.CITIES_BY_STATE;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.RETAIL_CATCHMENT.RETAIL_POIS;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.RETAIL_CATCHMENT;

Tip: Use the cleanup skill to auto-discover all tagged objects via COMMENT tracking.