차원이력관리기법(SCD) - Type 2, Type 3

✨ Type 2 : 신규 Row 추가

[기법 설명] 차원이력관리의 가장 기본적인 기법으로, 변경된 이력을 신규 Row로 새롭게 추가하는 것을 의미한다. 그리고, 각 row에 대해서 유효한 이력 범위를 명시하는 이력시작일시,이력종료일시와 같은 칼럼이 추가된다. 마지막으로, 해당하는 이력이 최신값인지에 대한 여부를 명시하는 플래그 속성을 추가한다.
Type 1에서는 원천 마스터 테이블의 PK를 기준으로 차원 테이블의 데이터 무결성이 동일하게 지켜지기 때문에 차원 테이블에서 별도의 Key를 생성할 필요가 없었다. 하지만 Type 2부터는 원천테이블의 PK에 대해서 이력이 발생한 만큼 새로운 Row가 생성되기 때문에 차원 테이블에서 관리되는 별도의 Key가 필요하다. DW에서는 BI에서의 Join 성능을 향상시키기 위해서 차원테이블에 별도의 Key로 대체키 (Surrogate Key)를 생성하는 것을 권장한다. 다만, BI를 사용하지 않는 경우 또는 대체키 사용이 익숙하지 않은 경우에는 원천 테이블의 PK (=Natural Key)와 이력시작일시, 이력종료일시를 함께 복합키로 사용한다면 테이블의 무결성을 유지할 수 있다.
대체키의 개념과 성능 이점은 추후 새로운 글로 기재된 예정입니다.


[스키마 설명] 위의 차원 테이블 (Member_Dim)의 경우, 기존 원천테이블에 비해서 새로운 속성 값이 추가된 것을 확인할 수 있다. 우선, 원천테이블의 PK (=Natural Key)만을 PK로 사용하는 것이 아닌 이력시작일시와 이력종료일시를 복합키로 사용하여 테이블의 무결성을 유지하였다. 위의 방식은 대체키(Surrogate Key)를 사용하지 않는 방식이며, 대체키를 사용한다면 PK를 대체키 하나로 설정하여 사용할 수 있다.
각 Row가 의미하는 바는, 각 회원 속성에 대한 이력범위이며 최신 이력이 되는 Row는 각 회원 당 하나의 Row만 존재한다. 그리고 최신값은 Current_Value_Yn 속성을 통해서 판별할 수 있다.
이력이 적재되는 주기는, 배치 주기에 따라서 상이하다. 즉, 배치 주기가 짧을수록 더 많은 이력들을 적재할 수 있고 배치 주기가 길수록 적은 이력이 적재되게 된다.

/* Ansi SQL (PostgreSQL 11) */
/* 1. 초기 적재 쿼리 */
INSERT
  INTO Member_dim  -- 차원 테이블
SELECT Member_No                        AS member_no
     , sysdate                          AS hist_start_ts
     , '9999-12-31 23:59:59'::timestamp AS hist_end_ts
     , Name                             AS name
     , Email                            AS email
     , 'Y'                              AS current_value_yn
     , sysdate                          AS load_ts
  FROM Member_m    -- 원천 마스터 테이블
;

/* 2. 변경분 적재 쿼리 */
/* 2.1 변경분 적재 대상 선정 */
DROP TABLE IF EXISTS T_Member_dim_target;
CREATE TEMPORARY TABLE T_Member_dim_target
AS
SELECT member_no
  FROM Member_M    -- 원천 마스터 테이블
 WHERE mod_ts BETWEEN '배치시작시간' AND '배치종료시간'
;

/* 2.2 변경분 Update & Insert */
UPDATE Member_dim  -- 차원 테이블
   SET hist_end_ts = '배치시작시간'
     , current_value_yn = 'N'
  FROM T_Member_dim_target tgt
 WHERE Member_dim.member_no = tgt.member_no
   AND Member_dim.current_value_yn = 'Y'
;
 
INSERT
  INTO Member_dim  -- 차원 테이블
SELECT tgt.Member_No                    AS member_no
     , '배치시작시간'                     AS hist_start_ts
     , '9999-12-31 23:59:59'::timestamp AS hist_end_ts
     , Name                             AS name
     , Email                            AS email
     , 'Y'                              AS current_value_yn
     , sysdate                          AS load_ts
  FROM Member_m    -- 원천 마스터 테이블
 INNER JOIN T_Member_dim_target tgt
    ON Member_m.member_no = tgt.member_no
;

