토스 SLASH 24 리뷰: 토스 증권의 DW 설계 및 운영에서 배운 점 (AU 파이프라인 개선 사례)

들어가며

 작년부터 팀에서는 각 서비스의 주요 지표를 직접 생성하기 시작했습니다. 기존에는 마케팅, 사업부 등 각 부서의 요청에 따라 애드혹(Ad-hoc) 방식으로 데이터를 추출하거나 대시보드를 제공했지만, 현재 팀의 목표는 이에 더해 선제적으로 주요 지표를 제공하여 더 다양한 의사결정을 지원하는 것입니다.

 

 이러한 변화 속에서 다른 화사들은 DW(Data Warehouse)를 어떻게 설계하고 운영하는지에 대한 궁금증이 많았습니다. 그러다 토스 SLASH 24의 전천후 데이터 분석을 위한 DW 설계 및 운영하기 발표를 접하게 되었는데요, 이번 글에서는 발표 내용을 정리하고 팀에 어떻게 적용할 수 있을지 생각해 본 내용을 적어보겠습니다.

 

 

 

 

토스 증권의 AU 파이프라인

토스 증권에서는 다양한 패턴(방문 일자, 주기, 유저 타입(이탈, 복귀 등))의 요청에 대응하기 위해 Active User(AU, 활성 유저) 테이블을 설계하여 운영하고 있습니다.

• 특정 지표를 설정하고 act_type이라는 명칭으로 설정 (예) 증권앱 방문, 매매 유저, 매매 유저-해외, 종목상세 방문)
• 지표마다 실버 레이어에서 팩트 테이블을 구성하고 최종적으로 au_list(_*) 테이블에 일, 주, 월 단위 등으로 집계   

 

기존 AU 테이블의 문제점과 해결책

1. 네이밍 컨벤션의 부재

• 기존 네이밍 컨벤션이 없어 일관성이 부족(예) au_list 테이블의 경우 일단위 데이터인지 명확히 확인 불가)
• 해결책: 테이블의 명칭을 [테이블 구분]_[액트 타입]__[집계 기준]__[주기]로 명확하게 설정
  • 테이블 구분의 경우 팩트 테이블(f_), 디멘젼 테이블(d_), 여러 데이터를 담고 있는 마트 테이블(m_)로 구분
  • 집계 기준의 경우 GROUP BY절에 들어가는 컬럼으로 2개의 언더바(__)를 사용

 

2. 지표 별 특수 지표 포함 불가

• 기존 테이블 특성 상 모든 act_type에 적용되는 공통 지표만 적재(예) 거래 데이터의 경우 금액 관련 데이터가 필요하나 반영 불가)
• 해결책: 액트 타입별로 골드레이어를 만들어 관리해 확장 가능한 설계

 

3. 이탈 / 복귀 관련 집계 불가

• 해결책: 유저 이탈 및 복귀 정보를 볼 수 있도록 활성화된 일자를 기준으로 다양한 분석 지표를 담은 테이블 추가
  • 신규 여부, 마지막 활성 일자, N일 이탈 여부, N일 복귀 여부 등을 분석할 수 있도록 설계 

 

결론적으로 여러 액트 타입에 대해 아래와 같은 테이블이 생성되게 됩니다.

• 각 지표의 특성을 담은 골드 레이어 테이블을 생성하고 (f_[act_type]_ukey_daily)
• 이탈 / 복귀 정보를 볼 수 있는 테이블을 추가 (f_[act_type]_ukey_detail_daily)
• 모든 지표를 다양한 주기로 집계(주간, 월간 등)하여 통합 (m_액트타입_모음_ukey_daily)

 

발표 자료

 

---

 

AU 테이블 활용

이러한 설계를 활용하면, 기존 요청을 단순화된 SQL로 제공하고 확장성 있는 DW를 구축할 수 있습니다. 발표에서 나온 활용 예시는 다음과 같습니다. 

 

• 기간별 활성 유저 수 조회

-- 1) 2024-01-01부터 2024-01-31까지 주식매매 일간 유저 수
SELECT act_date, count(*) AS DAU
FROM f_stk_log__ukey__daily
WHERE act_date >= '2024-01-01'
  AND act_date < '2024-02-01'
GROUP BY 1

-- 2) 2024-01-01부터 2024-01-31까지 커뮤니티 글쓴 일간 유저 수
SELECT act_date, count(*) AS DAU
FROM f_comm_record__ukey__daily
WHERE act_date >= '2024-01-01'
  AND act_date < '2024-02-01'
GROUP BY 1

 

 

• 신규 / 이탈 / 복귀 유저 조회

-- 1) 2024-01-01부터 2024-01-31까지 일간 신규 매매자 수
SELECT act_date, count(*) AS user_cnt
FROM f_stk_log__ukey__detailed_daily
WHERE act_date >= '2024-01-01'
  AND act_date < '2024-02-01'
  AND seq = 1
GROUP BY 1

-- 2) 2024-01-01부터 2024-01-31까지 30일 이탈 후 복귀 매매자 수
SELECT act_date, count(*) AS user_cnt
FROM f_stk_log__ukey__detailed_daily
WHERE act_date >= '2024-01-01'
  AND act_date < '2024-02-01'
  AND retain_days >= 30
