dbt에서의 Surrogate Key 생성 전략

dbt에서의 Surrogate Key 생성 전략

🧩 Surrogate Key란?

• 데이터 웨어하우스에서 조인 성능을 높이고, 변화 추적(SCD 등)을 위해 사용되는 의미 없는 대체 키입니다.

• 자연 키(Natural Key)와 달리, 비즈니스 의미 없이 단순히 데이터 식별자 역할만 수행합니다.

• 고전적인 사용 방법으로는 주로 정수형이었습니다.

• 예: user_sk = 123456

• 운영 시스템에서 이미 존재하는 고유 식별자로, 비즈니스적으로 의미가 있는 키입니다.

• 예: 주민등록번호, 이메일, 고객 ID, 제품 코드 등

• 장점: 기존 시스템과의 연동이 쉬움

• 단점: 변경될 가능성, 중복 위험, 복잡한 조합 등으로 인해 DW에선 보통 Surrogate Key로 대체합니다.

• 한 엔티티를 일관되게 식별하기 위한 불변의 식별자입니다.

• Surrogate Key와 비슷하지만, SCD(Slowly Changing Dimension) 처리 등으로 속성이 바뀌더라도 ID는 유지됩니다.

• 보통 ETL이 할당하고 관리하며, supernatural key, persistent identifier라고도 부릅니다.

• 많이 사용하는 예제 (uuid)
• UUID는 전역에서 유일(unique)하므로, 여러 시스템을 통합할 때 고유 ID 보장에 매우 유리합니다.
• 특히, 고객 ID나 글로벌 식별자처럼 시간이 지나도 변하지 않아야 하는 key를 만들기에 적합합니다.
• 다만 16바이트라는 용량으로 인해, 스토리지 부담이 존재합니다.

Hash 기반 Surrogate Key (dbt 권장 방식)

select
  {{ dbt_utils.generate_surrogate_key(['user_id', 'event_date']) }} as user_event_sk,
  ...
from {{ ref('stg_events') }}

• 같은 입력은 항상 같은 출력을 생성 → 멱등성 보장

• 플랫폼에 관계없이 사용 가능 (Snowflake, BigQuery, Redshift 등)

• SCD Type 2 등의 변경 추적에도 강력

• dbt의 철학인 재현 가능성(idempotency)과 잘 맞음

md5(concat_ws('||', col1, col2, ...))

MIISK 방식 (정수형 순차 surrogate key)

version: 2

models:
  - name: dim_customers
    description: 고객 차원 테이블
    config:
      meta:
        surrogate_key: true

create sequence if not exists {{ model.database }}.{{ model.schema }}.{{ model.name }}_seq;

select
  {{ increment_sequence() }} as customer_id,
  ...

• 외부 시스템(PK 등)과 연동 시 유리

• 정수형 조인 키로 성능 좋음

• 실패 시 key gap 발생

• 병렬 실행 시 역순·중복 가능성

• 뷰(view)에는 사용할 수 없음

• 정렬 보장 필요 (ORDER BY 필수)

• DAG 구성이 더 복잡해짐

💡 왜 dbt에서 Incremental Integer Surrogate Key가 어려운가?

1. ROW_NUMBER() 사용

ROW_NUMBER() OVER (ORDER BY something)

• 📌 문제점: 멱등성(idempotency)이 깨짐
• ROW_NUMBER()는 매번 실행할 때 정렬 기준이 바뀌면 같은 데이터라도 다른 surrogate key를 생성해요.
• 예를 들어, 어제는 A가 1번, B가 2번이었다면,
오늘은 B가 1번, A가 2번이 될 수도 있어요.
• → 이렇게 되면 downstream 모델에서 조인 불일치, SCD 실패, 이력 꼬임 등이 발생할 수 있어요.
❗ dbt는 모든 모델 실행 결과가 재실행해도 동일해야(idempotent)

1. SEQUENCE.NEXTVAL 사용 (ex: Snowflake)

SELECT my_schema.my_table_seq.NEXTVAL

1. IDENTITY 컬럼 사용

CREATE TABLE my_table (
    id INT AUTOINCREMENT PRIMARY KEY,
    ...
)

📌 문제점
• dbt와 충돌
• dbt는 CREATE OR REPLACE 전략을 자주 씀 (materialized='table')
• 하지만 IDENTITY 컬럼은:
• 테이블이 재생성될 때마다 초기화됨
• 즉, 1부터 다시 시작 → 기존 key와 불일치 발생
→ Snowflake는 IDENTITY 를 사용하는 테이블을 REPLACE 할 수 없음
• 즉, dbt의 기본 동작과 상충됨
• incremental table로 써도, append-only 구조가 아니면 관리가 복잡해짐

