The Automation That Ran Twice
An automation triggers when a record in an Orders table is created. It sends a confirmation email, creates a linked Shipment record, and updates a Status field to 'Processing'. The trigger is 'When record created' with a condition 'Status is empty'. During a busy week, customers reported receiving two confirmation emails, and the Shipments table shows duplicate shipment records for some orders. The team also noticed that orders imported in bulk overnight sometimes never got a shipment at all. The automation has run roughly 3,000 times this month against about 2,600 orders.
1. What are the plausible causes of duplicate emails and duplicate shipments, and how would you distinguish them?
Read the worked answer
Duplicates usually come from one of a few mechanisms:
- Multiple trigger firings: the record was edited in a way that re-satisfies the trigger, or two automations exist on the same table (one added by a teammate). Check the automation list for the table and each automation's run history.
- Retries on partial failure: if the email step succeeds but a later step (creating the shipment) fails, the automation may retry the whole run, re-sending the email. Run history will show failed runs followed by successful ones for the same record.
- Bulk import creating records twice (e.g., a re-run of an import), so two distinct records each trigger once - the duplicate is upstream, not in the automation.
Distinguish by inspecting run history per record ID: one record with two runs points to retries or a second automation; two records with one run each points to duplicate source data.
2. Why do overnight bulk imports sometimes produce no shipment, and what design change fixes it?
Read the worked answer
Bulk imports can create records faster than the trigger condition is evaluated, or the records may be created with Status already populated (so 'Status is empty' is false at trigger time), or the import may bypass the trigger depending on how records are written. The condition 'Status is empty' is fragile: if the import sets Status, the automation legitimately skips.
Design change:
- Do not gate the trigger on a field the import controls. Trigger on record creation and let the automation set Status itself; or use a dedicated 'Needs shipment' checkbox that the import explicitly leaves unchecked and the automation checks.
- Make the automation idempotent: before creating a shipment, search for an existing shipment linked to the order; if found, skip creation. This makes re-runs safe.
- Separate the email into its own step guarded by a 'confirmation sent' flag, so a retry after a later failure does not re-email.
3. The run count (3,000) exceeds the order count (2,600). What does that tell you, and what monitoring would you add?
Read the worked answer
The gap of ~400 runs means the automation fired more than once per order for a subset - consistent with retries or a second automation, not with a one-to-one trigger. It is a quantitative red flag that should be investigated before assuming the logic is correct.
Monitoring to add:
- Per-record run audit: a small table or log capturing order ID, run timestamp, and outcome, so duplicates are visible by counting runs per order.
- Alert on failure rate: if failed runs spike, retries (and duplicate emails) will follow.
- Reconciliation check: a scheduled automation or view comparing orders with no shipment against orders with more than one shipment, reviewed daily.
- Idempotency assertions in the automation itself (search-before-create) so monitoring confirms behavior rather than merely detecting damage.