Select primary keys

Stable and unique primary keys are crucial for unified customer profiles and for linking rows in source tables to rows in unified customer profiles. Stitch depends on well-defined primary keys being defined for each source table in order to keep track of which source rows are linked to each unified customer profile after ID resolution.

When a source row changes, such as when a user adds a secondary phone number or changes their account preferences, and as long as the customer profile remains stable, Stitch will assign the same Amperity ID to that customer profile instead of creating a new Amperity ID.

What makes a good primary key?

Uniqueness and stability are the most important traits of a good primary key. For example:

• first_name is a field with first names for all customers in the table. This field is an example of a bad primary key because it is unlikely to be unique among all rows in a table.

• updated_at is a field that records when the data in the row was last updated. This field is another example of a bad primary key because even though it is unique, its value for a given row can change, making it unstable.

• customer_id is a field that uniquely identifies customers within your brand’s point-of-sale data. This field is an example of a good primary key field because it is likely to identify a single customer and is a reliable value over time.

To select primary keys open the Identity resolution page in Quick start. On the Identity tables card, do one of the following:

1. Click AmpAI select to have the AmpAI Assistant determine which tables contain the best data for identity resolution, which fields in each data source are the optimal primary key, and which customer profile semantic tags to apply to fields in each data source.

2. Click Manually select on the Primary keys card to configure primary keys without using the AmpAI Assistant.

Click Edit on the Primary keys card to review primary keys for all tables.

Note

What happens to primary keys in the Unified Coalesced table?

The Unified Coalesced table has all PII data processed through Stitch. Each semantic tag is a column header. All data is coalesced into a single table. A unique Amperity ID may appear in more than one row.

• Each record in the Unified Coalesced table has a primary key.

• A primary key is unique within a data source, but that primary key may not be unique across all data sources.

• There can be only one primary key per data source. Each record in the Unified Coalesced table is uniquely identified by the pair of values defined in the “datasource” and “pk” columns.

• Each record in the Unified Coalesced table may only be associated with a single Amperity ID.