query_cached
Run SQL queries against locally cached Ontario open data using DuckDB. Supports aggregations, joins, and full SQL syntax.
Instructions
Run a SQL query against locally cached data in DuckDB.
Use table names from download_resource or cache_info. Supports full DuckDB SQL: aggregations, window functions, CTEs, JOINs across tables.
Use SUM(quantity_col) not COUNT() when rows contain per-row counts (e.g. a "count" or "number_of" column). COUNT() counts rows, not quantities. Column names vary across resources in the same dataset — always DESCRIBE first. Use SELECT * RENAME ("old col" AS new_col) to normalize column names when joining across datasets. Values containing semicolons should be matched with LIKE patterns, not = equality. Quote table names with double quotes in SQL.
Args: sql: SQL query (e.g. SELECT * FROM "ds_my_table_abc12345" LIMIT 10)
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | The SQL query that was executed | |
| rows | Yes | Result rows as JSON objects | |
| columns | Yes | Column names in result order | |
| total_rows | No | Total rows matched (may exceed rows if truncated) | |
| truncated | Yes | True if results were truncated to MAX_QUERY_ROWS | |
| warnings | Yes | Heuristic warnings (e.g. COUNT(*) vs SUM suggestion) |