[쿼리 설명] Type 2의 경우, 초기 적재 쿼리에서도 기존 로직과 달리, 마스터 테이블의 속성과 함께 3개의 신규 칼럼이 추가된 것을 확인할 수 있다. 초기 적재되는 row는 모두 최신값 상태이기 때문에, current_value_yn 속성값은 'Y'로 적재된다.
변경분 적재 쿼리에서 원천 마스터 테이블에서 변경이 일어난 row들을 변경분 적재 대상으로 생성한다. 그리고 변경분이 적재되는 쿼리는 UPDATE & INSERT 구문이다. 기존 차원에 적재되어 있는 데이터는 신규 이력이 생성됨에 따라서 과거 이력 값으로 UPDATE 처리를 해야 한다. 기존 이력 값 중, 가장 최신의 값만을 조회하여 (Where current_value_yn ='Y') 과거 이력 값으로 업데이트 하는(SET 구문) 로직을 확인할 수 있다.
다음으로는, 새롭게 추가된 이력값을 최신이력 상태로 INSERT를 해주어야 한다. Insert 대상이 되는 Row들은 최신 이력에 해당하는 값이므로, Current_value_yn 값은 'Y'가 되어야 하고, 이력종료일시 또한 임의의 timestamp 최대값으로 입력되어야 한다. 여기서 주의할 점은, 이력 시작일시 값에 어떤 값을 부여할지이다. 위의 로직에서는 이전 이력의 이력종료일시(hist_end_ts와 다음 이력의 이력시작일시(hist_start_ts)값이 동일한 값 ('배치시작시간')으로 입력된다. 위의 경우의 이점은, 이력 데이터 간의 join이 필요할 때 기존 PK (=Natural Key)와 이력종료일시 또는 이력시작일시를 조인 KEY로 사용하여 Join을 할 수 있다는 점이다.
반면 위의 경우의 단점은, 차원테이블을 사용할 때 Where 조건에서 Between 구문이 아닌 >=, < 또는 >. <= 구문을 사용하여야 한다는 점이다. 왜냐하면, 팩트테이블에서 특정 시점을 차원테이블과 between조건으로 join할 때 운이 나쁘면 차원테이블에서 2개의 row가 join 될 수 있기 때문이다.(극히 드문 case이기는 하다) 이와 같은 상황을 방지하기 위해서는, '배치시작시간'에 1초의 시간을 빼서 값을 업데이트해야 한다.

✨ Type 3 : 신규 속성 추가

[기법 설명] 대체(가상)현실이라고도 불리는 기법으로, 이전 이력에 대한 정보를 속성으로 추가하여 관리하는 기법이다. 그리고, 이전 이력의 유효한 시간 범위를 명시하는 속성을 추가하여 사용하기도 한다. 자주 활용되는 기법이 아니므로, 기법에 대한 자세한 설명은 생략했습니다.


[스키마 설명] 위의 차원 테이블 (Member_Dim)의 경우, 각 속성 값에 따라서 이전 속성 칼럼이 추가되어 이력이 관리된다는 점을 확인할 수 있다. 또한, 해당하는 이력의 유효한 이력 범위를 명시하기 위해서 이력종료일시(Hist_End_Ts)가 추가되었고 이는 원천테이블의 PK (=Natural Key)와 함께 차원테이블의 복합키(Composite Key)의 역할을 한다.

/* Ansi SQL (PostgreSQL 11) */
/* 1. 초기 적재 쿼리 */
INSERT
  INTO Member_dim  -- 차원 테이블
SELECT Member_No                        AS member_no
     , '9999-12-31 23:59:59'::timestamp AS hist_end_ts
     , Name                             AS name
     , Null                             AS pre_name
     , Email                            AS email
     , Null                             AS pre_email
     , sysdate                          AS load_ts
  FROM Member_m    -- 원천 마스터 테이블
;

/* 2. 변경분 적재 쿼리 */
/* 2.1 변경분 적재 대상 선정 */
DROP TABLE IF EXISTS T_Member_dim_target;
CREATE TEMPORARY TABLE T_Member_dim_target
AS
SELECT member_no
  FROM Member_M    -- 원천 마스터 테이블
 WHERE mod_ts BETWEEN '배치시작시간' AND '배치종료시간'
;

/* 2.2 변경분 Update & Insert */
UPDATE Member_dim  -- 차원 테이블
   SET hist_end_ts = '배치시작시간'
  FROM T_Member_dim_target tgt
 WHERE Member_dim.member_no = tgt.member_no
   AND Member_dim.hist_end_ts = '9999-12-31 23:59:59'::timestamp
;
 
INSERT
  INTO Member_dim      -- 차원 테이블
SELECT tgt.Member_No                    AS member_no
     , '9999-12-31 23:59:59'::timestamp AS hist_end_ts
     , Name                             AS name
     , pre.Name                         AS pre_name
     , Email                            AS email
     , pre.email                        AS pre_email
     , sysdate                          AS load_ts
  FROM Member_m        -- 원천 마스터 테이블
 INNER JOIN T_Member_dim_target tgt
    ON Member_m.member_no = tgt.member_no
  LEFT JOIN Member_dim          pre
    ON Member_m.member_no = pre.member_no
   AND pre.hist_end_ts = '배치시작시간'
;

[쿼리 설명] Type 3의 경우, 초기 적재 쿼리에서 원천테이블의 PK (=Natural Key)와 함께 이력종료일시를 추가하여 차원테이블의 복합키로 사용하는 것을 확인할 수 있다. 또한, Type 3의 특징인 이전속성 칼럼의 경우에는 초기 적재되는 Row이므로, Null 값이 들어가는 것을 확인할 수 있다.
변경분이 적재되는 쿼리에서는, 기존 이력에 대해서 이력종료일시를 배치시작시간으로 UPDATE해줌으로써 기존 이력이 더이상 최신값이 아님을 명시하였다. 반면 신규 적재되는 Row에 대해서는 초기 적재쿼리와 마찬가지로 이력종료일시를 임의의 최대값을 입력하여 해당 이력이 최신 값임을 명시하도록 하였다.
또한, 이전 속성값 칼럼을 가지고오기 위해서 위에서 UPDATE 되었던 이전 이력에 해당하는 Row를 Join하여 Pre로 시작하는 이전속성 값에 해당하는 속성값들을 INSERT하였다. 만약, 이전 이력에 해당하는 값이 없는 경우라면 (= 즉 원천 마스터 테이블에서 신규로 생성된 Row라면) pre 테이블이 LEFT로 JOIN되었기 때문에 초기적재와 마찬가지로 NULL값이 pre로 시작하는 이전속성에 들어갈 것이다.