SCD2 Generic stored procedure in Snowflake

Slowly Changing Dimension Type 2 (SCD2) is a crucial component in data warehousing, extensively utilized to preserve historical data within dimension tables.

Traditionally, implementing SCD2 operations necessitates writing specific code for each table. However, the following stored procedure allows for the execution of SCD2 operations without the need for custom coding. This procedure requires four parameters: source_table, target_table, key_column, and non_key_columns (in array format).

The stored procedure dynamically generates and executes an SCD2 (Slowly Changing Dimension Type 2) merge statement at runtime based on the provided parameters.

Note: The target tables for SCD2 operations utilize the columns start_ts, end_ts, and active_ind, where end_ts = '9999-12-31 24:59:59' signifies an active record.

CREATE OR REPLACE PROCEDURE generic_scd2(source_table varchar,target_table varchar,key_col varchar,non_key_col array)
RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS     
  $$  
    var gen1 = "";
    var gen2 = "";
    var gen3 = "";
    var gen4 = "";

    for(var i = 0;i < NON_KEY_COL.length ; i = i + 1) {
       gen1 =  gen1 + ' or src.' + NON_KEY_COL[i] + '<>trg.'+ NON_KEY_COL[i];
       gen2 =  gen2 + ' or src_main.' + NON_KEY_COL[i] + '<>trg_scd2.'+ NON_KEY_COL[i];
       gen3 = gen3 + ',' +  NON_KEY_COL[i] ;
       gen4 = gen4 + ',src_main.' +  NON_KEY_COL[i] ;

    }
    gen1_final = gen1.substring(3);
    gen2_final = gen2.substring(3);
    gen3_final = KEY_COL + ',' + gen3.substring(1);
    gen4_final = 'src_main.' + KEY_COL + ',' + gen4.substring(1);

var scd2_query = 'merge into ' + TARGET_TABLE + ' trg_scd2 using ( select src.' + KEY_COL + ' as join_key , src.* from ' + SOURCE_TABLE + ' src union all select null as join_key , src.* from  ' + SOURCE_TABLE + ' src inner join ' + TARGET_TABLE + ' trg on src.' + KEY_COL + ' =trg.' + KEY_COL + ' where (' + gen1_final + ' ) and trg.end_ts = \'9999-12-31 23:59:59\') src_main on src_main.join_key = trg_scd2.' + KEY_COL + ' when matched and (' + gen2_final + ' ) and trg_scd2.end_ts = \'9999-12-31 23:59:59\' then update set trg_scd2.end_ts = current_timestamp,active_ind = 0 when not matched then insert (' + gen3_final + ', start_ts,end_ts,active_ind) values ('+ gen4_final + ', current_timestamp,cast(\'9999-12-31 23:59:59\' as timestamp),1)';

   try {

         var res = snowflake.execute ({sqlText: scd2_query});
         res.next();
         row_count = 'SCD2 Summary: No of rows inserted ' + res.getColumnValue(1) + ' and No of rows updated ' + res.getColumnValue(2);
         return row_count;

        }
    catch (err)  {
        return "Failed: " + err;   
        }
  $$
  ;

Calling stored procedure

Syntax:
call generic_scd2(<source_table>,<target_table>,<key_column>,<non_key_columns in array format>)

Example:
call generic_scd2('consumer_db.consumer_scm.emp','consumer_db.consumer_scm.emp_scd2','emp_id',array_construct('emp_name','emp_mobile','emp_city'))

Example:

create or replace table emp(
emp_id integer autoincrement,
emp_name string, 
emp_mobile integer, 
emp_city string
)

select * from emp_scd2

create or replace table emp_scd2(
emp_id_sr_key integer autoincrement,
emp_id integer ,
emp_name string, 
emp_mobile integer, 
emp_city string,
start_ts timestamp_ltz,
end_ts timestamp_ltz,
active_ind integer
)

insert into emp(emp_name,emp_mobile,emp_city)
values('Amit','8646579876','Kolkata'),
('Pankaj','9034567829','Pune'),
('Shailesh','5463789274','Chennai'),
('Roshan','3456902789','Delhi')

call generic_scd2('consumer_db.consumer_scm.emp','consumer_db.consumer_scm.emp_scd2','emp_id',array_construct('emp_name','emp_mobile','emp_city'))

update emp
set emp_mobile=8765456782,emp_city='Pune'
where emp_id = 1

call generic_scd2('consumer_db.consumer_scm.emp','consumer_db.consumer_scm.emp_scd2','emp_id',array_construct('emp_name','emp_mobile','emp_city'))

Source table after update

SCD table after update