Applicable only to Microsoft Azure SQL Data Warehouse destinations, the
_sdc_primary_keys table contains a list of the tables in an integration schema and the columns those tables use as Primary Keys. Every integration schema created by Stitch will contain this table.
Usage in replication
Because Azure SQL Data Warehouse destinations don’t have native support for Primary Keys, Stitch uses the
_sdc_primary_keys table to store Primary Key information and de-dupe data during loading incrementally-replicated tables.
Replication Methods and de-duping data
De-duplicating data only applies to tables using an Incremental Replication Method. This ensures that only the most recent version of a record is loaded into the table.
Tables using Full Table Replication are not de-duped, but loaded in full during each replication job.
Determining Primary Keys
Depending on the data source type, Primary Keys are determined in one of two ways:
For database integrations, Primary Keys are defined by you in the source database. This will usually be columns with a Primary Key constrant or some other equivalent, depending on the type of database being used.
Note: For database views you set to replicate in Stitch, the Primary Key will be the field you define for the view during setup.
For SaaS integrations, Primary Keys are pre-defined by Stitch. Refer to the schema documentation for your SaaS integration for info on the Primary Keys Stitch uses for specific tables.
In every schema created by a Stitch integration will be a
_sdc_primary_keys table. The Primary Key data for every table set to replicate will be stored in this table.
Primary Keys table schema
_sdc_primary_keys table contains the following columns:
The name of the table in the integation schema.
The name of the column used as the table’s Primary Key. If a table uses multiple columns as a composite Primary Key, there will be a row for each column the table uses.
When Stitch receives data, Primary Keys are provided in an array. For example:
The value of this column corresponds to the index of the column in the array Stitch receives.
For every column a table uses as a Primary Key, the
_sdc_primary_keys table will contain a row containing the table’s name, the name of the column, and the column’s position in the Primary Key array Stitch receives.
For example: If Stitch received the array
["email_id", "updated_at", "customer_id"] for an
emails table, the
_sdc_primary_keys table would contain the following records:
When Stitch loads data for the
emails table, it will reference these records in
_sdc_primary_keys to de-duplicate the data. This will ensure that only the most recent version of a record exists in the
Effects of Primary Key changes
Replication issues can arise if Primary Keys in the source change, or if data in the
_sdc_primary_keys is incorrectly altered or removed.
Along with being unable to load data, Stitch will surface the following error if this occurs:
Primary Keys for table do not match Primary Keys of incoming data
If you recieve this error, you should reset the table(s) mentioned in the error. This will queue a full re-replication of the table, which will ensure Primary Keys are correctly captured and used to de-dupe data when loading.