SCD Type 2를 위한 `MERGE` 구문

SCD Type 2를 위한 MERGE 구문

DuckDB의 MERGE 구문(v1.4.0에서 도입)으로 upsert를 처리하고 Slowly Changing Dimension Type 2(SCD Type 2) 테이블을 만들 수 있어요. Type 2 SCD는 레코드의 전체 히스토리 버전을 유지하면서 현재 버전이 무엇인지 명확히 표시해 줘서, 감사 추적(audit trail)·데이터 웨어하우징·분석 워크로드에 아주 적합해요. 기본 키 데이터의 이전 값이 무엇이었는지, 언제 바뀌었는지, 특정 상태로 얼마나 오래 있었는지 알고 싶을 때 Type 2 SCD가 실용적이에요.

출처: 공식문서

DuckDB에서 MERGE를 쓰는 이유

• INSERT, UPDATE, 소프트 DELETE(upsert와 만료)를 단일 SQL 구문으로 처리해요.
• 동등한 Python/Pandas 로직보다 훨씬 깔끔하고 빨라요.
• 하드 삭제 없이 전체 히스토리를 추적해요.
• DuckDB의 연결성 덕분에 Parquet, CSV, 데이터베이스에서 바로 동작해요!

사전 준비

• 기본 SQL 지식

핵심 용어

SCD Type 2 차원 테이블 만들기

오리(duck)를 추적하면서 이름, 품종, 위치가 바뀔 때마다 히스토리를 보존해 볼게요.

DuckDB에는 프론트엔드 노트북 UI가 있어서, 여러 SQL 구문을 관리하고 코드를 분할하기 좋아요. 이 UI는 DuckDB CLI와 함께 제공되므로, CLI가 설치되어 있으면 프론트엔드를 쓸 수 있어요. 노트북 프론트엔드를 시작하려면 duckdb -ui를 실행하고 http://localhost:4213/로 이동해 노트북 안에서 SQL 코드를 작성하면 돼요. 아래 코드 블록을 복사·붙여넣기하며 이 가이드를 따라오면 됩니다.

1단계: 들어오는(소스) 테이블 만들기

이 테이블은 오늘의 트랜잭션 데이터를 나타내요.

CREATE TABLE IF NOT EXISTS incoming_ducks (
    duck_id     INTEGER,
    duck_name   VARCHAR,
    breed       VARCHAR,
    location    VARCHAR,
    begin_date  DATE,
    end_date    DATE,
    is_current  BOOLEAN
);

