はじめに

本記事では、2024年3月29日のリリースで発表された以下の機能と関数について、実際の使い方や権限付与を含めて紹介します。

• Data Quality Monitoring：データ品質モニタリング

• Data Metric Functions：データメトリック関数

https://docs.snowflake.com/en/release-notes/2024/other/2024-03-29-dmf

DQMとDMFでできること

• 通常テーブルに対しカラムレベルでデータの状態や整合性を確認することが可能

• あらかじめ用意されたシステム関数かユーザが独自に作成したカスタム関数をクエリで使用

• CRON・TIMING・DML操作をトリガーとして状態を記録し継続的な監視を実現

環境条件

データ品質機能を利用できるのは、以下の条件を満たすSnowflakeアカウントです。

• Snowflake Enterprise Edition以上
• ACCOUNTADMINが利用できる

Data Quality Monitoring（DQM）とは

データ品質は、新に追加されたSnowflakeの機能です。データ測定関数（DMF）を用いて、データのカラム値の状態や整合性を継続的に監視します。

※sample_mart_tableのshop_nameカラムでの重複を5分ごとに監視している様子

一般的なメトリクス（行数・重複・鮮度など）は、Snowflakeが提供するシステムDMFを使用することができ、独自のDMFを作成してビジネス固有のメトリクスを監視することもできます。

また、DMFは通常の関数のようにクエリ内でも使用することができ、テーブルに関連付けることで、CRON・TIMING・DML操作をトリガーとして継続的にモニタリングすることもできます。

料金や制限事項に関する情報はこちら↓
https://docs.snowflake.com/en/user-guide/data-quality-intro#billing-and-pricing

Data Metric Functions（DMF）とは

DMFは、テーブル内のデータに対して、行数・重複・鮮度などのデータの状態や整合性を確認できる新たな関数です。DMFにはあらかじめ用意されたシステムDMFと、ユーザが作成するカスタムDMFの2種類あります。

現時点で存在するシステムUDFは以下の6種類です。

https://docs.snowflake.com/en/user-guide/data-quality-system-dmfs#system-dmfs

システムUDFはSNOWFLAKEデータベースに配置され、デフォルトで使用できるのはACCOUNTADMINロールのみです。他のロールで使用したい場合は、以下のコマンドで権限を付与します。

1億レコード越えのテーブルの1カラムに対して重複カウントをすると、約10秒ほどで結果が返ってきました。（リザルトキャッシュ有だと約340ms）

※SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERSのO_ORDERKEYを使用

利用例

0. 事前準備

 

1. システムDMFの実行

まず、ACCOUNTADMIN以外の任意のロールでDMFを使用するため権限を付与します。

USE ROLE ACCOUNTADMIN;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE {任意のロール};

null_count関数を使用し、集計テーブル内のSHOP_NAMEでNULLとなっているレコードが何件あるか確認します。

select snowflake.core.null_count(
　　select shop_name
　　from sample_mart_table
) as null_count;

結果を確認すると、集計テーブルの中にSHOP_NAMEがNULLとなっているレコードが合計で1件あることが分かります。

2. カスタムDMFの作成と実行

続いて、独自のDMFを作成しSHOP_NAMEに小文字が含まれているデータが何件あるか確認します。

create data metric function lowercase_count(
    arg_t TABLE(arg_c varchar)
)
returns number
comment = '小文字が含まれるカラム値をカウント'
as
$$
    select
        count(*)
    from
        arg_t
    where
        0 < length(regexp_replace(arg_c, '[^a-z]', ''))
$$

実行するとSHOP_NAMEに小文字が含まれているレコードが2件あることが分かります。

select lowercase_count(select shop_name from sample_mart_table) as lower_count

⚠️ 関数の引数とテストするクエリのデータ型やカラム数は一致させてください もし、いくつかのデータ型のカラムに対してカスタムDMFを実行したい場合は、データ型ごとにカスタムDMFを作成しておかなければいけません。 例：システムDMFは各関数がデータ型別に用意されています

3. スケジュール設定

DMFをTABLEに設定するには、下記の権限を使用するロールに付与します。

USE ROLE ACCOUNTADMIN;

-- DMFのスケジュール実行権限を付与
GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE {任意のロール};

-- 監視結果を参照するためデータベースロールを付与
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE {任意のロール};
GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE {任意のロール};
GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE {任意のロール};

スケジュールの設定は以下の順序に従う必要があります。

• テーブルにデータメトリクススケジュールを設定
• テーブルにメトリクス関数を設定

まず、テーブルに対してデータメトリクスケジュールを設定します。

alter table sample_mart_table set
    data_metric_schedule = '5 minute';

スケジュールを設定したテーブルにシステムDMFのduplicate_countを店舗名を対象に設定します。

alter table sample_mart_table drop data metric function
  snowflake.core.duplicate_count
  on (shop_name);

モニタリング状況は、SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTSビューで確認できます。

select
    scheduled_time, table_name, argument_names, metric_name, value
from
    snowflake.local.data_quality_monitoring_results
where
    table_name = 'SAMPLE_MART_TABLE' and metric_name = 'DUPLICATE_COUNT';

ここでshop_nameが重複するレコードを追加します。

insert into sample_mart_table values('2024-04-01', 'SHOP_HOKKAIDO', 5233)

5分後の実行で重複が確認され、監視の結果としてVALUEの値が1となります。これを以上としてアラート設定を行うことで継続的な監視体制を整えることができそうです。

select
    scheduled_time, table_name, metric_name, value
from
    snowflake.local.data_quality_monitoring_results
where
    table_name = 'SAMPLE_MART_TABLE' and metric_name = 'DUPLICATE_COUNT';

4. スケジュール解除