Gotchas de MC SQL: lo que realmente rompe en producción

El SQL de Marketing Cloud parece T-SQL. Esa es la trampa. Los huecos entre parece y se comporta son donde las queries corrompen datos en silencio, hacen timeout en la fila 9 de 10, o reconstruyen un Data Extension dejándolo como cáscara vacía sin avisarle a nadie.

La lista de abajo es lo que nos hubiese gustado tener antes de nuestra primera reconstrucción en producción. Cada item está anclado al tipo de cosa que de verdad rompe: una Send Definition que se vacía, un Journey que no entra a nadie, un rebuild de 1,65M de filas que termina a los 31 minutos y pisa medio audience. Donde la doc de Salesforce está bien no la repetimos. Donde está mal lo decimos y mostramos el patrón que sobrevivió.

Los gotchas

1. No hay transacciones. No hay rollback. INSERT INTO se commitea fila por fila.

El modelo mental que rompe acá es el que dice "si la query falla a la mitad, el destino queda intacto". No queda. Una SQL Activity que corre 12 minutos, escribe 800k filas y después tira un error de tipo en la fila 800.001 te deja con 800k filas en el DE de destino y un paso de Automation en Failed. No hay ROLLBACK.

-- Esto no te da semántica "todo o nada".
-- Si el SELECT explota a mitad de stream, el destino se queda con
-- las filas que ya escribió antes del error.
INSERT INTO send_audience
SELECT SubscriberKey, EmailAddress, LoyaltyTier
FROM master_subscribers
WHERE Status = 'Active';

El patrón: escribí a un DE de staging de_stg_, después promové con una segunda Activity que truncate y reescriba el DE de producción solo cuando el staging terminó limpio. Dos Activities, dos filas en el log de corrida, recuperable.

2. Las SQL Activities solo hacen INSERT. UPDATE, MERGE, UPSERT viven en otro lado.

No podés escribir UPDATE send_audience SET LoyaltyTier = 'gold' WHERE … en una SQL Activity. La superficie SQL en Automation Studio es INSERT INTO <destino> SELECT …. Los updates y los upserts pasan por la primary key del DE de destino + el Overwrite/Update Type configurado en la Activity.

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

-- BIEN — re-emití la fila con el valor nuevo, dejá que la primary
-- key del DE de destino (SubscriberKey) la mergee vía modo "Update"
INSERT INTO send_audience
SELECT
  SubscriberKey,
  EmailAddress,
  'gold' AS LoyaltyTier
FROM send_audience
WHERE LastPurchase >= DATEADD(day, -30, GETDATE());

La decisión vive en el DE de destino, no en el SQL. La SQL Activity tiene tres modos — Overwrite, Append, Update. Elegí antes de escribir la query.

3. El timeout duro de 30 minutos es real. Y es silencioso sobre lo que ya escribió.

Una SQL Activity que corre 30 minutos y un segundo se mata. El paso de Automation muestra "Failed" con Query timed out. Lo que el INSERT ya había escrito en el destino sigue ahí. Corré la misma Automation otra vez y podés terminar con filas duplicadas, o con un DE de destino que está mitad-viejo, mitad-nuevo, dependiendo del modo de la Activity.

4. Las CTEs y las window functions dependen de la edición. No te apoyes en ellas.

WITH cte AS (…) SELECT … FROM cte funciona en la mayoría de los tenants Enterprise modernos. No funciona en todos. ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) está en el mismo balde — soportado, hasta que la versión del motor abajo de tu tenant cambia después de un update de plataforma de Salesforce.

-- EN RIESGO — funciona hoy en tu tenant, puede no sobrevivir un update del backend
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 — staging de dos pasos, sin window functions, sin 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;

La versión de dos pasos es más fea. También corre igual el año que viene, en el próximo tenant, después del próximo update de plataforma.

5. NULL es de tres valores. La longitud de campo trunca en silencio.

WHERE LoyaltyTier = NULL devuelve cero filas incluso cuando LoyaltyTier es NULL en millones de filas, porque NULL = NULL es desconocido, no verdadero. Siempre IS NULL.

El silencioso es el largo de campo. Insertá un email de 75 caracteres en una columna de destino VARCHAR(50) y MC lo trunca a 50, sin error, sin warning. El email truncado puede colisionar con el email de otro subscriber y mergearlos bajo la misma primary key.

-- BUG — saltea silenciosamente cada fila NULL
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier = NULL;

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

La defensa contra la truncación es un manifest: cada DE de staging tiene largos de columna explícitos que matchean el origen, cada DE de destino está dimensionado al menos tan ancho como staging. Corré SELECT MAX(LEN(EmailAddress)) contra el origen antes de dimensionar.

6. Las System Data Views no son tan persistentes como la doc dice.

