데이터에 신뢰성과 재사용성까지, Analytics Engineering with dbt

도입 배경과 문제 상황

(1) 데이터 인프라 구조

[다양한 원천 데이터] → [BigQuery] → [가공 테이블] → [집계 테이블] → [활용]
Plain Text
복사

(2) 기존 시스템의 장단점

장점:

•

스토리지 비용이 저렴

•

SQL로 쉽게 데이터 접근 가능

•

다양한 소스 데이터 저장 용이

단점:

•

데이터 검증 미흡

•

데이터 히스토리/오너십 파악 어려움

•

데이터 간 의존성 파악 어려움

예시 상황:

-- 기존의 복잡한 쿼리 예시
SELECT
  DATE_TRUNC(created_at, MONTH) as month,
  COUNT(DISTINCT user_id) as user_count,
  SUM(amount) as total_amount
FROM raw_data.transactions
WHERE status = 'completed'
GROUP BY 1
-- 이런 단순한 쿼리도 수백 줄로 구성되어 있었음
SQL
복사

dbt(data build tool) 도입

(1) 데이터 모델링 구조

  # dbt best practice
  ├── dbt_project.yml
  └── models
      ├── marts
      │   ├── core
      │   │   ├── core.md
      │   │   ├── core.yml
      │   │   ├── dim_customers.sql
      │   │   ├── fct_orders.sql
      │   │   └── intermediate
      │   │       ├── customer_orders__grouped.sql
      │   │       ├── customer_payments__grouped.sql
      │   │       ├── intermediate.yml
      │   │       └── order_payments__joined.sql
      │   ├── finance
      │   ├── marketing
      │   └── product
      └── staging


# 쏘카 커스텀

models/
├── mart/          # 비즈니스 지표 관련 모델
├── ods/           # 임시 데이터 저장소
└── staging/       # 기초 데이터 정제
Plain Text
복사

(2) ODS

ODS의 정의와 역할

•

EDW(Enterprise Data Warehouse)에서 사용되는 개념

•

운영계 데이터를 임시로 보관하는 저장소

•

Mart 테이블 생성 전 메인 로직을 저장

•

Mart에서는 ODS 레이어의 모델을 참조하여 사용

ODS 레이어 도입 목적

(1) 데이터 비교

•

기존 데이터(v1)와 새로운 데이터의 비교 용이

•

View로 모델 생성하여 유연성 확보

•

Mart에서 Table로 Materialize하여 특정 시점 기준 데이터 생성 가능

•

v1 데이터와 적재 시점 동일하게 맞추어 정확한 비교 가능

(2) 가독성 향상

•

레이어별 명확한 역할 구분:

◦

Staging: 원천 테이블 가공

◦

ODS: 메인 로직 저장

◦

Mart: 비즈니스 지표 생성

•

복잡한 로직을 ODS에 분산하여 저장

•

Mart는 단순한 SELECT 구문으로 구성 가능

(3) ODS 모델 예시:

dbt에서 복잡한 로직을 ODS에 분산하여 저장하는 방법

기본 디렉토리 구조

models/
├── staging/      # 기초 데이터 정제
├── ods/          # 복잡한 로직 분산 저장
└── mart/         # 최종 비즈니스 지표
Plain Text
복사

ii.

예시: 차량 예약 데이터 처리

(1) Staging 레이어 - 기본 데이터 정제

-- models/staging/reservations/stg_reservations.sql
{{ config(materialized='view') }}

SELECT
    reservation_id,
    vehicle_id,
    user_id,
    TIMESTAMP(start_at) as start_datetime,
    TIMESTAMP(end_at) as end_datetime,
    status,
    payment_amount
FROM {{ source('raw', 'reservations') }}
WHERE status IS NOT NULL
SQL
복사

(2) ODS 레이어 - 복잡한 로직 분산

-- models/ods/reservations/ods_daily_vehicle_usage.sql
{{ config(materialized='view') }}

WITH rental_hours AS (
    SELECT
        vehicle_id,
        DATE(start_datetime) as usage_date,
        -- 복잡한 시간 계산 로직
        TIMESTAMP_DIFF(end_datetime, start_datetime, HOUR) as rental_hours,
        -- 예약 중첩 체크 로직
        LAG(end_datetime) OVER (PARTITION BY vehicle_id ORDER BY start_datetime) as prev_end_time
    FROM {{ ref('stg_reservations') }}
    WHERE status = 'completed'
),

