はじめに
この記事はスタンバイ Advent Calendar 2024の15日目の記事です。
筆者は職場ではデータエンジニアとして業務に従事していますが、最近は dbt に触れる機会が多く、その機能の一つである seed と snapshot について紹介したいと思います。
背景
弊社にもマスタデータが格納されているデータベースが存在しますが、運用面で以下のような課題を抱えています。
- 新規マスタデータ・参照データの取り込みに工数がかかる
- 変更管理に苦労している
直近の業務では dbt を利用してデータ基盤を構築しているのですが、社内のあらゆる箇所に散らばっているマスタデータ・参照データを dbt でうまく管理・運用できないか？と考えています。
その解決のヒントになるかもしれないと期待しているのが、今回紹介する seed と snapshot の機能です。
デモ環境のセットアップ
セットアップが不要な方や機能だけを知りたい方は、この章は読み飛ばして構いません。
今回は、ローカル環境でも構築可能な dbt core + PostgreSQL の組み合わせで進めます。
(筆者は業務での PostgreSQL の利用経験が乏しいため、至らぬ点があるかもしれませんがご容赦ください)
公式ドキュメントの手順に沿って進めます。
セットアップが面倒な方は、ドキュメントで紹介されているサンプルプロジェクトを clone して試してみるのも良いと思います。
dbt のインストール
今回は python で行います。
% python -V
Python 3.12.8
% pip install dbt-core dbt-postgres
(中略)
% dbt --version
Core:
- installed: 1.9.0
- latest: 1.9.0 - Up to date!
Plugins:
- postgres: 1.9.0 - Up to date!
PostgreSQL のセットアップ
以下のような compose.yml を用意します。
今回はデモ用の構成のため、ユーザー名・パスワードは適当なものを設定しています (今回は dummy とします)。
version: '3.1'
services:
db:
image: postgres
container_name: demo-postgres
restart: always
ports:
- 5432:5432
environment:
POSTGRES_USER: dummy
POSTGRES_PASSWORD: dummy
PostgreSQL の docker コンテナを立ち上げ、接続できることを確認します。
% docker-compose up -d
% docker exec -it demo-postgres psql -U dummy
psql (16.4 (Debian 16.4-1.pgdg120+1))
Type "help" for help.
postgres=#
dbt project の作成
dbt init コマンドを実行すると、以下のようにいくつか入力を求められます。
今回は、プロジェクト名を demo とします。
% dbt init
16:01:49 Running with dbt=1.9.0
Enter a name for your project (letters, digits, underscore): demo
(中略)
16:02:04 Setting up your profile.
Which database would you like to use?
[1] postgres
(Don't see the one you want? https://docs.getdbt.com/docs/available-adapters)
Enter a number: 1
(後略)
入力が完了すると、 ~/.dbt/profiles.yml に内容が追記されます (※ファイルが存在しない場合は新規作成されます)。
最終的に、以下のような内容となればOKです。
demo:
outputs:
dev:
dbname: postgres
host: localhost
pass: dummy
port: 5432
schema: public
threads: 1
type: postgres
user: dummy
target: dev
また、dbt init コマンドを実行したディレクトリに demo というフォルダが作成されます。
% cd demo
% tree
.
├── README.md
├── analyses
├── dbt_project.yml
├── macros
├── models
│ └── example
│ ├── my_first_dbt_model.sql
│ ├── my_second_dbt_model.sql
│ └── schema.yml
├── seeds
├── snapshots
└── tests
この状態で dbt debug コマンドを実行して成功すれば準備は完了です。
% dbt debug
16:29:40 Running with dbt=1.9.0
16:29:40 dbt version: 1.9.0
16:29:40 python version: 3.12.8
(中略)
16:29:40 All checks passed!
dbt seed とは？
seed の機能を一言で述べると、「コマンド1つで csv の内容を dbt model として管理できる」となります。
データを取り込むだけならば PostgreSQL の COPY コマンド等でも行えますが、seed を使うと dbt model として管理されるため、下流の dbt model から以下のようなクエリで参照可能となります。
select * from {{ ref('prefectures') }}
seed のユースケース
公式ドキュメントに以下のように記載があります。
基本的には、変更頻度の低い参照データを管理するのに適しています。
Because these CSV files are located in your dbt repository, they are version controlled and code reviewable. Seeds are best suited to static data which changes infrequently.
Good use-cases for seeds:
- A list of mappings of country codes to country names
- A list of test emails to exclude from analysis
- A list of employee account IDs
Poor use-cases of dbt seeds:
- Loading raw data that has been exported to CSVs
- Any kind of production data containing sensitive information. For example personal identifiable information (PII) and passwords.
csvファイルの準備と実行
今回は、都道府県一覧のデータを用意します。
prefecture_code,prefecture_name,prefecture_name_kana
"01","北海道","ホッカイドウ"
"02","青森県","アオモリケン"
"03","岩手県","イワテケン"
csvファイルを seeds/ ディレクトリの直下に配置し、コマンドを実行します。
% dbt seed
(中略)
17:11:59 1 of 1 START seed file public.prefectures ...................................... [RUN]
17:11:59 1 of 1 OK loaded seed file public.prefectures .................................. [INSERT 47 in 0.13s]
(中略)
17:11:59 Completed successfully
17:11:59
17:11:59 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
PostgreSQL のテーブルを確認すると、データが入っていることが確認できます。
postgres=# \d public.prefectures
Table "public.prefectures"
Column | Type | Collation | Nullable | Default
----------------------+---------+-----------+----------+---------
prefecture_code | integer | | |
prefecture_name | text | | |
prefecture_name_kana | text | | |
postgres=# select * from public.prefectures limit 3;
prefecture_code | prefecture_name | prefecture_name_kana
-----------------+-----------------+----------------------
1 | 北海道 | ホッカイドウ
2 | 青森県 | アオモリケン
3 | 岩手県 | イワテケン
(3 rows)
csv のファイル名がそのままテーブル名・model 名となります。
csv のデータを更新して dbt seed を実行すると、データは全て更新後のcsvのデータが反映されます。
seed のオプションまわり
以下の公式ドキュメントにあるとおり、いくつかの設定が可能です。
カラムのデータ型を定義する
通常はcsvであるが故に型定義がしづらいのですが、定義することができます。
seeds:
demo:
prefectures:
+column_types:
prefecture_code: varchar(2)
prefecture_name: varchar(16)
prefecture_name_kana: varchar(32)
dbt seed を実行すると、各カラムのデータ型が定義したものになっていることが確認できます。
postgres=# \d public.prefectures
Table "public.prefectures"
Column | Type | Collation | Nullable | Default
----------------------+-----------------------+-----------+----------+---------
prefecture_code | character varying(2) | | |
prefecture_name | character varying(16) | | |
prefecture_name_kana | character varying(32) | | |
区切り文字を変更する
csv ファイルはカンマ区切りですが、任意の区切り文字を指定できます。以下は tsv 形式 の場合の例です。
seeds:
demo:
prefectures:
+delimiter: "\t"
ただし、残念ながらファイル拡張子は .csv でなければならず、 .tsv にはできないようです (seed コマンドの対象が .csv の拡張子に限定されている模様)。
full-refresh オプション
通常の dbt seed コマンドではデータの更新しか行いませんが、--full-refreshオプションを加えるとテーブル定義を含めてすべて作り直してくれます。
先述の例のように、テーブルのデータ型を変えたい場合や csv のカラム定義が変わった場合に使うとよいでしょう。
以下の結果のように、コマンド結果のメッセージが INSERT ではなく CREATE に変化します。
% dbt seed --full-refresh
(中略)
17:59:41 1 of 1 START seed file public.prefectures ...................................... [RUN]
17:59:41 1 of 1 OK loaded seed file public.prefectures .................................. [CREATE 47 in 0.15s]
(後略)
dbt snapshot とは？
snapshot の機能を一言で述べると、「コマンド1つでスナップショットデータを、履歴込みで蓄積してくれる」となります。
↑...あまり要約になっていませんが、高機能なので一言で表現するのが少し難しいというのもあります。
ソースデータに何も変更がないときは snapshot コマンドを実行しても何も変わらないのですが、変更があった場合その行だけ差分を蓄積してくれます。
Slowly changing dimension について
変更履歴の管理手法の一つとして、 Slowly changing dimension (SCD) が知られています。
SCD には種類がいくつかあるのですが、 dbt の snapshot は Type2 をサポートしています。
SCD Type 2のイメージは、各レコードが有効期間の情報を持っており、それをもってバージョン管理が可能となっています。
SCD Type 2 と dbt snapshot の挙動のイメージ
以下は dbt のドキュメントで紹介されている例です。
初期状態で以下のような「id = 1」のレコードがあり、
| id | status | updated_at |
|---|---|---|
| 1 | pending | 2024-01-01 |
2024-01-02 に 「status = shipped」に更新された場合、最終的なレコードの状態は以下のようになります。
| id | status | updated_at | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|
| 1 | pending | 2024-01-01 | 2024-01-01 | 2024-01-02 |
| 1 | shipped | 2024-01-02 | 2024-01-02 | null |
dbt_valid_from と dbt_valid_to カラムはそれぞれ有効期間の開始日・終了日を表しており、
dbt_valid_to = null のレコードは現在有効であることを意味しています。
snapshot のユースケース
ユースケースと言えるほどのものではないかもしれませんが、公式ドキュメントに以下の記述があります。
Analysts often need to "look back in time" at previous data states in their mutable tables. While some source data systems are built in a way that makes accessing historical data possible, this is not always the case. dbt provides a mechanism, snapshots, which records changes to a mutable table over time.
筆者の言葉で要約すると、「アナリストは過去に遡ってデータの状態を見たいことがあり、dbt はスナップショットというメカニズムで変更管理を記録する」となります。
変更管理されていると過去の特定のバージョンのデータを復元することも可能となり、データ処理の冪等性の観点でも有利となります。
snapshot の準備
ここで述べる設定は、dbt core version 1.9 以降のものです。1.8 以前とはだいぶ内容が変わっているのでご注意下さい。
筆者も試行錯誤しながら試していますが、ベストな構成ではない部分もあると思いますのでご容赦下さい。
実際に dbt snapshot コマンドを実行してみましょう。
まず、テストデータを用意します。
snapshot を利用する場合、原則として以下のカラムが必要です。
- ユニークキー
- 更新日時
CREATE TABLE public.users (
user_id INT PRIMARY KEY,
user_name TEXT NOT NULL,
address TEXT,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL
);
INSERT INTO public.users (user_id, user_name, address, created_at, updated_at)
VALUES
(1, 'Alice', 'Tokyo', now(), now()),
(2, 'Bob', 'Osaka', now(), now());
次に、users テーブルを sources として定義します。
sources の使い方は割愛しますが、詳細はリンク先のドキュメントをご確認下さい。
version: 2
sources:
- name: demo
database: postgres
schema: public
tables:
- name: users
最後に、snapshot の設定を yaml に書きます。
今回は demo 環境のため、snapshot テーブルを作成する database は postgres、 schema は public に統一していますが、本運用の際は適した分け方にして下さい。
snapshots:
- name: users_snapshot
relation: source('demo', 'users')
config:
target_database: postgres
target_schema: public
unique_key: user_id
strategy: timestamp
updated_at: updated_at
hard_deletes: invalidate
ここまで準備ができたら、 dbt snapshot コマンドを実行します。
% dbt snapshot
(中略)
13:49:52 1 of 1 START snapshot public.users_snapshot .................................... [RUN]
13:49:52 1 of 1 OK snapshotted public.users_snapshot .................................... [SELECT 2 in 0.15s]
13:49:52
13:49:52 Finished running 1 snapshot in 0 hours 0 minutes and 0.37 seconds (0.37s).
13:49:52
13:49:52 Completed successfully
(後略)
データベースにも snapshot のデータが出来上がっていることが確認できます。
postgres=# select * from public.users_snapshot;
user_id | user_name | address | created_at | updated_at | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to
---------+-----------+---------+----------------------------+----------------------------+----------------------------------+----------------------------+----------------------------+--------------
1 | Alice | Tokyo | 2024-12-12 13:10:30.990505 | 2024-12-12 13:10:30.990505 | 09fec2a730cb58e382807ce8b7d89e6f | 2024-12-12 13:10:30.990505 | 2024-12-12 13:10:30.990505 |
2 | Bob | Osaka | 2024-12-12 13:10:30.990505 | 2024-12-12 13:10:30.990505 | e237172e4f16572116ee8769318f0c27 | 2024-12-12 13:10:30.990505 | 2024-12-12 13:10:30.990505 |
(2 rows)
snapshot での更新
この状態で、users テーブルのデータを更新してみます。
INSERT INTO public.users (user_id, user_name, address, created_at, updated_at)
VALUES
(3, 'Charlie', 'Fukuoka', now(), now());
UPDATE public.users
SET address = 'Kyoto', updated_at = now()
WHERE user_id = 2;
DELETE FROM public.users
WHERE user_id = 1;
再度 dbt snapshot を実行すると、snapshot のテーブルは以下のように変化します。
postgres=# select * from public.users_snapshot;
user_id | user_name | address | created_at | updated_at | dbt_scd_id | dbt_updated_at | dbt_valid_from | dbt_valid_to
---------+-----------+---------+----------------------------+----------------------------+----------------------------------+----------------------------+----------------------------+----------------------------
2 | Bob | Osaka | 2024-12-12 13:10:30.990505 | 2024-12-12 13:10:30.990505 | e237172e4f16572116ee8769318f0c27 | 2024-12-12 13:10:30.990505 | 2024-12-12 13:10:30.990505 | 2024-12-12 14:01:06.209266
1 | Alice | Tokyo | 2024-12-12 13:10:30.990505 | 2024-12-12 13:10:30.990505 | 09fec2a730cb58e382807ce8b7d89e6f | 2024-12-12 13:10:30.990505 | 2024-12-12 13:10:30.990505 | 2024-12-12 14:01:54.585837
3 | Charlie | Fukuoka | 2024-12-12 14:00:46.240239 | 2024-12-12 14:00:46.240239 | 23ba24f820d3a3a806128f4071f75819 | 2024-12-12 14:00:46.240239 | 2024-12-12 14:00:46.240239 |
2 | Bob | Kyoto | 2024-12-12 13:10:30.990505 | 2024-12-12 14:01:06.209266 | 730d564a1cfba702f8871227f35dc95d | 2024-12-12 14:01:06.209266 | 2024-12-12 14:01:06.209266 |
SCD Type 2 形式のデータになっていることが確認できます。
snapshot のベストプラクティス
ベストプラクティスについては、同ドキュメント内に記述されています。https://docs.getdbt.com/docs/build/snapshots#configuration-best-practices
筆者の言葉で要約すると、以下のようになります。
- timestamp strategy を使いましょう
-
dbt_valid_to_currentを指定すると SQL がシンプルになるので便利です -
unique_keyのカラムは本当にユニークであるか確認しましょう - snapshot のスキーマは model のスキーマとは別のスキーマを使いましょう (※今回の demo 環境はこれを守っていません...)
- ephemeral model を使って、snapshot のクエリをクリーンな状態に保ちましょう
ちなみに、このベストプラクティスの記述内容は以前のドキュメントの記述から少々変わっているようです。
以前の記述は https://dev.classmethod.jp/articles/dbt-try-snapshot/#toc-snapshot の記事で解説されています。全体的な趣旨は変わっていないようなので、こちらもご覧になると良いです。
snapshot のオプションまわり
以下の公式ドキュメントより、特徴的なものをピックアップして紹介します。
物理削除時の挙動を変える
ソース側でレコードが物理削除された場合、snapshot のテーブルにどう反映するかを選択します。
hard_deletes で指定します。
-
ignore: 何もしない -
invalidate:dbt_valid_toカラムに値を設定して無効化します (先述の例ではこれを選択) -
new_record: 新たにdbt_is_deletedカラムを加えて管理します (dbt core 1.9 から選択肢として増えた)
check strategy
更新日時の代わりに、カラムの値の変化をもって更新されたかを判断します。
追加のオプション: check_cols でカラムを指定します。
ですが、先程のベストプラクティスにあるように、なるべく timestamp strategy を使用可能な設計としましょう。
メタデータカラムの名前を変更する
dbt snapshot が生成する dbt_xxx カラム名を任意のカラム名に変更することができます。
dbt core 1.9 から加わった機能です。
dbt_valid_to_current オプション
dbt_valid_to カラムの初期値を指定できます。
オプション未指定時の初期値は NULL となりますが、SQL で参照する際には NULL よりも 9999-12-31 といった遠い未来の日付が入っていたほうが扱いやすいケースもあるため、ユースケースに応じて指定するとよいです。
このオプションも dbt core 1.9 から加わった機能です。
おまけ
seed ・ snapshot ともに dbt model として管理可能なため、メタデータの記述や test の実行が可能です。
詳しくは以下のドキュメントをご確認下さい。通常の dbt model と同じような設定項目なので、dbt に慣れている方は理解しやすいと思います。
- https://docs.getdbt.com/reference/seed-properties
- https://docs.getdbt.com/reference/snapshot-properties
まとめ
dbt の seed と snapshot を用いると、マスタデータの取り込みや変更管理を低いハードルで導入することができます。
特に snapshot については複雑な裏でクエリを実行する必要があるのですが、dbt が全て吸収してくれるのでありがたみが大きいです。
既に dbt を導入されている方、これから導入予定の方、これらの便利な機能をぜひとも使ってみて下さい。