【Snowflake×dbt】レコードの削除

dbtとは

dbt (data build tool) は、データパイプラインを構築するためのツールです。データの前処理（ELT）におけるTransform（変換）を担当し、SELECT文を記述するだけでデータウェアハウス内のデータを変換できます。

また、新しいレコードと更新されたレコードのみを変換して読み込む機能もあり、incrementalモデル を使用して実装できます。

しかし、dbtのモデルファイルはSELECT文しか記述できないので、レコードを削除する場合、dbtの hook を使用する必要があります。

Snowflakeとdbtの環境構築に関してはこちらを参照ください：https://knowledge.insight-lab.co.jp/snowflake/-snowflakedbt-environmental-preparation

incrementalモデルについて

テーブルを継続的に更新する際、扱うデータが大きいと、テーブルの再生成に毎回と大幅な計算コスト、実行時間を費やすことになります。

そのためdbtには、変換するデータを制限するモデルタイプ：incrementalモデルが存在します。incrementalモデルを使用すると、モデルが最後に実行されてから更新されたレコードを指定して、テーブルを段階的に構築することができます。

公式 : https://docs.getdbt.com/docs/build/incremental-models

hookについて

hookはさまざまなタイミングで実行できるSQLステートメントを追加できます。

• pre-hook : モデルが構築される前に実行されます。
• post-hook : モデルが構築された後に実行されます。
• on-run-start : dbt run、dbt seedまたはdbt snapshotの開始時に実行
• on-run-end : dbt run、dbt seedまたはdbt snapshotの最後に実行

"dbt run" 後の順序としては以下のようなイメージ。

Hookは全モデルに適用する dbt_project.yml と、単一のモデルに適用する config どちらでも利用できます。今回は config に post_hook を設定してDELETE文を使用してみようと思います。

実行

今回は以下のデータ使って hook を使ったモデルの動作確認をしてみます。

実行するモデルはこちら。

post_hook に DELETE条件として「挿入日時が最新より古いもの」、SELECT文に ft_post の結果から code, name に加えて insert_timestamp として挿入日時を取得、is_incremental() に増分の条件として 「code の値が前回より大きい値」を設定しています。

ちなみに ft_post の結果には上のデータが入っています。

というわけでまず初回実行。

元データにある CODE と NAME に追加で、CURRENT_TIMESTAMP で挿入日時を追加しています。

次に元データにデータを追加して、再度実行してみます。

実行結果。

追加したデータのみ INSERT されていることが確認できました。

処理の内容を確認してみると、、、

SELECT文のあとにDELETE文が実行されており、DELETE文をdbtで使用することができました。

まとめ

今回は、 code と name という数値と文字列のカラムしかない単純なデータを使用して、最新データのみ残すような設定にしていましたが、DELETEの条件を変更して、数か月分など特定の期間のみデータを保持するような設定にして使用することもできます。

hook に関するより詳しい情報については、dbt公式ドキュメントを参照ください。

pre-hook & post-hook : https://docs.getdbt.com/reference/resource-configs/pre-hook-post-hook