A health insurer sends a payment reminder every morning to members behind on their contributions. The automation starts at 5 am, collects two files from the SFTP, imports them, builds the audience by excluding cancelled policies, then launches the send. It has run for two years without drawing attention.
One Tuesday, the process that produces the cancelled policies file is interrupted by maintenance. The file dropped holds 14 rows instead of around 9,000. The import, set to Overwrite straight onto the production table, replaces the full list with those 14 rows. The audience query runs without error. The send goes out. Around 9,000 former members receive a payment request for a policy they cancelled.
The Automation Studio dashboard shows a successful run. No alert went out, since no activity failed. Customer service finds out at 9 am, on the first call.
An import of 14 rows is a successful import. A query that writes zero rows is a successful query. Automation Studio monitors how each activity runs, not whether the data passing through it makes sense. Read the statuses it reports with that limit in mind.
| Status | Meaning | Expected action |
|---|---|---|
| Error | An error occurred during the run | Investigate the failed activity, fix it, run it again |
| Stopped | The automation was stopped during its last run | Read the warnings in the activity log |
| Skipped | The run was skipped | Run it by hand or wait for the next occurrence |
| Paused | Paused manually, it will not run again until reactivated | Check reactivation after every intervention |
The errors the platform can detect are documented: duplicates on the primary key and the 30 minute limit for queries, a missing or empty file for imports, rejected SFTP credentials for transfers. Script activities carry the same 30 minute limit. Nothing in that list covers a perfectly formed but incomplete file.
⚠️ The dashboard trap: a green run says that every activity finished, not that the data is good. A volume check is the only thing that tells those two apart.
An automation is a sequence of steps, and each step holds one or more activities. Trailhead puts the warning the other way round: an activity that reads a data extension must not sit in the same step as the query that fills it, or it will find nothing. Activities in a single step do not wait for one another, so anything that depends on a result goes into a later step.
Once that split is clear, the insurer's automation takes a different shape.
| Step | Activities | Why |
|---|---|---|
| 1 | Transfers of the two files | Independent of each other, they can share a step |
| 2 | Imports into the two staging tables | The files have to land before the import |
| 3 | Volume control query | It reads the staging tables filled in step 2 |
| 4 | Verification activities | Alone in their step, as this activity type requires |
| 5 | Update of the reference table and log | The data is validated, the two writes are independent |
| 6 | Audience query | It reads the reference table updated in step 5 |
| 7 | Email send | The audience is ready and checked |
Name activities with a prefix per type (QRY_, IMP_, FTP_, VER_) and repeat the automation name in the description. Six months later, facing a list of two hundred activities, that convention is what lets you read an automation without opening it.
The real fix rests on one principle: a file never overwrites a table the send depends on. Data arrives in a staging table, the checks run on that table, and the reference table is updated only afterwards, by a query.
IMP_Resilies : file -> Resilies_Import (Overwrite)
VER_Resilies_Min : Resilies_Import < 1,000 rows -> stop
QRY_Resilies_Reference : Resilies_Import -> Resilies (Overwrite)The benefit shows on the day of the incident. Because the reference table has not moved, a full rerun once the right file lands carries no risk, and you do not have to rebuild the previous day from an export.
The Verification activity evaluates a data extension against conditions you set, most often a minimum or maximum row count. When the condition is met, it stops the automation, sends an email, or both, with a note you write. It comes with a few documented constraints: it belongs to a single automation and cannot be reused elsewhere, several Verification activities can share a step but no other activity can sit there, and it can target any data extension, not only those written by the automation's activities.
Its check is limited to a row count. For a finer rule, a volume that collapses against the reference table, a negative amount, an inconsistent date, you combine two objects: a query that writes a row only when the rule is broken, and a Verification that stops the automation as soon as that control table is not empty.
SELECT
'RESILIES_VOLUME' AS Controle,
CONCAT('Import : ', i.NbImport,
' lignes, reference : ', r.NbReference,
' lignes') AS Detail,
GETDATE() AS DateControle
FROM (SELECT COUNT(*) AS NbImport FROM Resilies_Import) AS i
CROSS JOIN
(SELECT COUNT(*) AS NbReference FROM Resilies) AS r
WHERE i.NbImport < r.NbReference * 0.8
UNION ALL
SELECT
'RETARDS_MONTANT' AS Controle,
CONCAT(COUNT(*), ' montants nuls ou negatifs') AS Detail,
GETDATE() AS DateControle
FROM Retards_Import
WHERE Montant <= 0
HAVING COUNT(*) > 0Each subquery returns a single row, which makes the CROSS JOIN safe. The first part returns nothing while the volume stays normal, the second returns nothing while no amount is abnormal, thanks to the HAVING. The 80 per cent threshold is a business choice. A volume of cancelled policies rarely drops by a few per cent overnight, and beyond that, better to have a human look before the send.
The setup relies on one behaviour to confirm on your own account: in Overwrite mode, a query that returns no rows should leave the control table empty, and let the rest run. Force that case in testing before counting on it.
💡 Two safeguards beat one: the 80 per cent rule protects nothing when the reference table is itself empty, on first go live for instance. Add an absolute row count threshold on each staging table.
When the checks pass, a query writes one row per table into a run log, in Append mode and with no primary key, to keep a trace of each run.
SELECT 'AUTO_Relance_Cotisation' AS Automation,
'Resilies_Import' AS Etape,
COUNT(*) AS NbLignes,
GETDATE() AS DateExecution
FROM Resilies_Import
UNION ALL
SELECT 'AUTO_Relance_Cotisation',
'Retards_Import',
COUNT(*),
GETDATE()
FROM Retards_ImportIn a UNION ALL, the column names come from the first query, so aliases at the top are enough. After a few weeks, that log gives the usual volume of each file, and your thresholds rest on figures rather than on intuition. Plan a retention setting on the data extension so it does not grow without end, and remember that GETDATE() returns the Marketing Cloud server time, not your local time.
Each automation has its own notification settings, with the addresses to alert when a run is skipped, when it hits an error or when it completes, and an optional note added to the message. Point them at a shared team mailbox, never at the address of the consultant who built the automation, and test the alert by causing an error on purpose in a test environment. Salesforce also offers the Event Notification Service to push those events by webhook to a monitoring tool.
On the starting side, three sources exist: the schedule, a file landing on the Enhanced FTP, and the trigger on external storage such as AWS S3 or Azure. For the last two, files are queued by default, each waiting its turn. Disable the queue and the files arriving during a run are ignored, which is a fine way to lose a drop without knowing it.
The recovery procedure is written before you need it, and it fits in a few lines:
An automation plays the role of a technical workflow, with a structural difference that throws people at first: no transitions and no branches, just a list of steps. Conditional branching does not exist, and you replace it with a Verification activity or with a script that decides what comes next.
In Campaign, the workflow execution properties let you choose between suspend and ignore on error, and an activity can carry an error transition. The volume check you used to run with a test on the record count of a transition becomes a Verification activity, placed alone in its step, and your alerts are set automation by automation.
Faced with an automation that feeds a send, look for the place where an incomplete file would pass unnoticed, and ask the question out loud: what happens if this morning's drop holds ten rows? As long as the answer is "the send goes out anyway", the missing check costs less to write than a morning on the phone with customer service.
Automation Studio monitors how each activity runs, not whether the data passing through it makes sense. An import of 14 rows is a successful import, and a query that writes zero rows is a successful query. A green run therefore says that every activity finished, not that the data is good. Only a volume check tells those two cases apart.
The documentation lists duplicates on the primary key and the 30 minute limit for queries, a missing or empty file for imports, and rejected SFTP credentials for transfers. Script activities carry the same 30 minute limit. Nothing in that list covers a perfectly formed but incomplete file.
The principle is that a file never overwrites a table the send depends on. Data arrives in a staging table, the checks run on that table, and the reference table is updated only afterwards, by a query. On the day of the incident the reference table has not moved, so a full rerun once the right file lands carries no risk, and you do not have to rebuild the previous day from an export.
The Verification activity evaluates a data extension against conditions you set, most often a minimum or maximum row count, then stops the automation, sends an email, or both. Its check is limited to a row count, and it belongs to a single automation and cannot be reused elsewhere. For a finer rule, you combine a query that writes a row only when the rule is broken with a Verification that stops the automation as soon as that control table is not empty.
The recovery procedure is written before you need it. It means reading the anomaly table and the run log to identify the cause, obtaining a corrected file or deciding not to send that day, restarting the automation with Run Once once the file has been dropped again, checking in the log that volumes are back to normal, then recording the incident and adjusting the thresholds if needed.
I can review your production automations, their volume checks and their alerts, then hand you a written diagnosis with the safeguards to add.
Tell me about your context →