DBT를 활용해 준실시간으로 클라이언트 Journey 파악하기

1. 들어가며

안녕하세요 Tech Foundation Service team 소속 Austin 입니다. Tech Foundation Service Team에서 사내 개발자분들의 생산성을 높이기 위해 테스팅, 모니터링, Ai 적용등 다양한 서비스를 제공하고 있습니다. 이번 포스팅에서 DBT를 활용해 DB 레이어에서 클라이언트의 발자취를 탐색할 수 있는 테이블을 구축한 경험을 공유드리고자 합니다.

Data_logs가 나온 배경

많은 개발자분들은 개발을 하거나 Test를 하거나 디버깅을할때 클라이언트의 액티비티를 자주 추적하곤합니다. PM들은 새로운 기능 출시와 다양한 활동에 클라이언트들의 액티비티가 어떻게 변경되는지 알고 싶어하죠. 다양한 방법이 있지만 DB의 데이터로 확인 하는 방법은 빠지지 않을 것입니다. 하지만 DB의 저장된 테이블들을 사용해 클라이언트의 액티비티를 추척하는 것은 여간 귀찮은 일이 아닙니다. 그 이유는 DB에 저장된 테이블들은 분석적 용도에 맞게 쿼리하기 위해서는 여러 테이블을 조인해야하기 때문입니다. 

개발자나 다른 사내 이해관계자들은 모든 테이블들을 전부 알 수는 없습니다. 더나아가 각 테이블별 컬럼이나 해당 row가 왜 생기는 건지 잘 알지 못할 수 있습니다. 이러한 불편함은 데이터 이용자들에게 많은 시간을 뺏고, 쉽게 데이터를 확인하지 못하게 하여 조직의 전반적인 생산성과 관찰가능성을 낮출 수 있습니다. 이를 해결하기 위해, Data warehouse와 DBT 그리고 Airflow를 활용하여 data_logs 라는 테이블을 만들어 보았습니다.

1. 데이터베이스 레이어에서 클라이언트의 발자취를 확인할 수 있는 도구가 없다. 

2. 만약 개인이 이 테이블을 만들기 위해서는, 많은 테이블들을 찾아야하고 매우 복잡한 쿼리를 작성하게 될 것이다.

3. 데이터 사용자들이 전부 매우 복잡한 쿼리를 작성하고 실행하면 쿼리비용과 시간이 많이 소모된다

2. What is data_logs table?

data_logs는 클라이언트가 서비스를 사용하며 만들어낸 발자취를 쉽게 따라갈 수 있도록 도와주는 테이블입니다. 하지만 서비스 DB의 모든 테이블이 client_id를 갖고 있지 않죠. 그래서 개발자분들이나 비즈니스이해관계자분들이 클라이언트의 data 발자취를 따라가기 위해서는 백엔드 코드를 까보거나 메타데이터 저장소를 뒤져보거나 소중한 동료의 시간을 뺏어야합니다. 만일 찾는 다고 해도 클라이언트의 journey를 파악하기 위해선 많은 테이블들을 조인 하고 복잡한 쿼리를 작성해야 할 것입니다. 개별적으로 이런 복잡한 쿼리를 하게 되는 것은 시간도 많이 소모되지만, 비용도 무시 못할 것입니다.

이러한 문제를 해결하기 위해 data_logs를 만들게 되었습니다. 클라이언트가 저희 회사의 서비스를 사용하며 각 비즈니스 기능별로 남긴 흔적들을 클라이언트 아이디로 묶어 시간 순서대로 클라이언트의 액티비티를 확인할 수 있습니다. 클라이언트 아이디뿐만 아니라, 클라이언트 journey를 추적하는데 필요한 추가적인 ID들로유저의 활동을 묶어 놓아 사내 데이터 사용자들은 쉽게 해당 클라이언트 혹은 대출 신청 ID (혹은 다른 아이디) 별로 쉽게 클라이언트의 액티비티들을 추적 관찰 할 수 있게 됩니다.

예시를 위해 임의로만든 테이블

테이블 구성:

• 좌측에 ID 그룹: 주요 ID들

• table_name: 해당 줄에 원천 테이블

• log_at: 해당 줄이 삽입된 시간 (Binlog_timestamp) 

