I didn’t start my career in marketing.
Before I found my way into the MarTech world, I worked as a web developer, a database administrator, and eventually an IT manager. Along the way, I picked up a lot of technical skills that I didn’t necessarily realize would become useful in marketing.
SQL was one of them.
One of the things I’ve enjoyed most about working in Marketing Technology is realizing how well skills from earlier stages of my career transfer into this space. Understanding databases, how data relates to other data, and how to query it turns out to be incredibly useful when you’re trying to solve marketing problems.
When I first realized I could use SQL inside Salesforce Marketing Cloud, it was a bit of a game changer.
I had seen processes built around increasingly complicated spreadsheets that required exporting Data Extensions, manipulating data in Excel, building pivot tables, and eventually importing the results back into Marketing Cloud.
Once I started using SQL, I began to see a different way to solve those problems.
But there’s an important lesson I learned along the way:
Marketing Cloud SQL isn’t exactly the same as every other SQL environment.
It has its own quirks, limitations, and ways of getting you into trouble if you don’t understand what your query is actually doing.
Here are a few things I’ve learned.
1. A Spreadsheet Process Might Really Be a Query
One of the first things SQL helped me do was eliminate a complicated manual process.
We had a marketing process where we needed to communicate with different people based on actions they had taken over previous years.
The process involved exporting three or four different Data Extensions, opening them in Excel, building pivot tables, combining the data, working through the logic, and eventually importing the results back into Marketing Cloud as a new Data Extension.
It worked.
But it was manual, complicated, and required someone to repeat the entire process every time.
Once I realized I could query multiple Data Extensions directly, things became much simpler.
Instead of exporting everything and trying to work it out in spreadsheets, I could join the Data Extensions, apply the business logic in SQL, and write the results directly into a new Data Extension.
The process went from being a spreadsheet project to being a repeatable system.
That was one of the moments when SQL really clicked for me inside Marketing Cloud.
Spreadsheets aren’t bad. Sometimes they’re exactly the right tool.
But if you’re repeatedly exporting the same data, combining it manually, and importing the results back into Marketing Cloud, it’s worth asking whether that process should really be a query.
2. Marketing Cloud SQL Has Its Own Rules
One easy mistake is assuming that SQL inside Marketing Cloud works exactly like SQL everywhere else.
It doesn’t.
Marketing Cloud supports a particular implementation of SQL with its own limitations and quirks. Something that works perfectly in another database environment may not work the way you expect in Marketing Cloud.
You may also find that the tools inside Marketing Cloud behave differently.
For example, there are situations where syntax that works in Automation Studio doesn’t work the same way in Query Studio. Even something as simple as SELECT * can behave differently depending on where you’re running the query.
Those little differences can be frustrating.
My advice is to remember that you’re working with SQL inside Marketing Cloud, not a full-featured database environment that necessarily supports every feature you’re used to.
Test your queries.
Don’t assume.
And when something that seems perfectly valid doesn’t work, don’t immediately question everything you know about SQL.
Sometimes you’ve just discovered another Marketing Cloud quirk.
A quick terminology lesson I learned myself
There’s also an easy distinction to mix up when you work across the Salesforce ecosystem.
SQL is what you’re generally using in Marketing Cloud Query Activities to query Data Extensions and Data Views.
SOQL is Salesforce Object Query Language and is used to query Salesforce objects.
I caught myself mixing those up while working on this article, which feels appropriately on-brand for a post about learning the quirks of the platforms we work with.
3. Understand Your JOIN Before You Run It
One of the easiest ways to get the wrong answer from a perfectly valid query is to use the wrong JOIN.
I’ll admit that I sometimes default to an INNER JOIN.
It’s often the first thing I reach for because I’m looking for records that exist in both places.
But that isn’t always the question I actually need to answer.
I’ve had situations where I needed a list of customers and wanted to know whether they had made a purchase.
The important part was that I needed everyone in my customer Data Extension, regardless of whether they had actually made a purchase.
Conceptually, the query might look something like this:
SELECT
c.CustomerID,
c.EmailAddress,
p.PurchaseDate
FROM Customers c
LEFT JOIN Purchases p
ON c.CustomerID = p.CustomerID
If I used an INNER JOIN, I would only get customers who had a matching purchase record.
That’s not what I needed.
I needed everyone from the Customers Data Extension, including the people who had never made a purchase.
That’s where the LEFT JOIN matters.
The question I try to ask myself now is:
Who absolutely needs to be in my final result?
If the answer is everyone in the Data Extension on the left side of the query, a LEFT JOIN may be what you need.
The dangerous part is that an INNER JOIN can be perfectly valid SQL and still give you the wrong audience.
Your query may run successfully.
It may return thousands of records.
And you may not realize that it quietly excluded everyone who didn’t have a matching record in the other Data Extension.
Understanding what your JOIN is actually doing is more important than simply remembering the syntax.
4. ROW_NUMBER() Changed How I Handled Duplicates
Another thing that unlocked a lot for me was learning how to use ROW_NUMBER() and ranking functions.
At one point, I was working with a lot of duplicate contacts.
Fixing the underlying data problem was going to take time.
A long time.
But marketing still had to happen in the meantime.
I needed a way to build Data Extensions that contained one usable record per person without waiting for the larger duplicate problem to be completely solved.
That’s when I learned how useful ranking could be.
Instead of simply hoping that DISTINCT would solve everything, I could define which record I actually wanted to keep.
For example, conceptually, you might want to group records by SubscriberKey, rank them by the most recent date, and keep only the newest record.
SELECT
SubscriberKey,
EmailAddress,
CreatedDate
FROM (
SELECT
SubscriberKey,
EmailAddress,
CreatedDate,
ROW_NUMBER() OVER (
PARTITION BY SubscriberKey
ORDER BY CreatedDate DESC
) AS RowNumber
FROM YourDataExtension
) AS RankedRecords
WHERE RowNumber = 1
In plain English:
- Group the records by person.
- Rank the records within each group.
- Decide which record should win.
- Keep that record.
That was a major unlock for me.
It didn’t solve the underlying duplicate problem. The data still needed to be cleaned up.
But it allowed me to build Data Extensions that didn’t contain duplicates and create usable audiences while the larger data problem was being addressed.
That’s an important distinction.
Sometimes SQL doesn’t fix the underlying problem.
Sometimes it helps you safely work with reality until the underlying problem can be fixed.
5. A Query That Works Today Has to Work Tomorrow
Getting a query to run successfully is only the beginning.
Once you start using SQL in Automation Studio, you need to think about what happens the next time the query runs.
And the time after that.
What is happening to your target Data Extension?
Are you:
- Overwriting it?
- Updating existing records?
- Appending new records?
Does the Data Extension have a primary key?
What happens if the query returns duplicate records tomorrow?
What happens if the source data suddenly doubles in size?
A query that works perfectly when you test it once can cause problems when it becomes part of an automated process.
This is one of the biggest advantages of SQL inside Marketing Cloud, but it’s also where you can shoot yourself in the foot.
Automation is incredibly powerful.
A good process gets repeated automatically.
Unfortunately, so does a bad one.
Before scheduling a query, I think it’s worth asking:
What happens if this runs every day for the next year?
That question forces you to think beyond whether the query works right now.
The Real Lesson
One of the things I enjoy most about working in Marketing Technology is discovering that the things you learned earlier in your career don’t disappear.
They just become useful in new ways.
My background in web development, database administration, and IT gave me skills that transferred surprisingly well into MarTech.
Learning that I could use SQL inside Salesforce Marketing Cloud was one of those moments that changed how I approached the platform.
Suddenly, a process that involved multiple exports, spreadsheets, pivot tables, and imports could become a query.
A duplicate problem that couldn’t be fixed overnight could be managed with ranking.
Multiple Data Extensions could be joined together to create exactly the audience I needed.
But Marketing Cloud has its own rules.
The goal isn’t to write the cleverest SQL query you can come up with.
It’s to understand your data, understand the platform, and build something predictable.
Something that still works tomorrow.
And preferably something that doesn’t shoot you in the foot.