BigQuery Write Modes Explained: Overwrite, Append, and Update

When Porter Metrics loads data into your BigQuery table, it follows a write mode that you set. The write mode controls what happens to the existing data in your table when new data arrives.

There are three options: overwrite, append, and update. Choosing the right one depends on how you want to manage your data over time.

Write Mode 1: Overwrite

Overwrite deletes all existing rows in your table and replaces them with the new data from the current sync.

When to use overwrite: use this mode when you always want your table to contain exactly the data from your configured date range, nothing more. If your date range is “last 30 days,” your table always contains the last 30 days of data. Older data is deleted on each sync.

Use overwrite when:

You do not need historical data beyond your configured date range.

You want a clean, predictable table that always reflects a fixed window of time.

You are troubleshooting a data issue and want to reload a clean dataset.

The risk of overwrite: if your sync fails one day, you lose the data that was previously in the table. The table is empty until the next successful sync.

Write Mode 2: Append

Append adds new rows to your table without touching the existing rows. Each sync adds the data from the current sync period on top of what is already there.

When to use append: use this mode when you want to build a growing historical table. Your table starts with your initial date range and grows by one day (or one period) with each scheduled sync.

Use append when:

You want to accumulate historical data over time.

Your initial sync loads 30 days of data, and each daily sync adds one more day.

You want to preserve data even if a sync fails on a given day (the previous data stays intact).

The risk of append: if a sync runs twice for the same date (for example, if you manually trigger a sync after a scheduled one), you get duplicate rows. You handle this by checking for duplicates in your SQL queries or by using update mode instead.

Write Mode 3: Update

Update is the most intelligent write mode. Porter checks whether each row already exists in your table based on a unique key (typically the combination of date, campaign ID, and account ID). If the row exists, Porter updates it with the new data. If it does not exist, Porter inserts it as a new row.

When to use update: use this mode when you want to accumulate historical data without duplicates. It combines the best of overwrite (no duplicates) and append (growing history).

Use update when:

You want a growing historical table without the risk of duplicate rows.

Your ad platforms retroactively update data (for example, Meta sometimes adjusts conversion counts for the previous 7 days). Update mode ensures your table reflects the latest values.

You run manual syncs in addition to scheduled syncs without worrying about duplicates.

The trade-off: update mode is slower than overwrite or append because Porter checks each row against the existing table before writing. For large tables with millions of rows, this can add time to each sync.

Which Write Mode Should You Use for Marketing Data?

For most marketing teams, update is the recommended mode. It builds historical data over time and handles retroactive data corrections from ad platforms automatically.

Use overwrite if you are testing or troubleshooting and want a clean slate on each sync.

Use append if your data source does not update historical data and you are confident your sync will not run twice for the same period.

Use update as your default for production marketing data pipelines.

Configuring Write Mode in Porter Metrics

In Porter Metrics, write mode is the last configuration step before you send your data to BigQuery. After setting your date range, filters, and schedule, you select your write mode from a dropdown. Click “Send” and your pipeline is live.

Your write mode applies to every scheduled sync going forward. You change it at any time by editing your query in Porter.