0
0
Business-Day Validation with Snowflake External Functions: Stop Weekend/Holiday SLA Misses .When organizations calculate due dates for claims processing, vendor payments, reimbursements, or customer SLAs, the typical approach is simple arithmetic: “claim_date + 3 days.” But this naive calculation creates real operational problems. Teams across finance, operations, and customer service commit to due dates that are auto-calculated without considering: Weekends,National holidays that vary by country etc. As a result,Operations teams manually correcting dates in tickets or each team maintaining their own version of “business day” calculations.
For a global organization operating across India, US, UK, and Singapore, this inconsistency becomes expensive. What’s a business day in Mumbai (Republic Day on Jan 26) isn’t the same in New York. When your claims processing SLA promises “review within 3 business days,” you need precision—not approximations.
What We’re Solving
Build a single source of truth within Snowflake that can:
- Accepts a date and country code from Snowflake
- Fetches the relevant holiday calendar from a public API (Nager.Date)
- Determines if the date is a business day (not weekend, not holiday)
- Returns the result back to Snowflake
In this blog, we’ll explore a real-world scenario where Snowflake External Functions integrate with AWS Lambda to calculate business days across multiple countries. Using External Functions, we can securely connect Snowflake to AWS Lambda, which fetches real-time holiday data from public APIs and determines whether a date is a valid business day—all directly from Snowflake SQL queries.
Technical Implementation:
Technical Implementation:
Step 1: Create the Remote Service (AWS Lambda Function).
Step 2: Configure the Proxy Service i.e. Amazon API Gateway.
- Create an API Gateway linked to your Lambda function.
- Configure the API Gateway to accept requests and invoke the Lambda function.
- Deploy the API Gateway and note the endpoint URL (e.g., https://<your-api-endpoint>).
To configure the API please refer the below blogs:
https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-ui-proxy-service;
https://cloudyard.in/2022/04/external-function-in-snowflake/;
Step 3: Create the API Integration.
CREATE OR REPLACE API INTEGRATION SLA_API_INT
API_PROVIDER = aws_api_gateway
API_AWS_ROLE_ARN = 'arn:aws:iam::913267004595:role/NewhttpExternal'
API_ALLOWED_PREFIXES = ('https://sbx3aexnca.execute-api.us-east-1.amazonaws.com/Business_day_stage/Business_day_resource')
ENABLED = TRUE;
Setting up Trust Relationship:
Step 4: Create the External Function.
create or replace external function BUSINESS_DAY_SVC(
d DATE, country STRING, base_tpl STRING, op STRING
)
returns variant
api_integration = SLA_API_INT
as 'https://sbx3aexnca.execute-api.us-east-1.amazonaws.com/Business_day_stage/Business_day_resource';
Replace the < api_integration_name > with the name of your API integration
Replace the < invocation_url > value with your resource invocation URL.
CREATE OR REPLACE TABLE GOV_CONFIG ( country STRING, -- e.g., 'IN', 'US' base_tpl STRING -- e.g., 'https://date.nager.at/api/v3/PublicHolidays/{year}/{country}’);INSERT INTO GOV_CONFIG (country, base_tpl) VALUES ('IN', 'https://date.nager.at/api/v3/PublicHolidays/{year}/{country}'),('US', 'https://date.nager.at/api/v3/PublicHolidays/{year}/{country}');CREATE OR REPLACE TABLE TICKETS ( ticket_id STRING, intake_date DATE,sla_days INTEGER,country STRING);INSERT INTO TICKETS VALUES ('T-1001','2025-01-23', 3,'IN'), -- raw_target = 2025-01-26 (Sun) ('T-1002','2025-01-24', 1,'IN'), -- raw_target = 2025-01-25 (Sat) ('T-1003','2025-01-27', 0,'IN'), -- raw_target = 2025-01-27 (Mon)WITH cfg AS (
SELECT base_tpl
FROM GOV_CONFIG
WHERE country = 'IN'
)
SELECT
'2025-01-26'::date AS input_date,
'IN' AS country,
(BUSINESS_DAY_SVC(
'2025-01-26'::date, -- d
'IN', -- country
cfg.base_tpl, -- base_tpl
'NEXT_BUSINESS_DAY' -- op
):next_business_day)::date AS next_business_day
FROM cfg;
WITH cfg AS (
SELECT country, base_tpl FROM GOV_CONFIG
),
calc AS (
SELECT
t.ticket_id,
t.intake_date,
t.sla_days,
t.country,
DATEADD(day, t.sla_days, t.intake_date) AS raw_target,
-- Call the external function once per row
(BUSINESS_DAY_SVC(
DATEADD(day, t.sla_days, t.intake_date), -- d
t.country, -- country
cfg.base_tpl, -- base_tpl
'NEXT_BUSINESS_DAY' -- op
):next_business_day)::date AS sla_aligned_date
FROM TICKETS t
JOIN cfg USING (country)
)
SELECT
ticket_id,
intake_date,
sla_days,
country,
raw_target,
sla_aligned_date,
(sla_aligned_date = raw_target) AS is_business_day,
IFF(sla_aligned_date = raw_target, 'OK', 'ADJUSTED') AS status
FROM calc
ORDER BY ticket_id;
External Func Output
What each field means
- intake_date: when the ticket/request arrived.
- sla_days: how many calendar days you promise (e.g., 1, 3, 7).
- raw_target: intake_date + sla_days (calendar math only; can land on weekend/holiday).
- sla_aligned_date: the business-day–safe date you actually commit to (roll forward if needed).
- is_business_day: TRUE if raw_target is already a valid business day; otherwise FALSE.
What you’ll see (row by row)
- T-1001 (IN): raw_target 2025-01-26 (Sun) → aligned to 2025-01-27, is_business_day = FALSE, status = ADJUSTED.
- T-1002 (IN): raw_target 2025-01-25 (Sat) → aligned to 2025-01-27, ADJUSTED.
- T-1003 (IN): raw_target 2025-01-27 (Mon) → stays 2025-01-27, OK.
By exposing business-day logic through a Snowflake External Function, we avoid inconsistent date handling across teams and pipelines.With one external function call, every team now calculates SLA-safe dates consistently and accurately, reducing manual corrections.
