💫 SCD(Slowly Changing Dimensions) 란?
OLTP에서 발생된 마스터성 테이블은, 수시로 속성값이 변경될 가능성이 존재한다.그리고, 일반적인 DBMS에서 이러한 마스터 테이블의 모든 변경 이력을 관리하기 위해서는 그만큼 대용량의 저장공간(=money)가 필요하다.
따라서, 비교적 대용량 저장공간을 확보하는데 드는 비용이 저렴한 DW에서 DB의 마스터 테이블의 변경이력을 적재한다. 그리고 이러한 마스터 테이블은 DW 스타스키마에서 주로 차원으로 생성되어 사용되므로, 마스터 테이블의 이력을 관리한다는 것은 곧 차원의 이력을 관리한다는 것을 의미한다.
차원의 이력을 관리하는 기법에는 킴벌이 정의한 6가지 기법이 있다.
✨ Type 0 : 최초 값 유지
차원의 속성 값이 절대 변하지 않고, 최초값을 유지하는 경우이다.
차원이력관리가 도입되지 않은 DW에서 사용되는 기초적인 기법이지만, 필요에 의해 Type0를 사용하는 경우도 존재한다.
예를 들어, OLTP 마스터 테이블의 모든 속성이 절대 변하지 않는 값이라면 차원의 이력을 관리할 필요성이 없기 때문에 Type0를 사용할 수 있다.
[스키마 설명] 위의 회원마스터 테이블의 경우, 회원가입 시 생성되는 회원의 속성 값들로만 구성된 테이블이다. 그리고 해당하는 속성 값들이 최초로 생성된 이후 변경이 발생하지 않는다면, DW에서도 해당 테이블의 이력을 관리할 필요성이 없다. 따라서, 회원마스터 테이블에서 생성되는 row는 그대로 회원차원 테이블에 적재되고, 적재된 이후에는 변경 없이 그대로 최초의 값을 유지하게 된다.
/* Ansi SQL (PostgreSQL 11) */
/* 1. 초기 적재 쿼리 */
INSERT
INTO Member_dim -- 차원 테이블
SELECT *
FROM Member_m -- 원천 마스터 테이블
;
/* 2. 변경분 적재 쿼리 */
INSERT
INTO Member_dim -- 차원테이블
SELECT *
FROM Member_m -- 원천 마스터 테이블
WHERE load_ts BETWEEN '배치시작시간' AND '배치종료시간'
;[쿼리 설명] 초기적재와 변경분 적재의 쿼리에는 큰 차이점은 없으며, 변경분 적재의 경우 DELETE/TRUNCATE 등의 구문 없이 새롭게 추가된 Row가 차원에도 그대로 Insert되는 구조이다.
✨ Type 1 : 최신 값 유지
차원의 속성 값을 최신값으로만 유지하는 경우이다.
차원이력이 관리 되지 않는 DW에서 주로 사용하는 기법으로, 차원 생성로직이 간단하고 저장되는 데이터의 양이 적기 때문에 현업의 많은 케이스에서 Type 1를 채택하여 사용한다. 다만, Type 1 기법의 경우에도 차원 테이블의 과거 변경 이력에 대한 정보를 저장하고 있지 않기 때문에 분석할 수 있는 정보의 한계가 존재한다.
다차원 분석을 처음 시도하는 경우, 또는 차원의 변경이력 관리에 대한 현업의 요구사항이 없는 경우에만 사용하는 것을 권장한다.
[스키마 설명] 위의 회원마스터 테이블의 경우, Mod_ts (수정일시) 라는 속성 값이 추가되었다. 즉, 기존의 회원마스터 테이블과 달리 마스터 테이블에서 row의 변경이 발생할 수 있다는 것을 의미한다. Type 1에서는 마스터 테이블에서 변경이 발생하였을 때 최신 값으로 기존의 속성 값을 업데이트해야 하며, Mod_ts (수정일시) 값을 통해 변경분 적재 대상을 산정할 수 있다.
/* Ansi SQL (PostgreSQL 11) */
/* 1. 초기 적재 쿼리 */
INSERT
INTO Member_dim -- 차원 테이블
SELECT *
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 변경분 Delete & Insert */
DELETE
FROM Member_dim -- 차원 테이블
USING T_Member_dim_target tgt
WHERE Member_dim.member_no = tgt.member_no
;
INSERT
INTO Member_dim -- 차원테이블
SELECT *
FROM Member_m -- 원천 마스터 테이블
INNER JOIN T_Member_dim_target tgt
ON Member_m.member_no = tgt.member_no
;[쿼리 설명] Type 1의 경우, 변경분 적재 쿼리에서 원천 마스터 테이블에서 변경이 일어난 row들을 변경분 적재 대상으로 생성한다. 여기서 주의할 점은, 원천 마스터 테이블의 PK(= Natural Key) 값을 추출함으로써 원천 테이블과 차원테이블간의 데이터 무결성을 유지해야 한다는 점이다. 원천 테이블의 PK가 복합키(Composite Key)일 경우에는, 복합키 모두가 변경분 적재 대상에서 생성되어야 한다.
그리고 변경분이 적재되는 쿼리는 DELETE & INSERT 구문이다. 즉, 차원에 적재되어 있는 기존 데이터는 삭제하고 새롭게 변경된 Row(=최신값)을 차원에 다시 Insert하는 것이다. 이를 통해, 차원테이블에는 가장 최근의 변경이 일어난 최신 값만 적재되게 된다.
