見出し画像

データマート開発プロジェクトを通して気づいたメンテナンスしやすいクエリの書き方

rossokuno

マネーフォワード　分析推進室　アナリティクスエンジニアの奥野です！

私は、現在ビジネスユーザーが使いやすいデータマート開発プロジェクトに携わっており、その中でdbt Coreを使ったデータパイプラインの構築を行っています。データアナリスト1名、アナリティクスエンジニア2名の合計3名で開発を進めています。

このブログでは、そのデータマート開発プロジェクトにおいて、メンテナンスしやすいクエリを作るためのアイデアが出てきたので共有したいと思います。

伝えたいことは大きく以下の2つです。

  • SQLリンターSQLfluffを入れるとそれなりに分かりやすいクエリになる

  • リンターではカバーできない分かりやすい書き方のアイデア

では早速紹介していきます！

SQLリンターSQLfluffを入れるとそれなりに分かりやすいクエリになる

SQLfluffというSQLリンターがあります。すでに日本語でも紹介されている記事がたくさんあるのでSQLfluffの詳細は紹介しないのですが、このリンターを導入することによって以下のメリットがあります。
リンターによるメリットそのままなのですが、

  • 人間がSQLをレビューする手間が減る

  • 自動チェックが入ると細かいコードの指摘しなくて良いので気まずくない

  • コーディングルールを設定する手間が減る

というメリットがあります。

私が所属する分析推進室ではデータマートの開発時にCIにより、このリントによるチェックが入るようにしています。これによってSQLfluffが提供するコーディングルールに基づいたある程度整ったクエリが開発されるようになりました。

ところで、このSQLfluffを導入するタイミングで、本来どういったクエリが良いかコーディングスタイルを議論し、コーディングスタイルに沿った設定ファイル(.sqlfluff)を作成することがあると思います。しかし、個人的には、初期段階でこの作業は必ずしも必要ではないと思うようになりました。

私もまさに、前職でSQLfluffを導入するタイミングで、チームでコーディングスタイルを議論し、共有するためにドキュメント化、設定ファイルの作成を行いましたが、これら作業はそこそこ面倒な作業でもあります。

しかし、そのように労力を使って決めたルールを実際に運用してみると、ルールが使いにくいという意見があがることがあり、コーディングスタイルを何回か変更することがありました。例えば、join句のインデントや、group byでは列名だけでなく、番号を許容して欲しい等のリクエストがあがった覚えがあります。

この経験から、はじめから独自にコーディングスタイルを決める必要はなく、可能な限りSQLfluffのデフォルトのルールに従うのが良いのではと思うようになりました。

理由として、デフォルトのSQLfluffのルールに従うだけでも分かりやすいクエリになること、また独自のコーディングスタイルを決める手間が省けるためです。

実際、前述のデータマート開発プロジェクトではSQLfluffのデフォルトルールは全部適用し、一部ルールのオプション設定を適用しているくらいになります。（全部小文字にするとかそういうレベルの設定です）

導入当初はチーム内でSQLfluffのデフォルトのコーディングスタイルに慣れないという声もありましたが、一旦慣れてしまえば特に支障はなく、またどういう書き方にするべきかという疑問もSQLfluffの公式ドキュメント見るだけで済むので独自にコーディングスタイルの管理をしなくて良いので楽でした。

リンターではカバーできない分かりやすい書き方のアイデア

前述のとおり、SQLfluffを導入するだけでそれなりにメンテナンスしやすいクエリを開発できるようになると思います。

ただ、SQLfluffでは注意はされないが、従うとより良いクエリの書き方があることに気づきました。３つだけ紹介します。

この書き方はチームメンバーのデータアナリストのクエリの書き方がわりやすいと評判だったのでそこからヒントを得ています。また、dbt公式にあるクエリのリファクタリング方法のベストプラクティスにほぼ近い内容でもあります。

ソーステーブルをimportするだけのCTEを作ろう

