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:
- The destination supports or is configured to use Upsert loading, and
- The data has defined Primary Keys in the source and destination, and
- 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:
- The destination supports or is configured to use Upsert loading, and
- The data has defined Primary Keys in the source and destination, and
- The integration or table being loaded is not pre-configured to use Append-Only loading
![Click to enlarge: Upsert loading example](https://www.stitchdata.com/docs/images/replication/upsert-loading-example.png)
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](https://www.stitchdata.com/docs/images/replication/append-only-loading.png)
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](https://www.stitchdata.com/docs/images/replication/append-only-no-primary-key.png)
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.