GROUP BY 1

 

 

• 퍼널 분석 시 마트 테이블 조회

SELECT
  act_date
  , SUM(CASE WHEN act_type = 'se_visit' THEN 1 ELSE 0 END) AS securities_visit_user_cnt
  , SUM(CASE WHEN act_type = 'stk_visit' THEN 1 ELSE 0 END) AS stock_visit_user_cnt
  , SUM(CASE WHEN act_type = 'stk_log' THEN 1 ELSE 0 END) AS stock_transaction_user_cnt
FROM m_액트타입_모음__ukey__daily
WHERE act_type IN (
  'se_visit' -- 증권 방문
  , 'stk_visit' -- 종목 상세 방문
  , 'stk_log' -- 주식 매매
)
GROUP BY 1

 

 

---

 

추가 고려 사항

발표 중 파이프라인에서 추가적으로 고려된 사항은 다음과 같습니다.  

1. 재활용성: 최소한의 파이썬 스크립트로 여러 개의 SQL 코드를 단순하게 처리하고 집계
2. 작업 병렬도 최적화: 연관있는 지표는 순차적으로 실행해 리소스 최적화 (예) 종목 상세 관련 지표는 통합, US, KR별로 집계하는데, 통합 테이블을 우선 생성하고 여기에 국가 코드를 추가해 하위 지표 생성)
3. 백필 최적화: Airflow Variable을 활용해 쉽게 백필을 할 수 있도록 구성하고, split_date_unit을 사용해 긴 기간의 백필도 메모리 이슈 없이 실행하도록 설정

 

• 팀 내부에서 빠르게 지표를 생성하다보니 예시와 같이 순차적으로 실행할 수 있는 지표도 같은 소스 테이블을 2번씩 스캔하는 경우가 있는데 이 부분을 f_[act_type]_ukey_daily와 같은 테이블로 개선할 수 있겠다는 생각이 들었습니다.
• 또한 백필의 경우에도 집계 단위가 다양해지면서 단순화된 DAG의 필요성을 느꼈는데 airflow variable 기능으로 설정값을 컨트롤하는 방법을 도입해 봐야겠습니다.

 

---

 

✔️ 실습해 보기

 AU 발표에서 인상 깊었던 f_[act_type]_ukey_detail_daily 테이블을 직접 생성해 보았습니다. 사내에서는 주로 서비스별로 데이터를 집계해 service_id, user_id를 추가했습니다(데이터는 쿼리로 생성한 무작위 데이터입니다).
 

-- 사용자별 활성화 이벤트 데이터 추출
WITH CTE_BASE AS (
  SELECT 
    DISTINCT
      service_id,
      user_id,
      DATE(created_at) AS activation_date,
  FROM test.user_log 
),
-- 사용자별 활성화 이벤트에 대한 세부 정보 계산
CTE_BASE_DETAIL AS (
  SELECT
    service_id,
    user_id,
    activation_date,
    seq, -- 활성 순서
    prev_activation_date, -- 이전 활성 날짜
    DATE_DIFF(activation_date, prev_activation_date, DAY) - 1 AS  retain_period, -- 유지 기간 
    next_activation_date, -- 다음 활성 날짜
    DATE_DIFF(IFNULL(DATE_SUB(next_activation_date, INTERVAL 1 DAY), i_stats_date), activation_date, DAY) AS churn_period, -- 이탈 기간
    MAX(seq) OVER(PARTITION BY service_id, user_id) AS last_seq
  FROM (
    SELECT 
      service_id,
      user_id,
      activation_date,
      ROW_NUMBER() OVER(PARTITION BY service_id, user_id ORDER BY activation_date) AS seq,
      LAG(activation_date) OVER(PARTITION BY service_id, user_id ORDER BY activation_date) AS prev_activation_date,
      LEAD(activation_date) OVER(PARTITION BY service_id, user_id ORDER BY activation_date) AS next_activation_date,
    FROM CTE_BASE
  )
)
SELECT
  service_id,
  user_id,
  activation_date,
  seq,
  prev_activation_date,
  retain_period,
  next_activation_date,
  churn_period,
  CASE 
    WHEN seq=last_seq THEN 1 ELSE 0 
  END AS is_last 
FROM CTE_BASE_DETAIL

 

조회 결과

 

---

 

마치며 

 토스 증권의 DW 설계 사례를 보며 팀에서도 적용할 수 있는 여러 가지 인사이트를 얻을 수 있었습니다. 다른 회사의 네이밍 컨벤션 정립, 팩트 테이블 설계, 파이프라인 최적화 과정은 쉽게 접할 수 없는 내용이라 도움이 많이 되었어요(SLASH 행사에 꼭 참여해보고 싶어졌습니다!).

 앞으로 팀 지표 파이프라인을 고도화해 사내의 많은 사람들이 쉽게 데이터를 활용할 수 있도록 기여하고 싶습니다. 발표를 준비해 주신 토스 구성원 분들과 데이터 웨어하우스 팀 리더님께 감사를 전하며 그럼 다음 글로 돌아오겠습니다 ☘️