증분 리프레시(incremental refresh)를 위한 쿼리 최적화하기

증분 리프레시(incremental refresh)를 위한 쿼리 최적화하기

다이나믹 테이블의 증분 리프레시(incremental refresh)와 잘 맞는 쿼리를 설계하는 방법을 알려드릴게요. 어떤 연산자(operator)가 효율적으로 동작하는지, 어떤 구조로 바꿔야 하는지, 그리고 문제를 진단하는 법까지 정리합니다.

출처: Snowflake 문서

본문

증분 리프레시에 지원되는 쿼리 구문의 전체 목록은 다이나믹 테이블 지원 쿼리를 참고하세요.

쿼리를 먼저 단독으로 실행해 보세요

쿼리를 다이나믹 테이블로 감싸기 전에, 먼저 단독 SELECT로 실행해서 실행 시간을 확인하세요. 쿼리 실행 시간이 target lag보다 길다면 다이나믹 테이블은 결코 target lag 안에 들어올 수 없어요. 전체 <C>dt_orders</C> 정의는 다이나믹 테이블 만들기를 참고하세요.

쿼리 프로필에서 실행 시간, spill된 바이트, 스캔된 파티션 수를 확인하세요. 쿼리 프로필에 접근하는 방법은 실행 시간 살펴보기를 참고하세요. 이 값들이 기준선(baseline)이 됩니다. 단독 쿼리가 느리다면, 다이나믹 테이블을 만들기 전에 먼저 최적화하세요.

연산자별 성능 기대치

모든 연산자가 증분 리프레시에서 동일한 이득을 얻는 것은 아니에요. 어떤 연산자는 변경된 행만 처리하는 반면, 어떤 연산자는 그룹이나 파티션 안의 어떤 행이 변경되면 전체 그룹·파티션을 통째로 처리해야 합니다.

참고 10초 미만의 짧은 쿼리는 쿼리 컴파일, 웨어하우스 스케줄링 같은 고정 오버헤드 때문에 증분 리프레시의 이득이 작아 보일 수 있어요.

꾸준히 잘 동작하는 연산자

다음 연산자들은 변경된 행만 처리하며 변경량에 선형적으로 확장됩니다:

• SELECT
• WHERE
• FROM <base table>
• UNION ALL
• LATERAL FLATTEN
• QUALIFY (RANK, ROW_NUMBER, 또는 DENSE_RANK) … = 1 (insert-only 워크로드; delete는 더 넓은 파티션 스캔이 필요할 수 있음)

데이터 지역성(data locality)의 영향을 받는 연산자

이 연산자들의 성능은 데이터 지역성에 따라 달라져요. 데이터 지역성은 Snowflake가 같은 키 값을 가진 행들을 얼마나 가깝게 저장하는지를 나타내는 지표입니다.

• INNER JOIN
• OUTER JOIN
• GROUP BY
• DISTINCT
• OVER (윈도우 함수)

이 연산자들은 변경이 그룹화 키나 파티션 키의 약 5% 미만에만 영향을 줄 때 잘 동작해요. 변경이 많은 키에 퍼져 있거나 기본 테이블(base table)에 클러스터링이 되어 있지 않으면, 증분 리프레시가 전체 리프레시(full refresh)보다 더 느릴 수 있어요.

연산자 레퍼런스

다음 표는 증분 리프레시 중에 Snowflake가 각 SQL 연산자를 처리하는 방식을 설명합니다.

