MC SQL gotchas: what actually fires in production

Marketing Cloud SQL looks like T-SQL. That's the trap. The gaps between looks like and behaves like are where queries silently corrupt data, time out at row 9 of 10, or rebuild a Data Extension into an empty husk and never tell anyone.

The list below is what we wish someone had handed us before our first production rebuild. Each item is anchored to the kind of thing that actually fires: a Send Definition that empties, a Journey that doesn't enter anyone, a 1.65M-row rebuild that finishes in 31 minutes and overwrites half the audience. Where Salesforce's docs are right we don't repeat them. Where they're wrong we say so and show the pattern that survived.

The gotchas

1. No transactions. No rollback. INSERT INTO is committed line by line.

The mental model that breaks here is the one that says "if the query fails halfway, the destination is untouched." It isn't. A SQL Activity that runs for 12 minutes, writes 800k rows, then hits a type error on row 800,001 leaves you with 800k rows in the destination DE and a failed Automation step. There is no ROLLBACK.

-- This does not give you "all or nothing" semantics.
-- If the SELECT errors mid-stream, the destination keeps the rows
-- already written before the error.
INSERT INTO send_audience
SELECT SubscriberKey, EmailAddress, LoyaltyTier
FROM master_subscribers
WHERE Status = 'Active';

The pattern: write to a de_stg_ staging DE, then promote with a second Activity that truncates and rewrites the production DE only when staging finished cleanly. Two Activities, two rows in the run log, recoverable.

2. SQL Activities only INSERT. UPDATE, MERGE, UPSERT live elsewhere.

You cannot write UPDATE send_audience SET LoyaltyTier = 'gold' WHERE … in a SQL Activity. The SQL surface in MC Automation Studio is INSERT INTO <destination> SELECT …. Updates and upserts happen via the destination DE's primary key + Overwrite/Update Type setting on the Activity.

-- WRONG — this throws "INSERT INTO ... SELECT is the only supported syntax"
UPDATE send_audience
SET LoyaltyTier = 'gold'
WHERE LastPurchase >= DATEADD(day, -30, GETDATE());

-- RIGHT — re-emit the row with the new value, let the destination
-- DE's primary key (SubscriberKey) merge it via "Update" mode
INSERT INTO send_audience
SELECT
  SubscriberKey,
  EmailAddress,
  'gold' AS LoyaltyTier
FROM send_audience
WHERE LastPurchase >= DATEADD(day, -30, GETDATE());

The decision lives on the destination DE, not in the SQL. The SQL Activity has three modes — Overwrite, Append, Update. Pick before you write the query.

3. The 30-minute hard timeout is real. It is silent on partial writes.

A SQL Activity that runs 30 minutes and 1 second is killed. The Automation step shows "Failed" with Query timed out. Whatever the INSERT had already written to the destination is still there. Run the same Automation again and you may get duplicate rows, or you may get a destination DE that is half-old, half-new, depending on the Activity mode.

4. CTEs and window functions are edition-dependent. Don't rely on them.

WITH cte AS (…) SELECT … FROM cte works on most modern Enterprise tenants. It does not work on every tenant. ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) is in the same bucket — supported, until your tenant's underlying engine version says otherwise after a Salesforce platform update.

-- AT RISK — works today on your tenant, may not survive a backend update
WITH ranked AS (
  SELECT
    SubscriberKey,
    LastPurchase,
    ROW_NUMBER() OVER (PARTITION BY EmailAddress ORDER BY LastPurchase DESC) AS rn
  FROM master_subscribers
)
SELECT SubscriberKey
FROM ranked
WHERE rn = 1;

-- DURABLE — two-step staging, no window functions, no CTEs
INSERT INTO de_stg_max_per_email
SELECT EmailAddress, MAX(LastPurchase) AS MaxPurchase
FROM master_subscribers
GROUP BY EmailAddress;

INSERT INTO de_stg_dedup_subs
SELECT m.SubscriberKey
FROM master_subscribers m
INNER JOIN de_stg_max_per_email s
  ON m.EmailAddress = s.EmailAddress
  AND m.LastPurchase = s.MaxPurchase;

The two-step version is uglier. It also runs the same way next year, on the next tenant, after the next platform update.

5. NULL is three-valued. Field length truncates silently.

WHERE LoyaltyTier = NULL returns zero rows even when LoyaltyTier is NULL on millions of rows, because NULL = NULL is unknown, not true. Always IS NULL.

