Marketing Cloud SQL development
Query Activities that build the right audience every time: correct keys, no silent duplicates, and run times that hold up as your data grows.
In Marketing Cloud Engagement, most audiences are built by SQL. Query Activities in Automation Studio read data extensions and system data views, then write the results into a target data extension that a send or journey uses. The SQL is a subset of T-SQL with its own rules, and small mistakes have large consequences: a missing join condition doubles an audience, a wrong data action wipes a table, and a query that ran in seconds last year now times out.
GeekMidst writes, reviews and optimises Marketing Cloud SQL for teams that need their audience logic to be correct and explainable.
Where we help
- Audience building. Segment logic that combines profile, purchase and engagement data, with clear inclusion and exclusion rules.
- Deduplication. Picking one row per subscriber reliably using window functions instead of hoping the data is clean.
- Engagement reporting. Queries against data views such as sent, open, click, bounce and unsubscribe to produce reporting tables.
- Performance work. Rewriting slow queries, splitting large ones into staged steps and fixing primary key design on targets.
- Error diagnosis. Working out why a query fails validation, errors at run time or returns unexpected row counts.
Example: one row per subscriber
A common need is the latest order per subscriber in the last 90 days. The query below uses ROW_NUMBER() inside a derived table to keep only the most recent row. It assumes a data extension Orders_DE with SubscriberKey, EmailAddress, OrderNumber and OrderDate, and a target data extension whose primary key is SubscriberKey. Treat it as a pattern to adapt, not production code.
SELECT
x.SubscriberKey,
x.EmailAddress,
x.OrderNumber,
x.OrderDate AS LastOrderDate
FROM (
SELECT
o.SubscriberKey,
o.EmailAddress,
o.OrderNumber,
o.OrderDate,
ROW_NUMBER() OVER (
PARTITION BY o.SubscriberKey
ORDER BY o.OrderDate DESC
) AS rn
FROM Orders_DE o
WHERE o.OrderDate >= DATEADD(day, -90, GETDATE())
) x
WHERE x.rn = 1Without the window function, a subscriber with two orders would produce two rows, and the query would fail on the target's primary key or keep only one of them unpredictably. Our article on deduplicating with ROW_NUMBER covers tie-breaking and other edge cases, and the Marketing Cloud SQL Cheat Sheet collects the syntax we use most. Queries usually run inside larger automations, which our Automation Studio services cover.
Common problems and how we approach them
| Symptom | Likely cause | Our fix |
|---|---|---|
| Primary key violation | Duplicate keys in the result set | Deduplicate with a window function or fix the join |
| Audience larger than expected | Join on a non-unique field | Join on keys, check cardinality before and after |
| Query times out | Large scans, functions on filtered columns | Stage the work, filter early, simplify predicates |
| Target unexpectedly empty | Overwrite action with a failed or empty source | Staging table plus a verification step |
| Time-based logic off by hours | Server time zone differs from business time zone | Convert explicitly and document the assumption |
How a SQL engagement runs
- 01
Collect the logic
We document each audience or report in plain language with your marketers, including exclusions and edge cases.
- 02
Review the data
We check keys, field types and row counts in the source data extensions and data views involved.
- 03
Write and stage
Queries are written to test targets first, with intermediate staging tables where logic is complex.
- 04
Validate
Results are reconciled against expected counts and spot-checked record by record with your team.
- 05
Deploy and schedule
Queries move into production automations with the right data action, step order and error notifications.
- 06
Document
Each query gets a header comment and an entry in the automation documentation.
Questions we hear
Is Marketing Cloud SQL the same as SQL Server?
How long can a query run?
Can SQL read tracking data?
Can you fix our failing queries?
Get your audience SQL reviewed
Send us the queries that are slow, failing or hard to trust, and we will suggest how to fix them.
