I know that

_airbyte_unique_key

is the hash of primary keys used to de-duplicate the final table. I can reproduce this hash with

md5(primary_key)

in SQL when it's a single-column primary key. However, when it comes to

composite primary key

, I do not know how to reproduce it? For example, in my stream, the composite primary key is [account_id, campaign, site, date]. I tried the following but the resulting

md5

is not the same as

_airbyte_unique_key

SELECT  _airbyte_unique_key, 
        md5(concat(account_id, campaign, site, date)) AS md5
FROM airbyte_dev.util_campaign_site_day_breakdown

How to reproduce

_airbyte_unique_key

in case of

composite primary key

? Thanks.

The

_airbyte_unique_key

is indeed a hash of the primary key field(s), and it's used to de-duplicate the final table. However, the exact method Airbyte uses to generate this hash, especially for composite primary keys, is not detailed in the provided extracts. In your case, it seems like the

md5(concat(account_id, campaign, site, date))

approach is not producing the same hash as

_airbyte_unique_key

. This could be due to a variety of reasons such as the order of concatenation, the delimiter used in concatenation, or the data type of the fields. Unfortunately, without more specific information on how Airbyte generates the

_airbyte_unique_key

for composite primary keys, it's challenging to provide a definitive answer. I recommend checking the Airbyte documentation or reaching out to the Airbyte community for more detailed guidance.