dbtの基礎的なチュートリアルやトピックスに関してドキュメントでこれまでのドキュメントで理解ができたと思いますが、実際には分析やダッシュボードを提供するために既に動いているクエリやパイプラインがある中でそれらをどのようにdbtに移行していけば良いのか悩むと思います。

dbtへの移行を実現するには「移行」と「リファクタリング」の2つが重要です。このパートではレガシーなSQLをモジュール化されたdbtのモデルに変換するためのプロセスを例示します。

コードの「移行」と「リファクタリング」を行うためには以下のような手順で進めてみて下さい。

• コードを1:1でdbtに移行する
• データベースの生テーブルを参照するのではなく、dbtのSource（ソース）を利用する
• リファクタリングをいくつかのアプローチから選択
• CTEグループ化および外観のクリーンアップの実装
• データ変換を標準化されたレイヤーに分離する
• dbtモデルの出力とレガシーSQLの出力をチェックする

既存のSQLコードを移行する

最初のパートの目標はdbtを使用して、既存のSQLをdbt SQLに移行させ、できるだけ変更を加えないようにすることです。これにより作業sルウための強固な土台を得ることができます。

リファクタリング中は多くのロジックを移動させることになりますが、このパートの理想的にはロジックを変更しないようにすることです。何か変更したくなってもぐっと我慢してくください。「リファクタリング」パートで修正するタスクとして積みましょう。変更をしてしまうと移行が正しく行えたかのチェックが困難になります。

まずは既存のSQLクエリをdbtプロジェクトにコピーをして、プロジェクトの/modelsディレクトリの下に.sqlファイルとして保存します。

dbtプロジェクトのフォルダ構造

コピーができたらdbt runを実行し、ウェアハウスのテーブルにデータを作成しましょう。

このパートは単純そうに見えるかもしれませんが、既存のSQL処理を新しいdbt
SQLの書式に移行する際にdbtなりの表現に修正していく必要があります。先程リファクタリングをしないようにすると言いましたが、このdbtフォーマットのSQLに修正をかけていく作業は必要ですし、dbtへ移行する際によく起きるでしょう。

テーブルの参照をdbtのソースに切り替える

このパートではデータベース上にあるテーブルの参照の仕方をdbtのソースを利用した実装に修正します。

これによりではなくで複数の場所で同じテーブルを同一の参照元として利用することができるようになります。

これをするにはいくつかの理由があります。

ソースの鮮度レポート
ソースを使用することで、ソースのフレッシュネスレポートを実行し、生データが古くなっていないことを確認する機能が有効になります。

依存関係の追跡が容易になります
複数の既存クエリをdbt に移行する場合、ソースを使用するとどのクエリが同じRAWテーブルに依存しているかを確認できます。

これにより、複数の場所で別々に呼び出すのではなく、それらの基本テーブルに対するモデリング作業を統合することができます。

dbtのドキュメントでは、ソースはDAGの中で緑色で表示されます

Analytics-As-Codeの習慣を身につける
ソースはデータ変換パイプライン構築作業を設定ファイルを使い、簡単にトライできるパートです。

sources:
  - name: jaffle_shop
    tables:
      - name: orders
      - name: customers

dbtプロジェクトの/modelsの以下にある.ymlファイルに数行のコードを記述するだけで、データソース（例えばShopifyやGoogleAdWordsやSnowplowなど）と実際のデータベーステーブルの対応をバージョン管理できるようになります。

例えば、あるETLツールから別のツールに行こうし、新しいデータソースがウェアハウスにデータとして格納されるとします。dbtのソース機能では1つの設定ファイルでその変更を行うことで、dbtプロジェクト全体の参照を切り替え、1回のプルリクエストで変更を反映させることができます。

リファクタリング戦略の選択

リファクタリングには「インプレースリファクタリング」と「並行リファクタリング」があります。

インプレースリファクタリング

最初のステップで移植したSQLモデルを実施際の本番環境にデプロイ、動作するように切り替えしていく方式です。
/models/に先ほど作成した既存のクエリを直接修正していきます。

