동적 테이블용 입력 데이터 최적화

동적 테이블용 입력 데이터 최적화

이 페이지는 기본 키(primary keys)와 클러스터링이 Snowflake가 각 갱신 중 더 적은 행을 처리하도록 돕는 방법을 설명해요. 파이프라인 개발자는 INSERT OVERWRITE 또는 다른 전체 교체(full-replacement) 로딩 패턴을 사용하는 기본 테이블 위에 파이프라인을 구축하기 전에 이 페이지를 읽어야 해요.

기본 테이블에 RELY 속성이 있는 기본 키를 추가하세요. 이렇게 하면 Snowflake가 INSERT OVERWRITE 후에 실제로 변경된 행을 감지할 수 있고, 모든 행을 새 것으로 취급하지 않아요:

ALTER TABLE dim_customers
  ADD CONSTRAINT pk_dim_customers PRIMARY KEY (customer_id) RELY;

출처: Snowflake 문서

본문

기본 키가 갱신 작업을 줄이는 방법

각 갱신 중 Snowflake는 기본 테이블에서 어떤 행이 변경됐는지 결정해야 해요. 기본 키가 없으면 INSERT OVERWRITE는 모든 변경 추적 메타데이터를 교체하고 Snowflake는 모든 행을 새 것으로 취급해요. 이렇게 하면 실제로 변경된 데이터가 작은 비율일 때도 전체 갱신이 강제돼요.

기본 테이블에 RELY 속성이 있는 PRIMARY KEY 제약 조건이 있으면 Snowflake는 그 키 값을 업데이트 전반의 안정적인 행 식별자로 사용해요. 덮어쓰기 전후의 기본 키 값을 비교하고, 실제로 변경된 행만 식별해 다운스트림 동적 테이블로 처리해요.

RELY는 고유성을 강제하지 않음 RELY는 미래의 중복을 방지하지 않아요. RELY를 설정하기 전에 업스트림 ETL에서 고유성을 검증하고, 매 로드 주기 후 검사를 반복하세요.

SELECT customer_id, COUNT(*) AS cnt
FROM dim_customers
GROUP BY customer_id
HAVING cnt > 1;

+-------------+-----+
| CUSTOMER_ID | CNT |
|-------------+-----|
-- (no rows = safe to use RELY)

동적 테이블용 기본 키 유형

Snowflake는 변경 추적에 대해 두 가지 종류의 기본 키를 인식해요. 다음 표는 각각이 언제 적용되는지 요약해요.

유형	생성 방법	사용 시기
RELY가 있는 기본 테이블 PRIMARY KEY	기본 테이블에 제약 조건을 선언하고 RELY를 설정.	INSERT OVERWRITE, COPY INTO, 또는 외부 ETL로 로드되는 기본 테이블.
시스템 파생 고유 키	Snowflake가 동적 테이블의 정의(GROUP BY 또는 QUALIFY ROW_NUMBER() = 1)에서 추론.	구성상 키당 한 행을 만들어내는 동적 테이블.

RELY가 있는 기본 테이블 기본 키

기본 테이블에 RELY 속성이 있는 PRIMARY KEY 제약 조건이 있으면 Snowflake는 모든 다운스트림 동적 테이블의 행 수준 변경 추적에 그 키를 사용해요. 이것이 INSERT OVERWRITE 워크로드를 최적화하는 주요 메커니즘이에요.

CREATE OR REPLACE TABLE dim_products (
  product_id   INT PRIMARY KEY RELY,
  product_name VARCHAR,
  category     VARCHAR,
  price        DECIMAL(10,2)
);

시스템 파생 고유 키

Snowflake는 동적 테이블의 정의에서 자동으로 고유 키를 파생할 수 있어요. 다음 구조물이 시스템 파생 고유 키를 만들어요:

