LLM WikiAccess-protected knowledge portal
← 스터디 홈
4편 · 약 20분

Data Warehouse와 모델링 기본기

이 세션의 목표

Data Warehouse는 분석을 위해 정리된 데이터를 모아두는 저장소다. 운영 DB와 달리 트랜잭션 처리보다 반복적인 분석 쿼리, 리포트, 지표 계산에 맞춘다.

DBA가 Warehouse를 이해하려면 “테이블이 몇 개냐”보다 분석 질문에 맞게 데이터를 어떻게 모델링하는가를 봐야 한다.

Data Warehouse란

Data Warehouse는 여러 source에서 온 데이터를 통합하고, 정리하고, 과거 이력을 보관해 분석하기 쉽게 만든 시스템이다.

특징은 다음과 같다.

  • 주로 structured data를 다룬다.
  • SQL 분석에 최적화한다.
  • historical data를 보관한다.
  • BI dashboard와 report의 기반이 된다.
  • 운영 DB와 분리해 분석 부하를 격리한다.

대표 플랫폼으로는 Snowflake, BigQuery, Redshift, Synapse, Teradata, ClickHouse, PostgreSQL 기반 warehouse 등이 있다.

Fact와 Dimension

Warehouse 모델링에서 가장 중요한 용어는 Fact와 Dimension이다.

용어의미예시
Fact측정 가능한 사건/수치주문, 결제, 클릭, 취약점 발견, 패키지 수집
DimensionFact를 설명하는 기준날짜, 고객, 제품, 저장소, 패키지 ecosystem

Fact table은 보통 행이 많고 계속 추가된다. Dimension table은 상대적으로 작고 설명 정보를 담는다.

Dimensionsdim_datedim_customerdim_product
→
Factfact_order
date_key · customer_key · product_key · amount
←
Dimensionsdim_regiondim_channel
Star Schema 예시

Star Schema와 Snowflake Schema

Star Schema는 하나의 fact table이 여러 dimension table을 직접 참조하는 형태다. 구조가 단순하고 BI 사용자가 이해하기 쉽다.

Snowflake Schema는 dimension을 다시 정규화해 여러 테이블로 나누는 형태다. 중복은 줄지만 join이 늘고 사용자가 이해하기 어려울 수 있다.

모델장점단점
Star Schema단순함, BI 친화적, 쿼리 작성 쉬움dimension 중복 가능
Snowflake Schema중복 감소, 정규화join 증가, 사용자 이해도 낮음

운영 DB에 익숙한 DBA는 정규화를 선호하기 쉽다. 하지만 Warehouse에서는 분석 사용자와 쿼리 성능 때문에 일부 비정규화를 선택한다.

Grain: Fact table의 단위

Fact table을 설계할 때 가장 먼저 정해야 하는 것은 grain이다.

이 테이블의 한 행은 무엇을 의미하는가?

예를 들어 fact_order의 grain은 다음 중 하나일 수 있다.

  • 주문 1건당 1행
  • 주문 상품 line item 1개당 1행
  • 고객-일자별 주문 합계 1행

grain이 불명확하면 지표가 틀린다. 같은 주문이 여러 상품을 가진 경우, 주문 단위 fact와 line item 단위 fact를 섞어 COUNT(*)하면 주문 수가 부풀려진다.

Slowly Changing Dimension, SCD

Dimension 값은 변한다. 고객 등급, 회사명, 패키지 license, 담당 부서가 바뀔 수 있다. 이때 과거 분석을 어떻게 할지 정하는 개념이 SCD다.

유형의미예시
SCD Type 1이전 값을 덮어쓴다오타 수정, 최신 상태만 중요
SCD Type 2과거 이력을 새 row로 남긴다고객 등급 변경 이력, 조직 변경 이력
SCD Type 3이전 값 일부만 별도 컬럼에 남긴다이전 담당자 정도만 필요

DBA 관점에서는 Type 2가 특히 중요하다. surrogate key, effective_from, effective_to, is_current 같은 컬럼으로 시간 범위를 관리한다.

Warehouse 성능 관점

Warehouse 성능은 OLTP 인덱스 튜닝과 다르다.

  • columnar storage에서는 필요한 컬럼만 읽는 것이 중요하다.
  • partitioning/clustering/sort key가 scan 범위를 줄인다.
  • materialized view나 aggregate table로 반복 집계를 줄인다.
  • 너무 많은 small file은 lakehouse query 성능을 떨어뜨린다.
  • dimension join이 많으면 semantic layer나 denormalized mart가 필요할 수 있다.

DBA 체크리스트

  1. fact table grain이 문서화되어 있는가?
  2. surrogate key와 natural key를 구분하는가?
  3. dimension 변경 이력을 Type 1/2 중 무엇으로 처리하는가?
  4. partition key가 실제 쿼리 filter와 맞는가?
  5. dashboard가 raw fact를 직접 때리지 않고 aggregate/mart를 쓰는가?
  6. 데이터 재처리 시 같은 결과가 나오도록 idempotent한가?

세션 과제

아래 주제로 star schema를 설계해보자.

“일자별, ecosystem별, package별 취약점 수와 fixed version 존재 여부를 분석하고 싶다.”

  • fact table 이름:
  • grain:
  • dimension 후보:
  • 지표 후보:
  • SCD가 필요한 dimension:

References

  • https://www.ibm.com/think/topics/data-warehouse
  • https://cloud.google.com/learn/what-is-a-data-warehouse
  • https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
  • https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/