如何在Snowflake中创建U.S holiday Calendar供Date Dimension使用？

具体实现步骤 #

• 先对齐业务侧的节假日规则：先区分联邦法定固定假日、浮动假日、行业常用非法定假日三类，确认是否需要包含周末补休规则、各州专属假日，避免后续反复改逻辑。
• 在公共维度Schema下创建独立日历表：不要把日历逻辑写在各个业务表的视图里，统一放在公共层，给所有需要使用的计算角色分配只读权限，避免重复建设。
• 生成连续日期序列作为底表：时间范围建议覆盖业务最早发生日期往前推3年，到未来10年，满足历史回溯、未来预算/预测类场景的需求。
• 写入假日判断逻辑：先写每年固定日期的假日规则，再写浮动假日的日期计算逻辑，最后给每个日期打上是否法定假日、假日名称、是否工作日的标签。
• 抽样校验准确性：重点核对3-5个完整年度的浮动假日、跨周末补休的假日日期，确认和官方发布的节假日安排一致后再关联到日期维度表。

可直接复用的代码示例 #

以下代码完全基于Snowflake原生SQL编写，不需要依赖外部共享表或第三方数据，可直接运行。

-- 1. 创建美国节假日日历表
CREATE OR REPLACE TABLE COMMON_DIM.US_HOLIDAY_CALENDAR (
    CALENDAR_DATE DATE PRIMARY KEY,
    IS_FEDERAL_HOLIDAY BOOLEAN COMMENT '是否联邦法定假日',
    HOLIDAY_NAME VARCHAR(100) COMMENT '节假日名称',
    IS_BUSINESS_DAY BOOLEAN COMMENT '是否工作日（排除周末+联邦法定假日）',
    HOLIDAY_TYPE VARCHAR(50) COMMENT '假日类型：固定假日/浮动假日/行业常用假日'
)
COMMENT = '美国节假日日历，用于支撑日期维度构建';

