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 방식도 가능하지만, 운영/성능/순서 보장이 필요한 상황이 아니라면 일반적으로는 해시 방식이 더 적합합니다.

참고

Surrogate keys in dbt: Integers or hashes? | dbt Developer Blog

Surrogate keys in dbt: Integers or hashes? | dbt Developer Blog

Wondering how to build a data model with surrogate keys? Dave Connors walks you through two strategies.

RedditSurrogate Key and Natural Key

Surrogate Key and Natural Key

28 votes, 18 comments. From my understanding, surrogate keys should be a meaningless unique integer and natural keys should be business unique value…

Reddit