【Snowflake】データ品質監視を実現するためのデータメトリクス関数の使い方
はじめに
こんにちは！システムサポートの斎藤です。
みなさんは、Snowflake にデータの品質を監視できる機能が追加されたことはご存じですか？
2024年7月にデータ品質監視 (Data Quality Monitoring：DQM) のための仕組みが GA (一般公開) されました。これにより、データメトリクス関数 (Data Metric Functions：DMF) を使用して、データの重複・NULL・一意性などのデータ品質を担保する重要な値を継続的に監視する仕組みを構築可能になりました。
組み込みのシステム DMF の他に、ユーザー側でカスタム DMF を作成して取得した値を監視することも可能です。
業務に適合した基準を用いてデータの異常を迅速に検知することは、特にデータドリブンな意思決定を行う組織にとって非常に有効な機能と言えます。
今回は DMF を使用して、データ品質に関わる値を取得するための設定を紹介します！
本記事で分かること
本記事では、Snowflake 上でのカスタム DMF の作成・設定方法を中心に解説します。
具体的には以下の内容について理解することができます。
- Snowflake における DMF と DQM
- カスタム DMF の作成・設定方法
- DMF 使用時のコスト確認方法
※データ品質の監視を行う場合、DMF の設定とアラート機能の設定が必要ですが、本記事では DMF の設定に焦点を絞って説明します。
データ品質監視 (DQM) とデータメトリクス関数 (DMF) とは
Snowflake におけるデータ品質監視 (DQM) とは、テーブルやビューに対してデータメトリクス関数 (DMF) をスケジュール設定し、取得値が基準を超えている場合にアラート機能で通知する一連の仕組みを指します。DMF を対象のテーブル・ビューに対してスケジュール設定し、取得された値が閾値を超えていないかアラート機能で監視する形式です。（取得された値は Snowflake の DMFs 専用イベントテーブルに自動保存されます）
DMF には以下のような特徴があります。
- 組み込みのシステム DMF と、ユーザーで定義可能なカスタム DMF が存在
- テーブルまたはビューに設定することで、指定した周期でスケジュール実行可能
- 実行にはサーバーレスコンピューティングリソースを使用
- 実行結果は Snowlflake の DMF 専用イベントテーブルに自動保存
※サーバーレスコンピューティングリソースのコストは、ユーザ管理のウェアハウスのコンピューティングリソースコストとは異なります。そのため、本記事では DMF 実行時のサーバーレスコンピューティングリソースのコスト確認方法もあわせて紹介します。
※機能や料金・コストの詳細については、Snowflake 公式ドキュメントの以下もご参照ください。
データメトリクス関数 (DMF) を使ってみよう！
それでは、実際に DMF の動作の確認をしてみましょう！
今回は、対象テーブルにおいて E-Mail アドレス項目の値が一般的なメールアドレスの形式（xxx@xxx.xxx）に違反しているレコードがある場合にレコード件数を取得するカスタム DMF を作成し、5分おきに実行するよう設定します。
なお、本記事における設定内容は、以下の Snowflake 公式のチュートリアルを参考にしています。
注意
DMF の作成・設定において、以下が必要ですので事前にご確認ください。
- ご利用のアカウントが Enterprise Edition 以上であること
- ご利用のユーザーが、ACCOUNTADMIN ロールを使用可能であること
- （既存テーブルを使用する場合）DMF 設定時のロールに、対象テーブルの所有者権限を付与可能であること
※その他使用時の注意事項は以下を参照してください。
事前準備
使用するロール・DB・スキーマ・ウェアハウスを作成します。
作成したロールは SYSADMIN 配下の階層に設定し、現在のユーザーに割り当てます。
また、以下の権限を付与します。
- CREATE DATA METRIC FUNCTION
⇒ スキーマへの DMF 作成権限 - EXECUTE DATA METRIC FUNCTION
⇒ DMF 実行のためのサーバレスコンピューティングリソースへのアクセス権限 - APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER
⇒ DMF 実行結果ビューへのアクセス権限 - DATABASE ROLE SNOWFLAKE.USAGE_VIEWER
⇒ DMF 実行時のコストが確認できるビューが存在するシステムスキーマ「ACCOUNT_USAGE」へのアクセス権限
補足
今回は不要ですが、システム DMF を使用する場合は追加で以下の権限が必要です。
- DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER
⇒ システム DMF の存在するシステムスキーマ「CORE」と、その配下の全システム DMF へのアクセス権限
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS dq_tutorial_role;
GRANT CREATE DATABASE ON ACCOUNT TO ROLE dq_tutorial_role;
GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE dq_tutorial_role;
GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE dq_tutorial_role;
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE dq_tutorial_role;
CREATE WAREHOUSE IF NOT EXISTS dq_tutorial_wh;
GRANT USAGE ON WAREHOUSE dq_tutorial_wh TO ROLE dq_tutorial_role;
GRANT ROLE dq_tutorial_role TO ROLE SYSADMIN;
SET my_user = CURRENT_USER();
GRANT ROLE dq_tutorial_role TO USER IDENTIFIER($my_user);
USE ROLE dq_tutorial_role;
CREATE DATABASE IF NOT EXISTS dq_tutorial_db;
CREATE SCHEMA IF NOT EXISTS sch;
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON DATABASE dq_tutorial_db TO ROLE dq_tutorial_role;
GRANT CREATE DATA METRIC FUNCTION ON SCHEMA sch TO ROLE dq_tutorial_role;
使用するデータの登録
続いてデータ品質を監視する対象となるテーブルを作成し、データを登録します。
（既存のテーブルを使用する場合は、対象テーブルの所有者権限が必要です）
以下の手順で実行してください。
- テーブルの作成
USE ROLE dq_tutorial_role;
CREATE TABLE customers (
account_number NUMBER(38,0),
first_name VARCHAR(16777216),
last_name VARCHAR(16777216),
email VARCHAR(16777216),
phone VARCHAR(16777216),
created_at TIMESTAMP_NTZ(9),
street VARCHAR(16777216),
city VARCHAR(16777216),
state VARCHAR(16777216),
country VARCHAR(16777216),
zip_code NUMBER(38,0)
);
- データの登録（異常：メールアドレスの形式違反）
USE WAREHOUSE dq_tutorial_wh;
INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
VALUES (1589420, 'san francisco', 'usa', 'john.doe@', 'john', 'doe', 1234567890, null, null, null);
- データの登録（正常）
INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
VALUES
(8028387, 'san francisco', 'usa', 'bart.simpson@example.com', 'bart', 'simpson', 1012023030, null, 'market st', 94102),
(1589420, 'san francisco', 'usa', 'john.doe@example.com', 'john', 'doe', 1234567890, 'ca', 'concar dr', 94402),
(2834123, 'san mateo', 'usa', 'jane.doe@example.com', 'jane', 'doe', 3641252911, 'ca', 'concar dr', 94402),
(4829381, 'san mateo', 'usa', 'jim.doe@example.com', 'jim', 'doe', 3641252912, 'ca', 'concar dr', 94402),
(9821802, 'san francisco', 'usa', 'susan.smith@example.com', 'susan', 'smith', 1234567891, 'ca', 'geary st', 94121),
(8028387, 'san francisco', 'usa', 'bart.simpson@example.com', 'bart', 'simpson', 1012023030, 'ca', 'market st', 94102);
データメトリクス関数 (DMF) の作成・設定
次に、カスタム DMF を作成しテーブルに対してスケジュール実行するよう設定します。
以下の手順で実行してください。
- カスタム DMF 作成（指定した正規表現と一致しないメールアドレスを含むレコードの件数を返却）
CREATE DATA METRIC FUNCTION IF NOT EXISTS
invalid_email_count (ARG_T table(ARG_C1 STRING))
RETURNS NUMBER AS
'SELECT COUNT_IF(FALSE = (
ARG_C1 REGEXP ''^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$''))
FROM ARG_T';
- テーブルに対して、DMF のスケジュール実行周期を設定（5 分間隔）
ALTER TABLE customers SET DATA_METRIC_SCHEDULE = '5 MINUTE';
補足
上記の方法以外にも、Cron 式での指定や、トリガーイベントでの起動も可能です。
詳細は以下をご参照ください。
- テーブルに対して、カスタム DMF を設定
ALTER TABLE customers ADD DATA METRIC FUNCTION
invalid_email_count ON (email);
- カスタム DMF が設定できていることを確認
SELECT * FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
REF_ENTITY_NAME => 'dq_tutorial_db.sch.customers',
REF_ENTITY_DOMAIN => 'TABLE'));
主な出力列の意味は、以下のとおりです。
| 出力列 | 意味 |
|---|---|
| metric_name | DMF 関数名 |
| ref_entity_name | DMF 関数が設定されているテーブルまたはビュー名 |
| ref_entity_domain | DMF 関数が設定されているオブジェクトタイプ（テーブル/ビュー） |
| schedule | DMF 関数が実行されるスケジュール |
| schedule_status | DMF 関数のスケジュール実行予定状況 ・STARTED：実行予定あり ・STARTED_AND_PENDING_SCHEDULE_UPDATE：実行予定が変更され保留中 ・SUSPENDED：実行予定なし |
データメトリクス関数 (DMF) のスケジュール実行結果確認
DMF をスケジュール実行した結果は、DATA_QUALITY_MONITORING_RESULTS ビューで確認可能です。
SELECT scheduled_time, measurement_time, table_name, metric_name, value
FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS
WHERE TRUE
AND METRIC_NAME = 'INVALID_EMAIL_COUNT'
AND METRIC_DATABASE = 'DQ_TUTORIAL_DB'
LIMIT 100;
カスタム DMF が返却した値は、value 列に表示されます。
指定した正規表現と一致しないメールアドレスを含むレコード件数（1件）が表示されています。
補足
今回の記事では扱っていませんが、仮にデータ品質異常を通知させる場合は、この value 値をトリガー条件としてアラートを設定します。
アラートの設定方法は、以下の Snowflake 公式ページをご参照ください。
データメトリクス関数 (DMF) の設定解除
DMF を設定したままだと、指定のスケジュールで実行され続けサーバーレスのクレジットが消費されてしまいます。
スケジュール設定を残す必要がない（または、解除したい）場合は、以下のクエリを実行し、テーブルからカスタム DMF 設定を解除してください。
ALTER TABLE customers DROP DATA METRIC FUNCTION
invalid_email_count ON (email);
カスタム DMF 設定を確認した際のクエリを再実行すると、設定が解除されていることが確認できます。
SELECT * FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
REF_ENTITY_NAME => 'dq_tutorial_db.sch.customers',
REF_ENTITY_DOMAIN => 'TABLE'));
データメトリクス関数 (DMF) のコスト確認
最後に、DMF の実行時にサーバーレスコンピューティングリソースのクレジットがどの程度消費されたかは、Account Usage スキーマの DATA_QUALITY_MONITORING_USAGE_HISTORY ビューで確認可能です。
USE ROLE dq_tutorial_role;
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_QUALITY_MONITORING_USAGE_HISTORY
WHERE TRUE
AND START_TIME >= CURRENT_TIMESTAMP - INTERVAL '3 days'
LIMIT 100;
今回の DMF のスケジュール実行結果のレコードが格納されていることを確認できます。
利用したサーバーレスのクレジットは CREDITS_USED 列に表示されており、今回は0.002130373クレジット消費しました。
※ビューに実行結果が反映されるまで、1～2時間程度かかる場合があります。
まとめ
本記事では、以下について学習しました。
- Snowflake における DMF と DQM
- カスタム DMF の作成・設定方法
- DMF 使用時のコスト確認方法
DMF を設定・監視することで、業務で利用しているデータの品質を高水準に維持することが可能です。
皆さんもぜひ活用してみてください！
Discussion