Data ingestion issue with KQL update policy ; Query schema does not match table schema

I'm writing a function which takes in raw data table (contains multijson telemetry data) and reformat it to a multiple cols. I use .set MyTable <| myfunction|limit 0 to create my target table based off of the function and use update policy to alert my target table.

Here is the code :

.set-or-append MyTargetTable <| 
   myfunction
   | limit 0

.alter table MyTargetTable policy update 
@'[{ "IsEnabled": true, "Source": "raw", "Query": "myfunction()", "IsTransactional": false, "PropagateIngestionProperties": false}]'

But I'm getting ingestion failures: Here is the ingestion failure message :

Failed to invoke update policy. Target Table = 'MyTargetTable', Query = '

let raw = __table("raw", 'All', 'AllButRowStore') 
| where extent_id() in (guid(659e3b3c-6859-426d-9c37-003623834455));
myfunction()': Query schema does not match table schema

I double check the query schema and target table; they are the same . I'm not sure what this error means. Also, I ran count on both the raw and mytarget tables; there are relatively large discrepancies (400 rows for My target and 2000 rows in raw table).

Any advise will be appreciated.

Solution

Generally speaking - to find the root of the mismatch between schemas, you can run something along the following lines, and filter for differences:

myfunction
| getschema 
| join kind=leftouter (
    table('MyTargetTable')
    | getschema 
) on ColumnOrdinal, ColumnType

In addition - you should make sure the output schema of the function you use in your update policy is 'stable', i.e. isn't affected by the input data

The output schema of some query plugins such as pivot() and bag_unpack() depends on the input data, and therefore it isn't recommended to use those in update policies.