如何在BigQuery中基于每日全量快照创建增量数据

针对你这种每日全量快照表生成增量的需求，我推荐用日期分区表关联+列级差异对比的方案，既高效又能精准识别变更行和变更列，完全适配百万级数据量、每日50万更新的场景。

核心思路 #

因为你的表是每日全量快照，我们只需要将今日的快照数据和昨日的快照数据按用户ID（Custid）关联，逐列对比差异，最终筛选出至少有一列发生变更的行——同时还能标记具体哪些列发生了变更，满足业务对变更细节的需求。

前提假设 #

假设你的快照表是按日期分区的，表名为your_project.your_dataset.customer_purchase_snapshots，分区字段为dt（格式如YYYY-MM-DD），这样能大幅减少扫描的数据量，提升查询效率。如果还没做分区，建议先给表加上日期分区，这对大数据场景的性能提升非常关键。

具体SQL实现 #

1. 基础版：仅提取所有变更行 #

如果只需要保留有变更的行，不需要标记具体变更列，用这个轻量版查询就足够：

WITH today_snapshot AS (
  SELECT *
  FROM `your_project.your_dataset.customer_purchase_snapshots`
  WHERE dt = CURRENT_DATE() -- 取今日全量快照
),
yesterday_snapshot AS (
  SELECT *
  FROM `your_project.your_dataset.customer_purchase_snapshots`
  WHERE dt = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) -- 取昨日全量快照
)
SELECT t.*
FROM today_snapshot t
LEFT JOIN yesterday_snapshot y
ON t.Custid = y.Custid
WHERE 
  -- 包含首次出现的用户（昨日无快照数据，属于新增增量）
  y.Custid IS NULL
  OR 
  -- 对比所有列，用IS NOT DISTINCT FROM处理NULL值的对比
  t.first_product IS NOT DISTINCT FROM y.first_product = FALSE
  OR t.first_product_purchase_date IS NOT DISTINCT FROM y.first_product_purchase_date = FALSE
  OR t.last_product IS NOT DISTINCT FROM y.last_product = FALSE
  OR t.last_product_purchase_date IS NOT DISTINCT FROM y.last_product_purchase_date = FALSE
  OR t.second_product IS NOT DISTINCT FROM y.second_product = FALSE
  OR t.second_product_purchase_date IS NOT DISTINCT FROM y.second_product_purchase_date = FALSE
  OR t.third_product IS NOT DISTINCT FROM y.third_product = FALSE
  OR t.third_product_purchase_date IS NOT DISTINCT FROM y.third_product_purchase_date = FALSE
  OR t.fourth_product IS NOT DISTINCT FROM y.fourth_product = FALSE
  OR t.fourth_product_purchase_date IS NOT DISTINCT FROM y.fourth_product_purchase_date = FALSE

2. 增强版：标记具体变更列 #

如果业务需要明确知道哪些列发生了变更，可以用这个版本，新增列标记和变更列汇总：

WITH today_snapshot AS (
  SELECT *
  FROM `your_project.your_dataset.customer_purchase_snapshots`
  WHERE dt = CURRENT_DATE()
),
yesterday_snapshot AS (
  SELECT *
  FROM `your_project.your_dataset.customer_purchase_snapshots`
  WHERE dt = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)
