ETL设计实战：增量抽取、清洗拒绝表与幂等加载

简介：《ETL设计详解（数据抽取、清洗与转换）》是一份面向BI项目开发者、数据仓库工程师及ETL初学者的技术文档，聚焦数据抽取、清洗转换与加载三个核心环节，帮助读者理清ETL设计流程与常见问题。文档结合业务系统数据源差异，讨论数据库链接、ODBC、文件导入等抽取方式，并梳理不完整数据、错误数据与重复数据的清洗策略，同时涉及增量更新、数据粒度转换、商务规则计算及日志与警告发送等实践要点。压缩包内含1个docx文件，大小约20KB，篇幅紧凑，便于快速查阅与归档。已有1538人学习，适合作为数据仓库项目设计前的思路梳理材料，也可用于对照检查抽取方法、清洗规则与加载方案是否完整，为后续开发提供参考。

1. 做 ETL 的人，大多栽在“数据抽过来了，但不敢用”

链路跑通、报表出数，业务一句“这个数和上月对不上”，就能让人熬一整晚。数据仓库项目里有这样一条时间分配：整个 BI 项目大约三分之一的时间花在 ETL 上，而抽取、清洗、转换三段里，真正吃掉三分之二工时的是清洗和转换，抽取和加载加起来只是零头。拖垮项目的往往不是“连不上库”，而是“连上了、也抽过来了，却没人敢确认这批数据是对的”。所以ETL 的设计目标不是跑通一次，而是让每一行数据都能反查来源、每一次失败都能定位到具体步骤、每一条被过滤掉的记录都有一个去处。下面按抽取、清洗、转换与加载、排查的顺序，把这条链路拆开看。

2. 数据抽取：为什么“取大于最大时间”的增量一定会漏数据

2.1 调研阶段的四个问题，决定抽取能不能自动化

抽取前要问清几件事：数据来自几个业务系统、各系统跑的是什么 DBMS、有没有手工数据、手工数据量多大、有没有非结构化数据。这四个问题表面是清单，实际在回答同一件事——这条链路是机器全自动，还是必须留一个人工环节。系统数量决定接口矩阵的规模（n 个源系统乘 m 个主题）；DBMS 类型决定用库链接、标准驱动还是文件落地；手工数据的存在意味着调度里必须有一个“人 + 截止时间”的节点；非结构化数据则意味着清洗阶段要多挂一个解析器。

把答案整理成一张数据源画像表，比写在文档里管用得多，因为后续每一个设计决策都能回溯到某一行：

最后一行最容易被忽略。只追加的表最好处理；有更新的表要确认更新时间字段是不是真的被维护；有物理删除的表，任何基于时间戳的增量方案都看不见那些消失的行。我在项目里吃过一次亏：源系统删掉一批作废单据，下游报表里它们还在，因为删除在水位线模型里是隐形的。

2.2 同构、异构、文件：三条链路的真实边界

同构数据源最省事，在 DW 库和业务库之间建一个数据库链接，直接写 SELECT 就能跨库读。这里的问题在于压力回传，连接不是问题：跨库查询的执行计划经常整体下推到源库执行，一个没走索引的关联就能把业务库的 IO 打满。所以同构链路必须配三样东西——只读账号、明确的查询时间窗口、分批拉取。没有这三样，抽取任务就是埋在业务系统边上的一颗雷。

异构数据源通过标准驱动建立连接，真正的问题在类型映射。源端的decimal(38,10)落到目标端numeric(18,4)会静默截断，不报错；字符集不同会在中文和特殊符号上出乱码；带时区的时间戳落到date列会丢掉偏移量。稳妥做法是 ODS 层用最宽的类型接（字符串或高精度数值），把收窄动作推迟到转换层，让“截断”这件事发生在有校验的地方，而不是在传输途中悄悄发生。

连接建不起来时只有两条路：导出成 txt、xls 再导入 ODS，或者走程序接口。文件路线的难点不在读，而在“可信”：行数要和源端对得上，文件尾要有结束标记防止半截文件被当成完整文件，编码要确认是 GBK 还是带 BOM 的 UTF-8，分隔符和转义符要和导出方约定死。重复投递同一份文件时，靠文件名里的批次号做幂等，比靠内容判断可靠得多。

-- 分批抽取：按主键区间切片，避免深分页和源库长事务 -- ${lo} / ${hi} 由调度器按主键分布预先切分，每批约 5 万行 SELECT id, cust_code, amount, updated_at FROM src_order WHERE id > ${lo} AND id <= ${hi} AND updated_at > ${last_watermark} ORDER BY id;

这里用主键区间而不是LIMIT offset，原因是深分页越翻越慢——偏移量越大，数据库越要把前面的行扫一遍再丢掉。区间切片的扫描范围是可控的，每一批的耗时也基本稳定，调度器才好估算超时阈值。${lo}、${hi}在任务启动时一次性算好并写入批次记录，这样中途失败也能从断点续跑，而不是从头再来。

2.3 水位线：增量抽取的三种漏数方式

增量抽取的核心是一个水位线，通常存在元数据表里一行记录：任务号、上次成功的水位值、批次号、状态。运行时先读水位线，去源系统取大于它的记录，写目标端，成功之后再推进水位线。这个机制简单到几乎不需要设计，但它有几种失效方式，每一种都静默发生。

第一种是字段选错或压根没维护。业务表用覆盖式 UPDATE 做修改，UPDATE 语句里却没有刷新更新时间，这条记录的时间戳永远停在过去，水位线永远跨过它。第二种是长事务：某个事务 10:00 开始、10:30 才提交，记录的时间戳是 10:00；抽取任务 10:10 跑完并把水位线推到 10:10，这条记录就永久落在水位线之下，谁也不会再去捞它。第三种是时钟和时区：时间戳由应用服务器生成，多节点之间本来就漂移几秒到几分钟，源库存 UTC 而抽取按本地时间比较，就会出现“未来时间”的记录被上界挡掉。

-- 带安全窗口的增量抽取：用可控的重复换取不漏 -- ${last_watermark} 来自 etl_job_state 表上次成功提交的值 -- ${batch_upper_bound} 本批次启动时刻，冻结上界保证可重跑 SELECT id, order_no, amount, updated_at FROM src_order WHERE updated_at > DATEADD(minute, -10, ${last_watermark}) -- 回退窗口，容忍长事务与时钟漂移 AND updated_at <= ${batch_upper_bound} -- 上界冻结 ORDER BY updated_at, id;

两个参数各有分工。回退窗口（例子里取 10 分钟）的大小取决于源系统最长事务的持续时间，本质是主动制造一小段重复，再用幂等抵消掉——重复可以靠下游 merge 消除，漏抽没有任何后悔药。上界冻结是为了让同一批次可以原样重跑：如果上界取“当前时间”，每次重跑抽到的数据集都不一样，出了问题根本无法复现。水位线的推进必须放在目标端提交成功之后，顺序反了，一次崩溃就会丢一批数据，而且丢得毫无痕迹。

3. 数据清洗：过滤掉的数据去哪了

3.1 清洗的本质是分流，不是删除

不符合要求的数据分三类：不完整、错误、重复。多数人第一反应是写个 WHERE 把它们排除掉，然后忘记它们。问题在于，清洗规则本身是会变的，今天被判定为异常的值，明天业务说这是合法业务（比如负数金额其实是退款），这时候你要能回答两个问题：这批数据当时被谁、用什么规则、以什么理由拦下来的；以及能不能把它们找回来重新处理。

所以清洗的第一层设计不是规则，是一张拒绝表：批次号、源表名、主键值、原因码、原始字段快照、处置状态、责任人、创建时间、关闭时间。有了它，过滤才是一次可追溯的操作，而不是一次数据销毁。处置状态尤其关键，它要能表达“待确认”“已修正”“已豁免”三种情况，其中“已豁免”是给那些合法但不符合规则的数据准备的——没有这个状态，每次重跑它们又会被拦一遍，永远清不干净。

