AMC SQL: Where It Stops Behaving Like SQL

Amazon Marketing Cloud (AMC) SQL is a restricted dialect running inside a privacy clean room, so two things are true at once: the query you already know how to write will be rejected, and the query that gets accepted can still return nothing. SELECT *, ORDER BY, LIMIT, RIGHT JOIN and GETDATE() are all unsupported, and any output row describing fewer than 100 distinct shoppers gets dropped before you see it. Our agent checks both rule sets through the Amazon Ads MCP, grounded by Amazon Agent Atlas, before it writes a line.

I learned the second half the hard way. An analyst pinged me with a query and a shrug: "It runs. It returns four rows. There should be ninety." Nothing was broken. She had grouped purchases by postal_code, and AMC had silently withheld every postal code with fewer than 100 shoppers behind it. The SQL was correct, the result was censored, and nothing in the output said so.

Why not just ask ChatGPT or Claude to write the query? A plain chat hits the same three walls on any Amazon job. It has no access to your data, so it cannot see which columns your instance actually exposes or how many users sit behind a postal code. It has no way to take action, so it cannot run the workflow or read the result; the most it can do is hand you text to paste into the query editor yourself. And it runs on generic knowledge, not Amazon's, so it writes ANSI SQL habits like SELECT * and ORDER BY that AMC rejects outright. You get disconnected, generic, manual work that fails on submit or, worse, returns a filtered answer you trust. Each claim below hits one of those walls, and shows how the Amazon Ads MCP (your instance, plus the tools to run against it), Skills (the repeatable workflow), and Atlas grounding (the private rule book) get past it.

AMC SQL is a documented dialect, not the SQL you already know

The first thing the agent does is stop treating this as generic SQL. Atlas retrieves the Introduction to AMC SQL playbook and the AMC SQL functions catalog from the amazon_ads corpus, which together define the grammar, the data types, the logical operators, and the exact function list AMC accepts.

What is supported is narrower than it looks: SELECT <column> FROM <table>, WHERE, GROUP BY, aggregates like SUM, AVG, COUNT and COUNT DISTINCT, conditionals like CASE, IF, IN and NOT IN. What is not supported is documented just as precisely in Limitations and unsupported functions: no SELECT *, no LIMIT, no RIGHT JOIN, no GETDATE().

So a working query names its columns and nothing more:

SELECT
  campaign_id,
  campaign,
  campaign_start_date,
  campaign_end_date,
  SUM(impressions) AS impressions
FROM
  dsp_impressions
GROUP BY
  1,
  2,
  3,
  4

A valid query can return nothing, and AMC will not tell you

This is the rule that costs people days. AMC assigns every column an aggregation threshold, and rows that fall below it are removed from your output rather than flagged. The classifications run NONE, LOW, MEDIUM, HIGH, VERY_HIGH and INTERNAL. In practice you need 2 distinct users behind a row for a LOW column and 100 distinct users for a MEDIUM or HIGH one.

postal_code is a HIGH column, which makes it the clearest demonstration. This is the query my analyst ran:

SELECT
  postal_code,
  SUM(purchases) AS purchases,
  COUNT(DISTINCT user_id) AS customers
FROM
  amazon_attributed_events_by_conversion_time
GROUP BY
  1

In Amazon's own worked example of this pattern, 91 rows come back complete because each postal code had at least 100 users behind it. Four more rows come back with the postal_code value blank and the purchases still showing, because those postal codes had fewer than 100 shoppers. Nothing errors. The rows just quietly stop identifying themselves.

The agent knows the fix before you hit it, because the Data aggregation thresholds in AMC playbook lists the remedies: extend the time window, broaden a restrictive filter, or group by a less granular dimension. A week instead of a day. A region instead of a postal code. More distinct users per row, more rows that clear the floor.

user_id is the column you build on and never select

user_id is classified VERY_HIGH, the strictest level. Values from a VERY_HIGH column can never appear in workflow output, and no threshold will reveal them, because a single row would expose one shopper's events.

That does not make the column unusable. You can join on it, filter on it relationally, and aggregate it, which is why COUNT(DISTINCT user_id) appears in almost every AMC query worth writing, including the threshold example above. What you cannot do is put it in the final SELECT, and you cannot filter it against literal values. WHERE user_id IN (111,222) is rejected, because picking specific IDs out of a clean room is precisely what the classification exists to prevent.

The distinction is small in syntax and total in effect: user_id is how you count customers, never how you list them.

Sorting and row limits happen after the query, not inside it

ORDER BY is not a supported top-level clause in AMC, and neither is LIMIT. This surprises people more than the threshold rules, because sorting feels like part of asking the question rather than part of reading the answer.

There is exactly one place ORDER BY is legal: inside a PARTITION BY clause, where it orders rows within a window function rather than the result set. For everything else, you sort the downloaded output file. Amazon's own instructional queries carry that note inline as a pro tip, which is a fair signal of how often it trips people up.

The practical consequence for an agent workflow is that ranking is a post-processing step, not a query clause. The query returns the aggregate; the Amazon Agent Data layer sorts, formats and routes it.

What happens next

Once the dialect rules and the threshold rules are both loaded from Atlas, writing AMC SQL stops being trial and error and becomes a check-then-write loop. The agent reads the function catalog, checks the classification of every column you want to group by, warns you when a grain will not clear 100 users, and only then composes the query. It runs the workflow through the Amazon Ads MCP and returns the result rather than the SQL, so the failure modes above never reach you.

That loop is worth saving. Turned into a reusable Skill it becomes the front door to every AMC question your team asks, and it composes with the workflows you already run through the Selling Partner MCP and the rest of the Amazon Agent Data layer. The same pattern is what turns a one-off analysis into something scheduled, as in the AMC agent workflows guide.

The dialect is small enough to learn in an afternoon. The thresholds are what quietly cost you a week, and they are invisible in the output.

Next in this series: your first real AMC queries, where the tables live and how to scope one to your own campaigns.