[DE] 데이터웨어하우스 모델링 기본 - Star Schema

OLAP DB에 데이터를 넣을 때 분석하기 좋은 구조로 설계해야 한다.
그 대표적인 방법이 Star Schema다.

 
 

Fact 테이블 + Dimension 테이블

• Star Schema는 두 종류의 테이블로 구성된다.

구분	Fact 테이블	Dimension 테이블
의미	실제로 일어난 이벤트	이벤트의 부가 정보
중심	숫자(측정값)	텍스트/속성
예시	주문, 클릭, 결제	고객, 상품, 날짜, 지역

 
-> 가운데 Fact 테이블을 중심으로 Dimension 테이블들이 별처럼 붙어있는 구조라 Star Schema라고 부른다.
 
 

설계 예시

 

[dim_customer]                        [dim_product]
┌──────────────┐                      ┌──────────────┐
│ customer_id  │                      │ product_id   │
│ name         │                      │ name         │
│ region       │                      │ category     │
│ age_group    │                      │ brand        │
└──────┬───────┘                      └──────┬───────┘
       │                                     │
       └──────────────┬──────────────────────┘
                      ▼
             ┌─────────────────────────────┐
             │         fact_orders         │
             │  order_id    customer_id    │
             │  date_id     product_id     │
             │  amount      quantity       │
             │  discount    revenue        │
             └──────────────┬─────────────┘
                            │
                            ▼
                     [dim_date]
                     ┌──────────────┐
                     │ date_id      │
                     │ year         │
                     │ month        │
                     │ weekday      │
                     │ is_holiday   │
                     └──────────────┘

 
 

직관적인 분석 쿼리

• Star Schema로 설계하면 분석 쿼리가 일관된 패턴을 가진다.
• 항상 Fact 테이블을 중심으로 필요한 Dimension을 JOIN하는 구조이다.

-- 2024년 1월, 서울 고객의 카테고리별 매출
SELECT
	c.region,
	p.category,
    	SUM(f.revenue)
FROM fact_orders f
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_product p ON f.product_id = p.product_id
JOIN dim_Date d ON f.date_id = d.date_id
WHERE d.year = 2024 AND d.month = 1
  AND c.region = '서울'
GROUP BY c.region, p.category;

 
 

Fact 테이블 설계 원칙

• 숫자(측정값)만 넣는다.
• FK만 넣는다.
• 텍스트 속성은 넣지 않는다.
• Grain을 명확히 정한다.
  • Grain이란? "이 테이블의 행 하나가 무엇을 의미하는가". 설계 전에 반드시 먼저 정해야 한다.
  • Grain이 낮을수록 더 다양한 분석이 가능하지만 데이터 양도 늘어난다.

 

Star Schema vs Snowflake Schema

• Star Schema에서 Dimension 테이블을 더 정규화한 것 -> Snowflake Schema
• Snowflake Schema는 JOIN이 많아져서 쿼리가 복잡해지는 단점이 있다.

Star Schema                   Snowflake Schema

dim_product                   dim_product
┌──────────┐                  ┌──────────┐     ┌──────────┐
│product_id│                  │product_id│────▶│brand_id  │
│name      │                  │name      │     │brand_name│
│category  │                  │brand_id  │     └──────────┘
│brand     │                  │category_id───▶ ┌──────────┐
└──────────┘                  └──────────┘     │category  │
                                               └──────────┘