vehicle_availability AS (
    SELECT
        vehicle_id,
        usage_date,
        -- 차량 가용시간 계산 로직
        24 - SUM(rental_hours) as available_hours,
        -- 중첩 예약 체크
        COUNT(CASE WHEN start_datetime <= prev_end_time THEN 1 END) as overlapping_bookings
    FROM rental_hours
    GROUP BY 1, 2
)

SELECT * FROM vehicle_availability
SQL
복사

-- models/ods/reservations/ods_vehicle_efficiency.sql
{{ config(materialized='view') }}

WITH daily_revenue AS (
    SELECT
        vehicle_id,
        DATE(start_datetime) as revenue_date,
        -- 수익성 계산 로직
        SUM(payment_amount) as daily_revenue,
        COUNT(DISTINCT reservation_id) as booking_count
    FROM {{ ref('stg_reservations') }}
    WHERE status = 'completed'
    GROUP BY 1, 2
),

efficiency_metrics AS (
    SELECT
        v.vehicle_id,
        v.usage_date,
        v.available_hours,
        d.daily_revenue,
        -- 효율성 지표 계산
        SAFE_DIVIDE(d.daily_revenue, (24 - v.available_hours)) as revenue_per_used_hour
    FROM {{ ref('ods_daily_vehicle_usage') }} v
    LEFT JOIN daily_revenue d
        ON v.vehicle_id = d.vehicle_id
        AND v.usage_date = d.revenue_date
)

SELECT * FROM efficiency_metrics
SQL
복사

(3) Mart 레이어 - 단순화된 비즈니스 지표 집계

-- models/mart/vehicle_performance/fct_vehicle_daily_metrics.sql
{{ config(materialized='table') }}

SELECT
    vehicle_id,
    usage_date,
    available_hours,
    daily_revenue,
    revenue_per_used_hour,
    -- 간단한 비즈니스 로직만 추가
    CASE
        WHEN revenue_per_used_hour > 10000 THEN 'high'
        WHEN revenue_per_used_hour > 5000 THEN 'medium'
        ELSE 'low'
    END as efficiency_tier
FROM {{ ref('ods_vehicle_efficiency') }}
SQL
복사

iii.

ODS 레이어의 장점:

•

로직 분리

◦

복잡한 계산은 ODS에서 처리

◦

Mart는 간단한 SELECT 문으로 구성 가능

◦

코드 가독성 향상

•

재사용성

◦

동일한 ODS 뷰를 여러 Mart에서 참조 가능

◦

로직 중복 방지

•

유지보수 용이성

◦

로직 변경 시 ODS만 수정하면 됨

◦

영향도 파악이 쉬움

iv.

로직 분산 전략:

•

기능별 분리

◦

시간 계산 로직

◦

매출 계산 로직

◦

효율성 지표 계산 등

•

계층별 분리

◦

원시 데이터 처리

◦

중간 집계

◦

최종 지표 계산

이런 방식으로 복잡한 로직을 ODS에 분산 저장하면, 전체 데이터 파이프라인의 관리와 유지보수가 훨씬 수월해집니다.

이러한 구조는 데이터 파이프라인의 복잡성을 줄이고, 데이터 검증을 용이하게 만드는 효과적인 방법입니다.

-- models/ods/transactions/ods_monthly_transactions.sql
{{ config(materialized='view') }}

SELECT
    DATE_TRUNC(created_at, MONTH) as transaction_month,
    user_id,
    SUM(amount_krw) as total_amount,
    COUNT(*) as transaction_count
FROM {{ ref('stg_transactions') }}
WHERE status = 'completed'
GROUP BY 1, 2
SQL
복사

(4) Mart 모델 예시:

-- models/mart/finance/fct_monthly_revenue.sql
{{ config(materialized='table') }}

SELECT
    transaction_month,
    COUNT(DISTINCT user_id) as active_users,
    SUM(total_amount) as monthly_revenue,
    SUM(transaction_count) as total_transactions
FROM {{ ref('ods_monthly_transactions') }}
GROUP BY 1
SQL
복사

테스트 및 문서화

(1) 테스트 설정 예시:

# models/staging/transactions/schema.yml
version: 2

