dataformのSCDパッケージとdbtのsnapshot機能を比較してみた
こんにちは、風音屋 データエンジニアの妹尾です。
本記事ではdataform の SCD パッケージと dbt の snapshot 機能を比較検証した内容をご紹介します。この記事は、BigQuery Advent Calendar 2023 の 15 日目の記事です。
dataform の SCD パッケージと dbt の snapshot 機能について
両者とも、 SCD（Slowly Changing Dimension） Type2 というデータの変更履歴を取得するための手法に則って実装されています。
データ分析をする上で、データの変更履歴が見たいというケースが往々にしてあると思います。
ECサイトを例に挙げると、商品の価格をセール価格で更新して、セール終了後に戻すというようなオペレーションが行われている場合などです。過去の売り上げを分析する際に、受注データの売り上げと購入された商品データの価格が違うものが存在して、「この時、この商品何円だったんだー」みたいな問題が発生するケースがあると思います。
このような問題を回避するための手法がSCD Type2です。SCD Type2 を採用することには、2つの観点でメリットがあります。
- 分析観点：過去から現在に至る推移や、過去のある時点での集計が可能になります。
- システム観点：過去のデータに関するトラブルシューティングや調査の助けになります。
検証する環境
検証は BigQuery および、 GCP 版dataformとdbt Cloudを使用して実施します。
また今回使用するデータは、社内のデータベースにある読書リストです。reading_listというテーブルを使用します。
dataform の SCD パッケージと dbt の snapshot 機能を比較する
準備
dataform
package.json に SCDのパッケージを追加し、「パッケージをインストール」を押下します。（公式ドキュメントの手順）
{
"name": "リポジトリ名",
"dependencies": {
"@dataform/core": "2.4.2",
"dataform-scd": "https://github.com/dataform-co/dataform-scd/archive/0.4.tar.gz"
}
}
これで、パッケージを使用する準備は完了です。
パッケージのインストールが完了したら package.json の更新内容をコミットしておきましょう。
ちなみに、今回の検証で使用するのは記事執筆時点での最新バージョンである、バージョン0.4です。
バージョン1.0未満ですので、現時点では機能的にできることが限られているということについてご留意ください。
また、インストールの際は公式ドキュメントでパッケージのアップデートがないかをご確認ください。
dbt
dbt Cloud では snapshot 機能を使用する上で特に必要な準備はありません。
ただ、 dbt_project.yml の snapshot-paths で指定されたディレクトリ配下に snapshot 機能を使用するファイルを格納する必要があります。
両者の違い
大きな違いはありませんが、 dataform はパッケージをインストールする必要があり、 dbt はファイルロケーションが決まっているのが特徴でしょうか。
記法
dataform
以下のような JS ファイルを definitions ディレクトリ配下に作成します。（ sqlx ファイルではなく js ファイルなので注意）
設定する項目の詳細についてはdataform-scd のリポジトリをご確認ください。
const scd = require("dataform-scd");
const { updates, view } = scd("reading_list__scd", {
uniqueKey: "id", // 履歴を取得するテーブルのユニークキー
timestamp: "edited_at", // レコードの更新日を記録するカラム
// ソーステーブル
source: {
schema: "{ データセット名 }",
name: "reading_list",
},
tags: ["slowly-changing-dimensions"], // タグ
columns: {id: "ID", edited_at: "Timestamp for updates"}, // カラムのコメント
});
// 追加のカスタマイゼーション
updates.config({
schema: "{ データセット名 }", // テーブルを作成するデータセットの指定
description: "Updates table for SCD",
});
view.config({
schema: "{ データセット名 }", // Viewを作成するデータセットの指定
description: "View for SCD",
});
dbt
以下のような sql ファイルを snapshots ディレクトリ配下に作成します。
設定する項目の詳細についてはdbtの公式ドキュメントをご確認ください。
{% snapshot reading_list_snapshot %}
{{
config(
target_schema='{ データセット名 }',
unique_key='id',
strategy='timestamp',
updated_at='edited_at',
)
}}
select * from {{ source('{ データセット名 }', 'reading_list') }}
{% endsnapshot %}
両者の違い
dataform の SCD パッケージでは全カラムを取得するようになっていますが、 dbt の snapshot 機能では取得するカラムを選択できます。
履歴の保持という観点では、基本的に全カラム取得するのが好ましいように思います。
ただ、ストレージやクエリ料金を抑えたい場合など、状況に応じて取得カラムの選択ができるのはありがたいですね。
基本的な機能
検証する環境に記載の通り、今回は題材として、社内のデータベースにある読書リストを扱います。
データの更新があった場合
読書リストの『SQLアンチパターン』のカテゴリが「SQL」に更新されました。
dataform
dataform の SCD パッケージでは、更新履歴を格納するテーブルと、 SCD Type2 を実現するための View の 2 つが作成されます。
reading_list__scd_updates（テーブル）
更新されたレコードが、新しいレコードとして挿入されていることがわかります。
| id | title | category | created_at | edited_at |
|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | |
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC |
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC |
| 1 | SQLアンチパターン | SQL |
2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC |
reading_list__scd（View）
| id | title | category | created_at | edited_at | scd_valid_from | scd_valid_to |
|---|---|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC |
2023-12-06 06:55:00.000000 UTC |
|
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC |
|
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC |
|
| 1 | SQLアンチパターン | SQL |
2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC |
dbt
reading_list_snapshot（テーブル）
更新されたレコードが、新しいレコードとして挿入されていることがわかります。
| id | title | category | created_at | edited_at | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 6211a387f93e0280e83623faf93ce2d7 |
2023-01-06 06:55:00.000000 UTC |
2023-01-06 06:55:00.000000 UTC |
2023-12-06 06:55:00.000000 UTC |
|
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 8fe4b38a2a4976a961a855f7b885fe0a |
2023-01-06 06:55:00.000000 UTC |
2023-01-06 06:55:00.000000 UTC |
|
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | f83f0203825bfe7e161d876d0633c635 |
2023-01-19 15:58:00.000000 UTC |
2023-01-19 15:58:00.000000 UTC |
|
| 1 | SQLアンチパターン | SQL |
2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | ce63d2a6d1fe5966f7a02716b1f7d843 |
2023-12-06 06:55:00.000000 UTC |
2023-12-06 06:55:00.000000 UTC |
両者の違い
特筆すべき違いは、 dataform だとテーブルと View が 1 つずつ作成されるのに対し、 dbt では 1 つのテーブルしか作成されないという点です。
dataform のパッケージでは更新履歴を updates という suffix の付く実テーブルに格納して、SCD Type2 自体は View によって実現しています。
dataformで作成されるView定義
select
*,
edited_at as scd_valid_from,
lead(edited_at) over (partition by id order by edited_at asc) as scd_valid_to
from
`{ プロジェクト名 }.{ データセット名 }.reading_list__scd_updates`
作成されるカラムの違い
| 説明 | dataform | dbt |
|---|---|---|
| レコード有効期限開始日 | scd_valid_from | dbt_valid_from |
| レコード有効期限終了日 | scd_valid_to | dbt_valid_to |
| システム内部で使用されるユニークキー | なし | dbt_scd_id |
| システム内部で使用されるレコード更新日 | なし | dbt_updated_at |
dbt には、 SCD Type2 の基本的なカラム以外に、システム内部で使用する為のdbt_scd_id（ハッシュ化されたユニークキー）と、dbt_updated_atというカラムが実テーブルに追加されています。
公式ドキュメントによると、
strategyをtimestampに設定した場合、updated_atに指定したカラムの値がdbt_valid_fromとdbt_updated_atに使用されるようです。
つまり、今回の例で言うと、edited_at、dbt_valid_from、dbt_updated_atが全て同じ値を格納するカラムになります。
データの削除があった場合
読書リストのテーブルから『データ指向アプリケーションデザイン』のレコードが削除されました。
dataform
updatesテーブルにレコードが追加されたことが、Viewへのクエリの実行結果から分かります。
reading_list__scd（View）
削除したレコードが、変更なく残っていることがわかります。
| id | title | category | created_at | edited_at | scd_valid_from | scd_valid_to |
|---|---|---|---|---|---|---|
2 |
データ指向アプリケーションデザイン |
データエンジニアリング |
2023-01-06 06:21:00.000000 UTC |
2023-01-06 06:55:00.000000 UTC |
2023-01-06 06:55:00.000000 UTC |
|
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | |
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | |
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC |
dbt
reading_list_snapshot（テーブル）
削除したレコードが、変更なく残っていることがわかります。
| id | title | category | created_at | edited_at | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|---|---|---|---|
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | d8008bc3101891e29df9cb398f2746d6 | 2023-01-19 15:58:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | |
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 5c51b20b4e3ca92a00142fb5c964b941 | 2023-12-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | |
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 8b8bdc4181329a32c9f93f1c41c2b8cc | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | |
2 |
データ指向アプリケーションデザイン |
データエンジニアリング |
2023-01-06 06:21:00.000000 UTC |
2023-01-06 06:55:00.000000 UTC |
701ed5a35ccb1a8fa56a8c4e052384ab |
2023-01-06 06:55:00.000000 UTC |
2023-01-06 06:55:00.000000 UTC |
null |
dbtのsnapshot機能の場合、configでinvalidate_hard_deletes=Trueと設定した場合だと、物理削除の履歴も追うことができます。
dbt_valid_toにdbtのクエリ実行時間が格納されていることがわかります。
| id | title | category | created_at | edited_at | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|---|---|---|---|
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | d8008bc3101891e29df9cb398f2746d6 | 2023-01-19 15:58:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | |
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 5c51b20b4e3ca92a00142fb5c964b941 | 2023-12-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | |
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 8b8bdc4181329a32c9f93f1c41c2b8cc | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | |
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 701ed5a35ccb1a8fa56a8c4e052384ab | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-11 13:16:28.671445 UTC |
両者の違い
dbtはconfigでinvalidate_hard_deletes=Trueと設定することで、物理削除の履歴も捕捉することができます。
dataformだと、現状これを実現する方法はなさそうです。
カラムの追加があった場合
読書リストのテーブルに、statusというカラムが追加され、『SQLアンチパターン』のstatusが「Done」に更新されました。
dataform
reading_list__scd（View）
クエリの実行に失敗します。
ただ、updatesテーブルにもstatusカラムを追加することで、カラムの追加と変更履歴の追跡ができるようになります。
| id | title | category | created_at | edited_at | status | scd_valid_from | scd_valid_to |
|---|---|---|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | ||
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 2023-12-11 13:57:52.500019 UTC | |
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-11 13:57:52.500019 UTC | Done |
2023-12-11 13:57:52.500019 UTC | |
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | ||
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC |
dbt
reading_list_snapshot（テーブル）
statusというカラムと、SQLアンチパターンの新しいレコードが追加されていることがわかります。
| id | title | category | created_at | edited_at | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to | status |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 8b8bdc4181329a32c9f93f1c41c2b8cc | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | ||
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 5c51b20b4e3ca92a00142fb5c964b941 | 2023-12-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 2023-12-11 13:57:52.500019 UTC | |
1 |
SQLアンチパターン |
SQL |
2023-01-06 06:21:00.000000 UTC |
2023-12-11 13:57:52.500019 UTC |
21f5157aa52dde6220273e56951d4853 |
2023-12-11 13:57:52.500019 UTC |
2023-12-11 13:57:52.500019 UTC |
Done |
|
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | d8008bc3101891e29df9cb398f2746d6 | 2023-01-19 15:58:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | ||
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 701ed5a35ccb1a8fa56a8c4e052384ab | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-11 13:16:28.671445 UTC |
両者の違い
データの取得元テーブルにカラム追加があった場合、dataformでは、事前にupdatesテーブルにも同様のカラムを追加する必要があります。
一方でdbtだと、事前の作業は必要ありません。
カラムの削除があった場合
読書リストのテーブルから、statusカラムが削除されました。
dataform
reading_list__scd（View）
クエリの実行に失敗します。
ただ、こちらもカラム追加時同様、updatesテーブルからもstatusカラムを削除することで実行できるようになります。
| id | title | category | created_at | edited_at | scd_valid_from | scd_valid_to |
|---|---|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | |
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 2023-12-11 13:57:52.500019 UTC |
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-11 13:57:52.500019 UTC | 2023-12-11 13:57:52.500019 UTC | |
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | |
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC |
dbt
reading_list_snapshot（テーブル）
カラムの削除は行われていないことがわかります。
| id | title | category | created_at | edited_at | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to | status |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 8b8bdc4181329a32c9f93f1c41c2b8cc | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | ||
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 5c51b20b4e3ca92a00142fb5c964b941 | 2023-12-06 06:55:00.000000 UTC | 2023-12-06 06:55:00.000000 UTC | 2023-12-11 13:57:52.500019 UTC | |
| 1 | SQLアンチパターン | SQL | 2023-01-06 06:21:00.000000 UTC | 2023-12-11 13:57:52.500019 UTC | 21f5157aa52dde6220273e56951d4853 | 2023-12-11 13:57:52.500019 UTC | 2023-12-11 13:57:52.500019 UTC | Done |
|
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | d8008bc3101891e29df9cb398f2746d6 | 2023-01-19 15:58:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | ||
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 701ed5a35ccb1a8fa56a8c4e052384ab | 2023-01-06 06:55:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 2023-12-11 13:16:28.671445 UTC |
両者の違い
dbtは、データ取得元テーブルからカラムが削除された場合でも、クエリの実行が正常に完了し、削除されたカラムの履歴値を保持することができます。
一方で、dataformはデータ取得元テーブルからカラムが削除された場合、同様のカラムをupdatesテーブルからも削除しないと、クエリが実行できません。
カラムのデータ型の変更があった場合
読書リストのテーブルのcreated_atのデータ型がTIMESTAMPからDATETIMEに変更されました。
データ型の変更は、公式ドキュメントで紹介されているコンソールからの変更方法に沿って実施します。
両者の違い
カラムのデータ型の変更があった場合、両者ともクエリの実行に失敗します。
その他の機能
更新を追いたいカラムが限られている場合
dataform
dataform の SCD パッケージでは、バージョン0.4でhashという設定項目が追加され、特定のカラムの更新を追うことができるようになったようです。
更新を追跡したいカラムが複数ある場合は、それらを連結してハッシュ化することでチェックするカラム数を減らすことができます。 これにより差分チェックのクエリパフォーマンスを向上させることができます。
以下は、statusとcategoryというカラムをハッシュ化して、hashに指定した際の更新履歴です。
statusを変更した場合、その変更履歴をupdatesテーブルで取得できていることが、Viewへのクエリの実行結果からわかります。
reading_list__scd（View）
| id | title | status | category | created_at | edited_at | status_category__hashed | scd_valid_from | scd_valid_to |
|---|---|---|---|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | aTVzQQ/vKaYG77BZFf1Eiw== |
2023-01-06 06:55:00.000000 UTC | |||
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | LLDUBrhrgMJhQycDlduBZg== |
2023-01-06 06:55:00.000000 UTC | ||
| 3 | リーダブルコード | Done |
プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-11-19 15:58:00.000000 UTC | v5+bsXliD2cXNSxAzO8PXQ== |
2023-11-19 15:58:00.000000 UTC | |
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC | PnEKV543d6aJxMgMki4bBg== |
2023-01-19 15:58:00.000000 UTC | 2023-11-19 15:58:00.000000 UTC |
status、category以外の値が変更されても、その更新履歴はインサートされません。
また、このhashの更新履歴を追うためにはtimestamp項目に指定した更新日時のカラムも一緒に更新されている必要があります。
ちなみにhashという項目名ですが、ここで使用するカラムはハッシュ値でなくても問題ないようです。
dbt
dbt の snapshot では strategy でデータの差分チェック方法を設定することができます。
strategyをcheckに設定し、check_colsに比較したいカラムを配列（[“status”]）で指定することで更新を追うカラムを選択することができます。
以下は、statusのカラムをcheck_colsで指定した際の更新履歴です。
dataform 同様、ステータスを変更した際、その変更履歴を取得できていることが確認できます。
ここは dataform と違うところですが、更新前と後のレコードのedited_atを見ると、edited_atが更新されていなくても更新履歴を取得できていることがわかります。
| id | title | status | category | created_at | edited_at | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SQLアンチパターン | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 3b8bec75198b06a03a9ca061aa2b5b28 | 2023-12-03 14:41:51.264066 UTC |
2023-12-03 14:41:51.264066 UTC |
|||
| 2 | データ指向アプリケーションデザイン | データエンジニアリング | 2023-01-06 06:21:00.000000 UTC | 2023-01-06 06:55:00.000000 UTC | 818ea10cd60001bda87ab5cd2a440c1f | 2023-12-03 14:41:51.264066 UTC |
2023-12-03 14:41:51.264066 UTC |
||
| 3 | リーダブルコード | Done |
プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC |
4d76a53eeef84990ea2955cfe4d4d9ee | 2023-12-03 14:46:04.369706 UTC |
2023-12-03 14:46:04.369706 UTC |
|
| 3 | リーダブルコード | プログラミング | 2023-01-06 06:21:00.000000 UTC | 2023-01-19 15:58:00.000000 UTC |
e8c0ebe6601ed4abdc6db2c7ba5b7acc | 2023-12-03 14:41:51.264066 UTC |
2023-12-03 14:41:51.264066 UTC |
2023-12-03 14:46:04.369706 UTC |
またstrategyをcheckに指定した場合、dbt_valid_fromとdbt_updated_atの取る値がtimestampの時と違うことがわかります。
strategyをcheckに指定すると、dbt_valid_fromは変更履歴がレコードとして snapshot テーブルにインサートされた日時を格納する。strategyをtimestampに指定すると、dbt_valid_fromはデータの取得元テーブルのレコード更新日付のカラムの値を格納する。
つまり、採用するstrategyによってレコード有効期限開始日の意味合いが以下のように異なるということになります。
strategyがcheckの場合は、dbt_valid_fromは snapshot テーブルにおけるレコードの有効期限開始日を意味する。strategyがtimestampの場合は、dbt_valid_fromはデータの取得元テーブルにおけるレコードの有効期限開始日を意味する。
strategyをcheckに設定して使用する際は、dbt_valid_fromが必ずしもレコードが更新された日時を格納しているとは限らないという点に注意する必要があります。
両者の違い
dbt の snapshot 機能では、strategyをcheckにして使用すると、データ取得元のテーブルにレコードの更新日付を格納するカラムがない場合でも変更履歴を追うことができます。
ただし、パフォーマンスの観点からstrategyにはtimestampを採用することが公式で推奨されています。
パーティショニング
dataform
dataform の SCD パッケージでパーティション分割テーブルを作成する方法は以下です。
『基本的な機能』で紹介したスクリプトにincrementalConfigという項目を追加し、そこでパーティション分割に使用したいカラムを指定します。
TIMESTAMP 型のカラムをそのまま指定するとinvalidQueryのエラーが発生するので、今回はDATE 型に変換したものをedited_dateと命名して指定しました。
const scd = require("dataform-scd");
const { updates, view } = scd("reading_list__scd_partition", {
uniqueKey: "id",
timestamp: "edited_at",
source: {
schema: "{ データセット名 }",
name: "reading_list__scd_staging",
},
tags: ["slowly-changing-dimensions"],
columns: {id: "ID", edited_at: "Timestamp for updates"},
// パーティショニングの設定
incrementalConfig: {
bigquery: {
partitionBy: "edited_date",
},
},
});
updates.config({
schema: "{ データセット名 }",
description: "Updates table for SCD",
});
view.config({
schema: "{ データセット名 }",
description: "View for SCD",
});
dbt
dbt の snapshot 機能でパーティション分割テーブルを作成する方法は以下です。 configにpartition_byという項目を追加しました。
{% snapshot reading_list_snapshot_partition %}
{{
config(
target_schema="{ データセット名 }",
unique_key="id",
strategy="timestamp",
updated_at="edited_at",
partition_by={
"field": "edited_at",
"data_type": "timestamp",
}
)
}}
select *
from {{ source("{ データセット名 }", "reading_list") }}
{% endsnapshot %}
ジョブ履歴を見るとpartition by timestamp_trunc(edited_at, day)という処理が入っていることがわかります。
ちなみにpartition_by内でgranularityを指定することで、day以外にも分割基準（hour、month、year）の設定ができます。
両者の違い
dataformの場合は、パーティション分割に使用するカラムが適切な形にデータ加工されている必要があります。
SCD Type2 を採用する上で、更新日時のカラムはフィルタリング対象になることが多いので、データのスキャン量やクエリパフォーマンスを考えると、こういった設定が手軽にできるのは非常にありがたいです。
まとめ
今まで行った検証結果を基に、両者の各ケースごとの挙動の比較表を作成しました。冒頭でも触れましたが、今回検証で使用したdataformのSCDパッケージのバージョンは0.4（1.0未満）であることをご留意ください。
| ケース | dataform SCDパッケージ（ver 0.4） | dbt snapshot機能 | コメント |
|---|---|---|---|
| 更新履歴の取得 | ◯ | ◯ | 両者とも可能 |
| 削除履歴の取得 | × | ◯ | dbtはconfigでinvalidate_hard_deletes=Trueと設定することで、物理削除の履歴も捕捉することができる |
| 新規カラムの追加があった場合 | △ | ◯ | dataformでは、updatesテーブルにも同様のカラムを追加しないとクエリの実行に失敗する |
| 既存カラムの削除があった場合 | △ | ◯ | dataformでは、updatesテーブルからも同様のカラムを削除しないとクエリの実行に失敗する |
| 既存カラムのデータ型の変更があった場合 | × | × | 両者とも不可 |
| 更新を追いたいカラムが限られている場合 | ◯ | ◯ | dbtの場合はstrategyをcheckに設定することで、更新日付を格納するカラムを持たないテーブルの変更履歴も追うことができる |
| テーブルのパーティショニングがしたい場合 | ◯ | ◯ | dataformの場合は、パーティション分割に使用するカラムが適切な形にデータ加工されている必要がある |
おわりに
本記事では、 dataform の SCD パッケージと dbt の snapshot 機能の比較検証をした内容をご紹介しました。 両者ともデータウェアハウスを構築していく上で必要となる機能を豊富に備えた ETL ツールです。 みなさんもぜひ使ってみてください！