• description: 해당줄의 문맥을 바탕으로한 요약 메세지

• pay_load: 해당줄의 전체 데이터

• binlog_filename_position: binlog의 로그 위치 값. 이것을 통해 같은 시간대에도 순서를 파악할 수 있습니다.

• dt: 시간 단위 파티션. 데이터 사용자는 파티션을 사용해 더 빠르게 쿼리 가능합니다.

“아, 대출심사 3번을 신청한 클라이언트가 누구지? 이 대출심사한 클라이언트는 어떤 테이블을 조인해야 알 수 있는건지 모르겠네”

“걱정하지마, data_logs를 보면 어떤 클라이언트가 해당 대출심사를 받았는지 알 수 있어! 클라이언트 1번이 신청한 것이였네!”

사용예시

이 테이블은 다양한 용도로 사용될 수 있습니다:

• 디버깅

• 모니터링

• 사용자 패턴 파악

• 테스트 케이스 생성

• User journy 에서 각 단계별 소요 시간 파악

• …

클라이언트 액티비티를 데이터베이스 레이어에서 트레이싱할 수 있다는 것은 많은 이점을 제공합니다. 특정 유저에게 오류가 발생했다면, 재빠르게 어느 지점에서 사건이 일어났는지 파악할 수 있습니다. 이를 디버깅하기 위해 어떤 테이블을 조인할지 시간을 낭비하지 않아도 됩니다.

또한, 사용자의 패턴을 파악하여 여러 곳에 활용할 수도 있습니다. 특정 패턴을 가진 유저의 수를 모니터링하거나, 각 단계별(예: 심사 단계별 소요 시간, 신청 후 대출까지 걸리는 시간 등)로 소요 시간을 확인하여 비즈니스 연속성을 점검할 수도 있습니다. 개발자 분들은 가장 빈번한 사용자 패턴을 바탕으로 테스트 케이스를 만들 수 있을 것이고, 기획자분들은 이를 토대로 병목 지점을 찾아내어 제품 발전을 위한 시작점이 될 수 있을 것입니다.

3. Data_logs 테이블 구축 방법

아키텍쳐

data_logs 테이블은 데이터베이스 레이어에서 클라이언트의 액티비티를 추적하는 테이블입니다. 사내 데이터 사용자들의 편의성을 위해 실시간에 가깝게 데이터를 사용할 수 있는 CDC(Change Data Capture) 테이블들을 사용하여 구축 하였습니다. CDC란 데이터베이스 내에서 발생하는 INSERT·UPDATE·DELETE 등 모든 변경 사항을 실시간 또는 준실시간으로 추출해 내보내는 기술입니다. 주로 DB의 Binlog를 받아 변경 이벤트들을 스트리밍형태로 전달합니다. 

CDC를 사용하는데에는 이점이 많습니다. 먼저, 실시간성으로 데이터를 사용할 수 있다는 점, 그리고 binlog_filename_position를 활용하여 같은 시간에 발생한 이벤트에도 순서를 파악할 수 있습니다.

이 raw 데이터를 활용하여 data_logs로 변환하는 모델링을 진행합니다. 이 모델링은 dbt(Data Build Tool)를 사용하여 수행하며, “준실시간”이라는 목표에 맞춰 incremental 모드로 Airflow 스케줄러에서 정기적으로 실행되도록 설정했습니다.

Raw data(CDC tables) -> data_logs 테이블로 변환/모델링 in DBT (Airflow에서 주기적으로 실행)-> Data warehous에 저장 -> 유저가 쿼리 가능!

DBT: 데이터 모델링

DBT(Data Build Tool)는 데이터 변환과 모델링을 자동화·관리하는 오픈소스 프레임워크입니다.SQL과 Jinja 템플릿을 활용해 데이터 모델링 작업을 정의하고 실행할 수 있습니다. DBT는 다양한 소프트웨어 엔지니어링 컨벤션을 포함하며, 모듈화, GitHub에서 코드로 관리, 테스트 자동화, 매크로등등을 포함하고 있습니다. 

DBT가 정착되기 전까지는 많은 데이터 사용자들의 노트북에 쿼리를 저장해두고 필요할때마다 사용하였습니다. 거대한 monolothic query들을 파악하고 관리하기도 힘들었고, 버전 관리도 안되었습니다. 또한 중복되는 테이블 스캔들이 불필요한 시간소모와 비용으로 이어졌습니다.