• 長所:
  • リファクタリングが完了したら古いモデルを削除したりする必要がありません。
• 短所:
  • BIツールや下流工程からこのモデルを参照している場合にに最初から正しいモデルを作成しなければならないというプレッシャーがかかります
  • チェック対象のモデルを上書きしてしまうので、正常に動作しているのかのチェックが難しくなる

並行リファクタリング

既存のクエリは/models/legacyに移動させ、モデルを/models/martsフォルダにコピーをして、そのコピーに対して変更加える作業を行います。

• 長所：
  • エンドユーザーへの影響が少ない。リファクタリング中のモデルを参照しているものは、そのモデルを安全に非推奨にできるまでその参照を維持することができます。
  • 最初から正しいものを作るというプレッシャーが少ないので、より小さなプルリクエストをプッシュ＆マージすることがｄけいます。これは実装者にとっても、レビューアにとっても良いことでしょう。
  • 開発ブランチで新旧のモデルを実行し、その比較をすることでより簡単に監査をすることができます。これにより比較するデータが同じかそれに近いレコードを持っている状態なのかを確認することができます。
  • 古いコードは変更されていないので、より簡単に見ることができ、古いモデルをいつ非推奨になるかを決めることができる
• 短所：
  • 平行してモデルを存在させることになり、重複しているように感じられ、多くのクエリを一括で移行する場合には管理するのが面倒に感じるかもしれません。

このチュートリアルでは並行リファクタリングを推奨します。

CTEグループ分けの実施

リファクタリング戦略を選択したら、データモデリングのベストプラクティスに従って、外観のクリーンアップを行い、コードをCTEグループに移動し始めるとよいでしょう。これにより、CTEからSQLスニペットをモジュール化されたdbtデータモデルに移植するためのスタート地点に立つことができます。

CTEとは？

CTEは「Common Table Expression」の略で、SQLスクリプトの実行が終了するまで利用できる一時的なデータです。クエリの先頭でwithキーワードを使用すると、コード内でCTEを使用することができます。

今回リファクタリングするモデルの内部では、4つのパートからなるレイアウトを使用することにします。

1. インポート(import) CTE
2. ロジカル(logical) CTE
3. ファイナル(final) CTE
4. シンプルなSelect文

実際にはこのようになります。

with
import_orders as (
    -- query only non-test orders
    select * from {{ source('jaffle_shop', 'orders') }}
    where amount > 0
),
import_customers as (
    select * from {{ source('jaffle_shop', 'customers') }}
),
import_payment as (
    select * from {{ source('stripe', 'payment') }}
),
logical_cte_1 as (
    -- perform some math on import_orders
),
logical_cte_2 as (
    -- perform some math on import_customers
),
final_cte as (
    -- join together logical_cte_1 and logical_cte_2
)

select * from final_cte

ネストされたクエリがないことに注意してください。このため、ロジックをより簡単に読み取ることができます。もしクエリをネストする必要がある場合は、前のCTEを参照する新しいCTEを作成するだけです。

インポート(import) CTE

まず構成要素から分析に使用する生データを確認しましょう。このパートでは以下の3つのデータソースから構成されているのを例にします。

• jaffle_shop.customers
• jaffle_shop.orders
• stripe.payment

インポートCTEsのコメントでそれぞれCTEを作りましょう。これらのインポートCTEは単純なselect *文だけであるべきですが、必要であればフィルタを持たせることができます。

それぞれに対して適切な参照を指定して、select * from {{ source('schema', 'table') }}を使用するだけです。そして、ハードコードされた参照をすべてインポートCTE名で置き換えます。

ロジカル(logical) CTE

ロジカルCTEでは最終的に生成するデータで使用される固有の変換が含まれており、これらを論理ブロックに分離をさせます。ロジックCTEを整理、分割するためにサブクエリを順番に追っていきます。

サブクエリがネストしている場合は最初の層に到達するまで、最下層のサブクエリから順に引き出し、ロジカルCTEとして定義します。これらのCTEとしてにはサブクエリにつけられたエイリアスの名前をつけます。後で名前を変更することもできますが、今はできるだけ変更しないほうが良いでしょう。

