Writing AMC SQL with AI: provide the schema and check the result
In short: AI models write usable AMC SQL if you give them the schema of the tables, and otherwise invent fields and functions. Check every result: field names against the schema reference, functions against Amazon's list, `user_id` never in the final SELECT. Test in the sandbox first, then in the instance.
The workflow
- Provide the schema: copy the field list of the tables involved from the schema reference into the prompt, including thresholds. Without a schema the model guesses field names.
- Provide the rules: final SELECT without VERY_HIGH fields such as
user_id(only in CTEs or insideCOUNT(DISTINCT …)), no literal filters on fields with a high threshold. - Check the result (see below), run it in the sandbox, then in the instance.
Typical missteps and how to spot them
| Misstep | How to check |
|---|---|
Invented function (for example SIMILARITY or ARRAY_JOIN) | Check against Amazon's function list; it has ARRAY_TO_STRING, for example |
| Invented or misspelled field | Look up each name in the schema reference of the table |
user_id in the final SELECT | AMC rejects it; user_id only in a CTE or in COUNT(DISTINCT user_id) |
| Multi-line SQL in an API request | Make sqlQuery single-line, without tabs and line breaks |
| Too many similar variants in a row | Combine queries; AMC rejects executions on overlapping data sources |
Privacy when prompting
Give the model the schema and synthetic or sandbox examples, not result rows with real campaign or customer data. In the workflow documentation Amazon explicitly points out never to put information about individuals into descriptions or parameter values. Which data an external provider may see is for your data protection office to decide.