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 column is removed from the destination. |
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 PostgreSQL. |
THEN |
PostgreSQL will reject all data for the table. Rejected records are logged in the |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
If possible, change the table name in the source to be less than PostgreSQL’s character limit of 63 characters characters. Use the |
IF |
A table arrives with more columns than PostgreSQL allows. |
THEN |
PostgreSQL will reject all data for the table. Rejected records are logged in the |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
If possible, deselect some columns to allow Stitch to load data into PostgreSQL for the table. PostgreSQL has a limit of 250-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 PostgreSQL. For example: On the left is a table as it exists in the source; on the right is the table in PostgreSQL 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 PostgreSQL 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 PostgreSQL. |
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 PostgreSQL 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 PostgreSQL:
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 |
PostgreSQL will store all |
IF |
|
THEN |
No widening will occur. PostgreSQL stores all |
IF |
A column containing date data with timezone info is replicated to PostgreSQL. |
THEN |
PostgreSQL will store the data as Consider the following timestamp:
This data would be stored as the following in PostgreSQL:
For more info on this data type, refer to PostgreSQL’s Date/Time Type documentation. |
IF |
A column contains timestamp data that is outside PostgreSQL’s supported range. |
THEN |
PostgreSQL will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within PostgreSQL’s timestamp range of 4713 BC to 294276 AD. Use the |
IF |
A column contains decimal data that is outside PostgreSQL’s supported range. |
THEN |
PostgreSQL will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within PostgreSQL’s limit of . Use the |
IF |
A table arrives with more columns than PostgreSQL allows. |
THEN |
PostgreSQL will reject all data for the table. Rejected records are logged in the |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
If possible, deselect some columns to allow Stitch to load data into PostgreSQL for the table. PostgreSQL has a limit of 250-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 PostgreSQL. For example: On the left is a table as it exists in the source; on the right is the table in PostgreSQL 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 PostgreSQL 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 PostgreSQL. |
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 PostgreSQL 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 PostgreSQL:
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 PostgreSQL will reject the records and create a log for the rejected records in the Rejected records are logged in the |
AND |
PostgreSQL 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 PostgreSQL. Use the |
IF |
A column is replicated that has a mixed-case name. |
||||||
THEN |
PostgreSQL will maintain the case. For example:
|
IF |
A column is replicated that has a name with spaces. |
||||||
THEN |
PostgreSQL will maintain the spaces. For example:
|
IF |
A column is replicated with a name that contains unsupported special characters. |
||||||||||||
THEN |
PostgreSQL will remove the unsupported characters. For example:
|
IF |
A column is replicated with a name that begins with a non-letter. |
||||||||||||
THEN |
PostgreSQL will remove all leading non-letter characters with the exception of leading underscores. For example:
|
IF |
A column name contains more characters than allowed by PostgreSQL. |
THEN |
Columns with names that exceed the column character limit will be rejected; other columns in the table will persist to PostgreSQL. Rejected records are logged in the |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
If possible, change the column name in the source to be less than PostgreSQL’s character limit of 59 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 |
PostgreSQL will store all |
IF |
|
THEN |
No widening will occur. PostgreSQL stores all |
IF |
A column containing date data with timezone info is replicated to PostgreSQL. |
THEN |
PostgreSQL will store the data as Consider the following timestamp:
This data would be stored as the following in PostgreSQL:
For more info on this data type, refer to PostgreSQL’s Date/Time Type documentation. |
IF |
A column contains timestamp data that is outside PostgreSQL’s supported range. |
THEN |
PostgreSQL will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within PostgreSQL’s timestamp range of 4713 BC to 294276 AD. Use the |
IF |
A column contains decimal data that is outside PostgreSQL’s supported range. |
THEN |
PostgreSQL will reject the records that fall outside the supported range. Rejected records are logged in the |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
To resolve the error, offending values in the source must be changed to be within PostgreSQL’s limit of . 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 PostgreSQL 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 |
No widening will occur. PostgreSQL stores all |
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 PostgreSQL. |
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 PostgreSQL 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 PostgreSQL:
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 column is removed from the destination. |
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 PostgreSQL. |
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 |
You switch to a different destination of the same type. |
THEN |
This means the destination type is still PostgreSQL, Stitch may just be connected a different database in PostgreSQL.
|
IF |
You revoke Stitch’s schema and/or table permissions to PostgreSQL. |
THEN |
Data will fail to load and you will be notified. |
AND |
PostgreSQL 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 PostgreSQL entirely. |
THEN |
Data will fail to load and you will be notified. |
AND |
PostgreSQL will surface the following error:
|
FIX IT |
Re-authorize Stitch’s access to PostgreSQL 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.