😸

dbtベストプラクティスを読む~dbt projects構造編~

2023/09/30に公開

dbt

tech

「あれ？そう言えば今の開発ってdbtのベストプラクティスに準拠してるっけ？」こんな思いを最近してたので
今更ながらdbtのベストプラクティスを調べて、そのメモ書きになります。

要約

このセクションだけでも結構なボリュームがあるので、時間がなくて要点だけ知りたいって人はここ見ればOKです。

Stagingレイヤー

基本Stagingレイヤーでの集計や結合は非推奨、カラムのリネーム、型変換、基本的な計算は推奨
ファイル名に取得元情報を記載しよう
ソースデータ単位でサブティレクリを切りましょう（ビジネスグループごとに切るにはNG）
テーブルタイプはビューを推奨

Intermediateレイヤー

中間層からはビジネス上の関心毎にサブディレクトリを切る
モデル名に処理の動詞を入れ込もう
中間層はエンドユーザに見せない
中間層はエフェメラルテーブルを推奨
複雑な処理は分離するようにしましょう

Martsレイヤー

部門別にサブディレクトリを切る
エンティティ毎に名前をつける
テーブルはインクリメンタルモデル
セマンティックレイヤーを採用しな場合、非正規化を推奨
結合が多すぎるモデルは非推奨

その他の推奨事項

DRYの原則で同じ処理はマクロ化しましょう
テストは積極的にやりましょう
seed機能はあくまでウェアハウス内のデータを操作することを主軸である。ローディングツールにあらず

まずは公式ドキュメントに目を通す

避けては通れない、公式ドキュメントです。

Best practice guides

How we structure our dbt projects　★今回はここ
How we style our dbt projects
Intro to MetricFlow
Materializations best practices

公式では上記4つの項目からプロジェクトに対してのベストプラクティスを紹介しているようです。
早速深掘りしていきましょう！

学習目的

dbt公式ドキュメントを読み進めることで、見えてくるゴール

典型的なdbtプロジェクトの構成方法に関する最新の推奨事項を網羅する。
推奨するアプローチを説明することで、いつ、どこで推奨事項から逸脱して、組織独自のニーズにより適合させるべきかを判断できるようにする。

How we structure our dbt projects

ここではjaffle shopのモデルを例に説明されます。

jaffle shop models

jaffle_shop
├── README.md
├── analyses
├── seeds
│   └── employees.csv
├── dbt_project.yml
├── macros
│   └── cents_to_dollars.sql
├── models
│   ├── intermediate
│   │   └── finance
│   │       ├── _int_finance__models.yml
│   │       └── int_payments_pivoted_to_orders.sql
│   ├── marts
│   │   ├── finance
│   │   │   ├── _finance__models.yml
│   │   │   ├── orders.sql
│   │   │   └── payments.sql
│   │   └── marketing
│   │       ├── _marketing__models.yml
│   │       └── customers.sql
│   ├── staging
│   │   ├── jaffle_shop
│   │   │   ├── _jaffle_shop__docs.md
│   │   │   ├── _jaffle_shop__models.yml
│   │   │   ├── _jaffle_shop__sources.yml
│   │   │   ├── base
│   │   │   │   ├── base_jaffle_shop__customers.sql
│   │   │   │   └── base_jaffle_shop__deleted_customers.sql
│   │   │   ├── stg_jaffle_shop__customers.sql
│   │   │   └── stg_jaffle_shop__orders.sql
│   │   └── stripe
│   │       ├── _stripe__models.yml
│   │       ├── _stripe__sources.yml
│   │       └── stg_stripe__payments.sql
│   └── utilities
│       └── all_dates.sql
├── packages.yml
├── snapshots
└── tests
    └── assert_positive_value_for_total_amount.sql

Stagingレイヤーのフォルダ構造推奨事項

dbtのフォルダ構造は非常に重要です。
dbtが理解しやすく、またdbtセレクタ構文を実行できる構造である必要があります。
例えば、dbt build --select staging.stripe+のようにモデル指定をしたビルドを実行できるようにするためです。

✅ サブディレクトリはソースデータごとに切る

ソースシステムはテーブル間で似たようなローティグ方法やプロパティを共有することが多いので、このようなグループ化することを推奨する

❌ サブディレクトリはビジネスグループごとにを切ってはならない

ソースデータの段階から「ビジネス」や「ファイナンス」といったサブディレクトリを切ることは非推奨です。
　ソースデータのあり方としては、単一の真実のデータソースであるべきです。
　あまりにも早い段階でファイルを分割すると、後々重複や矛盾を生む出す可能性があります。

❌サブディレクトリはデータのローダー（Fivetran, Stitch, etc）ごとに切ってはならない

グループの規模が大きくなりすぎるため非推奨

Stagingレイヤーのファイル名推奨事項

dbtでは、一貫したファイル名のパターンを作成することが重要です。
ファイル名は一意でなければならず、ウェアハウスで選択・作成されたときのモデル名と一致していなければなりません。
ファイル名には、モデルが存在するレイヤーのプレフィックス、重要なグループ化情報、モデル内のエンティティやトランスフォーメーションに関する具体的な情報など、できるだけ明確な情報を含めることをお勧めします。

✅stg_[source]__[entity]s.sql - ソース・システムとエンティティの間にある二重のアンダースコアは、複数の単語を持つソース名の場合に、別々の部分を視覚的に区別するのに役立ちます。

✅デルが複数形であれば、ファイル名を複数形の表現を推奨する。

SQL、特にdbtのSQLは、できる限り散文のように読むべきです。
　可能な限り、SQLの大まかな明瞭さと宣言的な性質に寄り添いたいと考えています。
　そのため、ordersテーブルの注文が1つでない限り、複数行を持つテーブルでは複数形が正しい表現方法となります。

- ❌ファイル名にソースシステムを追加すべきである。

stg_[entity].sqlのようなファイル名は非推奨である。
　ソースツリーを見なくても。ソースデータがどこから取得されているのか分かるようにするべき。

Stagingレイヤーのモデル構造推奨事項

ステージング・モデルはすべて以下の推奨事項（パターン）に従います。
そのため、確立したパターンが揺るぎなく一貫していることを確認する必要があります。

jaffle_shopのstg_stripe__payments.sqlを例に推奨事項を確認

-- stg_stripe__payments.sql

with

source as (

    select * from {{ source('stripe','payment') }}

),

renamed as (

    select
        -- ids
        id as payment_id,
        orderid as order_id,

        -- strings
        paymentmethod as payment_method,
        case
            when payment_method in ('stripe', 'paypal', 'credit_card', 'gift_card') then 'credit'
            else 'cash'
        end as payment_type,
        status,

        -- numerics
        amount as amount_cents,
        amount / 100.0 as amount,

        -- booleans
        case
            when status = 'successful' then true
            else false
        end as is_completed_payment,

        -- dates
        date_trunc('day', created) as created_date,

        -- timestamps
        created::timestamp_ltz as created_at

    from source

)

select * from renamed

✅ カラムのリネーム
✅ 型変換
✅ 基本的な計算（amountをセント→ドルへ）
✅ カテゴライズ（条件ロジックを使用して、値をbucketsやbooleansにグループ化）

❌ 結合（JOIN）ソースデータはマスターデータであるべきで、Stagingで結合は非推奨

❌ 集計（Aggregations）集計はグループ化を伴うが、この段階ではそれを行わない。

このレイヤーでグループ化してテーブルの粒度を変更し始めると、いずれ必要になるであろうソースデータへのアクセスが失われる。

Stagingレイヤーのテーブルタイプの推奨事項

✅ ビューとしてマテリアライズする。

ステージングモデルは最終的な成果物ではなく、後のモデルのためのソースデータであるため、通常、2つの重要な理由からビューとしてマテリアライズされるべきです。

- ステージングモデルを参照するダウンストリームモデルは、常にすべてのコンポーネントビューから可能な限り新鮮なデータを取得します。

- データコンシューマがクエリを実行することを想定しておらず、従ってそれほど迅速かつ効率的に実行する必要がないモデルについて、ウェアハウス内のスペースを浪費することを避けることができる。

✅ Stagingモデルとソースは1対1

ステージングモデルはソースマクロを使用する唯一の場所であり、ステージングモデルはソーステーブルと1対1の関係を持つ必要があります。

Stagingレイヤーのその他の推奨事項

どうしてもStaging層で結合が必要な場合は、問題のソースシステムのステージングディレクトリにサブディレクトリを作成し、結合したモデルを構築することをお勧めする。
詳細は公式を参照してください。

Intermediateレイヤー(中間層)のファイルとフォルダ構造推奨事項

Files and folders

models/intermediate
└── finance
    ├── _int_finance__models.yml
    └── int_payments_pivoted_to_orders.sql

✅ ここではビジネスに適合する方向にシフトし、モデルをソースシステムごとではなく、ビジネス上の関心領域ごとにサブディレクトリに分割します。
✅ int_[entity]s_[verb]s.sql-中間レイヤーの内部で起こりうる変換は多様であるため、厳密にどのように名前を付けるかを指示するのは難しくなります。
最も良い指針は、中間レイヤーの動詞（pivoted、aggregated_to_user、joined、fanned_out_by_quantity、funnel_createdなど）について考えることです。

Intermediateレイヤーのモデル推奨事項

❌ 中間層はエンドユーザに公開すべきでない

中間モデルは一般的に、メインの本番スキーマで公開すべきではありません。
　中間モデルはダッシュボードやアプリケーションのような最終的なターゲットに出力されることを意図していないので、
　データガバナンスと発見可能性をより簡単に制御できるように、出力されるモデルから分離しておくのが最善です。

✅ 中間テーブルはエフェメラルにマテリアライズされる。

上記を考慮すると、よく使われるオプションの1つは、中間モデルをデフォルトでエフェメラルにマテリアライズすることです。
　これは一般的に、シンプルに始めるには最適な方法です。
　最小限の構成で、不要なモデルをウェアハウスから排除することができます。
　しかし、エフェメラルは単純ですが、トラブルシューティングが少し難しくなることに留意してください。

✅ 特別な権限を持つカスタム・スキーマのビューとして実体化する。

より堅牢なオプションは、メインの本番スキーマの外にある特定のカスタムスキーマのビューとして中間モデルを実体化することです。
　　これにより実装が簡単でスペースをとらないまま、モデルの数や複雑さが増すにつれて、開発に対する洞察力が増し、トラブルシューティングが容易になります。

中間モデルの目的は、マートモデルの複雑さを解消することであり、データ変換が必要とするさまざまな形を取ることができます。中間モデルの最も一般的な使用例には以下のようなものがあります：

✅ 構造の単純化

 妥当な数（通常は4～6）のエンティティまたは概念（ステージングモデル、または他の中間モデル）をまとめ、別の同様の中間モデルと結合してマートを生成する。
 マートに10個の結合があるのではなく、2つの中間モデルを結合することで、それぞれが複雑性の一部を収容し、可読性、柔軟性、テスト対象領域、コンポーネントに対する洞察が向上する。

✅ 再グレーニング

order_itemsのマートを構築する際に、quantityカラムに基づいて注文を扇形化し、各項目に対して新しい単一の行を作成する必要がある場合、マートの明快さを維持し、他のコンポーネントと混合する前に粒度が正しいことをより簡単に確認するために、特定の中間モデルでこれを行うのが理想的です。

✅ 複雑な操作を分離する

 特に複雑なロジックや理解しにくいロジックは、独自の中間モデルに移すと便利です。
 こうすることで、改良とトラブルシューティングが容易になるだけでなく、この概念をより明確に読みやすい方法で参照できる後のモデルを単純化することができます。

Marts層のビジネス定義

Martsレイヤー(中間層)のファイルとフォルダ構造推奨事項

Files and folders
データマートを提供する部門ごとにサブディレクトリを切る

models/marts
├── finance
│   ├── _finance__models.yml
│   ├── orders.sql
│   └── payments.sql
└── marketing
    ├── _marketing__models.yml
    └── customers.sql

✅ 部門や関心のある分野ごとにグループ化する

マートの数が10個以下であれば、サブフォルダの必要性はあまりない。
中間層と同様に、あまり早い段階で最適化しすぎないよう注意する。
しかし、より多くの構造やグループ化を挿入する必要がある場合は、ここで有用なビジネスコンセプトを使用してください。
マートレイヤーでは、もはやソースに準拠したデータを気にする必要はないので、部門別（マーケティング、財務など）にグループ化するのが、この段階では最も一般的な構造です。

✅ エンティティごとに名前を付ける

マートの顧客、注文の粒を形成する概念に基づいて、分かりやすい英語でファイル名を付ける。
純粋なマートの場合、ここに時間ディメンション（orders_per_day）を入れてはいけません。

Martsレイヤーのモデル推奨事項

✅ テーブルまたはインクリメンタルモデルとしてマテリアライズされる

マートレイヤーに到達したら、ウェアハウスにロジックだけでなく、データそのものを構築し始める時です。
こうすることで、エンドユーザーが実際に使用するために設計された後のモデルに対して、より高速なパフォーマンスを提供することができます。
また、ダッシュボードを更新したり、pythonでリグレッションを実行したりするたびに、モデルのチェーン全体を再計算するコストを削減することができます。
マテリアライゼーションに関する一般的な経験則は、常にビューから始めることです（ビューは基本的にストレージを使用せず、常に最新の結果を得ることができます）。
いつものように、シンプルに始めて、必要に応じて複雑にしていく。
最もデータ量が多く、計算負荷の高い変換を行うモデルは、dbtの優れたインクリメンタル・マテリアライゼーション・オプションを利用すべきですが、すべてのマートモデルをデフォルトでインクリメンタルにしようと急ぐと、余計な手間がかかります。

✅ 幅広く、非正規化

昔ながらのウェアハウジングとは異なり、現代のデータスタックでは、ストレージは安価で、高価なのはコンピュートであり、優先順位をつけなければならない。

❌ 1つのマートに結合が多すぎる

dbt変換を構築する際の経験則として、1つのマートであまり多くの概念を結合しないようにしましょう。
何をもって「多すぎる」と判断するかは様々です。
8つのステージングモデルを単純な結合だけでまとめる必要がある場合、それは問題ないかもしれません。
逆に、複雑で計算負荷の高いウィンドウ関数を使って4つのコンセプトをまとめる場合は、多すぎる可能性があります。
結合するモデルの数とマート内のロジックの複雑さを天秤にかけ、目を通し、明確なメンタルモデルを構築するのが大変であれば、モジュール化を検討する必要がある。
これは厳密なルールではありませんが、マートを作成するために4つか5つ以上の概念をまとめる場合、より明確にするために中間モデルをいくつか追加するとよいでしょう。
それぞれ3つの概念をまとめた2つの中間モデルと、その2つの中間モデルをまとめたマートは、通常、6つの結合を持つ単一のマートよりもはるかに読みやすいロジックのチェーンになります。

✅ 慎重に別々のマートを構築する

マートレイヤーまではDAGを狭く保つように努めますが、ここからは少し厳密さを欠くようになるかもしれません。
よくある例は、異なるグレインのマート間で情報を受け渡すことです。
上で見たように、受注マートを顧客マートに持ち込み、重要な受注データを顧客グレインに集約します。
アウトプットのデータを実際に構築することで、コンピュートとストレージを実際に「消費」している今、同じビューとCTEをゼロから再計算するよりも、同様のデータを必要とするアウトプットを高速化しコストを削減するために、以前に構築したリソースを活用する方が賢明です。
ここでの適切なアプローチは、独自のDAG、モデル、および目標に大きく依存します。
重要なのは、あるマートを使用して別の後発のマートを構築することは問題ありませんが、リソースの浪費や循環的な依存関係を避けるために慎重な検討が必要であるということです。

Marts層でのセマンティックレイヤー推奨事項

セマンティックレイヤーは、プロジェクトの構成方法の基本原則をいくつか変更します。
セマンティックレイヤーを使わずにdbtを使うと、ビルディングブロックコンポーネントの最も有用な組み合わせを、非正規化された広いマートに作成する必要があります。
一方、セマンティックレイヤーはMetricFlowを活用し、動的にエンコードされたコンポーネントのあらゆる可能な組み合わせを非正規化します。
そのため、より正規化されたモデルを論理レイヤーからセマンティックレイヤーに持ち込む方が、柔軟性を最大化することができます。

