简介这份文档面向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 customersrc_link; -- 增量抽取先取 ODS 里已有的最大时间戳 INSERT INTO ods.customer SELECT * FROM customersrc_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, encodingutf-8-sig) as f: reader csv.reader(f) header next(reader) bad_rows [] for i, row in enumerate(reader, start2): 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, threshold0.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 项目都强制先跑一遍数据源调研表、再定清洗规则版本、最后挂对账闸门三步缺一步不上线。这套顺序看着慢实际比反复补数快得多。希望帮到你。本文还有配套的精品资源点击获取