Snowflake Knowledge - INSIGHT LAB

【Snowflake×dbt】モデルで更新されたテーブル・ビューが完全一致しているかを検証するテスト

作成者: uta｜2023年4月07日

はじめに

概要

dbtのtest機能を使用することで、SQLプロジェクトのテストを自動化できます。

また、dbtでは多種多様なテストがあるので、SQLプロジェクトが期待どおりに機能することを様々な角度から確認できます。基本的なテストとして、NULLチェック、重複レコードチェック、範囲チェック、比較チェックなどがあります。これらのテストを組み合わせることで、SQLプロジェクトをより堅牢にすることができます。

本記事では、このテスト機能を用いて本番環境のオブジェクトと検証環境のオブジェクトの結果が完全に一致しているかを検証するテストを作成します。

対象

  • DWHとしてSnowflakeを使用している方
  • dbtへの移行やリファクタリングを考えている方
  • dbtでオリジナルのテストをつくりたいと考えている方

 

テストとは

dbtのテストはsingulartestとgenerictestの２種類があります。本記事で使用するのはgeneric testsのcustom generic testsです。

https://docs.getdbt.com/docs/build/tests

singular test

最もシンプルな形のテストで、失敗した行を返す SQL クエリを書くことができれば、そのクエリを tests/配下に.sql ファイルに保存することができます。

generic test

引数を受け付けるパラメータ化されたクエリによるテストです。テストクエリは、特別なテストブロック（マクロのようなもの）で定義されます。一度定義すると、.ymlファイル全体でジェネリックテストの名前を参照することができ、モデル、カラム、ソース、スナップショット、シードに定義することができます。

tests/generic/配下に.sql ファイルを配置すれば独自のテストを作成することもできます。

https://docs.getdbt.com/guides/best-practices/writing-custom-generic-tests

完全一致テストとは

完全一致テストは、指定したSQLクエリの出力結果が正しいかどうかを検証します。このタイプのテストは、dbtへの移行やdbtのリファクタリングなど、完全一致の出力が期待される場合に使用されること想定しています。完全一致テストは、データ不足、余分なカラム、または異なる順序のカラムが含まれる場合に失敗します。

完全一致テストを実現するために、Snowflakeの機能「HASG_AGG()」を使用します。HASH_AGGの存在知ったのはこちらの記事でした。

今までファイル同士の比較で検証を行っていたため、部分的な検証しかできず、数値検証にかなりの時間を要していました。しかし、HASH_AGGを使用すれば件数の多いテーブルでも、ものの数十秒で終わります。(70,000,000レコード30カラムのテーブルで30秒くらいでした)

これをdbtのテストに落とし込むことで、継続的なテストを行うことができるので、検証の精度とスピードが段違いで良くなりました。

 

環境

Snowflake

  • ウェアハウス：X-Small
  • データベース（本番想定）：TR_KEITA_UEHARA
    • スキーマ：
      • RAW
        • R_SALE（外部テーブル）
      • STAGING（カスタムスキーマ）
        • S_SALE
  • データベース（検証想定）：TR_DEV_KEITA_UEHARA
    • スキーマ：
      • RAW
        • R_SALE（外部テーブル）
      • DEV_UTA_STAGING（デフォルトスキーマ_カスタムスキーマ）
        • S_SALE

dbtCloud

フォルダ構成

 
 

test_validate_objects.sql

ポイント

  • 3行目
    • 検証環境はtargetスキーマ_customスキーマ、本番環境はcustomスキーマなので、model.schemaからtargetスキーマを除いたものを変数に格納しています。
  • 7、10行目
    • サブクエリ内のクエリをいじれば、別の用途での一致検証に変えることができます。
  • 13~15行目
     
    • minusで重複を排除し０件になれば完全一致とみなします。
    • ※minusの使用について結果が複数行になる場合は注意が必要です。

_staging_models.yml


ポイント

  • 5行目
    • カラムではなくモデルに対してテストを設定します。

 

s_sale.sql


 
 

ポイント

  • 9、12、15、30行目
    • sourceで定義した外部テーブルをデータソースとしています。

残りのファイル内容は記事の最後にまとめています。

 

結果

完全一致

 
 

ハッシュ値

PROD：-7,068,827,162,810,818,942

DEV：-7,068,827,162,810,818,942

データ不足

 
 

ハッシュ値

PROD：-7,068,827,162,810,818,942

DEV：-6,511,983,957,286,626,285

余分なカラムが含まれる

 
 

ハッシュ値

PROD：-7,068,827,162,810,818,942

DEV：3,215,789,804,469,934,941

カラムの順序が異なる

 
 

ハッシュ値

PROD：-7,068,827,162,810,818,942

DEV：-7,097,726,097,789,478,326

 

まとめ

本番環境のオブジェクトと検証環境のオブジェクト同士の完全一致を検証するテストを紹介しました。ここで紹介したのは「全データでの完全一致」でしたが、test_validate_objects.sqlの7、10行目のSELECT文を工夫することで、様々な条件下での完全一致テストが行えると思います。

このテストをtargetがdevの時だけ実行されるようにできると、開発時のみテストされるようにできると考えています。しかし方法が見つからず、現状は「テスト設定→テスト成功→YAMLから消す」という使い方になってしまいます。。

先日もdbtのイベントに参加させて頂き、同じ境遇の方の話を聴いたり、知らなかった便利な機能を知れたりと、dbtについてとても楽しく学ばせてもらっています。dbtを触り始めたばかりの新米ですが、私も「これいいな！」と思った機能やテクニックがあれば紹介したいと思います！

 

参考

テストについて

https://docs.getdbt.com/docs/build/tests

https://docs.getdbt.com/guides/best-practices/writing-custom-generic-tests

HASH_AGGについて

https://note.com/ym202110/n/n25340f9a7828

https://docs.snowflake.com/ja/sql-reference/functions/hash_agg

 

環境の続き

_raw_sources.yml

 
 

dbt_project.yml

 

ポイント

  • 36行目
    • transient（一時テーブル）での作成をしないようにしています。
  • 38~42行目
    • targetでデータベース環境を振り分けています。
  • 43~46行目
    • models配下のディレクトリでスキーマを振り分けています。

get_custom_schema.sql

 

 ポイント

  • 5~14行目
    • targetがprodの場合、カスタムスキーマが設定されていればスキーマ名は「カスタムスキーマ」となるよう設定
  • 16~25行目
    • targetがprod以外の場合、カスタムスキーマが設定されていればスキーマ名は「デフォルトスキーマ_カスタムスキーマ」となるよう設定
完全な記事を表示