はじめに

いらない設定やテーブルが増え続けて行くこと、検証環境あるあるだと思います...
BigQueryはGUI上でテーブルの有効期限を設定することで不要なテーブルが自動で消えるようにできますが、Snowflakeでは類似の設定がありません。
きれいな環境を保ち続けるために、Snowflakeでも不要なテーブルは一定期間経過後に自動で消えるよう設定してみました！

今回はTROCCOのデータマート定義やワークフローを使用して設定しています。詳しい設定方法は本記事では省いているので、気になる方は下記記事も合わせてご参考いただけると幸いです

今回のゴール

最終更新から15日以上経過したテーブルは自動で削除される

実装方法を考える

実装に必要な条件や工程を考えます。今回は下記が必要そうです。

工程

① 最終更新から15日経過したテーブルを特定してリスト化する
② リスト化したテーブル名をキーとしてテーブルを削除する
③ ループ実行して、複数テーブルの削除を1回で完了させる
④ スケジュール設定で定期的にタスクが実行されるようにする

条件

残しておきたいテーブルは削除対象から除外できること

ループ実行とは？

カスタム変数ループ実行とは、タスクに埋め込んだカスタム変数に対して、複数の値を展開するよう設定できる機能です。詳細は下記ドキュメントをご参考ください。

①最終更新から15日経過したテーブルを特定する

Snowflakeでは、データベース配下に INFORMATION_SCHEMA が存在します。このスキーマ配下の table 内にある LAST_ALTERED カラムが最終更新日時となっているようです。
これを利用して、最終更新が15日以上前のテーブルを特定しリスト化したいです。

TROCCOのデータマート定義を使って、「最終更新が15日以上前のテーブルを特定しリスト化するクエリ」を設定していきます。

下記のようなクエリになりました。
SELECT文で情報の取得元を指定し、WHERE句で～でないという条件をつけることで削除の対象外とする設定をいれました。

削除対象から除外するテーブルを追加設定しなくても良いように、削除対象外のテーブル名の先頭には demotable_ をつける運用にします

今日から15日後 という条件は毎回日時を指定するのが面倒なのでカスタム変数を使いました

上記クエリの結果を delete_table_list に出力するようGUIで設定していきます

設定に問題がなければ、保存します。

②リスト化したテーブル名をキーとしてテーブルを削除する

同じくTROCCOのデータマート定義でテーブルを削除するクエリを設定します。

SELECT文以外を使用するため、自由記述モードを利用し下記のようなクエリを設定しました。

削除する対象のテーブル名はカスタム変数で指定します。
値は、このあとのループ実行の設定に依るため、適当で大丈夫です

設定に問題がなければ、保存します

③ループ実行して、複数テーブルの削除を1回で完了させる

これまで設定したものをTROCCOのワークフローで組み合わせて、自動で複数テーブルの削除が実行されるようにします。

まず、ワークフローでタスクを下記のように配置しました。
①が完了したら②の実行に移るよう設定しています。

②のタスクに対してループ実行を設定します。
対象タスクの ･･･ から、編集を選択します。

カスタム変数でループ実行にチェックを入れます。
対象カスタム変数では、②で指定したカスタム変数( $table_name$ )を選択します。
その他、クエリの実行先となるSnowflakeの接続情報等を設定します。

クエリでは、SELECT文で、テーブルの削除対象リスト(delete_table_list)のテーブル名が入っているカラム(TABLE_NAME)から値を取り出すように設定します。

上記のように設定することで、 $table_name$ に削除対象のテーブル名を1つずつ挿入し、いれる値がなくなるまで繰り返し実行してくれます。

ループ実行の設定は以上となるので、保存します。

④ スケジュール設定で定期的にタスクが実行されるようにする

設定した内容が定期的に自動で実行されるようスケジュールの設定をします。

設定済みのワークフローの一覧から、今回設定したものをクリックします

下にスクロールすると、スケジュール設定があります。
15日に1度ワークフローを実行して不要なテーブルのチェックや削除を行うようにしました。

設定画面はこんな感じです

これですべての設定が終了です！お疲れ様でした

実行してみる

ワークフローを実行してみます。
結果画面を確認するとジョブの実行は成功していました。

Snowflake側の状況も合わせて確認します。まずは①のタスクの結果からです。

Snowflakeに、テーブルの削除対象リストdelete_table_listが作成されていました。
データプレビューでみると、7つのテーブルがリストアップされています。
スキーマ名や最終更新日時を見て、削除の除外条件に当てはまるテーブルはリストアップされていないことが確認できました。

②のタスクの結果も確認します。
ワークフローの結果画面から、②のタスクが7回実行されたことがわかります。
①でリストアップされたテーブル数と一致するため、問題なさそうです。

SnowflakeでPUBLICスキーマ配下のテーブルを確認しても想定外のテーブルの残存や削除はありませんでした！

おわりに

今回設定するときにつまずいた点は下記の2点でした。

WHERE句のBETWEENで、今日から15日後の間を実行時に自動で入れてくれるようにしたい
delete_table_listのTABLE_NAMEカラムの値を自動で入れてくれるようにしたい

クエリはChatGPTにきいて、出てきたクエリの使い方を調べて正しいか確認するという方法で進めていました。
ただ、この方法だと上記のような複雑な条件を指定したときにクエリも複雑になりすぎて正しいかどうか自分では判断できないことがありました。
なので、できるだけ簡単なクエリで実行できないか考えてカスタム変数を利用してみました。

今まで、カスタム変数という存在は認識しつつも具体的な利用場面に出会わなかったため、なんとなく使うの難しい機能だな、という印象でした・・・。
いざ使ってみると、日付の指定であればGUIで簡単に設定できました！
また、ループ実行の設定でもSELECT文を書くだけで難しいクエリや設定は特に必要なかったので安心しました。やっぱりGUIって便利！

また便利なことができたら記事にします！