SFMC SQL Basics

SELECT your best contacts FROM Salesforce Marketing Cloud WHERE Data Filter is not enough.

SQL in Salesforce Marketing Cloud

SQL (Structured Query Language) is one of the best ways to work with relational databases. And Marketing Cloud's Data Extensions, with the help of Data Designer, offers precisely that. A relational database. Same for system Data Views containing tracking data. Because of this, SQL is used in SFMC whenever powerful segmentation is needed (mostly using Automation Studio).

The main difference from standard SQL (SFMC uses more or less SQL Server 2016) is that SFMC supports only SELECT statement. Neither INSERT, UPDATE or DELETE are officially supported. It is because the queries are executed as a part of the backend query and are limited to provide non-breakable functionality.

Another limiting characteristic of SFMC SQL is visible in the data accessible by queries. Only data stored in data extensions or system data views is available. Moreover, you can save the results only to the data extensions. The reason is other data structures of SFMC are not relational databases.

Finally, the significant limitation that you might hit as your Marketing Cloud grows is the AutoKill. It will automatically stop your query if execution time exceeds the 30 minutes limit. Think about the query with the future in mind. It is especially important for queries that are supposed to be backbones of your data management flow in Marketing Cloud. Even if it works great now, consider how it will behave once you have more data in the system. One of the great ways to not trigger AutoKill is to implement retention periods for the Data Extensions used in the query to limit input data.

Fundamentals of SQL queries

SQL is straightforward to read but requires a bit of understanding to write. However, with only the SELECT being available, it is effortless to grasp.

There are four most important elements that you will be using in nearly every query in SFMC:

  • SELECT: tell the query what data you want to leverage
  • FROM: tell the query where the data is in Marketing Cloud (name of Data Extension or System Data View)
  • JOIN: tell the query how you want to merge data from multiple sources
  • WHERE: tell the query which part of the above data is interesting to you

However, there is much to learn to unlock the full power of SQL in SFMC. Check out deep-dive docs on SQL Statements (SELECT, FROM, JOIN, WHERE) and SFMC System Data Views.

Query Studio

Working with queries in the out-of-the-box version of Salesforce Marketing Cloud is quite hard. Whenever you want to check the output of your query, you must run the SQL Query in Automation Studio and check the output Data Extension. There is, however, a better way.

A Query Studio by Salesforce. It is a Marketing Cloud App available in AppExchange that is must-have for anyone interested in working with SQL in SFMC. Think about SQL Server Studio or, if this is not telling you much, a one-stop-shop for writing, validating, checking and saving your queries. Check it. You will never look back. Learn more on sfmarketing.cloud.

Last updated on by Mateusz Dąbrowski