Write Mode
  • 1 Minute to read
  • Dark
    Light

Write Mode

  • Dark
    Light

Write modes define the way the data is being written into a database, as such they are available only for data warehouses.

You can choose from four available write modes when creating your data flow:

  • Insert
  • Insert Ignore
  • Truncate Insert
  • Upsert
Dataddo Tip

Each warehouse is slightly different. For example, in Google Sheets, you can choose from truncate_insert and insert. As for other warehouses, you can select insert, upsert, truncate_insert, or insert_ignore.

INSERT

Insert is the default write mode which inserts new data and appends it to the pre-existing data in the destination. If data is extracted improperly, there is a high chance of data duplicates.

DATADDO TIP

To avoid duplicates we recommend selecting upsert. With upsert, once a record already exists, the data will not be written into the warehouse again even if it's inserted multiple times.

Example

Data Destination - Write Modes - INSERT

INSERT IGNORE

New data will be inserted while ignoring error messages. This means that if a wrong data type is used in a record, that record is omitted. This write mode is used rarely.

Example

The data type for the Amount column is integer or float. However, there is a field with "Hello Gabi", it's data type is therefore string or character. When insert_ignore is used, this row will be omitted when writing into the database.
Data Destination - Write Modes - INSERT IGNORE

TRUNCATE INSERT

Truncate_insert removes all the contents in the table prior to data insertion.

WARNING!

Double-check before selecting this write mode. The changes you make are irreversible!

Data Destination - Write Modes - TRUNCATE INSERT

UPSERT

Upsert inserts new rows and updates existing ones. You will have to set at least one unique key, which will represent one or multiple columns.

The primary key is a unique attribute that can never repeat and identifies entities in a table.

Before data is inserted, Dataddo checks the data in the destination. If data with the same primary key already exists in the table, the table is updated with the latest records, otherwise a new record is inserted. The advantage of upsert is avoiding duplicities.

Example

When extracting Facebook Post records, the primary key is "Post ID". If an ID has not been inserted before, a new record will be created. If there is already a record with the same ID, existing information will be updated.

Another example can be a phone number as more people cannot have the same one.
Data Destination - Write Modes - UPSERT


Was this article helpful?

What's Next