• GROUP BY: 그룹화 컬럼이 키를 형성하는데, 각 그룹이 정확히 하나의 출력 행을 생성하기 때문이에요.
• QUALIFY ROW_NUMBER() = 1: partition-by 컬럼이 키를 형성하는데, 필터가 파티션당 정확히 하나의 행을 유지하기 때문이에요.
• 기본 테이블 기본 키 통과(passthrough): 동적 테이블 정의가 함수, 캐스트, 표현식을 적용하지 않고 RELY 기본 키 컬럼을 통과시키면 시스템 파생 고유 키가 동적 테이블로 전파돼요.

동적 테이블에 시스템 파생 고유 키가 있는지 확인하려면 SHOW UNIQUE KEYS를 실행하세요:

SHOW UNIQUE KEYS IN dt_orders;

+----------------------------+-----------+--------+-------------+-----------+-----+----------------+
| CREATED_ON                 | TABLE_NAME| SCHEMA | COLUMN_NAME | KEY_INDEX | ... | CONSTRAINT_TYPE|
|----------------------------+-----------+--------+-------------+-----------+-----+----------------|
| 2025-01-15 08:32:28 +0000 | DT_ORDERS| TUTORIAL| ORDER_ID   |         1 | ... | UNIQUE         |
+----------------------------+-----------+--------+-------------+-----------+-----+----------------+

쿼리가 행을 반환하지 않으면 동적 테이블에 시스템 파생 고유 키가 없는 거예요.

다음 조건 중 하나 이상이 충족되면 다운스트림 동적 테이블이 전체 갱신 업스트림에 대해 증분 갱신을 사용할 수 있어요:

1. 업스트림에 시스템 파생 고유 키가 있음.
2. 업스트림에 frozen region이 있음.

그렇지 않으면 REFRESH_MODE=INCREMENTAL로 다운스트림 테이블을 만드는 것이 실패하고, REFRESH_MODE=AUTO는 FULL로 결정돼요.

INSERT OVERWRITE 워크로드 최적화

INSERT OVERWRITE는 기본 키의 혜택을 받는 가장 흔한 로딩 패턴이에요. 기본 키가 없으면 모든 INSERT OVERWRITE는 몇 행만 변경됐어도 Snowflake에 전체 삭제·재삽입처럼 보여요.

다음 예시는 권장 패턴을 보여줘요. 기본 테이블이 RELY가 있는 PRIMARY KEY를 선언하고, 다운스트림 동적 테이블은 증분 갱신을 사용해요:

CREATE OR REPLACE TABLE dim_customers (
  customer_id   INT PRIMARY KEY RELY,
  customer_name VARCHAR,
  region        VARCHAR,
  segment       VARCHAR
);

-- 외부 ETL이 이 테이블을 매일 밤 INSERT OVERWRITE로 다시 씁니다.
-- Snowflake가 기본 키를 사용해 실제로 변경된 행을 감지합니다.

CREATE OR REPLACE DYNAMIC TABLE dt_customer_orders
  TARGET_LAG = '10 minutes'
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    c.region,
    ...
  -- remaining columns omitted for brevity
  FROM raw_orders o
  JOIN dim_customers c ON o.customer_id = c.customer_id;

외부 ETL 프로세스가 dim_customers에 INSERT OVERWRITE를 실행하면 Snowflake는 덮어쓰기 전후의 기본 키 값을 비교하고, 변경된 차원 행만 식별하고, 그 변경된 차원에 조인하는 팩트 행만 갱신해요.

📌 기본 테이블에 기본 키를 추가하는 것이 불가능하다면 대안으로 ADAPTIVE 갱신을 고려하세요. ADAPTIVE는 기본 테이블에 기본 키가 없어도 INSERT OVERWRITE 워크로드를 처리할 수 있어요.

기본 테이블 교체 방법이 기본 키에 미치는 영향

모든 테이블 교체 패턴이 기본 키 제약 조건과 변경 추적 기록을 보존하는 것은 아니에요.

방법	기본 키 보존?	변경 기록 보존?	원자적?	다운스트림 동적 테이블에 미치는 영향
INSERT … OVERWRITE (권장)	예	예	예	변경된 행만 처리됨.
TRUNCATE + INSERT/COPY INTO	예	예	아니요 (두 문 사이 테이블이 비어 있음).	로드가 완료되면 INSERT OVERWRITE와 동일하지만, 로드 중 실행되는 갱신은 빈 테이블을 봄.
CREATE OR REPLACE TABLE	아니요 (다시 선언하지 않으면 기본 키가 삭제됨).	아니요 (모든 스트림이 지연됨).	예	전체 재초기화 필요. 기본 키를 다시 선언해도 초기 갱신에 비교할 이전 상태가 없음.

