데이터 이력 관리가 꼬이기 시작했다면? SCD Type 2 복잡성, 실제로 풀어낸 이야기

SCD Type 2 구현 시 겪었던 데이터 이력 관리의 복잡성을 실무 사례를 통해 풀어봅니다. 주니어 개발자가 놓치기 쉬운 함정과 베스트 프랙티스를 공유하며, 깔끔한 데이터 관리를 위한 노하우를 얻어가세요.

안녕하세요, 주니어 데이터 엔지니어 여러분! 데이터를 다루다 보면 늘 같은 고민에 부딪히곤 합니다. "고객 정보가 바뀌었는데, 이전 정보도 남겨야 할까?", "상품 가격이 변동되면, 과거 가격으로 매출 분석은 어떻게 하지?" 이런 질문들이 머릿속을 맴돌 때, 우리는 데이터 이력 관리라는 거대한 산 앞에 서게 됩니다.

특히 데이터웨어하우스(DW)나 데이터 레이크하우스(DLH) 환경에서 분석용 데이터를 구축할 때, 기준 정보(Dimension Data)의 변경 이력을 효과적으로 관리하는 것은 정말 중요합니다. 제가 처음 SCD(Slowly Changing Dimensions) Type 2를 접했을 때, 그 개념은 간단해 보였지만 막상 구현해보니 예상치 못한 복잡성 때문에 애를 먹었던 기억이 생생합니다. 오늘은 제가 직접 겪었던 SCD Type 2 구현의 복잡성과 이를 해소하기 위한 실전 노하우를 여러분과 공유해보려고 합니다. 주니어 개발자로서 데이터 이력 관리에 대한 감을 잡고, 실수를 줄이는 데 도움이 되기를 바랍니다.

📑 목차

• SCD Type 2, 왜 필요할까요? 데이터 이력 관리의 중요성
• SCD Type 2 구현, 생각보다 복잡한 이유
• 데이터 중복과 쿼리 복잡성
• ETL 파이프라인 설계의 어려움
• 복잡성 해소를 위한 핵심 전략: 효과적인 키 설계와 인덱싱
• 자연키 vs. 대리키 (Surrogate Key)
• 성능을 위한 인덱싱 전략
• ETL 파이프라인 설계, 실수를 줄이는 방법
• 변경 감지 (Change Data Capture, CDC)
• Upsert 로직 구현
• 복잡한 시나리오에 대비하기: 고려해야 할 사항들
• 늦게 도착하는 데이터 (Late Arriving Data)
• 대용량 데이터 처리 전략
• 데이터 품질 관리
• 실제로 적용해보니: 주니어 개발자를 위한 팁
• 마무리하며: 복잡성 너머의 가치를 찾아서

Image by Pexels on Pixabay

SCD Type 2, 왜 필요할까요? 데이터 이력 관리의 중요성

SCD Type 2는 말 그대로 '느리게 변하는 차원'의 변경 이력을 관리하는 방법 중 하나입니다. 데이터웨어하우스의 차원 테이블(Dimension Table)은 비즈니스 분석의 '측정 기준'이 되는 정보를 담고 있습니다. 예를 들어, '고객' 차원 테이블에는 고객의 이름, 주소, 등급 같은 정보가 담기겠죠. 그런데 이 정보들이 시간이 지나면서 변할 수 있습니다. 고객이 이사를 가거나, 등급이 오르내리는 것처럼 말이죠.

만약 단순히 고객 정보가 바뀔 때마다 기존 데이터를 덮어쓴다면 어떻게 될까요? 과거 데이터와 현재 데이터를 함께 분석할 때 문제가 발생합니다. "작년 A 지역 매출액"을 분석하고 싶은데, 그 사이 A 지역에 살던 고객이 B 지역으로 이사 가버렸다면, 작년 매출액이 B 지역으로 잡혀버리는 오류가 생기는 겁니다. 이런 경우를 방지하고, 시간의 흐름에 따른 정확한 분석을 가능하게 하는 것이 바로 SCD Type 2의 역할입니다.

