carto-query-datawarehouse

carto-query-datawarehouse

Run SQL — spatial or otherwise — against any connection CARTO has registered. Two execution surfaces, matched across MCP and CLI:

Access-path routing. With the CARTO MCP server attached, run SQL through execute_query and execute_async_query — both available even on token-authenticated sessions. The dialect guidance and performance rules here apply identically on either path. Use the carto sql CLI when the server isn't attached or the SQL is scripted/CI. carto activity query (local DuckDB over downloaded activity data) is CLI-only — no MCP equivalent. Detection signals: `carto-basics/references/access-paths.md`.

When to use this skill

• The user wants to count rows, run an exploratory SELECT, or build a transformation.
• The user is debugging slow / failing SQL.
• The agent needs to materialize an intermediate table before authoring a map.
• The user wants an ad-hoc spatial join, buffer, or H3 aggregation.

Quick reference (CLI)

# Read query (returns rows; 1-min timeout)
carto sql query <connection> "SELECT * FROM dataset.table LIMIT 10"

# Long-running job (DDL/DML; polls to completion; no rows back)
carto sql job <connection> "CREATE TABLE my_ds.out AS SELECT ..."

# From file / piped
carto sql query <connection> --file query.sql
echo "SELECT 1" | carto sql query <connection>

--cache (CLI) switches to a GET with a 1-year cached response; use only for deterministic, small queries.

What's in this skill

Always-on guidance

• Always specify a connection. The connection name comes from explore_data (list_connections) / carto connections list, not the warehouse project ID.
• Prefer the async path for any query that might exceed 60 s. Sync reads have a hard 1-minute server-side timeout regardless of the user's patience.
• Don't `SELECT on warehouse tables blindly.** Spatial tables can be 100M+ rows; project columns and add LIMIT` for exploration.
• Dialect mismatch is the #1 source of confusion. ST_DWithin is ST_DWITHIN in Snowflake, ST_DWithin in BigQuery/PostGIS/Redshift. The per-dialect reference gives the canonical form.
• For activity-data analysis (who edited what, quota usage, login patterns), use carto activity query — CLI-only, DuckDB over downloaded data. See references/activity-queries.md.