BigQuery + dbtのデータ基盤でコスト削減のためにやったこと

BigQuery + dbtのデータ基盤でコスト削減の ためにやったこと 2025年10月24日 株式会社コドモン 第4回福岡データエンジニアリング勉強会（LT）

自己紹介 mottake3 経歴 2025年7月入社。 データ系のチームにて、主にデータ基盤まわりの運用をやってます。 最近興味があるのはSalesforceなどのビジネスプロセスに関わるシ ステムと、データ基盤やAIをかけ合わせたイネーブルメント。 住まいは東京ですが、ラーメンはバリカタ、うどんはバリやわの福岡 人。 2

Mission 3

こんなプロダクトを開発しています すべての先生に 子どもと向き合う 時間と心のゆとりを メインプロダクトは、保育・教育施設向けWebアプリケーション。 保護者と施設のやり取りを支えるモバイルアプリケーションや、施設職員向けモバイル版 アプリケーション、外部サービスと連携するAPIなども開発しています。 4

今日話すこと 1 課題 2 BigQuery Editionsへの切り替えとslot数の調整 3 dbtのモデルのリファクタリング 4 dbt cloudのCICDジョブの調整 5 おわりに 3 5

1. 課題 データ基盤のコストが高い 今日1日にかかったコストを考えると 夜寝れなくなるくらい CONFIDENTIAL - © 2022 CoDMON Inc. 6 6

1. 課題 今日はデータ基盤でのコスト削減で取り組んだことのTipsを シェアします もっと良い方法ご存知でしたら遠慮なく突っ込んでください! BigQuery+dbtについての話になるのですが、他社製品の場合 でも考え方は参考になるかもです CONFIDENTIAL - © 2022 CoDMON Inc. 7 7

今日話すこと 1 課題 2 BigQuery Editionsへの切り替えとslot数の調整 3 dbtのモデルのリファクタリング 4 dbt cloudのCICDジョブの調整 5 おわりに 8

2. BigQuery Editionsへの切り替えとslot数の調整 社内的にクエリジョブの発生源には大きく3パターン ● 通常業務でのクエリ：散発的 ● dbt cloudでのデイリーでのmodel buildジョブ：夜間に1時間ほど ● dbt開発でのCICDジョブ:散発的だがオンデマンドでも平均数分かかる CONFIDENTIAL - © 2022 CoDMON Inc. 9 9

1. BigQuery Editionsへの切り替えとslot数の調整 社内的にクエリジョブの発生源には大きく3パターン ● 通常業務でのクエリ：散発的 →max_slots低くて問題ない ● dbt cloudでのデイリーでのmodel buildジョブ：夜間に1時間ほど →高くしたい ● dbt開発でのCICDジョブ:散発的だがオンデマンドでも平均数分かかる →高くしたい CONFIDENTIAL - © 2022 CoDMON Inc. 10 10

1. BigQuery Editionsへの切り替えとslot数の調整 dbtジョブの実行に合わせてmax_slotの変更をフックするようにした ● 以下のようなクエリでmax_slotsを変更するdbtのmacroを作成し、 buildの前後に実行 ALTER RESERVATION `ADMIN_PROJECT_ID.region- LOCATION.RESERVATION_NAME`SET OPTIONS ( slot_capacity= NUMBER_OF_BASELINE_SLOTS, autoscale_max_slots= NUMBER_OF_AUTOSCALING_SLOTS); https://cloud.google.com/bigquery/docs/reservations-tasks#change_the_size_of_a_reservation CONFIDENTIAL - © 2022 CoDMON Inc. 11 11

• https://cloud.google.com/bigquery/docs/reservations-tasks#change_the_size_of_a_reservation

1. BigQuery Editionsへの切り替えとslot数の調整 adhocに大きめのmax_slotが必要なとき ●別途大きめのreservationを作成し切り替えられるようにした(pre-GAの機 能なので注意は必要) SET @@reservation=' RESERVATION'; SELECT QUERY; https://cloud.google.com/bigquery/docs/reservations-assignments#reservation CONFIDENTIAL - © 2022 CoDMON Inc. 12 12

• https://cloud.google.com/bigquery/docs/reservations-assignments#reservation