SCD Type 2는 변경이 발생할 때마다 새로운 레코드를 추가하고, 이전 레코드는 '만료' 처리하여 모든 변경 이력을 유지합니다. 이렇게 하면 특정 시점의 데이터 상태를 정확하게 재구성할 수 있어, 시계열 분석이나 과거 시점의 스냅샷 분석 등 다양한 비즈니스 요구사항을 충족할 수 있습니다. 제가 직접 해보니, 처음에는 데이터가 불어나는 것에 대한 부담이 있었지만, 결국 데이터의 신뢰성과 분석의 깊이를 위해서는 필수적인 선택이라는 것을 깨달았습니다.

SCD Type 2 구현, 생각보다 복잡한 이유

SCD Type 2의 개념은 "바뀌면 새 레코드 추가하고 이전 건 만료시키기"로 간단하게 들립니다. 하지만 실제로 구현해보면 몇 가지 복병을 만나게 됩니다. 제가 처음 겪었던 주요 복잡성들을 정리해봤습니다.

데이터 중복과 쿼리 복잡성

가장 먼저 맞닥뜨리는 문제는 데이터 중복입니다. 동일한 고객이라도 주소가 세 번 바뀌었다면, 그 고객에 대한 레코드가 세 개가 됩니다. 이 중 현재 유효한 레코드를 찾으려면 `CURRENT_FLAG`나 `END_DATE` 같은 필드를 이용해 필터링해야 합니다. 예를 들어, 현재 유효한 고객의 모든 정보를 가져오려면 다음과 같은 조건이 필요합니다.

SELECT *
FROM customer_dimension
WHERE current_flag = 'Y';

혹은

SELECT *
FROM customer_dimension
WHERE end_date IS NULL OR end_date = '9999-12-31';

이런 조건이 모든 분석 쿼리에 들어가야 합니다. 만약 이 조건을 누락한다면, 과거 데이터까지 함께 조회되어 엉뚱한 결과가 나올 수 있습니다. 처음에는 이 필터링을 빼먹어서 "왜 이렇게 데이터가 많지?" 하고 한참 헤맸던 기억이 있습니다.

ETL 파이프라인 설계의 어려움

SCD Type 2 구현의 핵심은 ETL(Extract, Transform, Load) 파이프라인에 있습니다. 원본 시스템에서 변경된 데이터를 감지하고, 이를 차원 테이블에 반영하는 로직이 복잡합니다. 단순히 `INSERT`나 `UPDATE`만으로는 해결되지 않기 때문입니다. 새로운 데이터가 들어올 때마다 다음을 판단하고 실행해야 합니다.

1. 새로운 고객인가? -> 새 레코드 INSERT
2. 기존 고객인데 정보가 변경되었는가? -> 기존 레코드 UPDATE (종료 처리) 후 새 레코드 INSERT
3. 기존 고객인데 정보 변경이 없는가? -> 아무것도 하지 않음 (혹은 `UPDATE`하여 `last_updated_date`만 갱신)

이러한 로직을 정확하게 구현하고, 성능과 데이터 정합성을 동시에 잡는 것이 정말 까다로웠습니다. 특히 대용량 데이터에서 변경 감지 로직이 비효율적이면 ETL 시간이 엄청나게 길어지기도 합니다.

복잡성 해소를 위한 핵심 전략: 효과적인 키 설계와 인덱싱

이러한 복잡성을 해소하기 위해 제가 가장 먼저 신경 썼던 부분은 키 설계와 인덱싱이었습니다.

자연키 vs. 대리키 (Surrogate Key)

