ETL task aborted - Unexpected Primary Key violations detected

[vtatarnikov]

Greetings.

I have two tables in DWH - a directory and a fact table with one business key referencing the directory.

Accordingly, there are two mappings - for filling the directory and the fact table.

Why am I getting this error when running mappings?

I tried to form the directory from the fact table, the result is the same.

Even if we assume that the business key in the fact table refers to a missing key in the reference, then the loading task should be completed.

What's the matter here?

[jtompkins]

Do you have the Handle Duplicates checkbox selected?

[TimGarrod]

Compose will automatically detect if there are duplicate KEYS (aka the business key defined in the model) within the data set that it is processing.   

During a FULL LOAD - we do not expect to get duplicate business keys. 

During CDC processing, we may get duplicates (multiple updates to a single source record) - and thus you should enable "Handle Duplicates" for CDC processing. 

YOu could enable (as @jtompkins  suggests) "Handle Duplicates" for the "ACCOUNT" table load.  Note that when you do this, Compose will pick 1 record to load the target for each business key.

My initial question (looking at the model diagram) - do you have the "TRADING" source table actually mapped to the "ACCOUNT" entity?   (Is this a degenerative process where you want to select a DISTINCT set of values from "TRADING" to populate the "ACCOUNT" table?   

Do you not have a true source for "ACCOUNT" ? 

If the "TRADING" table has multiple values for the attributes in ACCOUNT - do you care which one is selected by de-duplication ? 

[vtatarnikov]

The ID account is not unique in the fact table, however it is the only business key in the fact table.

Does the fact table need to have a key that uniquely identifies each row?

If so, then this is the case. So I have to create a composite key from several keys.

Right?

[TimGarrod · Accepted solution]

Correct - I misunderstood (and thought the account table load was failing).   In a Compose model, each table needs to have a business  / natural key defined.  This can be simple or a composite key. 

Compose uses this key to determine if the incoming data is new or a change (and then processes the data according to the model characteristics - Type1 / Type2 attributes).    

[vtatarnikov]

Thanks it works