BigQuery SCD Type 2

유저 속성 하나를 판정하는 로직을 쿼리마다 복붙해서 썼어요. 로직을 손볼 때마다 여러 쿼리를 찾아 고쳐야 해서 함수 하나로 합쳤고, 로직이 한 곳에 모이니 깔끔해 보였습니다.

문제는 이 함수가 “지금” 값만 본다는 점이었어요. 이 함수로 과거 데이터를 백필하자 예전 기록까지 백필을 실행한 날의 값으로 전부 덮어써졌습니다. 백필은 지난 날짜의 데이터를 나중에 다시 계산해서 채워 넣는 작업이에요.

원인을 찾기까지 시간이 걸렸습니다. 다른 기준으로 숫자를 대조해 보고 나서야 이상하다는 사실을 알았고, 원인을 알고 보니 데이터 엔지니어링에서 이미 이름까지 붙은 문제였어요.

함수 하나로 합친 대가

BigQuery에서는 이렇게 직접 만든 함수를 UDF(User-Defined Function, 사용자 정의 함수)라고 부릅니다. 제가 만든 UDF에는 기준 시점이라는 개념이 없었어요. 호출하는 순간의 유저 테이블을 조회해서 값을 돌려줄 뿐이었습니다.

백필 쿼리는 과거 날짜마다 이 함수를 다시 호출하는데 함수가 시점을 가리지 않으니 2023년을 백필하든 2024년을 백필하든 결과가 똑같았어요. 어느 날짜를 채우든 백필을 실행한 날의 값이 들어갔습니다.

보통 버그는 티가 납니다. 스키마가 안 맞으면 쿼리가 실패하고 없는 컬럼을 참조하면 에러가 떠서 실행하는 순간 바로 알아채요.

이번 문제에서는 쿼리가 완벽하게 성공했고 숫자도 그럴듯한 범위로 나왔어요. 로그에도 흔적이 남지 않았습니다. 속성이 바뀐 유저가 적으면 티가 잘 안 나지만 이 속성 기준으로 집계하는 지표는 조용히 몇 %씩 어긋나요. 다른 지표와 우연히 비교해 보기 전까지 이 숫자는 그대로 보고서에 쓰이고 의사결정에 반영됩니다. 소프트웨어 엔지니어링에서는 이런 실패를 “silent failure(조용한 실패)“라고 불러요. 다른 숫자를 보다가 우연히 이상함을 느끼는 방법 말고는 발견할 길이 없어서예요.

SCD: 이미 이름이 붙은 문제

데이터 엔지니어링에서는 이 문제를 **Slowly Changing Dimension(SCD)**이라는 틀로 다뤄요. 시간에 따라 천천히 바뀌는 속성을 어떻게 저장할지 정리한 표준 분류이고, 유저 등급이나 구독 상태, 활성 여부 같은 값이 여기에 해당합니다.

Type 1, 단순 덮어쓰기

가장 단순한 방식은 값이 바뀌면 그 자리에서 덮어쓰기예요. 테이블이라면 UPDATE로 덮어쓰고 함수라면 호출할 때마다 “지금” 값을 계산해서 돌려줍니다.

user_id | status     | updated_at
--------|------------|------------
u001    | active     | 2026-06-01

이 유저의 상태가 6월 15일에 바뀌면 행은 이렇게 바뀝니다.

user_id | status     | updated_at
--------|------------|------------
u001    | inactive   | 2026-06-15

“6월 1일엔 active였다”는 기록은 이제 어디에도 남지 않아요. updated_at은 마지막으로 바뀐 시점만 알려 주고 그 이전 값은 알려 주지 않습니다.

제가 쓰던 함수가 정확히 이 구조였어요. 테이블 대신 함수라는 차이만 빼면 성격이 똑같아서 호출할 때마다 “지금” 값만 계산해서 돌려줬고, 과거 시점의 값이라는 개념이 함수 안에 없었습니다.

Type 2, 새 행 추가로 이력 보존

Type 2는 값이 바뀔 때 기존 행을 지우지 않고 새 행을 추가해요. 각 행에는 이 값이 언제부터 언제까지 유효했는지를 같이 기록합니다.

user_id | status     | valid_from  | valid_to    | is_current
--------|------------|-------------|-------------|------------
u001    | active     | 2026-01-10  | 2026-06-15  | false
u001    | inactive   | 2026-06-15  | NULL        | true

이제 “6월 1일엔 어떤 상태였나”를 물으면 valid_from과 valid_to 사이에 6월 1일이 들어가는 행을 찾으면 돼요. 첫 번째 행이 조건에 맞으니 답은 active입니다. 과거 행을 지우지 않았으니 어느 날짜든 다시 재현돼요.

컬럼 구성은 보통 이렇습니다.

• user_id: 누구인지 나타내는 자연키. 자연키는 업무에서 원래 쓰던 식별자를 그대로 키로 쓴 값이에요.
• status: 바뀌는 값 자체
• valid_from / valid_to: 이 값이 유효했던 기간
• is_current: 지금 유효한 행인지 (조회 편의용)

값이 바뀌면 두 작업이 함께 일어나요. 기존 행의 valid_to를 바뀐 시점으로 마감하고 그 시점부터 시작하는 새 행을 추가합니다. 이 패턴을 “expire-and-insert”라고 불러요.