1. BigQuery Editionsへの切り替えとslot数の調整 補足 ●当初はReservationを複数作成してassignmentを切り替える方法を考えたが 以下のブロック要因があったので深追いせず方針転換した →assignmentの更新がドキュメント上cliしか記載がない →assignmentの作成、削除はsqlでできるが作成後に5分のwaitが必要 https://cloud.google.com/bigquery/docs/reservations-assignments#assign-organization https://cloud.google.com/bigquery/docs/reservations-assignments#move_an_assignment_to_a_dif ferent_reservation CONFIDENTIAL - © 2022 CoDMON Inc. 13 13

• https://cloud.google.com/bigquery/docs/reservations-assignments#assign-organization
• https://cloud.google.com/bigquery/docs/reservations-assignments#move_an_assignment_to_a_different_reservation

1. BigQuery Editionsへの切り替えとslot数の調整 補足 ●dbt cloudはジョブがエラーになった際に、別のエラー処理用のジョブを起 動するような機構が存在しない(調べた限り) ●そのためCIジョブなどでエラーが発生するとmax_slotが高いままの状態に なってしまう。 ●ジョブがエラー時にwebhookする仕組みは存在するので、外部のコールバッ ク関数を呼びその中でmax_slotをデフォルト値に戻すようにした。 https://docs.getdbt.com/docs/deploy/webhooks CONFIDENTIAL - © 2022 CoDMON Inc. 14 14

• https://docs.getdbt.com/docs/deploy/webhooks

今日話すこと 1 課題 2 BigQuery Editionsへの切り替えとslot数の調整 3 dbtのモデルのリファクタリング 4 dbt cloudのCICDジョブの調整 5 おわりに 15

2.dbtのモデルのリファクタリング 夜間のビルドに一時間以上かかっているモデルがあった ● デイリーの増分テーブルのリネージの上流に多段ビューがあり、毎回フルス キャンでクエリが流れていた ●SCD Type2のテーブルから完全な履歴に復元するヘビーな処理が毎回走って いた CONFIDENTIAL - © 2022 CoDMON Inc. 16 16

2.dbtのモデルのリファクタリング 夜間のビルドに一時間以上かかっているモデルがあった ● デイリーの増分テーブルのリネージの上流に多段ビューがあり、毎回フルス キャンでクエリが流れていた →物理テーブル化してパーティションが効くように ●SCD Type2のテーブルから完全な履歴に復元するヘビーな処理が毎日走って いた →テーブル関数を利用して必要な前日分のみ処理対象にする CONFIDENTIAL - © 2022 CoDMON Inc. 17 17

2.dbtのモデルのリファクタリング SCD Type2のテーブルから完全な履歴に復元するヘビーな処理が毎日走っていた 今2025/10/24とすると。。。 product_id product_name unit_price P001 コーヒー 600 2025/10/22 P001 コーヒー 700 2025/10/23 NULL P002 ミルクティー 500 2025/10/22 NULL CONFIDENTIAL - © 2022 CoDMON Inc. dbt_valid_from dbt_valid_to 2025/10/23 as_of_date product_id product_name unit_price 2025/10/22 P001 コーヒー 600 2025/10/22 P002 ミルクティー 500 2025/10/23 P001 コーヒー 700 2025/10/23 P002 ミルクティー 500 2025/10/24 P001 コーヒー 700 2025/10/24 P002 ミルクティー 500 18 18

2.dbtのモデルのリファクタリング
テーブル関数とは

● パラメータで引数を渡すことができるビュー
● as_of_dateの範囲をパラメータで渡し、必要な範囲の履歴の復元をする。
例:

sample_tvf.sql

tvfを呼び出すクエリ

{{

select *
from sample_tvf(“2025-10-23”,“2025-10-24”)

config(
materialized="table_function",
params="start_dt DATE, end_dt DATE",
)

クエリ結果

}}
select as_of_date
from unnest(generate_date_array(start_dt, end_dt)) as
as_of_date

● dbtのmodelとして定義するにはcustom materializationsとして定義が必
要。 https://cloud.google.com/bigquery/docs/table-functions
https://docs.getdbt.com/guides/create-new-materializations?step=1
CONFIDENTIAL - © 2022 CoDMON Inc.

19

19

• https://cloud.google.com/bigquery/docs/table-functions
• https://docs.getdbt.com/guides/create-new-materializations?step=1

2.dbtのモデルのリファクタリング
補足:テーブル関数のcustom materializationsサンプル

