🧹

古いdbtモデルを一括削除するJinjaマクロをいじってみた

に公開
dbt
tech

どうも、stable株式会社でデータエンジニアをしているmyshmehです。

みなさん、dbt開発時のモデル名変更などでDWHに残った古いテーブルって、どうされてますか？ 本記事では、古いモデルを一括削除する便利なJinja マクロを見つけたのでそのご紹介と、一つ不具合を修正したので修正版を共有したいと思います。

問題: stg_orderとstg_ordersとstaging_ordersと。。

データエンジニアを始めて早半年経ちましたが、最近クライアントさんから「（掲題のような）似た名前のモデルがあるんだけど、どっちが正しいの？」と問い合わせされることがありました。

dbt開発をしていると、命名やmaterialize先の名前空間を変えることはよくあると思います。この過程で本来消しておくべきだったモデルを消し忘れていたわけです。

これによって、BIでは古いモデルが公開されてたままなので、分析者が混乱してしまうという問題がありました。

解決策: お手軽dbtマクロ！

Claude Deep Researchで調べさせると、お手製CI Jobやpythonスクリプトでdbt管理でないテーブルを検知するなど色々な方法が見つかりました。中でも、Revolt社の公開したJinjaマクロが良さそうだったので共有です。

Revolt社公開のcleanupマクロは、dbtで指定したDWHの名前空間でdbtが管理していないモデル（ここではorphan modelsと言います）を発見し、それらのDROP文を発行するJinjaマクロです。

ただのJinjaマクロなので、特別なCLIなどのインストールなしで、dbt run-operationで手軽に呼べるところが良いなと思っています！

マクロ概要

大枠、以下のようにdbt run-operationで呼び出すことができます。

dbt run-operation cleanup --args '{
    objects_type: ["BASE TABLE", "MANAGED", "VIEW"],
    dry_run: false,
    tables_to_exclude: [],
    delete_custom_schemas: false,
    schemas_to_exclude: []
  }'
  • objects_type（必須）: 削除するオブジェクトタイプのリスト（'VIEW', 'BASE TABLE', 'MANAGED'）
  • dry_run（デフォルト: true）: trueの場合はDROPコマンドをログに出力のみ、falseの場合は実行
  • tables_to_exclude: 削除対象外のテーブル名のリスト
  • delete_custom_schemas（デフォルト: false）: 孤立したスキーマを削除するかどうか
  • schemas_to_exclude: 削除対象外のスキーマ名のリスト

大まかな処理の流れは以下の通りです。

  1. dbt models/seeds/snapshotsをマッピング
  2. information_schema.tablesをクエリして既存のdatabase objectsを取得
  3. dbtプロジェクトに存在しないdatabase objects (i.e. orphan models) を特定
  4. orphan modelsのためのDROP文を生成
  5. DROP文をログ出力（dry_run=true）または実行（dry_run=false）

使い方

まず、Revolt社公開のcleanupマクロを、macros/<任意の名前>.sqlに保存します。

先にDROP文を実行すると、BI含むDWHの利用サービスに影響が出かねないので、以下のような使い方が良いと思いました。

# 1. まず、dry_runで何がorphan modelsが特定する。
dbt run-operation cleanup --args '{objects_type: ["BASE TABLE", "MANAGED", "VIEW"]}'

# 2. 削除の影響を把握。影響あるモデルは削除できるよう別途対応する。

# 3. 削除対象外のモデルを指定して、dry_run=falseで再実行する。
dbt run-operation cleanup --args '{objects_type: ["BASE TABLE", "MANAGED", "VIEW"], dry_run: false}'

step 1の実行結果例：

Generating cleanup queries.

15:37:29  DROP TABLE IF EXISTS DB.SCHEMA.TABLE1;
15:37:29  DROP TABLE IF EXISTS DB.SCHEMA.TABLE2;
# ...

例えば、step 1をCI Jobとして定期実行して、step 3は都度実行すれば、かなり運用コストを抑えつつ迷い子たちを除去できるのではと思いました！便利ですね！！

ただし、不具合あったよ

対象のdbt projectのtarget環境が、複数DBにまたがってモデルをmaterialiseする場合、このマクロは無効なSQLを展開してしまいます。原因は、Jinjaのfor loopが正しく置かれていないことにありました。

パッと直せたので、以下のgistに修正版マクロを添付しております。よかったらお試しください。
https://gist.github.com/myshmeh/17d0dc69f0a81021089e906d3e4bf6a5

ここまでで、Revolt社のJinjaマクロで古いモデル（i.e. orphan models）を自動的に検知できることがわかりました。次にこれを実運用する上での課題を考えてみます。

運用上の課題

確かに、このマクロは手軽にorphan modelsを特定・削除することができますが、実運用する上でいくつか課題だなと思った点がありました。

削除可否の判断方法

まず、当たり前ですがorphan modelsの中には、すぐに削除できないものがあります。たとえば、BIで参照されている場合、勝手に消してしまっては分析ができなくなってしまいます。

少数の参照先であれば、一つ一つデータエンジニアが確認しても良いのですが、多数参照先がある場合、定期的な業務としては辛いものがあります。

よって、何か定量的指標でもって、削除の影響範囲を特定し、削除可否を判断できるようにする必要があります。たとえば、出力されたorphan modelsに関して、過去N日でリクエストがあるのかチェックし、なければ削除候補に挙げるのはアリだなと思っています。

微妙に自動化しづらい

どれもシェル芸で乗り切れはするのですが。。そもそも、dbt run-operationのログは、どうしても余計なタイムスタンプが出たり、fileへデータ書き込みができなかったりで、CIで自動化しづらいと思いました。
加えて、本記事のマクロはDROP文をstdoutに出力結果として書き込むので、当該出力結果は何がorphan modelsなのか知るという側面ではちょっと扱いづらいなと思いました。

まとめ

いかがでしたでしょうか。本記事では、DWHに残った古いモデル（i.e. orphan models）を自動で削除するRevolt社のJinjaマクロをご紹介し、複数DBを使ったdbt projectに対応する修正版を共有しました。

実運用するにはいくつか考えたい課題がありますが、dbt run-operationで手軽に何がorphanなのかわかるのは嬉しいですね！

みなさんは、どのようにしてorphan modelsに立ち向かっていますか？もし何か良い取り組みをご存知でしたらぜひ共有ください！

References

stableでは、dbt をはじめ、各種データウェアハウス・BI におけるデータ支援業務を実施しています。社内でデータ基盤に関する課題を抱えている方は、お気軽に下記からご相談ください。
https://stable.co.jp/contact

また、採用も行なっているので、データエンジニアリング領域のクライアントワークにご興味がある方もぜひお声がけください。Pittaを開放しているので、よろしければ一度カジュアルに話しましょう。
https://pitta.me/matches/loYNLznDRaqV

Discussion