Recommendation tools

Single Origin continuously analyzes your query history and surfaces optimization recommendations — query rewrites, materialized views, cluster keys, query consolidation. These five tools let your agent browse the active recommendation list, drill into any one (or look up the recommendation attached to a specific job), see its per-query impact, and act on it.

All sample responses below use synthetic data.

list_recommendations

Lists recommendations sorted by estimated daily savings, 10 per page. Returns the latest version of each one; deleted recommendations are excluded. Defaults to ACTIVE and IN_REVIEW recommendations — your agent can pass other statuses (APPLIED, INVALIDATED, IGNORED, PENDING_REFRESH, PENDING_RELEASE) when needed.

Freshness: results are valid for ~1 hour. For a longer-running session, re-call the tool rather than reusing an old list.

Sample call

"Show me the top recommendations from last week."

Sample response

[
  {
    "id": "9c2b1f7e-4a3d-4e10-9f02-1ab2c3d4e5f6",
    "title": "Materialize daily aggregate over orders_fact",
    "type": "SIMILAR_QUERY_GROUP",
    "status": "ACTIVE",
    "processing_time": "2026-05-08T00:00:00Z",
    "estimated_daily_savings": 4820.5,
    "query_signatures": "[\"analytics_daily &&& orders_rollup||a1b2\", \"adhoc &&& c3d4\"]"
  },
  {
    "id": "1de83a4c-2b5a-4f01-8b39-7f6e5d4c3b2a",
    "title": "Cluster events_raw by (event_date, tenant_id)",
    "type": "SINGLE_QUERY",
    "status": "ACTIVE",
    "processing_time": "2026-05-08T00:00:00Z",
    "estimated_daily_savings": 1980.2,
    "query_signatures": "[\"events_etl &&& f7e8\"]"
  }
]

type is either SINGLE_QUERY (one query to fix) or SIMILAR_QUERY_GROUP (a cluster of structurally similar queries fixed together). estimated_daily_savings is in dollars.

get_recommendation

Returns the full payload for a single recommendation: title, background, the inefficiency it targets, the proposed change, and projected savings.

Sample call

"Walk me through recommendation 9c2b1f7e."

Sample response

{
  "id": "9c2b1f7e-4a3d-4e10-9f02-1ab2c3d4e5f6",
  "processing_time": "2026-05-08T00:00:00Z",
  "type": "SIMILAR_QUERY_GROUP",
  "title": "Materialize daily aggregate over orders_fact",
  "background": "37 queries scan analytics.orders_fact and aggregate by (order_date, region). Aggregate output is ~1/2000 of the raw fact scan.",
  "target_inefficiency": "Full table scan of orders_fact (1.4B rows) for an aggregate that returns ~700K rows.",
  "target_inefficiency_query_snippet": "SELECT order_date, region, SUM(amount) FROM analytics.orders_fact GROUP BY 1, 2;",
  "recommendation": "Create a materialized view that pre-aggregates orders_fact by (order_date, region) and rewrite consumers to read from it.",
  "recommendation_query_snippet": "CREATE MATERIALIZED VIEW analytics.orders_daily_agg AS\nSELECT order_date, region, SUM(amount) AS total_amount, COUNT(*) AS order_count\nFROM analytics.orders_fact\nGROUP BY 1, 2;",
  "estimated_savings_summary": "Eliminates ~4820 seconds/day of wall time across 37 queries.",
  "estimated_daily_savings": 4820.5,
  "query_signatures": "[\"analytics_daily &&& orders_rollup||a1b2\"]"
}

get_recommendation_for_job

The reverse lookup of get_recommendation: given a job_id, returns the latest non-deleted recommendation attached to that job (SINGLE_QUERY recommendations reference the job in their attributes). Use it when you have a job and want to know whether an optimization recommendation already exists for it. Returns the recommendation's id, type, status, title, processing_time, and is_dismissed — pass the returned id to get_recommendation for the full payload, or to update_recommendation to change its status. Returns an {error} object when no recommendation exists for the job.

Sample call

"Is there a recommendation for job events_etl_20260508?"

Sample response

{
  "id": "1de83a4c-2b5a-4f01-8b39-7f6e5d4c3b2a",
  "type": "SINGLE_QUERY",
  "status": "ACTIVE",
  "title": "Cluster events_raw by (event_date, tenant_id)",
  "processing_time": "2026-05-08T00:00:00Z",
  "is_dismissed": false
}

get_recommendation_items

Returns every query targeted by a recommendation, sorted by cost descending. cost is null when no cost data is available for the query.

Sample call

"Which queries does recommendation 9c2b1f7e target, and what does each cost?"

Sample response

[
  {
    "processing_time": "2026-05-08T00:00:00Z",
    "recommendation_type": "SIMILAR_QUERY_GROUP",
    "recommendation_id": "9c2b1f7e-4a3d-4e10-9f02-1ab2c3d4e5f6",
    "query_id": "01HXY8F3A4B5C6D7E8F9",
    "cost": 18.42
  },
  {
    "processing_time": "2026-05-08T00:00:00Z",
    "recommendation_type": "SIMILAR_QUERY_GROUP",
    "recommendation_id": "9c2b1f7e-4a3d-4e10-9f02-1ab2c3d4e5f6",
    "query_id": "01HXYA02B1C2D3E4F5G6",
    "cost": 22.10
  }
]

update_recommendation

Updates fields on an existing recommendation — status (APPLIED, INVALIDATED, IGNORED, etc.), title, savings estimates, or any of the recommendation's text fields. Only the fields your agent passes are written; everything else stays unchanged. Write tool.

Two required arguments:

• recommendation_id — which recommendation to update.
• processing_time — must match the recommendation's existing processing_time (fetch it with get_recommendation first).

Optional savings overrides: daily_estimated_savings, savings_pct (a fraction, e.g. 0.15, for SINGLE_QUERY recommendations), and savings_pct_by_signature (per-signature fractions keyed by query signature, for SIMILAR_QUERY_GROUP recommendations).

Sample call

"Mark recommendation 9c2b1f7e... as applied."

Sample response

{
  "updated": true,
  "recommendation_id": "9c2b1f7e-4a3d-4e10-9f02-1ab2c3d4e5f6"
}