Skip to main content

SFMC SQL Style Guide

Query with style. Readable, bug-free code is a few shifts & spaces away.

First things first: this Salesforce Marketing Cloud SQL style guide is highly subjective. You may use it as it is, implement only some parts of it, or ignore it altogether. There are only two rules that I believe are a must-have:

  1. Be consistent across your codebase.
  2. Strive for good readability.

Everything else is preference. And you are just about to learn about mine.

Letter Case#

SQL gives you much freedom regarding the type of letter case you will be using for parts of your query. Let's use this freedom to create queries that are readable and aligned in convention to other Marketing Cloud programmatic languages operating on the same data.

SQL Syntax Case#

Use UPPERCASE for all elements of SQL syntax.

This approach helps visually differentiate query language from Tables (data extensions, data views) and Columns (fields) to improve readability.

/* βœ… Upper Case for SQL Syntax */SELECT      SubscriberKey    , EmailAddressFROM Ent._SubscribersWHERE DateJoined >= DATEADD(MONTH, -1, GETDATE())
/* ❌ Pascal Case for SQL Syntax */Select      SubscriberKey    , EmailAddressFrom Ent._SubscribersWhere DateJoined >= DateAdd(Month, -1, GetDate())
/* ❌ Lower Case for SQL Syntax */select      SubscriberKey    , EmailAddressfrom Ent._Subscriberswhere DateJoined >= dateadd(month, -1, getdate())

Column & Table Names Case#

Use PascalCase for all Table and Column names.

Treat it as a soft recommendation, as it is highly connected to your organisation's conventions. If other connected systems are using a different style, it might be best to follow it in Marketing Cloud.

It is especially true for Tables, as you might be using mixed convention leveraging lowercase, uppercase and underscores for Data Extension names. However, try not to use hyphens, as those will require you to use square brackets around the name and might interfere in query syntax highlighting, leading to worse readability.

The most popular SQL convention for Table and Column names is snake_case. However, in Marketing Cloud, the Table and Column names will share the case between your queries and the User Interface. It is essential to make them as readable and human friendly as possible. Additionally, in many cases, you will also leverage those names in AMPScript and SSJS.

Due to the above, I believe PascalCase - especially for Column names - is the best choice for Marketing Cloud SQL.

/* βœ… Pascal Case for Table and Column names */SELECT      wel.SubscriberKey    , wel.EmailAddress    , o.EventDate AS OpenDateFROM WelcomeCampaignSegment AS wel    INNER JOIN _Open AS o        ON o.SubscriberKey = wel.SubscriberKey
/* βœ… Pascal Case for Column names and custom consistent covention for Table names */SELECT      wel.SubscriberKey    , wel.EmailAddress    , o.EventDate AS OpenDateFROM WEL_WelcomeCampaignSegment_20201011_OTH AS wel    INNER JOIN _Open AS o        ON o.SubscriberKey = wel.SubscriberKey
/* ❌ Lower Case for Table and Column names */SELECT      wel.subscriberkey    , wel.emailaddress    , o.eventdate AS opendateFROM welcomecampaignsegment AS wel    INNER JOIN _open AS o        ON o.subscriberkey = wel.subscriberkey
/* ❌ Upper Case for Table and Column names */SELECT      WEL.SUBSCRIBERKEY    , WEL.EMAILADDRESS    , O.EVENTDATE AS OPENDATEFROM WELCOMECAMPAIGNSEGMENT AS WEL    INNER JOIN _OPEN AS O        ON O.SUBSCRIBERKEY = WEL.SUBSCRIBERKEY
You Should Know

If you want to make your Style Guide even safer, you can decide on writing all Columns and Table names in square brackets. As Marketing Cloud is not checking whether you use SQL's reserved keywords, this approach will protect you from potential silent issue. The cost, however, is much more characters and less readable queries.

You need to decide how hard you want to leverage the square brackets
SELECT      [wel].[SubscriberKey]    , [wel].[EmailAddress]    , [o].[EventDate] AS [OpenDate]FROM [WelcomeCampaignSegment] AS [wel]    INNER JOIN [_Open] AS [o]        ON [o].[SubscriberKey] = [wel].[SubscriberKey]

Alignment and Indentation#

SQL is very flexible when it comes to alignment and indentation of the query. Writing everything in one line creates a valid code that will run. But just because it is correct, it doesn't mean it is good. Generous use of new lines and indents has close to no impact on performance but a massive impact on readability. Enter and Space keys are your friends.

