0

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

GMB
  • 216,147
  • 25
  • 84
  • 135
adarsh.hv99
  • 21
  • 4
  • 2
    INSERT ALL from the same table should be faster than separate UNION ALLs: https://stackoverflow.com/a/72159364/5070879 – Lukasz Szozda May 15 '23 at 19:58
  • @adarsh - did you try this already? Can you report results? Give us a starting point – Felipe Hoffa May 16 '23 at 03:12
  • 1
    this worked fine! source volume ~ 3 billion, target ( aggregated at higher level and about 15 measures unpivoted ) : 76 million time taken : 3 to 4 minutes thank you @LukaszSzozda ! – adarsh.hv99 May 17 '23 at 01:19

0 Answers0