© LayerX Inc. dbtとSnowflakeで実現する Reverse ETL 2025/02/17 @civitaspo Tokyo dbt Meetup #12

© LayerX Inc. dbtでETL/Reverse ETLやったら 思ったより運⽤が⼤変でした 2025/02/17 @civitaspo Tokyo dbt Meetup #12

© LayerX Inc. 3 バクラク事業部 機械学習‧データ部 DataOps チーム TechLead 兼 Platform Engineering部 DevOps チーム 今⽇は福岡から来ました！！！ Snowflake九州ユーザーグループ主宰 Snowflake Squad 2024 SNS 𝕏 civitaspo civitaspo その他 画像を⼊れてね civitaspo （ｷﾋﾞﾀｽﾎﾟ/ きびちゃん） ⾃⼰紹介

© LayerX Inc. 4 ref. Google BigQueryからSnowflakeへ。バクラクのデータ基盤技術移管事例 - Findy Tools ⾃⼰紹介

5 © LayerX Inc. すべての経済活動を、デジタル化する。 会社概要 会社名 株式会社LayerX（レイヤーエックス） 代表取締役 代表取締役CEO 福島 良典 代表取締役CTO 松本 勇気 創業 2018年 8⽉1⽇ 資本⾦ 約132.6億円 拠点 東京本社 〒104-0045 東京都中央区築地1-13-1 銀座松⽵スクエア 5階 関⻄⽀社 〒530-0002 ⼤阪府⼤阪市北区曽根崎新地1-13-22 御堂筋フロントタワー 内 中部⽀社 〒466-0064 愛知県名古屋市昭和区鶴舞1-2−32 STATION Ai 内 九州⽀社 〒810-0801 福岡県福岡市博多区中洲3-7-24 WeWorkゲイツ福岡 11F 内 従業員数 385名 （2024年10⽉末時点）

6 © LayerX Inc. 「すべての経済活動を、デジタル化する。」をミッションに、AI SaaSとAI DXの事業を展開 事業紹介 バクラク事業 企業活動のインフラとなる業務を 効率化するクラウドサービス Fintech事業 ソフトウェアを駆使したアセットマネジ メント‧証券事業を合弁会社にて展開 AI‧LLM事業 社内のナレッジやノウハウをデータ ベース化するAIプラットフォーム AI SaaSドメイン AI DXドメイン

© LayerX Inc.　 7 「バクラク」シリーズラインナップ ‧AIが請求書を５秒でデータ化 ‧仕訳 / 振込データを⾃動作成 ‧電帳法‧インボイス制度にも対応 仕訳‧⽀払処理効率化 ‧年会費無料で何枚でも発⾏可 ‧カード利⽤制限で統制を実現 ‧すべての決済で1%以上の還元 法⼈カードの発⾏‧管理 ‧帳票の⼀括作成も個別作成も⾃由⾃在 ‧帳票の作成‧稟議‧送付‧保存を⼀本化 ‧レイアウトや項⽬のカスタマイズも可能 請求書発⾏ ‧スキャナ保存データも直接取込  ‧AI-OCRが⾃動読取＆データ化 ‧[取引先][取引⽇][取引⾦額]での検索 帳票保存‧ストレージ ‧AIが⾒積書‧請求書を５秒でデータ ‧スマホからも申請‧承認OK ‧柔軟な通知設定‧承認の催促機能 稟議‧⽀払申請 ‧直感的UIで従業員の負担を軽減 ‧Slack連携で打刻や⾃動リマインド可能 ‧わかりやすい残業 / 休暇管理レポート 勤怠管理 ‧AIが領収書を5秒でデータ化 ‧スマホアプリとSlack連携あり ‧領収書の重複申請などミス防⽌機能 経費精算

今⽇話す内容

© LayerX Inc. 9 ● バクラクではdbtを使って、ETL/Reverse ETLの運⽤をしています。 ● この実運⽤における苦悩や運⽤ノウハウを発表していきます。 dbtでETL/Reverse ETLやったら運⽤⼤変だった！！ 今⽇話す内容

© LayerX Inc. 10 バクラクのデータ基盤 今⽇話す内容 ※ ⼀部構築中のものも含む