-- 检出"有主表无明细"的不完整数据，打标而不是丢弃 INSERT INTO etl_reject (batch_id, src_table, pk_value, reason_code, detail, status, created_at) SELECT '${batch_id}', 'src_order', o.order_no, 'ORPHAN_ORDER', '订单无明细行', 'PENDING', CURRENT_TIMESTAMP FROM src_order o LEFT JOIN src_order_item i ON i.order_no = o.order_no WHERE i.order_no IS NULL;

reason_code用枚举而不是自由文本，是为了后续能按原因聚合——按缺失内容分别导出成不同的文件交给业务方，比丢一张几十万行的大表过去有效得多。batch_id是回溯的锚点，任何一条被拦下的记录都能顺着它找回当时的源数据快照。

3.2 错误数据：会炸链路的那一类必须提前拦

错误数据的成因往往是业务系统输入时不做校验：数值输成全角数字、字符串尾部带一个看不见的回车、日期格式不统一、日期越界（比如 2 月 30 日）。这些值在源端能存下来，因为源端列是字符串；到了 DW 就炸，因为目标列是强类型。

关键区别在这里：类型转换失败发生在链路的末端——抽取跑完了、清洗跑完了、写入的时候才报错，前面所有工作全部白做，而且报错信息通常只告诉你“第 38291 行转换失败”，不告诉你这批一共还有多少条同类问题。所以类型断言要前移到清洗阶段，而且要设计成“打标不抛异常”，让一条坏数据不至于中断整批。

# 类型断言：在进入 DW 之前拦下"源端合法、目标端非法"的值 import re FULLWIDTH = str.maketrans("０１２３４５６７８９．－", "0123456789.-") def normalize_amount(raw: str) -> tuple[str, str | None]: s = (raw or "").translate(FULLWIDTH).strip() # 全角转半角 + 去掉首尾空白和回车 if not re.fullmatch(r"-?\d+(\.\d{1,2})?", s): return "", "BAD_NUMERIC" # 返回原因码，交给分流器写拒绝表 return s, None

函数返回一个二元组而不是抛异常，是为了让调用方对每一条记录都能做出“入库或分流”的决定，批次继续往下走。全角转换放在校验之前，是因为全角数字在业务上就是合法数字，只是录入方式不同，这类问题能在 ODS 层就地修掉，不必推回业务系统；而日期越界、格式错误这类问题没法可靠地猜，只能挑出来交给业务方限期修正，修正后再抽。两者的处置路径完全不同，混在一起处理就会变成一场灾难。

3.3 维表重复为什么不能自动去重

维表重复比事实表重复更危险。事实表重复通常表现为指标翻倍，一眼能看出来；维表重复意味着同一个实体（同一个供应商、同一个客户）在维度里对应了两个代理键，事实表挂哪个键都“合法”，但两条链路算出来的口径不一样，报表上表现为同一个客户被拆成两行，各占一半。

自动去重的诱惑很大——取更新时间最新的那条，或者取非空字段最多的那条。这两种规则都会静默改变历史口径：昨天挂在旧键上的历史事实，今天因为保留键变了而变成了“孤儿”。所以这块必须留人工裁决：系统负责检出并分组，把候选记录的完整字段导出，由业务方确认保留哪一条，然后把裁决结果写成一张映射表（旧键 → 保留键），再按映射表重写历史事实。这张映射表就是这里的补救手段，没有它，任何一次误判都不可逆。

3.4 清洗规则本身要有版本和回归样本

规则写进代码之后，最容易被当成一次性产物。实际上一条规则从上线起就在被修改：业务说这类数据不该拦，或者发现某类脏数据没拦住。每次修改都应该带着规则编号和版本号，并且配一组历史样本做回放——新规则在这批样本上拦了多少、放了多少，和上一版对比，重点看两边差异的那部分记录到底该拦还是该放。

这套土办法本质上是在盯两个方向：误杀和漏放。误杀会让业务方发现数据变少了，通常很快反馈；漏放最麻烦，脏数据一路流进 DW，等到分析结论出问题才被发现，那时候已经很难定位是哪条规则漏的。回放样本要长期保存，它比任何文档都更能说明这条链路当初为什么这么设计。

4. 转换与加载：从 ODS 到 DW 之间的三次重塑

4.1 不一致数据转换：编码统一与代理键

同一个供应商，在结算系统里编码是 XX0001，在客户关系系统里是 YY0001。这种不一致不是错误，是各系统独立建设的必然结果。转换阶段要做的是实体识别：把多个源编码归并到同一个实体上，并分配一个仓库内部的代理键。

实体识别一般落成一张映射表：源系统标识、源编码、实体代理键、生效区间。事实表只存代理键，不存业务编码。这一层间接性看着多余，实际是整个 DW 抗变化的根基：源系统哪天改了编码规则，只需要在映射表里加一行，历史事实不用动;如果事实表直接存业务编码，源系统一改，所有历史数据的关联全部断掉。

映射表必然会有未命中的情况——新供应商建了档但没同步过来，或者源系统编码变更没通知。这些不能静默丢弃，要进一张未命中队列表，由数据管理员补录。补录之后还有个动作容易漏：受影响的历史分区要重算，否则新旧键并存，同一个供应商在报表里出现两次。

4.2 粒度转换：聚合到底该发生在哪一层

业务系统存的是明细，数据仓库面向分析，通常不需要那么细。但“聚合”这件事发生在哪一层，直接决定了这套仓库未来能回答什么问题。

常见的分层是：ODS 保留源系统原样的明细，DW 层建明细事实表，之上再建按日期、渠道等常用维度聚合的汇总表。汇总表查询快，代价是维度组合被锁死——只预聚合了“日期 + 渠道”，就回答不了“日期 + 商品类目”的问题，除非再加一张表。明细全留则存储和查询成本都高，尤其当明细事实表到了十亿行量级，任何一次全表扫描都是事故。

折中的做法是给明细设一个保留窗口，比如保留最近 13 个月明细，更早的只保留汇总。这个决定是不可逆的：一旦明细被清掉，就再也回答不了当时没想到的维度组合。所以窗口长度应该由业务方的“回溯提问频率”决定，而不是由存储成本决定——存储是可以加的，历史明细删掉就真没了。

4.3 指标固化与幂等加载

商务规则计算是转换阶段最容易被低估的部分。指标往往不是简单加减：毛利率要不要含运费、退货要不要冲减收入、跨期订单算哪个月的业绩，每一个定义都对应一段代码。把这些算好存进 DW，好处是口径统一、查询快;代价是口径一旦变化，历史数据要按新定义重算。

稳妥的做法是给每个指标配一份定义记录：指标编号、版本号、口径描述、生效起止时间。重算历史时按订单发生日期去匹配当时生效的那一版定义，而不是用今天的定义去算三年前的数据。否则每年口径调整一次，历史报表就变一次，“同比”这个指标直接失去意义。

加载环节的核心要求只有一个：幂等。同一批数据重跑一次，结果必须和跑一次完全相同。

-- 分区覆盖：重跑同一天不会产生重复数据 BEGIN; DELETE FROM dw_sales_daily WHERE stat_date = '${biz_date}'; INSERT INTO dw_sales_daily (stat_date, channel_id, order_cnt, gmv) SELECT '${biz_date}', channel_id, COUNT(DISTINCT order_no), SUM(amount) FROM dw_order_fact WHERE stat_date = '${biz_date}' GROUP BY channel_id; COMMIT;

先删后插放在同一个事务里，是为了避免“删完还没插进去就失败”留下一个空分区——空分区比没有分区更危险，因为下游会把它当成“当天确实没有数据”。数据量大时，这个模式要换成按分区覆盖写或者分区交换，但语义不变：写入的边界必须是明确的、可重复的，而不是追加式的。追加式写入配上重跑，就是经典的指标翻倍事故。

5. 排查：ETL 出错了，为什么定位不到具体那一行

5.1 抽取侧的两个坑

坑一：任务天天成功，业务说昨天的修改没进报表。现象是增量任务日志全是成功，但源表里改过的记录在下游还是旧值。原因基本只有一种——源表用覆盖式更新，UPDATE 语句里没刷新更新时间字段，这条记录的“更新时间”停在创建那天，水位线永远跨过它。解决分两步：先确认变更字段到底有几个、哪些场景下不刷新；短期用关键字段指纹比对（每天抽一批样本对哈希）兜底，长期推动源系统补时间戳或者提供变更日志。指纹比对很土，但它能在你不知情的时候告诉你“有东西在变而你没抽到”。

坑二：补跑一次，指标翻倍。现象是人工重跑某个批次之后，当天的销售额变成了两倍。原因是目标端只做追加，没有幂等键，重跑就等于重新插入一遍。解决是把写入语义从追加改成按业务主键或分区覆盖，重跑之前先清掉对应范围。这件事必须在设计阶段定下来，事后补的幂等逻辑往往漏掉几张表，而漏掉的那张表通常就是出报表的那张。

5.2 清洗与转换侧的两个坑

坑三：上线一周，退款订单全被过滤，销售额虚高。现象是报表数字比业务系统高出一截，排查发现金额小于等于 0 的记录全被当成错误数据拦掉了。原因是规则写的时候只想到“金额应该是正数”，没跟业务确认负数的含义（退货冲减）。解决是规则上线前必须做历史样本回放，并且由业务方书面确认；对含义不确定的取值，默认动作是“标记但不拦截”，宁可让它带着标记流下去，也不要在清洗层替业务做决定。

坑四：新增一个供应商编码之后，历史报表口径变了。现象是同一个供应商在报表里出现两行，一行是旧数据，一行是新数据。原因是映射表补录了，但历史事实表还挂在旧代理键上。解决是把映射表的变更做成事件，变更时自动触发受影响分区的重算；映射表本身也做生效区间，让“某个时间点之前这个编码映射到谁”这个问题有确定答案。

5.3 日志与告警：没有这三层日志，排查只能靠猜

坑五：任务失败，日志里只有一条 failed，不知道哪一步出的问题。现象是值班的人收到告警，登上去只能看到任务整体失败，重跑又失败，只能全链路加打印。原因是只记录了总体日志。执行过程、错误、总体这三类日志，记录的内容和用途完全不同：

这三层缺一不可。只有总体日志，你知道失败了但不知道哪一步;有了过程日志，你能看到“清洗步骤跑了 4 小时”，这就已经指向了某个规则或某张表的数据量异常;有了错误日志，你能看到具体是哪一列转换失败、样例行的原始值是什么，而不是面对一句“转换失败”。

告警邮件的内容比告警本身更重要。一封只写“ETL failed”的邮件，半夜收到的人什么也做不了;一封带上任务号、批次号、失败步骤、错误码、影响行数和重跑命令的邮件，才是一次有效通知。另外，错误日志必须落表或者落文件，不能只打到控制台——进程崩掉的时候，控制台跟着一起没了，那才是最需要日志的时刻。

6. 三种实现方式的取舍，以及怎么验证这套链路真的对

ETL 的实现路径基本就三条：全用图形化工具搭、全用 SQL 和脚本手写、工具加 SQL 混合。这三条路的差别不在“能不能做”，而在“变化来了谁去改”：

混合的关键是分工要写死：工具负责连接管理、调度编排、失败重试、日志采集这些“外壳”；SQL 负责真正的业务规则计算这些“内核”。分工不清的混合最糟糕——一部分清洗逻辑藏在工具组件里，一部分在 SQL 里，出了问题两边都要翻，而且新人根本不知道逻辑在哪。

验证这套链路对不对，最实用的手段是对数校验，而且要在三个位置上同时做：源端抽取完成后，比源表和 ODS 的行数与关键字段汇总值；清洗完成后，比对“进入数 = 入库数 + 拒绝数”，这个等式不成立说明有数据凭空消失；加载完成后，比 DW 和上游事实表的汇总值。这三道校验跑在每天调度里，出问题当场报警，比事后被人问“这个数为什么不对”强太多。

从那以后，我每接一条新链路，第一件事是先把拒绝表和三层日志建起来，再写抽取逻辑——顺序反了，后面每一次排查都是盲人摸象。希望帮到你。

本文还有配套的精品资源，点击获取