企业官网建设流程全解析

简介：这份文档面向BI项目开发者、数据仓库工程师及ETL初学者，系统讲解ETL设计中数据抽取、清洗转换与加载三大环节的完整思路，帮助读者理清从业务系统到ODS再到DW的数据流转路径。内容涵盖数据源调研要点、同构与异构数据库的抽取方式、增量更新策略，以及不完整数据、错误数据、重复数据三类清洗规则和数据转换中的编码统一、粒度聚合与商务规则计算，并延伸至ETL日志分类与警告发送机制。资源包共1个docx文件，约20KB，以文字讲解为主，结构按抽取、清洗、加载顺序展开，便于按模块查阅。目前已有1535人学习下载，适合需要快速建立ETL设计框架、对照实际项目梳理流程的技术人员参考。

1. 从一次数据对不上账说起：ETL 方案到底要交付什么

业务方拿着报表问「为什么这个月销售额和财务系统差了三万」，你打开数据库一看，源系统里客户名称带全角空格、订单日期有2018/13/45这种越界值、同一个供应商在结算系统和 CRM 里挂着两个编码。这类问题不是 SQL 写得好不好，而是整条数据链路没有设计。ETL 设计详解（数据抽取、清洗与转换）要解决的就是这件事：把分散在多个业务系统、文件、手工表里的数据，按一套可复现的规则抽到 ODS、清洗转换后加载进 DW，并且全程留痕、出错能查。这套方案适合正在做 BI 课设、数据仓库毕设的学生，也适合要交付一份数据集成方案初稿的从业者。行业里通常的说法是 ETL 会吃掉整个 BI 项目三分之一的时间，而其中清洗转换又占掉这三分之一里的三分之二，先把这部分结构立住，后面才不至于天天救火。

2. 需求调研与总体架构：三库分层和四个必须先答的问题

2.1 先答清楚四个问题再动手画架构

数据抽取不是打开工具连上库就能干的事，调研阶段没做透，后面全是返工。我一般会强制自己先回答四个问题，答不上来就不进入设计：数据来自几个业务系统、各系统跑的是什么 DBMS、有没有手工数据且量级多大、有没有非结构化数据（合同扫描件、日志文本之类）。这四个答案直接决定抽取方式的选择——同构库可以建数据库链接直接写 Select，异构库走 ODBC 或者导出文件再导入，文件类数据源要么培训业务人员用数据库工具先落到指定库，要么用 ETL 工具的平面数据源组件直接读进 ODS。

架构上按 ODS、DW 两层来分，中间夹一层清洗转换。业务系统到 ODS 这一段做清洗，把脏数据和不完整数据挡在外面；ODS 到 DW 这一段做转换，完成编码统一、粒度聚合和业务指标计算。这个分层不是形式主义，它把「数据对不对」和「口径对不对」拆成两个可以分别验收的阶段，出问题时能快速定位是抽取环节漏了还是转换规则写错了。

2.2 三层结构的职责边界

职责边界划清楚之后，一个常见误区是把转换逻辑提前塞进抽取 SQL 里，看起来省了一步，实际上 ODS 层就失去了「原始快照」的价值，后面业务方说数据不对，你连对照的底稿都没有。通常的做法是 ODS 尽量贴近源系统结构，只做清洗不做业务加工。

2.3 增量抽取的时间戳设计

数据量大的系统必须做增量，全量抽一次几千万行，跑一次半天，谁也受不了。增量标志一般用业务系统的记录更新时间，抽取前先查 ODS 里该表的最大时间，再去源系统取大于这个时间的记录：

-- 增量抽取：先取 ODS 侧水位线，再拉源系统增量 SELECT MAX(etl_update_time) INTO @last_watermark FROM ods.order_main; INSERT INTO ods.order_main SELECT id, customer_id, supplier_code, order_date, amount, update_time AS etl_update_time FROM src.order_main WHERE update_time > @last_watermark AND update_time <= NOW(); -- 上界封口，避免抽取过程中新写入的数据被漏掉或重复

这里有两个参数要盯住：水位线字段必须是业务系统真实维护的更新时间，不能拿创建时间凑数，否则改过的单据永远抽不到；上界一定要封口，不然抽取执行期间源库还在写，同一批数据可能被下一次增量重复拉走。如果业务系统只有部分表有时间戳，剩下的表要么走全量覆盖，要么在 ODS 侧做全表比对生成变更集，前者简单但慢，后者快但要多写一套比对逻辑，课设规模下建议前者，生产规模下再上后者。

3. 数据清洗模块：三类脏数据的识别与回退机制