연산자	Snowflake가 처리하는 방식	성능 참고
SELECT	변경된 행에만 표현식을 적용합니다.	잘 동작합니다. 특별히 고려할 사항이 없습니다.
WHERE	변경된 행에만 조건(predicate)을 평가합니다.	잘 동작합니다. 비용이 변경량에 선형적으로 증가합니다. 선택도가 높은 WHERE는 출력이 변하지 않아도 웨어하우스가 계속 떠 있어야 할 수 있습니다.
FROM <table>	마지막 리프레시 이후 Snowflake가 추가하거나 제거한 마이크로 파티션을 스캔합니다.	비용이 변경된 파티션 수에 비례합니다. 변경을 기본 테이블의 약 5% 이하로 유지하세요.
UNION ALL	각 측의 변경 사항의 합집합을 취합니다.	잘 동작합니다. 특별히 고려할 사항이 없습니다.
WITH (CTE)	각 공통 테이블 표현식(Common Table Expression)의 변경 사항을 계산합니다.	잘 동작하지만, 지나치게 복잡한 단일 테이블 정의는 피하세요. 여러 다이나믹 테이블로 나누는 것을 고려하세요.
스칼라 집계(Scalar aggregates)	입력이 변경될 때마다 집계를 완전히 다시 계산합니다.	성능이 중요한 테이블에서는 피하세요. 대신 상수로 그룹화하는 것을 고려하세요.
GROUP BY	변경이 포함된 모든 그룹화 키의 집계를 다시 계산합니다.	기본 테이블을 그룹화 키로 클러스터링하세요. 키에서 복합 표현식은 피하세요. 집계 최적화를 참고하세요.
DISTINCT	GROUP BY ALL과 동일합니다.	지역성에 민감합니다. QUALIFY 사용을 고려하세요. 중복 제거 최적화를 참고하세요.
윈도우 함수(Window functions)	변경이 포함된 모든 파티션의 함수를 다시 계산합니다.	항상 PARTITION BY를 포함하세요. 기본 테이블을 파티션 키로 클러스터링하세요. 윈도우 함수 최적화를 참고하세요.
INNER JOIN	각 측의 변경 사항을 다른 테이블과 조인합니다.	한쪽이 작거나 변경이 드물면 잘 동작합니다. 덜 자주 변경되는 쪽을 클러스터링하세요. 조인 최적화를 참고하세요.
OUTER JOIN	NULL 계산을 위해 NOT EXISTS 쿼리와 내부 조인 로직을 결합합니다.	가장 지역성에 민감한 연산자입니다. 조인 최적화를 참고하세요.
LATERAL FLATTEN	변경된 행에만 flatten을 적용합니다.	잘 동작합니다. 비용이 변경량에 선형적으로 증가합니다.
QUALIFY with ranking	ROW_NUMBER/RANK/DENSE_RANK … = 1에 최적화된 경로를 사용합니다.	다이나믹 테이블의 최상위 프로젝션에 배치하면 매우 효율적입니다. 중복 제거 최적화를 참고하세요.

일반적인 최적화 패턴

다음 절에서는 지역성에 민감한 연산자를 사용하는 쿼리를 재구성하는 방법을 보여 드립니다.

집계 최적화하기

GROUP BY를 사용하면 Snowflake는 변경이 포함된 모든 그룹화 키의 집계를 다시 계산합니다. 성능은 다음 요인에 따라 달라집니다:

• 데이터 클러스터링: 그룹화 키로 클러스터링된 기본 테이블이 가장 잘 동작합니다.
• 변경 분포: 변경을 그룹화 키의 5% 미만으로 유지하세요.
• 키 복잡성: 단순한 컬럼 참조가 복합 표현식보다 더 잘 동작합니다.

문제: 그룹화 키의 복합 표현식

이 쿼리는 그룹화 키가 표현식이라 성능이 좋지 않습니다:

CREATE OR REPLACE DYNAMIC TABLE dt_hourly_sums
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT DATE_TRUNC('minute', order_date), SUM(quantity * unit_price)
FROM raw_orders
GROUP BY 1;

해결책: 표현식을 구체화(materialize)하세요

두 개의 다이나믹 테이블로 나눠 단순한 그룹화 키를 노출하세요:

CREATE OR REPLACE DYNAMIC TABLE dt_orders_with_minute
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
AS
SELECT DATE_TRUNC('minute', order_date) AS order_minute, quantity * unit_price AS line_total
FROM raw_orders;

CREATE OR REPLACE DYNAMIC TABLE dt_hourly_sums
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT order_minute, SUM(line_total)
FROM dt_orders_with_minute
GROUP BY 1;

중간 테이블이 GROUP BY용 단순 컬럼을 노출해서, Snowflake가 파티션 수준에서 변경을 더 효율적으로 추적할 수 있어요.

조인 최적화하기