There are many styles out there, and I decided to follow those that merge good writing speed with excellent readability.

Single Information per Line#

Limit information per line to one.

I recommend keeping one information per line, as it allows for much easier scanning. The four most important examples of this approach are:

  1. Each SELECT Column in a separate line
  2. Each WHERE condition in a separate line
  3. Each CASE condition in a separate line
  4. Each JOIN and ON relationship in a separate line
/* βœ… Single information per line  */SELECT      s.SubscriberKey AS SubscriberKey    , j.EmailName     AS EmailName    , s.EventDate     AS SentDate    , j.DeliveredTime AS DeliveryDate    , o.EventDate     AS OpenDateFROM _Sent AS s    LEFT JOIN _Job AS j        ON j.JobID = s.JobID    LEFT JOIN _Open AS o        ON o.JobID = s.JobID        AND o.ListID = s.ListID        AND o.BatchID = s.BatchID        AND o.SubscriberID = s.SubscriberID        AND o.IsUnique = 1WHERE    1 = CASE        WHEN j.EmailName LIKE 'UPS_%' THEN 1        WHEN j.EmailName LIKE 'CRS_%' THEN 1        ELSE 0    END
/* ❌ Multiple pieces of information per line */SELECT s.SubscriberKey AS SubscriberKey, j.EmailName AS EmailName, s.EventDate AS SentDate, j.DeliveredTime AS DeliveryDate, o.EventDate AS OpenDateFROM _Sent AS s    LEFT JOIN _Job AS j ON j.JobID = s.JobID    LEFT JOIN _Open AS o ON o.JobID = s.JobID AND o.ListID = s.ListID AND o.BatchID = s.BatchID AND o.SubscriberID = s.SubscriberID AND o.IsUnique = 1WHERE 1 = CASE WHEN j.EmailName LIKE 'UPS_%' THEN 1 WHEN j.EmailName LIKE 'CRS_%' THEN 1 ELSE 0 END

As you can see, the SELECT and WHERE keywords have their separate line to mark respective block starting. For readability reasons, I make an exception if there is only one Column or condition to be used:

/* βœ… Single Column in SELECT and single condition in WHERE lines */SELECT SubscriberKeyFROM _SubscribersWHERE Domain = 'mateuszdabrowski.pl'
/* ❌ Separate lines with single SELECT Column and WHERE condition */SELECT      SubscriberKeyFROM _SubscribersWHERE    Domain = 'mateuszdabrowski.pl'
You Should Know

If you are using DISTINCT or TOP, put it into the same line as SELECT. In such a case, even if you work on a single Column, move it to a separate row for better readability.

/* βœ… Single Column in SELECT and single condition in WHERE lines */SELECT DISTINCT TOP 10      SubscriberKeyFROM ContenstSubmissions
/* ❌ Separate lines for single SELECT Column and WHERE condition */SELECT DISTINCT TOP 10 SubscriberKeyFROM ContenstSubmissions

Left Aligned Keywords#

Align main SQL keywords to the left.

This rule focuses on writing speed. While I prefer the Vertically Aligned Space approach's aesthetics, I don't see any significant readability gains. On the other hand, writing in this style in Marketing Cloud is a pain, as there is no autoformatter supporting it in the Query Studio or Query Activity. We would have to add all those additional spaces manually. It's just not worth it.

/* βœ… Left Aligned Keywords */SELECT      SubscriberKey    , EmailAddressFROM Ent._SubscribersWHERE DateJoined >= DATEADD(MONTH, -1, GETDATE())
/* ❌ Post Keyword Space Aligned Vertically */SELECT SubscriberKey,       EmailAddress  FROM Ent._Subscribers WHERE DateJoined >= DATEADD(MONTH, -1, GETDATE())

Consistent Indentation#

Use indentation to highlight SQL keyword relationships.

This rule focuses on readability. The logic, in short, is to indent whenever the line is dependent on the previous line (child line), for example:

  • Selected Column Names are children of SELECT
  • JOIN keywords are children of the initial Table referenced with FROM
  • Joining relationships (ON) are children of JOIN
  • Case conditions are children of the CASE
  • Where conditions are children of the WHERE
  • Sub-queries follow the same rules in respective indentation level.

For simplicity I use 4-spaces indent, as it is both standard and can be added in Marketing Cloud Query with a single click of the Tab key.