3.1 不完整、错误、重复三类问题的处理路径

清洗的任务是把不符合要求的数据挡在 DW 外面，但挡的方式要分情况。不完整的数据——供应商名称缺失、分公司名称空、客户区域信息没有、主表和明细表匹配不上——这类不能直接丢，要按缺失字段分别导出成 Excel 交给业务主管部门，限期补全后再抽。错误的数据来源通常是业务系统录入时没做校验，全角数字、字符串尾部带回车、日期格式不对、日期越界，前两类只能写 SQL 找出来让业务方在源系统改，后两类会直接导致 ETL 跑挂，必须提前挑出来。

重复数据在维表里最常见，处理方式是把重复记录的所有字段导出来让业务方确认保留哪条，而不是自己拍脑袋去重。这里有个血泪经验：清洗规则每一条都要业务方书面确认，尤其是「过滤掉」这个动作，一旦把有用数据误杀，后面指标对不上账，责任全在 ETL 这边。

3.2 用 SQL 把三类问题捞出来

-- 1) 不完整数据：关键字段为空 SELECT id, supplier_name, region_code FROM ods.supplier_dim WHERE supplier_name IS NULL OR TRIM(supplier_name) = '' OR region_code IS NULL; -- 2) 错误数据：全角字符、尾部空白、日期越界 SELECT id, amount_raw, order_date_raw FROM ods.order_main WHERE amount_raw REGEXP '[０-９]' -- 全角数字 OR amount_raw <> TRIM(amount_raw) -- 首尾空白/回车 OR STR_TO_DATE(order_date_raw, '%Y-%m-%d') IS NULL OR YEAR(STR_TO_DATE(order_date_raw, '%Y-%m-%d')) NOT BETWEEN 2000 AND 2100; -- 3) 重复数据：维表按业务主键分组 SELECT supplier_code, COUNT(*) AS cnt FROM ods.supplier_dim GROUP BY supplier_code HAVING COUNT(*) > 1;

第一段查出来的结果按缺失字段拆成多个文件回退，第二段里全角和空白问题回源系统修，日期越界的必须在抽取前拦住，否则加载阶段直接报错中断整批任务。第三段只是定位，真正去重前要把重复组的全部字段拉出来给业务方看。清洗是个反复的过程，不可能几天做完，初期可以每天把过滤清单邮件发给业务方，既催修正，也留作后期验证数据的依据。

3.3 过滤结果要留底

过滤掉的数据不能一删了之，要么写进独立的过滤数据表，要么落 Excel 归档。我一般会在 ODS 侧建一张etl_reject_record，记录批次号、源表、主键、拒绝原因、拒绝时间，后面业务方质疑「这条数据怎么没进仓库」，直接按主键一查就有答案。这张表同时也是清洗规则有效性的证据，规则改了什么、影响了多少行，全在里头。

4. 数据转换与加载：编码统一、粒度聚合和指标落库

4.1 不一致数据的编码映射

转换的第一件事是整合。同一个供应商在结算系统里编码是XX0001，在 CRM 里是YY0001，抽到 ODS 之后必须统一成一个编码才能进 DW。做法是建一张映射维表，把各源系统的编码和统一编码对应起来，转换时走关联：

-- 编码统一：源系统编码映射到 DW 统一编码 INSERT INTO dw.dim_supplier (supplier_key, supplier_name, src_system, src_code) SELECT m.unified_code, s.supplier_name, s.src_system, s.supplier_code FROM ods.supplier_dim s JOIN ods.code_mapping m ON m.src_system = s.src_system AND m.src_code = s.supplier_code WHERE s.supplier_name IS NOT NULL;

映射表要有人维护，新供应商进来没配映射就会关联不上，所以加载后要统计未匹配行数，超过阈值就告警。这一步的坑在于映射表本身也可能重复，unified_code必须建唯一约束，否则一个供应商在 DW 里裂成两条。

4.2 粒度聚合与商务规则计算

业务系统存的是明细，数据仓库是拿来分析的，不需要那么细。订单明细按天、按客户、按品类聚合到 DW 的事实表，聚合口径要和业务方对齐——是按订单日期还是发货日期、退款算不算进去，这些都要在转换规则文档里写死。商务规则计算更麻烦，有些指标不是简单加减，比如客户等级、返利金额、同比环比，这些在 ETL 里算好存进 DW，分析端直接取，比每次查询现算稳定得多。