조인 성능은 어느 쪽이 변경되는지와 데이터를 어떻게 클러스터링하는지에 따라 달라집니다.

• INNER JOIN: Snowflake는 왼쪽의 변경 사항을 오른쪽 테이블과 조인한 뒤, 오른쪽의 변경 사항을 왼쪽 테이블과 조인합니다. 한쪽이 작거나 변경이 드물 때 잘 동작해요.
• OUTER JOIN: Snowflake는 매칭되지 않는 행의 NULL 값도 계산해야 합니다. 가장 지역성에 민감한 연산자예요. 어느 쪽이 변경되는지에 따라 성능이 크게 달라집니다.

문제: 양쪽 모두 큰데 클러스터링이 없는 테이블

어느 기본 테이블도 조인 키로 클러스터링되어 있지 않습니다:

CREATE OR REPLACE DYNAMIC TABLE dt_order_details
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT o.order_id, o.customer_id, c.customer_name, o.quantity
FROM raw_orders o
JOIN dim_customers c ON o.customer_id = c.customer_id;

해결책: 덜 자주 변경되는 테이블을 클러스터링하세요

차원 테이블(dimension table)을 조인 키로 클러스터링해서 조인이 더 나은 지역성을 얻도록 하세요:

ALTER TABLE dim_customers CLUSTER BY (customer_id);

CREATE OR REPLACE DYNAMIC TABLE dt_order_details
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT o.order_id, o.customer_id, c.customer_name, o.quantity
FROM raw_orders o
JOIN dim_customers c ON o.customer_id = c.customer_id;

OUTER JOIN의 경우:

• 더 자주 변경되는 테이블을 왼쪽(LEFT)에 두세요.
• OUTER 키워드 반대쪽의 변경을 최소화하세요.
• FULL OUTER JOIN은 양쪽 모두 좋은 지역성이 필수입니다.
• 가능하면 내부 조인(inner join)을 사용하세요. 데이터에 참조 무결성(referential integrity)이 있다면 outer join이 필요 없어요.

문제: 비동등(non-equality) 조인 조건이 행 폭발을 일으킴

범위 조인(range join)이나 BETWEEN 조건 같은 비동등 조인 조건은 중간 행 폭발(intermediate row explosion)을 일으켜, 조인이 기술적으로 지원되더라도 증분 리프레시를 극도로 느리게 만들 수 있어요.

예를 들어, 시간 범위를 사용해 sessions를 events에 조인하면 수십억 개의 중간 행이 생성될 수 있습니다:

-- 피하세요: 비동등 조인이 행 폭발을 일으킵니다.
CREATE OR REPLACE DYNAMIC TABLE dt_session_events
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
SELECT s.session_id, e.event_type, e.event_time
FROM sessions s
JOIN events e
    ON e.event_time BETWEEN s.start_time AND s.end_time;

해결책: 타임스탬프를 bin으로 나누고 동등 조인(equality join)을 사용하세요

범위 조건을 bin된 시간 컬럼의 동등 조인으로 바꾸세요. bin을 상위 다이나믹 테이블에서 구체화하세요:

CREATE OR REPLACE DYNAMIC TABLE dt_events_binned
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
SELECT *, DATE_TRUNC('hour', event_time) AS event_hour
FROM events;

CREATE OR REPLACE DYNAMIC TABLE dt_sessions_binned
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
SELECT *, DATE_TRUNC('hour', start_time) AS session_hour
FROM sessions;

-- 이제 동등 키로 조인합니다. 후처리 필터가 원래의 범위 로직을 유지합니다.
CREATE OR REPLACE DYNAMIC TABLE dt_session_events
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
SELECT s.session_id, e.event_type, e.event_time
FROM dt_sessions_binned s
JOIN dt_events_binned e
    ON s.session_hour = e.event_hour
WHERE e.event_time BETWEEN s.start_time AND s.end_time;

<C>session_hour</C>의 동등 조인은 Snowflake가 매칭해야 하는 행을 제한해서 행 폭발을 피하게 해줘요. 그런 다음 WHERE 절이 정밀한 범위 필터를 적용합니다.