/* βœ… Dependency based indentation */SELECT      s.SubscriberKey AS SubscriberKey    , j.EmailName     AS EmailName    , s.EventDate     AS SentDate    , j.DeliveredTime AS DeliveryDate    , o.EventDate     AS OpenDateFROM _Sent AS s    LEFT JOIN _Job AS j        ON j.JobID = s.JobID    LEFT JOIN _Open AS o        ON o.JobID = s.JobID        AND o.ListID = s.ListID        AND o.BatchID = s.BatchID        AND o.SubscriberID = s.SubscriberID        AND o.IsUnique = 1WHERE    1 = CASE        WHEN j.EmailName LIKE 'UPS_%' THEN 1        WHEN j.EmailName LIKE 'CRS_%' THEN 1        ELSE 0    END
/* ❌ Lack of indentation */SELECTs.SubscriberKey   AS SubscriberKey, j.EmailName     AS EmailName, s.EventDate     AS SentDate, j.DeliveredTime AS DeliveryDate, o.EventDate     AS OpenDateFROM _Sent AS sLEFT JOIN _Job AS j ON j.JobID = s.JobIDLEFT JOIN _Open AS o ON o.JobID = s.JobIDAND o.ListID = s.ListIDAND o.BatchID = s.BatchIDAND o.SubscriberID = s.SubscriberIDAND o.IsUnique = 1WHERE 1 = CASEWHEN j.EmailName LIKE 'UPS_%' THEN 1WHEN j.EmailName LIKE 'CRS_%' THEN 1ELSE 0END

Intentional Spacing#

Use spaces wherever it makes the query more readable.

The must-have is spacing around any operators (equality or comparison) and after commas.

However, there is another place where spacing is beneficial for readability but might require an exception in some scenarios. Spacing for aliases and THEN keywords of the CASE statement.

I try to use it whenever possible, but when there is huge imbalance in length (for example one Column requires multi-function calculation or there is a single complex WHEN in CASE) equal spacing might make the query harder to read. Always decide which approach works best for your query from readability perspective.

/* βœ… Spacing around operators plus equalizing aliases and THEN */SELECT      s.SubscriberKey AS SubscriberKey    , j.EmailName     AS EmailName    , s.EventDate     AS SentDate    , j.DeliveredTime AS DeliveryDate    , o.EventDate     AS OpenDateFROM _Sent AS s    LEFT JOIN _Job AS j        ON j.JobID = s.JobID    LEFT JOIN _Open AS o        ON o.JobID = s.JobID        AND o.ListID = s.ListID        AND o.BatchID = s.BatchID        AND o.SubscriberID = s.SubscriberID        AND o.IsUnique = 1WHERE    1 = CASE        WHEN j.EmailName LIKE 'UPS_%'     THEN 1        WHEN j.EmailName LIKE 'CRS_%'     THEN 1        WHEN j.EmailName = 'OTH_SeedList' THEN 1        ELSE 0    END
/* βœ… Spacing around operators plus equalizing aliases with exceptions */SELECT      s.SubscriberKey AS SubscriberKey    , j.EmailName     AS EmailName    , s.EventDate     AS SentDate    , j.DeliveredTime AS DeliveryDate    , o.EventDate     AS OpenDate    , DATEDIFF(HOUR, s.EventDate, o.EventDate) AS TimeToOpenFROM _Sent AS s    LEFT JOIN _Job AS j        ON j.JobID = s.JobID    LEFT JOIN _Open AS o        ON o.JobID = s.JobID        AND o.ListID = s.ListID        AND o.BatchID = s.BatchID        AND o.SubscriberID = s.SubscriberID        AND o.IsUnique = 1WHERE    1 = CASE        WHEN j.EmailName LIKE 'UPS_%' OR j.EmailName LIKE 'CRS_%' THEN 1        WHEN j.EmailName = 'OTH_SeedList' THEN 1        ELSE 0    END
/* ❌ Lack of spacing */SELECT    s.SubscriberKey AS SubscriberKey    ,j.EmailName AS EmailName    ,s.EventDate AS SentDate    ,j.DeliveredTime AS DeliveryDate    ,o.EventDate AS OpenDate    ,DATEDIFF(HOUR,s.EventDate,o.EventDate) AS TimeToOpenFROM _Sent AS s    LEFT JOIN _Job AS j        ON j.JobID=s.JobID    LEFT JOIN _Open AS o        ON o.JobID=s.JobID        AND o.ListID=s.ListID        AND o.BatchID=s.BatchID        AND o.SubscriberID=s.SubscriberID        AND o.IsUnique=1WHERE    1=CASE        WHEN j.EmailName LIKE 'UPS_%' THEN 1        WHEN j.EmailName LIKE 'CRS_%' THEN 1        WHEN j.EmailName='OTH_SeedList' THEN 1        ELSE 0    END

