Snowflake中如何获取表或视图单行的最后更新时间？

Snowflake本身没有自带的单行级更新时间字段，需要通过以下两种方案实现，可根据场景选择：

方案一：直接在表中维护更新时间字段（推荐） #

这种方式简单高效、实时性强，适合允许修改原表结构的场景：

1. 给原表添加更新时间字段

ALTER TABLE user ADD COLUMN LAST_UPDATED_TIMESTAMP TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP();

该字段会在插入新数据时自动填充当前时间。

2. 创建行级触发器，更新时自动刷新时间
   创建触发器确保每次更新该行数据时，自动将LAST_UPDATED_TIMESTAMP同步为当前时间：

CREATE OR REPLACE TRIGGER user_update_trigger
AFTER UPDATE ON user
FOR EACH ROW
BEGIN
  UPDATE user
  SET LAST_UPDATED_TIMESTAMP = CURRENT_TIMESTAMP()
  WHERE ID = :OLD.ID;
END;

3. 创建包含更新时间的视图
   视图直接引用新增字段即可：

CREATE OR REPLACE VIEW user_view AS
SELECT ID, NAME, LAST_UPDATED_TIMESTAMP
FROM user;

方案二：用流（Streams）+任务（Tasks）追踪更新（无需修改原表） #

如果无法修改原表结构，可通过Snowflake的流和任务捕获更新操作，再关联到视图中：

1. 创建流捕获表的更新操作

CREATE OR REPLACE STREAM user_update_stream
ON TABLE user
APPEND_ONLY = FALSE; -- 捕获INSERT/UPDATE/DELETE操作

2. 创建日志表存储更新记录

CREATE OR REPLACE TABLE user_update_log (
  ID INT,
  OLD_NAME VARCHAR,
  NEW_NAME VARCHAR,
  UPDATE_TIMESTAMP TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
);

3. 创建任务定期同步流数据到日志表

CREATE OR REPLACE TASK user_update_task
WAREHOUSE = your_warehouse_name -- 替换为你的仓库名称
SCHEDULE = '1 MINUTE' -- 可根据需求调整调度频率
WHEN SYSTEM$STREAM_HAS_DATA('user_update_stream')
AS
INSERT INTO user_update_log (ID, OLD_NAME, NEW_NAME)
SELECT ID, OLD_NAME, NEW_NAME
FROM user_update_stream
WHERE METADATA$ACTION = 'UPDATE';

4. 启动任务

ALTER TASK user_update_task RESUME;

5. 创建关联视图
   通过关联原表和日志表，获取每行的最新更新时间：

CREATE OR REPLACE VIEW user_view AS
SELECT 
  u.ID, 
  u.NAME, 
  COALESCE(MAX(l.UPDATE_TIMESTAMP), CURRENT_TIMESTAMP()) AS LAST_UPDATED_TIMESTAMP
FROM user u
LEFT JOIN user_update_log l ON u.ID = l.ID
GROUP BY u.ID, u.NAME;

若原表有创建时间字段，可将CURRENT_TIMESTAMP()替换为该字段，结果更准确。

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