Snowflake任务需求：保留14个工作日快照，删除更早记录

针对你的需求，原代码按日历日计算保留周期的问题，这里提供两种高效的实现方案，优先推荐第一种（性能更优）：

方案一：利用Snowflake内置工作日函数计算保留阈值 #

直接使用Snowflake的DATEADD('business_day', ...)函数，自动跳过周末（默认周一至周五为工作日），精准计算出需要保留的最早工作日日期，然后删除早于该日期的快照。

修改后的任务代码：

create or replace task purge_task
    warehouse = prod_analytics_wh
    after insert_task
as
delete from target_table
-- 保留最近14个工作日的快照，删除早于第14个工作日之前的记录
where snapshot_date < DATEADD('business_day', -14, CURRENT_DATE())
;

方案优势： #

• 精准匹配需求：自动排除周末，确保保留的是连续14个工作日的快照，而非日历日
• 性能高效：无需扫描全表排序，直接通过日期计算过滤数据，适合大数据量场景
• 逻辑简洁：与你的工作日插入任务逻辑完全匹配，仅在工作日触发清理，基准日期为当前工作日，计算结果准确

如果你的业务定义的工作日不是周一至周五，可以通过设置会话参数调整：

-- 设置周一为一周起始日（默认），指定工作日范围为周一至周五
SET WEEK_START = 1;
SET BUSINESS_DAYS_IN_WEEK = '1,2,3,4,5';

方案二：基于最新快照记录数保留（应对快照缺失场景） #

如果存在个别工作日未生成快照的情况，需要保留最新的14条快照记录（无论间隔多少日历日），可以使用子查询定位第14条最新快照的日期：

create or replace task purge_task
    warehouse = prod_analytics_wh
    after insert_task
as
delete from target_table
where snapshot_date < (
    -- 取第14条最新的快照日期（offset从0开始，所以offset 13对应第14条）
    select snapshot_date
    from target_table
    order by snapshot_date desc
    limit 1 offset 13
)
;

方案说明： #

• 适合存在快照缺失的场景，确保始终保留最新的14条记录
• 注意：数据量极大时，全表排序可能影响性能，需结合索引优化（如给snapshot_date创建索引）

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