ETL

ETL 은 여러 곳에 흩어진 데이터를 한곳에 모아 분석할 수 있게 해 줍니다. 원천에서 데이터를 뽑아 옵니다. 분석에 맞는 모양으로 먼저 바꾼 뒤에 분석용 저장소에 넣습니다. 이름은 뽑기·바꾸기·넣기를 뜻하는 영어 낱말 Extract·Transform·Load 의 머리글자입니다.

상세

여러 가게에서 장을 봐 오면 재료의 모양이 제각각입니다. 흙 묻은 무, 비닐에 싼 고기, 봉지째 산 양파가 섞여 있습니다. 집에 오면 씻고 다듬어 칸마다 나눠 냉장고에 넣습니다. 그래야 요리할 때 바로 꺼내 씁니다.

ETL(Extract Transform Load, 추출·변환·적재)은 데이터를 두고 이 일을 합니다. 장을 보는 것이 추출, 씻고 다듬는 것이 변환, 냉장고에 넣는 것이 적재입니다. 요리에 해당하는 것이 분석입니다.

이 절은 쇼핑몰 하나를 예로 들어 세 단계를 차례로 따라갑니다. 그다음 변환을 적재 뒤로 미루는 방식과 견줍니다. 이어서 작업이 도는 주기와 다시 돌려도 안전하게 짜는 법을 봅니다. ETL 이 맞지 않는 일로 끝냅니다.

흩어진 데이터를 한곳에 모으는 이유

쇼핑몰 백엔드를 떠올려 봅니다. 주문은 주문 데이터베이스에, 회원 정보는 회원 데이터베이스에 있습니다. 결제 내역은 결제 대행사가 날마다 파일로 보내 줍니다. 광고 클릭은 로그로 쌓입니다. 이렇게 데이터가 처음 생기는 곳을 원천 시스템이라고 부릅니다.

「지난달 광고를 보고 들어온 고객이 얼마를 썼나」에 답하려면 넷을 다 엮어야 합니다. 그런데 서비스용 데이터베이스는 주문 한 건을 읽고 쓰는 일에 맞춰 두었습니다. 여기에 한 달 치를 훑는 조회를 던지면 그동안 주문 처리가 밀립니다.

그래서 분석할 데이터를 따로 복사해 모으는 저장소를 둡니다. 이것이 데이터 웨어하우스입니다. 여러 원천의 데이터를 분석하기 쉬운 표로 정리해 담는 분석 전용 데이터베이스입니다. ETL 은 이 웨어하우스를 채우는 작업입니다.

추출

추출은 원천 시스템에서 필요한 데이터를 읽어 오는 단계입니다. 읽는 방법은 크게 둘입니다. 매번 표 전체를 읽는 전체 추출과, 지난번 이후 바뀐 행만 읽는 증분 추출입니다.

전체 추출은 단순합니다. 대신 데이터가 쌓일수록 읽는 양이 늡니다. 그만큼 원천 데이터베이스도 오래 붙잡힙니다. 그래서 표가 커지면 대개 증분 추출로 옮겨 갑니다.

증분 추출에는 「바뀐 행」을 가려낼 표시가 필요합니다. 흔한 방법은 행마다 마지막으로 고친 시각을 적는 열을 두는 것입니다. 추출할 때는 지난번 실행 이후에 고쳐진 행만 고릅니다.

SELECT * FROM orders
WHERE updated_at > :last  -- 바뀐 행만

:last 에는 지난번 추출을 시작한 시각이 들어갑니다. 이 방법은 지워진 행을 못 봅니다. 지워진 행은 고친 시각을 남기지 않기 때문입니다. 삭제까지 잡으려면 데이터베이스의 변경 기록을 읽어 오는 변경 데이터 캡처를 씁니다.

읽어 온 데이터는 곧장 변환하지 않고 스테이징 영역에 먼저 내려놓는 경우가 많습니다. 스테이징 영역은 추출한 원본을 손대지 않고 잠시 두는 중간 저장소입니다. 변환이 도중에 실패해도 원천을 다시 읽지 않고 여기부터 다시 돌릴 수 있습니다.