중요 이 패턴은 각 세션이 단일 시간 bin 안에 들어간다고 가정합니다. 세션이 여러 bin에 걸칠 수 있다면(예: 10:30에 시작해 12:15에 끝나는 세션), dt_sessions_binned 테이블에 bin당 한 행을 생성해서 알림 없이 이벤트가 유실되는 것을 방지하세요. 최대 세션 지속 시간보다 큰 bin 폭을 선택하거나, 각 세션을 여러 bin 행으로 나누세요.

윈도우 함수 최적화하기

Snowflake는 변경이 포함된 모든 파티션의 윈도우 함수를 다시 계산합니다. GROUP BY와 같은 방식으로 최적화하세요.

핵심 요구사항:

• 항상 PARTITION BY 절을 포함하세요. PARTITION BY가 없는 윈도우 함수는 전체 데이터셋을 하나의 파티션으로 취급해서 매 주기마다 전체 리프레시가 발생합니다.
• 기본 테이블을 파티션 키로 클러스터링하세요.
• 변경을 파티션의 5% 미만으로 유지하세요.

문제: 파티션 클러스터링이 없는 윈도우 함수

기본 테이블이 파티션 키로 클러스터링되어 있지 않습니다:

CREATE OR REPLACE DYNAMIC TABLE dt_ranked_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT region, salesperson, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS sales_rank
FROM daily_sales;

해결책: 파티션 키로 클러스터링하세요

기본 테이블을 윈도우 함수의 파티션 키로 클러스터링하세요:

ALTER TABLE daily_sales CLUSTER BY (region);

CREATE OR REPLACE DYNAMIC TABLE dt_ranked_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT region, salesperson, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS sales_rank
FROM daily_sales;

효율적으로 중복 제거하기

DISTINCT와 QUALIFY 모두 중복을 제거할 수 있지만, 증분 리프레시에서는 성능이 달라요.

• DISTINCT: <C>GROUP BY ALL</C>과 동일합니다. 성능이 전적으로 데이터 지역성에 달려 있습니다.
• ROW_NUMBER = 1을 사용한 QUALIFY: Snowflake는 <C>QUALIFY ROW_NUMBER() ... = 1</C> 패턴이 다이나믹 테이블의 최상위 프로젝션에 나타날 때 최적화합니다. 이 패턴은 전체 리프레시보다 일관되게 빠릅니다.
• OVER() 절의 모든 PARTITION BY와 ORDER BY 컬럼을 다이나믹 테이블의 SELECT 목록에 포함하세요. 그래야 엔진이 전체 테이블 스캔 없이 변경된 파티션을 추적할 수 있어요.

권장: DISTINCT 대신 QUALIFY 사용

DISTINCT 사용:

CREATE OR REPLACE DYNAMIC TABLE dt_unique_customers
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT DISTINCT customer_id, customer_name, region
FROM dim_customers;

QUALIFY 사용 (권장):

CREATE OR REPLACE DYNAMIC TABLE dt_unique_customers
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT customer_id, customer_name, region, segment
FROM dim_customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

QUALIFY 버전은 어떤 중복을 유지할지 명시적이며 증분 리프레시에서 일관되게 잘 동작해요. 또한 데이터가 이미 유일하거나 상위에서 중복을 제거했다면 불필요한 DISTINCT 절도 제거하세요.

다이나믹 테이블당 블로킹 연산자 제한하기

일부 연산자는 블로킹(blocking)입니다. 즉 Snowflake가 출력을 만들기 전에 모든 입력 행을 봐야 해요. 블로킹 연산자는 다음과 같습니다:

• 윈도우 함수 (출력 전에 전체 파티션을 처리해야 함)
• GROUP BY와 DISTINCT
• ORDER BY (서브쿼리에서)

쿼리가 여러 블로킹 연산자를 결합하면 각각 이전 연산자가 끝나기를 기다려야 해서 병렬성이 줄고 메모리 압력이 커집니다. 일반적으로 다이나믹 테이블 하나에는 블로킹 연산을 하나만 두는 것을 권장합니다. 조인과 집계, 윈도우 함수가 결합된 쿼리는 각 단계가 블로킹 단계 하나를 처리하도록 중간 다이나믹 테이블로 나누세요.

