SFMC Automation Studio and SQL Interview Questions and Answers

Automation Studio and Query Activity SQL are where a Marketing Cloud interview stops being about definitions. The panel wants to know whether you have run a nightly automation, watched it skip, and fixed it. They want you to write a join against _Sent and _Open without a browser open.

These questions come from real Marketing Cloud interview rounds collected by our trainers. The post covers the Automation Studio activity set, scheduled against file drop starting sources, the three write modes on a Query Activity target, Data View retention, and the SQL patterns that come up every time: non-openers, send performance, and deduplication.

Automation Studio

What is Automation Studio?

Automation Studio is the batch processing engine of Marketing Cloud. You build a workflow of activities, give it a starting source, and Marketing Cloud runs the activities in order on the server.

It is not Journey Builder. Journey Builder works on one contact at a time and holds them in a flow over days or weeks. Automation Studio works on a whole data set at once and finishes. Most production accounts use it to prepare the data Journey Builder then acts on: import the file, clean it with SQL, load the audience data extension, and let the journey pick it up.

What are the different activities in Automation Studio?

An automation is built from steps, and each step holds one or more activities. Activities inside the same step run in parallel with no guaranteed order between them. Steps run strictly in sequence.

Candidates lose marks here by listing the activities and stopping. Say which ones you have configured and on what. "I used Import File, SQL Query and Send Email in a daily loyalty automation" beats a complete list you cannot defend. Specificity is what creates credibility in this round.

What is a SQL Query Activity?

A Query Activity runs one SELECT statement against data extensions and Data Views in the current business unit, and writes the result set into a target data extension.

The part candidates get wrong is that Marketing Cloud SQL is a restricted subset of T-SQL. It runs on SQL Server, but almost everything outside a single SELECT is blocked. Not available in a Query Activity:

• INSERT, UPDATE, DELETE, MERGE, TRUNCATE. A Query Activity only reads. Writing happens through the target DE and the write mode you choose.
• Common table expressions. There is no WITH clause. Use a derived table in the FROM instead.
• Temp tables, table variables, DECLARE and SET. No variables of any kind.
• Stored procedures, user defined functions, cursors and dynamic SQL.
• ORDER BY in the outermost SELECT. Rows land in the target with no guaranteed order. ORDER BY inside a subquery with TOP, or inside an OVER clause, is fine.
• Cross business unit queries. A child BU sees only its own data, except for shared data extensions in an Enterprise 2.0 account, referenced with the ENT. prefix.

Available: joins of every type, subqueries and derived tables, UNION and UNION ALL, CASE, GROUP BY with HAVING, aggregates, and window functions such as ROW_NUMBER() OVER (PARTITION BY ...).

Two more rules. Column names in your result set must match the field names in the target DE, so alias every computed column. And the activity times out at 30 minutes, at which point it errors and writes nothing.

What is a File Import Activity?

An Import File Activity reads a delimited or fixed width file from the Enhanced FTP and loads it into a data extension or list. You define the file naming pattern, the delimiter, the column mapping and the import action.

The filename supports date placeholders such as %%YEAR%%, %%MONTH%% and %%DAY%%, which is how a daily automation points at a file carrying yesterday's date in its name. Bad rows go to an error file you collect from the FTP.

What is a Data Extract Activity?

A Data Extract Activity converts data into a file on the Marketing Cloud Safehouse. The two types that matter in interviews are Data Extension Extract, which writes the rows of a DE to a CSV, and Tracking Extract, which produces the standard sent, open, click, bounce and unsubscribe files for a date range. The Safehouse is not the FTP, so the file is not visible to your vendor until you move it.

What is a File Transfer Activity?

A File Transfer Activity has two directions. Move a File from Safehouse puts the file a Data Extract produced onto the Enhanced FTP account so an outside system can collect it. Manage File takes a file already on the FTP and unzips or decrypts it with the PGP key from Key Management, so an Import File Activity can read it.

What is the role of the Data Extract and File Transfer pairing?

This is the standard export pattern: Data Extract writes to the Safehouse, File Transfer moves that file to the FTP directory the vendor has credentials for.

One activity without the other is the most common broken export in a real account. The extract succeeds, the automation shows green, and the vendor reports no file. Say that in the interview. It shows you have seen the failure, not just read the documentation.

What is a Script Activity?

A Script Activity runs server-side JavaScript inside the automation. It is the escape hatch for logic SQL cannot express: calling a REST endpoint, or reading and writing data extensions through the Platform and Core libraries. It is also the hardest activity to debug, because errors surface only as a failed step. Use SQL wherever SQL can do the job.

What is Scheduled Automation?

A scheduled automation has a Schedule starting source. You set the start date, the time zone, the repeat pattern and the end condition, and Marketing Cloud runs the automation on that clock whether or not there is anything to process.