セマンティックレイヤーのファイルとフォルダ構造推奨事項

Files and folders
完全に正規化されたコンポーネントを形成する場合、ステージング・モデルを直接使用する可能性があります。
この組み合わせは、ステージングレイヤーとマートレイヤーの両方のモデルがセマンティックレイヤーに参加し、より強力で拡張的なYAMLコンフィギュレーションを使うことができることを意味します。
このことから、セマンティックレイヤーを使うプロジェクトでは、以下のようにモデルごとにYAMLファイルを使うアプローチを推奨します。

models
├── marts
│   ├── customers.sql
│   ├── customers.yml
│   ├── orders.sql
│   └── orders.yml
└── staging
    ├── __sources.yml
    ├── stg_customers.sql
    ├── stg_customers.yml
    ├── stg_locations.sql
    ├── stg_locations.yml
    ├── stg_order_items.sql
    ├── stg_order_items.yml
    ├── stg_orders.sql
    ├── stg_orders.yml
    ├── stg_products.sql
    ├── stg_products.yml
    ├── stg_supplies.sql
    └── stg_supplies.yml

Project structure review

これまで、dbtプロジェクトの主要なディレクトリであるmodelsフォルダに焦点を当ててきました。
次に、YAML設定ファイルへのアプローチ方法から始めて、プロジェクトの残りのファイルやフォルダがこの構造にどのように適合しているかを拡大して見ていきます。

models
├── intermediate
│   └── finance
│       ├── _int_finance__models.yml
│       └── int_payments_pivoted_to_orders.sql
├── marts
│   ├── finance
│   │   ├── _finance__models.yml
│   │   ├── orders.sql
│   │   └── payments.sql
│   └── marketing
│       ├── _marketing__models.yml
│       └── customers.sql
├── staging
│   ├── jaffle_shop
│   │   ├── _jaffle_shop__docs.md
│   │   ├── _jaffle_shop__models.yml
│   │   ├── _jaffle_shop__sources.yml
│   │   ├── base
│   │   │   ├── base_jaffle_shop__customers.sql
│   │   │   └── base_jaffle_shop__deleted_customers.sql
│   │   ├── stg_jaffle_shop__customers.sql
│   │   └── stg_jaffle_shop__orders.sql
│   └── stripe
│       ├── _stripe__models.yml
│       ├── _stripe__sources.yml
│       └── stg_stripe__payments.sql
└── utilities
    └── all_dates.sql

YAML in-depth
dbtプロジェクトでYAML設定ファイルを構造化するとき、特定の設定をできるだけ見つけやすくするために、一元化とファイルサイズのバランスをとることを推奨します。
トップレベルのYAMLファイル (dbt_project.yml、packages.yml) は具体的な名前と場所が必要ですが、ソースとモデルの辞書を含むファイルは好きな名前、場所、構成にすることができます。
ここで重要なのは内部の内容です。
ここでは、私たちが推奨する主な方法と、一般的な代替方法の長所と短所を説明します。
dbtプロジェクトを構造化する他の多くの側面と同様に、ここで最も重要なのは、一貫性、明確な意図、そしてどのように、なぜそうするのかについての徹底的な文書化です。

✅ フォルダーごとの設定

上の例のように、modelsフォルダのディレクトリごとに_[directory]_models.ymlを作成し、そのディレクトリ内のすべてのモデルを設定します。
ステージングフォルダの場合、ディレクトリごとに[directory]_sources.ymlも作成します。
先頭にアンダースコアをつけることで、YAMLファイルがすべてのフォルダーの先頭にソートされ、モデルから分離しやすくなります。
YAMLファイルはSQLモデルファイルのように固有の名前を必要としませんが、(それぞれのフォルダーに__sources.ymlの代わりに)ディレクトリを含めることで、正しいファイルをより速く見つけることができます。
プロジェクトでdocブロックを利用する場合、同じパターンに従って、モデルのフォルダのすべてのdocブロックを含む[ディレクトリ]__docs.mdマークダウンファイルをディレクトリごとに作成することを推奨します。

