qtag to map function breaks results cache for enriched query history

[rymurr]

qtag_to_map is a udf written in javascript and it appears that snowflake won't use results cache with a javascript UDF even if its not projected. This results on all the enriched_query_history queries taking longer than they should.

[rymurr]

Removing it appears to mean one of several options:

1. push into MV and add a migration step
2. use something like the below to calculate w/o javascript. Same result, all done in sql so cache is respected. However this takes a long time

with fl as (
select qh.*, coalesce(value:SOURCE,'unknown') as k, value:KEY::varchar as kk, value:VALUE as v from internal_reporting_mv.query_history_complete_and_daily qh, lateral flatten(qtag) where RECORD_TYPE in ('COMPLETE_FIXED', 'DAILY') 
), grp_pre as (
select kk, any_value(v) as v, k, query_id from fl group by all
), grp as (
select object_agg(kk, v) as vv, k, query_id from grp_pre group by all
), grp2 as (
select object_agg(k, vv) as qtag_filter_2, query_id from grp group by all
)
select * from grp2

[joshelser]

I assume it's a non-starter to push the qtag unwrapping into a downstream view, e.g. REPORTING.QTAGGED_QUERY_HISTORY? Assuming it's a non-starter, pushing into the MV seems much better but I am wary given our recent problems in doing those migrations correctly.

[rymurr]

that won't get rid of the udf unless we materalize it. Like you said I am not keen on materializing it atm