The silent one is field length. Insert a 75-character email into a 50-character VARCHAR destination column and MC truncates it to 50, no error, no warning. The truncated email may now collide with another subscriber's email and merge them under the same primary key.

-- BUG — silently misses every NULL row
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier = NULL;

-- CORRECT
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier IS NULL;

The defense for truncation is a manifest: every staging DE has explicit column lengths matching the source, every destination DE is sized at least as wide as staging. Run SELECT MAX(LEN(EmailAddress)) against the source before sizing.

6. System Data Views are not as persistent as the docs say.

_Subscribers, _Job, _Sent, _Open, _Click — Salesforce documents these as the canonical engagement tables. They feel persistent. They are not. A Send Definition rotation, a Send Classification change, or a tenant update can empty or shift the join keys silently. A Journey that reads engagement state from _Sent and never enters anyone is the failure shape.

7. WHERE 1=2 does not "clear" a Data Extension the way you'd expect.

The folklore pattern for resetting a DE is INSERT INTO target SELECT * FROM source WHERE 1=2 with the Activity in Overwrite mode. It works on some DEs, fails on others, depending on the destination DE's retention setting and primary-key configuration. We have seen this pattern leave a DE half-cleared — the rows the Overwrite intended to drop, intact; the rows the new SELECT added, present. Result: a DE that grows over time despite running a "clean" rebuild every day.

The reliable reset is explicit: clear the DE via the API or the UI's "Clear Data" action, then INSERT the new rows. Don't depend on Overwrite to do it implicitly.

8. Date arithmetic crosses year boundaries badly.

DATEADD(day, -30, GETDATE()) is fine. DATEADD(month, -3, GETDATE()) on April 30 returns January 30 — correct. On May 31 it returns February 28 (leap year) or February 29 — depending on the engine's interpretation, this is sometimes off by a day for the same query run two days apart.

The pattern: never do month-based DATEADD on a column that drives Send filtering. Use day-count windows (e.g. DATEADD(day, -90, GETDATE())) and document the window in a comment. If the business rule is "last 3 months," translate it to a fixed day count once at design time, not at query time.

9. SubscriberKey is a string. Cast before you join across DEs.

Even when SubscriberKey looks like an integer in the source — and even when CRM stores it as INT — Marketing Cloud stores it as a string identifier in the Subscriber records. A join ON sub.SubscriberKey = ext.UserId where ext.UserId is INT will silently miss rows where leading zeros differ, or where one side trims trailing whitespace and the other does not.

-- AT RISK — implicit type coercion, silent mismatches
SELECT s.SubscriberKey, e.LoyaltyTier
FROM _Subscribers s
INNER JOIN ext_loyalty e
  ON s.SubscriberKey = e.UserId;

-- SAFE — explicit cast on both sides, plus a defensive TRIM
SELECT s.SubscriberKey, e.LoyaltyTier
FROM _Subscribers s
INNER JOIN ext_loyalty e
  ON LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(e.UserId AS NVARCHAR(255))));

The first version is shorter. The second version is the one that matches the row count you expect.

10. ORDER BY inside INSERT INTO ... SELECT is unreliable.

Strict T-SQL says ORDER BY in a subselect or in INSERT … SELECT has no defined effect on the row order in the destination — only the optimizer's whim. Marketing Cloud's SQL Activity treats it the same way: the rows in the destination DE come out in whatever order the engine chose, regardless of the ORDER BY you wrote.

If row order matters — say, for a "first 100 by date" send — order is enforced at read time via the destination DE's primary key + retrieval, or via a sequence column you populate explicitly:

-- WRONG — assumes the destination preserves SELECT's ORDER BY
INSERT INTO top_100_sends
SELECT TOP 100 SubscriberKey, OrderDate
FROM master_subscribers
ORDER BY OrderDate DESC;

-- RIGHT — populate an explicit rank column the destination can sort on
INSERT INTO top_100_sends
SELECT
  SubscriberKey,
  OrderDate,
  ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS Rank
FROM master_subscribers;
-- (and accept the window-function risk from gotcha #4 — or stage and join)

If your data model relies on row order in the destination, the model is the bug. Fix the model before the order.

Closing

These ten are not theoretical. They are the kinds of things that break a Send the morning after a Salesforce platform update, or rebuild a 1.65M-row Data Extension into a half-empty one because the query ran 31 minutes instead of 29. Cleon writes MC SQL with the assumption that any of them can fire on the next run — and the queries we ship are the ones that survive that assumption.

If you find one missing — a gotcha that bit your team and isn't here — write to hello@wearecleon.com. We add it, with credit.