Two things interviewers probe. The schedule runs in the time zone you selected, which in many Indian accounts is not the account default, so a 2 AM job can run at a surprising hour. And if a run is still going when the next one is due, the new run does not queue. It is marked Skipped.

What is Triggered Automation?

A triggered automation has a File Drop starting source. You point it at a directory on the Enhanced FTP account and give it a filename pattern, and the automation starts when a matching file lands in the folder. The pattern options are exact match, contains, begins with and ends with.

Because the pattern is the trigger, the file naming convention is part of the design. A fixed prefix with a date suffix keeps the match reliable, stops a new file overwriting one that has not been processed, and lets you tell from the FTP listing which day's file failed.

Scheduled against file drop, the way to answer it:

How do you ensure an automation runs only when new data is available?

Three answers, and a good candidate gives all three.

Use a File Drop starting source, so the automation only runs when a file exists. Where the trigger has to stay scheduled, make the first step a Verification Activity pointed at the staging data extension, set to stop the automation if the row count is zero. And where the source updates a DE rather than dropping a file, compare a modified date against a control DE holding the last run timestamp, and verify against that.

What happens if you modify a SQL activity inside a running automation?

The running instance keeps executing against the definition it already loaded. Your edit does not apply mid run. It takes effect from the next execution.

The practical answer is that you do not do this. Pause the automation, make the change, validate the query and test it against a copy of the target, then resume. Editing a query while its automation runs is how a team ends up with a target DE holding half of yesterday's logic and half of today's, with no record of which.

How do you troubleshoot a failed automation?

Work from the outside in. Candidates lose this question by describing what an automation is instead of what they check. Being able to debug beats being able to define, every time.

1. Open Automation Studio and switch to the Activity view. It shows every run with its status: Complete, Error, Running, Skipped or Stopped.
2. Click the failed run. Marketing Cloud names the step and activity that failed, with the error text.
3. Read the error against the activity type. A Query Activity gives a SQL error or a timeout. An Import File Activity gives file not found, a delimiter mismatch, or a row error count with the bad rows written to the FTP.
4. Check the target. A primary key violation on an Append, and a field too short for the incoming value, are the two most common write failures.
5. Reproduce in isolation. Run the query by itself in Query Studio or against a scratch DE.

Automations also carry notification settings per automation, where you add email addresses for error, completion and skip events. Configure error and skip together, because a skip is silent by default.

Now the harder version: the automation did not fail, it skipped. Skipped means Marketing Cloud reached the start time and did not start the run. Check in this order:

• The previous instance was still running. This is the single most common cause and it is what Skipped literally means. A query that grows past its window pushes every later run into a skip.
• The automation is paused, or its schedule end date has passed.
• For a file drop automation, no file matched the pattern, the file landed in a different directory, or the name changed at source. Check the FTP listing and the file drop configuration side by side.
• A matching file arrived while a previous instance was still processing.

Say the words "the prior run had not finished" in the interview. That one sentence separates people who have operated an account from people who have read about one.

What are Automation Studio limitations?

• No conditional branching. Steps run in order. The nearest thing is a Verification Activity that stops the automation.
• One instance at a time. An overlapping run is skipped, not queued.
• Query Activities time out at 30 minutes.
• No native retry. A failed activity stops the automation and later steps do not run.
• Activities in the same step run in parallel with no guaranteed order, so a query that depends on another query must sit in a later step.
• No version history on a query. Once you save over it, the previous SQL is gone.
• Chaining automations is indirect. You end one with a Data Extract or a control DE write, and use that as the next one's starting condition.

SQL and Data Views

How many days of history do Data Views store?

Most tracking Data Views hold a rolling six months. _Sent, _Open, _Click, _Bounce, _Unsubscribe, _Complaint and _Job all work this way. As each day passes, the oldest day drops off.

It is a rolling window because Data Views are a read layer over the platform's own tracking store, not tables you own. Marketing Cloud sizes that store for operational reporting, not archiving. Nothing warns you when data ages out; the rows simply stop being returned.

The consequence is the real answer, and it is what the panel waits for. If the business needs engagement history beyond six months, you build it. A scheduled automation queries the Data Views into your own data extension in Append or Update mode, every day or every week. That DE is yours and has no retention limit.

Not every Data View is time bound. _Subscribers and _ListSubscribers hold the current state of the All Subscribers list and are not trimmed by date.

What is the difference between a Data View and a Data Extension?

A data extension is a table you create, own and write to. You control its fields, primary key and retention.

A Data View is a system table exposed by Marketing Cloud, always named with a leading underscore, holding platform tracking and subscriber data. You cannot see it in Contact Builder, change it, write to it or send to it. The only way to reach it is a Query Activity or Query Studio. To use its data anywhere else, you query it into a data extension first.

