Execute a read-only SQL query against the C99 Snowflake database.
**⚠️ PREREQUISITE**: You MUST call the getQueryDatabaseSchema tool first to obtain the
database schema before composing any SQL queries. Do not guess or hallucinate table names, column
names, or function signatures.
# **⚠️ CRITICAL: FULLY QUALIFIED TABLE NAMES REQUIRED ⚠️**
- **ALWAYS use fully qualified names**: All table and view references in SQL queries MUST use the
fully qualified name format: `inst_channel99.c99_ai.table_name`. For example, use
`inst_channel99.c99_ai.ad_keyword` instead of just `ad_keyword`.
- **This applies to ALL table references**: JOINs, FROM clauses, and any other table references
MUST use the fully qualified name exactly as shown in the schema from the
getQueryDatabaseSchema tool.
- **Example**: `JOIN inst_channel99.c99_ai.company AS c ON c.company_id = v.company_id`
# **⚠️ CRITICAL LOOKUP USAGE INSTRUCTIONS ⚠️**
- **Prefer ID joins over name joins**: When possible, join on the ID column of the entity rather
than a name column. Use the resolveObjectIds tool to get the ID if you don't have it. For
example, instead of joining on the `channel_name` column, ask the resolveObjectIds tool
for the ID of the channel and join on `channel_id` instead.
# **⚠️ CRITICAL UDTF USAGE INSTRUCTIONS ⚠️**
- **UDTF Syntax:** All UDTFs **must** be called using the exact format
`FROM TABLE(UDTF_NAME(param_name => value, ...))` with valid arguments. **Never** use
`FROM UDTF_NAME(...)` or `FROM UDTF_NAME`.
- **Named Arguments Only:** Always call UDTFs with named arguments (e.g.
`start_date => ...`). Do not use positional arguments, because parameter order may change
and can silently return incorrect metrics.
- **Casting Requirement:** All literal arguments (strings, timestamps, NULLs) **must** be
explicitly cast using the `::` operator. (e.g. `'2024-01-01'::TIMESTAMP_NTZ`,
`NULL::STRING`, `'campaign'::STRING`, etc.).
- **Alias Naming Requirement:** Use safe SQL aliases with letters, numbers, and underscores only
(e.g. `pipeline_per_dollar`). Do not use special characters like `$` in unquoted aliases.
- **JOIN Requirement:** Join dimension tables when needed to return human-readable names. If the join
is only for enrichment (labels/names) and should not change which base rows are returned, use
`LEFT JOIN`. Use `INNER JOIN` only when the user explicitly asks to include matched records only.
- **CTE-first UDTF pattern:** When UDTF results need to be combined with dimension tables or other
query results, execute the UDTF independently inside a CTE first. Then join the CTE to the required
dimension tables in the outer query.
- **Never use LATERAL or correlated UDTFs:** Never call a UDTF inside a `LATERAL` join,
correlated subquery, or once per row of another table. Never pass an outer-query column into a UDTF
argument. Snowflake can reject these queries with
`Unsupported subquery type cannot be evaluated`.
- **Use `group_by` only when supported by the schema:** If the UDTF schema defines a `group_by`
parameter and returns `grouping_id`, use the supported dimension to request the breakdown in one
UDTF call. Join `grouping_id` from the UDTF CTE to the corresponding dimension table ID in the
outer query. Never invent `group_by`, `grouping_id`, or a dimension value absent from the schema.
- **Choose `group_by` from the requested output grain:** Select the `group_by` dimension from
the entity the user asks to report “by” or “for each,” not from an entity used only as a filter.
For example, for metrics for each audience filtered to a particular vendor, use
`group_by => 'audience'::STRING` and pass the vendor ID through `filterlist_vendor_id`.
- **Join only the selected grouping dimension:** Join `grouping_id` only to the ID column for the
dimension selected by `group_by`. If `group_by` is `'audience'`, join `grouping_id` to the
audience ID. If a filter dimension differs from the selected `group_by` dimension, never join
`grouping_id` to that filter dimension. Never use a `CROSS JOIN` to associate grouped metrics
with dimension records.
- **Unsupported breakdowns:** If the requested dimension is not supported by the UDTF schema, do
not use `LATERAL`, a correlated subquery, or per-row UDTF calls as a workaround. Call the UDTF once
at the broadest supported scope and filter or aggregate its output afterward when possible. If the
breakdown cannot be derived from the available output, explain that it is not supported by the
available schema.
- **Temporal breakdowns:** `group_by` controls only the business-entity dimensions explicitly
supported by the UDTF schema. It does not implicitly support grouping by day, week, or month. Never
generate date, week, or month rows and invoke a UDTF once for each period. Never pass
boundaries generated for individual period rows or outer-query date columns into UDTF arguments.
A single overall query range for one UDTF call is allowed, including start_date/end_date values
calculated with DATE_TRUNC or CURRENT_DATE. For a temporal breakdown, use
an authorized schema object that returns both the requested metrics and a date column. If none is
available, explain that the temporal breakdown cannot be calculated from the exposed schema.
- **ID Requirement:** All UDTF parameters that are foreign keys MUST be the ID of the entity
returned by the resolveObjectIds tool. NEVER GUESS THE ID, NEVER ADD A PLACEHOLDER VALUE,
and NEVER USE A NAME IN PLACE OF THE ID. Call the resolveObjectIds tool only when you have
one or more concrete entities that need ID resolution for the query.
- **Use Columns Returned by the UDTF**: Do not invent columns. For example, if the UDTF returns a
column called `sum_spend`, do not invent a column called `spend`.
- **Required Parameters:** All UDTF parameters that are required MUST be included in the query.
- **Default Value:** If a UDTF parameter has a `default`, use this value in the query if no value
is provided for the parameter. If parameter documentation explicitly says null is allowed for "no
filter", follow that parameter documentation. If a default value is provided, let the user know what
the default value is.
- **Pay attention to type**: For each of the following types, pass the appropriate value:
- "STRING containing JSON array string": Pass a JSON array formatted string cast as `::STRING`
(e.g. `'["value1","value2"]'::STRING`).
- "STRING": Pass a string (e.g. `'value'::STRING`).
- "TIMESTAMP_NTZ": Pass a timestamp (e.g. `'2024-01-01'::TIMESTAMP_NTZ`).
- **`grouping_id` Note:** The `grouping_id` column contains different IDs based on the
`group_by` parameter value.
Example:
```sql
WITH visit_metrics AS (
SELECT *
FROM TABLE(inst_channel99.c99_ai.visits_summary(
start_date => (CURRENT_DATE() - INTERVAL '90 days')::TIMESTAMP_NTZ,
end_date => CURRENT_DATE()::TIMESTAMP_NTZ,
group_by => 'campaign'::STRING,
filterlist_channel_id => NULL::STRING,
filterlist_vendor_id => NULL::STRING,
filterlist_ad_group_id => NULL::STRING,
filterlist_audience_id => '["<TAM_AUDIENCE_ID>"]'::STRING,
filterlist_opportunity_id => NULL::STRING
))
)
SELECT vm.*, ac.ad_campaign_name
FROM visit_metrics AS vm
LEFT JOIN inst_channel99.c99_ai.ad_campaign AS ac
ON ac.ad_campaign_id = vm.grouping_id
```
When combining a UDTF with dimension tables, follow this CTE-first structure unless no join or
additional query composition is required.
You must use Snowflake SQL syntax or the query will fail to execute.
Format results in a clear, readable way for the user. If there are many rows, consider showing a
summary.
**Chart visualization:** When query results are suitable for visualization (time series, categories, comparisons, distributions), always include a chartSpec argument. The client renders an interactive chart from it alongside your response.
runReadOnlySqlQuery
View in the ChatGPT registry
Agent DiscoverabilityChatGPT Plugin Discovery Score
