很多数据集成项目最初关注的都是数据库能不能连上、接口能不能调通、任务能不能按时跑完。真正上线以后才会发现“数据搬过来了”只是第一关“搬过来的数据能不能直接用”才是更难的一关。同一个客户在CRM里有三条记录ERP里的区域编码是01销售系统里却写“华东”订单金额出现空值不知道是业务没填还是链路丢了同步任务失败重跑一次目标表突然多出几十万条重复数据。这些问题看起来只是重复、空值和编码不一致背后真正暴露的却是不同系统对同一业务对象、同一字段和同一状态没有使用同一套规则。所以数据清洗绝不是简单的删重、补空和替换字符而是要把分散在各系统里的业务规则重新统一起来。在正式展开之前我整理了一套《数据仓库建设解决方案》涉及数据标准、数据质量、数据治理等内容。正在做数据集成、数仓或者数据治理的可以结合本文一起参考。需要自取https://s.fanruan.com/7igmg复制到浏览器一、重复数据先判断“重复的到底是什么”很多人碰到重复数据第一个反应就是DISTINCT。但真实项目里两行完全一样的数据反而最好处理。更麻烦的是下面几种情况。同一笔业务被重复写入例如订单同步任务凌晨执行一次中途失败。开发人员重新跑任务后第一次已经写成功的数据再次进入目标库于是同一张订单出现两条甚至多条记录。这种重复的根源并不是源数据而是同步链路缺少幂等控制。真正需要控制的是业务唯一键是什么已经写入的数据再次到达时怎么办任务失败以后从哪里恢复重跑时覆盖哪一个时间范围。例如订单表通常可以用订单号判断唯一性支付流水则可能需要“支付单号流水号”库存流水甚至可能需要“单据号行号动作类型”。所以唯一键不能只看数据库有没有主键而要回到业务过程判断到底什么组合能够唯一代表一次业务事实。实际维护同步链路时这类规则最好直接落在数据处理任务中而不是报表发现重复以后再补救。用FineDataLink跑订单、客户这类日常同步时我通常会把增量范围、唯一键判断、字段加工和写入规则放在同一条链路里看。后面真的出现重复订单先查这几个节点基本就能判断是上游重复产生还是重跑机制出了问题。同一个业务对象出现多条记录第二类更复杂。例如张三138xxxx1234张三86 138xxxx1234技术上是两条记录但业务上可能是同一个客户。再比如上海XX科技有限公司上海XX科技有限公司总部到底是同一家企业的两个名称还是两个不同主体这就不是简单SQL去重能够解决的了而要建立实体识别规则。通常可以按照可信度分层强标识字段身份证号、统一社会信用代码、系统统一客户ID中等标识字段手机号、邮箱、银行账号弱标识字段名称、地址、联系人。强标识一致时可以直接合并只有弱标识相似时则不应该自动处理而应进入待确认名单。粒度变化造成的“伪重复”订单表一单一行订单明细表一件商品一行。两张表关联后一张订单自然会出现多行。如果直接去重很可能把正常商品明细删掉。所以判断重复前一定要先明确当前表是一客户一行、一订单一行还是一订单商品一行很多所谓“重复”本质上其实是统计粒度没有定义清楚。二、空值不要急着补先判断“为什么为空”空值比重复更容易被误处理。因为很多团队会习惯性做金额为空填0名称为空填“未知”日期为空填1900-01-01。结果表面上空值率下降了业务含义却被改变了。NULL、0、“未知”和“未发生”完全不是一回事。例如客户注销日期为空可能代表客户仍然有效。订单发货时间为空可能表示还没发货也可能是物流数据没有同步回来。交易金额为空则可能意味着系统异常因为一笔已完成订单理论上不应该没有金额。因此空值至少要分成四类。业务允许为空例如备注、附件、注销时间。这类数据不需要修复。业务应该有但没有采集例如客户行业、销售负责人。这属于数据录入质量问题应该追到业务流程而不是只在数仓里补一个默认值。上游系统本来就没有例如旧ERP没有客户等级字段新CRM才新增这个字段。这类空值体现的是系统覆盖范围差异。集成过程中产生的异常空值源系统有值目标表却为空。这种情况要重点排查字段映射有没有错、JOIN条件是否匹配、字段类型转换有没有失败、上游结构是否发生变化。所以空值治理真正需要的不是“统一填值”而是建立字段重要程度 空值原因 处理方式三者之间的关系。关键主键、交易金额、状态字段出现异常空值时可以直接拦截普通描述字段允许为空能够根据明确业务规则推导的字段才考虑补齐。数据完整率不是越高越好错误填充出来的100%完整率没有意义。三、编码不一致真正难的是标准如何长期维护跨系统集成里最常见的问题之一就是编码体系各自为政。同一个销售区域ERP01CRMEASTOA华东区历史系统HD如果直接汇总系统会认为它们是四个不同区域。最开始系统不多时开发人员往往直接写CASE WHEN region01 THEN 华东问题在于这种方法很容易越写越多。半年以后可能变成订单任务里一套地区转换客户任务里一套经营报表里又有一套。一旦业务把“华东区”拆成“华东一区”和“华东二区”最危险的不是改规则而是你根本不知道旧规则散落在哪些地方。所以成熟的编码治理通常会拆成三层。第一层定义标准编码例如统一规定华东一区 R001华东二区 R002第二层建立来源映射ERP01→ R001 CRMEAST-A→ R001 OA华东一区→ R001第三层保留版本这一点经常被忽略。假设2026年组织架构发生调整如果直接覆盖旧映射重新计算2025年的数据时也可能套用2026年的组织关系。因此映射表最好还要记录生效时间、失效时间、来源系统、责任部门。这样才能做到“历史数据按历史口径解释当前数据按当前规则解释”。这类编码转换如果反复出现在多条任务中我一般不会继续往SQL里堆CASE WHEN而是把常用映射单独维护。像FineDataLink里的地区、渠道、商品分类等处理逻辑会尽量让多条任务共用同一套规则。业务改一次分类时先改标准再看哪些数据任务受影响比逐条翻SQL更容易控制版本。四、清洗最危险的地方把“异常数据”洗成“看起来正常”很多数据项目有一个误区只要最后数据里没有空值、没有重复、编码统一就说明数据质量好了。实际上错误的清洗规则可能比脏数据更危险。例如“客户名称相同就合并。”结果两家不同企业恰好同名。“地区为空统一补华东。”结果全国未知地区的订单全部进入华东。“销售额异常高就删除。”结果刚好删掉一个真实大客户订单。因此数据清洗必须区分确定错误、可以推断、无法判断。确定错误的数据可以自动修复能够依据明确规则推断的数据可以自动转换无法判断的数据应该进入异常区而不是强行处理。我更倾向于在集成任务里给异常数据单独留一个出口。比如用FineDataLink处理订单时金额为空、非法地区编码、主键冲突的数据不一定直接丢掉可以单独落到异常表保留原始值、任务时间和异常原因。等业务确认以后再决定修复还是重新同步。这样做的关键不是“多存一张异常表”而是保住了一条原则清洗系统只处理自己能够确定的事情不替业务做无法确认的判断。五、真正成熟的数据清洗要从“修数据”变成“管规则”很多企业的数据质量工作长期处于救火状态。报表发现客户重复补一段SQL发现区域不一致再加一层映射出现空值又临时补一个默认值。几年以后真正麻烦的已经不是脏数据而是没人知道这些数据到底经过多少条规则。因此数据集成里的脏数据治理最终要形成四个环节。标准定义哪些字段不能为空哪些编码合法什么叫重复。清洗把标准转成可执行规则。校验任务跑完以后检查主键重复率关键字段空值率非法编码数量来源和目标记录数差异数据量是否异常波动。因为任务成功 ≠ 数据正确。凌晨任务全部显示成功但当天订单只有正常水平的30%这批数据依然不能直接给业务使用。追溯保留原始值、处理规则、处理时间和修改结果。否则一旦数字出错只能从最终报表反向猜测。结语重复、空值、编码不一致看起来只是数据集成中的三个基础问题。实际上它们分别在回答三个很重要的问题重复数据解决的是“同一个业务对象到底是谁”空值解决的是“数据缺失到底意味着什么”编码不一致解决的是“不同系统怎样使用同一种业务语言”。所以数据清洗真正的门槛从来不是会不会写DISTINCT、COALESCE或者CASE WHEN。真正困难的是企业能不能把分散在系统、代码和人员经验里的业务规则逐渐变成统一、可维护、可校验、可追溯的数据规则。低水平的数据清洗是看到问题以后把值改掉。更成熟的数据集成是先知道这个值为什么错再决定谁来改、按照什么规则改以及下一次怎样提前发现。当这套机制建立起来数据集成才真正从“搬运数据”走向了持续生产可信数据。
重复、空值、编码不一致：数据集成最常见的脏数据怎么处理？
很多数据集成项目最初关注的都是数据库能不能连上、接口能不能调通、任务能不能按时跑完。真正上线以后才会发现“数据搬过来了”只是第一关“搬过来的数据能不能直接用”才是更难的一关。同一个客户在CRM里有三条记录ERP里的区域编码是01销售系统里却写“华东”订单金额出现空值不知道是业务没填还是链路丢了同步任务失败重跑一次目标表突然多出几十万条重复数据。这些问题看起来只是重复、空值和编码不一致背后真正暴露的却是不同系统对同一业务对象、同一字段和同一状态没有使用同一套规则。所以数据清洗绝不是简单的删重、补空和替换字符而是要把分散在各系统里的业务规则重新统一起来。在正式展开之前我整理了一套《数据仓库建设解决方案》涉及数据标准、数据质量、数据治理等内容。正在做数据集成、数仓或者数据治理的可以结合本文一起参考。需要自取https://s.fanruan.com/7igmg复制到浏览器一、重复数据先判断“重复的到底是什么”很多人碰到重复数据第一个反应就是DISTINCT。但真实项目里两行完全一样的数据反而最好处理。更麻烦的是下面几种情况。同一笔业务被重复写入例如订单同步任务凌晨执行一次中途失败。开发人员重新跑任务后第一次已经写成功的数据再次进入目标库于是同一张订单出现两条甚至多条记录。这种重复的根源并不是源数据而是同步链路缺少幂等控制。真正需要控制的是业务唯一键是什么已经写入的数据再次到达时怎么办任务失败以后从哪里恢复重跑时覆盖哪一个时间范围。例如订单表通常可以用订单号判断唯一性支付流水则可能需要“支付单号流水号”库存流水甚至可能需要“单据号行号动作类型”。所以唯一键不能只看数据库有没有主键而要回到业务过程判断到底什么组合能够唯一代表一次业务事实。实际维护同步链路时这类规则最好直接落在数据处理任务中而不是报表发现重复以后再补救。用FineDataLink跑订单、客户这类日常同步时我通常会把增量范围、唯一键判断、字段加工和写入规则放在同一条链路里看。后面真的出现重复订单先查这几个节点基本就能判断是上游重复产生还是重跑机制出了问题。同一个业务对象出现多条记录第二类更复杂。例如张三138xxxx1234张三86 138xxxx1234技术上是两条记录但业务上可能是同一个客户。再比如上海XX科技有限公司上海XX科技有限公司总部到底是同一家企业的两个名称还是两个不同主体这就不是简单SQL去重能够解决的了而要建立实体识别规则。通常可以按照可信度分层强标识字段身份证号、统一社会信用代码、系统统一客户ID中等标识字段手机号、邮箱、银行账号弱标识字段名称、地址、联系人。强标识一致时可以直接合并只有弱标识相似时则不应该自动处理而应进入待确认名单。粒度变化造成的“伪重复”订单表一单一行订单明细表一件商品一行。两张表关联后一张订单自然会出现多行。如果直接去重很可能把正常商品明细删掉。所以判断重复前一定要先明确当前表是一客户一行、一订单一行还是一订单商品一行很多所谓“重复”本质上其实是统计粒度没有定义清楚。二、空值不要急着补先判断“为什么为空”空值比重复更容易被误处理。因为很多团队会习惯性做金额为空填0名称为空填“未知”日期为空填1900-01-01。结果表面上空值率下降了业务含义却被改变了。NULL、0、“未知”和“未发生”完全不是一回事。例如客户注销日期为空可能代表客户仍然有效。订单发货时间为空可能表示还没发货也可能是物流数据没有同步回来。交易金额为空则可能意味着系统异常因为一笔已完成订单理论上不应该没有金额。因此空值至少要分成四类。业务允许为空例如备注、附件、注销时间。这类数据不需要修复。业务应该有但没有采集例如客户行业、销售负责人。这属于数据录入质量问题应该追到业务流程而不是只在数仓里补一个默认值。上游系统本来就没有例如旧ERP没有客户等级字段新CRM才新增这个字段。这类空值体现的是系统覆盖范围差异。集成过程中产生的异常空值源系统有值目标表却为空。这种情况要重点排查字段映射有没有错、JOIN条件是否匹配、字段类型转换有没有失败、上游结构是否发生变化。所以空值治理真正需要的不是“统一填值”而是建立字段重要程度 空值原因 处理方式三者之间的关系。关键主键、交易金额、状态字段出现异常空值时可以直接拦截普通描述字段允许为空能够根据明确业务规则推导的字段才考虑补齐。数据完整率不是越高越好错误填充出来的100%完整率没有意义。三、编码不一致真正难的是标准如何长期维护跨系统集成里最常见的问题之一就是编码体系各自为政。同一个销售区域ERP01CRMEASTOA华东区历史系统HD如果直接汇总系统会认为它们是四个不同区域。最开始系统不多时开发人员往往直接写CASE WHEN region01 THEN 华东问题在于这种方法很容易越写越多。半年以后可能变成订单任务里一套地区转换客户任务里一套经营报表里又有一套。一旦业务把“华东区”拆成“华东一区”和“华东二区”最危险的不是改规则而是你根本不知道旧规则散落在哪些地方。所以成熟的编码治理通常会拆成三层。第一层定义标准编码例如统一规定华东一区 R001华东二区 R002第二层建立来源映射ERP01→ R001 CRMEAST-A→ R001 OA华东一区→ R001第三层保留版本这一点经常被忽略。假设2026年组织架构发生调整如果直接覆盖旧映射重新计算2025年的数据时也可能套用2026年的组织关系。因此映射表最好还要记录生效时间、失效时间、来源系统、责任部门。这样才能做到“历史数据按历史口径解释当前数据按当前规则解释”。这类编码转换如果反复出现在多条任务中我一般不会继续往SQL里堆CASE WHEN而是把常用映射单独维护。像FineDataLink里的地区、渠道、商品分类等处理逻辑会尽量让多条任务共用同一套规则。业务改一次分类时先改标准再看哪些数据任务受影响比逐条翻SQL更容易控制版本。四、清洗最危险的地方把“异常数据”洗成“看起来正常”很多数据项目有一个误区只要最后数据里没有空值、没有重复、编码统一就说明数据质量好了。实际上错误的清洗规则可能比脏数据更危险。例如“客户名称相同就合并。”结果两家不同企业恰好同名。“地区为空统一补华东。”结果全国未知地区的订单全部进入华东。“销售额异常高就删除。”结果刚好删掉一个真实大客户订单。因此数据清洗必须区分确定错误、可以推断、无法判断。确定错误的数据可以自动修复能够依据明确规则推断的数据可以自动转换无法判断的数据应该进入异常区而不是强行处理。我更倾向于在集成任务里给异常数据单独留一个出口。比如用FineDataLink处理订单时金额为空、非法地区编码、主键冲突的数据不一定直接丢掉可以单独落到异常表保留原始值、任务时间和异常原因。等业务确认以后再决定修复还是重新同步。这样做的关键不是“多存一张异常表”而是保住了一条原则清洗系统只处理自己能够确定的事情不替业务做无法确认的判断。五、真正成熟的数据清洗要从“修数据”变成“管规则”很多企业的数据质量工作长期处于救火状态。报表发现客户重复补一段SQL发现区域不一致再加一层映射出现空值又临时补一个默认值。几年以后真正麻烦的已经不是脏数据而是没人知道这些数据到底经过多少条规则。因此数据集成里的脏数据治理最终要形成四个环节。标准定义哪些字段不能为空哪些编码合法什么叫重复。清洗把标准转成可执行规则。校验任务跑完以后检查主键重复率关键字段空值率非法编码数量来源和目标记录数差异数据量是否异常波动。因为任务成功 ≠ 数据正确。凌晨任务全部显示成功但当天订单只有正常水平的30%这批数据依然不能直接给业务使用。追溯保留原始值、处理规则、处理时间和修改结果。否则一旦数字出错只能从最终报表反向猜测。结语重复、空值、编码不一致看起来只是数据集成中的三个基础问题。实际上它们分别在回答三个很重要的问题重复数据解决的是“同一个业务对象到底是谁”空值解决的是“数据缺失到底意味着什么”编码不一致解决的是“不同系统怎样使用同一种业务语言”。所以数据清洗真正的门槛从来不是会不会写DISTINCT、COALESCE或者CASE WHEN。真正困难的是企业能不能把分散在系统、代码和人员经验里的业务规则逐渐变成统一、可维护、可校验、可追溯的数据规则。低水平的数据清洗是看到问题以后把值改掉。更成熟的数据集成是先知道这个值为什么错再决定谁来改、按照什么规则改以及下一次怎样提前发现。当这套机制建立起来数据集成才真正从“搬运数据”走向了持续生产可信数据。