Primary Keys
IF |
A table without a Primary Keys is replicated. |
THEN |
|
IF |
A table with a single Primary Key is replicated. |
THEN |
|
IF |
A table with multiple Primary Keys is replicated. |
THEN |
|
IF |
The table’s Primary Key(s) is/are changed. |
THEN |
If using Key-based Incremental or Log-based Incremental Replication, data will be added to the table in an Append-Only fashion. If using Full Table Replication, the table will be overwritten in its entirety during each job. |
IF |
The table’s Primary Key comment is either removed or changed to be incorrectly formatted. |
THEN |
Data will continue to be loaded into the table in an Append-Only fashion. |
Replication Keys
IF |
A table is replicated where the Replication Key column contains |
THEN |
|
Tables
IF |
A table is replicated where the Replication Key column contains |
THEN |
|
IF |
A table name contains more characters than allowed by Redshift. |
THEN |
Redshift will reject all data for the table. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
If possible, change the table name in the source to be less than Redshift’s character limit of 127 characters characters. Use the |
IF |
A table arrives with more columns than Redshift allows. |
THEN |
Redshift will reject all data for the table. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
If possible, deselect some columns to allow Stitch to load data into Redshift for the table. Redshift has a limit of 1,600 columns columns per table. Use the |
IF |
A column contains only |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
THEN |
Only columns that are populated in at least one record will be created in Redshift. For example: On the left is a table as it exists in the source; on the right is the table in Redshift as replicated by Stitch:
Prior to the next job, a new record is added to the table with a populated value in the
Note In this example, |
IF |
A table is tracked that contains entirely |
THEN |
|
IF |
Columns in a table using Incremental Replication are backfilled. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
THEN |
Note: While this example is typical of Key-based Incremental Replication, it is also true for tables using Log-based Incremental Replication. In the latter scenario, think of the If the Replication Key used by the table is updated to a value greater than or equal to the last recorded maximum value, the backfilled records will be selected for replication. Consider the example below. On the left is a table as it exists in the source; on the right is the table in Redshift as replicated by Stitch:
A new record is added in the source with a populated Replication Key value. Stitch replicates record 4 and saves
Before the next job, the
For record 1’s
If there are a large number of backfilled records in a source table, resetting the table’s Replication Keys or temporarily using Full Table Replication may be required to replicate the backfilled values. |
IF |
A new column is added in a SaaS integration/data source to a table that’s currently set to replicate. |
THEN |
|
IF |
A new column is added by you to a Stitch-generated table in Redshift. |
THEN |
Columns may be added to tables created by Stitch as long as they are nullable, meaning columns don’t have |
IF |
A column is deleted at the source. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
THEN |
How a deleted column is reflected in Redshift depends on the table’s Replication Method. Consider the example below. On the left is a table as it exists in the source; on the right is the table in Redshift:
Before the next job, the Log-based Incremental ReplicationDue to how binary logs are structured, changes to a source table - including adding or removing columns, changing data types, etc. - require manual intervention before replication can continue. Refer to the Log-based Incremental Replication documentation for more info. Key-based Incremental ReplicationFor tables using Key-based Incremental Replication, default Below is the source table with the deleted
Full Table ReplicationFor tables using Full Table Replication: during the next job, the entire table will be overwritten. The table’s schema will reflect the column’s removal in the source:
|
IF |
You remove a column from a Stitch-replicated table in your destination. |
THEN |
The result of deleting a column from a Stitch-generated table depends on the type of column being removed:
|
Columns
IF |
A table is replicated where the Replication Key column contains |
THEN |
|
IF |
Stitch detects multiple data types for a single column. |
THEN |
For example: Stitch first detected that
|
IF |
A |
THEN |
Redshift will type the column as For example: During the initial replication job, data in a |
IF |
|
THEN |
Redshift will widen the column to accommodate the width of the largest record up to the limit of For example: during the initial replication job, a During a subsequent job, Stitch identifies data for this column that exceeds the current size maximum of To accommodate the data, the column will become |
IF |
A |
THEN |
Redshift will truncate the data to the maximumm width of |
IF |
A column containing date data with timezone info is replicated to Redshift. |
THEN |
Redshift will store the data as Consider the following timestamp:
This data would be stored as the following in Redshift:
For more info on this data type, refer to Amazon’s Datetime Type documentation. |
IF |
A column contains timestamp data that is outside Redshift’s supported range. |
THEN |
Redshift will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within Redshift’s timestamp range of 4713 BC to 294276 AD. Use the |
IF |
A column contains integer data that is outside Redshift’s supported range. |
THEN |
Redshift will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within Redshift’s limit of -9223372036854775808 to 9223372036854775807. Use the |
IF |
A column contains decimal data. |
THEN |
Redshift will truncate the data. |
IF |
A column contains decimal data that is outside Redshift’s supported range. |
THEN |
Redshift will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within Redshift’s limit for decimal data. Use the |
IF |
A table arrives with more columns than Redshift allows. |
THEN |
Redshift will reject all data for the table. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
If possible, deselect some columns to allow Stitch to load data into Redshift for the table. Redshift has a limit of 1,600 columns columns per table. Use the |
IF |
A column contains only |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
THEN |
Only columns that are populated in at least one record will be created in Redshift. For example: On the left is a table as it exists in the source; on the right is the table in Redshift as replicated by Stitch:
Prior to the next job, a new record is added to the table with a populated value in the
Note In this example, |
IF |
A table is tracked that contains entirely |
THEN |
|
IF |
Columns in a table using Incremental Replication are backfilled. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
THEN |
Note: While this example is typical of Key-based Incremental Replication, it is also true for tables using Log-based Incremental Replication. In the latter scenario, think of the If the Replication Key used by the table is updated to a value greater than or equal to the last recorded maximum value, the backfilled records will be selected for replication. Consider the example below. On the left is a table as it exists in the source; on the right is the table in Redshift as replicated by Stitch:
A new record is added in the source with a populated Replication Key value. Stitch replicates record 4 and saves
Before the next job, the
For record 1’s
If there are a large number of backfilled records in a source table, resetting the table’s Replication Keys or temporarily using Full Table Replication may be required to replicate the backfilled values. |
IF |
A new column is added in a SaaS integration/data source to a table that’s currently set to replicate. |
THEN |
|
IF |
A new column is added by you to a Stitch-generated table in Redshift. |
THEN |
Columns may be added to tables created by Stitch as long as they are nullable, meaning columns don’t have |
IF |
A column is deleted at the source. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
THEN |
How a deleted column is reflected in Redshift depends on the table’s Replication Method. Consider the example below. On the left is a table as it exists in the source; on the right is the table in Redshift:
Before the next job, the Log-based Incremental ReplicationDue to how binary logs are structured, changes to a source table - including adding or removing columns, changing data types, etc. - require manual intervention before replication can continue. Refer to the Log-based Incremental Replication documentation for more info. Key-based Incremental ReplicationFor tables using Key-based Incremental Replication, default Below is the source table with the deleted
Full Table ReplicationFor tables using Full Table Replication: during the next job, the entire table will be overwritten. The table’s schema will reflect the column’s removal in the source:
|
IF |
You remove a column from a Stitch-replicated table in your destination. |
THEN |
The result of deleting a column from a Stitch-generated table depends on the type of column being removed:
|
IF |
Two columns are replicated that canonicalize to the same name. |
THEN |
For example: a table containing both Redshift will reject the records and create a log for the rejected records in the Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
If possible, re-name one of the columns in the source so that both column names will be unique when replicated to Redshift. Use the |
IF |
A column is replicated that has a mixed-case name. |
||||||
THEN |
Redshift will convert letters to lowercase. For example:
|
IF |
A column is replicated that has a name with spaces. |
||||||
THEN |
Redshift will maintain spaces. For example:
|
IF |
A column is replicated with a name that contains unsupported special characters. |
|||||||||
THEN |
Redshift will remove all special characters. Note that this doesn’t include underscores or dollar signs. For example:
|
IF |
A column is replicated with a name that begins with a non-letter. |
THEN |
This scenario is unremarkable to Redshift. |
IF |
A column name contains more characters than allowed by Redshift. |
THEN |
Redshift will reject columns with names that exceed the column character limit. Other columns in the table will persist to Redshift. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
If possible, change the column name in the source to be less than Redshift’s character limit of 115 characters characters. Use the |
Data Typing
IF |
Stitch detects multiple data types for a single column. |
THEN |
For example: Stitch first detected that
|
IF |
A |
THEN |
Redshift will type the column as For example: During the initial replication job, data in a |
IF |
|
THEN |
Redshift will widen the column to accommodate the width of the largest record up to the limit of For example: during the initial replication job, a During a subsequent job, Stitch identifies data for this column that exceeds the current size maximum of To accommodate the data, the column will become |
IF |
A |
THEN |
Redshift will truncate the data to the maximumm width of |
IF |
A column containing date data with timezone info is replicated to Redshift. |
THEN |
Redshift will store the data as Consider the following timestamp:
This data would be stored as the following in Redshift:
For more info on this data type, refer to Amazon’s Datetime Type documentation. |
IF |
A column contains timestamp data that is outside Redshift’s supported range. |
THEN |
Redshift will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within Redshift’s timestamp range of 4713 BC to 294276 AD. Use the |
IF |
A column contains integer data that is outside Redshift’s supported range. |
THEN |
Redshift will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within Redshift’s limit of -9223372036854775808 to 9223372036854775807. Use the |
IF |
A column contains decimal data. |
THEN |
Redshift will truncate the data. |
IF |
A column contains decimal data that is outside Redshift’s supported range. |
THEN |
Redshift will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
Redshift will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within Redshift’s limit for decimal data. Use the |
Schema Changes
IF |
Stitch detects multiple data types for a single column. |
THEN |
For example: Stitch first detected that
|
IF |
Data is replicated to Redshift that is nested, containing many top-level properties and potentially nested sub-properties. |
THEN |
The core object - or the top-level properties - will be split into columns in a table. Any properties contained within a nested array - or sub-properties - will be “flattened” into subtables. For example: below is the raw JSON data for an
Because there is a nested array in the JSON data, two tables will be created: one named A tutorial on how Stitch de-nests and stores these types of records can be found in the Nested JSON Structures & Row Count Impact Guide. |
IF |
|
THEN |
Redshift will widen the column to accommodate the width of the largest record up to the limit of For example: during the initial replication job, a During a subsequent job, Stitch identifies data for this column that exceeds the current size maximum of To accommodate the data, the column will become |
IF |
A new column is added in a SaaS integration/data source to a table that’s currently set to replicate. |
THEN |
|
IF |
A new column is added by you to a Stitch-generated table in Redshift. |
THEN |
Columns may be added to tables created by Stitch as long as they are nullable, meaning columns don’t have |
IF |
A column is deleted at the source. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
THEN |
How a deleted column is reflected in Redshift depends on the table’s Replication Method. Consider the example below. On the left is a table as it exists in the source; on the right is the table in Redshift:
Before the next job, the Log-based Incremental ReplicationDue to how binary logs are structured, changes to a source table - including adding or removing columns, changing data types, etc. - require manual intervention before replication can continue. Refer to the Log-based Incremental Replication documentation for more info. Key-based Incremental ReplicationFor tables using Key-based Incremental Replication, default Below is the source table with the deleted
Full Table ReplicationFor tables using Full Table Replication: during the next job, the entire table will be overwritten. The table’s schema will reflect the column’s removal in the source:
|
IF |
You remove a column from a Stitch-replicated table in your destination. |
THEN |
The result of deleting a column from a Stitch-generated table depends on the type of column being removed:
|
Data Warehouse Changes
IF |
The table’s Primary Key(s) is/are changed. |
THEN |
If using Key-based Incremental or Log-based Incremental Replication, data will be added to the table in an Append-Only fashion. If using Full Table Replication, the table will be overwritten in its entirety during each job. |
IF |
The table’s Primary Key comment is either removed or changed to be incorrectly formatted. |
THEN |
Data will continue to be loaded into the table in an Append-Only fashion. |
IF |
A new column is added by you to a Stitch-generated table in Redshift. |
THEN |
Columns may be added to tables created by Stitch as long as they are nullable, meaning columns don’t have |
IF |
You remove a column from a Stitch-replicated table in your destination. |
THEN |
The result of deleting a column from a Stitch-generated table depends on the type of column being removed:
|
IF |
Indices are applied to Stitch-generated columns in the destination. |
THEN |
Stitch will respect the index application. |
IF |
SORT and/or DISTRIBUTION Keys are applied to Stitch-generated columns in the destination. |
THEN |
This is not applicable to Redshift. |
IF |
You switch to a different destination of the same type. |
THEN |
This means the destination type is still Redshift, Stitch may just be connected a different database in Redshift.
|
IF |
You revoke Stitch’s schema and/or table permissions to Redshift. |
THEN |
Data will fail to load and you will be notified. |
AND |
Redshift will surface the following error:
|
FIX IT |
Verify and, if necessary, re-grant the Stitch user’s permissions. |
IF |
You revoke Stitch’s access to Redshift entirely. |
THEN |
Data will fail to load and you will be notified. |
AND |
Redshift will surface the following error:
|
FIX IT |
Re-authorize Stitch’s access to Redshift by ensuring the Stitch user’s credentials are correct and the required permissions are granted. |
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.