변환

변환은 원천마다 제각각인 데이터를 웨어하우스 표의 모양에 맞추는 단계입니다. 표에 어떤 열이 있고 열마다 어떤 값이 들어가는지를 정한 약속을 스키마라고 부릅니다. ETL 은 이 스키마를 먼저 정해 두고 거기에 맞춰 넣습니다.

변환에서 하는 일은 대개 아래 넷입니다.

틀린 값을 거르는 일은 따로 데이터 정제라고도 부릅니다. 거른 행을 버리지 않고 따로 모아 두면 원천 쪽 버그를 찾을 때 단서가 됩니다.

미리 모아 두는 일에는 대가가 따릅니다. 합계만 남기면 표가 작아져서 조회가 금방 끝납니다. 대신 합계를 낼 때 쓰지 않은 기준은 그 표에서 다시 뽑을 수 없습니다. 날짜별로만 더해 두었다면 시간대별 매출은 이 표로 못 구합니다.

적재

적재는 변환을 마친 데이터를 웨어하우스 표에 넣는 단계입니다. 넣는 방식은 셋으로 갈립니다.

셋 중 무엇을 고르느냐는 추출 방식과 맞물립니다. 전체 추출이면 덮어쓰기가 간단합니다. 증분 추출이면 덧붙이기나 업서트를 씁니다. 업서트라는 이름은 update 와 insert 를 합친 말입니다.

적재는 대개 한 행씩 넣지 않고 여러 행을 묶어 한 번에 넣습니다. 행마다 데이터베이스와 주고받으면 그 왕복이 행 수만큼 쌓이기 때문입니다.

지금까지 본 세 단계를 이어 그리면 아래와 같습니다.

flowchart TD
    subgraph 원천["원천 시스템"]
        A["주문 데이터베이스"]
        B["회원 데이터베이스"]
        C["결제 파일"]
        D["광고 클릭 로그"]
    end
    원천 --> E["추출"]
    E --> S["스테이징 영역"]
    S --> T["변환"]
    T --> L["적재"]
    L --> W["데이터 웨어하우스"]

원천 시스템은 추출할 때 한 번만 읽힙니다. 그 뒤의 변환과 적재는 스테이징 영역에 내려놓은 사본을 읽습니다.

변환을 적재 뒤로 미루는 ELT

ETL 은 변환을 웨어하우스 밖에서 끝내고 넣습니다. 순서를 바꿔 원본을 먼저 넣고 웨어하우스 안에서 변환하는 방식도 있습니다. 이것을 ELT(Extract, Load, Transform, 추출·적재·변환)라고 부릅니다.

순서를 바꿀 수 있는 것은 웨어하우스가 큰 데이터를 스스로 변환할 만큼 계산 자원을 갖추게 됐기 때문입니다. 변환을 따로 도는 서버에 맡기지 않고 웨어하우스에 던지는 조회로 처리합니다. 두 방식을 나란히 놓으면 아래와 같습니다.

표의 마지막 줄이 ETL 을 계속 쓰는 까닭 하나입니다. 카드 번호처럼 분석에 쓸 일이 없는 값은 애초에 웨어하우스로 안 들어가게 막을 수 있습니다.

작업이 도는 주기

ETL 은 대개 정해진 때에 몰아서 돕니다. 밤 한 시에 어제 하루 치를 옮기는 식입니다. 모아 두었다가 한 번에 처리하는 이 방식을 배치 처리라고 부릅니다.

그래서 웨어하우스의 데이터는 원천보다 늦습니다. 오늘 오후에 들어온 주문은 오늘 밤 작업이 끝나야 보입니다. 이 늦음을 줄이려면 작업을 한 시간이나 몇 분마다 돌립니다. 더 줄이려면 바뀐 행을 들어오는 대로 흘려보내는 스트림 처리로 옮겨 갑니다.

ETL 작업은 여럿이 순서를 지켜 돌아야 합니다. 주문 추출이 끝나야 주문 변환을 시작할 수 있습니다. 작업을 정해진 때에 띄우고 앞뒤 순서를 지켜 주는 일은 워크플로우 오케스트레이션 도구가 맡습니다.

