| title |
anchor |
summary |
content |
subsections |
Loading behavior types |
loading-behavior-types |
The loading behavior types Stitch supports |
When data is loaded into your destination, Stitch will use one of the following loading behavior types:
{% for subsection in section.subsections %}
- [{{ subsection.title }}](#{{ subsection.anchor }})
{% endfor %}
|
| title |
anchor |
content |
Upsert |
loading-behavior-types--upsert |
{{ site.data.tooltips.upsert }}
|
|
| title |
anchor |
content |
Append-Only |
loading-behavior-types--append-only |
{{ site.data.tooltips.append-only }}
|
|
| title |
anchor |
content |
History Mode |
loading-behavior-types--history-mode |
When data is loaded using the History mode, records are appended to the end of the table as new rows.
When a record is added, the `_sdc_start_date` column is set to the loading date, and the `sdc_end_date` column is set to `9999-12-31` (UTC time).
When a new verson of the same record is added, the `_sdc_end_date` value of the previous version is updated to the loading date of the new version.
Multiple versions of a row can exist in a table, creating a log of how a record has changed over time. This means you can create a query that returns the version of the record for a specific date or date range.
**Note**: Since this loading type adds two system columns in the destination table, it will decrease the maximum number of columns available for your data if the destination has a limited number of columns per table.
|
|
|
|
| title |
anchor |
summary |
content |
subsections |
Determining loading behavior |
loading-behavior-determined |
How loading behavior is determined |
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
|
| title |
anchor |
content |
Upsert loading |
upsert-loading-conditions |
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]({{ link.replication.rep-methods | prepend: site.baseurl }}).
|
|
| title |
anchor |
content |
Append-Only loading |
append-only-conditions |
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
|
|
| title |
anchor |
content |
History mode |
history-mode-conditions |
History mode is only used when the destination is configured to use History mode.
|
|
|
|
| title |
anchor |
summary |
content |
subsections |
Examples |
examples |
Examples of each loading behavior type |
{% for subsection in section.subsections %}
- [{{ subsection.title }}](#{{ subsection.anchor }})
{% endfor %}
|
| title |
anchor |
summary |
content |
Upsert loading example |
example--upsert-loading |
Upsert loading |
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
{% include layout/image.html enlarge=true file="/replication/upsert-loading-example.png" alt="Click to enlarge: Upsert loading example" %}
|
|
| title |
anchor |
summary |
content |
Append-Only example |
example--append-only-loading |
Append-Only examples |
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.
{% include layout/image.html enlarge=true file="/replication/append-only-loading.png" alt="Click to enlarge: Append-Only loading example" %}
|
|
| title |
anchor |
content |
Append-Only loading, no defined source Primary keys |
example--append-only--no-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 `{{ system-column.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.
{% include layout/image.html enlarge=true file="/replication/append-only-no-primary-key.png" alt="Click to enlarge: Append-Only loading as a result of no defined Primary Keys" %}
|
|
| title |
anchor |
summary |
content |
History mode example |
example--history-mode-loading |
History mode |
In this example, the destination is configured to use History mode. The `id` column is the table's Primary Key.
The following record is added to the destination table in a first replication job. The `_sdc_end_date` column is set to `9999-12-31` to indicate that this is the latest version of this record:
|id|status|_sdc_start_date|_sdc_end_date|
|---|---|---|---|
|abc-123|Pending|2022-10-21|**9999-12-31**|
The record is then updated in the source. A second replication job creates a new version of the existing record on December 14, 2022. The previous version's `_sdc_end_date` value is updated and the new version is added to the table. The destination table now looks like this:
|id|status|_sdc_start_date|_sdc_end_date|
|---|---|---|---|
|abc-123|Pending|2022-10-21|**2022-12-14**|
|abc-123|In progress|**2022-12-14**|9999-12-31|
|
|
|
|
| title |
anchor |
summary |
content |
subsections |
Reference |
reference |
References lists for destinations, integrations, and loading behavior |
{% for subsection in section.subsections %}
- [{{ subsection.title }}](#{{ subsection.anchor }})
{% endfor %}
|
| title |
anchor |
content |
Destinations and default loading behavior |
reference--destinations-loading-behavior |
{% include misc/icons.html %}
**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](#upsert-loading-conditions) must also be met.
{% assign attributes = "Destination|Version|Default loading behavior|Loading behavior is configurable?" | split:"|" %}
{% assign destinations = site.destinations | where:"destination",true | sort_natural:"display_name" %}
<table class="attribute-list">
<tr>
{% for attribute in attributes %}
{% if forloop.first == true %}
<td align="right">
{% else %}
<td>
{% endif %}
<strong>{{ attribute }}</strong>
</td>
{% endfor %}
</tr>
{% for destination in destinations %}
{% assign version = destination.this-version | prepend: "v" %}
<tr>
<td align="right">
{{ destination.display_name }}
</td>
<td width="15%; fixed">
{{ version }}
</td>
<td width="20%; fixed">
{{ site.data.destinations[destination.type][version]replication.default-loading-behavior }}
</td>
<td width="25%; fixed">
{% case site.data.destinations[destination.type][version]replication.configurable-loading-behavior %}
{% when true %}
{{ supported | replace:"TOOLTIP","Loading behavior is configurable for this destination and version." }}
{% when false %}
{{ not-supported | replace:"TOOLTIP","Loading behavior is not configurable for this destination and version." }}
{% endcase %}
</td>
</tr>
{% endfor %}
</table>
|
|
| title |
anchor |
content |
Append-Only integrations and tables |
reference--append-only-integrations |
{% assign all-integrations = site.documents | where:"input",true %}
{% assign append-only-integrations = all-integrations | where:"append-only-integration",true %}
{% assign append-only-tables = all-integrations | where:"append-only-tables",true %}
{% assign all-append-only = append-only-integrations | concat: append-only-tables | sort:"display_name" %}
{% assign attributes = "Integration|Version|Notes" | split:"|" %}
The integrations listed below are pre-configured to use Append-Only loading for all or some tables.
<table class="attribute-list">
<tr>
{% for attribute in attributes %}
{% if forloop.first == true %}
<td align="right" width="40%; fixed">
{% else %}
<td>
{% endif %}
<strong>{{ attribute }}</strong>
</td>
{% endfor %}
</tr>
{% for integration in all-append-only %}
<tr>
<td align="right">
{{ integration.display_name }}
</td>
<td width="15%; fixed">
{{ integration.this-version | prepend: "v" }}
</td>
<td>
{% if integration.append-only-integration == true %}
All tables use Append-Only loading
{% endif %}
{% if integration.append-only-tables == true %}
{{ integration.append-only-tables-description }}
{% endif %}
</td>
</tr>
{% endfor %}
</table>
|
|
|
|