DBT를 사용함으로서, 각 쿼리들을 디커플링하여 함수화 해서 재사용성을 늘리고, 비즈니스 기능별로 나누고 레이어를 만들어 유지보수와 확장에 용이하도록 설정하였습니다.

data_logs table은 DBT에서 3가지 레이어를 거쳐서 만들어집니다. 먼저 Base layer에서 소스테이블들이 1차 전처리 과정을 거치고, Core layer에서 Base 테이블들은 각 팩트 테이블과 다이멘션 테이블로 구성하여 data_logs 형식에 맞게 transform합니다. 이때 클라이언트 ID같은 주요 ID가 없는 테이블들을 추가하기 위해서는 JOIN문이 많이 필요했습니다. JOIN 수가 늘어날수록 쿼리 성능이 저하되므로, 스타 스키마 구조를 적용했습니다. 마지막으로 액세스 레이어에서 data_logs 테이블이 완성되어 사내 데이터 사용자들이 사용할 수 있는 형태로 완성됩니다. 

How did I make?

테이블 선정방법

data_logs 테이블을 구축하기 위해 기존 자료를 살펴보고 사내 동료들과 소통하며 주요 테이블을 수집했습니다. 그러나 서비스가 발전하며 새로운 테이블이 생성되고, 제가 미처 파악하지 못한 테이블도 분명히 있었습니다. 특히 data_logs 테이블을 설계할 때 서비스 코드에 큰 변화가 있었습니다. 하루 사이에도 새로운 API가 추가되고, 기존 DB 테이블의 데이터 형식이 변경되기도 했습니다. 이러한 변화에 빠르게 대응하기 위해 안드로이드 스튜디오를 설치하여 개발 스테이지에서 직접 API를 호출해 보며, 클라이언트의 journey를 파악하기 위한 테이블이 뭐가 있을지 탐색하는 과정을 진행했습니다. 

1. 안드로이드 스튜디오에서 True Balance 앱 환경설정하고 앱 사용

2. 호출되는 API 확인

3. 서버 코드에서 해당 API를 처리하는 컨트롤러 파악

4. 메서드를 따라가며 INSERT/UPDATE 대상 DB 테이블 확인

5. data_logs 테이블에 반영

이 과정을 통해 비즈니스 도메인에 대한 이해도가 크게 높아졌고, 사용자 여정과 data_logs 간의 정합성을 확보했으며, data_logs table에 description 컬럼을 문맥에 맞게 작성할 수 있게 되었습니다.

모델링 최적화

가장 많은 시간을 할애한 부분은 DBT 모델링 Optimization 이었습니다. 물론 실시간 스트리밍 서비스를 사용할 수도 있겠지만, 요구사항과 관리 비용 측면에서 적합하지 않았습니다. data_logs 테이블은 저희 팀에서 관리하며, 실시간 처리가 필수가 아니기 때문에 스트리밍 파이프라인 도입이 꼭 필요하지 않았습니다. 또한 DBT 프레임워크는 개발자가 쉽게 관리할 수 있다는 장점이 있어, 이를 통해 높은 관리 생산성을 유지하면서 데이터 사용자에게 가치를 제공하고 최대한의 성능을 달성하는 것이 목표였습니다.

data_logs 테이블을 처음 만들 때는 스타 스키마도 적용하지 않은 채, 무거운 하나의 DBT 모델을 실행하면 실행 시간이 매우 오래 걸렸습니다. 중복적으로 소스 테이블을 스캔하고 중복되는 로직이 더욱 비효율적으로 만들었습니다. 

이를 해결하기 위해, 중복 참조되는 테이블들을 베이스 테이블로 분리하여 한번만 읽게하고, 스타스키마를 적용하여 JOIN의 스캔량을 줄였습니다. 중복된 쿼리 로직또한 macro를 사용하여 유지보수에 용이하게 하였습니다. 무거운 쿼리들을 분리하고 재사용시간을 늘리니, 스캔양도 줄고 dbt run 실행시에 병렬로 모델들을 실행할 수 있기 때문에 시간 단축이 되었습니다. 