_Subscribers, _Job, _Sent, _Open, _Click — Salesforce las documenta como las tablas canónicas de engagement. Se sienten persistentes. No lo son. Una rotación de Send Definition, un cambio en una Send Classification, o un update de tenant pueden vaciar o cambiar las claves de join en silencio. Un Journey que lee estado de engagement desde _Sent y nunca entra a nadie es la forma de la falla.

7. WHERE 1=2 no "limpia" un Data Extension como uno espera.

El patrón folclórico para resetear un DE es INSERT INTO target SELECT * FROM source WHERE 1=2 con la Activity en modo Overwrite. Funciona en algunos DEs, falla en otros, según el setting de retention del DE de destino y la configuración de primary key. Vimos este patrón dejar un DE medio limpio — las filas que el Overwrite quería tirar, intactas; las filas que el SELECT nuevo agregó, presentes. Resultado: un DE que crece con el tiempo a pesar de que corre un rebuild "limpio" todos los días.

El reset confiable es explícito: limpiá el DE vía API o vía la acción "Clear Data" de la UI, después INSERT las filas nuevas. No te apoyes en Overwrite para que lo haga implícitamente.

8. La aritmética de fechas cruza límites de año mal.

DATEADD(day, -30, GETDATE()) está bien. DATEADD(month, -3, GETDATE()) el 30 de abril devuelve el 30 de enero — correcto. El 31 de mayo devuelve el 28 de febrero (año bisiesto) o el 29 — dependiendo de cómo lo interpreta el motor, esto a veces queda desfasado por un día para la misma query corrida con dos días de diferencia.

El patrón: nunca hagas DATEADD por mes sobre una columna que filtra Sends. Usá ventanas en cantidad de días (ej. DATEADD(day, -90, GETDATE())) y documentá la ventana en un comentario. Si la regla de negocio es "últimos 3 meses", traducila a una cantidad fija de días en el momento del diseño, no en el momento de la query.

9. SubscriberKey es un string. Casteá antes de joinear entre DEs.

Incluso cuando SubscriberKey parece entero en el origen — y aún cuando el CRM lo guarda como INT — Marketing Cloud lo almacena como identificador string en los registros de Subscriber. Un join ON sub.SubscriberKey = ext.UserId donde ext.UserId es INT va a perder filas en silencio donde los ceros a la izquierda difieran, o donde un lado trimmee espacios al final y el otro no.

-- EN RIESGO — coerción de tipo implícita, mismatches silenciosos
SELECT s.SubscriberKey, e.LoyaltyTier
FROM _Subscribers s
INNER JOIN ext_loyalty e
  ON s.SubscriberKey = e.UserId;

-- SEGURO — cast explícito en los dos lados, más un TRIM defensivo
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))));

La primera versión es más corta. La segunda versión es la que matchea el conteo de filas que esperás.

10. ORDER BY adentro de INSERT INTO ... SELECT no es confiable.

T-SQL estricto dice que ORDER BY en un subselect o en INSERT … SELECT no tiene efecto definido sobre el orden de las filas en el destino — solo el capricho del optimizer. La SQL Activity de Marketing Cloud lo trata igual: las filas en el DE de destino salen en el orden que el motor eligió, sin importar el ORDER BY que vos escribiste.

Si el orden importa — digamos, para un send de "primeros 100 por fecha" — el orden se impone en el momento de lectura vía la primary key del DE de destino + retrieval, o vía una columna de secuencia que poblás explícitamente:

-- MAL — asume que el destino preserva el ORDER BY del SELECT
INSERT INTO top_100_sends
SELECT TOP 100 SubscriberKey, OrderDate
FROM master_subscribers
ORDER BY OrderDate DESC;

-- BIEN — poblá una columna de rank explícita por la que el destino
-- pueda ordenar
INSERT INTO top_100_sends
SELECT
  SubscriberKey,
  OrderDate,
  ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS Rank
FROM master_subscribers;
-- (y aceptá el riesgo de window function del gotcha #4 — o stagéa y joineá)

Si tu modelo de datos depende del orden de filas en el destino, el modelo es el bug. Arreglá el modelo antes que el orden.

Cierre

Estos diez no son teóricos. Son el tipo de cosas que rompen un Send la mañana después de un update de plataforma de Salesforce, o reconstruyen un Data Extension de 1,65M de filas dejándolo medio vacío porque la query corrió 31 minutos en vez de 29. Cleon escribe SQL de MC con la asunción de que cualquiera de estos puede dispararse en la próxima corrida — y las queries que entregamos son las que sobreviven a esa asunción.

Si encontrás uno que falte — un gotcha que mordió a tu equipo y no está acá — escribinos a hello@wearecleon.com. Lo agregamos, con crédito.