변형으로는 Type 3(직전 값 하나만 컬럼으로 저장)과 Type 4(현재 상태 테이블은 그대로 두고 이력만 별도 테이블에 쌓기)가 대표적이에요. Type 4는 Type 2와 함께 실무에서 가장 많이 쓰는 방식으로, “지금 상태 조회”는 빠른 메인 테이블이 맡고 “과거 재현”은 이력 테이블이 맡도록 나눕니다.

as-of join으로 특정 시점의 값 가져오기

Type 2 이력 테이블을 만들어 두면 특정 시점에 유효했던 값을 조인으로 가져와요.

SELECT
  a.user_id,
  a.target_date,
  h.status
FROM 분석_대상_테이블 a
JOIN 상태_이력_테이블 h
  ON a.user_id = h.user_id
  AND a.target_date >= h.valid_from
  AND a.target_date < COALESCE(h.valid_to, '9999-12-31')

target_date가 valid_from과 valid_to 사이에 들어가는 행만 고르는 조인이에요. 이 패턴을 “as-of join” 또는 “point-in-time join”이라고 부릅니다.

경계 조건에서 실수하기 쉬워요. BETWEEN처럼 양쪽 끝을 모두 포함하면 값이 바뀐 바로 그날 두 행이 동시에 매치돼서 결과 행이 두 배로 늘어납니다. SUM이나 COUNT를 같이 쓰는 쿼리라면 합계도 그대로 두 배가 돼요. 관례적으로 시작은 >=(포함), 끝은 <(미포함)로 짜는 이유입니다. valid_to가 NULL인 “지금도 유효한” 행에는 COALESCE로 먼 미래 날짜를 채워 줘야 해요. NULL과 비교하면 결과가 참이 되지 않아서 이 행이 조인에서 조용히 빠집니다.

이미 오염된 데이터는 하류 테이블로 맞췄다

이론대로라면 이력 테이블을 만들고 as-of join으로 다시 백필해야 했어요. 저는 더 빠른 길로 복구했습니다. 데이터 마트(분석용으로 미리 가공해 둔 테이블)를 다시 가공해서 쓰는 하류 테이블에 시점별로 정확한 값이 그대로 남아서, 함수로 다시 백필하는 대신 그 하류 테이블을 기준으로 정합성을 맞췄어요. 이력 테이블을 새로 만드는 쪽보다 빨랐습니다.

MERGE로 매일 갱신하는 이력 테이블

상태 이력 테이블을 매일 갱신하려면 MERGE(또는 UPDATE + INSERT 두 단계)로 기존 행 마감과 새 행 추가를 한 번에 처리합니다. 값이 안 바뀐 유저까지 매일 새 행을 추가하면 테이블만 커지니까 값이 실제로 바뀐 유저만 골라내요. 이력 테이블은 시간이 지날수록 계속 커져서 보통 valid_from으로 파티셔닝하고 user_id로 클러스터링해 조회 성능을 관리합니다. 파티셔닝은 테이블을 날짜 같은 기준으로 쪼개 저장하는 방식이고, 클러스터링은 같은 값끼리 모아 저장해서 필요한 부분만 읽게 하는 방식이에요.

BigQuery의 Time Travel 기능과 헷갈리기 쉬워요. FOR SYSTEM_TIME AS OF로 과거 시점의 테이블을 조회하거나 실수로 지운 테이블을 되살리는 기능인데, SCD Type 2와는 완전히 다른 기능입니다. Time Travel은 보관 기간이 기본 7일로 짧아서 방금 실수로 지운 데이터를 되돌리는 사고 복구용으로 써요. “6개월 전 이 유저 상태가 뭐였는지” 같은 장기 이력 조회에는 맞지 않으니 장기 이력이 필요하면 Type 2 같은 이력 관리를 따로 해야 합니다.

다시 만들지 못하는 과거

상태 변경 로그를 지금부터 남기기 시작하면 그 뒤의 이력은 완전하게 보존돼요. 전환 시점 이전의 과거는 저절로 생기지 않습니다. Type 1 방식은 값이 바뀔 때마다 이전 값을 지워 왔기 때문에 이전 값의 기록이 어디에도 남지 않아요. 이미 사라진 과거는 나중에 만들어 내지 못합니다.

로직을 함수 하나로 합치면서 정리가 끝났다고 생각했는데 “지금 값만 본다”는 성격은 그대로였어요. 요즘은 이렇게 확인합니다.

• 새 함수를 쓸 때는 함수 정의를 열어서 날짜를 인자로 받는지부터 봐요. 날짜를 받지 않는 함수는 항상 “지금” 값만 참조한다고 보고 씁니다.
• 함수 안에서 “지금” 상태를 담은 테이블을 그대로 조회하는지도 확인해요. 그렇다면 시점 개념이 없는 함수예요.
• 테이블을 새로 설계할 때는 한 유저(엔티티)당 행이 여러 개 나오는 구조인지부터 봐요. 한 행만 두고 UPDATE로 덮어쓰는 구조라면 과거를 물어볼 일이 생기기 전에 이력 버전부터 만듭니다.
• 백필 로직을 짤 때는 같은 로직을 서로 다른 두 날짜로 먼저 돌려 봐요. 결과가 똑같으면 그대로 쓰지 않습니다.
• 값이 바뀌는 속성을 다룰 때는 valid_from·valid_to 컬럼을 갖춘 이력 테이블부터 찾아요. 없으면 하류 테이블에 시점별로 정확한 값이 남았는지부터 확인합니다. 이번에도 이 방법으로 정합성을 맞췄어요.