SELECT 
  t.*,
  -- 给每个列加是否变更的标记
  CASE WHEN t.first_product IS NOT DISTINCT FROM y.first_product THEN FALSE ELSE TRUE END AS first_product_changed,
  CASE WHEN t.first_product_purchase_date IS NOT DISTINCT FROM y.first_product_purchase_date THEN FALSE ELSE TRUE END AS first_product_purchase_date_changed,
  CASE WHEN t.last_product IS NOT DISTINCT FROM y.last_product THEN FALSE ELSE TRUE END AS last_product_changed,
  CASE WHEN t.last_product_purchase_date IS NOT DISTINCT FROM y.last_product_purchase_date THEN FALSE ELSE TRUE END AS last_product_purchase_date_changed,
  CASE WHEN t.second_product IS NOT DISTINCT FROM y.second_product THEN FALSE ELSE TRUE END AS second_product_changed,
  CASE WHEN t.second_product_purchase_date IS NOT DISTINCT FROM y.second_product_purchase_date THEN FALSE ELSE TRUE END AS second_product_purchase_date_changed,
  CASE WHEN t.third_product IS NOT DISTINCT FROM y.third_product THEN FALSE ELSE TRUE END AS third_product_changed,
  CASE WHEN t.third_product_purchase_date IS NOT DISTINCT FROM y.third_product_purchase_date THEN FALSE ELSE TRUE END AS third_product_purchase_date_changed,
  CASE WHEN t.fourth_product IS NOT DISTINCT FROM y.fourth_product THEN FALSE ELSE TRUE END AS fourth_product_changed,
  CASE WHEN t.fourth_product_purchase_date IS NOT DISTINCT FROM y.fourth_product_purchase_date THEN FALSE ELSE TRUE END AS fourth_product_purchase_date_changed,
  -- 汇总所有变更列的名称，用逗号分隔
  ARRAY_TO_STRING(
    ARRAY_CONCAT(
      IF(t.first_product IS NOT DISTINCT FROM y.first_product = FALSE, ['first_product'], []),
      IF(t.first_product_purchase_date IS NOT DISTINCT FROM y.first_product_purchase_date = FALSE, ['first_product_purchase_date'], []),
      IF(t.last_product IS NOT DISTINCT FROM y.last_product = FALSE, ['last_product'], []),
      IF(t.last_product_purchase_date IS NOT DISTINCT FROM y.last_product_purchase_date = FALSE, ['last_product_purchase_date'], []),
      IF(t.second_product IS NOT DISTINCT FROM y.second_product = FALSE, ['second_product'], []),
      IF(t.second_product_purchase_date IS NOT DISTINCT FROM y.second_product_purchase_date = FALSE, ['second_product_purchase_date'], []),
      IF(t.third_product IS NOT DISTINCT FROM y.third_product = FALSE, ['third_product'], []),
      IF(t.third_product_purchase_date IS NOT DISTINCT FROM y.third_product_purchase_date = FALSE, ['third_product_purchase_date'], []),
      IF(t.fourth_product IS NOT DISTINCT FROM y.fourth_product = FALSE, ['fourth_product'], []),
      IF(t.fourth_product_purchase_date IS NOT DISTINCT FROM y.fourth_product_purchase_date = FALSE, ['fourth_product_purchase_date'], [])
    ),
    ', '
  ) AS changed_columns
FROM today_snapshot t
LEFT JOIN yesterday_snapshot y
ON t.Custid = y.Custid
WHERE 
  y.Custid IS NULL
  OR 
  t.first_product IS NOT DISTINCT FROM y.first_product = FALSE
  OR t.first_product_purchase_date IS NOT DISTINCT FROM y.first_product_purchase_date = FALSE
  OR t.last_product IS NOT DISTINCT FROM y.last_product = FALSE
  OR t.last_product_purchase_date IS NOT DISTINCT FROM y.last_product_purchase_date = FALSE
  OR t.second_product IS NOT DISTINCT FROM y.second_product = FALSE
  OR t.second_product_purchase_date IS NOT DISTINCT FROM y.second_product_purchase_date = FALSE
  OR t.third_product IS NOT DISTINCT FROM y.third_product = FALSE
  OR t.third_product_purchase_date IS NOT DISTINCT FROM y.third_product_purchase_date = FALSE
  OR t.fourth_product IS NOT DISTINCT FROM y.fourth_product = FALSE
  OR t.fourth_product_purchase_date IS NOT DISTINCT FROM y.fourth_product_purchase_date = FALSE

关键优化建议 #

1. 聚焦高频变更列：如果只关心那5个高频变更列，可以只保留这些列的对比条件，减少计算量，提升查询速度。比如只对比last_product、last_product_purchase_date等5个列。
2. 处理NULL值：一定要用IS NOT DISTINCT FROM替代普通的=或!=——BigQuery中NULL和任何值比较结果都是NULL，这个运算符会把NULL视为相等，更符合业务逻辑。
3. 落地增量表：建议将查询结果写入一个按日期分区的增量表（比如customer_purchase_increments），方便后续业务直接使用，避免重复计算。
4. 排除新增用户：如果不需要首次出现的用户数据，可以去掉y.Custid IS NULL这个条件。

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