SFMC SQL From

Tell the SQL query FROM where you need the data

The SELECT statement alone won't do anything. Once you have selected columns you want to work with, you also need to pick the table (Data Extension or System Data View) FROM which the query will take the right columns. The code for this is simple:

Get ContactKey and EmailAddress from Data Extension named MasterSubscriberDE
SELECT
ContactKey,
EmailAddress,
FROM MasterSubscriberDE

Just as with column names, if the name of your Data Extension has spaces in it, be sure to enclose it in brackets.

SELECT
ContactKey,
EmailAddress,
FROM [Master Subscriber DE]

There are, however, two cases, specific to Salesforce Marketing Cloud, that adds a twist. System Data Views and Enterprise Data Extensions.

System Data Views

Salesforce Marketing Cloud is partially hiding the System Data Views Data Extensions that store tracking data. Think about Email Sent history, Opens and Clicks, Bounces and Complaints. You can find there tracking data for SMS and Social channels. Also, more technical data points, like Jobs and Enterprise Attributes, are available in the System Data Views. You won't find them in the User Interface but can query them with the SQL. The difference related to those tables is in naming convention. When querying them, you must leverage _ prefix:

SELECT
JobID,
EmailID,
EmailName,
EmailSubject,
EmailSendDefinition,
DeliveredTime
FROM _Job

Enterprise Data Extension

If you want to query Shared Data Extensions or Synchronized Data Extensions, you must use different unique prefix, ENT.:

SELECT
ContactKey,
EmailAddress,
FROM ENT.SharedSegment

Remember that account permissions restrictions may apply.

You Should Know

In some scenarios, you might want to use both the above options at the same time. For example, if you query System Data View _Subscribers on child Business Unit, you will receive data only for the subscribers stored in this child. In case you want to query all your subscribers, you should point to Enterprise level System Data View:

SELECT
SubscriberKey,
EmailAddress,
DateJoined,
DateUnsubscribed
FROM ENT._Subscribers

When selecting Data Extensions to pull data from, remember about the best practice to avoid ones spanning a cumulative field width greater than 4000 characters. They will badly impact the performance of your query. If needed, consider splitting your query into multiple consecutive queries.

Last updated on by Mateusz Dąbrowski