models:
  - name: stg_transactions
    description: "거래 데이터 기초 모델"
    columns:
      - name: transaction_id
        description: "거래 고유 ID"
        tests:
          - unique
          - not_null
      - name: amount_krw
        description: "거래 금액(원)"
        tests:
          - not_null
          - positive_value
YAML
복사

(2) 커스텀 테스트 예시:

-- tests/positive_value.sql
{% test positive_value(model, column_name) %}

SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= 0

{% endtest %}
SQL
복사

배포 프로세스

(1) CI/CD 설정 예시:

# .github/workflows/dbt-ci.yml
name: DBT CI

on:
  pull_request:
    branches: [ main ]

jobs:
  dbt-test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v2
        with:
          python-version: '3.8'

      - name: Install dependencies
        run: |
          pip install dbt-bigquery

      - name: Run dbt tests
        run: |
          dbt test --target ci
YAML
복사

Slim CI 구현

(1) Dockerfile 예시:

# Dockerfile.incremental.ci
FROM python:3.8-slim

WORKDIR /dbt

COPY requirements.txt .
RUN pip install -r requirements.txt

COPY . .

# manifest.json을 포함
COPY --from=full-ci-image /dbt/target/manifest.json ./target/
Plain Text
복사

(2) 실행 스크립트 예시:

#!/bin/bash

dbt run --target=ci -s "state:modified+1 1+exposure:*,+state:modified+" \\
    --defer \\
    --state ./target \\
    -x \\
    --full-refresh
Shell
복사

데이터 문서화 예시

# models/mart/finance/finance.md
{% docs monthly_revenue %}

# 월별 매출 데이터
이 모델은 월별 매출 집계를 제공합니다.

## 컬럼 설명
- transaction_month: 거래 월
- active_users: 해당 월의 실거래 사용자 수
- monthly_revenue: 월 매출액
- total_transactions: 총 거래 건수

## 참고사항
- 취소된 거래는 제외됨
- 금액은 원화 기준

{% enddocs %}
YAML
복사

데이터 리니지 시각화

dbt docs generate 명령어로 생성되는 문서에서 확인 가능한 데이터 흐름도:

raw_data.transactions
    → stg_transactions
        → ods_monthly_transactions
            → fct_monthly_revenue
Plain Text
복사

이러한 구체적인 예제들을 통해 쏘카가 어떻게 데이터 파이프라인을 체계적으로 관리하고 있는지 더 명확하게 이해할 수 있습니다. 각 레이어별로 명확한 책임과 역할이 있으며, 테스트와 문서화를 통해 데이터의 품질과 이해도를 높이고 있습니다.

특히 중요한 점은:

•

모든 변환 과정이 버전 관리되고 추적 가능

•

자동화된 테스트로 데이터 품질 보장

•

명확한 문서화로 팀원 간 지식 공유 용이

•

효율적인 CI/CD 파이프라인으로 빠른 개발 사이클 구현

이러한 체계적인 접근은 데이터 파이프라인의 안정성과 확장성을 크게 향상시켰습니다.

Q1. View로 구성된 mid table을 mart에서 union할 때 발생하는 "too many subqueries" 문제

•

문제상황: staging과 mart 사이에 mid table을 view로 구성하고, mart에서 여러 mid table을 union할 때 BigQuery에서 에러 발생

•

해결방안:

◦

Custom materialization을 만들어서 해결

◦

materialized view 활용

◦

참고: dbt 공식 문서의 creating-new-materializations 가이드 활용

Q2. ODS와 Intermediate 테이블의 차이점과 운영방식

•

ODS(Operational Data Store)는 mid table과 유사한 개념

•

기본적으로 view로 materialize하나, 필요에 따라 table로도 구성

•

Intermediate 개념은 별도로 사용하지 않음

•

Mart 레이어에서 ODS 모델을 직접 참조하는 방식으로 운영

Q3. View/Table Materialization 기준

쏘카의 기준:

사용 목적 중심으로 결정

빠른 조회가 필요한 경우 → Table

재사용성이 중요하고 최신 데이터가 필요한 경우 → View

다른 테이블 생성에 소스로 사용되는 경우 → View

이는 일반적인 기준(중요 집계테이블, 쿼리 수행시간 등)과는 다르게, 데이터의 활용 목적과 특성에 따라 materialization 전략을 결정하는 접근방식입니다.