Write Mode

Write Modes define the way the data is being written into the database. They are set up when creating a Flow. Write modes are only applicable to Data Warehouses.

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

There are 4 write modes that are available in Dataddo:

  • Insert
  • Insert Ignore
  • Truncate Insert
  • Upsert

INSERT

The default write mode. The new data is inserted and appended to the pre-existing data in the destination. In data is extracted improperly, there is a high chance of data duplicates.

DATADDO TIP

To avoid duplicates we recommend selecting upsert so that even if the same data is written over and over again into the destination (into the data warehouse), it will not be written into the destination twice if this record already exists.

Example

Data Destination - Write Modes - INSERT

INSERT IGNORE

New data will be inserted (same as for INSERT write mode) while ignoring any error message. This means that if a wrong data type is used in a record, that record is omitted. This write mode is used rarely.

Example

in the mentioned example, the amount is of the data type integer or float (a number) but the data/information inserted here is “Hello Gabi” (which is a string/character) hence this row is ignored.

Data Destination - Write Modes - INSERT IGNORE

TRUNCATE INSERT

WARNING!

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

This strategy removes all the contents in the table prior to data insertion.

Data Destination - Write Modes - TRUNCATE INSERT

UPSERT

This strategy inserts new rows and updates existing ones. To perform this correctly, it is necessary to set at least one unique key representing one or multiple columns. The primary key is an attribute that can never repeat and identifies entities in a table.

Before data is inserted, Dataddo checks the table in the destination for the key. If data with the same Primary Key already exists in the table, then the table is updated with the latest records. If not, then the new record is inserted normally. The advantage of upsert is avoiding duplicities.

EXAMPLE

Let’s say the user wants his Facebook Posts records. Then the primary key would be “Post ID”. No post can have the same ID. A great life example is a phone number, no person can have the same phone number as you do. 

Data Destination - Write Modes - UPSERT