SCD Type 2에서는 대리키(Surrogate Key) 사용이 거의 필수적입니다. 자연키(Natural Key)는 원본 시스템에서 사용하는 고유 식별자(예: 고객 ID)를 의미하고, 대리키는 데이터웨어하우스 내에서 각 레코드를 고유하게 식별하기 위해 인위적으로 생성하는 키입니다. SCD Type 2는 동일한 자연키를 가진 여러 레코드가 존재할 수 있으므로, 각 레코드를 고유하게 식별할 수 있는 대리키가 필요합니다.

다음은 자연키와 대리키의 비교 및 SCD Type 2 적용 시 장단점입니다.

구분	자연키 (Natural Key)	대리키 (Surrogate Key)
정의	원본 시스템의 고유 식별자 (예: 고객 ID, 상품 코드)	DW 내에서 각 레코드에 부여되는 인위적인 고유 식별자 (예: 시퀀스 번호)
SCD Type 2 적용 시	동일 자연키로 여러 레코드 존재 (이력별) 이력 레코드 자체를 고유하게 식별 불가 팩트 테이블 조인 시 복잡한 조건 필요 (날짜 범위, 현재 플래그)	각 이력 레코드를 고유하게 식별 팩트 테이블과의 조인 시 단일 키로 간편하게 조인 데이터 모델의 독립성 및 유연성 확보
장점	원본 시스템과의 일관성 유지	SCD Type 2 구현 필수적 간결한 조인 조건 데이터 변경에 독립적 성능 향상
단점	SCD Type 2 구현 어려움 팩트 테이블 조인 복잡성 및 성능 저하	원본 시스템과의 직접적인 연관성 부족

제가 대리키를 도입했을 때, 팩트 테이블(Fact Table)과의 조인 로직이 훨씬 간결해지고 쿼리 성능도 크게 개선되는 것을 체감했습니다. 팩트 테이블은 대리키를 외래키로 참조하여 특정 시점의 차원 데이터를 쉽게 가져올 수 있게 됩니다.

성능을 위한 인덱싱 전략

SCD Type 2 테이블은 데이터량이 많아지기 때문에 인덱싱이 매우 중요합니다. 특히 다음 컬럼들에 인덱스를 걸면 성능을 크게 개선할 수 있습니다.

• 대리키(Surrogate Key): 기본 키로 설정하여 고유성을 보장하고 빠른 접근을 가능하게 합니다.
• 자연키(Natural Key): 변경 감지를 위해 원본 시스템의 고유 ID를 조회해야 하므로 인덱싱이 필수입니다.
• `current_flag` 또는 `end_date`: 현재 유효한 레코드를 빠르게 필터링하기 위해 인덱스를 걸어야 합니다. 특히 부분 인덱스(Partial Index)를 `current_flag = 'Y'` 또는 `end_date IS NULL` 조건에 거는 것이 매우 효과적입니다. 현재 유효한 레코드는 전체 레코드 중 소수이기 때문에, 부분 인덱스는 검색 범위를 크게 줄여줍니다.
• `start_date`: 특정 시점의 이력을 조회할 때 유용합니다.

인덱스를 제대로 활용하면 ETL 과정에서의 변경 감지 속도가 빨라지고, 분석가들이 쿼리를 실행할 때 데이터 조회 성능이 비약적으로 향상됩니다. 제가 실제 프로젝트에서 인덱스 설계를 최적화하면서 ETL 실행 시간이 30% 이상 단축되는 경험을 했습니다.

Image by geralt on Pixabay

ETL 파이프라인 설계, 실수를 줄이는 방법

SCD Type 2의 핵심은 역시 ETL 파이프라인입니다. 제가 처음 설계할 때 가장 어려웠던 부분이고, 여러 번의 시행착오를 거쳐 나름의 베스트 프랙티스를 찾았습니다.

변경 감지 (Change Data Capture, CDC)

원본 시스템의 데이터 변경을 효율적으로 감지하는 것이 SCD Type 2 ETL의 첫 단추입니다. 몇 가지 방법이 있습니다.

