선분 이력 모델 (Slowly Changing Dimension Type 2, SCD Type 2)

by

선분 이력 모델(Temporal Data Model, SCD Type 2)은 데이터가 변경될 때마다 새로운 행을 추가하여 이전 데이터를 보존하면서 변경 이력을 관리하는 데이터 모델입니다.

📌 선분 이력 모델이란?

  • 시간에 따라 변하는 데이터를 추적하기 위한 데이터 모델링 기법
  • 기존 데이터를 수정하지 않고, 새로운 데이터가 들어올 때마다 새로운 행을 추가
  • 일반적으로 시작일(START_DATE)과 종료일(END_DATE), 그리고 현재 데이터 여부를 나타내는 컬럼(IS_CURRENT) 을 사용하여 이력을 관리함

✅ 주로 사용되는 곳:

  • 고객 정보(주소, 연락처, 직급 변경)
  • 제품 가격 변동
  • 조직 개편 (부서 변경, 직원 이동)
  • 금융 거래 내역 (계좌 잔액 이력, 이자율 변화)

1️⃣ 선분 이력 모델의 테이블 구조

IDNAMEADDRESSSTART_DATEEND_DATEIS_CURRENT
1AliceNew York2023-01-012023-06-30N
2AliceLos Angeles2023-07-019999-12-31Y
  • Alice의 주소가 “New York” → “Los Angeles” 로 변경됨
  • 이전 행(Old Record)은 END_DATE를 2023-06-30으로 설정하고 IS_CURRENT = 'N' 처리
  • 새로운 행(New Record)을 추가하여 START_DATE = 2023-07-01, END_DATE = 9999-12-31, IS_CURRENT = 'Y'로 유지

2️⃣ 선분 이력 모델을 위한 SQL 설계

(1) 테이블 생성

CREATE TABLE customer_history (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    address VARCHAR2(255),
    start_date DATE,
    end_date DATE,
    is_current CHAR(1) CHECK (is_current IN ('Y', 'N'))
);

(2) 새 데이터가 변경될 때 이력을 관리하는 SQL

✅ 변경 감지 후 이력 관리 (MERGE 사용)

MERGE INTO customer_history ch
USING (
    SELECT 1 AS id, 'Alice' AS name, 'Los Angeles' AS address, SYSDATE AS start_date FROM dual
) new_data
ON (ch.id = new_data.id AND ch.is_current = 'Y')
WHEN MATCHED THEN
    -- 기존 데이터의 이력을 종료 (END_DATE 업데이트, IS_CURRENT='N' 처리)
    UPDATE SET ch.end_date = SYSDATE - 1, ch.is_current = 'N'
    WHERE ch.is_current = 'Y'
WHEN NOT MATCHED THEN
    -- 새로운 데이터 삽입 (이력 추가)
    INSERT (id, name, address, start_date, end_date, is_current)
    VALUES (new_data.id, new_data.name, new_data.address, new_data.start_date, TO_DATE('9999-12-31', 'YYYY-MM-DD'), 'Y');

3️⃣ 선분 이력 모델의 장점 & 단점

장점단점
과거 데이터를 보존하여 변경 이력 추적 가능데이터 크기가 지속적으로 증가하여 성능 저하 가능
특정 시점의 데이터를 쉽게 조회 가능 (타임 트래블)데이터 삽입/갱신 로직이 복잡할 수 있음
데이터 변경 내역을 쉽게 감사(Audit) 가능조회 시 IS_CURRENT='Y'를 필터링해야 성능 최적화 가능

4️⃣ 특정 시점의 데이터 조회

  • 현재 유효한 데이터만 가져오기 (IS_CURRENT = 'Y' 사용)
SELECT * FROM customer_history WHERE is_current = 'Y';
  • 특정 날짜 기준으로 과거 데이터 조회 (예: 2023년 5월 1일 기준 고객 주소)
SELECT * FROM customer_history
WHERE start_date <= TO_DATE('2023-05-01', 'YYYY-MM-DD')
AND end_date >= TO_DATE('2023-05-01', 'YYYY-MM-DD');

📌 결론

  • 선분 이력 모델(SCD Type 2)은 데이터 변경 이력을 추적하는 중요한 기법
  • 기존 데이터를 수정하지 않고 새로운 데이터를 삽입하여 과거 기록을 유지
  • START_DATE, END_DATE, IS_CURRENT 필드를 활용하여 이력 관리
  • MERGE INTO 구문을 사용하면 데이터 변경 자동 관리 가능
  • 조회 성능 최적화를 위해 IS_CURRENT 컬럼에 인덱스 추가 고려

🚀 이제 선분 이력 모델을 활용하여 데이터 변경 이력을 효과적으로 관리하세요! 😊

4o

나의 말:

SQLD 또는 SQLP에서 선분이력모델 모의문제 내줘

ChatGPT의 말:

다음은 SQLD(데이터 전문가) 또는 SQLP(데이터 분석 전문가) 시험을 대비한 선분 이력 모델(Temporal Data Model, SCD Type 2) 관련 모의 문제입니다.

📌 1. 개념 이해 문제

Q1. 선분 이력 모델(Sequence History Model, SCD Type 2)의 특징으로 올바른 것을 모두 고르시오.

