dbtにおける『SQLの記法』に関するベストプラクティス #dbt

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

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

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

そこで当エントリでは、幾つか展開されている「dbtベストプラクティスガイド」で紹介されているコンテンツの中から『SQLの書き方』に関するものを紹介し、読み解いていきたいと思います。

目次

• SQLに関するスタイルガイド・記法
  • 基本的な部分
  • フィールド、集約、グループ化
  • 結合(Joins)
  • 共通テーブル式(CTE)のインポート
  • 共通テーブル式(CTE)の実装
  • モデルに関する設定
• SQLサンプル例
• まとめ

　

SQLに関するスタイルガイド・記法

　

基本的な部分

• ✅ SQLにおける書式、スタイル維持には『SQLFluff』の利用がオススメです。
  • SQLFluff
  • SQLFluffでは、.sqlfluff設定ファイルをニーズに合わせてカスタマイズすることが出来ます。
  • dbt Labs社で展開しているSQLFluff設定ファイルの例は下記をご参照ください。
    • jaffle-shop-template/.sqlfluff at main · dbt-labs/jaffle-shop-template
  • 標準の.sqlfluffignoreファイルを使用してファイルやディレクトリを除外することができます。構文については.sqlfluffignore構文のドキュメントを参照してください。
    • Configuration — SQLFluff stable_version documentation
  • dbtにおけるSQLFluffの設定や実践については下記エントリ群をご参照ください。
    • Lint and format your code | dbt Developer Hub
    • dbt cloudでのSQL Fluffの設定で困ったこと
    • dbtプロジェクトにSQLFluffを導入する - dely Tech Blog
    • SQLFluffを導入してdbtの開発を加速させる
  • dbt CloudユーザーはSQLFluff Cloud IDE統合をを使用して、SQLを自動的にlintし、フォーマットすることができます。
    • デフォルトのスタイルシートは、このガイドで説明されている dbt Labs のスタイルに基づいていますが、ニーズに合わせてカスタマイズ可能です。
    • 利用に際しては外部ツールをセットアップする必要はなく、Lintを押すだけです。
    • また、その他のフォーマッターとしてsqlfmtも利用可能です。
• ✅ コンパイルしたSQLに含めるべきではないコメントには、Jinjaコメント({# #})を使用してください。
  • Removing Jinja Comments from Compiled SQL - Show and Tell - dbt Community Forum
• ✅ SQLでは末尾にカンマを付けましょう。
  • 参考) Snowflakeでもつい先日、末尾カンマ(Trailing Comma)に対応するようになりました。
    • Snowflake Supports Trailing Comma for the SELECT Clause | by Qinyi Ding | Snowflake | Mar, 2024 | Medium
• ✅ インデントは4スペースとします。
• ✅ SQLの行は80文字以内とします。
• ✅ フィールド名、キーワード、関数名はすべて小文字とします。
• ✅ フィールドやテーブルをエイリアスする場合は、asキーワード使って明示的に定義します。

　

フィールド、集約、グループ化

• ✅ フィールドは集約やウィンドウ関数の前に記述してください。
• ✅ 集計(Aggregations)は、パフォーマンスを向上させるために、他のテーブルに結合する前に、できるだけ早い段階で（できるだけ小さなデータセットで）実行すべきです。
• 列名を列挙するよりも、番号（例：group by 1, 2）で並べたりグループ化したりする方が好ましいです。
  • 理由： Write better SQL: In defense of group by 1
  • 注). 数列以上でグループ化する場合は、モデル設計を見直す価値があるかもしれないことに注意してください。

　

結合(Joins)

• ✅ 明示的に重複を削除するのでなければ、結合にはunion allを優先します。
• ✅ 2つ以上のテーブルを結合する場合は、必ずカラム名の前にテーブル名を付けてください。1つのテーブルからのみ選択する場合は、接頭辞は必要ありません。
• ✅ 結合する際は、都度joinの型を明示すべきです。
  • つまり、joinではなくinner joinと書く...ということです。
• ❌ 結合条件にテーブルの別名（特に頭文字）を使用するのは避けましょう。
  • "customers "と比較して、"c "というテーブルを使うと、それが何であるかを理解するのが難しくなります。
• ✅ 結合を推論しやすくするために、常に左から右に移動します。
  • right joinsは、多くの場合、どのテーブルから選択するか、どのテーブルに結合するかを変更する必要があることを示します。

　

共通テーブル式(CTE)のインポート

• ✅ すべての{{ ref('...') }}文は、ファイル先頭のCTEに記述します。
• ✅ CTEのインポートには、参照するテーブルの名前を付ける必要があります。
• ✅ CTEでスキャンするデータをできるだけ制限する。可能であれば、実際に使用する列のみを選択し、where句を使用して不要なデータをフィルタリングします。
• 記述例：

  with
  
  orders as (
  
      select
          order_id,
          customer_id,
          order_total,
          order_date
  
      from {{ ref('orders') }}
  
      where order_date >= '2020-01-01'
  
  )
  

　

共通テーブル式(CTE)の実装

• ✅ パフォーマンスが許す限り、CTE は単一の論理的な作業単位を実行すべきです。
• ✅ CTEの名前は、その機能を伝えるために必要なだけ冗長である必要があります。
  • user_eventsの代わりにevents_joined_to_usersを使用しましょう。
    • これは適切なモデル名かもしれませんが、特定の関数や変換を説明するものではありません。
• ✅ モデル間で重複する CTEは、独自の中間モデルに抽出する必要があります。
  • 独自のモデルにリファクタリングする必要がある、繰り返されるロジックの塊に注意してください。
• ✅ モデルの最後の行は、最終出力されるCTEからのselect *である必要があります。
  • これにより、モデルの開発中に、モデル内のさまざまなステップからの出力を具体化して監査することが簡単になります。
  • selectステートメントで参照されている CTEを変更するだけで、そのステップからの出力が表示されます。

　

モデルに関する設定

• ✅ モデル固有の属性（sort/distキーなど）は、モデルで指定されるべきです。
• ✅ 特定の設定がディレクトリ内のすべてのモデルに適用される場合は、dbt_project.ymlファイルで指定する必要があります。
• ✅ モデル内の設定については、最大限の可読性を得るために以下のような指定とすべきです。

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

　

SQLサンプル例

上述のルールを踏まえたSQLモデルの記述サンプルは以下のようなものとなります。

with

events as (

    ...

),

{# CTE comments go here #}
filtered_events as (

    ...

)

select * from filtered_events

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

　

まとめ

という訳で、dbtにおけるベストプラクティスの1つ『SQLの記法に関するベストプラクティス』の紹介でした。

合わせて投稿した『***』同様、この辺りのポイントはdbtでモデル開発をしていくうえでの『前提』として身に付けておくべきものと位置付けることが出来ます。『dbtの流儀』として早目に慣れておきたいところですね。

• dbt の記事一覧 | DevelopersIO
• dbtベストプラクティスまとめ の記事一覧 | DevelopersIO
• How we style our SQL | dbt Developer Hub