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

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

📌 선분 이력 모델이란?

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

✅ 주로 사용되는 곳:

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

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

ID	NAME	ADDRESS	START_DATE	END_DATE	IS_CURRENT
1	Alice	New York	2023-01-01	2023-06-30	N
2	Alice	Los Angeles	2023-07-01	9999-12-31	Y

• 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_id	name	address	start_date	end_date	is_current
1	Alice	New York	2023-01-01	9999-12-31	Y

📌 변경 사항

• 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 시험 대비를 위해 이론 개념 + 실습 문제를 병행해서 공부하세요! 🎯