BigQuery UDF vs TVF: as-of join을 함수로 감쌀 때 스칼라 함수가 느려지는 이유

함수 하나를 만들었는데, 그걸 데이터 몇 개에 써볼 땐 멀쩡했어요. 근데 전체 데이터(100만 행이 넘는)에 그대로 적용했더니 눈에 띄게 느려졌습니다.

원인은 [함수를 만드는 방식] 자체에 있었어요. 값 하나만 돌려주는 함수와, 표 전체를 돌려주는 함수. 이 둘을 구분하지 않고 쓴 게 문제였습니다.

배경을 짧게 설명하면, [그 시점엔 어떤 값이었는지]를 정확히 찾아내는 조인(join)을 쿼리마다 매번 손으로 다시 쓰고 있었어요. 그러다 보니 세부 조건 하나를 빠뜨리는 실수가 반복됐습니다. 그래서 이걸 함수 하나로 감싸고 싶었어요.

첫 시도: 값 하나만 돌려주는 함수 (UDF)

BigQuery에서 흔히 만드는 함수(UDF, User-Defined Function)는 값을 하나만 돌려줍니다. 유저 한 명, 날짜 하나를 넣으면 [그날 그 사람 상태] 딱 하나를 돌려주는 함수를 상상해보면 됩니다. 첫 시도는 이 함수 안에 조인 로직을 통째로 집어넣는 것이었어요.

CREATE OR REPLACE FUNCTION mart.get_status(p_user_id STRING, p_target_date DATE)
RETURNS STRING
AS ((
  SELECT status
  FROM `상태_이력_테이블`
  WHERE user_id = p_user_id
    AND p_target_date >= valid_from
    AND p_target_date < COALESCE(valid_to, DATE '9999-12-31')
));

(코드를 몰라도 괜찮습니다. 핵심은 [유저 한 명 + 날짜 하나를 넣으면 상태값 하나가 나오는 함수를 만들었다]는 것뿐이에요.) 문법 오류도 없었고, 유저 한두 명으로 테스트했을 땐 결과도 정확했습니다.

왜 느려졌나: 함수가 행마다 처음부터 다시 실행되는 구조였다

문제는 이 함수를 전체 유저 목록에 하나씩 적용하면서 생겼습니다.

SELECT
  a.user_id,
  a.target_date,
  mart.get_status(a.user_id, a.target_date) AS status
FROM 분석_대상_테이블 a

이렇게 쓰면, 함수 안의 조회 로직이 분석_대상_테이블의 행 하나하나에 대해 매번 처음부터 다시 실행됩니다. 유저 몇백 명이면 체감이 안 되는데, 100만 행 이상(저희 팀에서 쓰는 마트 테이블 중엔 몇천만 행짜리도 있습니다)으로 가면 눈에 띄게 느려졌어요.

비유하자면, 원래 방식(일반 JOIN)은 [두 명단을 나란히 펼쳐놓고 한 번에 맞춰보는] 방식이라 효율적인데, 이 함수는 [명단 한 줄 볼 때마다 다른 서랍을 처음부터 다시 뒤지는] 방식이었던 거예요. 행이 많아질수록 이 차이가 그대로 실행 시간으로 쌓입니다.

해결책: 표 전체를 돌려주는 함수 (TVF)

그래서 방식을 바꿨습니다. 유저 한 명씩 [이 사람 상태 뭐예요?]라고 묻는 대신, [이 날짜 기준 전체 유저 상태표를 통째로 주세요]로 질문 자체를 바꾼 거예요. BigQuery에서는 값 하나가 아니라 테이블 자체를 돌려주는 함수를 TVF(Table Function)라고 부릅니다.

CREATE OR REPLACE TABLE FUNCTION mart.status_as_of(p_target_date DATE)
AS (
  SELECT
    user_id,
    status,
    valid_from,
    valid_to
  FROM `상태_이력_테이블`
  WHERE p_target_date >= valid_from
    AND p_target_date < COALESCE(valid_to, DATE '9999-12-31')
);

쓰는 쪽은 이렇게 일반 JOIN처럼 씁니다.

SELECT
  a.user_id,
  s.status
FROM 분석_대상_테이블 a
JOIN mart.status_as_of(DATE '2026-06-01') s
  ON a.user_id = s.user_id

status_as_of(2026-06-01)가 그 날짜 기준 전체 유저 상태표를 한 번에 통째로 돌려주고, 그 표를 분석_대상_테이블과 그냥 JOIN으로 붙이는 구조예요. 함수 호출은 딱 한 번뿐이고, 나머지는 평범한 JOIN이라 앞서 겪은 [행마다 반복 실행] 문제가 없습니다.

협업 과정에서 좋아진 점도 있었어요. 조인 조건 안의 세부 규칙(예: [언제까지가 유효한 값인지] 계산하는 방식)이 함수 안 딱 한 곳에만 있으면 됩니다. 다른 PM이나 마케터가 이 함수를 가져다 쓸 땐 그 세부 규칙을 몰라도, 날짜만 넣으면 항상 정확한 결과를 받습니다.

아직 안 풀린 부분: 유저마다 기준 날짜가 다를 때

이 방식은 [한 시점 기준 전체 스냅샷]이 필요할 때, 특히 날짜별로 반복 호출하는 재계산 작업에는 잘 맞습니다. 하지만 한 쿼리 안에서 유저마다 서로 다른 기준 날짜를 물어야 하는 경우(예: 유저마다 본인 가입일 기준으로 계산해야 하는 경우)엔 이 방식 하나로는 풀리지 않아요. 이럴 땐 날짜별로 나눠서 따로 호출하거나, 처음 방식대로 조인 조건을 직접 쓰는 수밖에 없었습니다. 매끄럽게 푸는 방법은 아직 더 찾아보는 중입니다.

함수로 감쌀 때 지켜야 하는 것

• 값 하나만 필요하고 호출 빈도가 적다면 첫 번째 방식(UDF)으로도 충분합니다. 대량 데이터에, 특히 전체 목록에 하나씩 적용하는 경우라면 두 번째 방식(TVF)을 먼저 검토하세요.
• 어느 쪽이든, 함수 안에 [오늘 날짜]나 [지금 시각]을 직접 참조하게 만들면 안 됩니다. 기준 날짜는 항상 함수 밖에서 넣어줘야, 같은 날짜로 다시 물어봤을 때 항상 같은 답이 나옵니다.
• 함수로 감싸는 이유는 [같은 로직을 여기저기 복붙하지 않기 위해서]입니다. 딱 한 곳에서만 쓰는 조인이라면 굳이 함수로 만들 필요는 없어요.