#3392 feat(targets): Automatically generate primary key data when no key properties are specified in schema Issue created by ReubenFrankel ### Feature scope Targets (data type handling, batching, SQL object generation, tests, etc.) ### Description For streams that don't specify any key properties and those that are configured are incorrect/insufficient, it would be good if SDK targets could assume the responsibility of managing a generated primary key configuration, i.e. add property to schema and record hash as data. This is useful when • only appending records is not desirable due to warehouse storage constraints • for the convenience of being able to query distinct data downstream easily (e.g. dbt models) • a target lacks the ability to automatically upsert data based on distinct records already (e.g.

target-snowflake

) (for reference,

target-bigquery

has a working implementation of this in `dedupe_before_upsert`) This isn't bulletproof in that if the incoming schema changes, old hashes would become stale. Not sure if there is a way around this, but I believe this approach is fine as a fallback strategy and preferable to no primary key regardless. Fivetran does something like this with the `_fivetran_id` system column:

•

_fivetran_id

(TEXT) is a Fivetran-generated internal unique surrogate primary key. Its value is generated based on the row's set of values and helps us avoid duplicate rows in tables without a primary key.

Some considerations: • Name of primary key property to follow

_sdc

convention e.g.

_sdc_id

,

_sdc_auto_id

• SHA256 hash of to avoid collisions (more likely with MD5) • Metadata (

_sdc_*

) properties should be ignored • Record keys should be sorted prior to JSON serialisation for deterministic hashing (hash will change for the same set of keys in different orders) • How this might work with the (built-in?)

load_method

setting i.e. should

append-only

respect this or not meltano/sdk