macros/materializations/table_function.sql
{%- materialization table_function, adapter='bigquery' -%}
{%- set params = config.get('params', '') -%}
{%- set relation = api.Relation.create(identifier=model.alias,
schema=model.schema, database=model.database, type="view") -%}
{% set funcname = "`" + ([relation.database, relation.schema,
relation.identifier] | join(".")) + "`" %}
{% call statement('main') -%}
CREATE OR REPLACE TABLE FUNCTION {{ funcname }}({{ params
}})
AS (
{{ sql }}
)
{%- endcall %}
{{ return({'relations': [relation]}) }}
{%- endmaterialization -%}
CONFIDENTIAL - © 2022 CoDMON Inc.

20

20

2. dbtのモデルのリファクタリング テーブル ビュー 増分テーブ ル テーブル関 数 AS IS データソースA dbt snapshot (scd type2) 中間ビュー 形式的にはscd type2のまま。 scd type2を全期間 復元 ※プロダクトはDBが2つに分割されている DBごとのテー ブルを union データソースB dbt snapshot (scd type2) 中間ビュー 形式的にはscd type2のまま。 増分テーブル scd type2を全期間 復元 TO BE データソース A dbt snapshot (scd type2) 中間テーブル dbt_valid_toにパー ティション テーブル関数 データソース B dbt snapshot (scd type2) CONFIDENTIAL - © 2022 CoDMON Inc. 増分テーブル 中間テーブル dbt_valid_toにパー ティション 21 21

今日話すこと 1 課題 2 BigQuery Editionsへの切り替えとslot数の調整 3 dbtのモデルのリファクタリング 4 dbt cloudのCI/CDジョブの調整 5 おわりに 22

4. dbt cloudのCI/CDジョブの調整 dbt buildで差分を検知する--select state:modified+ コマンドであまり関係のないモデ ルまでビルドされてしまっている。 ●「差分があり」と判定する方法を制御するパラメータがあるので、自社の用途に合わせ てCIのカバレッジを調整した。 criteria state:modified.body state:modified.configs 変更検知するリソース モデルのSQL、シード値などの変更 config設定の変更 (database,schema,identifier,aliasを除く) state:modified.relation state:modified.persisted_descriptions state:modified.macros state:modified.contract database,schema,identifier,aliasの変更 descriptionの変更 マクロの変更 contractの変更 CONFIDENTIAL - © 2022 CoDMON Inc. 23 23

4. dbt cloudのCI/CDジョブの調整 ちなみに ●↓のように半角スペースで複数指定すると条件がunionされる。 dbt build --select "state:modified.body+ state:modified.persisted_descriptions" ●↓のようにカンマ区切りで指定するとIntersectionsされる。 dbt build --select "state:modified.body+,state:modified.persisted_descriptions" https://docs.getdbt.com/reference/node-selection/set-operators CONFIDENTIAL - © 2022 CoDMON Inc. 24 24

• https://docs.getdbt.com/reference/node-selection/set-operators

4. dbt cloudのCI/CDジョブの調整 補足 ●dbt cloudのstate:modifiedは前回成功したjobで出力された manifest.json(terraformのstateファイルのようなもの)が差分の判定に利用される。最 新のmanifest.jsonではない(厳密にはDeferの仕様) ●そのため前回成功したjobの中でmanifest.jsonを書き出すコマンド(dbt build, dbt run, dbt parse等)が存在しないとmanifest.jsonが存在しないのでエラーになるので注 意。 https://docs.getdbt.com/reference/node-selection/set-operators CONFIDENTIAL - © 2022 CoDMON Inc. 25 25

• https://docs.getdbt.com/reference/node-selection/set-operators

4. dbt cloudのCI/CDジョブの調整 補足 ●まだbetaですがstate-aware orchestrationという機能もいま開発されているそう。 ●これはモデルに対してデータの鮮度(freshness)を定義して、freshnessが満たせてい ないモデルのみbuildの対象にできるそう。将来的には主流になるかも。 https://docs.getdbt.com/docs/deploy/state-aware-about https://docs.getdbt.com/reference/resource-configs/freshness CONFIDENTIAL - © 2022 CoDMON Inc. 26 26

• https://docs.getdbt.com/docs/deploy/state-aware-about
• https://docs.getdbt.com/reference/resource-configs/freshness

4.おわりに 今回の内容で弊社ではざっくり 30〜40％ほどコストは下がりました。 が、まだまだできることはたくさん残っている状況。 お客様に継続的に適正価格でサービス提供し続けるために も、エンジニアもアウトカムだけでなくコストもしっかり意 識していきましょう! CONFIDENTIAL - © 2022 CoDMON Inc. 27 27