What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only rows matching on both sides. A LEFT JOIN returns every row from the left table and fills the right side with NULL where there is no match.

In Marketing Cloud this is not academic, because the negative audience is built from it. Sent and did not open, opened and did not click, in the master DE with no purchase record: all of these are a LEFT JOIN with IS NULL on the right side. An INNER JOIN can never produce them.

Write a query for non-openers in the last 90 days.

The most asked SQL question in a Marketing Cloud interview. It joins _Sent to _Open and keeps the rows with no match.

SELECT DISTINCT
        s.SubscriberKey
FROM    _Sent s
LEFT JOIN (
        SELECT DISTINCT SubscriberKey
        FROM   _Open
        WHERE  EventDate >= DATEADD(DAY, -90, GETDATE())
) o
        ON o.SubscriberKey = s.SubscriberKey
WHERE   s.EventDate >= DATEADD(DAY, -90, GETDATE())
AND     o.SubscriberKey IS NULL

Three things to explain while you write it.

The _Open side is a derived table with its own DISTINCT, not a direct join to _Open. A subscriber can open one email forty times. Joining the raw view would multiply the _Sent rows before the IS NULL test runs, and on a large account that is the difference between a query that finishes and one that hits the 30 minute timeout.

The date filter appears on both sides. Filtering only _Sent would exclude a subscriber whose single open was ninety-one days ago, which is not what the audience means.

And the result is subscribers who received something and did not open it, not everyone who did not open. Someone never sent an email is unmailed, not a non-opener. Starting from _Sent is what makes the audience correct.

How do you join Send, Open and Click Data Views?

Join on JobID plus SubscriberKey. JobID identifies the individual send; without it you match a click from one campaign against a send from another. This query also brings in _Job for the email name and _Subscribers for the email address.

SELECT  s.SubscriberKey,
        sub.EmailAddress,
        j.EmailName,
        CONVERT(DATE, s.EventDate) AS SendDate,
        CASE WHEN o.SubscriberKey IS NULL THEN 0 ELSE 1 END AS Opened,
        CASE WHEN c.SubscriberKey IS NULL THEN 0 ELSE 1 END AS Clicked
FROM    _Sent s
JOIN    _Job j
        ON j.JobID = s.JobID
JOIN    _Subscribers sub
        ON sub.SubscriberKey = s.SubscriberKey
LEFT JOIN (
        SELECT DISTINCT JobID, SubscriberKey FROM _Open
) o
        ON o.JobID = s.JobID
       AND o.SubscriberKey = s.SubscriberKey
LEFT JOIN (
        SELECT DISTINCT JobID, SubscriberKey FROM _Click
) c
        ON c.JobID = s.JobID
       AND c.SubscriberKey = s.SubscriberKey
WHERE   s.EventDate >= DATEADD(DAY, -30, GETDATE())

_Job is the send metadata view, one row per send job, carrying EmailName, EmailSubject, SchedTime and DeliveredTime. _Subscribers is the All Subscribers view, carrying EmailAddress, Status, DateJoined, DateUnsubscribed and BounceCount. Together with _Sent they give a per-send engagement row you can write to a reporting DE.

Note that the CASE expressions are aliased. An unaliased computed column has no name to map to a target field, and the activity fails on write.

How do you extract unique opens for a specific email in the last 30 days?

_Open holds one row per open event, so a raw count is total opens. Unique opens means distinct subscribers. The email is identified by joining _Job.

SELECT  j.EmailName,
        COUNT(DISTINCT o.SubscriberKey) AS UniqueOpens,
        COUNT(*)                        AS TotalOpens
FROM    _Open o
JOIN    _Job j
        ON j.JobID = o.JobID
WHERE   j.EmailName = 'Diwali_Offer_2026'
AND     o.EventDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY j.EmailName

If that email was sent several times inside the window, this aggregates across all of those jobs, because EmailName is shared. Group by o.JobID as well when you need the figure per send.

How do you find duplicate subscriber keys?

SELECT  SubscriberKey,
        COUNT(*) AS RecordCount
FROM    Staging_Customers
GROUP BY SubscriberKey
HAVING  COUNT(*) > 1

Run this against the staging DE before the load, not after. A staging DE deliberately has no primary key so the raw file lands intact, which is exactly why duplicates can sit in it.

How do you retrieve the latest record per subscriber key?

This is the deduplication query, and it is the one to have ready. ROW_NUMBER() with PARTITION BY numbers the rows inside each key, newest first, and the outer query keeps number one.

SELECT  x.SubscriberKey,
        x.EmailAddress,
        x.City,
        x.ModifiedDate
