Recommendation tools

Single Origin continuously analyzes your query history and surfaces optimization recommendations — query rewrites, materialized views, cluster keys, query consolidation. These six 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, act on it, and record a new one of its own.

For the recommendations attached to a specific table, see get_table_recommendations.

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. Pass types to restrict by recommendation type (SINGLE_QUERY, SIMILAR_QUERY_GROUP, CLUSTER_KEY_RECOMMENDATION, MV_REWRITE_RECOMMENDATION, RESOURCE_TUNING); when omitted, all types are returned.

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 one of SINGLE_QUERY (one query to fix), SIMILAR_QUERY_GROUP (a cluster of structurally similar queries fixed together), CLUSTER_KEY_RECOMMENDATION, MV_REWRITE_RECOMMENDATION, or RESOURCE_TUNING. 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"
}

create_recommendation

Creates a new recommendation and links it to the queries it applies to. Use it to record an optimization your agent found itself, so it lands in the same list, review flow, and cost rollups as the ones Single Origin generates.

The UUID is generated server-side and returned; you cannot supply one. Writing under an existing id would create a new revision of that recommendation rather than a new one — to revise something that already exists, use update_recommendation.

Required

• recommendation_type — one of SIMILAR_QUERY_GROUP, CLUSTER_KEY_RECOMMENDATION, MV_REWRITE_RECOMMENDATION, SINGLE_QUERY, RESOURCE_TUNING.
• title — one-line summary.
• background — the context: what this table or workload is and why it was examined.
• target_inefficiency — what is wrong today.
• recommendation — the change being proposed.

Optional

• status — one of ACTIVE, PENDING_REFRESH, INVALIDATED, IGNORED, IN_REVIEW, APPLIED, PENDING_RELEASE. Defaults to PENDING_RELEASE, so a new recommendation is not customer-visible until it is released.
• query_ids — the queries this applies to. Each becomes a recommendation_items row, which is what get_recommendation_items and the cost rollups read. Duplicates are ignored.
• processing_time — the daily partition it belongs to (ISO-8601, usually a plain date). Defaults to the server's configured processing time.
• target_inefficiency_query_snippet / recommendation_query_snippet — the before/after SQL.
• estimated_savings_summary — prose explanation of the savings.
• daily_estimated_savings — stored verbatim, in the same raw units update_recommendation writes; the display cost multiplier is applied on read, not on write.
• attributes — JSONB, per-type metadata: job_id (required for SINGLE_QUERY — get_recommendation_for_job looks the recommendation up by it), savings_pct (a fraction, e.g. 0.15), optimization_type, and signature_key (a JSON-encoded list of query signatures).

Sample call

"Record a cluster key recommendation for analytics.orders_fact on (order_date, region), covering those three queries."

Sample response

{
  "recommendation_id": "9c2b1f7e-4a3d-4e10-9f02-1ab2c3d4e5f6",
  "processing_time": "2026-06-05",
  "type": "CLUSTER_KEY_RECOMMENDATION",
  "status": "PENDING_RELEASE",
  "items_linked": 3
}