How to handle holidays that don’t have a set date but rather are a certain day of a month

Question

How to handle holidays that don’t have a set date but rather are a certain day of a month?. For example, Thanksgiving is every 4th Thursday of November

Is there a way in snowflake to filter data without holidays and weekends?

Answer 1

Since all floating holidays have their own rules, it's easier to build a lookup table for next N years than trying to build a single query that handles all such holidays. I'll answer the specific use case you brought up for Thanksgiving. You can modify it to build your lookup table that handles other floating holidays.

create or replace temporary table t as

select '2020-01-01'::date + row_number() over (order by 1) as dt
from table(generator(rowcount => 5000));

---

select dt as thanksgivings
from t
where monthname(dt) = 'Nov' and dayname(dt) = 'Thu'
qualify row_number() over (partition by year(dt), month(dt) order by dt) = 4 -- 4th week