The ability to write and automate SQL queries in Automation Studio is an indispensable feature when it comes to working with data in Salesforce Marketing Cloud. The current SQL query editor in Automation Studio is very rudimentary and makes it almost impossible to debug SQL queries. It can be very time consuming and frustrating to work on more complicated queries as each time your run your query, you need to go to Data Extensions to check the results.
Salesforce has decided to make things a bit easier for Marketing Cloud users and launched a new app last month called Query Studio for Marketing Cloud. The app allows you to quickly run queries with a similar experience to SQL Server Studio or MySQL workbench.
You can get Query Studio for Marketing Cloud for free from Salesforce AppExchange and install in a couple of clicks. After you install the app, you need to log out and log back in, to be able to access it from the App Exchange menu:
The user interface of Query Studio is quite simple:
- From the Query search box at the top, you can choose an existing query that you wish to run, or you can write your own query in the query editor window.
- Click the Run button or use the keyboard shortcut CTRL + Enter to run your query – you will see a timer next to the Run button, showing you how long the query takes to execute.
- You can also validate your query to check if query results can be inserted into target data extension – once you execute your query, choose a target data extension from the Data Extension search box, choose Data Action and click on Validate Data.
The validation function, according to Salesforce, will cross check for required fields, field lengths, and mismatched field data types. I ran a few tests and made a few mistakes on purpose to see how the validation feature works. It’s nice to see a description of the validation error in the results, but if you have more than one error, you will have to correct them one by one, as the validation tool only displays the first error it encounters:
Unfortunately, the Query Studio is still missing a debugging feature, which would show syntax errors in your query:
It is worth mentioning that Query Studio creates a new folder in your Data Extensions folder, called “QueryStudioResults”:
QueryStudioResults data extension folder will be created in your account to hold temporary data extensions, which have data retention set to auto-delete after 24 hours. Each time you run a query in Query Studio, a new Data Extension is created.
Every Query Studio user will also have a query activity created in Automation Studio for them, which is used for running one-off queries. It will contain ‘InteractiveQuery’ in the name:
Overall, the new Query Studio is great for developing new queries, when you want to see the results on-screen and be able to test and amend your queries quickly. I would say that this is a satisfying minimum viable product, which will hopefully evolve into a full-on SQL Console with proper debugging support in the future.
Read more and install here: https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000FP3yFUAT
Related article: Data Views in Salesforce Marketing Cloud
sfmarketing.cloud 
Pingback: Salesforce makes life easier with its latest releases -
If I change the Data Retention settings and the location of the DE created by Query Studio Results, will it still delete after 24 hours?
LikeLike
Pingback: Salesforce makes life easier with its latest releases – [dev] marketingcloud.today
I installed the Query studio app and it seemed I couldn’t run my SQL query. Nothing happens when I click “run” but it did created a QueryStudioResults folder in the DE folder. I expected it would automatically create a DE for the SQL query…
LikeLike
Query Studio has a limitation that it doesn’t allow us to collect all the fields available in the data. So in my first attempt, I tried:
SELECT * FROM _Subscribers WHERE DateJoined > dateadd(d, -30, getdate())
And it didn’t work at all, I can’t even run the SQL query.
In my second attempt, I tried to specify some fields and it worked
SELECT SubscriberKey, DateJoined, EmailAddress, Status, Locale FROM _Subscribers WHERE DateJoined > dateadd(d, -30, getdate())
LikeLike
Yes, you can not use * in query studio. Need to mention individual fields in query studio.
LikeLike
Thanks, man! You changed my life with that tip.
LikeLike
Pingback: So, You Want to be Certified as a Marketing Cloud Administrator? Follow Along! - Automation Champion
I wanted to use the Query studio but I couldn’t find it in the Menu. Another user in our organization unstalled it and uses it. So I’m wondering how can I make it visible in the menu?
LikeLike
Go to installed packages in the setup and make sure it’s enabled for all BUs and users
LikeLike
Pingback: Splitting a string of comma-separated values in a Data Extension field using SQL – sfmarketing.cloud
Do you have a view on the security profile of the query tool where your data included a heavy dose of personal data?
Technically Salesforce themselves didn’t ‘build it’ (it came out of their ‘labs’) and officially they don’t support it. Do they technically vouch for it security wise.
LikeLike