두 번째로 적용한 것은 증분 전략입니다. data_logs 테이블을 구축할 때 참조하는 원천 테이블이 약 50개이며, 앞으로 더 늘어날 예정이므로 매번 전체 full refresh를 실행하는 것은 시간과 스캔량 면에서 비효율적이었습니다. 이를 해결하기 위해 각 레이어 별로 데이터 정합성과 속도등을 고려하여 증분 방식을 사용하여 설계했습니다. 

• Append: 증분할 데이터만 추가

• Insert_overwrite: 특정 파티션을 Drop한 뒤 재생성

이 방법을 통해 하루에 자주 작게 돌려 준실시간성을 확보하고, 스캔량 및 모델링시간도 많이 줄게 되었습니다. 

마지막으로 파티션 단위를 더욱 짧게 만들어 보았습니다. 처음 insert_overwrite로 설계한 증분 모델은 2일 전 날짜를 구하는 매크로를 사용하여 매 실행 시 2일 단위의 파티션을 삭제하고 새로 생성되도록 하였습니다. 

• 6/25 00:17분 dbt run 실행 6/24 과 6/25일의 파티션을 교체하여 생성

이를 통해 기존 CDC 테이블의 날짜 파티션을 쉽게 구축할 수 있었습니다. 하지만 단위를 더 줄여보고 싶어서, DBT 모델이 실행될 때 어떻게 동작하는지 이해할 필요가 있었습니다. 그래서 dbt.log에 찍힌 로그를 살펴보며 DBT가 어떻게 실행되는지 파악하였습니다.

Insert_overwrite 증분DBT model 실행시 DBT의 작동방식:

1. 컴파일

2. 쿼리 실행후 결과를 임시 테이블에 저장

3. 파티션 목록 조회

4. 증분 파티션 S3에서 삭제

5. 본테이블에 임시테이블에 있던 데이터들을 삽입

6. 임시 테이블 Drop/ Glue 정리

여기서 제가 시간을 줄일 수 있었던 부분은 2번 쿼리 실행 부분과 5번 임시 테이블 insert 부분이였습니다. 이를 위해 증분될 파티션의 스캔량을 줄이고 파티션 단위도 세분화할 필요가 있었습니다. 그래서 전처리 과정에서 시간 단위 파티션으로 DBT 모델을 생성하여, 실행 시마다 1시간 전까지의 파티션을 교체하는 방식으로 변경하였습니다.증분 작업을 개선하는 것뿐만 아니라, 적절한 파티션 프루닝(pruning)을 통해 스캔 양을 줄이도록 노력했습니다. 이렇게 설정하니 Row count가 6,000,000줄에서 약 200,000줄로(시간에 따라 편차는 있지만) 줄어드는 것을 확인했으며, 2번 과정에서는 약 40%, 5번 단계에서는 약 90% 감소하는 효과를 보았습니다.

빌드 속도와 스캔양을 줄이는 방법 요약

1. 각 테이블별 적절한 증분방식 선택

2. 적절한 threads 수와 retries 숫자 정하기

3. 파티션 푸르닝으로 효과적인 쿼리 작성하기

4. 중복 스캔하지 않기

4. 마무리 하며

이번 프로젝트를 진행하며 클라이언트의 여정을 파악하는 중요성과 사내 데이터 사용자의 이해, 그리고 데이터 엔지니어 분야에 대한 탐구할 수 있는 좋은 기회였습니다. 

직접 앱을 구동해 서버 코드를 들여다보며 데이터가 어떤 흐름으로 데이터베이스에 저장되는지 Sequence Diagram를 그려보며 파악했는데, 마치 미로를 푸는 것 같은 즐거움을 느꼈습니다. 또한 DBT와 DW, 그리고 Airflow까지 다양한 데이터 엔지니어링 분야를 탐구할 수 있었습니다. (도움을 주신 모든 분께 감사드리지만, 특히 데이터 엔지니어분들께 깊이 감사드립니다.) 

제가 만든 내부 툴이 동료들의 시간을 절약해 주는 모습을 지켜보는 것만큼 즐거운 일은 없을 것입니다. 앞으로도 Tech Foundation Service Team에서 Hero들의 시간을 아껴 줄 수 있도록 노력하겠습니다!