今回の課題
dbtを使ってスナップショットの設定に挑戦する
dbtのスナップショットについて
dbtのスナップショットは、Slowly Changing Dimensions Type2という形式で作成される。
Slowly Changing Dimensions Type2は、履歴データをすべて保持していくことができるようにするもので、新しいレコードとして変更情報を追加していく方法。
dbtでは下記の例のように、スナップショットが保持されるようになっている。
（例）
2023-01-01に生成された下記のソーステーブルのデータが、
| id | status |
|---|---|
| 1 | 保留中 |
2023-01-02に下記にUPDATEされたとする。
| id | status |
|---|---|
| 1 | 出荷済 |
最終的にスナップショットのテーブルは、下記のように生成される。
| id | status | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|---|
| 1 | 保留中 | [一意な文字列] | 2023-01-01 | 2023-01-01 | 2023-01-02 |
| 1 | 出荷済 | [一意な文字列] | 2023-01-02 | 2023-01-02 | null |
snapshotで生成されるカラムについて
snapshotのテーブルには、上記のようにソーステーブルのカラムに加えていくつかカラムが追加されるので、それぞれどういった意味なのか調査をしてみた。
-
dbt_updated_at：行が最初にスナップショットに挿入された時のソースデータの更新日時。 -
dbt_valid_from：行が最初にスナップショットに挿入された時の日時。 -
dbt_valid_to：行が無効になった日時。スナップショット作成一回目はNULLになる。 -
dbt_scd_id：スナップショットされたレコードに生成される一意なキー。
スナップショットの作成手順
dbtで作成できるスナップショットについて理解が進んだので、実際にスナップショットを作成してみた。
- snapshotsディレクトリ配下に
.sqlファイルを作成する。 -
.sqlファイルに下記のようなコード・クエリを記述する。 -
dbt snapshotを実行する。
■スナップショットを生成するコード
{% snapshot daily_data_snapshot %}
{{
config(
target_database=[BigQueryで言うところのプロジェクト名]
target_schema=[データセット],
unique_key=[主キー],
strategy='timestamp',
updated_at=[date型のカラム。ここに指定した列を最終更新日時として、既存テーブルと比較して更新された行をINSERTする],
)
}}
select
*
from
{{ source([データセット名], [テーブル名]) }}
{% endsnapshot %}
上記コードの補足説明
configの引数のstrategyについて
strategyでは、timestampを使用する方が良いので、
できるだけソースデータに最終更新日時の列を実装して、timestampを使えるようにすると良い。
strategyには、timestampとcheckという２つの値を入れることができる。
timestampは最終更新日時を表す列があるときに使用可能で、checkよりも高速に実装される。
checkはデータが更新されたかどうか指定した列を比較することで実装されているので、timestampよりも低速。
※最終更新日時を表す列が無い場合は、checkを使用する。
以上のように進めることで、スナップショットを作成することができた。
■参考記事
・dbt入門/スナップショット(snapshot)を使おう
・snapshots(dbt公式ドキュメント)
・【DWH/モデリング】Type2 SCDとdbtのSnapshotについて(SCD:Slowly Changing Dimension)