How to run "ingest" command - soft delete as a function whenever the database is updated in realtime to delete the duplicate?

Dear supporters,
I would like to ask for a solution. I am facing the problem of duplicated data on Kusto when synchronize data real-time. I have searched and find out the solution of deleting dup data as soft-delete. But ask I knew, it is a "system command" and it cannot be attached into a function and is called automatically when update policy - database is updated. Therefore, I would like to ask for any solution to call the soft delete whenever the database is updated so as to remove the duplicate data.
Regards,
TriNHM1

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-10-12T12:05:32.913+00:00

Hi @Nguyen Huu Minh Tri (UTOP.PROD) ,

Thank you for posting query in Microsoft Q&A Platform.

Soft delete is recommended only for infrequent deletes, and not if you constantly need to deduplicate all incoming records.

Any specific reason for considering only soft delete?

There are many different best practices covered for handling duplicates in Azure Data explorer in below official documentation. Kindly explore them. Please let me know how it goes.
https://learn.microsoft.com/en-us/azure/data-explorer/dealing-with-duplicates
Nguyen Huu Minh Tri (UTOP.PROD) 1 Reputation point

2022-10-13T02:13:23.657+00:00

Hi @ShaikMaheer-MSFT ,
I have go through these methods, but I saw soft delete is quite comfortable, because as our project, the data is manually cloned into the ADX database. As it is not updated the old line, it create new line with the new row.
For example

No Column A Column B CreatedAt UpdateAt
1 User A 10.0 10/10/2022 10/10/2022
2 ...
3 ...
4 User A 12.0 10/10/2022 13/10/2022

So the purpose I want to do is removed the line number one, which has the updateAt and CreatedAt different, then we search for the ColumnA user and then delete the line of number 1.
But it is done manually, we want to ask if there is any method to delete the old line automatically whenever there is a line that contain the updateat != createdat

Sorry if my illustration is not clear enough
Please let me know if there is any solution or question further
Regards

1 answer

Sander van de Velde 28,311 Reputation points MVP

2022-11-17T16:28:43.963+00:00
Hello,

although I'm not familiar with your architecture, I faced a potential duplication of data too.

Data ingested on a source table was duplicated to other target tables.

I use the soft delete though:

.alter-merge table MySourceTable policy retention softdelete = 0s

I'm encouraging you to just try it out.

See also my blog post.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment