数据管道重跑一次,营收为什么会翻倍
用 MERGE 和事务让每日加载幂等,夜间任务就能直接回填多年历史数据。
要点速览
- 水位线只负责筛选数据,防重复要靠写入步骤。
- 按业务键 MERGE,或在同一个事务里对整个分区先 DELETE 再 INSERT,重跑后结果不变。
- 每个分区都幂等之后,回填就是按顺序把每一天重跑一遍。
设想你到一家零售客户驻场的第一周。昨晚的订单加载任务跑到一半挂了,运维点了重跑,今天早上看板上的营收翻了一倍。没有人写错一行 SQL,问题在于这个任务从设计上就没考虑过会被运行两次。
如果你是前线部署工程师(FDE),迟早会遇到这种情况。
客户早晚也会问:“能把我们从去年年初开始的数据都加载进来吗?”只要你的管道重跑不产生重复数据,你就可以放心答应,不用熬一个通宵。
本文搭建一个每日订单管道,它具备三个特性:增量加载、可以安全重跑、回填使用的正是每晚运行的同一套代码。下面的 SQL 做了简化,具体语法因客户的数据仓库而异。
开始之前需要准备什么?
IBM 把数据管道描述为三个阶段:从多个来源摄取原始数据,对数据做转换,再存入数据仓库。本文的管道以批处理模式运行,按固定间隔分块加载数据。每个批次由作业调度器触发,这类软件按计划在后台运行任务,无需有人值守。
你需要一个支持事务和 MERGE 的数据库,外加两张表:staging_orders 存放刚拉取的原始数据,orders 是目标表。每条订单以 order_id 作为业务键,以 order_date 作为事件日期。此外只需要一个小 Python 脚本,按天调用任务。
动手之前,先记住这个定义:一个操作如果执行多次后结果不变,它就是幂等的。正因如此,它才可以重试,而不会带来副作用或损坏数据。
有一点常被忽略:幂等是单个操作的属性,不会自动在整个系统上成立。管道里的每一步都要单独按幂等来设计。
第 1 步:以每日分区作为工作单元
每次运行只处理一个逻辑日,称为 batch_date。这个日期由调度器作为参数传入,任务本身从不自行判断“今天”是哪天。
-- 任务中的每条语句都按这个参数过滤
SELECT order_id, customer_id, amount, updated_at, order_date
FROM staging_orders
WHERE order_date = :batch_date;
**检查:**对 2026-03-01 把上面的查询运行两次,行数必须相同。如果不同,说明数据源在你读取时仍在变化,继续之前必须先弄清楚这一点。
第 2 步:用 MERGE 让重跑不产生新数据
营收看板翻倍,罪魁祸首通常是普通的 INSERT。假设 2026-03-01 有 1,000 笔订单,INSERT 运行两次,目标表里就有 2,000 行。MERGE(upsert)则不同:新键插入,已有的键更新。
MERGE INTO orders AS t
USING (
SELECT order_id, customer_id, amount, updated_at, order_date
FROM staging_orders
WHERE order_date = :batch_date
) AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
customer_id = s.customer_id,
amount = s.amount,
updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT
(order_id, customer_id, amount, updated_at, order_date)
VALUES (s.order_id, s.customer_id, s.amount, s.updated_at, s.order_date);
重跑同一批次时,每一行都会被更新成它原本的值,所以第二次运行不会改变任何东西。在上面的例子里,无论任务运行多少次,目标表始终是 1,000 行。
**检查:**把 MERGE 运行两次,然后查找重复数据,结果必须为空。
SELECT order_id, COUNT(*)
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
第 3 步:没有业务键,就覆盖整个分区
有些表没有稳定的业务键,比如每日营收汇总表。这时的做法是把整个分区当作工作单元,整体替换。重跑某一天,就是替换那一天的数据,而不是让它翻倍。
BEGIN;
DELETE FROM daily_revenue WHERE order_date = :batch_date;
INSERT INTO daily_revenue (order_date, total_amount, order_count)
SELECT order_date, SUM(amount), COUNT(*)
FROM orders
WHERE order_date = :batch_date
GROUP BY order_date;
COMMIT;
这段代码里最关键的是 BEGIN 和 COMMIT。先 DELETE 再 INSERT,只有放在同一个事务里才安全。如果任务在两条语句之间崩溃,分区就会被清空,第二天早上客户看到的当天营收是零。
**检查:**在测试环境里让任务在 DELETE 之后立即停止(例如故意注入一个错误),然后确认当天的旧数据完好无损。
第 4 步:把 NOW() 从写入中拿掉
很多任务会给每一行写入 loaded_at = NOW()。这样做的管道每次重跑都会产生不同的数据,也就不再幂等。应改用事件时间或批次的逻辑日期。下面仍是第 3 步的写入,只改动 BEGIN ... COMMIT 块里的 INSERT:
-- 修改前:... SELECT order_date, SUM(amount), COUNT(*), NOW()
-- 修改后:写入调度器传入的逻辑日期
INSERT INTO daily_revenue (order_date, total_amount, order_count, batch_date)
SELECT order_date, SUM(amount), COUNT(*), :batch_date
FROM orders
WHERE order_date = :batch_date
GROUP BY order_date;
**检查:**把同一天运行两次,将结果导出成文件后做 diff。哪怕只有一列不同,说明任务里仍有不确定性的来源。
为什么水位线救不了你?
常见的增量加载做法是保存一个水位线,例如已加载的最大 updated_at,下次只拉取比它更新的行。Algoscale 的一篇分析指出,水位线逻辑看似简单,实际却出奇地脆弱。
它会在恰好落在边界上的时间戳、迟到的数据行以及时区不一致时出问题,在 Fabric 上的一个案例中还产生了重复数据。
所以,别让水位线去负责数据正确性。写入一旦幂等,每个业务键只对应一行,同一行被选中两次也不会产生重复。按 Algoscale 的说法,重复选取此后不再损坏数据,最坏也只是多读一点数据。
实践中,写入改为 MERGE 之后,你可以有意把水位线往回挪一点,以接住迟到的数据行。代价是多读一些数据,换来的是不必再担心丢数据。
第 5 步:回填只是一个循环
到这一步,回填客户的历史数据就变得很简单:逐个分区重跑。Data Vidhya 写道,拥有幂等管道的团队,回填就是在某个普通的周二下午顺手完成的事。下面的脚本做了简化,run_partition 是针对指定日期调用第 1 至第 4 步的函数。
from datetime import date, timedelta
def run_partition(batch_date):
# 先 MERGE 写入 orders,再覆盖 batch_date 当天的 daily_revenue
...
d = date(2025, 1, 1)
end = date(2025, 12, 31)
while d <= end:
run_partition(d)
d += timedelta(days=1)
**检查:**在一个短区间(比如三天)上把循环运行两次,两次的行数和合计值必须完全相同。如果循环在第 200 天挂掉,从那一天接着重跑即可,无需任何清理。
常见陷阱
最常见的错误是选错业务键。如果客户的源系统在不同分店之间复用 order_id,MERGE 就会覆盖错误的订单,正确的键应该是分店加订单号的组合。这一点要在客户需求调研阶段问清楚,不要靠猜。
同样常见的是:DELETE 和 INSERT 分处两个任务,或者在自动提交模式下运行,这就丢掉了第 3 步中事务提供的安全网。还有很多团队只测试正常路径。一个从未对同一天运行过两次的管道,应当视为未经测试。
在客户现场时
到一家新客户,你的第一项任务不是写一条新管道,而是问一句:“如果重跑昨晚的任务,会发生什么?”答案会告诉你,他们的系统能否扛住重试和回填。
在答应加载三年历史数据之前,先查清每张目标表是用 INSERT、MERGE 还是分区覆盖写入的。
如果你在应聘 FDE 岗位,留意职位描述中提到“data ingestion”“backfill”或“pipeline reliability”的岗位。简历上写“构建过 ETL 管道”几乎等于什么都没说。不如写清楚:你把一个任务从 INSERT 改成按业务键 MERGE,因此回填了一年的数据且没有任何重复。
每条管道终究都会出故障。真正值得展示的,是出了故障只需重跑、无需有人去清理数据的管道。
这篇文章对你有帮助吗?
感谢你的反馈!