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.
In this guide, we’ll cover:
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
The _sdc_primary_keys table contains the following columns:
| Column name | Data type | Description |
| table_name | STRING |
The name of the table in the integration schema. Example data:emails
|
| column_name | STRING |
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. Example data:email_id
|
| ordinal_position | INTEGER |
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. Example data:0
|
Example table
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:
| table_name | column_name | ordinal_position |
| emails | email_id | 0 |
| emails | updated_at | 1 |
| emails | customer_id | 2 |
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 emails table.
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.
| 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.