【Redshift】dbtのgeneric testを利用してdbtでモデル化されていないテーブルとの完全一致テストを実施する
2024.01.26データアナリティクス事業本部のueharaです。
今回は、Redshiftにおいて、dbtのgeneric testを利用してdbtでモデル化されていないテーブル（dbtの外で作成されたテーブル）との完全一致テストを実施するしてみたいと思います。
はじめに
dbtでモデル化したテーブルについて、dbtでモデル化されていないテーブルとの完全一致をテストしたいケースがあります。
例えば、既存のデータマート構築処理をdbtにリプレースする際に、既存のテーブルとdbtによって作成したテーブル（モデル）が完全一致しているか確認したい場合が挙げられます。
今回、完全一致とは 重複したレコードも許容しない ということになります。
すなわち、以下のテーブルAとテーブルBは「完全一致していない」ものとして扱いたいケースになります。
準備
dbtのpackageとして、dbt_utilsを使用します。
packages.ymlに以下を追記して下さい。
packages:
- package: dbt-labs/dbt_utils
version: 1.1.1
完全一致テストの実施
SQLファイルの作成
generic testはSQLファイルで定義されます。dbtでは、以下の2つの場所にgeneric testのファイルを配置することができます。
tests/generic/配下macros/配下
generic testについてより詳しくしりたい方はWriting custom generic data testsをご確認下さい。
今回は、tests/generic/配下に、以下の test_equality.sql を用意します。
{% test test_equality(model, schema_name, table_name) %}
{% set compare_table_relation = api.Relation.create(
schema=schema_name,
identifier=table_name
) %}
with model_with_cnt as (
select
*,
count(*) as cnt
from {{ model }}
group by {{ dbt_utils.star(from=model) }}
),
compare_table_with_cnt as (
select
*,
count(*) as cnt
from {{ compare_table_relation }}
group by {{ dbt_utils.star(from=compare_table_relation) }}
),
model_except_compare_table as (
select * from model_with_cnt
except
select * from compare_table_with_cnt
),
compare_table_except_model as (
select * from compare_table_with_cnt
except
select * from model_with_cnt
),
unioned as (
select * from model_except_compare_table
union all
select * from compare_table_except_model
)
select * from unioned
{% endtest %}
引数の model はgeneric testにおけるデフォルトの引数で、こちらが完全一致の比較対象となるdbtのモデルとなります。
schema_name と table_name はdbtでモデル化されていないテーブルの所在を指すようにします。
dbtでモデル化されていないテーブルは別途 source として定義することもできますが、今回はテスト以外で利用しない想定のため api.Relation.create によりRelationを作成しています。
Relationオブジェクトについてより詳しく知りたい方はdbt Classesをご確認下さい。
8行目〜22行目の処理は、行が完全に一致するレコードをカウントして、新しい列に追加する処理となります。
すなわち、冒頭で図示したテーブルAとテーブルBを以下のように変換する形です。
GROUP BY句で利用している dbt_utils.star() 関数は、fromにRelationオブジェクトを渡すと、そのテーブルのカラムを列挙してくれる関数になります。
これにより、動的に「全レコードでGROUP BY」という処理を実現することができます。
24行目〜32行目は EXCEPT クエリによりそれぞれのテーブルに含まれない行を抽出し、36行目〜40行目でデータを連結しています。
これにより、最後の unioned テーブルが1件でもレコードが抽出されれば両テーブルは完全一致しておらず、0件であれば完全一致していると言えます。
ymlファイルでのテストの指定方法
モデルのスキーマのymlファイルについて、以下のようにテストを設定することができます。
version: 2
models:
- name: table_a
description: 'xxxxxに関するテーブル'
columns:
- name: col1
description: 'カラムxxx'
- name: col2
description: 'カラムyyy'
- name: col3
description: 'カラムzzz'
tests:
- test_equality:
schema_name: public
table_name: table_b
上記はdbtのモデル table_a について、dbtの外で作成された public.table_b との完全一致をテストする形になります。
テストの実行
テストは dbt test コマンドで実行可能です。完全一致していればテストは PASS されます。
$ dbt test --select table_a ... 07:52:12 Completed successfully 07:52:12 07:52:12 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
逆に一致しなかった場合は FAIL となり、SELECTされたデータの件数が表示されます。
$ dbt test --select table_a ... 08:46:20 Got 2 result, configured to fail if != 0 ... 08:46:20 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1
generic testにおいてはどこが一致しなかったかまでは表示されませんので、テストが失敗した際は別途同様のクエリを叩いてデータの中身を確認する必要があります。
最後に
今回は、dbtのgeneric testを利用してdbtでモデル化されていないテーブル（dbtの外で作成されたテーブル）との完全一致テストを実施してみました。
参考になりましたら幸いです。
