Snowflake时间旅行函数在复杂查询中报错的排查求助

需要在查询中获取Merchant Contract Service Discount表最近两个月月末的历史数据，尝试使用Snowflake的Time Travel功能实现，但在复杂多表关联查询中运行时出现意外错误，单独针对该表执行Time Travel语句却可正常运行，恳请提供解决思路。

用户提供的查询代码如下：

DECLARE @Today                datetime = GETDATE()
DECLARE @month                int = MONTH(@today)
DECLARE @year                 int = YEAR(@today)
DECLARE @beginOfPreviousMonth datetime = DATEFROMPARTS(@year, @month - 1, 1)
DECLARE @endOfPreviousMonth   datetime = EOMONTH(@beginOfPreviousMonth)

SELECT APPLICATION_NO as AppNo,
       MID,
       m.DBA_NAME,
       s.STATUS,
       APPLICATION_APPROVED_ON as ApprovedOn,
       i.AGENCY_NO,
       i.NAME        as 'Sales Office Name',
       pm.NAME      as Pricing,
       s1.NAME       as Service,
       dgs.NAME     as Tier,
       dg.NAME,
       BASIS_POINT    as [rate(bp)],
       ITEM_FEE
FROM  PROD.DWH_REPORTING_PPS.RPT_PPS_MERCHANT m
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_MERCHANT_CONTRACT mc
      ON m.ID = mc.MERCHANT_ID AND IS_DELETED <> 1 AND @endOfPreviousMonth BETWEEN mc.EffectiveFrom and mc.EffectiveTill
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_MERCHANT_CONTRACT_SERVICES mcs ON mcs.CONTRACT_ID = mc.ID and mcs.IS_DELETED <> 1
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_MERCHANT_CONTRACT_SERVICE_DISCOUNT_GROUP_SET mcsdgs
      on mcs.ID = mcsdgs.CONTRACT_SERVICE_ID and mcsdgs.IS_DELETED <> 1
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_MERCHANT_CONTRACT_SERVICE_DISCOUNT mcsd
      ON mcsdgs.ID = mcsd.MERCHANT_CONTRACT_SERVICE_DISCOUNT_GROUP_SET_ID and mcsd.IS_DELETED <> 1
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_STATUS s on m.STATUS_ID = s.ID
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_PRICING_MODELS pm on pm.Id = m.PRICING_MODEL_ID
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_DISCOUNT_GROUP dg on mcsd.DISCOUNT_GROUP_ID = dg.ID
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_DISCOUNT_GROUP_SET dgs on dg.DISCOUNT_GROUP_SET_ID = dgs.ID
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_SERVICE s1 on mcs.SERVICE_ID = s1.ID
    JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_ISC i on i.ISC_ID = m.ISC_ID
WHERE s.STATUS NOT IN ('Entered', 'Rejected', 'Terminated', 'Cancelled', 'Pending', 'Non-Processing', 'subject-to')
    and m.HIDDEN <> 1
order by MID, dgs.SERVICE_ID, (dg.TIER_NUMBER * -1) desc

解决思路 #

1. 规范Time Travel的使用方式 #

Snowflake的Time Travel需要为目标表明确指定历史版本，复杂查询中仅对单表使用Time Travel但其他表用当前版本，会因数据关联逻辑冲突报错。需在目标表后添加Time Travel语法，比如按指定时间回溯：

JOIN PROD.DWH_REPORTING_PPS.RPT_PPS_MERCHANT_CONTRACT_SERVICE_DISCOUNT mcsd 
  AT(TIMESTAMP => $endOfPreviousMonth)
  ON mcsdgs.ID = mcsd.MERCHANT_CONTRACT_SERVICE_DISCOUNT_GROUP_SET_ID and mcsd.IS_DELETED <> 1

如果需要回溯两个月的月末数据，需分别指定两个时间点的查询，再通过UNION ALL合并结果。

2. 修正Snowflake不兼容的语法 #

当前代码使用SQL Server风格的变量声明，Snowflake需改用自身语法：

SET Today = CURRENT_DATE();
SET month = EXTRACT(MONTH FROM $Today);
SET year = EXTRACT(YEAR FROM $Today);
SET beginOfPreviousMonth = DATE_FROM_PARTS($year, $month - 1, 1);
SET endOfPreviousMonth = LAST_DAY($beginOfPreviousMonth);
-- 上个月月末的前一个月月末
SET endOfTwoMonthsAgo = LAST_DAY(DATEADD(MONTH, -1, $beginOfPreviousMonth));

语法不兼容是复杂查询报错的常见隐性原因，单表查询时可能未触发语法校验问题。

3. 拆分查询逐步定位错误 #

将多表关联拆分为多个小步骤：

• 先单独验证带Time Travel的目标表查询结果是否符合预期
• 逐步加入关联表，每次运行后检查是否报错，定位到触发错误的关联环节
• 检查关联字段在历史版本数据中是否存在有效值，避免因历史数据缺失导致关联失败

4. 确认权限与数据保留期 #

• 确保当前用户对所有关联表拥有Time Travel访问权限
• 检查目标表的DATA_RETENTION_TIME_IN_DAYS配置，确认保留期覆盖需要回溯的两个月（默认仅1天，需提前设置）

5. 理清时间条件逻辑 #

原查询中@endOfPreviousMonth BETWEEN mc.EffectiveFrom and mc.EffectiveTill是过滤合同生效时间，结合Time Travel时，需明确该条件是针对历史版本的合同数据，还是当前版本，避免逻辑混淆导致数据不匹配。

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