简介：这份文档面向BI项目开发者、数据仓库工程师及ETL初学者，系统讲解数据抽取、清洗转换与加载的完整设计思路，帮助读者理清ETL在BI项目中的定位与实施要点。内容围绕数据抽取阶段的数据源调研问题与不同数据源处理方法、增量更新策略，数据清洗中不完整数据、错误数据与重复数据的分类处置，以及数据转换中的编码统一、粒度聚合与商务规则计算展开，并补充ETL日志分类与警告发送机制。资源包共1个docx文件，约20KB，篇幅精炼，适合作为ETL设计入门与方案梳理的参考材料。目前已有1536人学习下载，读者可从中获得从抽取到加载各环节的落地方法、常见问题处理思路与日志监控设计要点，为实际项目中的ETL开发提供可借鉴的框架。

1. 从一份 2018 年的 ETL 设计文档说起：它到底能帮你解决什么

上周有个做数仓的朋友甩给我一份文档，标题是《ETL设计详解（数据抽取、清洗与转换）》，说他们团队新接了一个 BI 项目，ODS 层刚建好，结果抽取脚本跑了两天，清洗规则改了四版，加载到 DW 的表还是对不上账。我翻完这份文档，发现它讲的不是某个工具的按钮怎么点，而是把 ETL 拆成抽取、清洗转换、加载三段之后，每一段该先想清楚什么、哪些坑是行业里反复踩的。这份资源适合两类人：一是刚接手 BI 项目、需要快速搭起 ETL 骨架的工程师；二是已经写过不少 SQL 脚本、但总觉得“跑通了却不敢上线”的熟手。它不教你写某一行代码，它教你在动手之前把数据源盘清楚、把清洗规则定死、把日志和告警留好后悔药。下面我按自己复现这套思路的顺序，把能抄作业的部分拆开讲。

2. 数据抽取：先盘数据源，再决定用链接还是落文件

2.1 抽取前必须回答的四个问题

文档里列了四个调研问题，我把它当成抽取设计的前置检查表：数据来自几个业务系统、各系统跑什么 DBMS、有没有手工数据、有没有非结构化数据。这四个问题决定了后面用数据库链接直连、走 ODBC、还是让业务方先导成文件。我一般会再加一条：每个源表有没有可靠的时间戳字段。没有时间戳，增量抽取就得靠全量比对，数据量一大就是灾难。调研阶段把这些写成一张表，后面每写一个抽取任务就对照一次，能省掉大量返工。

2.2 同构数据源：数据库链接直抽

源库和 DW 同属一类 DBMS 时，最省事的做法是在 DW 服务器上建数据库链接，直接写 SELECT 抽到 ODS。以常见的关系库为例，建链接和抽取的骨架大致是这样：

-- 在 DW 侧建立到业务库的链接（语法随 DBMS 不同，这里示意） CREATE DATABASE LINK src_link CONNECT TO src_user IDENTIFIED BY 'password' USING 'src_service_name'; -- 全量抽取到 ODS，先清后插，适合小表 INSERT INTO ods.customer SELECT * FROM customer@src_link; -- 增量抽取：先取 ODS 里已有的最大时间戳 INSERT INTO ods.customer SELECT * FROM customer@src_link WHERE update_time > (SELECT NVL(MAX(update_time), DATE '1970-01-01') FROM ods.customer);

这段代码的关键在增量那一段：MAX(update_time)从 ODS 侧取，而不是从源库取，避免源库时钟和 DW 时钟不一致导致漏数。参数上要注意update_time必须是业务系统真实写入的时间，不能是数据库的SYSDATE，否则批量补录的数据会被当成新数据重复抽。如果源表没有索引支撑这个时间条件，先让 DBA 加索引，否则每次抽取都是全表扫。

2.3 异构数据源与文件源：ODBC、导出文件、接口三条路

源库和 DW 不是同一类 DBMS 时，优先走 ODBC 建链接，SQL 写法基本不变。建不了链接的情况，文档给了两条路：工具导出成 txt/xls 再导入 ODS，或者走程序接口。我一般会按数据量选：日增量在十万行以内、字段稳定的，让业务方按固定格式导出文件，用 SSIS 的平面数据源组件或等价工具灌进 ODS；数据量大且实时性要求高的，走接口。文件源最容易翻车的是编码和分隔符，导入前先用脚本验一遍：

import csv # 校验文件源：编码、列数、空行 with open('src_export.csv', encoding='utf-8-sig') as f: reader = csv.reader(f) header = next(reader) bad_rows = [] for i, row in enumerate(reader, start=2): if len(row) != len(header): bad_rows.append((i, len(row))) print('列数异常行:', bad_rows[:10])

utf-8-sig是为了吃掉 Windows 导出文件开头的 BOM，不然第一列列名会带不可见字符，后面映射全错。列数校验放在导入前，比导入后查 ODS 快得多。这一步看着琐碎，但文件源出问题十有八九出在编码和列数上。

2.4 增量更新的判断逻辑

文档强调增量抽取靠业务系统记录的业务发生时间。实操里我会把增量分成两类：有可靠时间戳的走时间水位，没有的走主键比对或全量覆盖。时间水位要落一张控制表，记录每个源表上次抽取的最大时间，而不是每次去 ODS 里查 MAX，这样抽取任务之间不互相依赖。控制表结构简单到三列就够：表名、水位时间、更新时间。每次抽取前读水位、抽取后写水位，失败时水位不推进，重跑不会丢数。

3. 清洗与转换：把脏数据分类，规则要能回滚

3.1 三类脏数据的处理顺序

文档把不符合要求的数据分成不完整、错误、重复三类，这个分类我一直在用，因为它直接对应三种处理动作：补全、修正、去重。顺序上先处理错误数据，再处理不完整数据，最后去重。原因是错误数据里的日期越界、全角字符会让后续规则误判，先清掉噪音，补全和去重的判断才准。每一类都要留过滤结果，写到独立的表或文件里，交给业务方确认，不能自己拍板删数据。

3.2 用 SQL 把错误数据挑出来

全角字符、前后空白、日期越界这几类错误，用 SQL 定位最直接：

-- 找出含全角数字的数值字段 SELECT id, amount FROM ods.payment WHERE REGEXP_LIKE(amount, '[０-９]'); -- 找出前后带空白字符的字符串 SELECT id, customer_name FROM ods.customer WHERE customer_name <> TRIM(customer_name); -- 找出日期越界或格式错误的记录 SELECT id, order_date FROM ods.orders WHERE NOT REGEXP_LIKE(order_date, '^\d{4}-\d{2}-\d{2}$') OR TO_DATE(order_date, 'YYYY-MM-DD') > SYSDATE;

第一段用正则匹配全角数字区间，不同 DBMS 的正则函数名不一样，逻辑一致。第二段用TRIM前后比对，能同时抓出空格、制表符和回车。第三段先验格式再验范围，两步都过才算合法。查出来的结果按文档建议导出给业务主管部门限期修正，修正后再抽，不要直接在 ETL 里做静默替换，否则数据对不上账时根本查不到源头。

3.3 转换的三件事：编码统一、粒度聚合、业务指标

转换部分文档讲了不一致数据转换、粒度转换、商务规则计算。编码统一是整合多个业务系统的第一步，比如供应商在两个系统里编码不同，抽取后要映射成统一编码，映射表单独维护，不要写死在 SQL 里。粒度转换是把明细聚合到分析粒度，聚合口径必须和业务方书面确认，我见过按天聚合和按自然日聚合混用导致报表差一天的翻车。商务规则计算尽量前移到 ETL 里算好落表，别留给报表工具现算，否则每次查询都重复计算，口径还容易漂。

3.4 清洗规则要能回滚

清洗规则一多，最怕改错一条把历史数据洗坏。我的习惯是每条规则对应一个版本号，规则表里记录规则内容、生效时间、影响行数。跑完一批清洗，先看影响行数是否在预期区间，超出阈值就停下来人工确认。过滤掉的数据统一进一张清洗异常表，字段包括源表名、主键、异常类型、规则版本、处理时间。这张表既是给业务方确认的依据，也是将来验证数据时的黑匣子。

4. 加载与日志告警：让 ETL 出错时有人知道

4.1 加载方式的选择

文档说清洗完直接写入 DW。实操里加载分全量覆盖和增量追加两种：维表小、变化不频繁的走全量覆盖，先 TRUNCATE 再 INSERT，简单可靠；事实表走增量追加，按分区写入。加载前先做一次行数对账，源侧抽了多少、清洗过滤多少、加载多少，三个数对不上就中止。对账 SQL 不复杂，但能拦住大部分“跑成功但数据不对”的情况。

4.2 三类日志各记什么

文档把日志分成执行过程日志、错误日志、总体日志。我一般落三张表：过程日志记每一步的起止时间、影响行数；错误日志记出错模块、错误码、错误信息；总体日志记整批的开始结束时间和成功标志。过程日志的粒度到步骤就够，别细到每行，否则日志表比业务表还大。错误日志要带批次号，方便和总体日志关联。

4.3 告警发送的落地方式

出错告警最常用的是邮件，文档也是这么建议的。落地时注意两点：一是告警要带批次号和错误摘要，管理员不用登服务器就能判断严重程度；二是设告警抑制，同一批次连续失败不要每五分钟发一封，按批次发一次即可。告警渠道可以再加一个即时消息机器人，邮件容易被淹没。告警内容里附上错误日志表的查询条件，比附一整段堆栈更实用。

5. 避坑排查：五条血泪经验

5.1 增量抽取漏数

现象：报表某天数据突然少一截，重跑抽取又正常。原因：源库时间戳字段在批量补录时被写成未来时间，水位推进过头，后续正常数据被跳过。解决：水位推进前校验时间戳不超过当前时间，超出的记录单独进异常表人工确认，水位只推进到合法最大值。

5.2 全角字符导致数值计算报错

现象：金额汇总时报类型转换错误。原因：源系统录入时混入全角数字，抽取时没清洗，加载到数值字段直接失败。解决：抽取到 ODS 后先跑一遍字符校验，全角转半角或标记异常，转换层再做类型转换，别等到加载层才暴露。

5.3 清洗规则误杀有效数据

现象：某区域客户数比业务系统少了两成。原因：清洗规则把区域信息为空的记录整条过滤，但这类记录里有一部分是线上渠道客户，区域本就允许为空。解决：过滤规则按字段而非整条记录判断，允许为空的字段单独列白名单，规则上线前用历史数据回放验证影响行数。

5.4 日志表膨胀拖慢 ETL

现象：ETL 越跑越慢，排查发现日志表几千万行。原因：过程日志按行记录，没做清理。解决：过程日志只记步骤级，按批次保留最近三十天，历史日志归档到冷表，查询走批次号索引。

5.5 告警邮件被当成垃圾邮件

现象：ETL 失败两天没人处理。原因：告警邮件高频发送，被邮件系统拦截。解决：按批次发送、控制频率，同时加即时消息通道，告警内容带批次号和错误摘要，管理员一眼能判断要不要立即处理。

6. 进阶技巧：用对账脚本给 ETL 加一道验证闸门

这套设计文档最值钱的地方不是某段 SQL，而是它把 ETL 当成一个需要持续验证的工程。我在复现时加了一道文档没细说但很实用的闸门：每批加载完成后自动跑对账脚本，三个数对不上就阻断下游任务。对账逻辑不复杂，关键是口径固定：

# 批次对账：抽取数、清洗后数、加载数三方比对 def reconcile(batch_id, src_count, clean_count, load_count, threshold=0.01): diff = abs(clean_count - load_count) / max(clean_count, 1) if diff > threshold: raise RuntimeError( f'批次 {batch_id} 加载数偏差 {diff:.2%}，' f'抽取 {src_count}，清洗后 {clean_count}，加载 {load_count}' ) # 抽取到清洗的过滤比例单独记录，供业务方复核 filter_rate = (src_count - clean_count) / max(src_count, 1) print(f'批次 {batch_id} 过滤比例 {filter_rate:.2%}')

threshold默认百分之一，事实表可以收紧到千分之一，维表放宽到百分之五。过滤比例不设硬阈值，但每批打印出来，连续几天偏高就说明源系统数据质量在恶化，提前介入比事后补数便宜得多。这个脚本挂在加载任务之后、下游报表任务之前，失败就阻断，不给脏数据流到报表的机会。

从那以后我每次接新的 ETL 项目，都强制先跑一遍数据源调研表、再定清洗规则版本、最后挂对账闸门，三步缺一步不上线。这套顺序看着慢，实际比反复补数快得多。希望帮到你。

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