FROM (
        SELECT  SubscriberKey,
                EmailAddress,
                City,
                ModifiedDate,
                ROW_NUMBER() OVER (
                    PARTITION BY SubscriberKey
                    ORDER BY     ModifiedDate DESC
                ) AS RowNum
        FROM    Staging_Customers
) x
WHERE   x.RowNum = 1

Two points the interviewer listens for. The window function has to sit in a derived table, because Marketing Cloud SQL has no WITH clause and you cannot filter on an alias in the SELECT that creates it. And ORDER BY inside the OVER clause is allowed even though ORDER BY on the outer SELECT is not.

If ModifiedDate can tie, add a second sort key, otherwise which row survives is arbitrary.

What is Overwrite against Update against Append in a SQL activity?

The write mode decides what happens to the target data extension when the result arrives. Getting it wrong is how audiences get destroyed, so expect the panel to push.

Behaviour worth stating out loud. Update needs a primary key on the target; if the DE has none, Update behaves like Append and you silently accumulate duplicates. Append does not deduplicate, and if the target does have a primary key, a duplicate key in the incoming set makes the activity error. Only Overwrite and Update give a clean current-state table.

What is the impact of Query Activity Overwrite mode?

Overwrite deletes the contents of the target before writing, and that has consequences beyond the row count.

If the query returns zero rows, the target ends up empty. A bad WHERE clause on an Overwrite query against a sendable DE empties the audience, and the next step sends to nobody or fails. There is no undo and no copy of the previous contents.

The mitigation is to stage. Write the result to a staging DE in Overwrite, put a Verification Activity after it checking the staging row count is above a sensible floor, and only then run a second query in Update mode into the live audience DE. Verification stops the automation before the damage reaches production.

What is the impact of a primary key on SQL updates?

The primary key is the matching rule for Update mode. Marketing Cloud compares the primary key of each incoming row against the target and updates where it finds a match.

So the primary key defines your grain. A single key of SubscriberKey means one row per subscriber, replaced by every new result. A composite key of SubscriberKey plus JobID means one row per subscriber per send, which is what an engagement history table needs. A primary key field cannot be nullable, and two rows with the same key in one result set fail the activity. That is why deduplication runs before the load, not after.

How do you prevent data duplication in joins?

Row multiplication in a join is the most common cause of a wrong audience count, and of a query that worked in testing and times out in production.

• Know the grain of every table before you join. _Sent is one row per subscriber per send. _Open and _Click are one row per event, so many rows per subscriber per send.
• Join event views through a derived table with SELECT DISTINCT JobID, SubscriberKey, as in the queries above.
• Join on the full key. SubscriberKey alone across two send-level tables mixes campaigns; include JobID.
• Aggregate first, then join. Where you need a click count, build a GROUP BY subquery returning one row per key and join to that.
• Use DISTINCT as a check, not a fix. If removing it changes the count, find the join that is multiplying and repair it.

How do you optimize large dataset queries?

The 30 minute timeout is a hard wall, so this is not optional on a large account.

• Filter early. A date restriction applied inside a derived table cuts the working set before the join, not after.
• Select only the columns the target needs. Never SELECT * into a query target.
• Join on indexed fields. Primary key fields in a data extension are indexed, so a join on the primary key is far cheaper than one on an unindexed text column.
• Break one large query into staged queries. Write an intermediate result to a working DE in one step and join against that smaller table in the next. Two queries of five minutes beat one that never finishes.
• Query Data Views for the last thirty or ninety days into your own history DE on a schedule, then report against your DE instead of scanning six months every night.
• Watch wildcards. A leading wildcard in LIKE '%value' cannot use an index and scans the whole table.

What the interviewer is actually checking

• Whether you have operated an automation or only built one. Anyone can drag a Query Activity onto a step. Knowing that Skipped means the previous instance was still running is operational experience.
• Whether you know the boundaries of Marketing Cloud SQL. Candidates who learned SQL elsewhere reach for a CTE, a temp table or a variable, and the panel sees at once that they have never run a Query Activity.
• Whether you can reason about row grain. The non-opener query tests whether you understand that _Open has many rows per subscriber, not whether you remember LEFT JOIN syntax.
• Whether you protect production. Overwrite against a live sendable DE with no verification step is the answer of someone who has never emptied an audience by accident.
• Whether you know when and where your code runs, not only how to write it. A query that is valid in Query Studio and fails in an automation is a question about execution, and that is the gap most candidates fall into.

Where to take this next

Write the non-opener query, the three-view join and the deduplication query out by hand until you can produce them without thinking, and be able to say why each derived table is there. Then pick one automation you have genuinely built and walk through its steps, its starting source, its write modes, and the day it failed.

Our Salesforce Marketing Cloud training covers Automation Studio and Query Activity SQL on a live training account, so you build the automations and write the queries yourself rather than reading about them. That is the difference candidates feel in the room.