Write a query in snowflake

Hello,

I have a database in snowflake. The format of the database is as follows:

Table 1:

Engagement name
Engagement code
Engagement manager
Engagement start date
Engagement end date
Engagement update date
Engagement file size

Table 2:

Engagement name
Engagement code
Engagement manager
Last Update timestamp
Engagement file size

The query needs to run every 3 hours to do the following:

For every day, run the query every 3 hours
If engagement end date has elapsed

then skip

Else if engagement end date is less than current date

then check if engagement code in table 1 exists in table 2

if true then

if Engagement update date from table 1 is not equal to Last Update timestamp in table 2

update Engagement update date from table 1 to Last Update timestamp in table 2
update Engagement file size from table 1 to Engagement file size in table 2

else I if Engagement update date from table 1 = Last Update timestamp in table 2
if same

then skip

else if engagement code in table 1 does not exists in table 2

add a new row for that engagement code in table 2
copy Engagement name from table 1 to table 2
copy Engagement code from table 1 to table 2
copy Engagement manager from table 1 to table 2
copy Engagement update date from table 1 to Last Update timestamp table 2
copy Engagement file size from table 1 to table 2