単純にデータを参照するだけのCTE (Common Table Expression)を作りましょう。これらのCTEは極力クエリの頭にくるようにします。これにより、データマートで使用するソーステーブルが簡単に把握できるようになります。

-- customersを呼び出すだけのCTE
with import_customers as (
   select
       customer_id,
       customer_info_a,
       customer_info_b   
   from raw_customers
) 

join、case式、カラム名の変更のような種類の異なるロジックはそれぞれ別のCTEに分けよう

クエリではjoinやcase式、カラム名の変更、データ型の変更等の種類の異なるロジックがあります。それら処理は１つのCTEでも実現できますが、できるだけ別のCTEに分けると処理の流れが追いやすくなります。

簡単な例ですが、このCTEではjoinとカラム生成（date_diff）、カラムの並び替えの3種類のロジックをまとめています。

with legacy_cte as (
   select
       contracts.contract_id,
       contracts.start_at,
       contracts.expire_at,
       date_diff(contracts.start_at, contracts.expire_at, day) as term_in_day,
       contracts.customer_id,
       customers.customer_info
   from contracts
       left join customers
           on contracts.customer_id = customers.customer_id
) 

それぞれの役割に対応したCTEを作ると処理の流れを追いやすくなると思います。

-- joinするだけのCTE
with joined as (
   select
       contracts.contract_id,
       contracts.start_at,
       contracts.expire_at,
       contracts.customer_id,
       customers.customer_info,
   from contracts
       left join customers
           on contracts.customer_id = customers.customer_id
),
 
-- カラム生成
added_logic as (
   select
       contract_id,
       start_at,
       expire_at,
       customer_id,
       customer_info,
       date_diff(start_at, expire_at, day) as term_in_day
   from joined
),
 
-- カラム並べ替え
sorted as (
   select
       contract_id,
       start_at,
       expire_at,
       term_in_day,
       customer_id,
       customer_info,
   from added_logic
)
  

このように役割によってCTEを分割することにより、私達のチームでは、

  • どんな処理をしているのかすぐに把握しやすい

  • 必要な処理が行われているCTEを修正するだけなので改修する際に他のロジックに悪影響を与えてしまうリスクが少ない

というメリットがありました。

ちなみにこの書き方を教えてくれたメンバーは、複雑なクエリになる場合、さらに細かく種類が異なるロジックごとに分けていたりしました。例えば、日数の計算とデータ型を固める処理は、種類が異なるため別のCTEに分けるイメージです。上記のサンプルケースのようなシンプルなケースなら問題ないのですが、処理が複雑になりそうなときはより細分化することはおすすめです。

極力ワイルドカードを使わない

ワイルドカードはサクッとクエリを書きたいときには便利ですが、データマートのメンテナンス性を大きく損ないます。カラムがどのテーブルに存在しているのか分かりづらくなるためです。

例えば、私がデータマートのリファクタリングをする際、以下のようなクエリで困ったことがありました。

ワイルドカードを使用しているため、column_aとcolumn_bはどのソーステーブルにあるかすぐに把握できませんでした。このようにワイルドカードは、メンテナンスの際にコストがかかってしまいます。未来の自分のため、他のメンバーのためにも極力ワイルドカードは使わずに必要なカラム名を列挙することをおすすめします。

-- ワイルドカードを使用しているのでどのソースにcolumn_a, column_bがあるか簡単に把握できない
with joined as (
select
   table_a.*
   table_b.* except(id)
from table_a
   left join table_b
       on table_a.id = table_b.id
)
 
select
   column_a,
   column_b
from joined

おわりに

最後まで読んでいただいてありがとうございます！いかがだったでしょうか？もし、良いと思っていただけたらLikeしていただけると励みになります：）

今後もアナリティクスエンジニアの業務においての知見を共有していきたいと思います！

マネーフォワードの分析推進室にはそのアナリティクスエンジニアリングを一緒に考える仲間がいます。あれやこれやいろんな視点で議論しながら、最適解を導いていくプロセスを一緒に楽しむメンバーを募集しています！


この記事が気に入ったらサポートをしてみませんか？