Azure ADX - UpdatePolicy fails to insert data

Isn't this happing because you're trying to assign a full table (returned by G3MS_ClearAlarm(...)) to a scalar column (Result)?

Assuming G3MS_ClearAlarm(...) returns a table with Id, and SourceTable has a corresponding Id key column, can you try this?

.alter table TargetTable policy update
[
  {
    "IsEnabled": true,
    "Source": "SourceTable",
    "Query": "SourceTable
              | join kind=inner (
                  G3MS_ClearAlarm()
              ) on Id
              | project AlarmId, ClearAlarmId, ClearTime",
    "IsTransactional": true,
    "PropagateIngestionProperties": false
  }
]