dbtでのSQLモデル記述時に利用推奨されている『共通テーブル式(CTE/Common Table Expression)』について

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

dbtでは、SQLモデルを記述する際に『共通テーブル式(CTE/Common Table Expression)』の利用が推奨されています。

この共通テーブル式=CTE/Common Table Expression(以降CTEと呼びます)、馴染みの無い人には『CTE？何それ？』という感じではあるかなと思います。(実際自分もそうでした)

ということで当エントリではそもそもCTEってどういうものなのか、dbtでこのCTEをどういう風に扱っていくのが良いのか、等について色々と見ていきたいと思います。

目次

• そもそも共通テーブル式(CTE)って何？
• CTEに関心を持つべき理由
• CTE構文の仕組み
• CTEを使用するタイミング
• CTEの記述サンプル
• CTEとサブクエリ(の比較)
• CTEに対するデータウェアハウスのサポート
• まとめ

そもそも共通テーブル式(CTE)って何？

共通テーブル式(CTE/Common Table Expression)とは、シンプルなSQLステートメントから作成された一時的な名前付き結果セットで、SELECT、DELETE、INSERT、またはUPDATEステートメントで使用できます。共通テーブル式そのものの詳細については下記エントリ等をご参照ください。

• CTEs（共通テーブル式）の使用 | Snowflake Documentation
• 第181回　SQLの共通テーブル式（CTE）を使ってみよう | gihyo.jp
• CTEs(Common Table Expressions)について学び直す
• What Is a CTE in T-SQL? An In-Depth Beginner’s Guide with 7 Examples | LearnSQL.com

CTEに関心を持つべき理由

データ分析環境下で実装されているSQLを見て、

• このクエリーのこの部分は何をしているのだろう？
• このクエリで参照されているソースは何なのか？
• このクエリでは、なぜこの依存関係を参照したのか？
• クエリのどの部分が原因なのかわからない...

という局面に出くわしたことは多かれ少なかれあるかと思います。このような考えは、複雑なビジネスロジックを利用し、複数の上流の依存関係を参照・結合するSQLクエリやモデルを書いたんだけれども、期待した結果が出力されないときによく起こるケースと言えるでしょう。

一言で言えば、これらは『データモデルを書こうとしているときにしばしば起こりうるということ』となるでしょう。

こういった問題を解決する、すなわち複雑なコードをより消化しやすく、使いやすくするためにCTEが利用できます。

CTE構文の仕組み

CTEを使用するには、まずWITH文とSELECT文を使用して最初のCTEを定義します。以下はrename_columnsというCTEを使用した例です。

with rename_columns as (

    select

        id as customer_id,
        lower(first_name) as customer_first_name,
        lower(last_name) as customer_last_initial

    from {{ ref('raw_customers') }}
  
)

select * from rename_columns

このクエリでは、まずrename_columnsというCTE を作成し、raw_customersテーブル/モデルから列の名前を変更して小文字にする単純なSELECT文を実行しています。

そして最後のselect * from rename_columnsでは、rename_columnsCTE からのすべての結果を選択しています。

CTEをSQL関数のような古典的な引数を持つものと常に考えるべきではありませんが、CTEに必要な入力は何かと呼ばなければなりません。

• cte_expression_name：これは、他のCTEやSELECT文で参照できるCTEの名前です。この例では、rename_columnsがCTE_EXPRESSION_NAMEとなります。
  • 1つのクエリで複数のCTEを使用する場合、各CTE_EXPRESSION_NAMEは一意でなければならないことに注意。
• CTE_QUERY: CTE によって結果セットが生成される SELECT文です。
  • 上記の例では、select ... from {{ ref('raw_customers') }} が CTE_QUERY です。CTE_QUERY は括弧で囲まれています。

CTEを使用するタイミング

CTEを使ってSQLコードを実装する理由は『クエリの複雑さを単純化し、コードの可読性を高めること』であることがその第一義でありますが、クエリ実装にCTEを使用することの利点には他にもあります。

• 簡素化： 『CTEを使用することでクエリを簡素化できる』、これは具体的には『CTEを使用することでコードの構造、読みやすさ、デバッグのプロセスを簡素化できる』という意味となります。
  • 構造化の確立ができる： CTEを活用することで、複雑なコードをより小さなセグメントに分割することができ、最終的にコードに構造を持たせることができます。dbt Labsでは、インポート、論理的、最終的な構造をCTEに使用することで、dbtモデルに予測可能で組織的な構造を持たせることができます。
    • Refactoring legacy SQL to dbt | dbt Developer Hub
  • 依存関係を簡単に特定できるようになる： クエリ/モデルの最初にすべての依存関係をCTE としてインポートすると、モデルがどのモデル、テーブル、ビューに依存しているかを自動的に確認できるようになります。
  • コード・ブロックに明確なラベルを付けることができる： CTE_EXPRESSION_NAMEを使用することで、CTEが何を実行しているのかにタイトルを付けることができます。これにより、各コードブロックが何を実行しているのかをより深く理解することができ、なぜそのコードが必要なのかを明確にすることができます。これは、問い合わせを作成する開発者にとっても、それを継承する将来の開発者にとっても非常に有用です。
  • テストとデバッグがより簡単になる： クエリが長く、複数の結合や複雑なビジネスロジックを含む場合、クエリが期待した結果を出力しない理由を理解するのが難しいことがあります。クエリをCTEに分割することで、各CTEが正しく動作しているかどうかを個別にテストできます。CTEを消去するプロセスを使用することで、根本的な原因をより簡単に特定することができます。
