ETL
ETL ・ いーてぃーえる
散在するデータを抽出・変換して分析基盤へ集めるデータパイプラインの基本形。
✦ 30秒で試す — 航海エラーは出ていない。なのに売上の数字が静かに間違う実行し直した後の、9/5の売上の数字は？予想してみる概要
ETL は、Extract（抽出）・Transform（変換）・Load（格納）の頭文字で、あちこちに散らばったデータを分析できる形に整えて一か所に集める、データパイプラインの基本形です。業務システムのデータベース、SaaS の API、アプリケーションのログといった複数のデータ源から夜間などにデータを取り出し、形式を揃えてクレンジングし、データウェアハウスのような分析基盤へ積み込む、という一連の流れを指します。
「先月の売上をチャネル別に見たい」「解約率をユーザー属性と掛け合わせたい」— こうした分析の前提には、必要なデータが分析可能な形で一か所に揃っていることがあります。その「揃える」作業を自動化・定型化したものが ETL で、データエンジニアリングという職種の中心的な仕事のひとつです。ETL のジョブは毎日・毎時と繰り返し走り、ときどき失敗し、そのたびにやり直されます。この「繰り返しとやり直し」に耐える作りにできるかどうかが、良いパイプラインと壊れやすいパイプラインの分かれ目です。
なぜ生まれたか
企業のシステムは、販売管理・会計・在庫・顧客管理と、業務ごとに別々に作られていくのが常です。その結果、データはシステムごとの RDBMS やファイルに散在し、同じ「顧客」でもシステムによって ID 体系が違う、日付の形式が違う、金額に税が含まれたり含まれなかったりする、という状態になります。この状態で横断的な分析をしようとすると、毎回手作業でデータをかき集めて突き合わせることになり、時間がかかるうえに集計のたびに数字が食い違います。
さらに、業務システムのデータベースに重い集計クエリを直接投げると、本番業務の処理を圧迫する危険もあります。そこで1980年代末から90年代にかけてデータウェアハウスの考え方が広まるとともに、「業務システムからデータを定期的に写し取り、分析用に変換してから専用の置き場に積む」という工程が定型化されました。これが ETL です。90年代には Informatica などの専用 ETL ツールが登場し、画面上で抽出・変換・格納の段をつないでジョブを組むやり方が一般化します。散在と不整合という問題を、「変換を一度だけ、パイプラインの中で行い、以降は整った一枚のデータを全員が使う」ことで解決したのです。
詳細
三つの段階
Extract（抽出）は、データ源からデータを取り出す段階です。業務DBからの SQL による読み出し、SaaS の API 呼び出し、ログファイルの収集などが該当します。全件を毎回取り直す方式は単純ですが、データ量が増えると時間も業務DBへの負荷も膨らむため、前回以降の差分だけを取る増分抽出が必要になります。増分の取り方は大きく2つです。1つは「更新日時がこの時刻より後の行」を読む方式（最後に読んだ時刻を覚えておくので、高水位線と呼ばれます）で、手軽な反面、削除された行を拾えず、更新日時を書き忘れる更新も取りこぼします。もう1つが CDC（変更データキャプチャ）で、データベースの変更ログ（WAL）を読んで挿入・更新・削除を1件ずつ取り出します。取りこぼしがなく業務DBへの負荷も小さいため、Debezium のようなツールで CDC をストリーム処理の入力にする構成が増えています。
Transform（変換）は、取り出した生データを分析に使える形へ整える段階です。形式の統一（日付・通貨・文字コード）、テスト注文や重複・欠損の除去、システム間で異なる ID の突き合わせ（名寄せ）、集計しやすい構造への組み替えなどを行います。Load（格納）は、変換済みデータを分析基盤へ積み込む段階です。ここで「どう書き込むか」— 追記するのか、置き換えるのか — が、後で見るように再実行の安全性を決めます。
ETL から ELT へ — 変換を後回しにする転回
近年の大きな変化が、順序を入れ替えた ELT（Extract-Load-Transform）の台頭です。従来の ETL では、格納前に専用の変換サーバやツールでデータを整えていました。分析基盤の容量と計算力が貴重で、「汚れたデータを置く余裕はない」時代の合理的な設計です。ところが BigQuery や Snowflake といったクラウド DWH の登場で前提が変わりました。ストレージは安価になり、DWH 自身が巨大な計算力を持つようになったため、「まず生データをそのまま積んでしまい、変換は DWH の中で SQL として実行する」方が合理的になったのです。ELT では生データが手元に残るため、変換ロジックの誤りに後から気づいてもやり直しがきき、変換処理を SQL として Git でバージョン管理し、テストを書ける利点もあります。dbt のような変換管理ツールがこの流儀を後押しし、現在の主流になりつつあります。生データを置く場所としては、DWH の代わりにデータレイクを使う構成もよく見られます。
バッチとストリーミング
ETL の実行タイミングには二つの流儀があります。伝統的なのは「夜間に1日分をまとめて処理する」バッチ処理で、実装が単純で障害時のやり直しも容易なため、今も大半のパイプラインの基本形です。一方、「今起きていることを分単位で見たい」という要求には、データが発生するそばからメッセージキュー（Kafka など）を通じて流し続けるストリーム処理が使われます。リアルタイム性と引き換えに、順序の乱れや遅れて届くデータ、重複到着への対処が必要になり、設計の難易度は大きく上がります。「本当にリアルタイムが必要か」を見極め、バッチで済むものはバッチにするのが実務の定石です。
再実行に耐える — 冪等なパイプライン
ETL の厄介なところは、壊れても即座にエラーにならず「間違った数字が静かに流れ続ける」ことです。典型が再実行による二重計上です。9/5 の売上を積む日次ジョブが、半分ほど書き込んだところでネットワーク断により落ちたとします。運用者は当然、ジョブを再実行します。このとき格納が「行を追記する（INSERT）」だけだと、前回書き込んだ半分の上に、全行がもう一度積まれます。ジョブは今度は「成功」し、翌朝のダッシュボードには 9/5 の売上が1.5倍になった数字が何の警告もなく並びます。
これを防ぐのが、再実行しても結果が変わらない冪等性を持たせた設計です。定番は、書き込み先を日付ごとのパーティション（表を日付で物理的に区切った区画）に分け、ジョブは「その日のパーティションを丸ごと作り直す」ことだけをする形です。9/5 のジョブはまず 9/5 の区画を消し（DELETE）、それから積み直す（INSERT）。トランザクションで包むか、DWH の INSERT OVERWRITE のように区画を一気に差し替える命令を使えば、途中で落ちても半端な状態すら見えません。何度実行しても 9/5 の区画は同じ中身になるので、失敗したら迷わず再実行でき、過去の日付をまとめて作り直す「バックフィル」も同じジョブで安全に行えます。行に一意なキーがあるなら、MERGE（あれば更新・なければ挿入）で書くのも同じ効果を持ちます。
下の部品で、日次ジョブを実行中に段の箱を押して落とし、「再実行」を押してみてください。書き込み方を「追記」と「上書き」で切り替えると、同じ再実行が二重計上を生むか、正しい値に戻すかが変わります。
「9/5 の日次ジョブを実行」を押すと、注文の行が E→T→L の段を流れて DWH に積まれます。流れている最中に段の箱を押すと、ジョブはそこで落ちます。
冪等性のほかにも、壊れ方は多彩です。データ源側のスキーマ変更（列の追加・型の変更）で変換が黙って狂う、上流の遅延で「まだ届いていない日」を空のまま集計してしまう、といった事故が典型です。件数や値域、主キーの一意性を検証するデータ品質のテスト、ジョブの成否と所要時間・行数の監視は、ETL 運用の必須項目です。「分析の数字がおかしい」という指摘は往々にしてダッシュボードではなくパイプラインの上流に原因があり、データがどこから来てどう変換されたかを追える系譜（リネージ）の整備が、信頼されるデータ基盤の分かれ目になります。
試問
9/5 の日次ジョブが、業務DBの注文を E（抽出）→ T（変換: テスト注文と取消を除く）→ L（格納）の段に流して、データウェアハウスの 9/5 の区画に売上を積む画面です。流れている最中に E・T・L の箱を押すと、ジョブはそこで落ちます（途中まで書いた行は残る）。書き込み方は「追記（行を書き足す）」と「上書き（9/5 の区画を消してから積む）」から選べます。
「9/5 の日次ジョブを実行」を押すと、注文の行が E→T→L の段を流れて DWH に積まれます。流れている最中に段の箱を押すと、ジョブはそこで落ちます。