ちゅらデータアドベントカレンダー 17日目の記事です。（投稿12/27 大遅刻です。ごめんなさい。）
最近寒くなってきました。
きたっぴ@ちゅらデータです。気が付くともう12月。今年はPJにてdbtを利用してETL処理をガリガリ書く事が多かった1年でした。
この1年で何度もdbtの挙動に悩んだことがありましたが1つかなり長時間ハマったことがありましたので
ここで供養の意味も込めて記事にしたいと思います。
対象読者
- dbt について触ったことがある
- DB (primary key)について基礎知識がある
複合キーが一致していても更新されずに新規で追加される場合がある
dbtにて作成したincrementalのモデルに対してデータ挿入を行っていたところ、複合キーが一致していてもデータが更新されず、新規レコードとして追加される現象が発生しました。
dbt incremental modelの挙動について
dbt incremental modelとは、毎回モデル起動時にデータを洗い替えを行うのではなく新しいデータや変更されたデータのみを追加/更新するモデル。モデル起動の時間を短縮できたり過去データを保持することができるのがメリット。
環境
dbt core 1.7.6
dbt - snowflake 1.7.1
DWHはsnowflake利用
対象モデル詳細
{{
config(
materialized = 'incremental',
unique_key = [
'id' ,
'name'
]
)
}}
select
id,
name,
quantity
from base
初回dbt run時
初回実行時ソースとなるテーブルには以下のデータが格納されているものとします。
baseテーブル
| id | name | quantity |
|---|---|---|
| 1 | taro | 100 |
| 2 | null | 200 |
上記、testモデルを初回実行した場合には、以下のテーブルが作成されます。
ソースとなるbaseテーブルと同一のものが出来上がります。
| id | name | quantity |
|---|---|---|
| 1 | taro | 100 |
| 2 | null | 200 |
二回目dbt run時
初回実行時より、id 1,2のquantityを変更、3をbaseテーブルに追加したうえでtestモデルを実行します。
baseテーブル
| id | name | quantity |
|---|---|---|
| 1 | taro | 300 |
| 2 | null | 400 |
| 3 | hanako | 500 |
私の予想では、id 1,2が更新 3が追加されbaseテーブルと同一のものが出来上がると思っていましたが
実際は以下の状態となりました。
| id | name | quantity |
|---|---|---|
| 1 | taro | 300 |
| 2 | null | 200 |
| 2 | null | 400 |
| 3 | hanako | 500 |
調査
勿論、主キー制約にて主キーに指定されたフィールドがNULL値を取ることができないことは承知しておりますが、dbt にてテーブルを作成する場合
- モデル内configにてunique_keyを指定していてもテーブル自体に主キー制約が付与されるわけではない
- モデル内configにて指定されたunique_keyはデータの更新or新規追加の判断に利用される
ため、主キーの一部がnullであっても問題なく動作すると予想して吐いたのですが作成されたモデルを確認するとどうも関係がありそうなため調査を行いました。
結果、upsert/merge判断の為のキー同一チェックにはnull値が入力されている場合の処理が組み込まれていないことが分かりました。
複合キーの一部にnullが入力される場合は新規に全複合キーをタネとしたサロゲートキーを利用することで行いたかった動作は実現できそうですが、自身の予想が外れたためかなり調査に手間どりましたのでここに調査結果を残しておきたいと思います。
追記情報
2024/12/27現在
本問題を解決するためのPRがマージされていそうです。
今回は時間が無く、テストが行えませんでしたが時間に余裕出来たタイミングで試してみたいですね！