Applicable only to Google BigQuery (v2) and 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:
This guide is applicable to the following destinations:
Usage in replication
Because some 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.
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.
[TODO- BQ destinations using append-only rep]
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. These 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:
|Column name||Data type||Description|
The name of the table in the integration schema.Example data:
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:
Note: This column is only applicable to Microsoft Azure SQL Data Warehouse destinations.
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:
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 for Microsoft Azure SQL Data Warehouse destinations, 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
Example table: Google BigQuery (v2)
In Google BigQuery (v2) destinations, the Primary Key data for the
emails table will look like this in
Example table: Microsoft Azure SQL Data Warehouse
In Microsoft Azure SQL Data Warehouse destinations, the Primary Key data for the
emails table will look like this in
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 receive 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.