Already have an account? Sign in

Understanding Loading Behavior

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:

• The loading behavior types Stitch supports

• How loading behavior is determined

• Examples of each loading behavior type

• References lists for destinations, integrations, and loading behavior

Loading behavior types

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

• Upsert

• Append-Only

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 whenall 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 sourceand destination,and
3. The integration or table is not pre-configured to use Append-Only loading

Note: This is applicable to allReplication Methods.

Append-Only loading

Append-Only loading is used whenany 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 sourceor destination,or
• The integration or table is pre-configured to use Append-Only loading

Examples

• Upsert loading example

• Append-Only example

• Append-Only loading, no defined source Primary keys

Upsert loading example

In this example:

1. The destination supportsor 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

Append-Only example

This example is applicableany of the following are true:

• The destination only supportsor 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.

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.

Reference

• Destinations and default loading behavior

• Append-Only integrations and tables

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. Allother conditions for Upsert loading must also be met.

Append-Only integrations and tables

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

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