When using some of our web destinations, you can find several writing mode options. In this article, we will explain to you the differences between these options.
When configuring Dataslayer transfers to cloud destinations such as Google BigQuery, Google Cloud Storage (GCS), Database, Snowflake, Amazon S3, or Amazon Redshift, you will find an option called Write Mode.
The Write Mode defines how new data will be appended, upserted, or replaced in your destination table or storage bucket. Choosing the right mode is crucial for maintaining data accuracy, performance, and consistency.
You can configure this option in the Destination settings when creating or editing your transfer:
Example
Available Write Modes in Dataslayer
Dataslayer supports three types of write modes across BigQuery, GCS, Database, Snowflake, Amazon S3, and Redshift:
-
Append
-
Replace
-
Upsert
Let’s break down how each works:
Append (Recommended)
Append is the most common and recommended option when sending data to BigQuery, GCS, Amazon S3, Snowflake, Redshift, or Database.
With this mode, the new records are simply added to the end of the existing table, keeping all historical information intact.
- If your table already has 10,000 rows, the next transfer will continue from row 10,001.
- If the table is empty, the records will be inserted normally, starting from the first row.
IMPORTANT
When the format of the data doesn’t match the table’s structure, the system will block the transfer to prevent errors, and the records won’t be added. Learn more in this article.
Replace (Use with caution)
Replace completely deletes all existing rows in the destination table and loads only the new dataset. This can be useful if you need to correct mistakes from a previous import or refresh the data entirely, for example in small lookup tables.
Since Replace removes all existing records before loading the new data, it should be used carefully, especially when working with large datasets.
That’s why it’s generally better to use Append or Upsert unless you are sure you want to overwrite everything.
Upsert (Update + Insert)
The Upsert mode updates existing records in the table and inserts any new ones that are not already present. This ensures that outdated information is refreshed while new data is added seamlessly.
This option takes longer to complete as it must check and update all the data previously imported.
Upsert is particularly useful when the data already stored has become outdated. However, it is not recommended for very large tables, since processing and updating a high volume of records may increase the risk of errors.
As always, please contact us via our live chat on our website or via email if you still have doubts or questions. We are happy to help!