財務データを題材に、 ETLとは何であるかを考える

バフェットコードでは、XBRL Fileとして表現された「決算書」をETLによってサービスで使える "標準化された" 決算に変形しています。

この資料では、バフェットコードでの事例を通して、「ETLとはデータの論理的・物理的な再配置」ということを説明しています。

Transcript

1. https://www.buffett-code.com/ 財務データを題材に、 ETLとは何であるかを考える {“name”: “shoe116”, “company”: “バフェットコード”, “date”:”2025-01-24”}
2. 今日話すこと 1. データパイプラインとETL 2. バフェットコードのETL a. s3の決算書（XBRL File）をPostgreSQLに保存する b. 決算書（生データ）と決算（標準化データ） 3. まとめ
3. whoami: Shu Suzuki(@shoe116) - お仕事 - Yahoo! Japanの広告システム -> データ基盤 - Mercari/Merpayでデータ基盤 - Buffett Code共同創業 - （さくらインターネットの監視プラットフォームのお手伝い） - 好き - ロック、アイドル、アイリッシュ音楽
4. バフェットコードについて 企業情報を網羅している - 日本上場・未上場企業 - 米国の上場企業 - 個社別の詳細なページ 便利な企業横断の分析機能 - 条件検索（スクリーニング） - 企業比較（comps） - 資料検索 https://www.buffett-code.com/
5. いい感じに決算ごとの財務数値が見れる https://www.buffett-code.com/company/4385/financial 表の1列が1つの決算
6. 予備知識(1) 決算と財務数値、TDNETとEDINET 決算 - 各銘柄、四半期ごとに開示される（FY2023 Q1, Q2…） - 売上や利益、資産や負債などの「財務数値」 2種類の決算報告書と「訂正」 - 東証の「TDNET」と金融庁の「EDINET」の2種類 - TDNETが速報値で、EDINETが確定値（と言いつつ「訂正」可能 - 人が読むためのPDF fileと、機械のためのXBRL file
7. 予備知識(2) 決算が書かれたXBRL file について XBRL(eXtensible Business Reporting Language)で記述 データ構造は👇の配列 - Tag: - contextRef - unitRef - decimals - values <NetSales contextRef="CurrentYearDuration" unitRef="JPY" decimals="-3">1234</NetSales>
8. Xmlのtagが縦軸、contextRefが横軸 https://www.fsa.go.jp/search/20231211/1b-1_GaiyoSetsumei.pdf
9. Xmlのtagが縦軸、contextRefが横軸 https://www.fsa.go.jp/search/20231211/1b-1_GaiyoSetsumei.pdf
10. <jpdei_cor:SecurityCodeDEI contextRef="FilingDateInstant"> 1234 </jpdei_cor:SecurityCodeDEI> <jpdei_cor:TypeOfCurrentPeriodDEI contextRef="FilingDateInstant"> Q2 </jpdei_cor:TypeOfCurrentPeriodDEI> <jpdei_cor:CurrentFiscalYearStartDateDEI contextRef="FilingDateInstant"> 2018-04-01 </jpdei_cor:CurrentFiscalYearStartDateDEI> <jpdei_cor:CurrentFiscalYearEndDateDEI contextRef="FilingDateInstant"> 2019-03-31 </jpdei_cor:CurrentFiscalYearEndDateDEI> <jppfs_cor:NetSales contextRef="Prior1YearDuration" unitRef="JPY" decimals="-3"> 123456000 </jppfs_cor:NetSales> <jppfs_cor:NetSales contextRef="CurrentYearDuration" unitRef="JPY" decimals="-3"> 223456000 </jppfs_cor:NetSales> 銘柄コード 四半期 今期の売上 締め日 去年の売上 四半期の初日
11. バフェットコードのデータパイプライン（の一部） XBRL-> RDB batch RDB -> Search batch RDB->RDB batch XBRL File収集
12. ETLとは？ 抽出・変換・格納してデータを「利用可能」にする - E: データソースからデータをExtract（抽出） - T: データ形式・データ構造をTransform（変換） - L: 変換したデータをストレージにLoad（格納） データの論理的・物理的な再配置を行う処理 - ピュアなETLでは（ユーザが利用できる）情報量は増えない - Transformが論理的、Loadが物理的な再配置
13. データを再配置する目的 アクセス方法の共通化とデータの標準化 - 利用可能 ≒ クエリできる - ユースケースに沿って標準化されたデータ 要件を満たす時間内にクエリを完了させる - データの物理的な置き方で、パフォーマンスは大きく変化する - 要件によっては、データを別の仕組みに再配置する必要がある
14. バフェットコードのETL データパイプラインの半分くらいはピュアなETL - s3上のXBRL File -> PostgreSQL - データモデリング（生データから標準化データへ） PostgreSQLからOpenSearchへの同期も、実はピュアなETL - record -> document はTransform（論理的な再配置）そのもの - 転置インデックスの作成を伴うデータの物理的な再配置
15. 番外編: よくあるlog pipelineのETL 再配置 - 論理的: Kafka topic内のmessage => Iceberg tableのrecord - 物理的: Kafka BrokerのLog File => s3のParquet File 目的 - ストリームなlogを、SQLでクエリしやすいTable形式に変換 - コンピュート・ストレージコストの最適化（列志向・圧縮）
16. なんでETLしてわざわざ「再配置」するの？ 再配置の目的 - 論理的な: クエリに適した再データモデリング - 物理的な: クエリに対するパフォーマンスの最適化 クエリで表現される、要件を満たすためのデータの事前処理
17. バフェットコードの「決算」データの持ち方 銘柄コード x 四半期で一意な「決算」を定義する 決算書の生データ != サービスで使える決算のデータ - 1つの決算で、決算書が少なくとも2つある - TDNETの「決算短信」 - EDINETの「有価証券報告書」 or 「四半期報告書」 - どの報告書も、 “訂正” されうるので値が確定しない
18. SELECT ticker, fiscal_year, fiscal_quarter, end_date, net_sales FROM financial_results WHERE ticker = '1234' ORDER BY end_date desc; ticker | fiscal_year | fiscal_quarter | end_date | net_sales --------+-------------+----------------+------------+-------------- 1234 | 2024 | 1 | 2024-09-30 | 123456000 1234 | 2023 | 4 | 2024-06-30 | 223456000 1234 | 2023 | 3 | 2024-03-31 | 323456000 1234 | 2023 | 2 | 2023-12-31 | 423456000
19. SELECT ticker, fiscal_year, fiscal_quarter, end_date, net_sales FROM financial_results <- 「決算」はEDINET でも TDNETでもない！ WHERE ticker = '1234' ORDER BY end_date desc; ticker | fiscal_year | fiscal_quarter | end_date | net_sales --------+-------------+----------------+------------+-------------- 1234 | 2024 | 1 | 2024-09-30 | 123456000 1234 | 2023 | 4 | 2024-06-30 | 223456000 1234 | 2023 | 3 | 2024-03-31 | 323456000 1234 | 2023 | 2 | 2023-12-31 | 423456000
20. RDBの1行が、表の1列
21. XBRL File -> s3上のXML fileをPostgreSQLに保存 XBRL-> RDB batch RDB -> Search batch RDB->RDB batch XBRL File収集
22. 復習: 決算書をXMLで記述した、XBRL file について XBRL(eXtensible Business Reporting Language) データ構造は👇の配列 - Tag: - contextRef - unitRef - decimals - value <NetSales contextRef="CurrentYearDuration" unitRef="JPY" decimals="-3">1234</NetSales>
23. XBRL File -> s3上のXML fileをPostgreSQLに保存 1つの決算書（XBRL file）を1列の”record”に変換する - TDNETとEDINETは別テーブル、別スキーマ XBRLのparseが難しいと言われるが、難しいのはformat - XBRLはXMLなので、parseは標準のXML parserで一発 - 前述の通り、元が2次元の表なのでformat時にflattenが必要 「parseしてformat」=> 論理的な再配置
24. <jpdei_cor:SecurityCodeDEI contextRef="FilingDateInstant"> 1234 </jpdei_cor:SecurityCodeDEI> <jpdei_cor:TypeOfCurrentPeriodDEI contextRef="FilingDateInstant"> Q2 </jpdei_cor:TypeOfCurrentPeriodDEI> <jpdei_cor:CurrentFiscalYearStartDateDEI contextRef="FilingDateInstant"> 2018-04-01 </jpdei_cor:CurrentFiscalYearStartDateDEI> <jpdei_cor:CurrentFiscalYearEndDateDEI contextRef="FilingDateInstant"> 2019-03-31 </jpdei_cor:CurrentFiscalYearEndDateDEI> <jppfs_cor:NetSales contextRef="Prior1YearDuration" unitRef="JPY" decimals="-3"> 123456 </jppfs_cor:NetSales> <jppfs_cor:NetSales contextRef="CurrentYearDuration" unitRef="JPY" decimals="-3"> 223456 </jppfs_cor:NetSales>
25. <jpdei_cor:SecurityCodeDEI contextRef="FilingDateInstant"> 1234 </jpdei_cor:SecurityCodeDEI> <jpdei_cor:TypeOfCurrentPeriodDEI contextRef="FilingDateInstant"> Q2 </jpdei_cor:TypeOfCurrentPeriodDEI> <jpdei_cor:CurrentFiscalYearStartDateDEI contextRef="FilingDateInstant"> 2018-04-01 </jpdei_cor:CurrentFiscalYearStartDateDEI> <jpdei_cor:CurrentFiscalYearEndDateDEI contextRef="FilingDateInstant"> 2019-03-31 </jpdei_cor:CurrentFiscalYearEndDateDEI> <jppfs_cor:NetSales contextRef="Prior1YearDuration" unitRef="JPY" decimals="-3"> 123456 </jppfs_cor:NetSales> <jppfs_cor:NetSales contextRef="CurrentYearDuration" unitRef="JPY" decimals="-3"> 223456 </jppfs_cor:NetSales> 銘柄コード 四半期 今期の売上 締め日 前期の売上 四半期の初日
26. <jpdei_cor:SecurityCodeDEI contextRef="FilingDateInstant"> 1234 </jpdei_cor:SecurityCodeDEI> <jpdei_cor:TypeOfCurrentPeriodDEI contextRef="FilingDateInstant"> Q2 </jpdei_cor:TypeOfCurrentPeriodDEI> <jpdei_cor:CurrentFiscalYearStartDateDEI contextRef="FilingDateInstant"> 2018-04-01 </jpdei_cor:CurrentFiscalYearStartDateDEI> <jpdei_cor:CurrentFiscalYearEndDateDEI contextRef="FilingDateInstant"> 2019-03-31 </jpdei_cor:CurrentFiscalYearEndDateDEI> <jppfs_cor:NetSales contextRef="Prior1YearDuration" unitRef="JPY" decimals="-3"> 123456 </jppfs_cor:NetSales> <jppfs_cor:NetSales contextRef="CurrentYearDuration" unitRef="JPY" decimals="-3"> 223456 </jppfs_cor:NetSales> ticker | fiscal_year | fiscal_quarter | end_date | net_sales | net_sales_1y_ago --------+-------------+----------------+------------+------------------------------ 1234 | 2018 | 2 | 2019-03-31 | 223456 | 123456
27. XBRL File -> s3上のXML fileをPostgreSQLに保存 データの「論理的な再配置」が想像よりずっと大変 net_sales (売上)として扱いたいTagが無数にある - 会計基準・業態によって「売上」の呼び方に違いがある - ”タクソノミ” が金融庁から公開され、毎年増減する - TagとcontextRefに優先度をつけ、「一番それっぽい」 ものを net_sales として採用している
28. BC的に「売上」に名寄せしているタグのほんのごく一部 - Revenue - RevenueIFRS - Revenue2IFRS - OperatingRevenue1 - OperatingRevenue2 - OperatingRevenueIFRSSu mmaryOfBusinessResults - OperatingRevenue1Summa ryOfBusinessResults - OperatingRevenue2Summa ryOfBusinessResults 金融庁に文句は言いたくないが、絶対「何か」がおかしい
29. タクソノミは何がおかしい？ モデリングのアンチパターン - 「見た目」「要件」からデータ 構造を逆算する - 表示項目名に1:1対応するTag - メンタルモデルの欠落 美しすぎるアンチパターン👉 https://www.fsa.go.jp/search/20231211/1b-1_GaiyoSetsumei.pdf
30. 複数ある決算書のレコードを1列にマージする XBRL-> RDB batch RDB -> Search batch RDB->RDB batch XBRL File収集
31. 複数ある決算書のレコードを1列にマージする 特定の決算書に紐づく生データ => 標準化された「決算」 - TDNET, EDINETのレコードを集約して、銘柄コードと四 半期でuniqueなデータにする - 決算書に書かれた「去年の売上」を、該当決算に遡って 適用する - 簡単にいうと、各財務数値において一番新しい値を選ぶ
32. – EDINET REPORTS（有価証券報告書） ticker | fiscal_year | fiscal_quarter | published_at | end_date | net_sales | net_sales_1y_ago --------+-------------+----------------+--------------+------------------------------ 1234 | 2018 | 2 | 2019-05-05 | 2019-03-31 | 223456 | 123456 1234 | 2018 | 2 | 2019-05-01 | 2019-03-31 | 22345 | 123456 1234 | 2017 | 2 | 2018-05-01 | 2018-03-31 | 123450 | 123450 – TDNET REPORTS（決算短信） ticker | fiscal_year | fiscal_quarter | published_at | end_date | net_sales | issued_stocks --------+-------------+----------------+--------------+---------------------------------------- 1234 | 2018 | 2 | 2019-04-15 | 2019-03-31 | 223450 | 2000 1234 | 2017 | 2 | 2018-04-15 | 2018-03-31 | 123450 | 1000 – FINANCIAL_RESULTS（標準化された「決算」） ticker | fiscal_year | fiscal_quarter | last_updated | end_date | net_sales | issued_stocks --------+-------------+----------------+--------------+---------------------------------------- 1234 | 2018 | 2 | 2019-05-05 | 2019-03-31 | 223456 | 2000 1234 | 2017 | 2 | 2018-05-01 | 2018-03-31 | 123456 | 1000
33. RDBの1行が、表の1列
34. 今日のまとめ ETLとは、データの論理的・物理的な再配置のこと ETLをしても、情報量が増えるわけではない ETLの目的 - データモデリングとシステムの最適化 - バフェットコードでは、ETLによって決算書の「生デー タ」から、標準化された「決算データ」を作成している
35. 絶対テストに出ます 見た目とか要件からデータ構造を決めてはいけない 決算書から「正しく」決算のデータを作るのは難しい Don’t Repeat Yourself, Use BuffettCode Quarterly API! https://docs.buffett-code.com/api/
36. 今日話さなかったこと 1. XBRL以外のデータどうしてんの？ 株式分割とか、ものすごく大変な思いをして作っています 未上場企業とかヤバみしかないです・・・ 2. バフェットコードって採用してるの？ 絶賛採用活動中です→ https://career.buffett-code.com/