如何防止Snowflake Streams进入过期（Stale）状态？

针对长时间无数据流入导致流过期的问题，以下是几种实用解决方案：

1. 定期插入"心跳"记录 #

给长期无数据的目标表定期插入并立即删除一条测试记录，触发流捕获变更，从而重置流的过期时间。这种方法不会影响业务数据，只需在下游处理逻辑中过滤心跳记录即可。

示例SQL（通过Snowflake Task调度，每月执行一次）：

-- 插入心跳记录
INSERT INTO target_table (col1, col2) VALUES ('heartbeat', 'dummy');
-- 立即删除心跳记录
DELETE FROM target_table WHERE col1 = 'heartbeat';

2. 按需或定期重建流 #

如果可以接受短暂的流中断，定期检查流的状态，若已过期则删除旧流并重建同名流。注意：重建前需确保流中未消费的所有数据已处理完毕，避免数据丢失。

示例SQL：

-- 检查流是否过期
SELECT STALE FROM INFORMATION_SCHEMA.STREAMS 
WHERE TABLE_NAME = 'TARGET_TABLE' AND STREAM_NAME = 'TARGET_STREAM';

-- 若STALE为TRUE，删除并重建流
DROP STREAM IF EXISTS target_stream;
CREATE STREAM target_stream ON TABLE target_table;

3. 调整表的数据保留参数（受版本限制） #

如果当前目标表的DATA_RETENTION_TIME_IN_DAYS和MAX_DATA_EXTENSION_TIME_IN_DAYS未设为平台最大值，可先调至对应版本的上限，延长流的有效时长。但此方法无法突破Snowflake本身的参数限制（比如企业版DATA_RETENTION最多90天，MAX_DATA_EXTENSION最多1000天）。

示例SQL：

-- 调整数据保留天数至版本最大值
ALTER TABLE target_table SET DATA_RETENTION_TIME_IN_DAYS = 90;
-- 调整最大数据扩展天数至上限
ALTER TABLE target_table SET MAX_DATA_EXTENSION_TIME_IN_DAYS = 1000;

4. 用任务自动监控并维护流 #

创建Snowflake Task，定期查询流的状态，当检测到流即将过期时，自动执行心跳操作或重建流，实现自动化维护。

示例SQL（每月1号执行一次检查）：

CREATE OR REPLACE TASK maintain_stream_task
WAREHOUSE = your_warehouse
SCHEDULE = 'USING CRON 0 0 1 * * UTC'
AS
BEGIN
  WITH stream_status AS (
    SELECT 
      STREAM_NAME,
      TABLE_NAME,
      CURRENT_TIMESTAMP() - LAST_ALTERED AS time_since_last_change,
      DATA_RETENTION_TIME_IN_DAYS
    FROM INFORMATION_SCHEMA.STREAMS
    JOIN INFORMATION_SCHEMA.TABLES 
      ON STREAMS.TABLE_NAME = TABLES.TABLE_NAME
    WHERE STREAM_NAME = 'TARGET_STREAM'
  )
  -- 当距离上次变更的时间接近数据保留期时，插入心跳记录
  INSERT INTO target_table (col1, col2) VALUES ('heartbeat', 'dummy')
  WHERE time_since_last_change > (DATA_RETENTION_TIME_IN_DAYS - 7) * INTERVAL '1 day';
  
  DELETE FROM target_table WHERE col1 = 'heartbeat';
END;

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