활용 시나리오와 내부 동작

-- 이 컬럼을 참조하는 모든 downstream 확인
SELECT DISTINCT
    target_table_full_name,
    target_column_name
FROM system.lineage.column_lineage
WHERE source_table_full_name = 'production.ecommerce.silver_orders'
    AND source_column_name = 'amount';

-- 결과:
-- gold_daily_revenue.total_revenue
-- gold_customer_360.lifetime_revenue
-- 매출 대시보드 (dataset query)
-- → 이 3곳도 함께 변경해야 합니다

-- upstream 추적: 이 테이블의 소스 확인
SELECT source_table_full_name
FROM system.lineage.table_lineage
WHERE target_table_full_name = 'production.ecommerce.gold_daily_revenue';
-- → silver_orders

-- silver_orders의 upstream 확인
SELECT source_table_full_name
FROM system.lineage.table_lineage
WHERE target_table_full_name = 'production.ecommerce.silver_orders';
-- → bronze_orders

-- → bronze_orders에서 해당 날짜의 원본 데이터를 확인하여 원인 파악

-- system.lineage.table_lineage 테이블 스키마
SELECT * FROM system.lineage.table_lineage LIMIT 0;
-- 주요 컬럼:
--   source_table_full_name    : 소스 테이블 (catalog.schema.table)
--   source_type               : TABLE, VIEW, STREAMING_TABLE 등
--   target_table_full_name    : 타겟 테이블
--   target_type               : TABLE, VIEW, MODEL, DASHBOARD 등
--   created_by                : 리니지를 생성한 사용자/서비스 프린시펄
--   event_time                : 리니지가 기록된 시간

-- 컬럼 리니지 상세 조회
SELECT
    source_table_full_name,
    source_column_name,
    target_table_full_name,
    target_column_name,
    event_time
FROM system.lineage.column_lineage
WHERE target_table_full_name = 'production.ecommerce.gold_daily_revenue'
ORDER BY target_column_name, event_time DESC;

-- 특정 테이블의 전체 downstream 재귀 탐색 (3단계까지)
WITH RECURSIVE downstream AS (
    -- 1단계: 직접 downstream
    SELECT
        source_table_full_name,
        target_table_full_name,
        target_type,
        1 AS depth
    FROM system.lineage.table_lineage
    WHERE source_table_full_name = 'production.ecommerce.bronze_orders'

    UNION ALL

    -- 2~3단계: 간접 downstream
    SELECT
        d.target_table_full_name AS source_table_full_name,
        l.target_table_full_name,
        l.target_type,
        d.depth + 1 AS depth
    FROM downstream d
    JOIN system.lineage.table_lineage l
        ON d.target_table_full_name = l.source_table_full_name
    WHERE d.depth < 3
)
SELECT DISTINCT
    source_table_full_name,
    target_table_full_name,
    target_type,
    depth
FROM downstream
ORDER BY depth, target_table_full_name;

-- 최근 90일간 downstream이 없는 테이블 (미사용 후보)
SELECT t.table_catalog, t.table_schema, t.table_name, t.created
FROM system.information_schema.tables t
LEFT JOIN system.lineage.table_lineage l
    ON t.table_catalog || '.' || t.table_schema || '.' || t.table_name = l.source_table_full_name
    AND l.event_time > DATEADD(day, -90, CURRENT_TIMESTAMP())
WHERE l.source_table_full_name IS NULL
  AND t.table_catalog = 'production'
  AND t.table_schema = 'ecommerce'
ORDER BY t.created;

-- 가장 많은 downstream을 가진 테이블 Top 20
SELECT
    source_table_full_name,
    COUNT(DISTINCT target_table_full_name) AS downstream_count,
    COLLECT_SET(target_type) AS target_types
FROM system.lineage.table_lineage
WHERE event_time > DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY source_table_full_name
ORDER BY downstream_count DESC
LIMIT 20;