-- 2. 写入日历数据，默认覆盖2015-01-01至2035-12-31，可按需调整日期范围
INSERT OVERWRITE INTO COMMON_DIM.US_HOLIDAY_CALENDAR
WITH date_spine AS (
    -- 生成连续日期序列
    SELECT DATEADD(DAY, seq4(), '2015-01-01'::DATE) AS CALENDAR_DATE
    FROM TABLE(GENERATE_SERIES(0, DATEDIFF(DAY, '2015-01-01'::DATE, '2035-12-31'::DATE)))
),
floating_holidays AS (
    -- 按年份计算所有非固定日期的假日
    SELECT
        YEAR(CALENDAR_DATE) AS cal_year,
        -- 马丁路德金日：1月第三个周一
        NEXT_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),1,1), 'Monday') + INTERVAL '2 WEEKS' AS mlk_day,
        -- 总统日：2月第三个周一
        NEXT_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),2,1), 'Monday') + INTERVAL '2 WEEKS' AS presidents_day,
        -- 阵亡将士纪念日：5月最后一个周一
        DATEADD(DAY, -1 * CASE WHEN DAYOFWEEK(LAST_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),5,1)), 'sunday')=1 
            THEN 0 ELSE DAYOFWEEK(LAST_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),5,1)), 'sunday')-1 END, 
            LAST_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),5,1))) AS memorial_day,
        -- 劳动节：9月第一个周一
        NEXT_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),9,1), 'Monday') AS labor_day,
        -- 哥伦布日：10月第二个周一
        NEXT_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),10,1), 'Monday') + INTERVAL '1 WEEK' AS columbus_day,
        -- 感恩节：11月第四个周四
        NEXT_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),11,1), 'Thursday') + INTERVAL '3 WEEKS' AS thanksgiving,
        -- 黑色星期五：感恩节后第一天
        (NEXT_DAY(DATE_FROM_PARTS(YEAR(CALENDAR_DATE),11,1), 'Thursday') + INTERVAL '3 WEEKS') + INTERVAL '1 DAY' AS black_friday
    FROM date_spine
    GROUP BY 1
),
holiday_calc AS (
    SELECT
        d.CALENDAR_DATE,
        CASE
            -- 固定联邦假日，六月节2021年才成为法定假日，加年份判断避免误标
            WHEN MONTH(d.CALENDAR_DATE)=1 AND DAYOFMONTH(d.CALENDAR_DATE)=1 THEN (TRUE, '元旦', '固定假日')
            WHEN MONTH(d.CALENDAR_DATE)=6 AND DAYOFMONTH(d.CALENDAR_DATE)=19 AND YEAR(d.CALENDAR_DATE)>=2021 THEN (TRUE, '六月节', '固定假日')
            WHEN MONTH(d.CALENDAR_DATE)=7 AND DAYOFMONTH(d.CALENDAR_DATE)=4 THEN (TRUE, '独立日', '固定假日')
            WHEN MONTH(d.CALENDAR_DATE)=11 AND DAYOFMONTH(d.CALENDAR_DATE)=11 THEN (TRUE, '退伍军人节', '固定假日')
            WHEN MONTH(d.CALENDAR_DATE)=12 AND DAYOFMONTH(d.CALENDAR_DATE)=25 THEN (TRUE, '圣诞节', '固定假日')
            -- 浮动联邦假日
            WHEN d.CALENDAR_DATE = fh.mlk_day THEN (TRUE, '马丁路德金日', '浮动假日')
            WHEN d.CALENDAR_DATE = fh.presidents_day THEN (TRUE, '总统日', '浮动假日')
            WHEN d.CALENDAR_DATE = fh.memorial_day THEN (TRUE, '阵亡将士纪念日', '浮动假日')
            WHEN d.CALENDAR_DATE = fh.labor_day THEN (TRUE, '劳动节', '浮动假日')
            WHEN d.CALENDAR_DATE = fh.columbus_day THEN (TRUE, '哥伦布日', '浮动假日')
            WHEN d.CALENDAR_DATE = fh.thanksgiving THEN (TRUE, '感恩节', '浮动假日')
            -- 行业常用非法定假日
            WHEN d.CALENDAR_DATE = fh.black_friday THEN (FALSE, '黑色星期五', '行业常用假日')
            WHEN MONTH(d.CALENDAR_DATE)=12 AND DAYOFMONTH(d.CALENDAR_DATE)=24 THEN (FALSE, '平安夜', '行业常用假日')
            WHEN MONTH(d.CALENDAR_DATE)=12 AND DAYOFMONTH(d.CALENDAR_DATE)=31 THEN (FALSE, '新年前夜', '行业常用假日')
            ELSE (FALSE, NULL, NULL)
        END AS holiday_detail
    FROM date_spine d
    LEFT JOIN floating_holidays fh ON YEAR(d.CALENDAR_DATE) = fh.cal_year
)
SELECT
    CALENDAR_DATE,
    holiday_detail[0]::BOOLEAN AS IS_FEDERAL_HOLIDAY,
    holiday_detail[1]::VARCHAR AS HOLIDAY_NAME,
    -- 周日=0、周六=6为周末，排除周末+联邦假日即为工作日
    CASE WHEN DAYOFWEEK(CALENDAR_DATE, 'sunday') IN (0,6) OR holiday_detail[0]::BOOLEAN = TRUE THEN FALSE ELSE TRUE END AS IS_BUSINESS_DAY,
    holiday_detail[2]::VARCHAR AS HOLIDAY_TYPE
FROM holiday_calc;

后续关联日期维度表时，直接用CALENDAR_DATE字段做关联键，把需要的节假日字段写入日期维度表即可。

相关注意事项 #

• 补休规则按需添加：如果法定假日刚好赶在周六/周日，联邦政府一般会在周五/周一补休，上述基础代码没有包含补休逻辑，如果业务需要统计补休假日，要单独加日期偏移判断，不要直接用固定日期。
• 不要依赖第三方共享表：第三方开放的节假日共享表可能出现权限回收、数据更新不及时、规则不符合业务需求的问题，用原生SQL生成的表完全自主可控，每年不需要手动维护数据。
• 重点校验容易算错的假日：阵亡将士纪念日是5月最后一个周一，不是第四个周一（部分年份5月有5个周一），感恩节是11月第四个周四，不要算成第三个，这两个是出错率最高的浮动假日。
• 州级假日单独加字段：如果业务需要统计美国各州专属假日（比如德州独立日、加州凯撒查韦斯日），不要混在联邦假日字段里，单独加州编码、州级假日标识字段做区分。
• 权限配置要到位：公共日历表不要给普通用户开写权限，只给ETL运维账号写权限，避免误操作改乱日历数据影响所有下游报表。

内容的提问来源于stack exchange，提问作者Guru