データアナリティクス事業本部の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_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ファイルについて、以下のようにテストを設定することができます。
table_a.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の外で作成されたテーブル)との完全一致テストを実施してみました。
参考になりましたら幸いです。