ハッピバースデートゥミ〜〜〜♪ハッピバースデートゥミ〜〜〜♪、ハッピバースデーディアわたし〜♪
ハッピバースデートゥミ〜〜〜♪
がく＠ちゅらデータ（49）です。
この記事は、dbtアドベントカレンダー2023のシリーズ2の12日めの記事です。
※先行して公開していたのでごめんなさい・・・・、早めに公開したい事情がありまして、、、
概要
最近、dbtを使っての開発をしていたのですが、やっぱり今一度 Best Practiceを読んでみよう、
特に、SQLの書き方（jinja含めて）でのセオリーを押さえておきたい！
ということで、こちらの公式ドキュメントを読んでみたいと思います。
※開発メンバーにこのあたりのスタイルガイドを把握してもらい、それに沿った開発をチームでやりたい・・・やらないと・・・・というモチベーションです
まずは、dbt Style Guid を読んでみたいと思います
※2023/11/21 時点 ( 2023年11月17日に最終更新日だった ）
dbt Project の Style
なぜ Style が重要か？
Style をどうするか？なんてことは大して重要じゃない！
って思う人もいるかも知れませんが、
- 複数人で開発する際には、コード規約とか開発規約を決めて、遵守する ってことも、開発効率を上げる上でもとても重要（バグも減るはず）
- 可読性が上がることで、レビューなどもしやすくなり、コードの品質も担保しやすくなる
Style に関して重要なこととは？
コードスタイルには2つの重要なポイントがある
- 明瞭さ
- 一貫性
コードを見てすぐに理解できるようになることと、gitでの差分を減らし、レビューしやすくなるのも重要。
※モデル（dbtでいうモデル、SQLファイル）へ変更を加えるとしても、変更してるところだけを見てもらいたい、レビューアーにも優しい
どのように Style を設定すればいいか
コードスタイルは、チームで話し合って決める必要がある
※SQLのコードとか、血みどろの宗教戦争になるので、気をつけて！
※ちなみに、私は 前カンマ派、予約語は大文字派（・・・なんだが全部小文字でええやんって最近思ってる）
あくまで、dbtのStyle Guideは、参考にするのであって、まるっきり従う必要はなくて、チームなりの自分スタイルを作ってそれを遵守するのが良き
可能な限り、フォーマッタやリンター（SQLFluffのような）を使うべき。
人間は間違いを犯すものなので、このあたりを入れると自動的に整形やチェックしてくれるので、開発環境には必ず入れたいところ
※とはいえ、ついここを疎かにしてしまう・・・・ちゃんとしないと
dbt model の Style
ここでいう 「model」 は、dbtにおけるsqlファイル（hoge.sql）
※このファイルの select が、そのまま DWでテーブルやビューとして実体化（materialized）される
Fields and model names （カラム名とモデル名について）
- モデル名は、複数形にする（例えば、
customers,orders,productsみたいに） - モデルは、 Primary Key を必ず持つべき
- モデルの Primary Key の命名規則は、
<object>_id例えば、account_id。大切なのは下流モデルでなんのidかを識別できるようにすること - キーは、文字列型 であること
- 可能な限りモデル間では、同じフィールド名を使用する
- 略語や別名は使用しない。簡潔さよりも読みやすさを重視する（
customer_id→custとか、order_id→oはNG ） - 予約語をカラム名に使わない
- Bool型のカラム名は、
is_かhas_を使う - Timestamp型のカラム名は
<event>_at、またUTCのTimezoneにすべき。ただ、UTC以外を使うのであれば、<event>_at_jstとする ※ここについては、日本においては守らなくて基本JSTで作ってもいいと思う - Date型は
<event>_dateとする。created_date - イベントの日時は、過去形にすべし
created,updated,deleted - 価格／収益フィールドは、10進数の通貨である必要がある。 ＄19.99の場合
19.99※日本ならここは気にしなくてもいいと思う - スキーマやテーブルは、
snake_caseで命名すべし- snake_case とは
- カラム名はビジネス用語を使う。データソースの名前ではない（ データソースが
user_idだとしても、ビジネス的にcustomer_idと呼ばれていたらこちらを使う） - モデルのバージョンには、一貫性を保つために、
customers_v1やcustomers_v2などにする- ※ここは、下位互換性を持つために過去のテーブルを持っておくってことかな
- 以下の例のように、データ型ごとに記載すると、可読性の向上、結合エラーが最小限に抑えられる。
- idごと
- stringごと
- numericごと
- booleanごと
- date, timestamp ごと
with
source as (
select * from {{ source('ecom', 'raw_orders') }}
),
renamed as (
select
---------- ids
id as order_id,
store_id as location_id,
customer as customer_id,
---------- strings
status as order_status,
---------- numerics
(order_total / 100.0)::float as order_total,
(tax_paid / 100.0)::float as tax_paid,
---------- booleans
is_fulfilled,
---------- dates
date(order_date) as ordered_date,
---------- timestamps
ordered_at
from source
)
select * from renamed
SQLのStyle
Basic
- SQLFluff を使って、自動的に整形するようにしましょう
-
.sqlfluffをカスタマイズして、ご自身のコードスタイルに合う形で設定してください - dbtLabsのSQLFuffのconfig file を参考にしてみてください
- 不要だったり無視するルールは、
.sqlfluffignoreで設定してね
-
- コンパイル後のSQLに出したくないコメントは Jinjaのコメント
{# #}で記述する - カンマは後カンマ ※個人的に田代は前カンマ派
- インデントはスペース4つ
- SQLは一行80文字以下
- フィールド名、キーワード、ファンクションは、小文字
- フィールドやテーブルに別名を付ける場合は
asを明示的に使う
Fields, aggregations（集計）, and grouping
- Fieldは、集計やウィンドウ関数より前に指定する
- パフォーマンスを向上させるために、集計はできるだけ早い段階で行なう。テーブルの結合より前に行うのが望ましい
- グループ化(group by)と順序付け（order by)については、カラム順の数字が「カラム名」よりも優先される。
Joins
- 重複を排除する必要がないなら、
union allを使うべし-
unionは暗黙的なソートがかかるので、重複排除が必要ないなら必要なし
-
- 2つ以上のテーブルを
joinするためには、常に、テーブル名をカラム名の前に記載すべき。テーブルが必要なら必要ないcustomers.customer_id
-
joinのタイプは明示する-
inner joinは、joinと記述できるけど、inner joinにしてね
-
- テーブル名の別名は使わない
-
customers.customer_idをc.customer_idみたいに書かないように - ※これ私はやってる・・・・
-
- 結合は常に左から右へ
- left joinを使うようにする・・・かな？
- （原文） Always move left to right to make joins easy to reason about - right joins often indicate that you should change which table you select from and which one you join to.
Import CTEs
- すべての {{ ref('...') }} や {{ source('...','....') }} ファイルの先頭のCTEに配置する
- Import CTEは、参照テーブルにちなんだ名前にしましょう
- Import CTEには、利用するカラムのみ（DW製品はカラムが増えると走査量が増えるので）、可能なら
where句を使用して不要なデータを除外する
with
-- Import CTEs
orders as (
select
order_id, -- 必要なカラムのみを列挙 * とかはできるだけしない
customer_id,
order_total,
order_date
from {{ ref('orders') }}
where order_date >= '2020-01-01' -- 不要なデータをフィルタリングする
)
select * from orders
Functional CTEs
- パフォーマンスが許せば、CTEは単一の論理的な作業単位で記述する
- ※このあたりは、Web系のSQLとデータ分析SQLでは、書き方がガラッと変わりますね
- CTE名には、冗長でもいいから、そこで処理する機能の内容を「文章的」な名前＝冗長な名前 をつけると良い。
events_joined_to_usersみたいな。user_eventsもいいが、joinするってことがスポイルされている モデル間で重複するCTEは、バグの温床になるので、1ファイルの中にCTE名はユニークにしておいた方がいい（訳に自信なし）- CTEがモデルを横断して複製されて使われている場合は、独立した中間モデルとして作成すべき
- きたやんさんご指摘感謝！
- 繰り返されるロジックの塊があればそれらを独自のモデルにリファクタリングする必要がある
- 2024/2/16追記
- （原文）CTEs that are duplicated across models should be pulled out into their own intermediate models. Look out for chunks of repeated logic that should be refactored into their own model.
- モデルの最後の行は
select *とすべし- というのも開発中（もしくは後から）に、モデルの様々なステップの出力を確認したくなる場合があります。
select * from orders where order_id = 'hogehoge'みたいな
- というのも開発中（もしくは後から）に、モデルの様々なステップの出力を確認したくなる場合があります。
Model Configuration
- Model独自の設定はモデルファイル（.sql）に記載する
- 全体で適用したい場合は、
dbt_project.yml - モデルファイル内のconfigrationの記載は読みやすくするために下記のように書く
{{
config(
materialized = 'table',
sort = 'id',
dist = 'id'
)
}}
Example SQL（むしろこれは、テンプレート）
だいたいこんな感じのブロックで書きましょう
with
events as (
...
),
{# CTE comments go here #}
filtered_events as (
...
)
select * from filtered_events
具体的なSQL例は
with
my_data as (
select
field_1,
field_2,
field_3,
cancellation_date,
expiration_date,
start_date
from {{ ref('my_data') }}
),
some_cte as (
select
id,
field_4,
field_5
from {{ ref('some_cte') }}
),
some_cte_agg as (
select
id,
sum(field_4) as total_field_4,
max(field_5) as max_field_5
from some_cte
group by 1
),
joined as (
select
my_data.field_1,
my_data.field_2,
my_data.field_3,
-- use line breaks to visually separate calculations into blocks
case
when my_data.cancellation_date is null
and my_data.expiration_date is not null
then expiration_date
when my_data.cancellation_date is null
then my_data.start_date + 7
else my_data.cancellation_date
end as cancellation_date,
some_cte_agg.total_field_4,
some_cte_agg.max_field_5
from my_data
left join some_cte_agg
on my_data.id = some_cte_agg.id
where my_data.field_1 = 'abc' and
(
my_data.field_2 = 'def' or
my_data.field_2 = 'ghi'
)
having count(*) > 1
)
select * from joined
Python Modelの書き方
Pythonには、成熟した formatterやlinterがたくさんあります。
- Black formatter
- ruff linter
を使っています。
import pandas as pd
def model(dbt, session):
# set length of time considered a churn
pd.Timedelta(days=2)
dbt.config(enabled=False, materialized="table", packages=["pandas==1.5.2"])
orders_relation = dbt.ref("stg_orders")
# converting a DuckDB Python Relation into a pandas DataFrame
orders_df = orders_relation.df()
orders_df.sort_values(by="ordered_at", inplace=True)
orders_df["previous_order_at"] = orders_df.groupby("customer_id")[
"ordered_at"
].shift(1)
orders_df["next_order_at"] = orders_df.groupby("customer_id")["ordered_at"].shift(
-1
)
return orders_df
Jinjaの書き方
- jinjaでの関数の書き方は、
{{ this }}({{this}}ではなく、スペースを入れよう） - 改行を使って、jinja の論理ブロックを視覚的に示します
- Jinjaブロック内に、インデントは４つのスペースで。
- Jinjaのホワイトスペース制御についてはあまり気にしなくて、読みやすければOK
{% macro make_cool(uncool_id) %}
do_cool_thing({{ uncool_id }})
{% endmacro %}
select
entity_id,
entity_type,
{% if this %}
{{ that }},
{% else %}
{{ the_other_thing }},
{% endif %}
{{ make_cool('uncool_id') }} as cool_id
YAMLの書き方
- インデントはスペース２つ
- リスト項目はインデントする必要あり
- 必要に応じて、新しい行を辞書であるリスト項目を区切る
- YAMLの行は80文字以下
- 互換性のあるIDE及びYAMLフォーマッタで dbt JSON スキーマを使用する。（YAMLファイルを検証して、自動的にフォーマットするために、Prettierの使用を推奨）
あなたの番ですよ！
BYO Styles
これまでのおすすめを参考に、ご自身独自のプロジェクトスタイルを作ってください。
よければ、コミュニティなどに連絡してくれると嬉しい・・・とのこと
※私ははずかしくてやらないだろうなぁ・・・
Pre-commit hooks
チームにちゃんとスタイルガイドを遵守してもらうために、コミット前フックを使用して、コミットされる前にスタイル違反がないか？のチェックをして、自動的に修正してくれるようにするとよいよ
にいいセットがあるのでこちらを参考にすると良さそう
Style guide template
# dbt Example Style Guide
## SQL Style
- Use lowercase keywords.
- Use trailing commas.
## Model Organization
Our models (typically) fit into two main categories:\
- Staging — Contains models that clean and standardize data.
- Marts — Contains models which combine or heavily transform data.
Things to note:
- There are different types of models that typically exist in each of the above categories. See [Model Layers](#model-layers) for more information.
- Read [How we structure our dbt projects](/best-practices/how-we-structure/1-guide-overview) for an example and more details around organization.
## Model Layers
- Only models in `staging` should select from [sources](https://docs.getdbt.com/docs/building-a-dbt-project/using-sources).
- Models not in the `staging` folder should select from [refs](https://docs.getdbt.com/reference/dbt-jinja-functions/ref).
## Model File Naming and Coding
- All objects should be plural.
Example: `stg_stripe__invoices.sql` vs. `stg_stripe__invoice.sql`
- All models should use the naming convention `<type/dag_stage>_<source/topic>__<additional_context>`. See [this article](https://docs.getdbt.com/blog/stakeholder-friendly-model-names) for more information.
- Models in the **staging** folder should use the source's name as the `<source/topic>` and the entity name as the `additional_context`.
Examples:
- seed_snowflake_spend.csv
- base_stripe\_\_invoices.sql
- stg_stripe\_\_customers.sql
- stg_salesforce\_\_customers.sql
- int_customers\_\_unioned.sql
- fct_orders.sql
- Schema, table, and column names should be in `snake_case`.
- Limit the use of abbreviations that are related to domain knowledge. An onboarding employee will understand `current_order_status` better than `current_os`.
- Use names based on the _business_ rather than the source terminology.
- Each model should have a primary key to identify the unique row and should be named `<object>_id`. For example, `account_id`. This makes it easier to know what `id` is referenced in downstream joined models.
- For `base` or `staging` models, columns should be ordered in categories, where identifiers are first and date/time fields are at the end.
- Date/time columns should be named according to these conventions:
- Timestamps: `<event>_at`
Format: UTC
Example: `created_at`
- Dates: `<event>_date`
Format: Date
Example: `created_date`
- Booleans should be prefixed with `is_` or `has_`.
Example: `is_active_customer` and `has_admin_access`
- Price/revenue fields should be in decimal currency (for example, `19.99` for $19.99; many app databases store prices as integers in cents). If a non-decimal currency is used, indicate this with suffixes. For example, `price_in_cents`.
- Avoid using reserved words (such as [these](https://docs.snowflake.com/en/sql-reference/reserved-keywords.html) for Snowflake) as column names.
- Consistency is key! Use the same field names across models where possible. For example, a key to the `customers` table should be named `customer_id` rather than `user_id`.
## Model Configurations
- Model configurations at the [folder level](https://docs.getdbt.com/reference/model-configs#configuring-directories-of-models-in-dbt_projectyml) should be considered (and if applicable, applied) first.
- More specific configurations should be applied at the model level [using one of these methods](https://docs.getdbt.com/reference/model-configs#apply-configurations-to-one-model-only).
- Models within the `marts` folder should be materialized as `table` or `incremental`.
- By default, `marts` should be materialized as `table` within `dbt_project.yml`.
- If switching to `incremental`, this should be specified in the model's configuration.
## Testing
- At a minimum, `unique` and `not_null` tests should be applied to the expected primary key of each model.
## CTEs
For more information about why we use so many CTEs, read [this glossary entry](https://docs.getdbt.com/terms/cte).
- Where performance permits, CTEs should perform a single, logical unit of work.
- CTE names should be as verbose as needed to convey what they do.
- CTEs with confusing or noteable logic should be commented with SQL comments as you would with any complex functions and should be located above the CTE.
- CTEs duplicated across models should be pulled out and created as their own models.
Discussion
この部分に関してですが、私は「CTEがモデルを横断して複製されて使われている場合は、独立した中間モデルとして作成すべき」という趣旨で解釈しています！
返信コメントが遅れましたこと、深く深く・・ごめんなさいーーーー
そうですね、そのとおりです＞＜