carto-query-datawarehouse

Run SQL — spatial or otherwise — against any connection CARTO has registered. The CLI exposes two surfaces:

• carto sql query — SELECT queries that return rows. Has a 1-minute timeout. Optional client-side caching.
• carto sql job — DDL/DML jobs (CREATE TABLE AS SELECT, UPDATE, INSERT). No timeout; polls until done; returns no rows.

Plus a sibling for usage analytics:

• carto activity query — DuckDB-backed SQL over downloaded CARTO activity data. Local execution, separate from warehouse SQL.

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 to run an ad-hoc spatial join, buffer, or H3 aggregation.

Quick reference

# 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
carto sql query <connection> --file query.sql

# Piped
echo "SELECT 1" | carto sql query <connection>

--cache switches to GET with a cached response (1 year, 1 min timeout). Use only for queries that are deterministic and small enough for a URL.

What’s in this skill

Always-on guidance

• Always specify a connection. <connection> in sql query <connection> ... is the connection name from connections list, not the warehouse project ID.
• Use --json when an agent will parse the output. Default text output is for humans.
• Prefer sql job for any query that might exceed 60 s. sql query has 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; always project columns and add LIMIT for exploration.
• Dialect mismatch is the #1 source of confusion. ST_DWithin exists in PostGIS and Redshift, but is ST_DWITHIN in Snowflake and lives under ST_DWithin in BigQuery’s bigquery-public-data.geo_us_boundaries style. The reference per dialect explains the canonical form.
• For activity-data analysis (who edited what, quota usage, login patterns), use activity query — it runs DuckDB SQL locally over downloaded data. See references/activity-queries.md.