Overwrite, Update, Append: the SQL queries that succeed while writing the wrong data

A health insurer sends a reminder every morning to members whose premium falls due within seven days. An automation imports the due dates, a Query Activity feeds the Cible_Relance data extension, and a Journey Builder journey starts on that table. The setup has run in Overwrite mode for two years.

In the spring, marketing asks to keep a record of the members who were chased. A developer switches the activity to Update mode, without touching the query. Nothing breaks, testing passes, the people selected are the right ones.

Three weeks later, the service desk takes calls from members who are fully paid up and get a reminder every morning. Update mode adds and updates, it never deletes. Members who had paid stayed in Cible_Relance with their old due date, and the journey, which reads the table on every run, picked them up again.

Three modes, and three things they never do

The Salesforce documentation describes each mode in one sentence. The column that matters in production is the third one.

Choosing the mode from the table

The choice follows from what the table is for, not from the query. A send audience or a working table has to reflect the present state, so Overwrite. A reference table enriched piece by piece, where you want to keep rows that today's query no longer sees, calls for Update. Append only suits logs where every row is new by construction, and even then: an Update on a well chosen key does the same job while staying replayable after an incident.

Note the blind spot in the model: no mode deletes rows selectively. To remove rows from a data extension, you rewrite the whole table in Overwrite.

The primary key decides, and it does not always warn you

Update mode relies on the target's primary key to choose between updating and adding. With no primary key, it has no way of recognising an existing row. Behaviour with duplicates differs by mode, and the most used mode is the most misleading.

In the insurer's case, a member with two due dates in the same week produces two rows for the same key. Writing the rule into the query costs three lines of SQL and avoids a value picked at random.

SELECT
    a.AdherentId,
    a.Email,
    LEFT(a.Prenom, 50)                    AS Prenom,
    MIN(e.DateEcheance)                   AS DateEcheance,
    CAST(SUM(e.Montant) AS DECIMAL(10,2)) AS Montant
FROM Echeances AS e
INNER JOIN Adherents AS a
    ON a.AdherentId = e.AdherentId
WHERE e.Statut = 'A_PAYER'
  AND e.DateEcheance >= CAST(GETDATE() AS DATE)
  AND e.DateEcheance <  DATEADD(DAY, 8, CAST(GETDATE() AS DATE))
  AND a.Email IS NOT NULL
GROUP BY a.AdherentId, a.Email, a.Prenom

Keeping the reminder history apart

The amount becomes the total owed over the week, which the team writing the email needs to know. The traceability request belongs in a second table: an activity reads Cible_Relance and writes into Historique_Relance in Update mode, with a key made of the member identifier and the due date. A member chased seven days running for the same due date takes up one row, and a manual rerun after an incident rewrites the same keys instead of duplicating them. The documentation notes in passing that a primary key must not exceed 1,700 bytes and that each extra key slows writes down: two or three short fields stay reasonable.

A column without an alias goes nowhere

The activity matches result columns to target fields by name, and by nothing else. Four constraints follow.

• Every computed column carries an alias equal to the target field name, otherwise it has no name and the activity does not know where to write it.
• Every value fits inside the field length, otherwise the whole run fails on a truncation, not just the offending row.
• Every required field gets a non null value, otherwise the run fails.
• Types are compatible, on pain of implicit conversion or failure.

A target field missing from the query stays empty, or takes its default value if it has one. Handy for a load date, dangerous for a required field. The LEFT(a.Prenom, 50) in the query above comes from the same habit: a text length can be increased on a data extension, never reduced, so truncate explicitly at the source.

Salesforce also documents a known issue with decimals: a Decimal field used in a query does not necessarily come back as a decimal. Check the amounts after the first run and convert explicitly, as the CAST above does.

Zero rows, status complete, empty send

Overwrite mode has a downside. If the morning file arrives empty, the query returns zero rows, the activity completes without error, and Cible_Relance is emptied. Nobody gets chased, and no alert goes out.

Checking the volume with a Verification Activity

Automation Studio offers a Verification Activity, which evaluates a data extension against conditions you define, then stops the automation, sends a notification, or both. You place it straight after the audience query, with a condition on the row count. The upper bound matters as much as the lower one: a join that has gone wrong multiplies rows without raising an error.

To keep a numeric record, a small query in Update mode writes the day's volume into a control table.

SELECT
    'Cible_Relance'                 AS NomTable,
    CAST(GETDATE() AS DATE)         AS DateControle,
    COUNT(*)                        AS NbLignes
FROM Cible_Relance

With NomTable and DateControle as the primary key, the table accumulates one row per day, and a rerun on the same day simply updates that row. COUNT(*) always returns a row, even on an empty table, and that is exactly what makes the record reliable. One last point on the lifespan of the setup: Salesforce suspends a Query Activity after a system error or 24 consecutive failures. A query broken by a renamed field eventually stops running altogether, and recovery means fixing the cause, then saving the activity again.

If you are coming from Adobe Campaign

In an Adobe Campaign workflow, the update data activity keeps the operation type explicit, insert, update, insert or update, delete, and separate from the reconciliation keys, which you choose every time. In Marketing Cloud, reconciliation always rests on the primary key of the target data extension, and no mode deletes rows.

A Marketing Cloud query also mixes two roles that Adobe Campaign keeps apart, selection and writing. That is why the write mode belongs to the activity definition rather than to the next step, and why it can be changed without changing a line of SQL. Which is exactly what happened at the insurer.

The habit worth keeping

When a target table changes role, the write mode changes with it. Before switching an activity, ask a simple question: what should disappear from this table when the query stops returning it? If the answer is everything that is no longer current, the mode is Overwrite, and traceability goes somewhere else.

Frequently asked questions