I have an ADF which writes output of a Kusto Function to a Kusto Table daily. I need to upsert the data daily into the table. I did not find a way to update the existing data in Kusto DB. Is there any way I can insert row if data not exists with the grain else update the existing row through ADF?
Set or replace will be really expensive for me since the table consist of a huge set of data
If that is not possible Can I truncate the last 2 months data using the datetime stamp I have and reload it from function
ADX/Kusto is built for analytics, rather than OLTP, scenarios. Therefore, its design trade-offs favor very fast bulk Create (supporting high rates of inserts/appends of new records) and very fast bulk Read (supporting queries over large amounts of data). ADX/Kusto's support for Delete scenarios focuses on bulk-delete (mainly for retention period), and per-record deletion is not supported. Likewise, Updates of existing records is not supported in ADX/Kusto.
There are several techniques for dealing with "duplicate" data, which may allow you to simply ingest new records without doing what you call an upsert (which isn't an operation supported by ADX/Kusto, as explained above): https://learn.microsoft.com/en-us/azure/data-explorer/dealing-with-duplicates
Another option for you to consider is replacing data in bulk - by tagging and replacing data shards (extents), using extent-level control commands: