run_sql
Execute read-only SQL queries to analyze NYC capital project schedule, budget, and lifecycle data. Query typed tables with domain rules for accurate project and funding insights.
Instructions
Run a read-only SELECT against the local CPD DuckDB.
output: 'inline' (default, rows capped) | 'csv' | 'xlsx' (writes a file, returns path).
PREFER the typed tables: latest_project_state (1 row/PID, borough+boroughs,
attributed_budget), schedule_history (PID x period), budget_history (snapshot rows
at the (fms_id, managing_agency) x period grain), original_budget (adopted first
budgets), lifetime_budget_variance (per-line lifetime, original_budget_source),
schedule_budget_link (PID<->FMS edges), pid_funding (per-PID link rollup),
cumulative_schedule_variance (per-PID lifetime days), fms_sponsor (fms_id -> owner),
fms_location (line-level borough/community board), agency_rollup_by_period,
category_dim, agency_dim, project_budget_fy, meta.
GRAIN RULES: budget comparisons key on (managing_agency, fms_id) — never fms_id
alone; sponsor-scoped budget sums use the semi-join
fms_id IN (SELECT fms_id FROM fms_sponsor WHERE sponsor_agency = ...) — a
value-bearing JOIN fans out across a line's agency rows and double-counts.
PERIOD BASIS: fms_location, fms_sponsor, lifetime_budget_variance are ALL-HISTORY
dimensions (latest row per line/owner, NO reporting_period column) — JOIN them to
enrich or for lifetime figures; do NOT COUNT them as a single period's inventory.
For a period count, aggregate raw_project_detail / schedule_history / budget_history
filtered by reporting_period. Every result echoes latest_reporting_period (and warns
via period_basis_note when a query counts an all-history dim) — state the basis.
RAW mirrors (raw_project_detail, raw_budget_fy, raw_budget_history,
raw_schedule_history) are all VARCHAR — cast as needed.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | ||
| output | No | inline |