I'm trying to unpivot a fact table with below structure
source :
customer_id , product_id, week, measure1 , measure2 , measure3 ... measure10
CUST_101,PROD_101, W10,10,20,30...100
to
target :
customer_id , product_id,week,measure_code, measure_value
CUST_101,PROD_101, W10,'MSR1',10
CUST_101,PROD_101, W10,'MSR2',20
CUST_101,PROD_101, W10,'MSR3',30
CUST_101,PROD_101, W10,'MSR4',40
CUST_101,PROD_101, W10,'MSR5',50
CUST_101,PROD_101, W10,'MSR6',60
CUST_101,PROD_101, W10,'MSR7',70
CUST_101,PROD_101, W10,'MSR8',80
CUST_101,PROD_101, W10,'MSR9',90
CUST_101,PROD_101, W10,'MSR10',100
Data volume in source table : ~ 15 ~ 25 million records in incremental batches
I'm planning to use UNION ALL approach as suggested in Unpivot multiple columns in Snowflake
INSERT into Target (customer_id , product_id,week,measure_code, measure_value) select customer_id , product_id,week,'MSR1',measure1 from source union all select customer_id , product_id,week,'MSR2',measure2 from source ... select customer_id , product_id,week,'MSR10',measure10 from source
Is there a more optimal way to accomplish this? I'm aiming for the stored procedure to finish running in 30 minutes and using a medium size warehouse.
Thanks, Adarsh