BigQuery SCD Type 2: "지금 값"만 보는 로직의 과거 재계산 함정

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

그런데 이 함수는 “지금” 값만 보는 함수였어요. 이 함수로 과거 데이터를 백필했더니, 백필을 실행한 시점의 값으로 예전 기록까지 전부 덮어써졌습니다.

원인을 찾는 데 시간이 좀 걸렸어요. 다른 기준으로 대조해보고 나서야 뭔가 이상하다는 걸 알았거든요. 그리고 원인을 알고 나니, 이게 그냥 제 실수가 아니라 데이터 엔지니어링에서 이미 이름까지 붙어 있는 유명한 문제라는 걸 알게 됐습니다. 오늘은 그 이야기를 정리해보려고 해요.

함수 통합의 대가, 조용히 오염된 과거 데이터

BigQuery에서는 이렇게 직접 만든 함수를 UDF(User-Defined Function, 사용자 정의 함수)라고 부릅니다. 그런데 제가 만든 함수 안에는 “언제 기준으로 봐야 하는지”에 대한 정보가 없었어요. 그냥 그 순간의 유저 테이블을 조회해서 값을 돌려줄 뿐이었습니다.

백필 쿼리는 과거 날짜 하나하나에 대해 이 함수를 다시 호출하는데, 함수가 시점을 안 가리다 보니 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, 새 행 추가로 이력 보존

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

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과의 비교 때문에 조인에서 조용히 빠지는 걸 막을 수 있어요.

백필 직후엔 숫자가 딱히 이상해 보이지 않았어요. 이 속성 기준 합계만 미묘하게 흔들리고 있었는데, 다른 기준으로 대조해보기 전까진 몰랐습니다.

다행히 이 마트를 가공해서 쓰는 하류 테이블에는 시점별로 정확한 값이 이미 남아 있었어요. 함수로 다시 백필하는 대신, 그 하류 테이블을 기준으로 정합성을 맞췄습니다. 이력 테이블을 새로 만드는 것보다 빨랐어요.

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가 있는 이력 테이블부터 찾아서 써요. 없으면, 하류 테이블에 이미 시점별로 정확한 값이 있는지부터 확인합니다. 제 경우엔 실제로 이 방법으로 정합성을 맞췄어요.