• 타임스탬프 기반(Timestamp-based): 원본 테이블에 `last_updated_timestamp` 같은 컬럼이 있다면, 이전 ETL 실행 시점 이후 변경된 데이터만 가져올 수 있습니다. 가장 흔하고 구현하기 쉬운 방법입니다.
• 해시 값 비교(Hash value comparison): 주요 속성들을 조합하여 해시 값을 생성하고, 이 해시 값을 비교하여 변경 여부를 판단합니다. 데이터량이 많을 때 효율적일 수 있습니다.
• CDC 툴 활용: 데이터베이스의 트랜잭션 로그를 기반으로 변경을 실시간으로 감지하는 전문 CDC 툴(예: Debezium, Fivetran)을 사용하는 것이 가장 이상적입니다. 하지만 도입 비용과 복잡성이 높을 수 있습니다.

저는 주로 타임스탬프 기반의 변경 감지를 사용했고, 여기에 원본 데이터와 DW 차원 테이블의 현재 레코드를 조인하여 변경된 속성을 찾아내는 방식을 사용했습니다. 단순히 `UPDATE`만 하는 것이 아니라, 어떤 필드가 변경되었는지 정확히 파악하는 것이 중요합니다.

Upsert 로직 구현

변경이 감지되면, DW 차원 테이블에 이를 반영하는 Upsert(Update + Insert) 로직이 필요합니다. 제가 주로 사용하는 방식은 다음과 같습니다.

1. 기존 레코드 종료 처리: 원본 시스템의 자연키와 동일한 `natural_key`를 가지면서 `current_flag = 'Y'`인 레코드를 찾습니다. 이 레코드의 `current_flag`를 'N'으로 변경하고, `end_date`를 현재 시점(혹은 이전 시점)으로 업데이트합니다.
2. 새 레코드 삽입: 변경된 속성들을 포함하여 새로운 레코드를 삽입합니다. 이 레코드의 `current_flag`는 'Y'로, `start_date`는 현재 시점으로 설정하고, `end_date`는 '9999-12-31'과 같은 미래 값이나 `NULL`로 설정합니다.

예시를 통해 설명해볼게요. 고객 테이블에 '홍길동'의 주소가 변경되었다고 가정합시다. (간소화된 예시)

-- 1. 기존 레코드 종료 처리 (PostgreSQL 기준)
UPDATE customer_dimension
SET
    end_date = CURRENT_DATE - INTERVAL '1 day', -- 변경 전날로 종료
    current_flag = 'N'
WHERE
    natural_key = 'CUST001' AND current_flag = 'Y'
    AND EXISTS (
        SELECT 1 FROM incoming_data
        WHERE incoming_data.natural_key = customer_dimension.natural_key
          AND incoming_data.address <> customer_dimension.address -- 주소 변경 감지
    );

-- 2. 새 레코드 삽입
INSERT INTO customer_dimension (
    surrogate_key, natural_key, name, address,
    start_date, end_date, current_flag
)
SELECT
    NEXTVAL('customer_seq'), -- 새로운 대리키 생성
    i.natural_key, i.name, i.new_address,
    CURRENT_DATE, '9999-12-31', 'Y'
FROM incoming_data i
WHERE
    i.natural_key = 'CUST001'
    AND EXISTS (
        SELECT 1 FROM customer_dimension cd
        WHERE cd.natural_key = i.natural_key
          AND cd.address <> i.new_address -- 주소 변경 감지
          AND cd.current_flag = 'Y'
    )
    OR NOT EXISTS ( -- 또는 새로운 고객인 경우
        SELECT 1 FROM customer_dimension cd
        WHERE cd.natural_key = i.natural_key
    );

