Azure Data Explorer apply migration in Update Policy

Question

Looked all over Azure Data Explorer documentation for migration scenarios and I didn't manage to find an article on this. What I'm trying to do is to apply migration to incoming data and I thought of putting it in the Update Policy. I don't know if this is a good idea or not, let me know. Aside for this, I don't know if what I'm doing is good enough or if it could be made better.

I have table Target and table Source. Source has a dynamic Payload column and I'm mapping that column to the table Target IF it has a certain property. I did it as such:

let new_data = Source
| where Payload.Name == 'NameImLookingFor'
;
let good_data = new_data
| where isnull(Payload.DeprecatedField)
| project
    FieldA = todouble(Payload.FieldA),
    FieldB = todouble(Payload.FieldB),
    FieldC = todouble(Payload.FieldC)
;
let migrated_data = new_data
| where isnotnull(Payload.DeprecatedField) 
| project
    FieldA = iff(toint(Payload.DeprecatedField)==0,todouble(Payload.DeprecatedFieldValue), Payload.UndefinedMemeber),
    FieldB = iff(toint(Payload.DeprecatedField)==1,todouble(Payload.DeprecatedFieldValue), Payload.UndefinedMemeber),
    FieldC = iff(toint(Payload.DeprecatedField)==2,todouble(Payload.DeprecatedFieldValue), Payload.UndefinedMemeber)
;
good_data
| union migrated_data

I have some questions and incertitudes:

iff must have an else value specified. I want it to be null, but that type doesn't exist so I'm using Payload. some field that I'm sure it doesn't exists on the object so I have an empty value. Is this good enough? Could it be better?
I'm calling that iff 3 times, could a function be made for it? If yes, how and where? Should I place that in the update policy also or define it somewhere else?
Could it be done in a single query? I looked into case statement but I didn't feel like it would make my life easier.

Thanks.

Yoni L. Yoni L. · Accepted Answer · 2020-12-16T16:38:56

Using an update policy is valid (though, ideally, you'd fix the data at its source, if possible, before ingestion into Kusto/ADX).

You could replace your logic with the following:

Source
| where Payload.Name == 'NameImLookingFor'
| extend df = toint(Payload.DeprecatedField)
| project FieldA = case(isnull(df), todouble(Payload.FieldA), case(df == 0, todouble(Payload.DeprecatedFieldValue), double(null))),
          FieldB = case(isnull(df), todouble(Payload.FieldB), case(df == 1, todouble(Payload.DeprecatedFieldValue), double(null))),
          FieldC = case(isnull(df), todouble(Payload.FieldC), case(df == 2, todouble(Payload.DeprecatedFieldValue), double(null)))

Azure Data Explorer apply migration in Update Policy

1 Answers