Alerts

Pancake users can leverage Snowflake's alert features to create notifications when a schema change or data shape change is detected during a scheduled scan. Alerts are available to all Pancake users for free, but the level of detail in the alerts will depend on the data source product tier.

Data sources on the Schema Summary tier will only notify users that that a schema/data shape change has occurred on that data source. To receive attribute level information about schema/data shape changes detected during a scan, you will need to upgrade the data source to the Schema Analysis or Extract, Relate, and Flatten product tier.

Schema Summary Setup Script

/* ********** SCRIPT #5 - Schema Summary (Free Tier) - Schema Update Alert Set Up (Data Source Names Only)

Create Schema/Data Shape Update Alert Script


Script Steps
#1 - Grant the EXECUTE ALERT privilege to the app that installed Pancake.
#2 - Create the NOTIFICATION INTEGRATION
#3 - Create the Alert.
#4 - Commented steps for manually executing the alert and suspending or resuming the alert to run on a specified time interval

Script Placeholders:

(For convenience, you can use the built in find/replace feature in Snowflake for the following placeholders)
 
<database_name_where_the_alert_will_located> (x4)
<schema_name_where_the_alert_will_located> (x4)
<interval_quantity> (x3)
<interval_type> (x3) (MINUTE, HOUR, DAY)
<application_install_role> (x2)
<email_adddress_of_a_user_that_is_registered_and_confirmed_with_snowflake> (X1)
<warehouse_name> (X1)


**After the placeholder values have been updated you can run the entire script at once. After the alert has been created you can either execute the command to resume to alert so that is runs on a scheduled interval or you can execute the alert manually after a scan has been completed.

*/

USE ROLE ACCOUNTADMIN;

GRANT EXECUTE ALERT ON ACCOUNT TO ROLE <application_install_role>;

USE ROLE <application_install_role>;

CREATE OR REPLACE NOTIFICATION INTEGRATION PANCAKE_EMAIL_INTEGRATION
  TYPE=EMAIL
  ENABLED=TRUE;

--Schema Summary (Free Tier) - Create Alert
CREATE OR REPLACE ALERT <database_name_where_the_alert_will_located>.<schema_name_where_the_alert_will_located>.pancake_datasource_schema_update_alert
  WAREHOUSE = <warehouse_name>
  SCHEDULE = '<interval_quantity> <interval_type>'
  IF( EXISTS(
    SELECT datasource_id FROM pancake.core.vw_datasources WHERE last_scan_polymorphic_count > 0 and  last_scan >= Dateadd(<interval_type>,-<interval_quantity>, CURRENT_TIMESTAMP()) ))
  THEN
    begin 
        let modified_datasources varchar := (select listagg(datasource_name,'\n')  WITHIN GROUP (ORDER BY datasource_name) FROM pancake.core.vw_datasources WHERE last_scan_polymorphic_count > 0 and last_scan >= Dateadd(<interval_type>,-<interval_quantity>, CURRENT_TIMESTAMP()));
        CALL SYSTEM$SEND_EMAIL(
        'PANCAKE_EMAIL_INTEGRATION',
        '<email_adddress_of_a_user_that_is_registered_and_confirmed_with_snowflake>',
        'Pancake Alert: Data Source Schema/Polymorphic State Update - Summary',
        concat('The schema, polymorphic state, or Snowflake inference of the following data source(s) has been modified:\n\n',:modified_datasources)
        );
    end;

--Manually Execute the Alert
--execute alert <database_name_where_the_alert_will_located>.<schema_name_where_the_alert_will_located>.pancake_datasource_schema_update_alert;

--Suspend and Resume Alert
--ALTER ALERT <database_name_where_the_alert_will_located>.<schema_name_where_the_alert_will_located>.pancake_datasource_schema_update_alert SUSPEND;
--ALTER ALERT <database_name_where_the_alert_will_located>.<schema_name_where_the_alert_will_located>.pancake_datasource_schema_update_alert RESUME;

--View Alert History
/*
SELECT *
FROM
    TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(
    SCHEDULED_TIME_RANGE_START
    =>dateadd('hour',-1,current_timestamp())))
ORDER BY SCHEDULED_TIME DESC;
*/

Schema Analysis Setup Script

