Skip to main content

Marketing Cloud SQL: Style Guide

The opinionated rules Cleon applies to every MC SQL Activity we ship — naming, formatting, commenting, patterns to prefer, anti-patterns to refuse — distilled from the gotchas and reference pages into a single discipline document.

Decision framework·Last updated 2026-05-07·Drafted by Lira · Edited by German Medina

This is the page where Cleon stops describing what MC SQL is and starts saying what we do with it. Salesforce defines what works. The reference pages document the syntax. The gotchas document what fires at scale. This Style Guide is the discipline that keeps an implementation maintainable a year after we hand it off.

Use it as a checklist before merging any new SQL Activity into production. The rules are short on purpose — when a rule needs an explanation, the explanation is in the page it links to.

Naming

Data Extensions follow a prefix convention

Decided once, before the first DE is created. Renaming 200 of them later is the alternative.

| Prefix | Holds | |---|---| | DE_ | Master Data Extension owned by the implementation | | de_stg_ | Staging DE — rebuilt on every run, safe to truncate | | de_log_ | Run log — append-only, indexed by date | | de_log_<sdv>_ | Snapshot of a System Data View (e.g. de_log_sent_30d) | | de_lookup_ | Reference / config DE that marketing edits without touching SQL | | TS_ | Triggered Send Definition | | J_ | Journey | | Auto_ | Automation | | CR_ | Code Resource |

The pattern: prefix communicates lifecycle, the rest of the name communicates purpose. de_stg_active_subs_30d reads in one pass.

Column aliases carry intent

A column called EmailAddressLower carries the decision (this was lowered at query time). A column called Email1 carries nothing. Always alias normalized values with a name that documents the normalization.

Table aliases are short, lowercase, and consistent

s for subscribers, p for purchases, lt for loyalty_tier. Single letter when only one source of that initial; two letters when there'd be a collision. Never Master_Subscribers AS Master_Subscribers. See FROM.

Formatting

One column per line for SELECT past 3 columns

-- Past 3 columns, line-per-column makes diffs and code review readable
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  s.LoyaltyTier,
  s.LastPurchase,
  p.Amount AS LastPurchaseAmount
FROM master_subscribers s
INNER JOIN purchases p
  ON s.SubscriberKey = p.SubscriberKey;

Keywords UPPER, identifiers lowercase / configured-case

SELECT, FROM, WHERE, INNER JOIN, GROUP BY, etc. are uppercase. Table names match the DE configuration (which is case-insensitive but consistency makes diffs cleaner). Column names follow how they're defined in the DE.

JOIN conditions on their own indented line

FROM master_subscribers s
INNER JOIN purchases p
  ON s.SubscriberKey = p.SubscriberKey
INNER JOIN loyalty_tier lt
  ON s.LoyaltyTier = lt.TierCode

When the ON is on its own line, the join structure becomes scannable. Multiple ON conditions get one per line.

Indent at 2 spaces, never tabs

Tabs render differently across editors and code review tools. Two spaces is the convention across the SQL Activity UI and our docs.

Commenting

Don't comment what the code does — comment why

The code says what. Comments add the context the code can't carry.

-- POINTLESS — repeats what the code already says
-- Get all active subscribers
SELECT * FROM master_subscribers WHERE Status = 'Active';

-- USEFUL — explains why this specific filter
-- Excluding 'pending' status because the verification webhook
-- (added 2026-04) doesn't fire for legacy imports until day 7.
SELECT SubscriberKey, EmailAddress
FROM master_subscribers
WHERE Status = 'Active';

Comment the receipt for non-obvious choices

When you cut a corner, comment it with the date and the reason. The next dev (often you, six months later) needs the receipt.

-- TEMPORARY — replace by 2026-06-15 (calendar reminder set 2026-05-15)
-- Reason: hard-coded sender ID until the new SAP package finishes
-- verification.
DECLARE @senderId INT = 1234567;

See MC SQL principles — #11 for the temporary-code discipline.

Patterns to prefer

INSERT INTO ... SELECT via Activity wrapper

Never write standalone INSERT VALUES, UPDATE, DELETE, MERGE in MC SQL — they don't work. Always shape the query as SELECT and let the Activity's target action drive the merge behavior. See INSERT INTO.

Stage, validate, then promote

