Investigate a query
Read a query's SQL, warehouse context and execution profile, and find queries with the same shape.
Questions you can ask your agent:
- "Why is query 01b2c3d4-0000-1111-2222-333344445555 so slow?"
- "Was the warehouse busy when nightly_revenue_job's insert ran last night?"
- "Have we run anything shaped like this aggregate over analytics.orders_fact before?"
A typical chain: read the query with query_detail, read its profile with query_profile_detail, then look for the same shape elsewhere with search_similar_queries.
A query's processing_time is the day it belongs to. Pass it back when you have it; the tools look it up otherwise.
All sample responses use synthetic data and are shortened.
query_detail
query_detailReturns a query's text, cost, signature, warehouse, concurrency and column usage.
When to use: to read the SQL of a query id, or to see which columns it filters and joins on.
Parameters
| Parameter | Type | Required | Default | Meaning |
|---|---|---|---|---|
query_id | string | Yes | The query id. | |
processing_time | string | No | looked up | The query's day, as this tool returns it. |
query_offset | integer | No | 0 | For a query longer than 40,000 characters: where to start reading. |
Returns:
query: the text, at most 40,000 characters per call. A longer query also returnsquery_chars,query_offsetandquery_next_offset; call again withquery_offset = query_next_offsetuntil it is null.cost,query_signature,default_database,job_id("<dag> &&& <task>", or null).warehouse_name,warehouse_size,max_concurrencyandavg_concurrency(peak and average parallel queries on the warehouse while it ran). Null on Spark.execution_status: the engine's own value. A failed or cancelled query is costed like any other, but its profile is partial; don't size a saving on it.column_usage: one entry per table, column and usage type, from the query profile:SELECT,GROUPBY,ORDERBY,AGGREGATE,WHERE(with operator, value and wrapping functions) and joins (INNER_JOIN,LEFT_JOIN,FULL_JOIN,JOIN_UNSPECIFIED, with the join column). Empty for a query without a profile.next: when the query has a job and the server serves a job health card, the call that reads it.
Example
"Show me query 01b2c3d4-0000-1111-2222-333344445555. Was the warehouse busy?"
{
"query": "INSERT INTO analytics.orders_daily SELECT order_date, region, SUM(amount) FROM analytics.orders_fact GROUP BY 1, 2",
"cost": 18.42,
"query_signature": "nightly_revenue &&& build_orders_daily",
"job_id": "nightly_revenue &&& build_orders_daily",
"warehouse_name": "ETL_WH",
"warehouse_size": "Medium",
"max_concurrency": 12,
"avg_concurrency": 7.4,
"column_usage": [{"table_fqn": "ANALYTICS_DB.ANALYTICS.ORDERS_FACT", "column": "ORDER_DATE", "usage_type": "GROUPBY"}]
}query_profile_detail
query_profile_detailSnowflake only. Returns a query's execution profile as a compact text tree.
When to use: "why is this query slow?" Read the operators that carry the cost.
Parameters
| Parameter | Type | Required | Default | Meaning |
|---|---|---|---|---|
query_id | string | Yes | The query id. | |
processing_time | string | No | searches the last 30 days | The query's day, as query_detail returns it. |
Returns plain text, not JSON. The first line is a totals header (scan, spill, and the share of the query's cost the profile accounts for). Then one operator per line, depth-first:
{step}.{node}:{type} c={cost%} i={input_rows} o={output_rows} p={parent} partitions=S/T sc=<bytes> spl=<bytes> spr=<bytes> fcols=<cols> {attrs}c: the operator's own cost as a percent of the query's cost. Missing in a preparatory step: unknown, not zero.p: parents, comma-joined when several; missing for the root.partitions: scanned/total on scans; total?when unknown.sc,spl,spr: bytes scanned, spilled locally and spilled remotely.fcols: the predicate columns on a scan.
Subtrees under 1% of the cost collapse into one [...N nodes, tables: X, Y] line; scans, joins and filters over scans still print. The text is at most 50,000 characters. No profile returns {"error": ...}: the id may be unknown, a query inside a script, or on another day.
Example
"Why is query 01b2c3d4... slow?"
# totals (whole query, incl. collapsed nodes): scan=1.2TB spill=0B (local=0B remote=0B) attributed=97%
1.0:Insert c=2% o=720
1.1:Aggregate c=31% i=1.4B o=720 p=1.0
1.2:TableScan c=64% o=1.4B p=1.1 partitions=3650/3650 sc=1.2TB fcols=ORDER_DATE table=ANALYTICS.ORDERS_FACTsearch_similar_queries
search_similar_queriesSnowflake only. Finds historical query fragments similar to one input fragment.
When to use: you have a new or slow query and want to know whether its shape is a one-off or recurs, for example while reviewing new SQL.
Parameters
| Parameter | Type | Required | Default | Meaning |
|---|---|---|---|---|
sql_text | string | Yes | One fragment to search for, best as an indented operator tree (see below). Can't be empty. | |
limit | integer | No | 10 | Results to return. At least 1. |
start_time | string (ISO-8601) | No | no lower bound | Inclusive lower bound on the query day. |
end_time | string (ISO-8601) | No | no upper bound | Exclusive upper bound on the query day. Must be after start_time. |
exclude_job_ids | list of strings | No | none | Jobs whose fragments to leave out. |
The index is built from plan subtrees, not raw SQL. The best input is an indented operator tree:
Filter attrs={"filter_condition": "order_date >= ?"}
TableScan attrs={"table_name": "analytics.orders_fact", "columns": ["order_date", "amount"]}For new SQL without a profile, approximate this from the SQL: keep table names, columns, filters, joins, aggregations, windows and nesting. Raw SQL usually scores worse. Split a large query into fragments yourself and search one per call.
Returns at most one result per query signature (its closest fragment), best first: query_id, fragment_id, query_signature, similarity_score, processing_time and fragment_plan_text_preview. An empty list means no stored fragment cleared the noise floor.
This tool is present only where similarity search is enabled. Where it isn't, the tool is missing from the list or returns {"error": "similarity search not enabled"}.
Example
"Have we run anything like this aggregate over orders_fact before?"
[
{
"query_id": "01b2c3d4-...8888",
"fragment_id": "f2e3d4c5-2222-3333-4444-555566667777",
"query_signature": "weekly_finance &&& orders_rollup",
"similarity_score": 0.94,
"processing_time": "2026-05-07T00:00:00Z",
"fragment_plan_text_preview": "Aggregate attrs={\"functions\": \"sum(amount)\"}\n Filter ..."
}
]Updated 17 days ago