スクリプトが特に複雑な場合はサブクエリを出し終わったあとに、最終結果に対して意味のある順序でCTEが実行されていることを確認するために、実行して正しく処理が行われているか確認してみましょう。

ファイナル(final) CTE

これまでの処理では、通常、最後にselect文が残ります。このselect文は、final CTEと呼ばれる独自のCTEに移動したり、他の人が理解できるような固有の名前を付けることができます。このCTEがモデルの最終的な成果物を決定するのです。

シンプルなSelect文

すべてをCTEに移した後、モデルの最後にselect * from final（最終的なCTE名にもよりますが、似たようなもの）を書きましょう。

これにより、トラブルシューティングの際に、ネストしたクエリを解く必要がなく、誰でも簡単にCTEを触ることができるようになります。

CTEに関するより詳しい背景については、dbt Labsのスタイルガイドを参照してください。

CTEを個々のデータモデルに移植する

SQLコードを1つの長いSQLファイルに閉じ込めておくのではなく、モジュール化された再利用可能な複数のdbtデータモデルに分割していくようにします。

dbt Labsの内部では、このデータモデリングテクニックにほぼ沿って、dbtプロジェクトを構成しています。

このチュートリアルでは、このような構造に従いますが、あなたのチームの慣習があり、提示したものとは異なる可能性はあります。

ステージングモデルの特定

ステージングモデルを特定するために、インポートCTEでインポートしたものを見たいと思います。
先ほどの例ではデータソースとして顧客、注文、支払が対象でした。ステージングモデルのアプローチではウェアハウスから送られてくる生データをクリーンアップして標準化し、下流のモデルで使用する際に一貫性を持たせることにあります。

dbtプロジェクトでは、これらをステージングフォルダに置き、識別しやすいようにファイル名の前にstg_を付けます（Zendeskチャットログはstg_zendesk_chatsとなり、生のzendesk.chatsソーステーブルを基にしたものです）。

通常、ステージング層は各生成ソースを1対1で反映したものであり、ステージング層での変換は本当に軽微なものです。ステージングレイヤーでデータモデルを結合することはほとんどありませんが、その代わりに以下のような変換を行います。

• フィールドタイプのキャスト（FLOATからINT、STRINGからINTなど）により、カラムを下流の結合やレポート作成に適したタイプにする
• 読みやすくするためのカラム名の変更
• 削除されたレコードや不要なレコードのフィルタリング

ステージング層（およびステージング層のみ）でこの種の基本的な変換を行うことで、下流の重い変換層へのジャンプポイントとして機能します。
ソースデータに何か変更があった場合でも、ステージングレイヤーを修正すれば、手動で手を加えることなく下流のモデルに変更が反映されるという確信が持てます。

CTEまたは中間モデルの選択

そして、残ったロジックは、より理解しやすいステップに分割することができます。
まずはCTEを使いますが、モデルが複雑になったり、再利用可能なコンポーネントに分割できるようになったら、中間モデルを検討してもよいでしょう。
中間モデルはオプションであり、常に必要というわけではありませんが、大規模なデータフローが発生する場合に役立ちます。

最終モデル

最終的なモデルは、期待するデータの生成を達成するものであり、今まで構築したコンポーネントを使用するものです。

データモデルのチェック

dbtの外部パッケージであるudit_helperを使用して、結果を監査しましょう。

このパッケージは、リファクタリング前と後の状態の比較クエリを生成し、元のクエリ結果とリファクタリング後の結果を比較して、違いを特定することができます。

もちろん、これらのモデルを監査するために手動で独自のクエリを書くこともできますが、audit_helperパッケージを使うことで先手を打ち、より迅速に差異を特定することができます。

リファクタリング練習の準備は整いましたか？

より詳細なリファクタリングの例と、リファクタリングの練習問題をご覧になりたい方は、無料のオンデマンドコース「手続き型SQLからdbtへのリファクタリング」をご覧ください。

このチュートリアルやコースについて質問がありますか？dbt Community Slackの#learn-on-demand、もしくは#local-tokyoに書き込んでください。