アライアンス事業部 エンジニアグループ モダンデータスタック(MDS)チームのしんやです。
dbtはクラウド型データウェアハウス（DWH）におけるデータ変換に特化したツールです。非常に使い勝手が良く便利なツールである一方、様々な機能が提供されているのでいざ使ってみよう！となると『何をどうやって作り上げていけば良いんだろう？』『この場合のルールや制限はどういうものがあるの？どういう取り決めをもって扱えば良いんだろう？』という風に思うこともあるかと思います。(実際私自身そう感じました)
そんなユーザーの疑問や悩みを解決する、いわゆるdbtユーザー向けのガードレール的な存在となりうるコンテンツがdbt社から展開されています。それが『dbtベストプラクティスガイド(Best practice guides)』です。構造、スタイル、セットアップなど、dbt Labsの現在の視点を通した「ベストプラクティス」がまとめられています。
そこで当エントリでは、幾つか展開されている「dbtベストプラクティスガイド」で紹介されているコンテンツの中から『dbtプロジェクト構築』に関するもの、データウェアハウスにおける『ステージングデータ層(Staging Layer)』をどうdbtで構築していくかについて読み解いていきたいと思います。
目次
ステージング層におけるファイルとフォルダの検討事項
下記はこのベストプラクティスシリーズで扱うjaffle_shopのdbtプロジェクト構成をツリー表示させたものです。当項で言及するステージングデータレイヤー層に相当するstagingフォルダ配下の要素行を強調表示させています。
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この構成を踏まえて「フォルダ」と「ファイル」について見ていきます。
フォルダ
- dbtではフォルダ構造はとても重要。
- プロジェクトにエンコードされたナレッジグラフをより分かりやすく理解するための重要なインタフェースを担ってもいる。
- 多種多様なソースに準拠したモデルから、より少なくよりリッチなビジネスに準拠したモデルへと段階的にデータがどのように流れているかを反映したものであるべき。
- フォルダ分けをしておくことで、dbtではこのフォルダ構造を「選択の手段」として利用することが出来る。
- Syntax overview | dbt Developer Hub
- 例)上記構造例で「Stripeデータに関して構築された全てのモデルを実行」したい場合であれば
dbt build --select staging.stripe+とすることで一括実行が可能。
フォルダにおける「べき＆べからず」集
| ✅ すべき(dos) | ❌ べからず(dont’s) |
|---|---|
| ソースシステムに基づいたサブディレクトリ構成： 任意のソースシステムにおけるデータはテーブル間で似たようなロードの手段プロパティを共有する傾向があるため、それらの括りでグループ化(ディレクトリにまとめる)する事で効率良いdbtでの操作が出来るようになります。 |
ローダーに基づいたサブディレクトリ構成： FivetranやStitch、その他カスタムな同期方法などの「データのロード方法」でのグループ分け。これは括りが広すぎで実際の案件、プロジェクトではあまり意味を為さない。 |
| ---- | ビジネス要件に基づいたサブディレクトリ構成： ステージング層において"marketing"や"finance"といった ビジネス要件に基づく形でのサブディレクトリ構成もお勧めしません。あまりに早い段階でこういった分割を進めてしまうと、重複や矛盾した定義を生み出す可能性があるためんです。同じ粒度でのビルドがなされるのが望ましいし、経験上、ソースシステムの構造を反映したこのステージング層で、変換処理を開始することがこのステップでの最適なグループ化レベルであると考えます。 |
この記述を踏まえて「あれ、冒頭のプロジェクト構成にfinanceとかフォルダあったけどな..」と思ってましたがあれは中間データ層(Intermediate)、マート層(Mart)での話でしたね。ここでは「ステージング層(Staging)」では、ということです。
ファイル名
dbtでは一貫したファイル名のパターンを作成し展開することが重要です。ファイル名は全体で一意であり、ウェアハウスで選択／作成された時のモデル名と一致している必要があります。
ファイル名については様々な情報を出来るだけ明確に把握出来るように、以下の要素を含めておくことが望ましいです。
- モデルが存在するレイヤーのプレフィックス
- グループ化に関する情報
- モデル内のエンティティやトランスフォーメーションに関する具体的な情報
ファイル名における「べき＆べからず」集
| ✅ すべき(dos) | ❌ べからず(dont’s) |
|---|---|
| stg_[ource]__[entiry]s.sql： この命名規約とすることで、複数の単語から構成されるソース名であっても(アンダースコア2連で区切られていることで)それぞれの箇所を視覚的に区別することが出来ます。例)stg_google_analytics__campaigns.sql |
stg_[entity].sql： 左記の例とは異なり、ソース名が記載されていない。この場合このモデルがどこからやってきたものかが分からなくなります。 |
| 名称は複数形であること： dbtのSQLは散文的に読むべきであり、可能な限りSQLの大まかな明瞭さと宣言的な性質に寄り添うべきという考えから。 |
---- |
ステージング層におけるモデルの検討事項
続いてのトピックは「ステージング層におけるモデル」の構造についてです。
下記ファイルは標準的なステージング層におけるモデル(stg_stripe__payments)の例です。sourceマクロを介してソーステーブルを読み込んでいるものと、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モデル定義における「べき＆べからず」集
| ✅ すべき(dos) | ❌ べからず(dont’s) |
|---|---|
| 名前の変更 | 結合(joins)： ステージング層でのモデルの目的は「個々のソースをクリーンな状態にして、後工程で使えるように準備しておくこと」です。このタイミングで結合をしてしまうと後工程での混乱を招くような影響が発生するため、やるべきことではありません。 |
| データ型の変更 | 集計(Aggregations)： 上記結合(Joins)同様の理由から。このタイミングで集計をしてしまうと、何かあった際にソースデータの内容を確認する、ということが出来なくなってしまいます。 |
| 基本的な計算(例:セントからドル等) | ---- |
| カテゴライズ(ケースによる変換)： 上記case文のような、条件ロジックを使用して値をバケツやブール値にグループ化する「カテゴライズ」に関する変換処理。 |
---- |
| ビューとしてマテリアライズ： (※1 詳細後述) |
---- |
(※1 ビューのマテリアライズについて)：
dbtプロジェクト配下にある
dbtプロジェクト配下にある
dbt_project.ymlファイルにおける下記部分をみると、ステージングディレクトリ全体がビューとして実体化されるように定義されています。
dbt_project.yml
models:
jaffle_shop:
staging:
+materialized: viewステージング層におけるモデルは後工程におけるモデル構築のためのブロックとして意図されたものであるため、2つの理由からビューとして実体化しておく必要があります。
・ステージング層のモデルを参照する後工程のモデル(マート層など)では、常に全てのコンポーネントやビューから可能な限り新鮮なデータを取得し、実体化させておく必要があるため。
・ビューとして実体化させておくことでウェアハウス内のスペースを無駄に消費することを避けるため。一般的にはこの層のデータは、ユーザーからは参照／アクセスされない。
ステージング層のモデルはソースマクロを使用する唯一の場所であり、ステージングモデルはソーステーブルと1:1の関係となる必要があります。これはすなわち、各ソースシステムテーブルに対し、それを参照する1つのステージング層のモデルがあり、後工程で使用するためのエントリーポイントとして機能する、ということになります。
ステージング層でモデルを上記のような形で定義しておくことで、コードをDRYに保つことが出来るようになります。再利用可能な構造としておくことで特定のコンポーネントモデルに対して常に実行したい変換をプッシュ出来るようになります(そして本来そうあるべきです)。同じ変換処理を複数回実行して複雑さを生じさせたり、計算リソースが無駄になるといった可能性もなくなります。後工程の様々なモデルで変換処理が必要となるのであればステージング層でそういった処理を済ませておくことは賢明な判断です。
ステージング層におけるその他検討事項
ベースモデルの使用を検討するケース
場合によっては、クリーンでDRYなステージング層を維持するために、またビルディングブロックを確固たるコンセプトを作成するために、(ステージング層で)幾つかの結合を行う必要が出てくる可能性があります。
その場合、対象となるステージング層のディレクトリにサブディレクトリを作成し「ベースモデル」を用意することをお勧めします。これらは通常ステージング層にあるものとすべて同じプロパティを持ち、生データを直接取得して非結合変換を実行し、ステージング モデルで必要な基本モデルを結合します。
models/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ステージング フォルダーの下にベース レイヤーを構築する最も一般的な使用例は以下の通り。
✅ 任意のテーブルにおける「削除」に関するレコードが別テーブルとなっているような場合
場合によっては、ソースシステムが通常のテーブルと分けて「削除された(データを格納する)テーブル」を保存することがあります。こういった状況の場合、通常のテーブルを削除された(データを格納する)テーブルを結合させて扱う必要が出てきます(削除されたレコードをフィルタリング、またはマーキングするなど)。そんな場合は以下のように定義することで対応が可能です。
通常のテーブルをbaseフォルダ配下に配置、ファイル名も変更しSQLで定義：
jaffle_shop/base/base_jaffle_shop__customers.sql
with
source as (
select * from {{ source('jaffle_shop','customers') }}
),
customers as (
select
id as customer_id,
first_name,
last_name
from source
)
select * from customers削除に関するテーブルも同様にbaseフォルダ配下に配置、ファイル名も変更しSQLで定義：
jaffle_shop/base/base_jaffle_shop__deleted_customers.sql
with
source as (
select * from {{ source('jaffle_shop','customer_deletes') }}
),
deleted_customers as (
select
id as customer_id,
deleted as deleted_at
from source
)
select * from deleted_customersbase配下に格納した定義を活用して、本来やりたかったことを実現：
jaffle_shop/stg_jaffle_shop__customers.sql
with
customers as (
select * from {{ ref('base_jaffle_shop__customers') }}
),
deleted_customers as (
select * from {{ ref('base_jaffle_shop__deleted_customers') }}
),
join_and_mark_deleted_customers as (
select
customers.*,
case
when deleted_customers.deleted_at is not null then true
else false
end as is_deleted
from customers
left join deleted_customers on customers.customer_id = deleted_customers.customer_id
)
select * from join_and_mark_deleted_customers✅ 異なるデータソース由来のものだが、スキーマが同じソースを結合させるような場合
ShopifyのようなSaaSプラットフォームを介して様々な地域で複数のeコマースプラットフォームを運営している場合、それらのデータは完全に同一のスキーマとなりますが、データは全て個別にウェアハウスにロードされる流れとなります。この場合、全てのショップデータが結合されていると後の処理が楽になるため、予め結合を済ませておきたいところです。ここにベースモデルの仕組みを適用させます。この使用例の詳細については下記エントリを御参照ください。
ステージングテーブルの生成を自動化するCodegenパッケージを使用
dbtにおけるModelの定義や設定等を学び、また当エントリで紹介されているようなベストプラクティスを学ぶ上では自ら手を動かしてその挙動等を確かめるのは非常に勉強になる点が多いです。ただその辺りを把握してしまえばあとはこういった作業は自動化させてしまいたいと思うのがエンジニアの常です。
dbtではそんな部分をサポートしてくれる仕組みが「codegenパッケージ」として提供されています。これを使うことで、全てのYAMLファイルとステージングモデルのボイラープレートを自動的に作成してくれます。実運用では必携のパッケージです。
ユーティリティフォルダの活用
ステージングフォルダに含まれない要素となりますが、「ユーティリティフォルダ」の仕組みを頭に入れておくと何かと役立ちます。models/utilitiesフォルダにマクロから生成したり、モデル化に役立つツールを提供するシードに基づいて生成した汎用モデルを保存できます。dbt_utilsパッケージを用いたdate_spineが分かりやすい例です。
dbtにおけるモデル開発のプロセス
モデル開発における通常一般的なプロセスとしては概ね以下のような流れです。
- 最終的なアウトプットのイメージが関係者と一致していることを確認するために、スプレッドシート等で設計をモックアウトすることから始める
- そのアウトプットを生成するSQLを記述し、どのテーブルが関係しているかを特定
- ロジック及び依存関係を把握できたら、必要となる全てのアトミックな要素(モデル＝テーブル)をステージング層に用意
- マートを生成するために作成したロジックに基づき、それらをまとめる
- マートのリファクタリングと最適化を行う
ロジックを分割し、分割した箇所を中間データ層のモデルに戻すことで、全てのモデルがクリーンで読み易くなり、テストも行い易くなります。
まとめ
という訳で、dbtプロジェクト構築に関するベストプラクティス第2弾としてデータウェアハウスにおける「ステージング層」に関する取り組み方の紹介でした。
ステージング層におけるデータの特徴や状況を踏まえた、明確で分かりやすい「べき」「べからず」がまとめられているな、という印象でした。後工程で色々と参照される重要な位置づけのレイヤーであるため、このあたりについてはデータウェアハウス設計のタイミングで考慮しておきたいものです。dbt側でかなり明確なルール、規約決めがなされていますので基本的にはこれをベースにデータウェアハウス側の「レイヤー分け」を進めても良いのかなと思いました。
2