Commas Placement#

Use commas at the beginning of the line.

There are few approaches related to comma placement. Initially, I favoured commas at the end of the line, as I found them more accessible to use due to their similarity to natural language. However, there are strong reasons to use the comma at the beginning of the line. Here is what convinced me to change my style:

  1. Adding or deleting a Column requires a change in only one line. It's easier, faster and minimises the risk of a crash due to the trailing comma.
  2. Above is also very beneficial during difference checking and when leveraging git for query version control. With a comma at the beginning of the line, the Column addition or deletion is displayed correctly as a single line change.
  3. Having a comma in the beginning makes it easier to assess where a new Column definition starts quickly. It is a vast readability improvement when you are using more complex structures, like CASE in SELECT.
  4. It's also easier to quickly assess whether you have a comma for each Column. With lines of various length, it is much harder with the comma at the end.

For the sake of readability, I indent the first Column by two spaces so that it is in line with the following ones. Similarly, I indent END after CASE in SELECT by two spaces for the same reason.

/* βœ… Commas at the beginning */SELECT      SubscriberKey    , EmailAddress    , DateJoinedFROM Ent._Subscribers
/* ❌ Commas in the end */SELECT    SubscriberKey,    EmailAddress,    DateJoinedFROM Ent._Subscribers

Explicit vs Implicit#

Many things in SQL can be done in multiple ways - using various functions, symbols or shortcuts. While all those options may work, not all are a good idea. Whenever you are choosing the approach, go for the explicit and straightforward. Even if it means writing a few characters more.

Date Parts#

Use full Date Parts names.

Yes, abbreviations let you save few characters, but they kill the readability unless someone is fluent in those shortcuts. For Marketing Cloud purposes, the time saved during writing is not worth the time lost when reading. Go with the full version.

/* βœ… Full Date Part */SELECT      SubscriberKey    , EmailAddressFROM Ent._SubscribersWHERE DateJoined >= DATEADD(MONTH, -1, GETDATE())
/* ❌ Abbreviated Date Part */SELECT      SubscriberKey    , EmailAddressFROM Ent._SubscribersWHERE DateJoined >= DATEADD(M, -1, GETDATE())

JOIN and ON#

Use INNER JOIN name and reference Column from the new Table first after ON.

Instead of just JOIN, write INNER JOIN - it's longer but explicit and in line with the rest of JOIN keywords. It makes it easier to scan queries with multiple various JOIN statements.

Additionally, when defining JOIN relationships after ON, firstly reference the Column from the joined Table. The previous Table should be on the right side. It will improve consistency and reading speed.

/* βœ… Explicit Inner Join and Column from new Table on the left side of the ON condition */SELECT      wel.SubscriberKey    , wel.EmailAddress    , o.EventDate AS OpenDateFROM WelcomeCampaignSegment AS wel    INNER JOIN _Open AS o        ON o.SubscriberKey = wel.SubscriberKey
/* ❌ Implicit Inner Join and Column from new Table on the right side of the ON condition */SELECT      wel.SubscriberKey    , wel.EmailAddress    , o.EventDate AS OpenDateFROM WelcomeCampaignSegment AS wel    JOIN _Open AS o        ON wel.SubscriberKey = o.SubscriberKey

Table Aliases#

When using JOIN, add meaningful aliases for Tables and prefix all Columns with them.

There are a few points:

  1. If you are working on only one Table without JOIN - don't use aliases at all. They are not needed and would make the query less readable.
  2. If you have any JOIN - always use aliases and prefixes. SQL doesn't always require it, but it is much easier to read the query when the Columns have an explicit Table mention in the prefix.
  3. When you create an alias for a Table, make it meaningful. Using subsequent letters of the alphabet is not a good idea. Instead, use something that will remind the reader of the full name - for example, abbreviation using the first letter of each word in the Table. It will allow for much easier reading.
  4. Always use AS keyword between the Table name and the alias.
