Data Warehouse System
Kimball vs Inmon, Star/Snowflake 스키마, ETL, SCD, OLAP 연산
📚 시리즈 네비게이션
| 이전 | 현재 | 다음 |
|---|---|---|
| OLTP System | DW System | Data Lake |
→ 시리즈 목차
🎯 DW란?
Data Warehouse (DW): 다양한 원천 시스템에서 수집한 데이터를 통합하여 분석 및 의사결정에 활용하는 중앙 집중식 저장소
핵심 특징:
- 분석 목적에 최적화 (OLAP)
- 히스토리 데이터 보관 (수년~수십년)
- 여러 원천의 데이터를 통합
- 읽기 중심 (쓰기보다 조회가 많음)
🆚 OLTP vs OLAP
DW를 이해하려면 OLTP와의 차이를 알아야 함.
| 구분 | OLTP (운영계) | OLAP (분석계/DW) |
|---|---|---|
| 목적 | 업무 처리 | 분석/의사결정 |
| 사용자 | 현업 직원 | 분석가, 경영진 |
| 쿼리 패턴 | 단순, 소량, 빈번 | 복잡, 대량, 집계 |
| 데이터 | 현재 상태 | 히스토리 포함 |
| 정규화 | 높음 (3NF) | 낮음 (비정규화) |
| 응답 시간 | 밀리초 | 초~분 |
| 동시 사용자 | 수천~수만 | 수십~수백 |
| 예시 | ERP, CRM, 주문시스템 | DW, BI 시스템 |
flowchart LR subgraph OLTP["OLTP (운영계)"] O1["ERP"] O2["CRM"] O3["주문시스템"] end ETL["ETL"] subgraph OLAP["OLAP (분석계)"] A1["DW"] A2["Data Mart"] A3["BI 리포트"] end OLTP -->|INSERT/UPDATE<br/>실시간 트랜잭션| ETL -->|SELECT<br/>배치 분석| OLAP
🏛️ DW 아키텍처 유형
1. 기본 구조 (Kimball vs Inmon)
DW 설계에는 두 가지 대표적인 접근법이 있음.
flowchart TB subgraph Kimball["Kimball (Bottom-Up)"] direction LR KS["원천"] --> KE["ETL"] --> KM1["Data Mart"] --> KU["사용자"] KE --> KM2["Data Mart"] --> KU end subgraph Inmon["Inmon (Top-Down)"] direction LR IS["원천"] --> IE["ETL"] --> IDW["Enterprise DW"] --> IM["Data Mart"] --> IU["사용자"] end
Kimball (Bottom-Up):
- Data Mart를 먼저 구축하고 통합
- 빠른 구축, 부서별 요구 반영 쉬움
- 일관성 유지가 어려울 수 있음
Inmon (Top-Down):
- 전사 DW를 먼저 구축하고 Data Mart 파생
- 일관성 높음, 전사 통합 뷰
- 초기 구축 비용/시간 큼
💡 실무에서는 두 방식을 혼합해서 사용하는 경우가 많다.
2. 계층 구조
flowchart TB subgraph Layers["DW 계층 구조"] Staging["[1] Staging Area<br/>원천 데이터 임시 저장<br/>ETL 작업 공간<br/>변환 전 원본 상태"] ODS["[2] ODS<br/>운영 데이터 통합<br/>정제됨, 현재/최근 상태<br/>실시간/준실시간 조회"] DW["[3] DW (Data Warehouse)<br/>전사 통합 저장소<br/>히스토리 관리 (수년~수십년)<br/>주제 영역별 구성"] DM["[4] Data Mart<br/>부서/목적별 부분 집합<br/>분석에 최적화 (집계, 요약)<br/>예: 영업/재무/인사 마트"] Staging --> ODS --> DW --> DM end
각 계층의 역할:
| 계층 | 목적 | 데이터 특성 | 보존 기간 |
|---|---|---|---|
| Staging | ETL 작업 공간 | 원본 그대로 | 단기 (일~주) |
| ODS | 운영 통합 | 정제됨, 현재 상태 | 단기~중기 |
| DW | 전사 분석 | 히스토리 포함 | 장기 (년) |
| Data Mart | 부서별 분석 | 집계/요약 | 중기 |
📐 DW 모델링
스타 스키마 (Star Schema)
가장 기본적인 DW 모델링 방식임.
flowchart TB DimTime["Dimension<br/>(시간)"] DimProduct["Dimension<br/>(제품)"] Fact["Fact<br/>(매출)"] DimCustomer["Dimension<br/>(고객)"] DimRegion["Dimension<br/>(지역)"] DimTime --> Fact DimProduct --> Fact DimCustomer --> Fact DimRegion --> Fact
구성 요소:
| 구분 | Fact 테이블 | Dimension 테이블 |
|---|---|---|
| 내용 | 측정값 (숫자) | 분석 관점 (속성) |
| 예시 | 매출액, 수량, 금액 | 시간, 제품, 고객, 지역 |
| 특징 | 대용량, 좁고 김 | 소용량, 넓음 |
| 키 | FK (외래키 여러 개) | PK (기본키) |
예시:
-- Fact 테이블: 매출
CREATE TABLE fact_sales (
date_key INT, -- FK → dim_date
product_key INT, -- FK → dim_product
customer_key INT, -- FK → dim_customer
store_key INT, -- FK → dim_store
sales_amount DECIMAL, -- 측정값
quantity INT -- 측정값
);
-- Dimension 테이블: 시간
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
full_date DATE,
year INT,
quarter INT,
month INT,
day INT,
weekday VARCHAR(10)
);스노우플레이크 스키마 (Snowflake Schema)
Dimension 테이블을 정규화한 형태임.
flowchart TB Cat["카테고리"] Prod["제품"] Fact["Fact"] Region["지역"] City["도시"] Cat --> Prod --> Fact City --> Region --> Fact
Star vs Snowflake:
| 구분 | Star Schema | Snowflake Schema |
|---|---|---|
| 정규화 | 비정규화 | 정규화 |
| 조인 | 적음 | 많음 |
| 쿼리 성능 | 빠름 | 상대적으로 느림 |
| 저장 공간 | 많음 (중복) | 적음 |
| 유지보수 | 단순 | 복잡 |
💡 대부분의 DW에서는 쿼리 성능을 위해 Star Schema를 선호한다.
🔄 ETL 프로세스
DW 구축의 핵심임. 원천 → DW로 데이터를 이동하는 과정.
flowchart LR subgraph Extract["Extract (추출)"] E1["원천 시스템에서<br/>데이터 가져오기"] E2["전체/증분 추출"] end subgraph Transform["Transform (변환)"] T1["데이터 정제"] T2["형식 변환, 코드 매핑"] T3["집계, 계산, 중복 제거"] end subgraph Load["Load (적재)"] L1["DW에 저장"] L2["전체/증분/Merge"] end Extract --> Transform --> Load
적재 방식
| 방식 | 설명 | 용도 |
|---|---|---|
| Full Load | 전체 데이터 삭제 후 재적재 | 초기 적재, 소규모 테이블 |
| Incremental | 변경분만 추가 | 대규모 테이블, 일반적 |
| Merge (Upsert) | 있으면 UPDATE, 없으면 INSERT | SCD Type 1 |
SCD (Slowly Changing Dimension)
Dimension 데이터가 변경될 때 어떻게 처리할지 정의한 것임.
| 타입 | 설명 | 히스토리 |
|---|---|---|
| Type 1 | 덮어쓰기 | X |
| Type 2 | 새 행 추가 (유효기간 관리) | O |
| Type 3 | 이전 값 컬럼 추가 | 제한적 |
Type 2 예시:
customer_key | name | address | start_date | end_date | is_current
-------------|--------|---------|------------|------------|------------
1001 | 홍길동 | 서울 | 2020-01-01 | 2023-05-31 | N
1002 | 홍길동 | 부산 | 2023-06-01 | 9999-12-31 | Y
📊 OLAP 분석
DW 데이터를 다차원으로 분석하는 기법임.
OLAP 연산
| 연산 | 설명 | 예시 |
|---|---|---|
| Roll-up | 상위 레벨로 집계 | 월별 → 분기별 → 연도별 |
| Drill-down | 하위 레벨로 상세화 | 연도별 → 분기별 → 월별 |
| Slice | 하나의 차원 고정 | 2024년 데이터만 |
| Dice | 여러 차원 조건 | 2024년 + 서울 + A제품 |
| Pivot | 축 변경 | 행↔열 전환 |
flowchart TB RollUp["Drill-up (Roll-up)"] Slice["Slice"] Cube["CUBE"] Dice["Dice"] DrillDown["Drill-down"] RollUp --> Cube Slice --> Cube Cube --> Dice Cube --> DrillDown
OLAP 유형
| 유형 | 저장 방식 | 특징 |
|---|---|---|
| MOLAP | 다차원 배열 (Cube) | 빠른 조회, 사전 집계 |
| ROLAP | 관계형 DB | 유연함, 대용량 |
| HOLAP | 혼합 | 요약은 MOLAP, 상세는 ROLAP |
🛠️ 주요 DW 솔루션
전통적 DW
| 솔루션 | 설명 |
|---|---|
| Oracle Database | 엔터프라이즈 시장 점유율 높음 |
| Oracle Exadata | Oracle DB 전용 어플라이언스 |
| Teradata | 대용량 DW 특화 |
| IBM Db2 Warehouse | IBM 엔터프라이즈 |
| Microsoft SQL Server | BI 스택 통합 (SSIS, SSAS, SSRS) |
클라우드 DW
| 솔루션 | 클라우드 | 특징 |
|---|---|---|
| Snowflake | Multi | 컴퓨팅/스토리지 분리, 사용량 과금 |
| Amazon Redshift | AWS | PostgreSQL 기반 |
| Google BigQuery | GCP | 서버리스, 컬럼 기반 |
| Azure Synapse | Azure | SQL DW + Spark 통합 |
ETL 도구
| 도구 | 유형 | 설명 |
|---|---|---|
| Informatica PowerCenter | 상용 | 엔터프라이즈 ETL 표준 |
| Oracle ODI | 상용 | Oracle 환경 최적화 |
| Talend | 오픈소스 | 무료 버전 있음 |
| Apache Airflow | 오픈소스 | 워크플로우 오케스트레이션 |
| AWS Glue | 클라우드 | 서버리스 ETL |
BI 도구
| 도구 | 특징 |
|---|---|
| MicroStrategy | 엔터프라이즈 OLAP |
| Tableau | 시각화 강점 |
| Power BI | Microsoft 통합 |
| Looker | Google, 데이터 모델링 |
🔗 관련 문서
- Data Infrastructure Overview
- DW 스토리지 용어 정리
- (예정) ETL 설계 패턴
- (예정) DW 모델링 실습