Destination Snowflake: _AIRBYTE_UNIQUE_KEY dependent on order of composite primary key fields - can cause duplicates

[luke-oa]

Environment

• Airbyte version: 0.40.15
• Deployment: Kube
• Destination Connector and version: Snowflake 0.4.41
• Step where error happened: Sync Normalization

Current Behavior

Airbyte normalization dedupes data using an _AIRBYTE_UNIQUE_KEY, which is the MD5 hash of the values of the primary key(s) for the row; however, if the order in which these field names is changed in the config/catalog (only applicable to composite/multiple primary keys), the MD5 hash value (_AIRBYTE_UNIQUE_KEY) for a given row will also change, resulting in duplicate row values (only applicable when deduping data).

Expected Behavior

Deduplication should not be impacted by the order in which composite primary key field names are stored. Ideally a non-order dependent algorithm could be used, but this would be a breaking change in the future. More realistically, something that ensures a given sort order in the normalization query regardless of config/catalog order would solve this.

Steps to Reproduce

1. Set a composite key for a table, sync it, and dedupe the data
2. Change the order of the composite key fields (set catalog via API)
3. Sync and dedupe again
4. Check row counts based on a hash of the composite values and see there are now duplicates

[evantahler]

Closing this issue because normalization as we know it is going away shortly (and the way de-duplication works will be changing) - learn more @ #26028