Alert System

In today's fast-paced business world, data-driven decision-making is crucial. However, accessing and analyzing data can be a tedious and time-consuming task, especially for database administrators (DBAs) and business people who rely heavily on data to drive their decisions. To address this problem, a database alert system has been developed that can automate data monitoring and alert users via email. The database alert system is designed to help users save time and effort by automating data monitoring. Users can store SQL queries that they want to monitor, and the system will run these queries on a selected data and frequency. If the system finds any results for the query, it will alert the user via email. To build a database alert system, you need to follow a few simple steps. First, you need to set up the database and tables to store the queries, users, and other information. Any database management system (DBMS) can be used, such as MySQL, PostgreSQL, or SQL Server. Next, you need to create a user interface that allows users to input the queries and select the data and frequency. This user interface can be created using any programming language, such as Python, PHP, or JavaScript. Once the user inputs the queries and selects the data and frequency, the system executes the queries and checks for any results. If it finds any results, it sends an email alert to the user. To implement the query execution and alert system, Python and SQL can be used. Finally, you need to test the system to ensure that it is working correctly. You can test the system by inputting some test queries and selecting a short frequency to check if the system alerts you via email. The database alert system has many benefits for DBAs and business people. For DBAs, the system can help them monitor their application metrics and track their business processes without having to manually check multiple dashboards and run SQL queries. For business people, the system can help them keep track of their business operations and make data-driven decisions. In conclusion, the database alert system is a simple yet effective solution for automating data monitoring and alerting users via email. It can help DBAs and business people save time and effort by automating their data monitoring and making it easier for them to access the data they need. The system can be built using any DBMS and programming language, and it has many benefits for businesses looking to streamline their data monitoring processes.

Table structure

create or replace TABLE USFD.ETL.ALERTSYSTEM ( 
  ROWNUMBER NUMBER(38,0), 
  SQLQUERY VARCHAR(1000), 
  FREQUENCY VARCHAR(50), 
  EMAILTO VARCHAR(500), 
  SUBJECT VARCHAR(500), 
  BODY VARCHAR(1000), 
  ISACTIVE BOOLEAN, 
  LASTRUNDATETIME TIMESTAMP_NTZ(9), 
  LASTRUNSTATUS BOOLEAN, 
  LASTRUNERROR VARCHAR(1000) 
);

Notification integration object definition

create or replace notification integration my_email 
type=email 
enabled=true 
allowed_recipients=('support@pbsinfosystems.com', 'admin@pbsinfosystems.com');

System Send email definition

call system$send_email( 
  'my_email', 
  'support@pbsinfosystems.com,admin@pbsinfosystems.com', 
  'Test', 
  'This is the test email from Snowflake' 
);

Procedure to pull records that are scheduled at the time of running the proc

CREATE OR REPLACE PROCEDURE USFD.ETL.DAILYALERT() 
RETURNS TABLE ("ROWNUMBER" NUMBER(38,0), "SQLQUERY" VARCHAR(2000), "EMAILTO" VARCHAR(75), "SUBJECT" VARCHAR(250), "BODY" VARCHAR(5000), "FREQUENCY" VARCHAR(250)) 
LANGUAGE SQL 
EXECUTE AS OWNER 
AS ' 
Declare DayNumber varchar(25); 
res Resultset; 
Begin 
DayNumber := DATE_PART(dw, CURRENT_TIMESTAMP()::DATE); 
res := (SELECT ROWNUMBER,SQLQUERY,EMAILTO,SUBJECT,BODY,FREQUENCY 
FROM usfd.etl.AlertSystem 
WHERE ( FREQUENCY like ''Weekly%'' AND ( LASTRUNDATETIME IS NULL OR TO_DATE(LASTRUNDATETIME) <> CURRENT_DATE()) 
And SUBSTRING(FREQUENCY,8,1) = TO_CHAR( DATE_PART(dw, CURRENT_TIMESTAMP()::DATE)) 
And SUBSTRING(FREQUENCY,10) <= TO_CHAR(CURRENT_TIMESTAMP(), ''HH24:MI'')) 
Or (( FREQUENCY like ''Daily%'' ) AND ( LASTRUNDATETIME IS NULL OR TO_DATE(LASTRUNDATETIME) <> CURRENT_DATE()) 
And REPLACE(FREQUENCY,''Daily_'','''') <= TO_CHAR(CURRENT_TIMESTAMP(), ''HH24:MI'')) 
Or (FREQUENCY LIKE ''Every_%'' AND ( LASTRUNDATETIME IS NULL 
OR REPLACE(FREQUENCY,''Every_'','''') <= TIMEDIFF(Hour, LASTRUNDATETIME, CURRENT_TIMESTAMP()))) 
); 
return table(res); 
end ;'

Task scheduled every 60 minutes

CREATE OR REPLACE TASK DailyAlertTask 
WAREHOUSE = COMPUTE_WH 
SCHEDULE = '60 minute' 
AS call CallAlert123() 

show tasks; 
alter task dailyalerttask resume;