topic Re: ETL task aborted - Unexpected Primary Key violations detected in Qlik Compose

ETL task aborted - Unexpected Primary Key violations detected

vtatarnikov — Fri, 14 Mar 2025 20:49:07 GMT

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?

Re: ETL task aborted - Unexpected Primary Key violations detected

jtompkins — Thu, 21 Oct 2021 15:33:29 GMT

Do you have the Handle Duplicates checkbox selected?

Re: ETL task aborted - Unexpected Primary Key violations detected

TimGarrod — Thu, 21 Oct 2021 15:39:00 GMT

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 ?

Re: ETL task aborted - Unexpected Primary Key violations detected

vtatarnikov — Thu, 21 Oct 2021 15:48:46 GMT

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?

Re: ETL task aborted - Unexpected Primary Key violations detected

TimGarrod — Thu, 21 Oct 2021 15:52:01 GMT

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).

Re: ETL task aborted - Unexpected Primary Key violations detected

vtatarnikov — Fri, 22 Oct 2021 07:35:54 GMT

Thanks it works