Automating Business Day-Specific Tasks with Snowflake

Read Time:1 Minute, 51 Second

In today’s dynamic environment, automating business day-specific tasks is crucial for aligning with operational workflows and regulatory requirements. The Snowflake Data Cloud offers powerful tools and capabilities to streamline such processes, ensuring efficiency and accuracy.

Imagine you manage a data pipeline in Snowflake that involves various tasks, including data ingestion, processing, and analysis. These tasks are crucial for your daily operations, but you don’t want them to run on weekends or holidays to avoid unnecessary resource consumption. This use case demonstrates how to automate the execution of a long-running task only on business days using Snowflake’s procedural logic and a business calendar table.

The solution involves creating a calendar table that identifies business days, and a stored procedure that checks the current date against this calendar and conditionally executes a long-running task only if it is a legal day.

Implementation:

1. Create the Business Calendar Table: The business_calendar table holds the calendar dates, a boolean flag indicating if the date is a business day, and an optional holiday name.

2. Create the Stored Procedure: This procedure run_if_business_day() leverages JavaScript to check if the current date falls on a business day based on the business_calendar table.

• It retrieves the current date.
• It constructs a SQL query to fetch the is_business_day value for the current date from the business_calendar table.
• It executes the query and checks the returned value.
• If it’s a business day (TRUE), the procedure calls another procedure named LONG_RUNNER.
• If it’s not a business day (FALSE), the procedure simply logs a message indicating the task is skipped.

Procedure:

Scenario:

Let’s consider a scenario where we have a long-running process executing in our environment. Since today is neither a holiday nor a weekend, we will trigger our run_if_business_day() procedure. This procedure checks if today is a valid day. Given that it is, the process will proceed and call the long-running task, which will then abort the query.

Long Runner:

Long Runner

Call the Procedure:

Proc Output

Verify Long Runner:

Query Cancelled

Note: You can schedule the procedure using TASK which can be run once in a day.

The story is originally published at:

https://cloudyard.in/2024/06/automating-business-day-specific-tasks-with-snowflake/