sample type 2

gistfile1.txt

1. max surrogate key for dim
--drop table tmp_max_key_d_type2
create temporary table tmp_max_key_d_type2 as
select case when max(type2_key)is null then 1 else max(type2_key) end as max_key, 
trunc(getdate())as created_date
from d_type2
 
2. last record in dim for selected natural key
create temp table temp_d_type2_old 
as
select b.* 
from stg_type2 a
join (select * from d_type2
where is_current = 'Y')b on a.id = b.type2_id
where a.state <> b.state
or a.status <> b.status --additional check   

3. new valid record (for existing id in the dim and for new id)
--drop table temp_d_type2_new
create temp table temp_d_type2_new
as
--for existing id
select
a.id,
...
'Y' as is_current,
b.est_impression_count, --***CASE WHEN NEW RECORD SHOULD HAVE VALUES FROM PREVIOUS RECORD
b.est_spot_count, 
b.est_engagement_count, 
b.est_engagement_time,
b.est_cost, 
getdate() as effective_from,
'9999-12-31 00:00:00' as effective_to,
a.created_at as created_at,
getdate() as etl_date,
getdate() as etl_updated,
'MySQL' as etl_source
from stg_cmpgn_mgmt_type2 a
join temp_d_type2_old b on a.id = b.type2_id

UNION
--for new id
select
a.id,
...
'Y' as is_current,
1 as est_impression_count, --****DEFAULT VALUES
1 as est_spot_count, 
1 as est_engagement_count, 
1 as est_engagement_time, 
1 as est_cost,
getdate() as effective_from,
'9999-12-31 00:00:00' as effective_to,
a.created_at as created_at,
getdate() as etl_date,
getdate() as etl_updated,
'MySQL' as etl_source
from stg_cmpgn_mgmt_type2 a
where id not in (select type2_id from d_type2);

4. delete old record from d_type2
delete from d_type2
where type2_key in (select type2_key from temp_d_type2_old)
 
5. insert modified old record to d_type2
insert into d_type2
select type2_key,
type2_id,
...
'N' as is_current,
...
effective_from,
getdate() - 1as effective_to,
...
from temp_d_type2_old

6. insert new record in d_type2
insert into d_type2
select row_number ()over (partition by 1 order by 1)+ b.max_key,
a.id,
...
getdate(),--a.etl_date_updated,
a.etl_source
from temp_d_type2_new a
join tmp_max_key_d_type2 b on trunc(a.etl_date) = b.created_date