What is a record's

primary_key

and how does it relate to unique identity of records?

A record's

primary key

is a field or a combination of fields that is used to uniquely identify the entities of a table. It can be a single field, an array of fields representing a composite key, or an array of arrays representing a composite key where the fields are nested fields. For example, it can be a single field like

"id"

or a combination of fields like

["code", "type"]

[[source](https://docs.airbyte.com/connector-development/config-based/understanding-the-yaml-file/reference)]. In a database table, only one row per primary key value is permitted. In the data warehouse, multiple rows for the same primary key can be found in the history table. The unique records per primary key behavior is mirrored in the final table with incremental deduped sync mode. The primary key is then used to refer to the entity which values should be updated [[source](https://docs.airbyte.com/understanding-airbyte/connections/incremental-append-deduped)]. In some sources, the primary key can be determined without any user input. For example, in the (JDBC) Database sources, the primary key can be defined in the table's metadata [[source](https://docs.airbyte.com/understanding-airbyte/connections/incremental-append-deduped)]. In the context of Airbyte's incremental data synchronization, a primary key field is used to select only the most recent version of a given record from the history table for inclusion in the final deduplicated table, and all other historical versions of that record are not included [[source](https://airbyte.com/tutorials/incremental-data-synchronization)].

@kapa.ai If I have 2 separate sources pushing data to a single destination, do I have to ensure my

primary_key

is unique across both of those sources?

Based on the information provided in the extracts, the primary key is used to uniquely identify a record within a source and is crucial for downstream de-duplication of records, especially in the Incremental Sync - Append + Deduped sync mode. However, the extracts do not explicitly mention the need for the primary key to be unique across multiple sources pushing data to a single destination. In general, if two sources are pushing data to the same destination table, and there's a possibility of overlap in the primary keys, it could potentially lead to data integrity issues. But this would depend on the specifics of your data and how you're using Airbyte. For a definitive answer, you may want to refer to more detailed documentation or reach out to the Airbyte community for support. Sources: - [Understanding-airbyte > Connections > Incremental Sync - Append + Deduped > Source-Defined Primary key](https://docs.airbyte.com/understanding-airbyte/connections/incremental-append-deduped) - [Understanding-airbyte > Connections > Incremental Sync - Append + Deduped > User-Defined Primary key](https://docs.airbyte.com/understanding-airbyte/connections/incremental-append-deduped) - [Connector-development > Connector-builder-ui > Record processing > Meta data](https://docs.airbyte.com/connector-development/connector-builder-ui/record-processing/) - [Explore Airbyte’s incremental data synchronization](https://airbyte.com/tutorials/incremental-data-synchronization)