For any non-trivial query, split into:

  1. SQL Activity: SELECT ... INTO de_stg_*
  2. Verification: row count check, data sanity, alert if outside expected range
  3. SQL Activity: INSERT INTO production_de SELECT * FROM de_stg_* (or appropriate target action)

Three Activities, three checkpoints, recoverable. See INSERT INTO and MC SQL principles — #1.

LEFT JOIN ... IS NULL for suppression / anti-join

Don't use NOT IN (SELECT ...) against large sources. The anti-join is the durable pattern. See JOIN and WHERE.

COALESCE over ISNULL

Multi-arg, portable, predictable types. ISNULL is for the rare case where its specific behavior is what you want. See Null functions.

TRY_CAST over CAST when the source is dirty

A bad row otherwise kills the Activity with no rollback. See Conversion functions and gotchas — #1.

LTRIM(RTRIM(CAST(... AS NVARCHAR(255)))) for SubscriberKey joins

The single most common silent-bug source. See JOIN, String functions, gotchas — #9.

Snapshot System Data Views before reading them in production

Never FROM _Sent (or _Open, etc.) directly in a scheduled Activity. Snapshot into de_log_* once, read from the snapshot. See FROM and gotchas — #6.

Day counts over month math for date filters

DATEADD(day, -90, GETDATE()) is stable. DATEADD(month, -3, GETDATE()) is not. Translate "last 3 months" to "last 90 days" once at design time. See Date functions and gotchas — #8.

Explicit JOIN types, never implicit comma joins

-- AVOID
FROM a, b WHERE a.k = b.k

-- PREFER
FROM a INNER JOIN b ON a.k = b.k

See JOIN.

Patterns to refuse

SELECT * in any production Activity

Source schema changes silently re-shape the destination. Always project explicit columns. See SELECT.

NOT IN (SELECT ...) against a Data Extension

Performance trap that gets worse with scale. Use anti-join. See WHERE and JOIN.

ROW_NUMBER() OVER (...) as the dedup mechanism

Edition-dependent. Use the MAX-per-group two-Activity pattern. See Aggregate functions and gotchas — #4.

WHERE ... = NULL (or != NULL)

Always returns zero rows. Use IS NULL / IS NOT NULL. See WHERE, Null functions, gotchas — #5.

Wrapping a column in a function inside WHERE

Kills the index. Move the function to the literal side or stage the normalized value. See WHERE, Date functions, String functions.

Unparenthesized AND mixed with OR

AND binds tighter than OR. Always parenthesize when mixing. See WHERE.

Update-mode Activity without a primary key on the destination

Silently behaves like Append. See INSERT INTO.

WHERE 1=2 to "clear" a Data Extension

Folklore that doesn't always work. Use the API or UI's "Clear Data" action explicitly. See gotchas — #7.

The discipline check before merging

Before any new SQL Activity goes from staging into a scheduled Automation, walk through this checklist:

  • [ ] Naming follows the prefix convention (DE / de_stg_ / de_log_ / TS_ / etc.)
  • [ ] All columns explicit in the SELECT (no SELECT *)
  • [ ] Source is your own DE, not a System Data View directly
  • [ ] Target action picked first; the SELECT shape matches it (Overwrite / Append / Update)
  • [ ] If Update: destination DE has a primary key configured
  • [ ] All SubscriberKey joins use LTRIM(RTRIM(CAST(... AS NVARCHAR(255))))
  • [ ] All conversions use TRY_CAST if the source isn't your own clean DE
  • [ ] All NULL checks use IS NULL / IS NOT NULL, never = NULL
  • [ ] Date filters use day counts, not month math
  • [ ] No function wraps on columns inside WHERE (or it's intentional and the source is small)
  • [ ] AND / OR mixes are parenthesized
  • [ ] NOT IN (SELECT ...) rewritten as anti-join if the source is non-trivial
  • [ ] Multi-source query stages each join in its own Activity
  • [ ] Estimated runtime against production volume is under 10 minutes (well below the 30-min hard timeout)
  • [ ] Comments explain the why of any non-obvious choice
  • [ ] Any "temporary" code has an expiration date and a calendar reminder

When all twelve fire, the Activity is ready to ship.

Related

Catalog progress: with this Style Guide, all 15 reference + decision-framework pages in the SQL section are shipped. The remaining catalog work is 3 how-to debugging snippets (Email Sends, Value Length, All Contacts).

If you spot a rule missing — or one of these rules in our public work being violated — write to hello@wearecleon.com. We add it, or we fix it and we say so.