Medallion 아키텍처

-- 실전 예시: Gold 매출이 이상할 때 Bronze 원본과 역추적
-- 1단계: Gold에서 이상 수치 확인
SELECT order_date, SUM(amount) FROM gold_daily_revenue
WHERE order_date = '2025-03-15';  -- 매출이 평소의 2배?

-- 2단계: Silver에서 해당 날짜의 주문 건수 확인
SELECT COUNT(*), COUNT(DISTINCT order_id) FROM silver_orders
WHERE DATE(order_date) = '2025-03-15';  -- 중복 주문이 있는지?

-- 3단계: Bronze 원본에서 소스 데이터 확인
SELECT * FROM bronze_orders
WHERE _source_file LIKE '%2025-03-15%'
ORDER BY _ingested_at;  -- 같은 파일이 2번 수집되었는지?

-- Silver 재생성: Bronze 원본에서 다시 처리
-- SDP 파이프라인에서 Full Refresh 실행
-- databricks pipelines start-update --pipeline-id <id> --full-refresh

-- 또는 특정 테이블만 재생성
REFRESH TABLE silver_orders;  -- Materialized View 새로고침

-- Bronze 테이블 예시: 원본 JSON 데이터를 그대로 수집
CREATE OR REFRESH STREAMING TABLE bronze_orders
AS SELECT
    *,
    _metadata.file_path AS _source_file,
    _metadata.file_modification_time AS _ingested_at,
    current_timestamp() AS _processing_time
FROM STREAM read_files(
    's3://raw-data/orders/',
    format => 'json',
    inferColumnTypes => true
);

-- Silver 테이블 예시: 정제 및 표준화
CREATE OR REFRESH STREAMING TABLE silver_orders (
    -- 데이터 품질 규칙 정의
    CONSTRAINT valid_order_id EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW,
    CONSTRAINT valid_amount EXPECT (total_amount > 0) ON VIOLATION DROP ROW,
    CONSTRAINT valid_email EXPECT (email RLIKE '^[^@]+@[^@]+\\.[^@]+$') ON VIOLATION DROP ROW
)
AS SELECT
    CAST(order_id AS BIGINT) AS order_id,
    CAST(customer_id AS BIGINT) AS customer_id,
    TRIM(LOWER(email)) AS email,
    CAST(order_date AS TIMESTAMP) AS order_date,
    CAST(total_amount AS DECIMAL(12, 2)) AS total_amount,
    UPPER(TRIM(status)) AS status,
    TRIM(shipping_city) AS shipping_city
FROM STREAM(bronze_orders)
WHERE order_id IS NOT NULL;

-- Gold 테이블 예시 1: 일별 매출 요약
CREATE OR REFRESH MATERIALIZED VIEW gold_daily_revenue
AS SELECT
    DATE(order_date) AS order_date,
    shipping_city AS city,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM silver_orders
WHERE status = 'COMPLETED'
GROUP BY DATE(order_date), shipping_city;

-- Gold 테이블 예시 2: 고객 360 뷰
CREATE OR REFRESH MATERIALIZED VIEW gold_customer_360
AS SELECT
    c.customer_id,
    c.name,
    c.email,
    c.signup_date,
    COUNT(o.order_id) AS lifetime_orders,
    SUM(o.total_amount) AS lifetime_revenue,
    AVG(o.total_amount) AS avg_order_value,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(CURRENT_DATE(), MAX(o.order_date)) AS days_since_last_order
FROM silver_customers c
LEFT JOIN silver_orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email, c.signup_date;