이 SQL은 간소화된 예시이며, 실제로는 `MERGE` 문(SQL Server, Oracle)이나 `UPSERT` 구문(PostgreSQL `ON CONFLICT`)을 활용하면 훨씬 깔끔하게 구현할 수 있습니다. 중요한 것은 기존 레코드의 종료 시점과 새 레코드의 시작 시점을 정확히 정의하여 데이터 이력의 연속성을 보장하는 것입니다. 제가 처음에는 종료 시점을 '현재 시점'으로 잘못 설정해서 하루 동안 데이터 공백이 발생했던 적이 있습니다. 항상 변경 전날로 종료하고 변경 당일로 시작하는 것이 일반적인 관례입니다.

복잡한 시나리오에 대비하기: 고려해야 할 사항들

실제 프로젝트에서는 항상 예상치 못한 시나리오가 발생하기 마련입니다. 몇 가지 복잡한 상황과 그에 대한 대비책을 공유합니다.

늦게 도착하는 데이터 (Late Arriving Data)

원본 시스템의 문제나 네트워크 지연 등으로 인해 데이터가 정해진 시간보다 늦게 도착할 수 있습니다. SCD Type 2에서는 늦게 도착한 데이터가 과거의 특정 시점 사이에 끼어들어야 할 때 문제가 복잡해집니다.

• 처리 방법: 늦게 도착한 데이터를 포함하여 해당 자연키의 모든 이력을 다시 정렬하고, `start_date`와 `end_date`, `current_flag`를 재조정해야 합니다. 이는 상당한 리소스와 복잡한 로직을 요구합니다.
• 실전 팁: 가능하면 원본 시스템에서 데이터 전송 시점의 정확성을 높이거나, 늦게 도착하는 데이터의 비율이 매우 낮은 경우 단순 무시하거나 가장 최근 이력으로 처리하는 정책을 고려할 수도 있습니다. 하지만 분석의 정확성이 중요할 경우, 재처리 로직을 갖추는 것이 좋습니다.

대용량 데이터 처리 전략

차원 테이블의 레코드가 수억 개를 넘어가면, ETL 성능이 급격히 저하됩니다. 이럴 때는 다음과 같은 전략을 고려해야 합니다.

• 파티셔닝(Partitioning): `natural_key`의 해시 값이나 `start_date`를 기준으로 테이블을 파티셔닝하여, 검색 및 업데이트 범위를 줄일 수 있습니다.
• 증분 처리(Incremental Processing): 변경된 데이터만 가져와서 처리하는 것은 기본이고, ETL 과정 자체도 작은 배치 단위로 쪼개어 처리하는 것이 좋습니다.
• 병렬 처리(Parallel Processing): 여러 ETL 워커가 동시에 데이터를 처리하도록 구성하여 전체 처리 시간을 단축합니다. Spark, Flink 같은 분산 처리 프레임워크를 활용하는 것도 좋은 방법입니다.

데이터 품질 관리

SCD Type 2는 이력 관리를 하다 보니, 데이터 품질 문제가 더 도드라질 수 있습니다. 예를 들어, 동일한 자연키에 대해 `current_flag = 'Y'`인 레코드가 두 개 이상 존재하거나, `start_date`와 `end_date`가 겹치거나 구멍이 생기는 경우 등입니다. 이런 문제는 분석 결과의 오류로 직결되기 때문에, ETL 후 데이터 품질 검증 로직을 반드시 추가해야 합니다.

-- current_flag = 'Y'인 레코드가 중복되는 자연키 찾기
SELECT natural_key, COUNT(*)
FROM customer_dimension
WHERE current_flag = 'Y'
GROUP BY natural_key
HAVING COUNT(*) > 1;

-- start_date와 end_date가 겹치거나 구멍이 생기는 자연키 찾기
-- (복잡하므로 별도 함수나 프로시저로 구현하는 경우가 많음)

이러한 검증 쿼리들을 ETL 파이프라인의 일부로 자동화하고, 이상 감지 시 알림을 보내는 시스템을 구축하는 것이 중요합니다.

Image by geralt on Pixabay

실제로 적용해보니: 주니어 개발자를 위한 팁

제가 SCD Type 2를 구현하고 운영하면서 얻었던 몇 가지 실전 팁을 드리고 싶습니다.

