Update Data trying to insert duplicates - Even after a Deduplication activity.

[CampaignerForLife]

Hello, I'm having a problem and I don't have any more idea of how to fix it.

I have a workflow that insert registries into database, first read a file, then deduplicate the data and then insert it. 

The Data schema has a compound primary key of 6 different attributes, defined like this:

Then In the deduplication, I search values deduplicating on all these fields

And then in the update data, I selected record identification using reconciliation keys and selected all the primary key's attributes.

But somehow, it keeps giving me this error: 

26-04-2024 14:45:08 ORA-210000 Oracle error: ORA-00001: unique constraint (U_ADOBECMP.IOP_PRIMARYKEY_D3F6991F) violated

I have checked and tried everything, but this error keeps happening. How can that happen if i'm deduplicating them? I tried to insert them using direct SQL code (INSERT INTO...) and it's inserted, but I've to remove "." before. Maybe it's having a problem reading it? has someone ever faced this and solved it??

Thank you in advance

@_Manoj_Kumar_ @ParthaSarathy @Parvesh_Parmar @DavidKangni

[rahulkad29786]

Hi @CampaignerForLife ,
Use an enrichment activity post your data loading and map your file or incoming payload data to the target schema in that enrichment and post that use the deduplication on different parameters and finally post that add a new split to check if the records already existing in the target schema(In which you are inserting the records).
This will remove this unique constraint issue.
Hope that helps.

Thanks

[CampaignerForLife]

This didn't work we're trying to recreate the workflow because it's the only one giving us errors. I think the main problem is, it's detecting as duplicates records that are not duplicated (If I generate the INSERT INTO codes, they're not duplicated!)

[rahulkad29786]

Unique constraint is when the record which you are trying to insert into the target schema is already present in it, so to have a split before it on PK is mandatory to resolve this issue.

[CampaignerForLife]

But how to explain, i'll tell you the steps we did

Truncate table - Data loading - Deduplication using the primary keys - update data. This would remove like 10 % of the data, but we still get duplicates. 

Then I tried with the enrichment, and would insert only 1 record.

I've checked the data itself using Python and, after the deduplication, there is not duplicated data. 

[_Manoj_Kumar_]

Hello @CampaignerForLife 

The data you are trying to ingest already existing in the Schema.

If you don't want to update the existing records. Enable the rejects option it will pass the existing records to reject transition.

If you want to update the existing records in schema then change the operation to insert or update

[CampaignerForLife]

Hello Manoj, the data does not exist in the schema. Before insert it, I execute a Truncate table command

[_Manoj_Kumar_ · Accepted solution]

Instead of using the SQL query to truncate table. Try using the Update data activity to delete the data and see if that works.

[Sukrity_Wadhwa]

Hi @CampaignerForLife,

Was the given solution helpful to resolve your query or do you still need more help here? Do let us know.

Thanks!