키 조합 vs Surrogate Key에 대해서

Snowflake SEQUENCE 기반 Surrogate Key 자동화 매크로 (MIISK 방식)

• Snowflake의 SEQUENCE 객체를 이용하여 정수형 surrogate key 자동 생성

┌───────────────────────┐
│ dbt_project.yml       │
│ meta.surrogate_key: T │
└────────┬──────────────┘
         ▼
┌────────────────────────────┐
│ macros/generate_sequences.sql │  ← on-run-start
└────────────────────────────┘
         ▼
┌───────────────────────────┐
│ 모델 내 {{ increment_sequence() }} 사용 │
└───────────────────────────┘

{% macro generate_sequences() %}
  {% if execute %}
    {% set models = graph.nodes.values() | selectattr('resource_type', 'eq', 'model') %}
    {% set sk_models = [] %}
    {% for model in models %}
      {% if model.config.meta.surrogate_key %}
        {% do sk_models.append(model) %}
      {% endif %}
    {% endfor %}
  {% endif %}

  {% for model in sk_models %}
    {% if flags.FULL_REFRESH or model.config.materialized == 'table' %}
      create or replace sequence {{ model.database }}.{{ model.schema }}.{{ model.name }}_seq;
    {% else %}
      create sequence if not exists {{ model.database }}.{{ model.schema }}.{{ model.name }}_seq;
    {% endif %}
  {% endfor %}
{% endmacro %}

{% macro increment_sequence() %}
  {{ this.name }}_seq.nextval
{% endmacro %}

select
  {{ increment_sequence() }} as customer_id,
  first_name,
  last_name
from source_customers

해시 기반 Surrogate Key (dbt_utils 활용)

• 여러 column을 조합한 해시 기반 surrogate key 생성

• 플랫폼 독립적, 멱등성(idempotent) 보장

┌──────────────────────────────┐
│ dbt_utils.generate_surrogate_key() │
└──────────────────────────────┘
           │
           ▼
┌──────────────────────────────┐
│ md5(concat_ws('||', col1, col2, ...)) │
└──────────────────────────────┘

select
  {{ dbt_utils.generate_surrogate_key(['user_id', 'event_date']) }} as user_event_sk,
  user_id,
  event_date,
  ...
from {{ ref('stg_events') }}

md5(cast(user_id as string) || '||' || cast(event_date as string))

SCD Type 2 적용 예시 (surrogate key + hash 활용)

• 변경 이력을 저장하기 위한 SCD Type 2 전략

• surrogate_key, is_current, start_date, end_date 포함

Input (stg_customers)
┌────────┬───────────┐
│ cust_id│ name      │
├────────┼───────────┤
│ 101    │ Alice     │
│ 101    │ Alice Kim │ ← 변경
└────────┴───────────┘

SCD 적용 후 (dim_customers)
┌─────────────┬───────────┬────────────┬────────────┬────────────┐
│ surrogate_sk│ cust_id   │ name       │ start_date │ end_date   │
├─────────────┼───────────┼────────────┼────────────┼────────────┤
│ 1           │ 101       │ Alice      │ 2022-01-01 │ 2023-06-01 │
│ 2           │ 101       │ Alice Kim  │ 2023-06-02 │ 9999-12-31 │
└─────────────┴───────────┴────────────┴────────────┴────────────┘

{{ config(
    materialized='incremental',
    unique_key='surrogate_sk'
) }}

with source as (
  select *,
    {{ dbt_utils.generate_surrogate_key(['cust_id', 'name']) }} as natural_sk
  from {{ ref('stg_customers') }}
),

deduped as (
  select *,
    row_number() over (partition by cust_id order by updated_at desc) as row_num
  from source
)

select
  {{ dbt_utils.generate_surrogate_key(['cust_id', 'name']) }} as surrogate_sk,
  cust_id,
  name,
  current_timestamp() as start_date,
  '9999-12-31' as end_date,
  true as is_current
from deduped
where row_num = 1

최종 요약

dbt에서는 기본적으로 해시 기반 surrogate key 생성 방식을 사용하며, 이는 멱등성, 자동화, 유지보수 관점에서 가장 권장되는 방식입니다. Snowflake SEQUENCE 기반의 MIISK 방식도 가능하지만, 운영/성능/순서 보장이 필요한 상황이 아니라면 일반적으로는 해시 방식이 더 적합합니다.

참고