1. 시작은 작게, 점진적으로 확장하세요: 처음부터 완벽한 SCD Type 2 파이프라인을 구축하려고 하지 마세요. 하나의 차원 테이블에 대해 가장 기본적인 로직부터 구현하고, 충분히 테스트한 후 점차 다른 테이블로 확장하거나 복잡한 시나리오를 추가하는 것이 좋습니다.
2. 명확한 문서화는 필수입니다: 데이터 모델, ETL 로직, 각 필드의 정의, 그리고 특히 `start_date`, `end_date`, `current_flag`의 의미와 사용 규칙을 명확하게 문서화해야 합니다. 동료들과의 협업은 물론, 시간이 지난 후 본인이 다시 로직을 파악할 때도 큰 도움이 됩니다.
3. 테스트 코드를 작성하세요: 다양한 변경 시나리오(새로운 데이터, 기존 데이터 변경, 변경 없음, 늦게 도착하는 데이터 등)에 대한 테스트 케이스를 만들고, ETL 로직의 유효성을 검증하는 테스트 코드를 작성하는 것이 중요합니다. 특히 `start_date`와 `end_date`의 경계값 테스트는 필수입니다.
4. 동료와의 코드 리뷰를 적극 활용하세요: 저보다 경험 많은 선배 개발자에게 코드 리뷰를 요청하여, 놓치기 쉬운 부분이나 비효율적인 로직을 일찍 발견할 수 있었습니다. 주니어 때는 혼자 고민하기보다 적극적으로 도움을 요청하는 것이 성장하는 지름길입니다.
5. 데이터를 직접 쿼리해보세요: ETL을 돌린 후, 직접 SQL 쿼리를 작성하여 데이터가 의도대로 잘 들어갔는지, 이력 관리가 제대로 되었는지 확인하는 습관을 들이세요. `current_flag = 'Y'` 조건으로 조회해보고, 과거 시점으로 `start_date <= 'YYYY-MM-DD' AND end_date >= 'YYYY-MM-DD'` 조건을 넣어 조회해보면서 감을 익히는 것이 중요합니다.

SCD Type 2는 분명 복잡하지만, 데이터 이력 관리의 기본이자 핵심입니다. 이 개념을 제대로 이해하고 구현할 줄 알게 되면, 여러분의 데이터 엔지니어링 역량이 한 단계 성장할 것이라고 확신합니다.

마무리하며: 복잡성 너머의 가치를 찾아서

SCD Type 2 구현은 단순히 코드를 짜는 것을 넘어, 데이터의 생명주기와 비즈니스 요구사항을 깊이 이해해야 하는 과정입니다. 처음에는 그 복잡성에 압도될 수도 있지만, 결국에는 정확하고 신뢰할 수 있는 분석 데이터를 제공하는 강력한 도구가 될 것입니다.

오늘 제가 공유해드린 내용들이 여러분이 SCD Type 2를 직접 구현하며 겪을 수 있는 어려움을 조금이나마 덜어주고, 더 나은 데이터 엔지니어로 성장하는 데 도움이 되기를 바랍니다. 궁금한 점이나 여러분이 겪었던 SCD Type 2 관련 경험이 있다면, 댓글로 함께 이야기 나눠주세요!

📌 함께 읽으면 좋은 글

• [데이터 엔지니어링] 데이터 품질 규칙 엔진, 실무에서 마주한 핵심 동작 원리와 구현 노하우
• [개발 도구] 성능 프로파일링, 개발자가 흔히 저지르는 5가지 치명적인 실수와 해결책
• [데이터 엔지니어링] 분산 ID 생성, 이 가이드로 충돌 0% 달성하고 시스템 성능 30% 개선한 비결

이 글이 도움이 되셨다면 공감(♥)과 댓글로 응원해 주세요!
궁금한 점이나 다루었으면 하는 주제가 있다면 댓글로 남겨주세요.