Learn about the methods Stitch uses to load data into your destination and what the impact will be on your destination tables.

In this guide, we’ll cover:

Loading behavior types

When data is loaded into your destination, Stitch will use one of the following loading behavior types:

Upsert

When data is loaded using the Upsert behavior, existing rows are updated in tables with defined Primary Keys. Stitch will de-dupe records based on Primary Keys before loading them, meaning that only one version of a record will exist in the table at any given time.

Append-Only

When data is loaded using the Append-Only behavior, records are appended to the end of the table as new rows. Existing rows in the table aren’t updated even if the source has defined Primary Keys. Multiple versions of a row can exist in a table, creating a log of how a record has changed over time.

Determining loading behavior

At a high level, loading behavior is determined by the following:

  • The destination’s support for Upsert loading
  • The presence of Primary Keys in the source data and destination
  • The integration or table has pre-configured loading behavior

Upsert loading

Upsert loading is used when all of the following conditions are met:

  1. The destination supports or is configured to use Upsert loading, and
  2. The data has defined Primary Keys in the source and destination, and
  3. The integration or table is not pre-configured to use Append-Only loading

Note: This is applicable to all Replication Methods.

Append-Only loading

Append-Only loading is used when any of the following conditions are met:

  • The destination only supports or is configured to use Append-Only loading, or
  • The data doesn’t have defined Primary Keys in the source or destination, or
  • The integration or table is pre-configured to use Append-Only loading

Examples

Upsert loading example

In this example:

  1. The destination supports or is configured to use Upsert loading, and
  2. The data has defined Primary Keys in the source and destination, and
  3. The integration or table being loaded is not pre-configured to use Append-Only loading
Click to enlarge: Upsert loading example

Append-Only example

This example is applicable any of the following are true:

  • The destination only supports or is configured to use Append-Only loading, or
  • The integration or table being loaded is pre-configured to use Append-Only loading, or
  • The source data has defined Primary Keys, but the table in the destination doesn’t. For example: Primary Key table comments are removed from a table in Amazon Redshift.
Click to enlarge: Append-Only loading example

Append-Only loading, no defined source Primary keys

This example is applicable when the source data doesn’t have a defined Primary Key.

When source data that doesn’t have a Primary Key is replicated, Stitch appends an __sdc_primary_key to the data to function as a Primary Key. Data will be loaded using Append-Only loading, regardless of what loading behavior the destination supports or is configured to use.

Click to enlarge: Append-Only loading as a result of no defined Primary Keys

Reference

Destinations and default loading behavior

Note: If a destination supports and is configured to use Upsert loading, Stitch will attempt to use Upsert loading before Append-Only. All other conditions for Upsert loading must also be met.

Destination Version Default loading behavior Loading behavior is configurable?
Amazon Redshift v2 Upsert
Amazon S3 v1 Append-Only
data.world v1 Upsert
Databricks Delta Lake (AWS) v1 Upsert
Google BigQuery v1 Append-Only
Google BigQuery v2 Selected by you
Microsoft Azure Synapse Analytics v1 Upsert
Microsoft SQL Server v1 Upsert
MySQL v1 Upsert
Panoply v2 Upsert
PostgreSQL v1 Upsert
Snowflake v1 Upsert

Append-Only integrations and tables

The integrations listed below are pre-configured to use Append-Only loading for all or some tables.

Integration Version Notes
Amazon S3 CSV v1 Unless Primary Keys are defined for the table, Append-Only loading will be used.
Campaign Manager v1 All tables use Append-Only loading
Google Ads v1 All Report tables
Google Ads (AdWords) v1 All Report tables
Microsoft Advertising v2 All Report tables
SFTP v1 Unless Primary Keys are defined for the table, Append-Only loading will be used.
Related Troubleshooting
Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.