/* βœ… No aliasing in single Table query */SELECT      SubscriberKey    , EmailAddressFROM Ent._Subscribers
/* ❌ Aliasing in single Table query */SELECT      s.SubscriberKey    , s.EmailAddressFROM Ent._Subscribers AS s
/* βœ… Meaningful aliasing and prefixes in multi Table query */SELECT      wel.SubscriberKey    , wel.EmailAddress    , o.EventDate AS OpenDateFROM WelcomeCampaignSegment AS wel    INNER JOIN _Open AS o        ON o.SubscriberKey = wel.SubscriberKey
/* ❌ No aliasing and no prefix in multi Table query */SELECT      SubscriberKey    , EmailAddress    , EventDate AS OpenDateFROM WelcomeCampaignSegment    INNER JOIN _Open        ON _Open.SubscriberKey = WelcomeCampaignSegment.SubscriberKey
/* ❌ Not meaningful aliasing and no AS keyword in multi Table query */SELECT      a.SubscriberKey    , a.EmailAddress    , b.EventDate AS OpenDateFROM WelcomeCampaignSegment a    INNER JOIN _Open b        ON a.SubscriberKey = b.SubscriberKey

Not Equal To Symbol#

Use != instead of <>.

The != symbol is much more popular and used in many languages. Using it makes the query more readable to people not experienced in SQL.

/* βœ… Use of != for negation */SELECT SubscriberKeyFROM Ent._SubscribersWHERE CONVERT(DATE, DateJoined) != CONVERT(DATE, GETDATE())
/* ❌ Use of <> for negation */SELECT SubscriberKeyFROM Ent._SubscribersWHERE CONVERT(DATE, DateJoined) <> CONVERT(DATE, GETDATE())

Meaningful Column Names#

Use Column names that suggest the data type.

Just as with a letter case, you might have your hands tied by cross-system dependencies. But whenever possible, strive for meaningful names:

  1. Use descriptive Column names. It is better to have a long explicit one (IsTrackingSuppressed) than a short mysterious abbreviation (trk).
  2. When the Column is a boolean, prefix it with Is (or Has/Does depending on the underlying data).
  3. When the Column is a date, suffix it with Date.
/* βœ… Meaningful and consistent Column Names */SELECT      s.SubscriberKey    AS SubscriberKey    , j.EmailName        AS EmailName    , s.EventDate        AS SentDate    , j.DeliveredTime    AS DeliveryDate    , o.EventDate        AS OpenDate    , j.SuppressTracking AS IsTrackingSuppressedFROM _Sent AS s    LEFT JOIN _Job AS j        ON j.JobID = s.JobID    LEFT JOIN _Open AS o        ON o.JobID = s.JobID
/* ❌ No consistency and type alignment in Column Names */SELECT      s.SubscriberKey    AS SubscriberKey    , j.EmailName        AS EmailName    , s.EventDate        AS SentDate    , j.DeliveredTime    AS DeliveryTime    , o.EventDate        AS OpenedAt    , j.SuppressTracking AS SuppressTrackingFROM _Sent AS s    LEFT JOIN _Job AS j        ON j.JobID = s.JobID    LEFT JOIN _Open AS o        ON o.JobID = s.JobID

Sum Up#

It is a long article, so let's gather all the recommendations in one place:

  1. Be consistent
  2. Strive for readability
  3. Use comments to provide required context to your query
  4. Use UPPERCASE for SQL syntax πŸ”—
  5. Use PascalCase for Table & Column names πŸ”—
  6. Limit information per line to one πŸ”—
  7. Align main SQL keywords to the left πŸ”—
  8. Use indentation to highlight the relationship between SQL keywords πŸ”—
  9. Use spaces wherever it makes the query more readable πŸ”—
  10. Use commas at the beginning of the line πŸ”—
  11. Use full Date Parts names πŸ”—
  12. Use INNER JOIN name over JOIN πŸ”—
  13. In JOIN ON reference Column from newly joined Table on the left πŸ”—
  14. When using JOIN, add meaningful aliases for Tables and prefix all Columns πŸ”—
  15. Always use AS keyword between the Table name and the alias πŸ”—
  16. Use != instead of <> πŸ”—
  17. Use Column names that suggest the data type πŸ”—

If you want to share something I'm missing or have arguments for a different recommendation - let me know.

Looking for more Marketing Cloud style? Check out my: