dbtプロジェクト構築に関する ベストプラクティス #3「中間データ層(Intermediate Layer)に関する検討事項」 #dbt

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

dbtはクラウド型データウェアハウス（DWH）におけるデータ変換に特化したツールです。非常に使い勝手が良く便利なツールである一方、様々な機能が提供されているのでいざ使ってみよう！となると『何をどうやって作り上げていけば良いんだろう？』『この場合のルールや制限はどういうものがあるの？どういう取り決めをもって扱えば良いんだろう？』という風に思うこともあるかと思います。(実際私自身そう感じました)

そんなユーザーの疑問や悩みを解決する、いわゆるdbtユーザー向けのガードレール的な存在となりうるコンテンツがdbt社から展開されています。それが『dbtベストプラクティスガイド(Best practice guides)』です。構造、スタイル、セットアップなど、dbt Labsの現在の視点を通した「ベストプラクティス」がまとめられています。

そこで当エントリでは、幾つか展開されている「dbtベストプラクティスガイド」で紹介されているコンテンツの中から『dbtプロジェクト構築』に関するもの、データウェアハウスにおける『中間データ層(Intermediate Layer)』をどうdbtで構築していくかについて読み解いていきたいと思います。

目次

• 中間データ層におけるファイルとフォルダの検討事項
  • フォルダ
  • ファイル名
• 中間データ層におけるモデルの検討事項
• まとめ

中間データ層におけるファイルとフォルダの検討事項

「ステージングデータ層(Staging Layer)」で、データの"原子"といえる生データ状態のデータを定義したことで、それらをより複雑で連携された、いわば"分子"の形に加工編集する準備が整いました。当エントリで言及する「中間データ層(Intermediate Layer)」では、データに命を吹き込むために使用する、特定の目的を持つさまざまな形式への加工を担う作業を行います。

当エントリでの内容を説明、解説するために、jaffle_shopプロジェクトのmodels/intermediate配下の構造を使います。

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

フォルダ

中間データ層(Intermediate Layer)におけるフォルダ構成のベストプラクティスは以下の通り。

ファイル名

中間データ層(Intermediate Layer)におけるファイル関連のベストプラクティスは以下の通り。

上述のサンプルプロジェクトはある種の説明を目的としたものなので非常に単純な構造となっています。なのでこれくらいの"シンプルさ"の状態で済むのであれば正直ステージング後のレイヤーでこのレベルの分割はおそらく不要でしょう。

ですが、実環境、本番運用を想定した構成であればより複雑さを増すことになるでしょうし、ここで述べられているルールに従うことで効果・恩恵を受けることが出来るはずです。ここで実現したい目標は「唯一の真実の情報源」を作り上げることです。我々は財務とマーケティングを別々の注文モデルで運用することを望んでいません。そしてこれらの定義を1 つにまとめる手段としてdbtプロジェクトを使用したいと考えています。

この事を踏まえると、分割や最適化に関しては進め方を急ぎ過ぎない、早め過ぎないようにするというのもポイントです。マートモデルが10個未満で、それらの開発と使用に問題がない場合は、完全にサブディレクトリを使用しなくても構いません (ステージング層を除き、プロジェクトに新しいソース システムを追加するときに必ずサブディレクトリを実装する必要があります)。 プロジェクトが成長し、本当にそれらを必要とするようになったタイミングで作業を行う...という感じで良いと思います。

中間データ層におけるモデルの検討事項

以下に記載したコードは、小規模なサンプルプロジェクトにおける「唯一の中間モデル」です。 これは、上記の原則に従った優れたユースケースを表しており、ステージングモデルをグループ化し、異なる粒度(テーブル内のレコードが一意になる列の組み合わせ)にピボットするという明確な1つの目的を果たしています。

• Data grain: What granularity means in terms of data modeling

ここではモデルをDRY化するために、Jinjaを少し利用しています (DRYであることの努力は、コードベース全体の変換に加えて、単一モデル内で記述するコードにも適用されます) が、あまり慣れていない場合でも怖がる必要はありません。

• What is DRY? Hint: It makes for great code - dbt Labs
• Jinja and macros | dbt Developer Hub

CTE(Common Table Expression/共通テーブル式)の名前を見ると、pivot_and_aggregate_payments_to_order_granという定義が使われており、このブロック内で何が起こっているのかが非常に明確になります。 ファイルやフォルダーの場合と同じように、モデル内の CTE 内で発生する変換に説明的なラベルを付けることで、SQL を知らない関係者でも、コードではないにしても、このセクションの目的を理解できるようになります。

• CTE in SQL: Quite possibly the best thing to happen to SQL

ステージング層から離れて、より複雑な変換を書き始める際はこの考えを念頭に置いてください。 モデルがDAGに接続し、マクロスケールで変換のストーリーを伝えるのと同じ方法で、CTE はモデルファイル内でこれをより小規模なスケールで行うことができます。

-- int_payments_pivoted_to_orders.sql

{%- set payment_methods = ['bank_transfer','credit_card','coupon','gift_card'] -%}

with

payments as (

   select * from {{ ref('stg_stripe__payments') }}

),

pivot_and_aggregate_payments_to_order_grain as (

   select
      order_id,
      {% for payment_method in payment_methods -%}

         sum(
            case
               when payment_method = '{{ payment_method }}' and
                    status = 'success'
               then amount
               else 0
            end
         ) as {{ payment_method }}_amount,

      {%- endfor %}
      sum(case when status = 'success' then amount end) as total_amount

   from payments

   group by 1

)

select * from pivot_and_aggregate_payments_to_order_grain

中間モデルの目的は、マートモデルの複雑さを解消する役割を果たし、データ変換に必要なだけの形式を取ることにも繋がります。中間モデルの最も一般的な使用例には次のようなものがあります。

まとめ

という訳で、dbtプロジェクト構築に関するベストプラクティス第3弾としてデータウェアハウスにおける「中間データ層」に関する取り組み方の紹介でした。(前回第2弾からだいぶ間隔が空いてしまった...反省)

中間データ層はその名前が示すようにあくまでも「生データ層(ステージング層)」と「マート層」の間に位置するレイヤーです。文中「必ずしも無ければならない、ということは無い」という言及もあったように割と流動的な位置付けでもあるので実際の案件で適用させる場合も試行錯誤が多く入る場所になるのかな、とは思いました。少ないながらも有効な"プラクティス"が提示されていますので、ここはそれらの方針を上手く活用して運用していきたいですね。

• dbt の記事一覧 | DevelopersIO