❌ プロジェクトごとのコンフィグ

ソースとモデルのYAMLを1つのファイルにまとめる人がいます。
技術的にはこれを行うことができ、探しているコンフィグがどのファイルにあるのかを知ることは確かにシンプルになりますが(ファイルが1つしかないため)、そのファイル内で特定のコンフィグを見つけることはとても難しくなります。
この2つの懸念のバランスを取ることをお勧めします。

⚠️ モデルごとのコンフィグ

一方で、モデルごとに1つのYAMLファイルを作ることを好む人もいます。
ファイルを素早く検索でき、特定のコンフィギュレーションが存在する場所を正確に知ることができ、ファイルツリーを見ることでコンフィギュレーションが存在しない (したがってテストが存在しない) モデルを見つけることができ、その他さまざまな利点があるからです。

✅ カスケード設定

dbt_project.ymlを活用して、ディレクトリレベルでデフォルトの設定を行います。
ベースラインのスキーマとマテリアライゼーションを定義するために、これまでに作成した整理されたフォルダ構造を使用し、これに対するバリエーションを定義するためにdbtのカスケードスコープ優先順位を使用します。
例えば、デフォルトでテーブルとしてマテリアライズされるようにマートを定義し、別々のサブフォルダに対して別々のスキーマを定義し、インクリメンタルなマテリアライゼーションを使用する必要があるモデルはモデルレベルで定義することができます。

その他のフォルダ

jaffle_shop
├── analyses
├── seeds
│   └── employees.csv
├── macros
│   ├── _macros.yml
│   └── cents_to_dollars.sql
├── snapshots
└── tests
└── assert_positive_value_for_total_amount.sql

ここまで、dbtプロジェクトの主要な活動領域であるmodelsフォルダに重点を置いてきました。
しかし、お気づきのように、プロジェクトには他にもいくつかのフォルダがあります。
これらは設計上、あなたのニーズに対して非常にフレキシブルですが、ここでは、あなたが始めるのに役立つように、これらの他のフォルダーの最も一般的な使用例について説明します。

seeds folder

✅ シードの最も一般的なユースケースは、モデリングに役立つがソースシステムに存在しないルックアップテーブルをロードすることです。
❌ ソースシステムからウェアハウスにデータをロードするためにシードを使用しないでください。
dbtはデータローディングツールとしてではなく、ウェアハウス内のデータを操作するように設計されています。

tanalyses folder

analysesフォルダには、Jinja を使用してバージョン管理したいクエリを保存できます。
このパッケージは、他のシステムからdbtにロジックを移行する際に、出力の矛盾を見つけるのに非常に便利です。

tests folder

dbtのテストが進化するにつれて、単発のテストを書く必要性が少なくなってきました。
テストロジックのワークショッピングには非常に便利ですが、多くの場合、そのロジックを独自の汎用テストに移行するか、あるいは拡張を続ける dbtパッケージの中から自分のニーズに合ったビルド済みのテストを見つけることになるでしょう（dbt-utils と dbt-expectations の追加テストにより、ほとんどすべての状況をカバーできます）。
しかし、単数テストがまだ優れている点は、様々な特定のモデルを必要とするテストに柔軟に対応できることです。
ソフトウェアエンジニアリングにおけるユニットテストと統合テストの違いに馴染みがあるなら、ジェネリックテストと単数テストを同じように考えることができます。
複数の特定のモデルがどのように相互作用するか、あるいはどのように関連しあうかという結果をテストする必要がある場合、単数テストがロジックを明確にする最も手っ取り早い方法でしょう。

snapshot folder

スナップショットは、タイプ 1 (破壊的に更新される) ソース・データから、ゆっくりと変化するタイプ 2 ディメンジョン・レコードを作成するためのものです。

macros folder

繰り返し行う変換をDRYにするためのスナップショットと同様、マクロの詳細については、このガイドの範囲外であり、別の場所で十分カバーされていますが、構造に関する重要な推奨事項の1つは、マクロのドキュメントを書くことです。
_macros.ymlを作成し、マクロを使用できるようになったら、その目的と引数を文書化することをお勧めします。