dbt Style Guideを読む

こちらの記事の続きです。
dbt style guideを元にプロジェクトを構築するといいと書かれていたので、dbt style guideを読んで行きます。
公式のリファレンス読むの大事ですよね。
例にもよってモチベーションは、同僚にも読んでもらってデファクトスタンダードを作るためです。

https://github.com/dbt-labs/corp/blob/master/dbt_style_guide.md

まとめ

• dbtに限らず、いい感じのことが書いてあった
• SQL触る人なら一度目を通してもいいかもと思った
• ただ、スタイルガイドは一人で「これに従うんだー」っていうんじゃなくて、チームで合意形成することが大切だろうなと思った(余計な論争に発展しかねない)

Model Naming

• モデルはstaging、marts、base/intermediateの3つのカテゴリーに分類される
• なぜこの設計を採用するかの詳細はこちらのポストを参照してね(僕なりにまとめたものはこちら)
• ファイル、ディレクトリ構造は次のようになるよ

├── dbt_project.yml
└── models
    ├── marts
    |   └── core
    |       ├── intermediate
    |       |   ├── intermediate.yml
    |       |   ├── customers__unioned.sql
    |       |   ├── customers__grouped.sql
    |       └── core.yml
    |       └── core.docs
    |       └── dim_customers.sql
    |       └── fct_orders.sql
    └── staging
        └── stripe
            ├── base
            |   ├── base__stripe_invoices.sql
            ├── src_stripe.yml
            ├── src_stripe.docs
            ├── stg_stripe.yml
            ├── stg_stripe__customers.sql
            └── stg_stripe__invoices.sql

• stg_stripe__invoicesのように、全てのオブジェクト名は複数形にしましょう
• base__<source>_<object>のようにベーステーブルはbase__のプレフィックスをつけよう
• customers__unionedのように、中間テーブルはオブジェクトに対して行われたアクションを示す過去形の動詞で終わるようにしましよう
• fct_orders、dim_customersのように、マートはfactとdimensionに分類しよう

Model configuration

• モデル特有のアトリビュート(例えばsortやdistなど)はモデルの中で指定すべきだよ
• ディレクトリないのモデルに特定の設定を適用する場合はdbt_project.ymlファイルで指定すべきだよ
• In-model configurationsは次のように指定すべきだよ

  {{
    config(
      materialized = 'table',
      sort = 'id',
      dist = 'id'
    )
  }}
  

• マート層では常に(viewではなく)テーブルとして構成した方がいいよ

dbt conventions

• stg_モデル(もし必要だったらbase_モデル)のみがsourceからselectすべき
• 他の全てのモデルはsourceからではなく、モデルを参照すべきだよ

Testing

• 全てのサブディレクトリに.ymlファイルを置くべき、そしてそれぞれのモデルはそのファイルによってテストされるべき
  • stagingフォルダでの命名規則はsrc_sourcename.ymlであるべきで、他のフォルダでの命名規則はfoldername.ymlであるべき(例: core.yml)
• 最低限、それぞれのモデルのprimary keyがユニークかどうかと非nullかどうかはテストされるべき

Naming and field conventions

• スキーマ、テーブル、カラムはsnake_caseであるべき
• 命名はプロダクト由来の用語より、ビジネス由来の用語で行うべき
• どのモデルもprimary keyを持つべき
• モデルのprimary keyはaccount_idのように<object>_idとすべき、これはなんのidを指し示しているかが下流の処理でわかりやすくするためである
• base/stagingモデルではフィールドはカテゴリーから並べるべき
  • 識別子が最初でタイムスタンプは最後のようにするべき
• タイムスタンプはcreated_atのように<event>_atとすべき、そしてUTCにしておくべき
  • 特定のタイムゾーンを指定するときはcreated_at_ptのようにサフィックスで表すべき
• (海外の考えっぽい)価格、収益のフィールドは小数点表示すべき($19.99をセントの整数表示ではなく19.99のように表すべき)
  • もし小数点表示でなく整数表示にするときはprice_in_centsのようにサフィックスで表すべき
• カラムに予約語は避けるべき
• 一貫性はとても重要！customersテーブルのidを表すならuser_idではなくcustomer_idにすべき

CTEs

CTEとはCommon Table Expressionであり、with句で括るやつです

なぜ多くのCTEを使うと良いかの詳細はこのポストを読んでね

• ファイルの最上位階層のCTEでは全て{{ ref('...') }}ステートメントが使われるべきである
• パフォーマンスが許す限り、CTEは単一の論理的な作業単位を実行する必要がある
• CTEの名前は、処理の内容を伝えるためならば冗長になっても良い(短くしてカッコつけるより、処理の内容を正確に伝える方が大切)
• CTE内の複雑なロジックや、注意すべきロジックにはコメントするべき
• モデル間で重複しているCTEは、単一のモデルに出して参照するべき
• finalという名前のCTEか、もしくはそれに相当するCTEを最後にselectするべき、そうするとデバッグ容易性が上がる
• CTEはこのような書式にするべき

    with
  
    events as (
  
        ...
  
    ),
  
    -- CTE comments go here
    filtered_events as (
  
        ...
  
    )
  
    select * from filtered_events
  

SQL style guide

• カンマは末尾につける
• インデントはスペース4つにする
• 1行の文字数は80文字以下にする
• フィールド名や予約語は小文字にする
• フィールドやテーブル名に別名をつけるときはas句を使う
• フィールドは集計やwindowの前に記載すべき
• order byやgroup byにおいては列の名前を列挙するよりも数字で指定する方が好ましい(例: group by 1, 2)
  • もしいくつかのかのカラムでgroup byをするのであれば、モデルの設計を見直す価値があるかもしれない
• union *よりもunion allの方が好ましい
• 結合するときにテーブルに別名をつけるのは避ける(特に頭文字による別名はよくない)
  • customersと比較してcというテーブルが何であるか理解するのが難しくなる
• ２つ以上ののテーブルをジョインする時は、常にカラム名の前にテーブルの別名をつけること
  • 1つのテーブルからselectするだけならいらないよ
• ジョインは明示的に記述する方が親切(joinの代わりにinnner joinと記述するようにする)
  • left joinを常用するべきである、right joinを書いたら、fromとjoinする対象を反対にするべき(right joinを使うな)
• 適切に改行しろ、改行によるパフォーマンス悪化なんてたかが知れてるが、読みづらいことによる脳のリソース消費はえぐい

SQLの例

with

my_data as (

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

),

some_cte as (

    select * 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

),

final as (

    select [distinct]
        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 final

• joinしたものは左からselectするべき

    select
        trips.*,
        drivers.rating as driver_rating,
        riders.rating as rider_rating
  
    from trips
    left join users as drivers
        on trips.driver_id = drivers.user_id
    left join users as riders
        on trips.rider_id = riders.user_id
  
  

YAML style guide

• インデントは2つのスペースにするべき
• リストアイテムはインデントする
• 辞書であるリスト項目を区切るには、必要に応じて改行を使用すべき
• 1行が80文字以下になるようにすべき

YAMLの例

version: 2

models:
  - name: events
    columns:
      - name: event_id
        description: This is a unique identifier for the event
        tests:
          - unique
          - not_null

      - name: event_time
        description: "When the event occurred in UTC (eg. 2018-01-01 12:00:00)"
        tests:
          - not_null

      - name: user_id
        description: The ID of the user who recorded the event
        tests:
          - not_null
          - relationships:
              to: ref('users')
              field: id

Jinja style guide

• Jinjaデリミタを使うときはデリミタの両端にスペースを入れること({{this}}ではなく、{{ this }}のようにする)
• Jinjaの論理ブロックを視覚的に示すために改行を使うこと