SELECT * 
FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_QUALITY_MONITORING_USAGE_HISTORY ;

//ロール作成,ユーザー作成
USE ROLE ACCOUNTADMIN;
CREATE ROLE DMF_TEST;
CREATE USER Phosphophyllite;

//権限付与、データベースロールを付与
GRANT CREATE DATABASE ON ACCOUNT TO ROLE DMF_TEST;
GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE DMF_TEST;
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE DMF_TEST;
GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE DMF_TEST;

//ウェアハウス作成、ロールに対しUSAGE権限を付与
CREATE WAREHOUSE IF NOT EXISTS DMF_TEST_WH;
GRANT USAGE ON WAREHOUSE DMF_TEST_WH TO ROLE DMF_TEST;

//ユーザーにロール付与
GRANT ROLE DMF_TEST TO ROLE SYSADMIN;
GRANT ROLE DMF_TEST TO USER Phosphophyllite;

//テーブル作成
USE ROLE DMF_TEST;
CREATE DATABASE IF NOT EXISTS DMF_TEST_DB;
CREATE SCHEMA IF NOT EXISTS SCH;

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 DMF_TEST_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);

INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
　VALUES
(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の実行スケジュールを5分間に設定します。最後にテーブルに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';

//スケジュールの作成
ALTER TABLE customers SET DATA_METRIC_SCHEDULE = '5 MINUTE';

//テーブルにDMFをセット
ALTER TABLE customers ADD DATA METRIC FUNCTION
invalid_email_count ON (email);

//解除
ALTER TABLE customers DROP DATA METRIC FUNCTION
invalid_email_count ON (email);