Query with style. Readable, bug-free code is a few shifts & spaces away.
Table of Contents
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:
- Be consistent across your codebase.
- Strive for good readability.
Everything else is preference. And you are just about to learn about mine.
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.
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.
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.
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.
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.
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:
SELECTColumn in a separate line
WHEREcondition in a separate line
CASEcondition in a separate line
ONrelationship in a separate line
As you can see, the
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:
You Should Know
If you are using
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.
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.
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
JOINkeywords are children of the initial Table referenced with
- Joining relationships (
ON) are children of
- Case conditions are children of the
- Where conditions are children of the
- 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.
Use spaces wherever it makes the query more readable.
The must-have is spacing around any operators (equality or comparison) and after commas.
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
CASE) equal spacing might make the query harder to read. Always decide which approach works best for your query from readability perspective.
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:
- 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.
- 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.
- 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
- 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
SELECT by two spaces for the same reason.
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.
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.
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.
JOIN, add meaningful aliases for Tables and prefix all Columns with them.
There are a few points:
- 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.
- 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.
- 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.
- Always use
ASkeyword between the Table name and the alias.
!= instead of
!= symbol is much more popular and used in many languages. Using it makes the query more readable to people not experienced in SQL.
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:
- Use descriptive Column names. It is better to have a long explicit one (
IsTrackingSuppressed) than a short mysterious abbreviation (
- When the Column is a boolean, prefix it with
Doesdepending on the underlying data).
- When the Column is a date, suffix it with
It is a long article, so let's gather all the recommendations in one place:
- Be consistent
- Strive for readability
- Use comments to provide required context to your query
UPPERCASEfor SQL syntax 🔗
PascalCasefor Table & Column names 🔗
- Limit information per line to one 🔗
- Align main SQL keywords to the left 🔗
- Use indentation to highlight the relationship between SQL keywords 🔗
- Use spaces wherever it makes the query more readable 🔗
- Use commas at the beginning of the line 🔗
- Use full Date Parts names 🔗
INNER JOINname over
JOIN ONreference Column from newly joined Table on the left 🔗
- When using
JOIN, add meaningful aliases for Tables and prefix all Columns 🔗
- Always use
ASkeyword between the Table name and the alias 🔗
- 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.