ETL设计落地：抽取、清洗、转换与幂等加载避坑指南

简介面向BI与数据仓库初学者、ETL开发人员及需要梳理数据集成流程的从业者这份《ETL设计详解数据抽取、清洗与转换》docx文档围绕抽取、清洗转换、加载三条主线展开帮助读者建立从业务系统到ODS、DW的整体设计思路。内容覆盖数据源调研问题、同构与异构数据库链接、文件数据导入、增量抽取判断以及不完整数据、错误数据、重复数据的过滤与修正策略并延伸到不一致数据转换、粒度转换、商务规则计算和加载方式选择、日志与告警等细节。资源包共1个docx文件约20KB篇幅轻便适合碎片化阅读也可作为项目设计前的自查清单。已有1538人学习读者可据此快速理解ETL各阶段的任务边界、常见问题与设计取舍为后续数据仓库实践提供参考。1. ETL 三段的时间账为什么清洗转换要吃掉 2/3 的工期刚接手数据仓库项目的人第一反应往往是先连数据库、先把抽取脚本跑通因为数据抽取看得见摸得着配个链接、写条查询、导一批数据当天就能出结果。真做过两轮的人会更谨慎ETL 里最耗时间的从来不是 E而是 T。数据抽取、数据清洗、数据转换这三段抽取基本是一次性投入清洗转换却是长期拉锯——规则要业务方逐条确认脏数据改一批又冒一批一个批次跑完发现口径不对第二天从头再来。按项目经验值算清洗转换通常占掉整个 ETL 工作量的 2/3而 ETL 本身又占掉整个 BI 项目 1/3 的工期。这份 ETL 设计文档的价值就在这儿它没有停在概念层而是把数据源普查、增量水位线、脏数据分类、隔离回退、日志告警这些容易含糊的环节拆成了可执行的清单。适合正在做数仓落地、被数据质量反复拖回去重跑的人。2. 抽取层设计九项数据源普查决定全量与增量的取舍抽取这一步最容易被跳过的是调研。很多人拿到需求直接开写 SQL写到一半发现源库没有更新时间字段、主键会复用、字段是 GBK 编码于是整个增量方案推倒重来。数据源普查不是走流程它直接决定后面用链接直取、用文件落地还是用接口推送。2.1 抽取前必须填完的数据源普查表把下面九项填完再动手能省掉后面至少两轮返工。这张表我一般会作为设计文档的第一页评审时逐项过。普查项具体要问清楚影响什么决策业务系统数量有几个源库是否有上下游依赖批次编排顺序、并行度DBMS 类型与版本同构还是异构驱动是否可用直连、外部表还是文件落地字符集源库与目标库编码是否一致是否需要转码乱码根因历史总量单表行数、总容量是否必须分区、是否分批抽日均增量每日新增与变更行数增量窗口、批次耗时预估时间戳字段有无 create_time / update_time是否可靠能否做增量抽取主键稳定性是否有业务主键是否会被复用去重与幂等策略手工补录是否存在人工导数、Excel 补录文件源通道、落地表设计非结构化数据附件、日志、文本字段是否单独走一条链路字符集这一项最容易被忽略。源库用 GBK、目标库用 UTF-8抽取阶段不做转换数据进了 ODS 就是一堆问号等到报表上发现已经过去一周回查成本极高。时间戳字段则直接决定增量可行性如果业务表只有创建时间没有更新时间记录被修改后时间戳不变增量就会漏数据这时候只能退化成全量比对或者加触发器。2.2 同构、异构、文件三类源的接法数据源按接入难度分三类处理方式差别很大。源类型常见接法适用场景主要风险与目标库同构数据库链接、外部表、直接跨库查询同一厂商、网络互通长事务拖慢源库异构数据库ODBC/JDBC 驱动桥接或导出文本再导入跨厂商、版本差异大驱动版本、类型映射错位文件源先落到 staging 表校验后再进 ODStxt/xls/csv、手工补录字段错位、编码不一致同构源最省事建个链接就能写查询直取但要注意别在业务高峰期跑大查询源库被拖垮的锅最后还是要 ETL 背。异构源如果没有稳定的驱动常见做法是先导出成文本文件再统一灌进落地表虽然多一步但可控性高得多。文件源的关键是落地表字段全部按字符串类型建先保证能装进去再在下一层做类型转换和校验这样格式错误不会在装载阶段就把批次打断。-- 文件源落地表所有字段先用字符串兜住避免导入阶段类型报错 CREATE TABLE stg_supplier_raw ( row_no BIGINT, -- 文件内行号便于回查原始文件 supplier_code VARCHAR(64), -- 供应商编码原始值不做任何处理 supplier_name VARCHAR(256), -- 供应商名称可能含全角字符和回车 region_name VARCHAR(128), -- 区域常见缺失字段 create_date VARCHAR(32), -- 日期先按字符串收后续统一校验 batch_id VARCHAR(32), -- 批次号重跑时按批次删除 load_time TIMESTAMP -- 入库时间 );这段 DDL 的核心思路是「先落地、后校验」。row_no是为了出问题时能定位到原始文件的第几行方便业务方核对所有业务字段统一用 VARCHAR是因为文本文件里经常混进全角数字、多余空格、回车符直接按 DATE 或 DECIMAL 建表会导致整批导入失败batch_id是后面做幂等重跑的基础同一批次重跑时先按这个字段删干净再插。2.3 增量抽取的水位线半开区间加回刷窗口数据量大的系统必须做增量。最常见的做法是用业务表的时间戳做增量标志每次抽取前先查 ODS 里该表的最大时间再去源库取大于这个时间的记录。这个思路本身没错但直接实现有两个坑边界重复和迟到数据。边界重复指的是用和双向闭合同一秒内的记录会被相邻两个批次各抽一次。迟到数据指的是源库事务提交延迟或者业务方补录历史单据时间戳落在已经抽过的区间里下一批再也抽不到。-- 抽取控制表每个源表一行记录水位线和回刷窗口 CREATE TABLE etl_ctrl_watermark ( src_system VARCHAR(64) NOT NULL, -- 源系统标识 src_table VARCHAR(128) NOT NULL, -- 源表名 ts_column VARCHAR(64) NOT NULL, -- 增量时间戳字段 last_ts TIMESTAMP NULL, -- 已成功抽取到的最大时间戳 overlap_hours INT DEFAULT 2, -- 回刷窗口单位小时 batch_id VARCHAR(32) NULL, -- 最近一次批次号 updated_at TIMESTAMP NULL, PRIMARY KEY (src_system, src_table) ); -- 增量抽取区间左开右闭并向前回刷 overlap_hours 小时 INSERT INTO ods_order (order_id, customer_id, amount, update_time, batch_id) SELECT order_id, customer_id, amount, update_time, :batch_id FROM src_order WHERE update_time ( SELECT last_ts - INTERVAL 2 HOUR -- 各库写法不同按实际语法替换 FROM etl_ctrl_watermark WHERE src_system CRM AND src_table order ) AND update_time :batch_end; -- 本批次统一截止时间不取库时间几个参数要说清楚。overlap_hours是回刷窗口取 2 小时还是 24 小时取决于源库事务提交的延迟程度和补录频率业务补录多的系统直接给到 24 小时。batch_end必须是批次启动时统一取的一个时间点不能每张表各自取当前时间否则同一批次内不同表的截止时间不一致对账时永远对不平。控制表的last_ts要在批次成功提交之后再更新失败了不回写下次重跑还能覆盖到。提示回刷窗口不是越大越好。窗口开得太大每批重复抽取的数据量成倍上升去重成本也跟着涨。先用一周的迟到数据分析迟到分布再定窗口。2.4 全量还是增量一张判断表不是所有表都值得做增量小维表老老实实全量覆盖反而更省心。判断条件结论行数小于十万且变化不频繁全量覆盖每天清空重灌有可靠更新时间字段、增量占比低于 5%增量追加加回刷无更新时间字段但行数可控全量比对用主键做差异更新行数上亿且无时间戳分区抽取加主键区间切分全量覆盖的写法简单DELETE加INSERT一个事务搞定缺点是源库压力大增量追加效率高但对时间戳的依赖很强。判断标准不是技术先进与否而是这张表的数据特征支不支持。3. 清洗层落地三类脏数据的判定口径与隔离回退清洗是整个 ETL 里最磨人的一段。规则本身不复杂难的是「什么算脏」这件事需要业务方点头而且今天确认的规则下个月业务口径一变又要改。所以清洗层的设计目标不只是过滤掉坏数据还要留下证据、留出回退通道。3.1 三类问题的判定口径要写成可执行 SQL文档里把脏数据分成不完整、错误、重复三类这个分类是对的但落到实现必须转成可执行的判定条件否则每个人理解不一样。-- 不完整数据关键字段为空或空串 SELECT * FROM stg_customer WHERE supplier_name IS NULL OR TRIM(supplier_name) OR region_name IS NULL OR TRIM(region_name) ; -- 错误数据全角字符、尾部回车、日期越界 SELECT * FROM stg_customer WHERE supplier_code REGEXP [---] -- 全角数字或字母 OR supplier_name LIKE % || CHR(13) || % -- 含回车符 OR create_date NOT REGEXP ^[0-9]{4}-[0-9]{2}-[0-9]{2}$ OR TO_DATE(create_date, YYYY-MM-DD) CURRENT_DATE 1; -- 日期越界 -- 重复数据业务主键重复 SELECT supplier_code, COUNT(*) AS cnt FROM stg_customer GROUP BY supplier_code HAVING COUNT(*) 1;第一条查的是缺失注意空串和 NULL 要一起判很多业务系统用空串代替 NULL只判IS NULL会漏。第二条查格式错误全角字符用正则匹配字符区间回车符用CHR(13)日期越界要先保证格式合法再转日期否则转换函数自己就会抛异常。第三条查重复重点是按业务主键而不是物理主键分组——维表里同一个供应商被录入两次物理主键不同但业务主键相同这种才是真正要清掉的。正则和日期函数的写法各库差异很大REGEXP、CHR、TO_DATE这些按实际数据库替换思路不变。3.2 过滤不是删除隔离表加回退通道新手最容易犯的错是查出来直接DELETE。数据删了业务方问「上个月华南区的供应商怎么少了三十家」你连原始行都拿不出来。正确做法是建一张隔离表把不符合规则的行原样搬进去附上规则编号、批次号和原始行定位信息。字段含义batch_id批次号与主链路一致rule_code命中的清洗规则编号src_row_id源表主键或落地表行号raw_payload原始行内容整行保留handle_status待确认 / 已修正 / 已废弃handle_time处理时间处理流程是主链路只灌入通过校验的行隔离表存下被拦下的行每天按规则汇总一份清单发给业务方业务方确认哪些是误杀、哪些需要回源修正。修正完成后重新抽取handle_status改成已修正。这样一来任何时候都能回答「这批数据为什么少了」「少了哪些」也能在规则改错时快速回滚。注意隔离表本身也要有保留周期。建议至少保留最近三个月的原始行超过周期的归档到冷表不要直接清空。3.3 格式类错误全角、回车符、日期越界的处理套路三类错误里格式类最典型处理方式也不一样。全角字符和前后不可见字符理论上可以在 ETL 里直接转换但转换完之后就和源系统不一致了后续对账会出问题。常见做法是能明确映射的全角数字转半角在落地层转换并记录转换次数不能确定语义的全角空格、特殊符号一律拦进隔离表交给业务方在源系统修正。-- 全角数字转半角仅在落地层做转换前先记录原始值 UPDATE stg_customer SET supplier_code TRANSLATE(supplier_code, , 0123456789), rule_code R012 -- 标记本条命中过格式转换规则 WHERE supplier_code REGEXP [-]; -- 去掉首尾回车、换行、制表符 UPDATE stg_customer SET supplier_name TRIM(BOTH CHR(13) || CHR(10) || CHR(9) FROM supplier_name) WHERE supplier_name LIKE % || CHR(13) || % OR supplier_name LIKE % || CHR(10) || %;日期越界是另一类危害更大。日期格式不正确或者超出合理范围比如 1900-01-01、2099-12-31在装载阶段做类型转换时直接报错整批回滚。处理办法是落地层全部按字符串收进 ODS 之前单独做一次合法性校验不合法的进隔离表合法但越界的标记异常由业务方确认是录入错误还是特殊业务含义。这里有一条血泪经验不要在一条 SQL 里同时做格式校验和类型转换。转换函数遇到非法字符会直接中断整个语句前面校验出来的结果一行都留不下。拆成两步先筛后转出问题时至少知道是哪一批哪几行。3.4 清洗规则台账每条规则都要有编号和签字清洗规则会一直变没有台账根本管不住。台账不要写成散文就一张表每条规则一行。规则编号规则描述作用表过滤条件处理方式确认人生效批次R001供应商名称为空stg_suppliername IS NULL隔离待补业务A20240101R012全角数字字符stg_supplierREGEXP 全角区间转换并标记业务A20240105R020日期超出当前日期stg_orderdate CURRENT_DATE隔离待确认业务B20240108台账的作用有三个规则变更时有据可查过滤数据有编号可追溯业务方换人时交接不用靠嘴说。每条规则上线前让业务方在台账上确认一次比事后争论「这条数据该不该过滤」省事得多。4. 转换与加载从 ODS 到 DW 的编码统一、粒度聚合与幂等写入抽取和清洗解决的是「数据对不对」转换和加载解决的是「数据能不能用」。这一段的设计重点在于分层职责要清晰编码、粒度、指标计算各归各层混在一起后期维护会非常痛苦。4.1 ODS 与 DW 的分工清洗在前转换在后常见做法是从业务系统到 ODS 做清洗把脏数据和不完整数据挡在外面从 ODS 到 DW 做转换做业务规则计算和聚合。这个划分的理由很实在。ODS 层保持与源系统一致的粒度一行源数据对应一行 ODS 数据好处是对账简单——源表多少行、ODS 多少行差额就是被清洗规则拦下的行一目了然。如果 ODS 阶段就做聚合源表和 ODS 行数对不上出问题时根本没法定位是清洗拦多了还是聚合算错了。DW 层才做聚合和指标计算。到了这一层数据已经是干净的聚合逻辑出错只需要重跑 DW 段不用回源。分层清晰之后重跑成本大幅下降这是后期运维能不能扛住的关键。4.2 编码不一致映射表怎么做才不返工同一个供应商在不同业务系统里有不同编码这是转换层最典型的活儿。做法是建一张编码映射表而不是在 SQL 里写一堆CASE WHEN。-- 编码映射表带生效时间和失效时间支持历史编码查询 CREATE TABLE dim_code_mapping ( domain VARCHAR(32) NOT NULL, -- 编码域如 supplier src_system VARCHAR(64) NOT NULL, -- 来源系统 src_code VARCHAR(64) NOT NULL, -- 源系统编码 std_code VARCHAR(64) NOT NULL, -- 统一编码 valid_from DATE NOT NULL, -- 生效日期 valid_to DATE NULL, -- 失效日期NULL 表示当前有效 PRIMARY KEY (domain, src_system, src_code, valid_from) ); -- 转换时按业务发生日期匹配当时有效的编码 SELECT o.order_id, COALESCE(m.std_code, o.supplier_code) AS supplier_code FROM ods_order o LEFT JOIN dim_code_mapping m ON m.domain supplier AND m.src_system o.src_system AND m.src_code o.supplier_code AND o.order_date m.valid_from AND (m.valid_to IS NULL OR o.order_date m.valid_to);关键在于valid_from和valid_to这两个字段。如果映射表只存「当前映射关系」一旦供应商编码变更历史订单的编码会被一起改写去年的报表今年重跑结果就变了。加上生效时间之后历史数据永远按当时的映射关系计算重跑结果稳定。COALESCE是兜底映射表里没配的编码原样保留同时通过日志记下来后续补配。不要用INNER JOIN那样会直接把没配映射的数据丢掉属于典型的静默丢数。4.3 商务指标算在 ETL 里还是留给 BI这个问题的判断标准不是技术是口径稳定性。判断条件建议位置口径固定、多张报表复用ETL 内算好落到 DW 宽表口径经常调整、只用于单张报表留给 BI 层用视图或语义层实现需要跨多个源系统关联才能算ETL 内算BI 层拿不到跨源明细计算量大、明细数据量大ETL 内预聚合避免每次查询扫全表口径固定且被多张报表复用的指标放在 ETL 里算一次所有下游共享同一份结果能避免「同一个指标三张报表三个数」的经典问题。口径还在调整期的指标放 ETL 里会导致每次改口径都要重跑历史数据放在 BI 层反而灵活。判断依据是「这个指标的口径半年内会不会变」会变就别急着固化。4.4 加载策略全量覆盖、增量追加与拉链加载方式要和表类型匹配用错了后面全是坑。加载方式实现要点适用表全量覆盖事务内先删后插或 truncate 后灌小维表、配置表增量追加按批次号追加配合水位线事实表、流水表缓慢变化维拉链比对变化字段旧记录关链新记录开链需要保留历史状态的维表最需要强调的是幂等。任何一个加载脚本同一个批次跑两遍结果必须一样否则出问题时不敢重跑只能手工修数据。-- 幂等加载同一批次号先删后插放在同一事务里 BEGIN; DELETE FROM dw_order_fact WHERE batch_id :batch_id; INSERT INTO dw_order_fact (order_id, customer_id, amount, order_date, batch_id) SELECT order_id, customer_id, amount, order_date, :batch_id FROM ods_order WHERE batch_id :batch_id; COMMIT;batch_id是整个幂等机制的核心必须从抽取阶段一路带到加载阶段不能中途重新生成。DELETE和INSERT放同一事务中途失败整体回滚不会出现删了没插的中间状态。对于拉链维表幂等更麻烦一些需要先按批次号删除本批次新增的链再把被关链的旧记录恢复成开链状态这部分逻辑建议单独封装成一个存储过程不要写在调度脚本里。5. 避坑与排查日志三件套加四条现场翻车记录ETL 出问题不可怕可怕的是出了问题不知道从哪儿查。日志和告警是排查的地基剩下的是经验。5.1 日志三件套和告警发送的最小实现日志分三类缺一不可。执行过程日志记每步的起止时间和影响行数错误日志记出错模块和错误信息总体日志只记批次开始、结束和成功与否。这三类日志分开存表不要混在一张表里否则查询时全是噪音。-- 执行过程日志每步一行用于定位慢步骤和行数异常 CREATE TABLE etl_run_step_log ( batch_id VARCHAR(32) NOT NULL, -- 批次号 step_name VARCHAR(128) NOT NULL, -- 步骤名如 ext_order start_time TIMESTAMP NOT NULL, end_time TIMESTAMP NULL, affected_rows BIGINT DEFAULT 0, -- 本步影响行数 status VARCHAR(16) NOT NULL -- RUNNING / SUCCESS / FAILED ); -- 错误日志只记失败附错误堆栈摘要 CREATE TABLE etl_error_log ( batch_id VARCHAR(32) NOT NULL, step_name VARCHAR(128) NOT NULL, err_time TIMESTAMP NOT NULL, err_code VARCHAR(64), err_msg VARCHAR(2000) -- 截断到 2000 字符避免超长 );affected_rows这个字段看着不起眼实际排查时最有用。正常情况下每步影响行数在一个区间内波动某天突然从五万掉到三百基本可以断定上游出问题了。告警走邮件或者消息推送都行内容必须包含批次号、失败步骤、错误摘要三样缺一样管理员就得自己登库查。告警要设置收敛同一个错误连续触发时合并成一条否则一晚上几百封邮件最后没人看。5.2 现象批次显示成功数据少了三千行现象很隐蔽调度平台显示批次成功日志也没有错误但对账时发现 ODS 比源表少了三千行。原因是时间戳边界写成了和双向闭合相邻两个批次在边界秒上重叠而 ODS 表上又有唯一约束重复行被INSERT IGNORE静默丢弃日志里不会报错。另一种情况是源库事务隔离级别较低抽取时读到了未提交数据后续回滚导致实际少了行。解决办法是把区间改成左开右闭同时给每张表加行数对账源表按相同条件COUNT(*)和 ODS 实际插入行数比对差额超过阈值直接告警。对账不要只做总量按天分组做能快速定位是哪一天出的问题。5.3 现象一条全角数字让整批转换中断现象是批次跑到转换步骤直接失败错误日志里是类型转换异常但看不出是哪一行。原因是落地表按字符串收数据没问题但转换步骤里直接写了CAST(supplier_code AS DECIMAL)遇到全角数字或者尾部回车符转换函数抛异常整个语句回滚一行都没写进去。解决办法是把类型转换和格式校验拆开先跑校验 SQL 把不合法的行挑进隔离表主链路只处理校验通过的行。如果数据库支持逐行容错转换部分数据库有TRY_CAST之类的函数可以用它兜底转换失败返回 NULL 并记录而不是中断整批。没有这个函数就老老实实两步走。5.4 现象重跑一次指标翻倍现象是某个批次失败后手工重跑跑完发现当天的销售额统计翻了一倍。原因很明确加载用的是纯追加模式重跑时没有按批次清理同一批数据进了两次。这类问题在测试环境不容易发现因为测试时很少重跑上线后一出错就暴露。解决办法是所有加载脚本强制带batch_id执行前先按批次删除。更进一步的做法是在事实表上建(batch_id, 业务主键)的唯一索引重复写入直接报错而不是静默成功宁可失败也不要产生重复数据。删和插必须在一个事务里避免出现删完插入失败的中间状态。5.5 现象脏数据被过滤了业务方坚持说数据没错现象是清洗规则拦下了一批供应商记录业务方核对后认为这些数据是有效的要求全部补回去。原因是规则上线前只做了技术验证没让业务方逐条确认。比如「区域信息缺失」这条规则技术上看确实是空值但业务上有一批供应商本来就归属总部直管区域为空是正常状态被误杀了。数据已经拦进隔离表还好如果当时是直接删的恢复起来就麻烦了。解决办法是规则台账加签字确认每条规则上线前由业务方确认过滤条件上线后头一周每天发过滤清单核对。隔离表必须保留原始行任何时候都能回捞。规则变更要有版本号改规则的同时记录影响范围方便评估要不要回刷历史数据。6. 上线前的三道自检行数对账、重跑一致性与时间边界ETL 脚本能跑通只是及格线敢不敢让它无人值守跑一整年取决于上线前有没有做够自检。我一般会强制走三道少一道都不签字。第一道是行数对账而且要分层对。源表按抽取条件COUNT(*)ODS 按批次COUNT(*)DW 按业务主键去重后COUNT(DISTINCT)三个数之间的差额必须能逐条解释——被清洗规则拦下的、被去重合并的、被业务规则过滤的每一类都要有明确数字。对账 SQL 建议固化成脚本每天批次结束后自动跑差额超阈值直接告警而不是等月底出报表才发现。-- 分层对账源表、ODS、DW 三层行数一次查出差额留空说明链路正常 SELECT src AS layer, COUNT(*) AS row_cnt FROM src_order WHERE update_time :low AND update_time :high UNION ALL SELECT ods, COUNT(*) FROM ods_order WHERE batch_id :batch_id UNION ALL SELECT dw, COUNT(DISTINCT order_id) FROM dw_order_fact WHERE batch_id :batch_id;第二道是重跑一致性。挑三个批次每个跑两遍比对两次结果的校验和——按业务主键排序后取关键字段拼接算一个摘要值。两次一致才算幂等过关。这一步能揪出绝大部分追加模式没删干净、拉链逻辑写错、指标重复累加的问题比看代码靠谱得多。第三道是时间边界。跨月、跨年、闰日、夏令时切换日这几个日子是增量逻辑的重灾区。构造边界数据跑一遍时间戳正好落在批次截止时刻的记录、补录到上个月的历史单据、2 月 29 日的日期值。我吃过一次亏跨年那天批次的时间窗口算错了一天导致 12 月 31 日的订单全部落在下一个批次报表上的年度数字差了整整一天的量事后回补花了半天。还有一个小技巧把每张表的实际耗时记进过程日志跑一个月之后画个趋势哪张表在变慢一目了然。增量抽取变慢通常意味着源库索引失效或者回刷窗口开得太大提前发现比等它跑超时强。从那以后我每次上线新批次都会先把这三道自检跑一遍哪怕需求再急也不跳过因为返工的成本永远比自检高。希望帮到你。本文还有配套的精品资源点击获取