Snowflake实现临时表列转行逐值插入事实表的方法

我们正在向事实表加载数据，Snowflake平台上的原始临时表结构如下：
（原始临时表结构说明截图）
其中indicator_nbr类字段代表调研问卷中设置的不同问题。

我们采用数仓建模方法搭建数仓数据库，数据将按照如下结构写入目标事实表：
（目标事实表结构说明截图）
后续按照同样逻辑处理indicator_2、indicator_3等其余问题字段，每个字段及其对应的单独值将作为独立一行存储。当然还需要补充load_dt、record_src这类元数据字段，这部分实现不存在难点。

当前脚本的实现逻辑 #

• 将待处理字段存入数组，即fields_array = ['indicator_1', 'indicator_2', 'indicator_3']
• 对数组执行循环遍历，逐行将每个字段及其对应值写入目标表。假设临时表共100行数据，就需要逐次执行300次独立插入操作，循环代码片段如下：

for (var col_num = 0; col_num < fields_array.length; col_num = col_num + 1) {
    var COL_NAME = fields_array[col_num];
    var field_value_query = "INSERT INTO SAT_FIELD_VALUE SELECT md5(id), CURRENT_TIMESTAMP(), NULL, 'SRC', " + COL_NAME + ", md5(foreign_key_field) FROM " + TEMP_TABLE_NAME;
}

在StackOverflow相关讨论的评论中提到，更优的实现思路是通过字符串拼接，将各值整合到insert语句的from values子句中一次性执行，但该方案存在两个明显问题：

• Snowflake存在查询大小限制，单条查询语句需小于1MB；
• 如果逐字段循环拼接from values子句，仍需要从临时表执行select查询获取对应列的值，实际优化效果有限，仅能小幅降低执行耗时。

潜在实现思路 #

我们曾考虑编写SQL从临时表查询全量数据，完成哈希计算等全部处理逻辑后，将结果转置存入数组，但暂未找到明确的实现方法。

内容的提问来源于stack exchange，提问作者alim1990