【discource dbt】How we stracture our dbt projects

概要

https://discourse.getdbt.com/t/how-we-structure-our-dbt-projects/355
dbtプロジェクトの構成方法（dbt Labsでのやりかた！）

How we stracture our dbt projects

• 筆者は、dbtLabsの中の人っぽい　（いや、　Fishtown Analyticsの中の人）
• dbtのメンテナー、分析コンサルタントとして多くのdbtプロジェクトを構築してきた中で、開発パターンが見えててきた

この記事では、具体的な最終形を示すものではない。
すべてのマスターテーブルを非正規化するのか　とか。

dbtの中でどのように分割したらいいのか（管理的に、構成的に）。そういった話題のガイドとして使ってくさい

そのあたりはそれぞれのプロジェクト似合った形で作り上げてね。あくまでもこれは目安

この記事の内容が、dbtプロジェクトを構築する唯一最良の方法というわけではないです。むしろ、主観的な意見が主です。

• 担当しているクライアント向けの分析内容
• 取り込みはサードパーティ、データウェアハウスは分析クエリ用に最適化されている

要は一般的ではない、数をこなしてるから、良さげな構成パターンが出てきただけ・・・かな？

https://docs.getdbt.com/docs/guides/best-practices
はすべてのdbtプロジェクトに当てはまる原則を反映している。
このドキュメントと、ベストプラクティスは密接に関係しており、
この記事では、特に次のような原則を簡単に理解できるように構成されている

• 生データへの参照を制限する
• フィールドの名前を変更してキャストし直す
• モデルをディレクトリにグループ化する
• モデルにテストを追加する
• データウェアハウスの情報アーキテクチャを検討する
• ソース中心の変換とビジネス中心の変換を分離する

ここ重要っぽい

Data transformation 101

1. Sources データソースに準拠した構造のスキーマとテーブル　　何らかのツール・仕組みで取り込まれる
2. Staging models データモデリングの最小単位。各モデルは、それが表すソースデータテーブルと1対1の関係にあります。 粒度は同じですが、列の名前が変更されたり、再キャストされたり
3. Marts models ビジネスプロセスとエンティティを表すモデルであり、それらが基づいているデータソースから抽象化されています。

シンプルなプロジェクトならこの３層でOK
ただ、より複雑なプロジェクトになると中間層やその他いろいろな付属物がついてくるかも

StripeとBraintreeを使った例

1. Sources StripeAPIからの支払いレコードとBraintreeAPIからの支払いレコードで、サードパーティのツールによってデータウェアハウスに読み込まれます。
2. Staring models　StripeとBraintreeの両方の支払いは、APIと同じ列名で整形される
3. Marts models　毎月の顧客あたりの収益を新しい収益、アップグレード、ダウングレード、チャーンとして分類し、ビジネスが時間の経過とともにどのように実行されているかを理解する、月次経常収益（MRR）モデル。 収益がStripeまたはBraintreeのどちらを介して収集されたかに注意することは有用かもしれませんが、それらは基本的に別個のモデルではありません。

SourcesとStaging Modelsはソース中心でほぼ一緒のものだけど、Marts　　Modelsはビジネスロジックが中心に変換がかかっている。
我々のプロジェクトでは、この分割が最初のフォルダ分けになっていて、下記のような構成にまずなる

├── dbt_project.yml
└── models
    ├── marts
    └── staging

staging　というよりは、lakeのほうがしっくりきます。 環境の dev, stg, prd とごっちゃになる

Staging raw data

ステージング層（レイク層）の目標は、ステージングモデルを作ること。ステージングモデルは生データを取得し、それらをクリーンアップ（＝クレンジング？）をして、更に分析できるように準備する。

ここでいう「クリーンアップ」をどこまでやるかは不明。非常に「軽い」クレンジングをする程度っぽい

データウェアハウスにクエリを実行するユーザーの場合、stg_プレフィックスを持つリレーションは次のことを示します

• フィールドの名前が変更され、一貫した方法で再キャストされました。
• タイムゾーンなどのデータ型は一貫しています。
• 空の文字列をNULL値に置き換えるなどの軽いクレンジングが発生しました。
• 有用な場合は、オブジェクトの平坦化が行われた可能性があります。（＝JSONを平坦化する的な？）

複数のデータソースを使用することが多いため、ステージングディレクトリでは、ソースごとに1つのディレクトリを作成します。

├── dbt_project.yml
└── models
    ├── marts
    └── staging
        ├── braintree
        └── stripe

ステージングディレクトリの中に最低限以下のものが含まれている

• ステージングモデル
  • stg_＜source＞_＜object＞
  • 基本的に view で作成される（パフォーマンスに問題がある際はTableの場合もある）
• src_＜source＞.yml Source, 定義、テスト、ドキュメントが含まれる
• stg_＜source＞.yml テストとドキュメント

├── dbt_project.yml
└── models
    ├── marts
    └── staging
        └── braintree
            ├── src_braintree.yml
            ├── stg_braintree.yml
            ├── stg_braintree__customers.sql
            └── stg_braintree__payments.sql

モデルごとに一つの　 .yml ファイルにする場合もある。
例としては、stg_brantree__customers.yml　など。ymlファイルが肥大化して扱いにくくなったらこのように分割することもあり。

But what about base models?

（しかし、ベースモデルはどうですか？）

dbtドキュメントの以前のバージョンでは、変換の最初のレイヤーとして「ベースモデル」を実装することが推奨されていました。以前は、models / braintree / base / base_payments.sqlのように、この方法でモデルを整理して名前を付けていました。

今のベストプラクティスでは、ベースモデルの記述を削除。代わりに

• 名前の変更と再キャストを一回
• 生データへの依存を制限する（＝ ref関数経由ってことかな？）
  とした

dbt的には、ソースは取り込み済みのもの。
ソースへのアクセスにref関数経由で使うためにも、ソースとほぼ一緒（＝小さいクレンジングのみ）をしたほうがいいと言うことかなと思う。
※ ref関数はdbtの肝と言ってもいいと思っている

そうは言っても、私たちのdbtプロジェクトでは、すべてのソースが次の形式の1つのモデルを通過します。

with source as (
    select * from {{ source('braintree', 'payments') }}
),

renamed as (
    select
        id as payment_id,
        order_id,
        convert_timezone('America/New_York', 'UTC', createdat) as created_at,
        ...
    from source
)
select * from renamed

これを基本変換と呼びます。 ソースデータが良好な状態である場合、ステージングモデルを構築するために必要なのはこの変換だけである可能性があり、ステージングモデルはこのSQLです。

ただし、ステージングモデルを構築すると、複数のモデルに相当するクリーニング、修正、分類が必要になる場合や、別のソースへの結合または結合が必要になる場合があります。 データソースがベーストランスフォーメーションを確実に通過するように、別のベースモデルを作成して、ステージングモデルの上流にDAGを拡張し、そこから選択します。

├── dbt_project.yml
└── models
    ├── marts
    └── staging
        └── braintree
            ├── base
            |   ├── base.yml
            |   ├── base_braintree__failed_payments.sql
            |   └── base_braintree__successful_payments.sql
            ├── src_braintree.yml
            ├── stg_braintree.yml
            ├── stg_braintree__customers.sql
            └── stg_braintree__payments.sql

我々のプロジェクトの base models は

• よく ephemeral=一時的なマテリア来ゼーションを指定して使ってる。なので、エンドユーザが（直接）ウェアハウスでクエリを実行することはない
• テストは、同じディレクトリ無い（/models/staging/brantree/base）の base.ymlでテストされている

base　モデル　→　ステージングモデルで追加の　Transform が必要な場合は、　srtaging/＜source＞/intermediate （中間）ディレクトリを作ってそこに変換ロジック（SQL）を配置する

base_ というPrefix名がついているが、現在は、src_が正しい？
いや、つかってるなぁ・・・

Describing a bussiness through marts

mart層を介してビジネスを記述する

Mart層は、ビジネスエンティティ（ビジネスの実体？）とプロセスを記載するモデルが配置される。

多くの場合、マーケティング、財務、製品などのビジネスユニットごとにグループ化されます。 ビジネス全体で共有されるモデルは、コアディレクトリにグループ化されます。

これをdbtのモデルに当てはめると

├── dbt_project.yml
└── models
    ├── marts
    |   ├── core
    |   ├── finance
    |   ├── marketing
    |   └── product
    └── staging

モデル設計自体はそれだけで一分野になるぐらい大きな話題なので、我々のプロジェクトでどうしているか？を記載する。
我々の目標は、データソースからファクトモデルとディメンションモデルを構築すること。すなわち

• 　fcl_<verb> 実世界で起きてることをそのままモデル化したもの。セッション、トランザクションなどの変更が殆どないイベント tall narrow data model
• dm_<noun>　 組織のエンティティを識別および説明する際の信頼できる唯一の情報源。 顧客、製品、候補者、建物、従業員など、ゆっくりと変化しますが、変更可能です。 wide short data

https://en.wikipedia.org/wiki/Wide_and_narrow_data
Narrow ( tall ) Data

Person	Variable	Value
Bob	Age	32
Bob	Weight	168
Bob	Height	180
Alice	Age	24
Alice	Weight	150
Alice	Height	175
Steve	Age	64
Steve	Weight	144
Steve	Height	165

Wide (short) Data

Person	Age	Weight	Height
Bob	32	168	180
Alice	24	150	175
Steve	64	144	165

ファクトテーブル（　fcl_<verb>）は、実質的なデータの変換を行う場。
ファクトテーブルとディ面所なるテーブルに関しては

• ファクトテーブルとディメンションテーブルは、クエリのパフォーマンスを向上させるため　tableで作成する。もしパフォーマンス的に難しかったら incremantal（追記）で作成する
• ファクトテーブルとディメンションモデルを作る際の中間テーブルを作る返還は、 marts / <mart>　/ intermediateディレクトリに配置される。命名は<useful_name> __ <transformation_in_past_tense> .sql 。接頭辞がなく、二重下線が使用されていることは、これらが中間モデルであり、信頼できないことを示している。
• テストは同じディレクトリ内の　`<dir_name>.yml' に記載
• ドキュメントは <dir_name>.mdに

├── dbt_project.yml
└── models
    ├── marts
    │   ├── core
    │   │   ├── core.md
    │   │   ├── core.yml
    │   │   ├── dim_customers.sql
    │   │   ├── fct_orders.sql
    │   │   └── intermediate
    │   │       ├── customer_orders__grouped.sql
    │   │       ├── customer_payments__grouped.sql
    │   │       ├── intermediate.yml
    │   │       └── order_payments__joined.sql
    │   ├── finance
    │   ├── marketing
    │   └── product
    └── staging

Accessories to data

データの付属品
ステージングとマートに加えて、次のようなモデルディレクトリがある

• utils: all_daysテーブル。 これはどこでも役立ちますが、分析/レポートの基礎を形成することはありません。 （？？？？？よくわからない・・・）
• lookups: ユーザーマッピングテーブル、郵便番号国テーブルなど。dbt SeedのCSVファイルを置く場所？
• admin: Audit Log（操作監視ログ）、ウェアハウスの実行ログ
• metrics: ファクトテーブルから取得され、時系列レポートに直接役立つ正確に定義された測定値であり、目標および予測との1対1の比較を可能にするように厳密に構造化されています。 メトリックテーブルは、DAGのディメンションテーブルとファクトテーブルの下流にあり、特別なステータスに値します。
• Packages: モデルフォルダではないがモデルを含めるパッケージを配置する。

これらの追加モデルを使用しているプロジェクトでは、カスタムスキーマをウェアハウスのディレクトリとして利用して、モデルを論理的にグループ化し、dbtプロジェクトのディレクトリ名と一致するスキーマ名を選択することがよくあります。

all_daysテーブルというのがよくわからない。集計前のテーブル・・・？

いまいちピンとこない・・・が、こういうものもあるから、分けるとかにはいいよ　ということだろうか。
adminなどは、dbtで管理するべきことだろうか。ここはWAREHOUSE側でやるべきではないか

:::metrics
これを modelとは別に置くのはなぜだろう。
:::

Final thoughts

この記事では、dbtプロジェクトのDAGの構築について、ソースから始まり、martsモデルで終わるように左から右に説明しました。

ただし、実際には、最初にモデリングの問題を右から左に考えることがよくあります。まず、作成するダッシュボードまたはレポートのアイデアから始めて、倉庫に必要なマートモデルの構造をホワイトボードに記録します。 このダッシュボードに電力を供給します。

同じホワイトボードで、実際のSQLの記述を開始する前に、ソースに到達するまで逆方向に作業することがよくあります。

モデリングの問題を数回解決したのは一度だけで、DAGを左から右に構築する方法を直感的に理解できることがわかりました。

言い換えれば、モデリングの旅を始める前に目的地について考える傾向があります。

このdbtのモデルで作った場合、実テーブルはどこにできるんだろうか・・・・
自分自身では、最近は三層構造（Lake層、Warehouse層、Mart層）でやってて、できればスキーマを分けたい