Materialized View 증분 처리

-- BAD: MV에서 비결정적 함수 사용 → Full Recompute
CREATE OR REFRESH MATERIALIZED VIEW daily_summary
AS SELECT
  product,
  SUM(amount) AS total,
  CURRENT_DATE() AS report_date  -- 비결정적!
FROM orders
GROUP BY product;

-- GOOD: 소스 ST에서 날짜를 미리 기록
CREATE OR REFRESH STREAMING TABLE orders_enriched
AS SELECT
  *,
  CURRENT_DATE() AS etl_date  -- ST에서는 문제 없음 (새 행에만 적용)
FROM STREAM(raw_orders);

CREATE OR REFRESH MATERIALIZED VIEW daily_summary
AS SELECT
  product,
  SUM(amount) AS total,
  etl_date AS report_date  -- 결정적 참조
FROM orders_enriched
GROUP BY product, etl_date;

-- 소스 테이블에 incremental refresh 전제조건 설정
ALTER TABLE catalog.schema.source_table
SET TBLPROPERTIES (
  'delta.enableRowTracking' = 'true',
  'delta.enableDeletionVectors' = 'true',
  'delta.enableChangeDataFeed' = 'true'
);

-- BAD: 하나의 MV에 모든 로직
CREATE OR REFRESH MATERIALIZED VIEW final_report
AS SELECT
  o.region,
  p.category,
  SUM(o.amount) AS total_sales,
  COUNT(DISTINCT o.customer_id) AS unique_customers,
  AVG(r.rating) AS avg_rating
FROM orders o
JOIN products p ON o.product_id = p.id
JOIN reviews r ON o.order_id = r.order_id
GROUP BY o.region, p.category;

-- GOOD: 단계별 MV 분리
CREATE OR REFRESH MATERIALIZED VIEW order_product
AS SELECT
  o.order_id,
  o.region,
  o.amount,
  o.customer_id,
  p.category
FROM orders o
JOIN products p ON o.product_id = p.id;

CREATE OR REFRESH MATERIALIZED VIEW order_with_review
AS SELECT
  op.order_id,
  op.region,
  op.amount,
  op.customer_id,
  op.category,
  r.rating
FROM order_product op
LEFT JOIN reviews r ON op.order_id = r.order_id;

CREATE OR REFRESH MATERIALIZED VIEW final_report
AS SELECT
  region,
  category,
  SUM(amount) AS total_sales,
  COUNT(DISTINCT customer_id) AS unique_customers,
  AVG(rating) AS avg_rating
FROM order_with_review
GROUP BY region, category;

-- BAD: Window 함수 → Full Recompute
CREATE OR REFRESH MATERIALIZED VIEW latest_orders
AS SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) WHERE rn = 1;

-- GOOD: 집계 함수로 대체 (마지막 주문 날짜 기준)
CREATE OR REFRESH MATERIALIZED VIEW latest_order_dates
AS SELECT
  customer_id,
  MAX(order_date) AS latest_order_date,
  MAX_BY(order_id, order_date) AS latest_order_id,
  MAX_BY(amount, order_date) AS latest_amount
FROM orders
GROUP BY customer_id;

CREATE OR REFRESH MATERIALIZED VIEW daily_sales
REFRESH POLICY INCREMENTAL
AS SELECT
  sale_date,
  region,
  SUM(amount) AS total
FROM sales
GROUP BY sale_date, region;

CREATE OR REFRESH MATERIALIZED VIEW daily_sales
REFRESH POLICY INCREMENTAL STRICT
AS SELECT
  sale_date,
  region,
  SUM(amount) AS total
FROM sales
GROUP BY sale_date, region;

EXPLAIN MATERIALIZED VIEW FOR
SELECT
  sale_date,
  region,
  SUM(amount) AS total,
  CURRENT_DATE() AS report_date  -- 문제를 미리 발견
FROM sales
GROUP BY sale_date, region;

Cannot incrementally refresh: Non-deterministic function CURRENT_DATE() detected.
Recommendation: Move CURRENT_DATE() to the source table.