基于Snowflake修正布尔值回退导致的视图状态记录错误

一、现有错误数据的修正 #

针对示例中序列号245586的错误数据，可通过以下SQL操作完成修正：

1. 调整错误行的ExportDate #

将第2行的ExportDate更新为下一个状态行LoadDate的前一日，匹配状态周期的结束时间：

UPDATE your_view_table
SET ExportDate = DATEADD(DAY, -1, '2022-07-05 04:33:32.551'::TIMESTAMP)
WHERE Serial_No = '245586'
  AND LoadDate = '2022-06-03 04:33:05.452'::TIMESTAMP;

2. 插入缺失的状态回退记录 #

新增对应Reserved=FALSE状态的记录，LoadDate设为上一个TRUE状态行ExportDate的次日，ExportDate使用原错误的时间戳：

INSERT INTO your_view_table (Serial_No, Reserved, In_Stock, Blocked, Disposed, LoadDate, ExportDate)
VALUES (
  '245586',
  FALSE,
  TRUE,
  FALSE,
  FALSE,
  DATEADD(DAY, 1, '2022-12-22 03:53:00.000'::TIMESTAMP),
  '2023-01-16 03:54:00.000'::TIMESTAMP
);

二、优化后续状态捕获逻辑（避免同类问题复发） #

单纯的哈希对比无法区分相同状态但不同时间周期的记录，需结合状态的时间序列判断是否生成新行，核心逻辑如下：

核心规则 #

• 每日捕获当前状态时，仅对比该序列号的最新历史状态，而非所有历史状态
• 若当前状态与最新历史状态不同，直接插入新行，保留原有状态的时间周期
• 若状态无变化，则更新最新行的ExportDate为当前时间，延续状态周期

实现SQL（每日执行的同步任务） #

-- 获取每个序列号的最新历史状态
WITH latest_hist_status AS (
  SELECT 
    Serial_No,
    Reserved,
    In_Stock,
    Blocked,
    Disposed,
    MAX(LoadDate) AS last_load_date
  FROM your_view_table
  GROUP BY Serial_No, Reserved, In_Stock, Blocked, Disposed
),
-- 获取底层数据的当前最新状态
current_source_status AS (
  SELECT 
    Serial_No,
    Reserved,
    In_Stock,
    Blocked,
    Disposed,
    CURRENT_TIMESTAMP AS sync_time
  FROM your_underlying_table
)
-- 合并数据：判断插入新行或更新ExportDate
MERGE INTO your_view_table target
USING (
  SELECT 
    css.Serial_No,
    css.Reserved,
    css.In_Stock,
    css.Blocked,
    css.Disposed,
    css.sync_time,
    lhs.last_load_date
  FROM current_source_status css
  LEFT JOIN latest_hist_status lhs ON css.Serial_No = lhs.Serial_No
) source
ON target.Serial_No = source.Serial_No AND target.LoadDate = source.last_load_date
WHEN MATCHED AND (
  target.Reserved != source.Reserved OR 
  target.In_Stock != source.In_Stock OR 
  target.Blocked != source.Blocked OR 
  target.Disposed != source.Disposed
) THEN
  INSERT (Serial_No, Reserved, In_Stock, Blocked, Disposed, LoadDate, ExportDate)
  VALUES (source.Serial_No, source.Reserved, source.In_Stock, source.Blocked, source.Disposed, source.sync_time, source.sync_time)
WHEN MATCHED THEN
  UPDATE SET ExportDate = source.sync_time
WHEN NOT MATCHED THEN
  INSERT (Serial_No, Reserved, In_Stock, Blocked, Disposed, LoadDate, ExportDate)
  VALUES (source.Serial_No, source.Reserved, source.In_Stock, source.Blocked, source.Disposed, source.sync_time, source.sync_time);

此逻辑会完整记录每次状态变更（包括回退场景），确保每个状态的时间周期清晰可追溯。

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