Snowflake & dbt Cloudハンズオン実践 #6: 『実践編3(マートモデルの作成)』 #snowflakeDB #dbt

アライアンス事業部 エンジニアグループ　モダンデータスタック(MDS)チームの しんや です。

Snowflakeが展開しているサイト『Snowflake Quickstarts』では、Snowflake単体、またSnowflakeと他サービスとの連携について実戦形式で手を動かしながら学んでいけるコンテンツが多数公開されています。

その中の1つ『Accelerating Data Teams with Snowflake and dbt Cloud Hands On Lab(Snowflake と dbt Cloud ハンズオン ラボを使用してデータ チームを加速する)』は、dbt CloudとSnowflakeを連携させる形で、Snowflakeのデータを使ってdbt Cloudでデータ変換の処理を作り上げていく流れを学ぶことが出来る非常に参考になるコンテンツです。

当エントリ及び一連のエントリ群では、この一連の手順を実際に手を動かしながら進めた記録をまとめて行こうと思います。

第6弾の当エントリでは『実践編3(マートモデルの作成)』パートについて実践内容を紹介します。

目次

• Step09. マートモデルの作成
  • 中間モデルの作成
  • マートモデルの作成
  • モデルのビルドと出力結果
  • リネージ(Lineage)
• まとめ

Step09. マートモデルの作成

前エントリまでの内容でステージング層のモデルが構築できたので、モデリングニーズを満たすためにデータの変換を開始できるようになりました。

今後進めていくことは『新しい行項目の計算を実行する中間モデル』の作成と『新しい行項目の計算を注文レベルに集約するファクトモデル』の作成です。最後のファクトモデルは、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のステージングモデルからすべてのデータを選択しています。

• About ref function | dbt Developer Hub

メインとなるselect文は、2つのステージングモデルを結合し、既存のカラムのチャンクをプルし、ラインアイテムのデータに対して多くの異なる計算を実行しています。すべての計算は、私たちが興味を持っているデータポイントであり、割引を含む個々の商品の価格や税金を含む総売上金額など、生のデータソースでは計算されませんでした。

ref関数はdbtにおいて最も重要な関数で、前エントリで使っていたsource関数に似ています。

• About source function | dbt Developer Hub

今回はステージングモデルを参照する形となりました。原則として、モデルを構築する際には常に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

モデルのビルドと出力結果

ハンズオンにおける一連のモデル群の作成が完了したので、これらのモデルを開発スキーマに構築するために、もう一回dbtを実行してみます。今回はdbt runで全てのモデルを実行するのではなく、当エントリ内で作成した『新しい中間モデルとファクトモデル』のみをビルドをします。

この場合のコマンド指定はdbt run --select int_order_items+ となります。

int_order_itemsの末尾に追加されたプラス記号は、dbtの依存関係グラフを使用して、選択されたモデルの下流のすべてのモデルを実行するグラフ演算子です。つまりこの場合、dbtはint_order_itemsとそのモデルの下流にあるすべての依存関係を実行します。

グラフ演算子については下記ドキュメントをご参照ください。

コンパイルされたコードで何が起こっているのかを理解するために、fct_ordersモデルの詳細な結果を見てみましょう。まず、dbt_project.ymlファイルを作成したときに、martsフォルダ内のすべてのモデルがpc_dbt_wh_large warehouseを使用して実行されるように設定しました。その設定がここに生きています。

モデル自体についてもいくつか指摘すべき点がある。1つ目は、dbtがDDLでselect文をラップし、開発スキーマにテーブルを構築してくれることです。dbt_project.ymlファイルのマテリアライズド設定は、デフォルトのビューのマテリアライズ化とは対照的に、これをテーブルとして構築する役割を担っています。

もう1つの重要な点は、最初の2つのcteのref文がどのように適切なデータベースオブジェクトにコンパイルされているかです。ここでは、開発環境でモデルを構築していることを考慮して、開発スキーマのstg_tpch_ordersモデルとint_order_itemsモデルを参照するようにdbtがコードをコンパイルしていることがわかります。

リネージ(Lineage)

これですべてのモデルのビルドが完了しました。(都度リネージグラフの表記も見せてきましたが)、今回の一連の流れの最終形はfct_ordersモデルタブを開いた状態でIDEのlineageタブをクリックすると見ることが出来ます。

左から右に、モデル群の大本となるソース(緑のノード)、そしてステージングモデル/中間モデル/最終的なファクトモデルが連動しています。dbt でソースを宣言し、ref 関数と source 関数を使用することで、dbt はこれらのリレーションを作成することができます。プロジェクトの規模が大きくなり、数百のモデルが含まれるようになると、この機能は非常に強力に効果を発揮します。

まとめ

という訳で、Snowflake QuickStarts『Accelerating Data Teams with Snowflake and dbt Cloud Hands On Lab』実践第6弾、dbt Cloud実践編3(マートモデルの作成)に関する内容の紹介でした。

次エントリ(第7弾)ではdbt Cloudでのモデル作成実践編その4(テスト＆ドキュメント)に関するパートを進めていきます。

• dbt の記事一覧 | DevelopersIO
  • dbt Cloud の記事一覧 | DevelopersIO
• Snowflake の記事一覧 | DevelopersIO