-- 按天聚合 + 商务规则：返利按阶梯计算后落事实表 INSERT INTO dw.fact_sales_daily SELECT o.order_date, o.customer_id, SUM(o.amount) AS sales_amt, SUM(CASE WHEN SUM(o.amount) >= 100000 THEN SUM(o.amount) * 0.03 WHEN SUM(o.amount) >= 50000 THEN SUM(o.amount) * 0.02 ELSE 0 END) AS rebate_amt, COUNT(DISTINCT o.order_id) AS order_cnt FROM ods.order_main o WHERE o.order_date = CURDATE() - INTERVAL 1 DAY GROUP BY o.order_date, o.customer_id;

聚合的粒度一旦定下来就不要轻易改，事实表的主键组合变了，下游所有报表都得跟着动。加载方式上，小批量可以直接 insert，大批量建议先写临时表再 swap，避免加载中途失败留下半张表的数据。

4.3 加载批次与幂等

加载必须带批次号，同一批次重跑要能覆盖而不是追加，否则一次失败重试就多出一倍数据。通常的做法是加载前按批次号删除目标表对应分区，再整批写入，整个过程包在一个事务里。DW 侧的表按日期分区，删除和写入都落在同一个分区上，代价可控。

5. 日志、告警与常见问题排查：让 ETL 不再是黑匣子

5.1 三类日志各管什么

ETL 跑起来之后最怕的是「不知道它跑到哪了、错在哪了」。日志分三类：执行过程日志记录每一步的起止时间、影响行数，流水账形式，用来算每步耗时；错误日志在模块出错时记录时间、模块名、错误信息，是排查的第一入口；总体日志只记开始时间、结束时间、成功与否，给调度系统看。用 ETL 工具的话工具自带一部分日志，但业务侧的过滤行数、映射未匹配数这些还得自己补。

告警走邮件最稳妥，出错时把错误日志的关键行附上发给系统管理员，别只发一句「任务失败」，收件人还得登服务器翻日志，一来一回半小时没了。

5.2 常见问题排查清单

现象：增量抽取后数据重复。原因多半是水位线用了创建时间而不是更新时间，或者抽取过程中没封上界导致边界数据被两次拉取。解决是把水位线字段换成业务真实更新时间，并在 SQL 里加上界条件，重跑前先按批次清理。

现象：加载到一半任务中断，目标表数据不全。原因是没做事务包裹或没走临时表 swap，中途失败留下半批数据。解决是加载逻辑改成「删分区—写临时表—原子替换」，失败时目标表保持上一批完整状态。

现象：报表指标和源系统对不上，差几百块。先查清洗环节有没有误杀，再查聚合口径——退款单、测试单、跨月订单是否被算进去。通常的做法是拿一天的明细逐条对账，定位到具体订单再反推规则。

现象：编码映射关联不上，DW 里供应商为空。新供应商没配映射，或者映射表里unified_code有重复导致关联放大。解决是加载后统计未匹配行数并告警，映射表加唯一约束，新增供应商走审批流程再入库。

现象：日期越界导致整批 ETL 失败。源系统录入没校验，2018/13/45这种值进了库。解决是在 ODS 清洗阶段就用STR_TO_DATE判空拦截，把越界记录写进拒绝表回退业务方，而不是等到加载阶段让数据库报错。

提示：清洗规则每改一次都要回归验证历史批次，确认没有把之前正常的数据变成拒绝记录，规则版本和生效时间要记在配置表里。

6. 验收与答辩：用对账表和批次日志证明这套 ETL 是可信的

验收不是把任务跑通就完事，要能拿出证据。我一般准备三样东西：一张源系统与 DW 的日对账表、一份清洗拒绝记录汇总、一份批次运行日志。对账表按天比对订单数、销售额、客户数三个指标，差异率控制在千分之一以内算通过，超出就逐条下钻；拒绝记录汇总说明每类脏数据拦截了多少、回退了多少、业务方修正了多少，这是清洗规则有效性的直接证据；批次日志展示最近若干次运行的耗时和成功率，证明方案可重复执行而不是碰运气跑通一次。

答辩时最容易被问的是「你怎么保证数据是准的」。回答不要停留在「我写了清洗 SQL」，而是拿出对账差异从千分之一收敛到零的过程，说明哪条规则拦住了哪类问题。另一个高频问题是增量抽取的边界，把水位线字段、上界封口、重跑幂等这三件事讲清楚，基本就稳了。

从那以后我每次做数据集成方案，都会在写第一行抽取 SQL 之前先把对账口径和拒绝记录表定下来，跑通了不算完，对得上账才算。希望这套结构能帮到你，拿去改改就能当课设的方案骨架，也能当交付文档的初稿。

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