SFMC Query Activity: Overwrite vs Update vs Append

Overwrite, Update or Append? Learn how each SFMC query activity data action behaves, which primary key it needs, and how to avoid wiping or duplicating data.

You have written a working query, and now Automation Studio asks you to pick a data action: Overwrite, Update or Append. Choose wrong and you either wipe a Data Extension you wanted to keep, or pile up duplicate rows you will spend a week cleaning.

The short answer for SFMC query activity overwrite vs update vs append: use Overwrite when the target should always mirror the latest query result, Update when you want to add new rows and refresh existing ones by primary key, and Append when you only ever add rows, like an event log.

This guide explains how each data action behaves, shows one SQL example per mode with the target Data Extension and primary key, and ends with a checklist you can copy. If you would rather skip writing the SQL itself, you can also generate SFMC SQL from plain English and keep this article for the data action decision.

‍

Overwrite vs update vs append: what each data action does

‍

The data action is the setting on a query activity that tells SFMC what to do with the rows your query returns. Salesforce lists three options on the activity: Append, Update and Overwrite (see the Trailhead module on querying data with SQL).

‍

Overwrite replaces everything

Overwrite clears the target Data Extension and writes the new result set. After the run, the target contains exactly what the query returned, nothing more. That makes it the natural choice for audiences, because an audience should reflect who qualifies today, not who qualified last month.

The catch is that an empty result also empties the target. If your query returns zero rows on a given day, your send audience is empty too.

‍

Update adds new rows and refreshes existing ones

Update matches each returned row to the target using its primary key. Rows with a new key are added, and rows with an existing key are updated with the new values. Rows already in the target that the query did not return are left untouched.

This only works if the target Data Extension has a primary key, and the query must return that key column. It suits master tables and scoring tables that grow and change over time.

‍

Append only adds rows

Append adds every returned row to the end of the target without touching existing data. It is the right fit for logs and history tables, where each run contributes a new slice of records.

Because Append does not deduplicate for you, running the same query twice can load the same rows twice, or fail if the target has a primary key and a returned row collides with an existing one. Plan the date window so each run covers a distinct period.

‍

SFMC query activity setup: Overwrite example for a daily audience

‍

A common marketing use case is a rolling audience, such as customers who ordered in the last 30 days. The audience changes daily, so Overwrite is the correct data action.

SELECT
    c.SubscriberKey,
    c.EmailAddress,
    c.FirstName,
    MAX(o.OrderDate) AS LastOrderDate
FROM Customers_Master c
INNER JOIN Orders o
    ON o.SubscriberKey = c.SubscriberKey
WHERE o.OrderDate >= DATEADD(day, -30, GETDATE())
GROUP BY c.SubscriberKey, c.EmailAddress, c.FirstName

Target Data Extension: Recent_Buyers. Primary key: SubscriberKey. Data action: Overwrite. Here is what each part does:

SELECT lists the four columns the target needs: SubscriberKey, EmailAddress, FirstName and the most recent order date.
FROM and INNER JOIN connect the customer master to the Orders table on SubscriberKey, so only customers with at least one order survive.
The WHERE clause uses DATEADD and GETDATE to keep orders from the last 30 days. Because it is relative to today, the audience moves with the calendar.
GROUP BY with MAX collapses several orders into one row per customer, which protects the primary key from duplicates.

If you are building more audiences like this, the SFMC SQL query examples for marketers post has more patterns you can adapt.

‍

Update example: keeping a customer score table fresh

‍

Imagine a Data Extension that stores one row per customer with recent order counts and revenue. You want to refresh customers who ordered recently without deleting everyone else. That calls for Update.

SELECT
    o.SubscriberKey,
    COUNT(*) AS Orders90d,
    SUM(o.OrderTotal) AS Revenue90d
FROM Orders o
WHERE o.OrderDate >= DATEADD(day, -90, GETDATE())
GROUP BY o.SubscriberKey

Target Data Extension: Customer_Scores. Primary key: SubscriberKey. Data action: Update. Line by line:

SubscriberKey is returned so SFMC can match each row to an existing record in the target.
COUNT and SUM compute the order count and revenue for the last 90 days, written to Orders90d and Revenue90d.
The WHERE clause limits the scan to 90 days, which keeps the run time down.
GROUP BY gives one row per customer, so each key appears once per run.

A pitfall to know: customers with no orders in the last 90 days are not returned, so their old values stay in the table. If stale values would mislead a segment, either use Overwrite with a query that returns every customer, or add a second step that resets them.

‍

Append example: building an engagement history table

‍

Data views such as _Open only keep a limited window of history, so teams copy events into their own Data Extension. That is a textbook Append job. For background on these tables, see the guide to querying SFMC data views with SQL.

SELECT
    o.SubscriberKey,
    o.JobID,
    o.EventDate,
    GETDATE() AS LoadedAt
FROM _Open o
WHERE o.IsUnique = 1
    AND o.EventDate >= DATEADD(day, -1, GETDATE())

Target Data Extension: Open_History. Primary key: none, or a composite of SubscriberKey, JobID and EventDate if you want collisions to be rejected. Data action: Append. Explanation:

The query reads unique open events from the _Open data view, using the IsUnique flag to skip repeat opens.
EventDate with DATEADD keeps only the last day, so a daily run adds one new day each time.
GETDATE stamped as LoadedAt records when each row was copied, which helps with audits.
Schedule the automation once per day. Running it twice in one day would load that day twice.

‍

Quick answer: which data action should you pick?

‍

Use this table before you configure the activity. It covers the questions that decide the choice.

Situation	Data action	Primary key needed
Audience that must match today's result exactly	Overwrite	Recommended
Master or scoring table that grows and changes	Update	Yes
Log or history table that only grows	Append	Optional
Query might return zero rows and the target must survive	Update or Append	Depends

And a short checklist to run through each time:

Decide whether the target should mirror the query result or accumulate over time.
Confirm the target Data Extension has the right primary key, and that your query returns it.
Make sure the query returns one row per primary key.
Pick the data action, then run the query once manually and check the row count.
Schedule it, and avoid having another automation read the target while it is being written.

‍

Common pitfalls with query activity data actions

‍

Timeouts and heavy updates

Query activities stop after 30 minutes, according to Salesforce Trailhead. Larger result sets and wide joins make that limit easier to hit, so select only the fields you need rather than using SELECT * and filter early with a date condition.

In practice, Update tends to take longer than Overwrite on large tables because every row must be matched to the target. If a heavy Update keeps timing out, consider whether Overwrite on a smaller, purpose-built Data Extension would do the job.

‍

Mismatched columns and data types

The columns your query returns must line up with the target Data Extension by name and compatible type. A renamed alias, a text value going into a number field, or a value longer than the field length are the usual causes of failed runs. Name aliases to match the target exactly.

For naming and structure habits that prevent this, read SFMC Data Extensions: best practices for clean data.

‍

FAQ

‍

What is the difference between Overwrite and Update in SFMC?

Overwrite deletes the target contents and loads the query result, so the target ends up identical to the result. Update keeps existing rows, adds new keys and refreshes matching keys. Rows the query no longer returns stay in the target under Update and disappear under Overwrite.

‍

Does Update need a primary key in SFMC?

Yes. Update matches rows on the primary key of the target Data Extension, so the target needs one and the query must return that column. Without a key there is nothing to match on.

‍

When should I use Append in an SFMC query activity?

Use Append for event-style tables where every run adds new records, such as send, open or click history. Keep each run on a distinct date window so you do not load the same rows twice.

‍

Can Overwrite delete my audience by mistake?

It can if the query returns no rows, because the target is cleared and replaced with an empty result. Test the query before scheduling, and keep sends from running against a target that was just emptied.

‍

Build this audience in seconds with QAiry

‍

Choosing the data action is a small decision, but it only comes after the SQL is right. QAiry turns a plain-language request into production-ready SFMC SQL and Data Extensions inside your own Marketing Cloud, so you can spend your time on the audience logic instead of syntax.

Want to try it? Install QAiry for free or see a product demo to watch it build an audience from a one-line request.

Related guides