Skip to content

SKILL.md

Latest commit

 

History

History
226 lines (171 loc) · 7.47 KB

SKILL.md

File metadata and controls

226 lines (171 loc) · 7.47 KB
Raw
name bigquery-overture-skill
description Build and optionally execute cost-safe Overture Maps SQL through the BigQuery bq CLI, with mandatory dry-run cost checks and visualization handoff. Use when a user needs map-ready Overture Maps queries in BigQuery, wants SQL-only output or executed numeric results, and needs budget enforcement with clear over-budget fallback options.

BigQuery Overture Cost Aware

Use this skill for Overture Maps work in BigQuery with strict cost controls.

Non-Installation Rule

Never install software automatically. If dependencies are missing, report exact prerequisite commands for the user to run.

Preferred Execution Path

Use the bundled script for deterministic behavior:

./scripts/run_cost_checked_query.sh --query-file /path/to/query.sql --mode sql_only

Or:

./scripts/run_cost_checked_query.sh --query "SELECT ..." --mode execute

Script location:

  • scripts/run_cost_checked_query.sh

The script already handles:

  • Optional .env loading from current working directory
  • BQ_PROJECT_ID fallback to gcloud config get-value project
  • BQ_LOCATION optional passthrough
  • BQ_MAX_BYTES_BILLED safe default 10737418240 (10 GiB)
  • Optional auth env support: GOOGLE_APPLICATION_CREDENTIALS, BIGQUERY_CREDENTIALS_BASE64
  • Mandatory dry run and bytes budget gate before execution

Inputs

Collect or infer:

  • mode: infer from user intent (sql_only for query drafting/verification, execute when user asks for actual numbers/results)
  • User intent: dataset/theme, filters, output columns, aggregation, map vs numeric output
  • Optional bounds: bbox, date/time, row limit
  • Optional explicit over-budget override

Guardrails

Apply all guardrails every time:

  1. Run dry run before execution.
  2. Enforce maximum_bytes_billed.
  3. Prefer bounded SQL by default (bbox/date/limit, minimal selected columns).
  4. If estimated bytes exceed budget and no explicit override: do not execute.
  5. If over budget: provide lower-cost SQL variants.

Query Construction Rules

  1. Select only required columns; avoid SELECT *.
  2. Prefer filtered Overture tables and partition-friendly predicates.
  3. Add default limits when user omitted bounds:
  • Spatial bounding constraints for map requests
  • Row cap (LIMIT) for previews
  1. Separate heavy geometry retrieval from numeric aggregation when practical.

H3 Aggregation Support

Use H3 when requested by the user, or when spatial aggregation/binning is needed (heatmaps, density summaries, cell-based rollups).

H3 function namespace by location

  • Use jslibs.h3.* (US convenience namespace) for US/default location queries.
  • Use jslibs.eu_h3.* for EU location queries.
  • jslibs.us_h3.* is equivalent to US behavior when explicit namespace is preferred.

H3 usage patterns

  1. Point/cell index:
jslibs.h3.ST_H3(<geography_point>, <resolution>)
  1. Polygon fill to cells:
jslibs.h3.ST_H3_POLYFILLFROMGEOG(<geography_polygon>, <resolution>)
  1. Cell boundary for visualization:
jslibs.h3.ST_H3_BOUNDARY(<h3_index>)

H3 safety/cost rules

  1. Apply selective WHERE + hardcoded bbox first, then compute H3.
  2. For aggregation queries, return h3 + aggregate metrics before adding boundaries.
  3. Use COUNT(*) previews before geometry-heavy ST_H3_BOUNDARY output.
  4. Keep resolution reasonable by default (7-9 city scale) unless user requests otherwise.
  5. If over budget, lower resolution or narrow bbox/date filters before retrying.

Schema Discovery Patterns

List Overture tables:

SELECT table_name
FROM `bigquery-public-data.overture_maps.INFORMATION_SCHEMA.TABLES`
ORDER BY table_name;

List columns for one table:

SELECT
  column_name,
  data_type,
  is_nullable,
  ordinal_position
FROM `bigquery-public-data.overture_maps.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'division_area'
ORDER BY ordinal_position;

Querying Overture Safely (Small/Fast Only)

Rules:

  • Always start with selective WHERE filters.
  • Always include a hard-coded bbox prefilter on bbox.xmin/xmax/ymin/ymax for large feature tables (for example, segment, building, place). This prevents full table scans by enabling partition pruning.
  • bbox does not replace real boundaries. When the user asks about a named area (city, district, country), always resolve its actual geometry from division_area and clip with ST_INTERSECTS. bbox is only a fast pre-filter gate — it is rectangular and overshoots real boundaries.
  • Never omit ST_INTERSECTS for named-area queries. Always use bbox + ST_INTERSECTS together: bbox gates the scan cost, ST_INTERSECTS ensures geographic correctness.
  • Add LIMIT for exploration.
  • Prefer aggregate previews (COUNT(*)) before geometry-heavy pulls.
  • Avoid full table scans and large result sets.

Example (optimized rail query pattern):

WITH city AS (
  SELECT geometry
  FROM `bigquery-public-data.overture_maps.division_area`
  WHERE country = 'DE'
    AND region = 'DE-BE'
    AND subtype = 'region'
    AND class = 'land'
  LIMIT 1
)
SELECT
  s.id,
  s.geometry
FROM `bigquery-public-data.overture_maps.segment` s
CROSS JOIN city c
WHERE s.subtype = 'rail'
  -- hardcoded city bbox (Berlin in this example)
  AND s.bbox.xmax >= 13.08834457397461
  AND s.bbox.xmin <= 13.761162757873535
  AND s.bbox.ymax >= 52.33823776245117
  AND s.bbox.ymin <= 52.67551040649414
  -- exact clip after bbox gate
  AND ST_INTERSECTS(s.geometry, c.geometry)
LIMIT 1000;

Recommended Agent Workflow

  1. Discover table and column metadata via INFORMATION_SCHEMA.
  2. Resolve the target area's actual geometry from division_area. Extract its bbox numeric constants and hardcode them into the query as the scan gate.
  3. Draft the query with bbox prefilter first (scan gate), then ST_INTERSECTS against the real boundary geometry second (exact clip). Both are required for named-area queries.
  4. Validate output shape and types with LIMIT or COUNT(*).
  5. Iterate in small steps; do not run broad/full extraction queries unless explicitly requested.

Mode Behavior

Decide mode automatically unless user explicitly requests one.

sql_only (default)

  • Build optimized SQL
  • Run mandatory dry run
  • Return SQL + estimated bytes + budget pass/fail + visualization handoff

execute

  • Build optimized SQL
  • Run mandatory dry run
  • Execute only if user asked for results and estimate is within budget (or explicit user override)
  • Return rows/aggregates preview + SQL + visualization handoff

Failure Handling

If bq unavailable or auth fails:

  • Return exact fix commands only (no auto-install/no auto-auth side effects).

If over budget:

  • Keep status=blocked_over_budget
  • Do not execute query
  • Return at least one cheaper SQL variant

If query invalid:

  • Return corrected SQL draft and rerun dry-run logic

Output Contract

Always return:

  • mode
  • status (dry_run_only | executed | blocked_over_budget)
  • project_id
  • location
  • estimated_bytes
  • max_bytes_billed
  • query_sql
  • result_preview (if executed)
  • visualization_handoff (dekart, bigquery_studio)
  • next_steps

Visualization Handoff

Always include both handoffs:

  • dekart: open Dekart, connect to same project, paste query_sql, map geometry fields.
  • bigquery_studio: open https://console.cloud.google.com/bigquery?project=<PROJECT_ID> and paste query_sql in SQL workspace.

Response Quality Rules

  1. Be explicit about budget pass/fail.
  2. Show the final SQL used for dry run.
  3. Keep previews concise.
  4. If blocked, include cheaper alternatives.