복잡한 쿼리를 여러 다이나믹 테이블로 나누기

복잡한 쿼리를 중간 다이나믹 테이블로 나누면 병목을 식별하기 쉬워지고 증분 리프레시 효율도 높아져요.

지침:

• 일찍 필터링하세요. 기본 테이블에 가장 가까운 다이나믹 테이블에서 WHERE 절을 적용해서 다운스트림 테이블이 처리할 행 수를 줄이세요.
• 일찍 중복 제거하세요. 상위에서 중복 행을 제거해서 다운스트림에서 반복되는 DISTINCT 작업을 피하세요.
• 블로킹 연산자 사이를 나누세요. 조인, 집계, 윈도우 함수를 별도의 중간 다이나믹 테이블로 옮겨서 각 단계가 자기 핵심 연산에 좋은 데이터 지역성을 갖게 하세요.
• 복합 표현식을 구체화하세요. <C>DATE_TRUNC('minute', ts)</C> 같은 표현식을 그룹화하기 전에 중간 테이블로 옮기세요. 집계 최적화를 참고하세요.

초기 복잡 쿼리:

CREATE OR REPLACE DYNAMIC TABLE dt_final_result
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT DATE_TRUNC('day', o.order_date) AS order_day, c.region, COUNT(*) AS order_count, SUM(o.quantity * o.unit_price) AS daily_revenue
FROM raw_orders o
JOIN dim_customers c ON o.customer_id = c.customer_id
GROUP BY ALL;

중간 다이나믹 테이블을 추가해 파이프라인을 나누세요:

CREATE OR REPLACE DYNAMIC TABLE dt_stg_order_customers
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
AS
SELECT o.order_id, o.order_date, o.quantity * o.unit_price AS line_total, c.region
FROM raw_orders o
JOIN dim_customers c ON o.customer_id = c.customer_id;

CREATE OR REPLACE DYNAMIC TABLE dt_final_result
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
AS
SELECT DATE_TRUNC('day', order_date) AS order_day, region, COUNT(*) AS order_count, SUM(line_total) AS daily_revenue
FROM dt_stg_order_customers
GROUP BY ALL;

중간 테이블이 조인을 처리하고 최종 테이블이 집계를 처리합니다. 각 단계가 자기 특정 연산에 더 나은 데이터 지역성을 유지할 수 있어요.

데이터 지역성 개선하기

데이터 지역성(data locality)은 Snowflake가 같은 키 값을 가진 행을 얼마나 가깝게 저장하는지를 나타냅니다. 같은 키를 가진 행이 더 적은 마이크로 파티션에 저장되면(지역성 좋음) 증분 리프레시가 스캔하는 데이터가 줄어들어요. 매칭 키가 많은 마이크로 파티션에 흩어져 있으면(지역성 나쁨) 증분 리프레시가 전체 리프레시보다 오래 걸릴 수 있습니다.

Snowflake가 데이터를 저장하는 방식에 대한 자세한 내용은 마이크로 파티션 & 데이터 클러스터링을 참고하세요.

기본 테이블 클러스터링하기

지역성을 개선하는 가장 효과적인 방법은 정의에서 사용하는 키(JOIN, GROUP BY, PARTITION BY 키)로 기본 테이블을 클러스터링하는 것입니다:

ALTER TABLE raw_orders CLUSTER BY (customer_id);

여러 컬럼으로 조인하고 전부 클러스터링할 수 없을 때:

• 더 큰 테이블을 가장 선택도가 높은 키로 클러스터링하는 것을 우선하세요.
• 각 조인이 잘 클러스터링된 데이터에서 동작하도록 파이프라인을 나누는 것을 고려하세요.

자세한 내용은 클러스터링 키 & 클러스터링 테이블을 참고하세요. 자동 재클러스터링을 활성화하려면 자동 클러스터링을 참고하세요.

지역성에 영향을 주는 요인

기본 테이블 클러스터링 외에도 두 가지 요인이 지역성에 영향을 줍니다:

• 새 데이터가 파티션 키와 어떻게 정렬되는가: 새 행이 키의 작은 부분에만 영향을 줄 때 증분 리프레시가 더 빠르다. 시간별로 그룹화된 시계열 데이터는 새 행이 최근 타임스탬프를 공유하므로 지역성이 좋다. 값이 테이블 전체에 퍼진 컬럼으로 그룹화한 데이터는 지역성이 나쁘다.
• 변경이 다이나믹 테이블 클러스터링과 어떻게 정렬되는가: 리프레시 중 Snowflake가 변경 사항을 다이나믹 테이블에 쓸 때 영향을 받는 행을 찾아야 한다. 시간 순서 테이블의 최근 행 변경은 빠르다. 테이블 전체에 흩어진 변경은 느리다.

이런 요인 때문에 지역성이 나쁘다면, 상위에서 데이터 모델이나 수집(ingestion) 패턴을 재구성하는 것을 고려하세요.

증분 리프레시가 전체 리프레시보다 느릴 때

증분 리프레시가 항상 더 빠른 것은 아니에요. 다음 조건에서는 증분 리프레시가 전체 리프레시보다 느리고 더 비쌀 수 있습니다:

• 높은 변경량: 리프레시 사이에 행이나 마이크로 파티션의 약 5% 이상이 변경되면, 변경 추적 오버헤드가 전체 리프레시 비용을 초과한다.
• 나쁜 데이터 지역성: 변경된 키가 많은 마이크로 파티션에 걸치면 증분 리프레시가 넓게 스캔해야 한다.
• 많은 delete 또는 truncate-reload 패턴: 큰 delete 배치는 제거된 행을 식별하기 위해 많은 파일을 스캔하도록 만들 수 있다.

증분 리프레시가 도움이 되는지 해가 되는지 확인하려면 DYNAMIC_TABLE_REFRESH_HISTORY로 증분 리프레시와 전체 리프레시 시간을 비교하세요:

SELECT refresh_action, AVG(DATEDIFF('second', refresh_start_time, refresh_end_time)) AS avg_seconds
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(NAME => 'mydb.myschema.dt_orders'))
WHERE refresh_start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
AND refresh_action IN ('INCREMENTAL', 'FULL')
GROUP BY refresh_action;

증분 리프레시 평균 시간이 전체 리프레시 평균 시간에 가깝거나 그 이상이면 <C>REFRESH_MODE = FULL</C>로 전환하세요.

참고 워크로드가 보통은 증분 친화적이지만 가끔 변경량이 급증한다면 ADAPTIVE 리프레시를 고려하세요. ADAPTIVE는 기본적으로 증분 리프레시를 사용하지만, 큰 상위 변경이 감지되면 자동으로 재초기화한 뒤 증분 리프레시를 재개합니다. 선언적 최적화로 충분하지 않다면 커스텀 증분화(custom incrementalization)로 리프레시 로직을 완전히 제어할 수 있어요. 매 리프레시마다 실행되는 정확한 MERGE 또는 INSERT 문을 직접 작성합니다.

한번 해 보세요: 다이나믹 테이블을 증분 리프레시로 다시 작성하기

이 튜토리얼은 비효율적인 서브쿼리 패턴을 <C>QUALIFY RANK() = 1</C>로 바꾸면 SCD Type 1 워크로드의 증분 리프레시 성능이 어떻게 개선되는지 보여 줍니다.

사전 요구사항

• 웨어하우스 하나. x-small 웨어하우스면 충분합니다. 튜토리얼은 웨어하우스 이름으로 <C>transform_wh</C>를 사용합니다. 튜토리얼 전체에서 이 이름을 여러분의 웨어하우스 이름으로 바꾸세요.
• 데이터베이스, 스키마, 다이나믹 테이블을 만들 권한. 액세스 제어 권한을 참고하세요.

1단계: 기본 데이터 만들기

가격 기록이 있는 데이터베이스, 스키마, 기본 테이블을 만드세요:

CREATE DATABASE IF NOT EXISTS mydb;
CREATE SCHEMA IF NOT EXISTS mydb.myschema;
USE SCHEMA mydb.myschema;

CREATE OR REPLACE TABLE product_changes (
    product_code VARCHAR(50),
    product_name VARCHAR(200),
    price NUMBER(10,2),
    price_start_date TIMESTAMP_NTZ(9)
);

