基于Snowflake SQL和dbt增量模型计算用户最后活跃日期

针对你在dbt+Snowflake增量模型中计算用户最后活跃日期的需求，这里提供一个同时支持增量更新和全量刷新的高效方案，解决你之前尝试方法中的痛点：

核心思路 #

利用Snowflake的LAST_VALUE窗口函数结合IGNORE NULLS特性，配合dbt的增量模型判断宏is_incremental()，实现：

• 全量刷新时，扫描整个源表计算所有日期的最后活跃日期
• 增量更新时，仅处理目标表最新日期之后的新增数据，避免全量扫描

完整dbt模型代码 #

{{ config(
    materialized='incremental',
    unique_key=['user_id', 'date'],
    incremental_strategy='merge'
) }}

WITH daily_activity AS (
    SELECT
        user_id,
        date,
        active,
        -- 计算截至当前日期的用户最后活跃日期：跳过非活跃日期的空值，取最近的活跃日期
        LAST_VALUE(CASE WHEN active = 1 THEN date END IGNORE NULLS) 
            OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_active_dt
    FROM {{ ref('daily_user_activity_table') }}
    {% if is_incremental() %}
        -- 增量模式：仅加载目标表中最新日期之后的新增数据
        WHERE date > (SELECT MAX(date) FROM {{ this }})
    {% endif %}
),
-- 可选：如果源表存在重复的user_id+date记录，添加去重逻辑
deduped_activity AS (
    SELECT
        user_id,
        date,
        active,
        last_active_dt
    FROM daily_activity
    QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id, date ORDER BY date DESC) = 1
)

SELECT * FROM deduped_activity

方案优势说明 #

1. 同时兼容增量与全量

   • 全量刷新时，is_incremental()返回false，会扫描整个源表，重新计算所有日期的last_active_dt，确保数据准确性。
   • 增量更新时，仅加载目标表最新日期之后的新增数据，窗口函数仅在新增数据范围内计算，大幅减少扫描数据量，提升效率。

2. 解决你之前方法的问题

   • 替代滑动窗口MAX：无需扫描无界前置行，LAST_VALUE+IGNORE NULLS会自动跳过非活跃日期的空值，直接取最近的活跃日期，逻辑更简洁高效。
   • 替代LAG函数：不会因为中间非活跃记录返回空值，LAST_VALUE会持续传递最近的活跃日期，确保每个日期的last_active_dt都是截至当天的最后活跃日期。
   • 替代QUALIFY子句：无需单独关联历史最后活跃日期，直接在窗口函数中完成计算，同时支持全量刷新场景。

优化建议 #

• 如果源表的user_id+date组合是唯一的，可以去掉deduped_activity的去重逻辑，进一步提升性能。
• 可以在模型配置中添加incremental_predicates，帮助Snowflake更精准地扫描增量数据：

  {{ config(
      materialized='incremental',
      unique_key=['user_id', 'date'],
      incremental_strategy='merge',
      incremental_predicates=["date > (SELECT MAX(date) FROM {{ this }})"]
  ) }}
  

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