리니지 기본 개념과 시각화

silver_orders.total_amount 컬럼 타입 변경 시 영향 범위:
  → gold_daily_revenue.total_revenue       (SUM 집계)
  → gold_customer_360.lifetime_revenue     (고객별 SUM)
  → revenue_dashboard                      (AI/BI Dashboard)
  → churn_prediction_model                 (MLflow 모델 Feature)

쿼리 실행
  → Spark QueryExecution Plan 분석
  → 입력 테이블/컬럼 및 출력 테이블/컬럼 추출
  → Unity Catalog Metastore에 리니지 메타데이터 저장
  → system.access.table_lineage / column_lineage 테이블에서 조회 가능

silver_orders.total_amount
  ← bronze_orders.raw_data:amount  (JSON에서 추출)

gold_daily_revenue.total_revenue
  ← silver_orders.total_amount  (SUM 집계)

gold_customer_360.lifetime_revenue
  ← silver_orders.total_amount  (고객별 SUM)

-- 특정 테이블의 downstream: 이 테이블을 참조하는 모든 객체
SELECT
    target_table_full_name   AS downstream_table,
    target_type,
    event_time,
    created_by
FROM system.access.table_lineage
WHERE source_table_full_name = 'production.ecommerce.silver_orders'
ORDER BY event_time DESC;

-- 특정 테이블의 upstream: 이 테이블이 참조하는 소스
SELECT
    source_table_full_name   AS upstream_table,
    source_type,
    event_time,
    created_by
FROM system.access.table_lineage
WHERE target_table_full_name = 'production.ecommerce.gold_daily_revenue'
ORDER BY event_time DESC;

-- 특정 컬럼의 upstream: total_revenue가 어디서 왔는지
SELECT
    source_table_full_name,
    source_column_name,
    target_column_name,
    event_time
FROM system.access.column_lineage
WHERE target_table_full_name = 'production.ecommerce.gold_daily_revenue'
  AND target_column_name = 'total_revenue'
ORDER BY event_time DESC;

-- PII 컬럼의 전파 경로 추적 (GDPR 감사 대응)
SELECT
    source_table_full_name,
    source_column_name,
    target_table_full_name,
    target_column_name,
    event_time
FROM system.access.column_lineage
WHERE source_column_name IN ('email', 'phone', 'ssn', 'name')
ORDER BY source_table_full_name, target_table_full_name;

-- 미사용 테이블 후보 식별 (비용 최적화)
-- downstream이 한 번도 기록되지 않은 테이블 확인
SELECT DISTINCT source_table_full_name
FROM system.access.table_lineage
WHERE source_table_full_name NOT IN (
    SELECT DISTINCT target_table_full_name
    FROM system.access.table_lineage
)
ORDER BY 1;

-- 변경 전, 영향 받는 downstream 테이블 전체 목록 조회
SELECT DISTINCT
    target_table_full_name,
    target_type
FROM system.access.column_lineage
WHERE source_table_full_name = 'production.ecommerce.silver_orders'
  AND source_column_name = 'total_amount';

-- email 컬럼이 전파된 모든 테이블 확인
SELECT DISTINCT target_table_full_name
FROM system.access.column_lineage
WHERE source_column_name = 'email'
ORDER BY 1;

-- 월별 리니지 검토: 최근 30일간 변경된 테이블 의존 관계 확인
SELECT
    source_table_full_name,
    target_table_full_name,
    COUNT(*)        AS lineage_event_count,
    MAX(event_time) AS last_seen
FROM system.access.table_lineage
WHERE event_time >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY 1, 2
ORDER BY last_seen DESC;