# Bigquery Overture Skill

> 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.

- Skill: `dekart-xyz/bigquery-overture-skill` (Agent Skill, multi-file: 5 files)
- Install (CLI): `npx skillmds@latest add dekart-xyz/bigquery-overture-skill`
- Raw SKILL.md: https://api.skillmd.com/api/skills/dekart-xyz/bigquery-overture-skill/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: dekart-xyz (https://skillmd.com/u/dekart-xyz)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/dekart-xyz/bigquery-overture-skill

---


# 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:

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

Or:

```bash
./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
4. 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:

```sql
jslibs.h3.ST_H3(<geography_point>, <resolution>)
```

2. Polygon fill to cells:

```sql
jslibs.h3.ST_H3_POLYFILLFROMGEOG(<geography_polygon>, <resolution>)
```

3. Cell boundary for visualization:

```sql
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:

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

List columns for one table:

```sql
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):

```sql
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.