We are trying to calculate metrics and store it in a target table in databricks in append mode and it is not working with error column not found as given below.
Column ‘file_id’ does not exist. Did you mean one of the following? [metric_subq.file_id, metric_subq.btch_id, metric_subq.shop, metric_subq.Record_Count]; line 4 pos 2
If i change the materialized to table, it is dropping and re-creating the table without issue, but old data is getting dropped.
Is it possible to store the metrics in append mode?
My metric model is as below
select
file_id file_id
, shop
, ‘btch_id’ btch_id
, Record_Count mtrc
from {{ metrics.calculate(metric(‘Record_Count’),dimensions=[‘file_id’,‘shop’,‘btch_id’]) }}