Update Policy in Azure Data Explorer

ADX 156 Reputation points
2021-11-19T12:45:44.613+00:00

Hi

I have created a table called SalesTransaction & SalesDim tables in the ADX
SalesTransaction: Table holds the daily transaction data which we get on daily basis and it will be loaded through ADF pipeline (It always truncate & load i.e. Previous day data will be truncated & latest data will be loaded)
SalesDim: Table holds the historical data

Now i have created a function:

.create-or-alter function fnSalesData()
{
SalesTransaction
| join kind = leftouter(SalesDim|project HistSalesId=SalesId) on $left.SalesId==$right.HistSalesId
| where isnull(HistAlarmId)
}

and defined the update policy on SalesDim policy like below mentioned

.alter-merge table SalesDim policy update @'[{"IsEnabled": true, "Source": "SalesTransaction", "Query": "fnSalesData()", "IsTransactional": true, "PropagateIngestionProperties": false}]'

Whenever the data is inserted into SalesTransaction table the data is getting inserted twice in the SalesDim .

Note: I am trying to insert the transactional data into SalesDim table if the SalesId is not present.

Could you please help me what is the issue here.

Referred the document: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/updatepolicy

Thanks,
Kesav

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
248 questions
{count} votes