• ビューの置換： ビューとして存在する可能性のある、あるいは存在した可能性のあるデータをクエリで参照したいことがよくあります。ビューが実際に存在するかどうかを心配する代わりに、CTEを活用してビューから必要な一時的な結果を作成することができます。
• 再利用性が高まる： CTEを使用すると、from文でCTE_EXPRESSION_NAMEを参照することで、作業を重複させることなく、1つのクエリで同じ結果セットを複数回参照できるようになります。

CTEの記述サンプル

下記SQLサンプルはjaffle_shopデモdbtプロジェクトのデータを使ったものとなります。jaffle_shopには3つのテーブルがあり、このクエリでは、3つのCTEを作成し、最終的に購入者が何回購入したかでセグメント化できるようにしています。このCTEを使ったクエリをそれぞれ紐解いてみましょう。

• 最初のimport_orders CTEでは、顧客セグメントを作成するためのデータを保持しているordersテーブルをインポートしています。
  • この最初のCTEはWITH文で始まり、それに続くCTEはWITH文で始まらないことに注意。
• 2番目のaggregate_orders CTEは、import_orders CTEを使用して、フィルタを適用したユーザごとの注文件数を取得しています。
• 最後のsegment_users CTE は、customer_id、count_ordersを選択し、buyer_typeセグメントを作成することで、aggregate_ordersから構築されています。最後のsegment_users CTE には、閉じ括弧の後にカンマがないことに注意。
• 最後のselect * from segment_users 文は、segment_users CTE からすべての結果を選択しています。

with import_orders as (
    
    select * from {{ ref('orders') }}

),
aggregate_orders as (

    select

        customer_id,
        count(order_id) as count_orders

    from import_orders
    where status not in ('returned', 'return pending')
    group by 1

),
segment_users as (
    
    select

        *,
        case 
            when count_orders >= 3 then 'super_buyer'
            when count_orders <3 and count_orders >= 2 then 
                'regular_buyer'
            else 'single_buyer'
        end as buyer_type
    
    from aggregate_orders

)
select * from segment_users

クエリ実行結果のイメージはこんな感じになります。

USER_ID	COUNT_ORDERS	BUYER_TYPE
--------------------------------------
3	3	super_buyer
64	1	single_buyer
94	2	regular_buyer

CTEとサブクエリ(の比較)

CTEについて語る時に、『それってサブクエリじゃダメなの？』と思う方もいらっしゃるかと思います。(私もそうでした)

『サブクエリ』は入れ子になったクエリで、CTE の代わりに使用されることもあります。サブクエリは CTE とは構文が異なりますが、似たような使用例がよくあります。このコンテンツではサブクエリについてあまり深く説明しませんが、CTE とサブクエリの主な違いについて以下のような違いがあります。

CTEに対するデータウェアハウスのサポート

CTEは、すべてとは言わないまでも、ほとんどの最新のデータウェアハウスでサポートされている状況ではあります。特定のデータウェアハウスでCTEを使用する場合の詳細については、以下それぞれのドキュメントをご参照ください。

• [Snowflake] Working with CTEs (Common Table Expressions) | Snowflake Documentation
• [Amazon Redshift] WITH clause - Amazon Redshift
• [Google BigQuery] Query syntax  |  BigQuery  |  Google Cloud
• [Databricks] Common table expression (CTE) | Databricks on AWS
• [PostgreSQL] PostgreSQL Common Table Expression (CTE)

まとめ

という訳で、dbtにおける共通テーブル式(CTE/Common Table Expression)の概要、及びベストプラクティス的な扱い方の紹介でした。

CTEは基本的に、クエリ全体で使用できる一時的なビューです。SQLをより構造化し、読みやすくし、コードのデバッグを簡素化する素晴らしい方法です。適切な名前のCTEを活用することで、上流の依存関係やコードの機能を簡単に特定することができます。また、CTEは同じクエリ内での再帰性と再利用性をサポートします。全体として、CTEはSQLをより整理され、理解しやすいものにレベルアップさせる効果的な方法です。ポイントを把握し、より良い洗練されたSQLコードをdbt上で書いていけるようにCTEを学んで行きましょう！

• dbt の記事一覧 | DevelopersIO
• dbtベストプラクティスまとめ の記事一覧 | DevelopersIO
• CTE in SQL: Quite possibly the best thing to happen to SQL