テーブルに新しいデータが入った時に品質テストを行い異常があったら通知するアラートを作ってみた

-- メール送信用のNotification Integration作成
use role accountadmin;
create or replace notification integration email_int
  type = email
  enabled = true;
grant usage on integration email_int to role sysadmin;

use role accountadmin;

-- サーバーレスアラート関係
grant execute managed alert on account to role sysadmin;
grant execute alert on account to role sysadmin;

-- ロールの設定
use role sysadmin;

-- データベースとスキーマの作成
create database if not exists dq_tutorial_db;
create schema if not exists dq_tutorial_db.sch;

-- 従業員テーブルの作成
create or replace table dq_tutorial_db.sch.employeestable (
  id number,
  name varchar,
  last_name varchar,
  email varchar,
  zip_code number
);

-- 正常なデータを挿入
insert into dq_tutorial_db.sch.employeestable (id, name, last_name, email, zip_code)
values
  (1, 'john', 'doe', 'johndoe@example.com', 12345),
  (2, 'jane', 'smith', 'janesmith@example.com', 23456),
  (3, 'robert', 'johnson', 'rjohnson@example.com', 34567);

-- データの確認
select * from dq_tutorial_db.sch.employeestable;

-- アラートの作成
use role sysadmin;
create or replace alert dq_tutorial_db.sch.data_quality_alert
  if (exists(
    select 1 
    from dq_tutorial_db.sch.employeestable
    where name = '' or email is null
  ))
  then
    begin
      -- エラー詳細を取得
      let error_details varchar;

      -- 空白値のある行を取得
      select 'blank name values found in the following rows:' || chr(10) || 
             array_to_string(array_agg('id: ' || id || ', last name: ' || last_name), chr(10))
      into :error_details
      from dq_tutorial_db.sch.employeestable
      where name = '';

      -- null値のあるメールアドレスを追加
      let null_email_details varchar;
      select 'null email values found in the following rows:' || chr(10) || 
             array_to_string(array_agg('id: ' || id || ', name: ' || name || ', last name: ' || last_name), chr(10))
      into :null_email_details
      from dq_tutorial_db.sch.employeestable
      where email is null;

      -- エラー情報を結合
      let full_error_details varchar := :error_details || chr(10) || chr(10) || :null_email_details;

      -- メール送信
      call system$send_email(
        'email_int',
        'sagara.satoshi@classmethod.jp',
        'data quality alert: issues detected in employee table',
        'the following data quality issues were detected:' || chr(10) || chr(10) || :full_error_details || 
        chr(10) || chr(10) || 'please review and fix these issues as soon as possible.'
      );
    end;

-- アラートを有効化
use role sysadmin;
alter alert dq_tutorial_db.sch.data_quality_alert resume;

-- 問題のあるデータを挿入
use role sysadmin;
insert into dq_tutorial_db.sch.employeestable (id, name, last_name, email, zip_code)
values
  (4, '', 'williams', 'williams@example.com', 45678),   -- 空の名前
  (5, 'michael', 'brown', null, 56789);                -- nullのメール

-- データの確認
select * from dq_tutorial_db.sch.employeestable;