Snowflake社が公開、展開しているdbt Cloudとの連携実践チュートリアル「Accelerating Data Teams with Snowflake and dbt Cloud Hands On Lab」(Snowflakeとdbtクラウドでデータチームを加速するハンズオンラボ)。直近(2025年07月)時点での環境で一通り実践してみました。
当エントリでは実施した一連の手順のうち、「9. Transformed Models」(マートモデルの作成) の内容について実践と解説を行っていきます。
※実施手順の全容については下記をご参照ください。
※これまでの実践内容一覧は以下をご参照ください。
- Snowflake&dbt Cloud Quickstart 実践 #1〜#5「実践環境の準備・導入」
- Snowflake&dbt Cloud Quickstart 実践 #6「dbtプロジェクトの基本構造」
- Snowflake&dbt Cloud Quickstart 実践 #7「ソースデータ設定とステージング層モデルの作成」
- Snowflake&dbt Cloud Quickstart 実践 #8「シードと増分マテリアライゼーション」
実践#9: マートモデルの作成
前エントリまでの内容でステージング層のモデルが構築できたので、モデリングニーズを満たすためにデータの変換を開始できるようになりました。
この後は『新しい行項目の計算を実行する中間モデル』の作成と『新しい行項目の計算を注文レベルに集約するファクトモデル』の作成を進めていきます。最後のファクトモデルは、Snowsightでチャートを作成するために使用します。
中間モデルの作成
int_order_items.sqlというファイルをmodels/marts/coreの配下に作成し、以下のSQL文を記載して保存。
with orders as (
select * from {{ ref('stg_tpch_orders') }}
),
line_item as (
select * from {{ ref('stg_tpch_line_items') }}
)
select
line_item.order_item_key,
orders.order_key,
orders.customer_key,
orders.order_date,
orders.status_code as order_status_code,
line_item.part_key,
line_item.supplier_key,
line_item.return_flag,
line_item.line_number,
line_item.status_code as order_item_status_code,
line_item.ship_date,
line_item.commit_date,
line_item.receipt_date,
line_item.ship_mode,
line_item.extended_price,
line_item.quantity,
-- extended_price is actually the line item total,
-- so we back out the extended price per item
(line_item.extended_price/nullif(line_item.quantity, 0))::decimal(16,2) as base_price,
line_item.discount_percentage,
(base_price * (1 - line_item.discount_percentage))::decimal(16,2) as discounted_price,
line_item.extended_price as gross_item_sales_amount,
(line_item.extended_price * (1 - line_item.discount_percentage))::decimal(16,2) as discounted_item_sales_amount,
-- We model discounts as negative amounts
(-1 * line_item.extended_price * line_item.discount_percentage)::decimal(16,2) as item_discount_amount,
line_item.tax_rate,
((gross_item_sales_amount + item_discount_amount) * line_item.tax_rate)::decimal(16,2) as item_tax_amount,
(
gross_item_sales_amount +
item_discount_amount +
item_tax_amount
)::decimal(16,2) as net_item_sales_amount
from
orders
inner join line_item
on orders.order_key = line_item.order_key
order by
orders.order_date
このモデルで何が為されているのかを分解してみます。まず最初に、ref関数(3行目、9行目)を使用して2つのcteのステージングモデルからすべてのデータを選択しています。
メインとなるselect文は、2つのステージングモデルを結合し、既存のカラムのチャンクをプルし、ラインアイテムのデータに対して多くの異なる計算を実行しています。すべての計算は、私たちが興味を持っているデータポイントであり、割引を含む個々の商品の価格や税金を含む総売上金額など、生のデータソースでは計算されませんでした。
ref関数はdbtにおいて最も重要な関数で、前エントリで使っていたsource関数に似ています。
今回はステージングモデルを参照する形となりました。原則として、モデルを構築する際には常にref関数を使用して既存のdbtモデルを参照する必要があります。これは、dbtモデル間の依存関係を作成したり、異なる環境間でコードをシームレスにプロモートするために重要です。dbtにおいてコードを開発環境から本番環境にデプロイするとき、ref関数は（接続とプロジェクトの両方の）設定に基づいて、本番環境に関連する正しいデータベースオブジェクトをコンパイルします。
中間モデルを作成・保存した時点で、IDEで表示されるリネージは以下のような表示となっています。
マートモデルの作成
最終的な変換ファクトモデルを作成します。fct_orders.sqlというファイルをmodels/marts/core配下に作成。
このモデルは、ステージングされたオーダーデータと、変換されたオーダーアイテムデータをそれぞれref関数を使って取り込むことから始まっています。
その後、新しい注文項目の計算を行い、注文レベルでそれらの値を集計してから、注文レベルの属性と結合し、最終的な結果を変換の上出力しています。その結果、ステージング・データだけではできなかった、対応する割引額や税額を含む注文をレポートすることができるようになりました。
with orders as (
select * from {{ ref('stg_tpch_orders') }}
),
order_item as (
select * from {{ ref('int_order_items') }}
),
order_item_summary as (
select
order_key,
sum(gross_item_sales_amount) as gross_item_sales_amount,
sum(item_discount_amount) as item_discount_amount,
sum(item_tax_amount) as item_tax_amount,
sum(net_item_sales_amount) as net_item_sales_amount
from order_item
group by
1
),
final as (
select
orders.order_key,
orders.order_date,
orders.customer_key,
orders.status_code,
orders.priority_code,
orders.clerk_name,
orders.ship_priority,
1 as order_count,
order_item_summary.gross_item_sales_amount,
order_item_summary.item_discount_amount,
order_item_summary.item_tax_amount,
order_item_summary.net_item_sales_amount
from
orders
inner join order_item_summary
on orders.order_key = order_item_summary.order_key
)
select
*
from
final
order by
order_date
このモデルは、ステージングされたオーダーデータと、変換されたオーダーアイテムデータをそれぞれref関数を使って取り込むことから始まります。その後、新しい注文項目の計算を行い、注文レベルでそれらの値を集計し、最終的に変換された出力を得るために注文レベルの属性と結合します。その結果、ステージング・データだけではできなかった、対応する割引額や税額を含む注文をレポートすることができるようになりました。
モデルのビルドと出力結果
ハンズオンにおける一連のモデル群の作成が完了したので、これらのモデルを開発スキーマに構築するために、もう一回dbtを実行してみます。今回はdbt runで全てのモデルを実行するのではなく、当エントリ内で作成した『新しい中間モデルとファクトモデル』のみをビルドをします。
この場合のコマンド指定はdbt run --select int_order_items+ となります。
このコマンドにはいくつかのコンセプトが含まれています。
- すでに
--select引数を使って、引数の後に指定したモデルやパス（この場合はint_order_itemsモデル）を実行するようにdbtに指示しました。 -
int_order_itemsの末尾に付加されたプラス記号はグラフ演算子で、dbtの依存関係グラフを使用して、選択されたモデルの下流のすべてのモデルを実行します。つまり、この場合、dbtはint_order_itemsとそのモデルの下流のすべての依存関係を実行します。
グラフ演算子については下記ドキュメントをご参照ください。
コンパイルされたコードで何が起こっているのかを理解するために、fct_ordersモデルの詳細な結果を見てみましょう。まず、dbt_project.ymlファイルを作成したときに、martsフォルダ内のすべてのモデルが指定したウェアハウス(今回の場合だとshinyaa31_sandbox_wh_mart)を使用して実行されるように設定しました。その設定がここに生きています。
また、モデル自体についてもいくつか指摘すべき点があります。1つ目は、dbtがDDLでselect文をラップし、開発スキーマにテーブルを構築してくれることです。dbt_project.ymlファイルのmaterialized設定は、デフォルトのビューのマテリアライズ化とは対照的に、これをテーブルとして構築する役割を担っています。
ここでのもう1つの重要な点は、最初の2つのcteのref文がどのように適切なデータベースオブジェクトにコンパイルされているかです。ここでは、開発環境でモデルを構築していることを考慮して、開発スキーマのstg_tpch_ordersモデルとint_order_itemsモデルを参照するようにdbtがコードをコンパイルしていることがわかります。
リネージ(Lineage)の確認
これですべてのモデルのビルドが完了しました。(都度リネージグラフの表記も見てきましたが、)今回の一連の流れの最終形はfct_ordersモデルタブを開いた状態でIDEのlineageタブをクリックすると見ることが出来ます。
左から右に、モデル群の大本となるソース(緑のノード)、そしてステージングモデル/中間モデル/最終的なファクトモデルが連動しています。dbt でソースを宣言し、ref 関数と source 関数を使用することで、dbt はこれらのリレーションを作成することができます。プロジェクトの規模が大きくなり、数百のモデルが含まれるようになると、この機能は非常に強力に効果を発揮します。
以上で当ステップでの作業は終了です。(※そういえば敢えて事前にブランチを切らなくても良くなってる=ブランチを切ってない状態でもコードや設定の修正が可能になっているなぁ...)
まとめ
というわけで、Snowflake社提供のSnowflake&dbt Cloud実践チュートリアル「Accelerating Data Teams with Snowflake and dbt Cloud Hands On Lab」(Snowflakeとdbtクラウドでデータチームを加速するハンズオンラボ)の実践編、#9「マートモデルの作成」の紹介でした。
次のエントリ「#10」ではテストとドキュメントについて見ていきます。
参考：