dbt增量模型构建咨询：surrogate_key生成与备份问题

问题背景与疑问 #

• 首次构建dbt增量模型，campaignchannel表的surrogate_key由clientcode_id拼接序号生成，作为唯一键。
• 需求：新增campaign时，获取对应客户的最大surrogate_key并加1，生成该客户下新campaign的唯一键。
• 疑问：
  1. 此场景是否适合用增量模型？
  2. 为增量模型创建备份是否合理？

原dbt模型代码 #

-- dbt campaign channel level dimension : dim_campaignchannel

{{ config(materialized='incremental', unique_key=surrogate_key) }}

SELECT  --ROW_NUMBER() OVER (ORDER BY c.clientcode, c.adservername) AS index_number,
CAST(CONCAT(c.clientcode_id, ROW_NUMBER() OVER (ORDER BY c.clientcode, c.adservername)) as numeric) AS surrogate_key, c.* from
(
SELECT b.clientcode_id, a.clientcode, a.adservername, a.mediachannel, a.adtech, a.programname, a.funnelstage, a.period, a.season, a.campaigntype
      ,a.lob, a.businessline, a.objective, a.market, a.targettype, a.subcampaign, a.campaignyear, a.startdate, a.enddate
FROM  public.map_campaign_segments a
JOIN
public.map_client_segments b ON a.clientcode = b.clientcode
where a.adservername != '-'
order by a.clientcode, c.adservername) c
order by c.clientcode, c.adservername

改写后的尝试代码 #

-- dbt campaign channel level dimension : dim_campaignchannel_master

{{ config(materialized='incremental',
          unique_key='surrogate_key') }}

SELECT
    CASE
        WHEN EXISTS (
            SELECT 1 FROM dim_campaignchannel WHERE adservername = c.adservername
        ) THEN
            CAST(CONCAT(c.clientcode_id, ROW_NUMBER() OVER (ORDER BY c.clientcode, c.adservername)) as numeric) -- Generate a new surrogate key if adservername exists
        ELSE
            (SELECT MAX(surrogate_key) + 1 FROM dim_campaignchannel WHERE clientcode = c.clientcode) -- Increment the surrogate key for existing clientcode
    END AS surrogate_key,
    c.*
FROM (
    SELECT
        b.clientcode_id, a.clientcode, a.adservername, a.mediachannel, a.adtech, a.programname, a.funnelstage, a.period, a.season, a.campaigntype,
        a.lob, a.businessline, a.objective, a.market, a.targettype, a.subcampaign, a.campaignyear, a.startdate, a.enddate
    FROM
        public.map_campaign_segments a
    JOIN
        public.map_client_segments b ON a.clientcode = b.clientcode
    WHERE
        a.adservername != '-'
    ORDER BY
        a.clientcode, a.adservername
) c

{% if is_incremental() %}

  -- this filter will only be applied on an incremental run
  -- (uses >= to include records arriving later on the same day as the last run of this model)
  WHERE
      NOT EXISTS (
          SELECT 1 FROM dim_campaignchannel WHERE adservername = c.adservername
      )

{% endif %}

ORDER BY
    c.clientcode, c.adservername

问题解答 #

1. 此场景是否适用增量模型？ #

适用，但现有改写逻辑存在关键缺陷：

• 增量模型的核心价值是只处理新数据、避免全量重跑，你的需求是针对新增campaign生成唯一键，完全匹配增量模型的适用场景——无需每次重新计算所有历史数据的surrogate_key，仅处理新增数据即可。
• 你改写的代码逻辑问题：
  • 增量过滤条件NOT EXISTS (SELECT 1 FROM dim_campaignchannel WHERE adservername = c.adservername)仅处理全新adservername的campaign，但同一clientcode下新增多个campaign时，MAX(surrogate_key)+1会导致所有新增campaign共用同一个key，违反唯一键要求。
  • 正确逻辑示例：针对每个客户的新增campaign，基于该客户历史最大surrogate_key，按新增顺序依次递增：

    SELECT
        CAST(
            COALESCE((SELECT MAX(surrogate_key) FROM dim_campaignchannel WHERE clientcode = c.clientcode), 0)
            + ROW_NUMBER() OVER (PARTITION BY c.clientcode ORDER BY c.adservername)
            AS numeric
        ) AS surrogate_key,
        c.*
    FROM (
        -- 新增数据子查询（需确保仅包含未进入模型的campaign）
    ) c
    

  • 补充：原代码中unique_key=surrogate_key需加引号，改为unique_key='surrogate_key'，否则dbt会将其识别为变量而非字段名。

2. 为增量模型创建备份是否合理？ #

非常合理，甚至是生产环境的必要操作：

• 增量模型依赖历史数据状态，若某次跑数出现逻辑错误、数据污染，直接覆盖/追加错误数据后，恢复成本极高。备份可快速回滚到之前的正确状态。
• 推荐备份方式：
  • 利用dbt的快照（snapshot）功能，定期为增量模型表生成历史版本快照。
  • 每次增量跑数前，自动复制当前表到按日期命名的备份表（如dim_campaignchannel_backup_YYYYMMDD）。
  • 若数据仓库支持原生版本控制（如Snowflake Time Travel、BigQuery快照），可直接利用该功能简化备份逻辑。

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