동적 테이블 파이프라인을 공급하는 기본 테이블에는 INSERT OVERWRITE를 사용하세요. 이미 다운스트림 동적 테이블이 있는 테이블에는 CREATE OR REPLACE를 피하세요.

전체 갱신의 다운스트림에서 증분 갱신 활성화

일반적으로 REFRESH_MODE=INCREMENTAL 동적 테이블은 REFRESH_MODE=FULL 동적 테이블에서 읽을 수 없어요. 전체 갱신이 변경 추적 메타데이터를 폐기하기 때문이에요. 업스트림 전체 갱신 동적 테이블에 시스템 파생 고유 키가 있으면 Snowflake가 전체 갱신 사이의 차이를 계산할 수 있고, 다운스트림 테이블은 증분으로 갱신할 수 있어요.

REFRESH_MODE = INCREMENTAL을 명시적으로 설정 시스템 파생 고유 키가 있는 전체 갱신 동적 테이블의 다운스트림에서 증분 갱신을 사용하려면 다운스트림 테이블에 REFRESH_MODE=INCREMENTAL을 명시적으로 설정해야 해요. 이 시나리오에서 REFRESH_MODE=AUTO를 설정하면 여전히 FULL로 결정돼요.

예시: GROUP BY가 시스템 파생 고유 키를 생성

-- Full refresh: GROUP BY가 (order_day, region, segment)에 시스템 파생 고유 키를 생성합니다.
CREATE OR REPLACE DYNAMIC TABLE dt_orders_daily
  TARGET_LAG = '30 minutes'
  WAREHOUSE = transform_wh
  REFRESH_MODE = FULL
AS
  SELECT
    DATE_TRUNC('day', s.order_date) AS order_day,
    c.region,
    c.segment,
    COUNT(*) AS order_count,
    SUM(s.line_total) AS daily_revenue
  FROM dt_orders s
  JOIN dim_customers c ON s.customer_id = c.customer_id
  GROUP BY ALL;

-- Downstream incremental: dt_orders_daily가 시스템 파생 고유 키를 가지므로 작동합니다.
CREATE OR REPLACE DYNAMIC TABLE dt_region_trends
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT
    region,
    AVG(daily_revenue) AS avg_daily_revenue,
    COUNT(*) AS days_with_orders
  FROM dt_orders_daily
  GROUP BY region;

시스템 파생 고유 키 확인

다운스트림 증분 동적 테이블을 만들기 전에 업스트림 테이블에 시스템 파생 고유 키가 있는지 확인하세요:

SHOW UNIQUE KEYS IN dt_orders_daily;

+----------------------------+------------------+--------+-------------+-----------+-----+----------------+
| CREATED_ON                 | TABLE_NAME       | SCHEMA | COLUMN_NAME | KEY_INDEX | ... | CONSTRAINT_TYPE|
|----------------------------+------------------+--------+-------------+-----------+-----+----------------|
| 2025-01-15 10:00:00 +0000 | DT_ORDERS_DAILY | TUTORIAL| ORDER_DAY  |         1 | ... | UNIQUE         |
| 2025-01-15 10:00:00 +0000 | DT_ORDERS_DAILY | TUTORIAL| REGION     |         2 | ... | UNIQUE         |
| 2025-01-15 10:00:00 +0000 | DT_ORDERS_DAILY | TUTORIAL| SEGMENT    |         3 | ... | UNIQUE         |
+----------------------------+------------------+--------+-------------+-----------+-----+----------------+

SHOW UNIQUE KEYS가 행을 반환하지 않으면 REFRESH_MODE=INCREMENTAL로 다운스트림 생성이 실패해요:

-- This fails if the upstream table has no system-derived unique key.
CREATE OR REPLACE DYNAMIC TABLE dt_downstream_agg
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT region, SUM(daily_revenue) AS total_revenue
  FROM dt_orders_daily
  GROUP BY region;

SQL compilation error:
Incremental refresh mode is not supported because the upstream dynamic table
does not have a unique key for change tracking.

기본 키를 클러스터링과 짝짓기

기본 키는 Snowflake에 어떤 행이 변경됐는지 알려줘요. 클러스터링은 Snowflake가 그 행들을 얼마나 효율적으로 찾을 수 있는지를 결정해요. 기본 테이블이 기본 키 컬럼에 또는 근처에 클러스터링되면 Snowflake는 갱신 중 더 많은 마이크로 파티션을 가지치기(prune)하고 더 적은 데이터를 읽을 수 있어요.

최상의 결과를 위해:

• 기본 키 컬럼 또는 다운스트림 동적 테이블과의 조인 조건에서 가장 자주 사용되는 컬럼에 기본 테이블을 클러스터링하세요.
• 기본 테이블에 쿼리 성능용 클러스터링 키가 이미 있으면 그것이 기본 키와 겹치는지 확인하세요. 겹치는 키는 쿼리 가지치기와 갱신 가지치기 모두에 이점을 줘요.
• 백그라운드 재클러스터링은 새 마이크로 파티션을 만들어 갱신 기간의 일시적 급증을 유발할 수 있다는 점에 유의하세요. 이 급증은 일시적이며 재클러스터링이 완료된 후 해결돼요.

일반적인 클러스터링 지침은 마이크로 파티션과 데이터 클러스터링 문서를 참조하세요.

기본 키 추적 저하 감지

기본 키 기반 변경 추적은 오류를 생성하지 않고 저하되거나 작동을 멈출 수 있어요. Snowflake는 이런 경우 예외를 발생시키지 않아요. 대신 표준 변경 추적 컬럼으로 폴백하는데, 이는 기본 키 기반 추적의 성능 이점을 제거해요.

시나리오	어떤 일이 일어나는가	감지 방법
기본 테이블의 중복 기본 키 값	변경 추적이 예상치 못한 결과를 만들 수 있음(행 과소 계산 또는 누락).	RELY를 설정하기 전에 기본 키 컬럼에 GROUP BY/HAVING 쿼리를 실행.
기본 키 컬럼의 마스킹 정책	Snowflake가 키 값을 읽을 수 없어 표준 변경 추적 컬럼으로 폴백.	마스킹 정책이 어떤 기본 키 컬럼에 적용되는지 확인.
ALTER TABLE이 기본 키 제약 조건을 삭제	시스템 파생 고유 키가 다운스트림 동적 테이블에서 사라짐.	스키마 변경 후 SHOW UNIQUE KEYS 실행.

기본 키 컬럼의 마스킹 정책

마스킹 정책이 기본 키 컬럼을 난독화하면 Snowflake는 그 컬럼을 변경 추적에 사용할 수 없어요. 동적 테이블은 갱신을 계속하지만, Snowflake는 표준 변경 추적 컬럼으로 폴백해요. INSERT OVERWRITE 워크로드에서는 이 폴백이 모든 갱신이 모든 행을 처리하게 만드는데, INSERT OVERWRITE가 표준 추적 컬럼을 재설정하기 때문이에요. 일반 DML 작업(INSERT, UPDATE, DELETE)에서는 표준 변경 추적 컬럼이 여전히 행 수준 변경을 감지할 수 있어요.

이를 감지하려면 마스킹 정책이 기본 키 컬럼에 적용되는지 확인하세요:

-- 기본 키에 사용되는 컬럼의 마스킹 정책 확인
SELECT *
FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
  REF_ENTITY_NAME => 'dim_customers',
  REF_ENTITY_DOMAIN => 'TABLE'
));

마스킹 정책이 기본 키 컬럼에 적용되면 SHOW UNIQUE KEYS 출력과 관계없이 기본 키 기반 변경 추적이 비활성화돼요. 기본 키 컬럼에서 마스킹 정책을 제거하거나 비키(non-key) 컬럼으로 옮기세요.

