Snowflake

-- 데이터베이스 및 스키마 생성
CREATE DATABASE analytics_db;
CREATE SCHEMA analytics_db.raw;

-- 가상 웨어하우스 생성
CREATE WAREHOUSE analytics_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 300       -- 5분 후 자동 중지
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3;   -- 멀티 클러스터 (자동 확장)

-- 테이블 생성
CREATE TABLE raw.sales (
    sale_id NUMBER AUTOINCREMENT,
    product_id NUMBER,
    customer_id NUMBER,
    sale_date DATE,
    amount DECIMAL(10,2),
    region VARCHAR(50)
);

-- S3에서 데이터 로드
COPY INTO raw.sales (product_id, customer_id, sale_date, amount, region)
FROM @my_s3_stage/sales/
FILE_FORMAT = (TYPE = 'PARQUET')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

-- 분석 쿼리
SELECT
    DATE_TRUNC('month', sale_date) AS month,
    region,
    SUM(amount) AS total_sales,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM raw.sales
WHERE sale_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY 1, total_sales DESC;

-- Zero-Copy Cloning (즉시 복제)
CREATE TABLE raw.sales_backup CLONE raw.sales;
CREATE DATABASE analytics_dev CLONE analytics_db;

-- Time Travel (과거 데이터 조회, 기본 1일)
SELECT * FROM raw.sales AT (OFFSET => -60*60);  -- 1시간 전
SELECT * FROM raw.sales BEFORE (TIMESTAMP => '2024-01-15 10:00:00');

-- Data Sharing (읽기 전용 공유)
CREATE SHARE sales_share;
GRANT USAGE ON DATABASE analytics_db TO SHARE sales_share;
GRANT SELECT ON TABLE raw.sales TO SHARE sales_share;
ALTER SHARE sales_share ADD ACCOUNTS = partner_account;

-- Snowpark (Python)
-- from snowflake.snowpark import Session
-- from snowflake.snowpark.functions import col, sum as sum_
--
-- session = Session.builder.configs(connection_params).create()
--
-- df = session.table("raw.sales")
-- result = (df
--     .filter(col("sale_date") >= "2024-01-01")
--     .group_by("region")
--     .agg(sum_("amount").alias("total_sales"))
--     .sort(col("total_sales").desc())
-- )
-- result.show()

-- Python UDF 생성
CREATE OR REPLACE FUNCTION calculate_tax(amount FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'compute_tax'
AS $$
def compute_tax(amount):
    return amount * 0.1
$$;

SELECT sale_id, amount, calculate_tax(amount) AS tax FROM raw.sales;

-- Stream (CDC)
CREATE STREAM sales_changes ON TABLE raw.sales;
-- 변경 사항 조회
SELECT * FROM sales_changes;