© LayerX Inc. 11 バクラクのデータ基盤 今⽇話す内容 ※ ⼀部構築中のものも含む 今⽇話すのはこのあたりの話

⽬次 Agenda ● バクラクにおけるETL/Reverse ETL事例 ● dbtでETL/Reverse ETLする嬉しさ ● dbtでETL/Reverse ETLする⾟さ ● ⾟さ①: 実⾏ライフサイクル ● ⾟さ②: エラーハンドリング ● ⾟さ③: 環境差分吸収 ● おわりに

バクラクにおけるETL/Reverse ETL事例

© LayerX Inc. 14 ● Pythonを使って外部APIからデータを取得 し、データを格納するパターン ● 前回取得データがあれば、incrementalに データ取得可能なAPIであれば、可能な限り incremental modelにする運⽤にしていま す。 事例①: dbt Python Modelを使ったETL バクラクにおけるETL/Reverse ETL事例 前回実⾏時間以降のデータを取得する場合、格納先テーブルから前回更新時刻の最⼤を取得 その時刻を元にAPIリクエストを組み⽴てる。

© LayerX Inc. 15 ● Snowflake Marketplaceで、SaaSデータ連携 ⽤のStored ProcedureやUDFを取得できるこ とがある。 ● それらStored ProcedureやUDFを使⽤した SQLでデータ取得するパターン。 事例②: Stored ProcedureやUDFでETL バクラクにおけるETL/Reverse ETL事例 ※ Snowflake Marketplace はSnowflake上でデータ‧アプリケーションを配布するプラットフォーム ※ UDFは正確にはUDTF = User Defined Table Function

© LayerX Inc. 16 ● 取得できるデータがJSONとなっている場 合、型を付けるためにSQLを⽣成するSQLを 書く必要があったりする。 事例②: Stored ProcedureやUDFでETL（つづき） バクラクにおけるETL/Reverse ETL事例

© LayerX Inc. 17 ● Pythonを使って外部APIなどへデータを書き 戻すパターン ● dbt Python Modelは何らかのデータをDWH 側に返せば良いので、外部APIをCallしつつ、 実データとしては何も保存しない、といった 使い⽅ができる。 ● バクラクでは、外部APIに送付したレコード を保存しておき、再実⾏時に既に送付済みの レコードは送付しないようにしている。 事例③: dbt Python Modelを使ったReverse ETL バクラクにおけるETL/Reverse ETL事例

dbtでETL/Reverse ETLする嬉しさ

© LayerX Inc. 19 ● dbt Python Modelは使⽤するadapterごとに実⾏環境が異なります。 ○ BigQueryの場合はDataproc ○ Snowflakeの場合はSnowpark ○ Databricksの場合はPySpark ○ … ● いずれの実⾏環境もちょっとした設定を加えるだけで実⾏環境を⽤意することができます 嬉しさ①: 特別な実⾏環境が不要 dbtでETL/Reverse ETLする嬉しさ

© LayerX Inc. 20 ● dbtでETL/Reverse ETLを⾏うと、データ取得元からデータ送信先まで⼀気通貫で依存関係を構築で きます ● エラー発⽣時にdbtの情報だけで影響範囲を特定できます ● データ変更時に、下流のデータモデルへの影響がシュッと把握できるのも嬉しいポイントです 嬉しさ②: Data Lineageがdbt内で完結する dbtでETL/Reverse ETLする嬉しさ

© LayerX Inc. 21 ● dbt内でデータ取得やデータ送信の実装が完結するので、dbt利⽤者の施策の幅が広がる ● AIによる強い実装サポートを受けられる時代なので、利⽤者だけで取れる⼿段は広げておくべし 嬉しさ③: dbt利⽤者がdbtだけでやれることが増える dbtでETL/Reverse ETLする嬉しさ

dbtでETL/Reverse ETLする⾟さ

© LayerX Inc. 23 ここまでがイントロダクションでした dbtでETL/Reverse ETLする⾟さ

⾟さ①: 実⾏ライフサイクル