-- 1억 개의 행 생성: 가격 기록이 있는 10,000개 제품.
INSERT INTO product_changes (product_code, product_name, price, price_start_date)
SELECT 'PC-' || LPAD(TO_VARCHAR(MOD(SEQ4(),10000) + 1),3,'0') AS product_code,
       'Product ' || LPAD(TO_VARCHAR(MOD(SEQ4(),10000) + 1),3,'0') AS product_name,
       ROUND(10.00 + (MOD(SEQ4(),10000) * 5) + (SEQ4() * 0.01),2) AS price,
       DATEADD(MINUTE, SEQ4() * 5, '2025-01-01 00:00:00') AS price_start_date
FROM TABLE(GENERATOR(ROWCOUNT => 100000000));

2단계: 비교용 다이나믹 테이블 두 개 만들기

MAX()를 사용한 자기 조인(self-join) 버전(비효율적)을 만드세요:

CREATE OR REPLACE DYNAMIC TABLE dt_product_current_price_v1
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = transform_wh
    INITIALIZE = ON_SCHEDULE
    REFRESH_MODE = INCREMENTAL
AS
SELECT h.product_code, h.product_name, h.price, h.price_start_date
FROM product_changes h
INNER JOIN (SELECT product_code, MAX(price_start_date) max_price_start_date FROM product_changes GROUP BY product_code) m
ON h.price_start_date = m.max_price_start_date AND h.product_code = m.product_code;

QUALIFY를 사용한 최적화된 버전을 만드세요:

CREATE OR REPLACE DYNAMIC TABLE dt_product_current_price_v2
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
    INITIALIZE = ON_SCHEDULE
AS
SELECT product_code, product_name, price, price_start_date
FROM product_changes
QUALIFY RANK() OVER (PARTITION BY product_code ORDER BY price_start_date DESC) = 1;

두 테이블을 초기화하세요:

ALTER DYNAMIC TABLE dt_product_current_price_v1 REFRESH;
ALTER DYNAMIC TABLE dt_product_current_price_v2 REFRESH;

3단계: 증분 리프레시 성능 비교하기

다섯 제품의 가격을 갱신하는 새 행 1,000개를 삽입하세요:

INSERT INTO product_changes (product_code, product_name, price, price_start_date)
SELECT 'PC-' || LPAD(TO_VARCHAR(MOD(SEQ4(),5) + 1),3,'0') AS product_code,
       'Product ' || LPAD(TO_VARCHAR(MOD(SEQ4(),5) + 1),3,'0') AS product_name,
       ROUND(50.00 + (MOD(SEQ4(),10) * 5) + ((SEQ4() + 100000000) * 0.01),2) AS price,
       DATEADD(MINUTE, (SEQ4() + 100000000) * 5, '2025-01-01 00:00:00') AS price_start_date
FROM TABLE(GENERATOR(ROWCOUNT => 1000));

각 테이블을 리프레시하고 비교하세요:

ALTER DYNAMIC TABLE dt_product_current_price_v1 REFRESH;
ALTER DYNAMIC TABLE dt_product_current_price_v2 REFRESH;

리프레시 기록에서 결과를 확인하세요:

SELECT name, refresh_action, DATEDIFF('millisecond', refresh_start_time, refresh_end_time) AS duration_ms
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(NAME_PREFIX => 'mydb.myschema.dt_product_current_price_'))
ORDER BY refresh_start_time DESC
LIMIT 4;

QUALIFY 버전(dt_product_current_price_v2)은 엔진이 변경된 다섯 제품만 식별해 처리하므로 훨씬 빠르게 완료되어야 해요. 자기 조인 버전(dt_product_current_price_v1)은 10,000개 제품 전체에 걸쳐 MAX() 서브쿼리를 다시 계산해야 합니다.

정리하기

튜토리얼 데이터베이스와 그 안의 모든 객체를 삭제하세요:

DROP DATABASE mydb;

더 알아보기 (Learn more)

• 다이나믹 테이블 지원 쿼리
• 다이나믹 테이블 리프레시 모드
• 커스텀 증분화(custom incrementalization)