데이터 모델링 — 정규화, Star 스키마, Snowflake 스키마

-- 실무에서 필요한 dim_date 테이블 예시
CREATE TABLE dim_date (
    date_key      INT PRIMARY KEY,        -- 20250301 형식
    full_date     DATE NOT NULL,
    year          INT,
    quarter       STRING,                  -- 'Q1', 'Q2', ...
    month         INT,
    month_name    STRING,                  -- '3월', '4월', ...
    week_of_year  INT,
    day_of_week   STRING,                  -- '월요일', '화요일', ...
    day_of_week_num INT,                   -- 1(월) ~ 7(일)
    is_weekend    BOOLEAN,
    is_holiday    BOOLEAN,                 -- 공휴일 여부
    holiday_name  STRING,                  -- '설날', '추석', ...
    is_business_day BOOLEAN,              -- 영업일 여부
    fiscal_year   INT,                     -- 회계연도 (3월 결산이면 다를 수 있음)
    fiscal_quarter STRING,
    year_month    STRING                   -- '2025-03' 형식, 리포트용
);

-- 주문번호(order_number)는 팩트 테이블에 그대로 둡니다
-- 별도의 dim_order를 만들 필요가 없습니다
CREATE TABLE fact_orders (
    order_key       BIGINT,
    order_number    STRING,     -- ← Degenerate Dimension
    invoice_number  STRING,     -- ← Degenerate Dimension
    customer_key    INT,
    product_key     INT,
    date_key        INT,
    quantity        INT,
    amount          DECIMAL(15,2)
);

-- Before: 팩트 테이블이 플래그로 비대해짐
-- fact_orders에 is_returned, is_gift, is_priority, payment_type, channel ...

-- After: Junk Dimension으로 조합을 미리 만들어 둡니다
CREATE TABLE dim_order_flags (
    flag_key        INT PRIMARY KEY,
    is_returned     BOOLEAN,
    is_gift         BOOLEAN,
    is_priority     BOOLEAN,
    payment_type    STRING,     -- 'card', 'cash', 'transfer'
    channel         STRING      -- 'web', 'app', 'store'
);

-- 팩트에는 flag_key 하나만 남깁니다
-- fact_orders.flag_key → dim_order_flags.flag_key

-- "2025년 Q1, 영업일 기준, 서울 지역의 카테고리별 매출"
-- dim_date가 있으니 이렇게 깔끔하게 쿼리할 수 있습니다
SELECT
    p.category,
    d.fiscal_quarter,
    SUM(f.amount) AS total_revenue,
    COUNT(f.order_key) AS order_count,
    AVG(f.amount) AS avg_order_value
FROM fact_orders f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_store s ON f.store_key = s.store_key
WHERE d.fiscal_year = 2025
  AND d.fiscal_quarter = 'Q1'
  AND d.is_business_day = true    -- 영업일만!
  AND s.region = '서울'
GROUP BY p.category, d.fiscal_quarter
ORDER BY total_revenue DESC;

-- Type 2 SCD 예시: 고객 등급 변경 이력
-- customer_key는 Surrogate Key (자동 생성), customer_id는 Natural Key (비즈니스 키)
| customer_key | customer_id | name   | tier   | effective_date | end_date   | is_current |
|-------------|-------------|--------|--------|----------------|------------|------------|
| 1001        | C1          | 김철수 | Silver | 2024-01-01     | 2025-02-28 | false      |
| 1002        | C1          | 김철수 | Gold   | 2025-03-01     | 9999-12-31 | true       |

-- Gold 계층: 비정규화된 OBT 생성
CREATE OR REPLACE TABLE gold.wide_orders AS
SELECT
    f.order_key,
    f.order_number,
    f.quantity,
    f.amount,
    -- 고객 디멘전
    c.name AS customer_name,
    c.city AS customer_city,
    c.tier AS customer_tier,
    -- 상품 디멘전
    p.name AS product_name,
    p.category AS product_category,
    p.brand AS product_brand,
    -- 날짜 디멘전
    d.full_date AS order_date,
    d.month,
    d.quarter,
    d.year,
    d.is_business_day,
    d.fiscal_year,
    -- 플래그 디멘전
    fl.is_returned,
    fl.payment_type,
    fl.channel
FROM fact_orders f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_order_flags fl ON f.flag_key = fl.flag_key;

-- 전통적 파티셔닝 (이제 권장하지 않음)
-- CREATE TABLE fact_orders (...) PARTITIONED BY (order_date);

-- Liquid Clustering (현재 권장)
CREATE TABLE fact_orders (
    order_key BIGINT,
    order_number STRING,
    customer_key INT,
    product_key INT,
    date_key INT,
    quantity INT,
    amount DECIMAL(15,2)
)
CLUSTER BY (date_key, customer_key);

-- 클러스터링 키는 나중에 변경 가능!
ALTER TABLE fact_orders CLUSTER BY (date_key, product_key);