다시 돌려도 안전하게 짜는 법

ETL 작업은 도중에 실패하는 일이 흔합니다. 원천 데이터베이스와 연결이 잠깐 끊기기도 합니다. 예상 못 한 형식의 값이 들어와 변환이 멈추기도 합니다. 이때 가장 쉬운 복구는 그 작업을 다시 돌리는 것입니다.

걸리는 것은 절반쯤 들어간 상태에서 다시 돌릴 때입니다. 덧붙이기로 넣었다면 앞서 들어간 행이 한 번 더 들어가 매출이 두 번 잡힙니다.

그래서 ETL 작업은 멱등성을 갖추도록 짭니다. 멱등성은 같은 입력으로 여러 번 돌려도 결과가 한 번 돌린 것과 같은 성질입니다. 멱등하면 실패한 작업을 몇 번이고 다시 돌려도 값이 겹치지 않습니다.

흔한 방법은 둘입니다. 하나는 「그날 치를 지우고 다시 넣기」를 트랜잭션 하나로 묶는 것입니다. 트랜잭션은 여러 작업을 한 덩이로 묶어 전부 반영되거나 전부 없던 일이 되게 하는 단위입니다. 다른 하나는 키가 겹치면 고치는 업서트로 넣는 것입니다.

넣은 뒤에 결과를 확인하는 단계를 두기도 합니다. 원천에서 읽은 행 수와 웨어하우스에 들어간 행 수를 견줍니다. 합계 금액이 맞는지도 봅니다. 이런 검사를 묶어 데이터 품질 검사라고 부릅니다.

ETL 이 맞지 않는 일

결과를 몇 초 안에 봐야 하는 일에는 안 맞습니다. 이상 거래를 잡아내는 일이 그렇습니다. 몇 시간마다 도는 작업으로는 답이 몇 시간 늦습니다. 이런 일은 스트림 처리가 받습니다.

어떤 질문이 나올지 모르는 데이터에도 부담이 큽니다. ETL 은 넣기 전에 모양을 정하므로 정한 표에 없는 값은 버려집니다. 원본을 먼저 쌓아 두고 쓸 때 모양을 정하려면 ELT 로 갑니다. 원본을 모양 그대로 쌓아 두는 저장소인 데이터 레이크도 같은 길입니다.

원천이 하나뿐이고 분석 조회가 드물면 ETL 을 따로 둘 까닭이 적습니다. 운영 데이터베이스를 복제한 읽기 복제본에 조회를 던지면 주문 처리를 밀어내지 않고 답을 얻습니다.

관련 항목

ETL 이 데이터를 싣는 저장소

데이터 웨어하우스 · 데이터 마트 · 데이터 레이크 · 레이크하우스 · 스테이징 영역

ETL 이 속하는 상위 분류

데이터 엔지니어링 · 데이터 통합 · 데이터 파이프라인 · 파이프라인

ETL 이 거치는 처리 단계

추출 · 변환 · 적재 · 증분 추출 · 데이터 정제 · 업서트

ETL 과 같은 일을 다른 순서로 하는 통합 방식

ELT · 역방향 ETL · 변경 데이터 캡처 · 데이터 복제 · 데이터 가상화

ETL 작업이 기대는 처리 방식

배치 처리 · 마이크로 배치 · 스트림 처리 · 배치 창

ETL 작업을 짜고 돌리는 도구

워크플로우 오케스트레이션 · 스케줄러 · cron · Apache Airflow · dbt · Apache Spark

ETL 이 변환 목표로 삼는 데이터 모델

스키마 · 쓰기 시 스키마 · 읽기 시 스키마 · 차원 모델링 · 스타 스키마 · OLAP

ETL 작업이 지켜야 하는 성질

멱등성 · 트랜잭션 · 데이터 품질 · 데이터 계보 · 재시도 · 체크포인트

ETL 에서 자주 나는 장애

중복 적재 · 배치 창 초과 · 스키마 드리프트 · 늦게 도착한 데이터 · 데이터 누락