/* ********** SCRIPT #6 - Schema Analysis (Paid Tier) - Schema Update Alert Set Up (Data Source Names with version number and attribute information)

Create Schema/Data Shape Update Alert Script


Script Steps
#1 - Grant the EXECUTE ALERT privilege to the app that installed Pancake.
#2 - Create the NOTIFICATION INTEGRATION
#3 - Create the Alert.
#4 - Commented steps for manually executing the alert and suspending or resuming the alert to run on a specified time interval

Script Placeholders:

(For convenience, you can use the built in find/replace feature in Snowflake for the following placeholders)
 
<database_name_where_the_alert_will_located> (x4)
<schema_name_where_the_alert_will_located> (x4)
<interval_quantity> (x4)
<interval_type> (x4) (MINUTE, HOUR, DAY) 
<application_install_role> (x1)
<email_adddress_of_a_user_that_is_registered_and_confirmed_with_snowflake> (X1)
<warehouse_name> (X1)

**After the placeholder values have been updated you can run the entire script at once. After the alert has been created you can either execute the command to resume to alert so that is runs on a scheduled interval or you can execute the alert manually after a scan has been completed.

*/

USE ROLE ACCOUNTADMIN;

GRANT EXECUTE ALERT ON ACCOUNT TO ROLE <application_install_role>;

USE ROLE <application_install_role>;

CREATE or REPLACE NOTIFICATION INTEGRATION PANCAKE_EMAIL_INTEGRATION
  TYPE=EMAIL
  ENABLED=TRUE;

CREATE OR REPLACE ALERT <database_name_where_the_alert_will_located>.<schema_name_where_the_alert_will_located>.pancake_datasource_schema_update_with_attributes_alert
  WAREHOUSE = <warehouse_name>
  SCHEDULE = '<interval_quantity> <interval_type>'
  IF( EXISTS(
    SELECT datasource_id FROM pancake.core.vw_datasources WHERE last_scan_polymorphic_count > 0 and  last_scan >= Dateadd(<interval_type>,-<interval_quantity>, CURRENT_TIMESTAMP()) ))
  THEN
    begin 
        let modified_datasources varchar := ( select listagg(concat('data source: ', datasource_name,' - version #: ', version_number, ' - path: ', attribute_path,' type: ', source_data_type),'\n')  WITHIN GROUP (ORDER BY datasource_name, version_number, attribute_path) from pancake.core.vw_datasource_version_all_attributes dva inner join
    (
    SELECT datasource_id FROM pancake.core.vw_datasources WHERE last_scan_polymorphic_count > 0 and  last_scan >= Dateadd(<interval_type>,-<interval_quantity>, CURRENT_TIMESTAMP())
    ) as updated 
    on dva.datasource_id = updated.datasource_id 
    where dva.version_status = 'active' and dva.version_status_date >= Dateadd(<interval_type>,-<interval_quantity>, CURRENT_TIMESTAMP())) ;
        CALL SYSTEM$SEND_EMAIL(
        'PANCAKE_EMAIL_INTEGRATION',
        '<email_adddress_of_a_user_that_is_registered_and_confirmed_with_snowflake>',
        'Pancake Alert: Data Source Schema/Polymorphic State Update - Analysis',
        concat('The schema, polymorphic state, or Snowflake inference of the following data source(s) has been modified. The modified attributes include:\n\n',:modified_datasources)
        );
    end;


--Manually Execute the Alert
--execute alert <database_name_where_the_alert_will_located>.<schema_name_where_the_alert_will_located>.pancake_datasource_schema_update_with_attributes_alert;

--Suspend and Resume Alert
--ALTER ALERT <database_name_where_the_alert_will_located>.<schema_name_where_the_alert_will_located>.pancake_datasource_schema_update_with_attributes_alert SUSPEND;
--ALTER ALERT <database_name_where_the_alert_will_located>.<schema_name_where_the_alert_will_located>.pancake_datasource_schema_update_with_attributes_alert RESUME;

--View Alert History
/*
SELECT *
FROM
    TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(
    SCHEDULED_TIME_RANGE_START
    =>dateadd('hour',-1,current_timestamp())))
ORDER BY SCHEDULED_TIME DESC;
*/