직접 해보기: 기본 키 유무에 따른 갱신 비교

이 튜토리얼은 같은 데이터와 같은 조인 쿼리로 두 파이프라인을 구축해요. 유일한 차이는 차원 테이블에 기본 키가 있는지 여부예요. 그런 다음 INSERT OVERWRITE로 차원 테이블 재작성을 시뮬레이션하고 갱신 성능을 비교해요.

사전 요구 사항

• 데이터베이스, 스키마, 동적 테이블을 만들 권한이 있는 Snowflake 계정.
• X-Small 웨어하우스. 튜토리얼은 모든 예시에서 transform_wh를 사용해요.

1단계: 소스 데이터 만들기

CREATE DATABASE IF NOT EXISTS mydb;

CREATE SCHEMA IF NOT EXISTS mydb.myschema;

USE SCHEMA mydb.myschema;

-- 기본 키가 있는 차원 테이블
CREATE OR REPLACE TABLE dim_products_with_pk (
  product_id   INT PRIMARY KEY RELY,
  product_name VARCHAR(200),
  category     VARCHAR(100),
  price        DECIMAL(10,2)
)
CHANGE_TRACKING = TRUE;

-- 기본 키가 없는 차원 테이블 (나머지 스키마는 동일)
CREATE OR REPLACE TABLE dim_products_no_pk (
  product_id   INT,
  product_name VARCHAR(200),
  category     VARCHAR(100),
  price        DECIMAL(10,2)
)
CHANGE_TRACKING = TRUE;

-- 공유 팩트 테이블
CREATE OR REPLACE TABLE fact_orders (
  order_id   INT,
  product_id INT,
  quantity   INT,
  order_date TIMESTAMP_NTZ
)
CHANGE_TRACKING = TRUE;

두 차원 테이블에 100,000개의 제품을, 팩트 테이블에 1,000만 개의 주문을 삽입하세요:

INSERT INTO dim_products_with_pk (product_id, product_name, category, price)
  SELECT
    SEQ4() + 1 AS product_id,
    'Product ' || LPAD(TO_VARCHAR(SEQ4() + 1), 6, '0') AS product_name,
    'Category ' || LPAD(TO_VARCHAR(MOD(SEQ4(), 10) + 1), 2, '0') AS category,
    ROUND(5.00 + MOD(SEQ4(), 500) * 0.50, 2) AS price
  FROM TABLE(GENERATOR(ROWCOUNT => 100000));


INSERT INTO dim_products_no_pk
  SELECT * FROM dim_products_with_pk;


INSERT INTO fact_orders (order_id, product_id, quantity, order_date)
  SELECT
    SEQ4() + 1 AS order_id,
    MOD(SEQ4(), 100000) + 1 AS product_id,
    MOD(SEQ4(), 10) + 1 AS quantity,
    DATEADD(SECOND, SEQ4(), '2025-01-01 00:00:00') AS order_date
  FROM TABLE(GENERATOR(ROWCOUNT => 10000000));

2단계: 두 파이프라인 만들고 초기 갱신 실행

기본 키 차원 테이블로 하나, 없는 것으로 하나의 파이프라인을 만들어요. 둘 다 같은 조인 쿼리를 사용해요.

-- Pipeline WITH primary key
CREATE OR REPLACE DYNAMIC TABLE dt_enriched_with_pk
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT
    f.order_id,
    f.product_id,
    d.product_name,
    d.category,
    f.quantity,
    d.price,
    f.quantity * d.price AS order_total,
    f.order_date
  FROM fact_orders f
  JOIN dim_products_with_pk d ON f.product_id = d.product_id;

-- Pipeline WITHOUT primary key
CREATE OR REPLACE DYNAMIC TABLE dt_enriched_no_pk
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT
    f.order_id,
    f.product_id,
    d.product_name,
    d.category,
    f.quantity,
    d.price,
    f.quantity * d.price AS order_total,
    f.order_date
  FROM fact_orders f
  JOIN dim_products_no_pk d ON f.product_id = d.product_id;

