【Redshift】dbtのgeneric testを利用してdbtでモデル化されていないテーブルとの完全一致テストを実施する

データアナリティクス事業本部の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の外で作成されたテーブル）との完全一致テストを実施してみました。

参考になりましたら幸いです。

参考文献

• dbt_utils
• Writing custom generic data tests
• dbt Classes
• A star (generator) is born
• dbt-utils/macros/generic_tests/equality.sql
• 【Snowflake×dbt】モデルで更新されたテーブル・ビューが完全一致しているかを検証するテスト