© LayerX Inc. 25 ● TransformはCI/CDで関連モデルを全て実⾏したい ● ⼀⽅、ETL/Reverse ETLは⾼頻度で実⾏出来ないケースもある ○ 世の中には優しく叩かないといけないAPIが多い ETL/Reverse ETLと純粋なTransformはライフサイクルが異なる ⾟さ①: 実⾏ライフサイクル 安直に全モデルをビルドするのは危険！ 1⽇1回の更新にして欲しい 変更のたびに更新したい

© LayerX Inc. 26 ● --select、--exclude、--selector の対象を、変更ファイルや実⾏コンテクストによって⾃動 的に⽣成するようにして解決 ● dbtの実⾏は必ずTask Runner経由で⾏い、実⾏コンテクストはTask Runner内で⾃動判定している 解決策: バッチ実⾏時‧CI/CD時の実⾏対象モデルを⾃動⽣成 ⾟さ①: ライフサイクル ※ exclude models の⽣成スクリプトは運⽤ルールを知らないと読めないので割愛しています。 Pull RequestなContextで変更差分を取得するスクリプト ローカル環境なContextで変更差分を取得するスクリプト

© LayerX Inc. 27 ● dbt-labs/jaffle-shopでも使われてるTask Runner ● YAMLでタスク定義を書けるので、学習コストが低い ● 細かい単位でタスクを定義するので、シェル芸⼈しかメンテできないスクリプトが⽣まれにくい 閑話休題: taskfile.devが便利 閑話休題

⾟さ②: エラーハンドリング

© LayerX Inc. 29 ● 5xxエラーやRate Limitなど外部要因のエラーによってdbtの実⾏が失敗する ● また、⼤量のデータを取得‧送信するときに途中でエラーが起きるとゼロから再実⾏になる ○ dbtは処理途中のデータを保存しない ○ 特にデータ送信では、データ重複の原因になるので避けたい 外部要因のエラーがそれなりに起きる ⾟さ②: エラーハンドリング

© LayerX Inc. 30 ● 可能な限りincrementalモデルを使⽤し、細かい単位で処理結果をストアできる状態を作る ● エラーが発⽣したら、処理したところまでデータを格納し、後続モデルが実⾏されるようにする ● 正しくデータが処理されているかどうかは、エラーログから検出して対応する ● 取得データの性質によっては、この戦略が使えないので悩ましい... 解決策: エラーが起きたら、途中結果をストアし、成功させる ⾟さ②: エラーハンドリング

© LayerX Inc. 31 ● Snowflakeでdbt Python Modelを動かした場合、ログはEventテーブル※ に格納される ● Eventテーブルに定期的にクエリを投げて、条件に合致したレコードをSlackに通知している エラーを検出して通知する（Snowflake編） ⾟さ②: エラーハンドリング ※ Snowflake Event table overview https://docs.snowflake.com/en/developer-guide/logging-tracing/event-table-setting-up

⾟さ③: 環境差分吸収

© LayerX Inc. 33 ● [CT-997] [Feature] Support dbt.var + dbt.env_var in Python models · Issue #5617 · dbt-labs/dbt-core · GitHub ● Jinjaも使えないので、環境差分吸収が難しい dbt Python Modelはvarsが使えない ⾟さ③: 環境差分吸収 dbtはcompile時にPython Modelを実⾏して評価するわけではないので、こういった動的定義もエラーになる

© LayerX Inc. 34 ● 現時点で実⾏時に動的に値を渡すにはschema.ymlで定義するしかない ● schemaのconfigでPython Modelに渡したい変数を定義し、Python Model側で受け取る 解決策: schema.ymlから渡す ⾟さ③: 環境差分吸収

おわりに

© LayerX Inc. 36 もし今日の話を聞いて「面白そうなやつだ」と思ったら x.com/civitaspo のフォローをお願いします！ もし今日の話を聞いて「もっと話したい！」と思ったら「civitaspo layerx カジュアル面談」で検索！検索！ データエンジニアもアナリティクスエンジニアもアナリストも全方位、採用強化中です！ 興味のある方は「jobs.layerx.co.jp」から採用情報を見てみてください！！ ご視聴ありがとうございました！ おわりだよ〜 おわり

おわり