1. はじめに
こんにちは．株式会社フェズ開発本部でデータエンジニアをしています志賀です．
今回は，dbtを題材に記事を書こうと思います．
dbtを使っていると，モデルが気づけば "長いCTEの連続"になり，全体がブラックボックス化してしまうことがあります．
今回は，私が実際に取り組んだ「複雑なロジックをもつdbtモデルをどう分割し，テストしやすい形にしたか」という実践例をご紹介します．
この記事は，3部作のうちはじめの記事となります．
- 複雑なdbtモデルを分割する ← いまここ
- 分割したモデルをテストする
- おまけ~失敗談~
2. 背景と目的
早速ですが，2つのdbt モデルを見比べてみましょう．
生成AIに"複雑そうにみえる"SQLを作ってもらいました．
(このSQLは、ユーザーごとの複数スコアを持つ配列データを展開・正規化・集計して、最終的にJSON形式でまとめる処理を行うものです)
実際に業務で直面したクエリはより複雑で，CTEの数が多くネストが深いモデルでした.
before
{{config(
materialized="incremental",
incremental_strategy="merge",
unique_key="user_id",
alias="macro_table"
)
}}
WITH
-- 01: 元データ（STRUCT 配列）
cte_01 AS (
SELECT
GENERATE_UUID() AS user_id,
[
STRUCT('A' AS code, 10 AS score),
STRUCT('B' AS code, 20 AS score),
STRUCT('C' AS code, 30 AS score)
] AS metrics,
CURRENT_DATE() AS dt
),
-- 02: STRUCT 配列を UNNEST して展開
cte_02 AS (
SELECT
user_id,
dt,
metric.code AS code,
metric.score AS score
FROM cte_01,
UNNEST(metrics) AS metric
),
-- 03: スコアの正規化処理（全体に占める比率）
cte_03 AS (
SELECT
user_id,
code,
score,
SAFE_DIVIDE(score, SUM(score) OVER(PARTITION BY user_id)) AS norm_score,
dt
FROM cte_02
),
-- 04: ARRAY_AGG + STRUCT で再構築
cte_04 AS (
SELECT
user_id,
ARRAY_AGG(
STRUCT(
code,
score,
norm_score
)
ORDER BY code
) AS metrics_summary,
dt
FROM cte_03
GROUP BY user_id, dt
),
-- 05: JSON 形式へ整形
cte_05 AS (
SELECT
user_id,
dt,
TO_JSON_STRING(metrics_summary) AS metrics_json,
metrics_summary
FROM cte_04
)
SELECT
user_id,
dt,
metrics_json,
metrics_summary
FROM cte_05;
どうでしょうか．パッと見で読むのが嫌になりますよね...
みなさんも感じていただけたと思うのですが，beforeのdbtモデルには次のような課題がありました．
- 可読性が低い
- 複雑さゆえに，修正や変更に弱い作りになっている
- テストが作りにくい
- 1モデルで単一の機能をもつ作りだとしても，その機能を構成するロジックが多い
本記事は，こうした課題の解消を目指す取り組みとなります．
3. 結論
早速結論です．
次のdbtモデルをみてください．
after
-- 一部のロジックを外部で管理する形式に変更したクエリ
{{config(
materialized="incremental",
incremental_strategy="merge",
unique_key="user_id",
alias="macro_table"
)
}}
WITH
-- 01: 元データ（STRUCT 配列）
cte_01 AS (
SELECT
GENERATE_UUID() AS user_id,
[
STRUCT('A' AS code, 10 AS score),
STRUCT('B' AS code, 20 AS score),
STRUCT('C' AS code, 30 AS score)
] AS metrics,
CURRENT_DATE() AS dt
),
-- 02: STRUCT 配列の展開 → macro 化
cte_02 AS ({{ metric_expand() }}),
-- 03: 正規化処理 → macro 化
cte_03 AS ({{ metric_normalize() }}),
-- 04: ARRAY_AGG + STRUCT 化 → macro 化
cte_04 AS ({{ metric_summary_struct() }}),
-- 05: JSON 形式へ変換（通常 CTE）
cte_05 AS (
SELECT
user_id,
dt,
TO_JSON_STRING(metrics_summary) AS metrics_json,
metrics_summary
FROM cte_04
)
SELECT
user_id,
dt,
metrics_json,
metrics_summary
FROM cte_05;
Afterのdbtモデルでは，CTEの途中で生成されるテーブルをmacro化しています．
コアとなるロジックをmacro化し外部で管理することで，元のdbtモデルをスリムにすることができます．
また，macro化されたモデルは他のmacroと同様に次のように管理されます．
macro cte_02
{% macro metric_expand() %}
(
SELECT
user_id,
dt,
metric.code AS code,
metric.score AS score
FROM cte_01,
UNNEST(metrics) AS metric
)
{% endmacro %}
macro cte_03
{% macro metric_normalize() %}
(
SELECT
user_id,
code,
score,
SAFE_DIVIDE(score, SUM(score) OVER(PARTITION BY user_id)) AS norm_score,
dt
FROM cte_02
)
{% endmacro %}
macro cte_04
{% macro metric_summary_struct() %}
(
SELECT
user_id,
ARRAY_AGG(
STRUCT(code, score, norm_score)
ORDER BY code
) AS metrics_summary,
dt
FROM cte_03
GROUP BY user_id, dt
)
{% endmacro %}
macro化されたCTEの役割が明確になることで可読性があがり，かつ元のdbtモデルがスリムになったと思います！
macroを採用した理由は，第2部の”分割したモデルをテストする”でもう少し詳しく触れます．
4. macroの展開
macroの中身をみて，”おや”っと思われた方もいらっしゃるのではないでしょうか？
macro内のFROM句で，親CTEで定義されたテーブル名を参照しているじゃん！と．
ここが今回私もはじめて知った挙動です．
dbtのmacroでは挿入した部分にmacro内部の処理がインライン展開される挙動となります．
ですので，親CTEで定義済みのテーブル名もそのままモデルのCTE内に展開され，参照可能となります．
もちろんこれでdbt runコマンドは成功しますし，実際にコンパイルした結果は次の通りとなります．
dbt run --select models/test_macro
09:35:13 Running with dbt=1.10.15
09:35:15 Registered adapter: bigquery=1.10.3
09:35:16 Found 1 model, 711 macros
09:35:16
09:35:16 Concurrency: 4 threads (target='dev')
09:35:16
09:35:17 1 of 1 START sql incremental model test_dataset.macro_table ............. [RUN]
09:35:19 1 of 1 OK created sql incremental model test_dataset.macro_table ........ [CREATE TABLE (1.0 rows, 0 processed) in 2.42s]
09:35:19
09:35:19 Finished running 1 incremental model in 0 hours 0 minutes and 3.30 seconds (3.30s).
09:35:19
09:35:19 Completed successfully
compiled_query
WITH
-- 01: 元データ（STRUCT 配列）
cte_01 AS (
SELECT
GENERATE_UUID() AS user_id,
[
STRUCT('A' AS code, 10 AS score),
STRUCT('B' AS code, 20 AS score),
STRUCT('C' AS code, 30 AS score)
] AS metrics,
CURRENT_DATE() AS dt
),
-- 02: STRUCT 配列の展開 → macro 化
cte_02 AS (
(
SELECT
user_id,
dt,
metric.code AS code,
metric.score AS score
FROM cte_01,
UNNEST(metrics) AS metric
)
),
-- 03: 正規化処理 → macro 化
cte_03 AS (
(
SELECT
user_id,
code,
score,
SAFE_DIVIDE(score, SUM(score) OVER(PARTITION BY user_id)) AS norm_score,
dt
FROM cte_02
)
),
-- 04: ARRAY_AGG + STRUCT 化 → macro 化
cte_04 AS (
(
SELECT
user_id,
ARRAY_AGG(
STRUCT(code, score, norm_score)
ORDER BY code
) AS metrics_summary,
dt
FROM cte_03
GROUP BY user_id, dt
)
),
-- 05: JSON 形式へ変換（通常 CTE）
cte_05 AS (
SELECT
user_id,
dt,
TO_JSON_STRING(metrics_summary) AS metrics_json,
metrics_summary
FROM cte_04
)
SELECT
user_id,
dt,
metrics_json,
metrics_summary
FROM cte_05
5. おわりに
この記事では，”複雑なロジックをもつdbtモデルをどう分割し、テストしやすい形にしたか”をテーマに，まずは"dbtモデルの分割"に焦点をあてご紹介しました！
macro化されたことでCTEの役割が明確になり，また元のdbtモデルがスリムになったことで可読性も向上するメリットがあったと思います．
一方で，無闇にCTEを外部管理すればよいというわけではない，と私は考えています．
大切なのは，モデルのコアロジックは何かを見定めた上で，何をテストするのか，担保したい品質とは，を考えながらモデルをリファクタリングすることが肝心．
これも，開発業務の実体験から得た知見なので補記させていただきます．
6. 次回予告
さて次の記事では，"分割したdbtモデルをどのようにテスト可能にしたのか", また，なぜmacroを採用したのかその理由についてもまとめていきたいと思います！
フェズは、「情報と商品と売場を科学し、リテール産業の新たな常識をつくる。」をミッションに掲げ、リテールメディア事業・リテールDX事業を展開しています。 fez-inc.jp/recruit
Discussion