INSERT INTO incoming_ducks VALUES
    (101, 'Quackers',   'Mallard',       'Pond B',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (102, 'Waddles',    'Pekin',         'Pond A',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (104, 'Splash',     'Muscovy',       'Pond C',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (105, 'Puddles',    'Indian Runner', 'Relocated',   CURRENT_DATE - INTERVAL '1 day', NULL, true);

2단계: 마스터(타깃) 테이블 만들기

이 테이블은 type 2 SCD 데이터(즉, 히스토리가 있는 트랜잭션 데이터)를 나타내요.

CREATE TABLE IF NOT EXISTS master_ducks (
    record_id   INTEGER PRIMARY KEY,
    duck_id     INTEGER NOT NULL,
    duck_name   VARCHAR,
    breed       VARCHAR,
    location    VARCHAR,
    begin_date  DATE NOT NULL,
    end_date    DATE,
    is_current  BOOLEAN NOT NULL DEFAULT true
);

CREATE SEQUENCE IF NOT EXISTS duck_record_seq START 1;

INSERT INTO master_ducks VALUES
    (nextval('duck_record_seq'), 101, 'Quackers', 'Mallard',       'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 102, 'Waddles',  'Pekin',         'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 103, 'Feathers', 'Rouen',         'Pond B', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 105, 'Puddles',  'Indian Runner', 'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true);

3단계: MERGE 구문 실행하기

이 구문은 merge를 수행하며 타깃과 소스 데이터의 차이를 확인하고 지정된 WHEN MATCHED 또는 WHEN NOT MATCHED 로직을 따라요.

MERGE INTO master_ducks AS target
USING incoming_ducks AS source
ON target.duck_id = source.duck_id AND target.is_current = true

WHEN MATCHED AND (
       target.duck_name <> source.duck_name OR
       target.breed     <> source.breed     OR
       target.location  <> source.location
) THEN UPDATE SET
    end_date    = CURRENT_DATE - INTERVAL '1 day',
    is_current  = false

WHEN NOT MATCHED BY SOURCE AND target.is_current = true THEN UPDATE SET
    end_date    = CURRENT_DATE - INTERVAL '1 day',
    is_current  = false

WHEN NOT MATCHED BY TARGET THEN INSERT (
    record_id, duck_id, duck_name, breed, location,
    begin_date, end_date, is_current
) VALUES (
    nextval('duck_record_seq'),
    source.duck_id, source.duck_name, source.breed, source.location,
    source.begin_date, source.end_date, source.is_current
)

RETURNING merge_action, *;

4단계: 변경된 레코드의 새 현재 버전 삽입하기

이 구문은 새 현재 레코드를 마스터 테이블에 삽입해요. MERGE 구문의 RETURNING 절로 같은 결과를 얻을 수도 있지만, 이 2단계 접근이 더 직관적이고 이해하기 쉬워요.

INSERT INTO master_ducks (
    record_id, duck_id, duck_name, breed, location,
    begin_date, end_date, is_current
)
SELECT
    nextval('duck_record_seq'),
    source.duck_id,
    source.duck_name,
    source.breed,
    source.location,
    CURRENT_DATE AS begin_date,
    NULL AS end_date,
    true AS is_current
FROM incoming_ducks AS source
INNER JOIN master_ducks AS target
    ON source.duck_id = target.duck_id
WHERE target.is_current = false
  AND target.end_date = CURRENT_DATE - INTERVAL '1 day';

5단계: 결과 조회하기

다음 쿼리들로 MERGE 구문의 결과 데이터를 살펴볼 수 있어요.

-- All history
SELECT * FROM master_ducks ORDER BY duck_id, begin_date DESC;

-- Only current records
SELECT * FROM master_ducks WHERE is_current = true;

-- Only expired historical records
SELECT * FROM master_ducks WHERE is_current = false ORDER BY duck_id, begin_date DESC;

6단계: 오리 한 마리 살펴보기

개념을 더 잘 설명하기 위해 오리 한 마리를 살펴볼게요. type 2 SCD의 가치를 실감하는 데 도움이 돼요. merge 구문과 그 뒤의 삽입 구문을 실행한 뒤 마스터 테이블에서 조회하면 Quackers의 개별 행을 볼 수 있어요.

히스토리인 원본 행을 보려면:

SELECT * FROM master_ducks where duck_name = 'Quackers' and is_current = false;

결과:

참고:

• end_date는 NOT NULL이고, 이 오리의 데이터가 업데이트된 날짜를 담고 있어요.
• is_current가 false로 이 레코드가 히스토리 레코드임을 나타내요.
• 바뀔 필드는 location이며, 현재 Pond A에서 Pond B로 업데이트될 거예요.

현재 행을 보려면:

SELECT * FROM master_ducks where duck_name = 'Quackers' and is_current = true;

참고:

• end_date가 NULL인데, 이 문맥에서 NULL은 이 duck_id의 최신 레코드임을 나타내요.
• is_current가 true로 이 레코드가 현재 레코드임을 나타내요.
• 이제 location이 Pond B예요.

Quackers의 모든 데이터(현재 행과 비현재 행 모두)를 보려면:

SELECT * FROM master_ducks where duck_name = 'Quackers';

일반적인 패턴과 변형

모범 사례

• TARGET이 마스터 테이블이고 SOURCE가 들어오는 테이블 또는 쿼리라는 점을 기억하세요.
• 현재 행의 end_date는 NULL로 유지하세요 (쿼리가 빨라져요).
• 필요하면 MERGE와 INSERT 구문을 트랜잭션으로 감싸세요.
• 고유성 보장을 위해 기본 키 또는 대리 키(surrogate key)를 쓰세요.
• 먼저 RETURNING으로 테스트해 보세요.

더 알아보기 (Learn more)

• MERGE 구문 (SQL)
• Slowly Changing Dimension (Wikipedia)
• INSERT INTO 구문