① 과거 데이터를 보존하면서 새로운 데이터 변경 시 기존 데이터를 수정한다.
② START_DATE와 END_DATE를 사용하여 데이터 유효 기간을 관리한다.
③ IS_CURRENT 등의 컬럼을 활용하여 현재 유효한 데이터를 식별할 수 있다.
④ DELETE 문을 사용하여 과거 데이터를 주기적으로 삭제한다.
⑤ 동일한 엔티티에 대한 데이터 변경이 발생할 때마다 새로운 행을 추가한다.

📌 2. 테이블 설계 문제

Q2. 다음 중 선분 이력 모델을 위한 테이블 설계로 적절한 것은?

A) CREATE TABLE customer_history (
customer_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
address VARCHAR2(255),
updated_date DATE
);

B) CREATE TABLE customer_history (
customer_id NUMBER,
name VARCHAR2(100),
address VARCHAR2(255),
start_date DATE,
end_date DATE,
is_current CHAR(1) CHECK (is_current IN ('Y', 'N')),
PRIMARY KEY (customer_id, start_date)
);

C) CREATE TABLE customer_history (
customer_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
address VARCHAR2(255),
history_id NUMBER UNIQUE
);

💡 정답을 고르시오.

📌 3. SQL 작성 문제

Q3. 선분 이력 모델을 활용하여 현재 유효한 고객 데이터를 조회하는 SQL을 작성하시오.

SELECT *
FROM customer_history
________________________;
(빈칸을 채우시오.)

📌 4. 데이터 삽입 및 변경 문제

Q4. 다음 고객의 주소가 변경되었을 때, 선분 이력 모델을 유지하면서 데이터를 갱신하는 SQL을 작성하시오.

📌 기존 데이터

customer_idnameaddressstart_dateend_dateis_current
1AliceNew York2023-01-019999-12-31Y

📌 변경 사항

  • Alice의 주소가 “Los Angeles” 로 변경됨
  • start_date = 2024-02-01로 새로운 행을 추가해야 함

💡 수행해야 할 작업

  1. 기존 행(customer_id=1)의 end_date를 2024-01-31로 업데이트
  2. 새로운 주소(“Los Angeles”) 데이터를 삽입 (start_date=2024-02-01)
-- 기존 데이터 종료 처리
UPDATE customer_history
SET end_date = TO_DATE('2024-01-31', 'YYYY-MM-DD'),
    is_current = 'N'
WHERE customer_id = 1 AND is_current = 'Y';

-- 새로운 데이터 삽입
INSERT INTO customer_history (customer_id, name, address, start_date, end_date, is_current)
VALUES (1, 'Alice', 'Los Angeles', TO_DATE('2024-02-01', 'YYYY-MM-DD'), TO_DATE('9999-12-31', 'YYYY-MM-DD'), 'Y');

📌 5. 고급 응용 문제

Q5. 고객의 주소 변경 이력을 조회할 때, 특정 날짜(2023-06-01) 기준으로 유효한 데이터를 조회하는 SQL을 작성하시오.

SELECT *
FROM customer_history
WHERE ____________________;

(빈칸을 채우시오.)

📌 정답

🔹 Q1 정답: ②, ③, ⑤

🔹 정답 설명

✔ ② START_DATE와 END_DATE를 사용하여 데이터 유효 기간을 관리한다. ✅
→ 선분 이력 모델에서는 특정 데이터의 유효 기간을 START_DATE와 END_DATE 컬럼으로 관리합니다.

✔ ③ IS_CURRENT 등의 컬럼을 활용하여 현재 유효한 데이터를 식별할 수 있다. ✅
→ 일반적으로 IS_CURRENT 컬럼('Y', 'N')을 사용하여 현재 활성 상태인 데이터를 쉽게 조회할 수 있도록 설계합니다.

✔ ⑤ 동일한 엔티티에 대한 데이터 변경이 발생할 때마다 새로운 행을 추가한다. ✅
→ 선분 이력 모델에서는 기존 데이터를 수정하지 않고, 변경 발생 시 새로운 행을 추가하는 방식을 사용합니다.


🔹 Q2 정답: B

✅ 이유:

  • 선분 이력 모델에서는 변경 이력을 관리하기 위해 start_date, end_date 컬럼이 필요함
  • is_current 컬럼을 추가하여 현재 유효한 데이터인지 식별할 수 있도록 함
  • PRIMARY KEY (customer_id, start_date)를 설정하여 한 고객이 여러 개의 변경 이력을 가질 수 있도록 허용


🔹 Q3 정답:

WHERE is_current = 'Y';

🔹 Q5 정답:

WHERE start_date <= TO_DATE('2023-06-01', 'YYYY-MM-DD')
AND end_date >= TO_DATE('2023-06-01', 'YYYY-MM-DD');

🚀 시험 대비 TIP

  • ROW_NUMBER() 또는 RANK()를 활용한 최신 데이터 조회 문제도 자주 출제됨
  • MERGE INTO를 활용한 갱신(SQLP 시험에 자주 등장)
  • PARTITION BY를 활용한 그룹별 이력 관리도 함께 연습할 것

✅ SQLD/SQLP 시험 대비를 위해 이론 개념 + 실습 문제를 병행해서 공부하세요! 🎯

Leave a Comment