-- Run the initial refresh for both
ALTER DYNAMIC TABLE dt_enriched_with_pk REFRESH;
ALTER DYNAMIC TABLE dt_enriched_no_pk REFRESH;

3단계: INSERT OVERWRITE 시뮬레이션 및 비교

제품의 10%(Category 01)에 대한 가격을 바꾸고 나머지 행은 동일하게 유지하면서 두 차원 테이블을 다시 써요:

INSERT OVERWRITE INTO dim_products_with_pk
  SELECT
    product_id,
    product_name,
    category,
    CASE WHEN category = 'Category 01' THEN ROUND(price * 1.10, 2) ELSE price END AS price
  FROM dim_products_with_pk;


INSERT OVERWRITE INTO dim_products_no_pk
  SELECT
    product_id,
    product_name,
    category,
    CASE WHEN category = 'Category 01' THEN ROUND(price * 1.10, 2) ELSE price END AS price
  FROM dim_products_no_pk;

두 파이프라인을 갱신하고 성능을 비교하세요:

ALTER DYNAMIC TABLE dt_enriched_no_pk REFRESH;
ALTER DYNAMIC TABLE dt_enriched_with_pk REFRESH;

각각의 갱신 기록을 확인하세요:

SELECT
  name,
  state,
  refresh_trigger,
  DATEDIFF('second', refresh_start_time, refresh_end_time) AS duration_seconds,
  statistics:numInsertedRows::INT AS num_inserted_rows,
  statistics:numDeletedRows::INT AS num_deleted_rows
FROM
  TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'MYDB.MYSCHEMA.DT_ENRICHED_',
    ERROR_ONLY => FALSE
))
ORDER BY refresh_start_time DESC
LIMIT 4;

+-----------------------+-----------+-----------------+------------------+-------------------+------------------+
| NAME                  | STATE     | REFRESH_TRIGGER | DURATION_SECONDS | NUM_INSERTED_ROWS | NUM_DELETED_ROWS |
|-----------------------+-----------+-----------------+------------------+-------------------+------------------|
| DT_ENRICHED_WITH_PK   | SUCCEEDED | MANUAL          |               12 |           1000000 |          1000000 |
| DT_ENRICHED_NO_PK     | SUCCEEDED | MANUAL          |               34 |          10000000 |         10000000 |
| DT_ENRICHED_WITH_PK   | SUCCEEDED | MANUAL          |               45 |          10000000 |                0 |
| DT_ENRICHED_NO_PK     | SUCCEEDED | MANUAL          |               46 |          10000000 |                0 |
+-----------------------+-----------+-----------------+------------------+-------------------+------------------+

기본 키가 있는 파이프라인은 변경된 차원 행의 10%를 참조하는 100만 팩트 행만 처리했어요. 기본 키가 없는 파이프라인은 INSERT OVERWRITE 후 Snowflake가 변경된 행과 변경되지 않은 행을 구분할 수 없었으므로 1,000만 행 전체를 갱신했어요.

💡 팩트 테이블이 커지고 변경된 차원 행의 비율이 줄어들수록 성능 격차는 커져요. 수백만 차원 행과 로드 주기당 한 자리 수 비율 변경이 있는 프로덕션 파이프라인에서는 기본 키 기반 변경 추적이 갱신 시간을 한 자릿수로 줄일 수 있어요.

정리

DROP DATABASE mydb;

다음 단계

• 증분 갱신의 쿼리 수준 튜닝은 증분 갱신용 쿼리 최적화 문서를 참조하세요.
• 변경되지 않은 과거 파티션을 건너뛰려면 Frozen regions와 backfill 문서를 참조하세요.
• 갱신 기간을 추적하고 회귀를 감지하려면 동적 테이블 모니터링 문서를 참조하세요.
• 갱신 모드의 크레딧 영향을 이해하려면 동적 테이블 비용 이해 문서를 참조하세요.
• 동적 테이블 빠른 시작 모범 사례.

더 알아보기 (Learn more)

• 증분 갱신용 쿼리 최적화
• Frozen regions와 backfill
• 동적 테이블 개요