数据清洗建议草案

以下为针对“多源日志字段不一致、重复记录较多”的数据清洗步骤与策略。目标是统一结构、消除重复、提高数据的可用性与可信度，同时建立可持续的质量监控机制。

一、总体清洗流程
- 原始层保留：原样落地各源日志，禁止覆盖或直接删除。
- 标准化层：统一字段、类型、编码、时区与枚举值。
- 去重层：执行精确与近似去重，并进行记录合并（survivorship）。
- 验证层：按规则校验并生成质量指标与报告。
-发布层：提供面向分析与下游使用的干净数据集。
- 监控与回馈：持续度量质量、预警、回滚与修复。

二、详细步骤

1. 数据盘点与剖析
- 枚举数据源与版本：识别各源的字段名、格式、编码、时区、日志级别定义、分隔符、嵌套结构（JSON/文本）。
- 字段画像与分布：统计非空率、类型一致性、异常值比例、时间戳解析成功率。
- 重复画像：按源与时间窗口统计重复率（精确重复、近似重复），识别重复产生路径（如双采集、日志转发环路、重试导致多次写入）。

2. 构建统一日志标准（Canonical Schema）
- 字段集合建议（可按需扩展）：event_ts、source_system、host、service、env、event_id、level、trace_id/request_id、user_id、ip、message、raw_payload。
- 统一命名规范：建立映射字典（例如 ts/time/timestamp → event_ts；lvl/severity → level；uid/userId → user_id；msg/message → message；eventId/code → event_id）。
- 数据契约与版本化：通过模式文件（如 JSON Schema/Avro）发布标准，维护变更日志与兼容策略。

3. 字段标准化与映射
- 时间戳规范：
  - 多格式解析（识别ISO8601、epoch秒/毫秒、地区格式），统一转换为UTC，输出ISO8601精度到毫秒。
  - 确认时区来源（字段内标注、源系统默认、采集侧附加）。无法确定时区的记录标记为“tz_unknown”并进入隔离队列或使用源默认时区。
  - 修正常见格式错误（美式/欧式日月倒置、缺年、毫秒误作秒等），保留修正策略标记。
- 类型与编码：
  - 严格类型转换（整型、浮点、布尔、枚举），失败写入错误原因字段（error_type）并进入隔离区。
  - 统一文本编码为UTF-8，清理不可见字符与异常分隔符。
- 值域与枚举统一：
  - level枚举统一（如 DEBUG/INFO/WARN/ERROR/FATAL），映射源自定义级别到标准集合，未映射值标记为“level_unknown”。
  - ip校验合法性（IPv4/IPv6），非法值置为null并记录原因。
- 结构规范：
  - 解析并扁平化JSON字段，保留原始raw_payload。
  - 对多行message进行归并（基于堆栈标记、连续行合并规则）。

4. 缺失值处理与字段合并
- 关键字段优先级：event_ts、service、message、source_system。缺失时执行策略：
  - event_ts缺失：可选用采集时间（ingest_ts）替代并打标“ts_imputed”，严禁默默替换。
  - service/host缺失：尝试源侧元数据补齐；无法补齐则置null并标注缺失来源。
- 字段合并：当同义字段并存（如 uid 与 userId），按映射统一到user_id，保留来源字段以供追溯。
- 统一空值表示：使用显式null，避免混用空字符串、"N/A"、"-"等。

5. 去重策略
- 预规范化：去重前必须完成上述标准化，以减少“格式差异导致的假不同”。
- 精确去重（Exact Dedup）：
  - 指纹生成：基于规范化后的关键字段组合计算哈希（如 SHA-256）。
  - 推荐指纹字段：normalized(event_ts round到秒或毫秒、source_system、service、host、level、event_id（若可靠）、trace_id/request_id（若存在）、normalized_message）。
  - normalized_message处理：去除可变片段（如动态ID、时间、GUID）与多余空白，可用正则抽取稳定模板。
  - 策略：在滑动时间窗口内（例如 ±5s 或按系统SLA设定）对相同指纹记录保留一条，其他标记为重复。
- 近似去重（Fuzzy/Similarity Dedup）：
  - 适用场景：event_id缺失或不稳定、message存在微小差异。
  - 方法：对message计算SimHash/MinHash或编辑距离，结合窗口与阈值（如Jaccard ≥ 0.9）判定重复。
  - 联合键：同一source_system+service+level+近似message+时间邻近判定为重复。
- 合并/保留规则（Survivorship）：
  - 保留信息最完整记录（非空字段数多者优先）。
  - 时间冲突：默认保留最早发生时间或按业务定义保留最新；需一致化并记录决策。
  - 字段合并：对非冲突字段进行填充（例如一条有user_id，另一条有ip，合并为一条完整记录），保留合并来源列表。

6. 验证与质量评估
- 规则清单：
  - 时间戳可解析且为UTC；event_ts在合理范围（不早于系统上线、不晚于当前时间+容错偏移）。
  - level属于标准枚举集。
  - IP格式合法。
  - 关键字段非空率阈值（例如 event_ts ≥ 99.5%、service ≥ 98%、message ≥ 99%）。
  - 唯一性：指纹在窗口内唯一；trace_id/request_id在同窗口内的重复率低于阈值。
- 指标与报表：
  - 模式符合率（Schema Conformance Rate）。
  - 时间戳解析成功率与时区确定率。
  - 字段类型有效率与枚举映射成功率。
  - 去重前后重复率、去重召回率与误杀率（通过抽样人工复核估计）。
  - 完整性提升（字段非空率变化）。
- 样本抽检：定期对随机样本进行人工比对，以校准近似去重阈值与合并策略。

7. 监控与异常处理
- 在线/离线监控：
  - 阈值告警（例如解析失败率>1%、重复率突增）。
  - 源级别健康评分与趋势。
- 隔离区与回滚：
  - 解析失败或规则不通过的记录进入隔离表，支持快速回放与规则修订。
  - 版本化清洗规则，支持灰度发布与回滚。
- 变更管理：
  - 新源加入或字段变化需走变更评审，更新映射与契约，并进行回归验证。

8. 性能与实现要点
- 流批结合：批处理用于历史对齐，流处理用于增量清洗与近实时去重。
- 窗口管理：为去重设定合理窗口（依据系统延迟与乱序情况），支持跨分区聚合。
- 指纹存储与查重：使用键值存储或布隆过滤器减少查重成本；为近似去重引入分桶（按service/level）降低复杂度。
- 审计与血缘：记录每条数据的清洗步骤、修改点与来源，以支持追溯与合规。

9. 常见风险与防范
- 误删真实事件：所有删除或合并需保留原始记录与指纹匹配依据，提供可审计的恢复路径。
- 时区误判导致错误去重：时间统一前严禁去重；无法判定时区的记录不做近似去重。
- 动态内容未正确归一化：定期更新normalized_message规则库，避免过度归一化造成不同事件被错误合并。

通过上述步骤，可在保证可追溯与可回滚的前提下，系统性解决多源日志字段不一致与重复记录问题，并建立可持续的数据质量保障与监控框架。

以下清洗步骤面向“维度口径不统一导致指标偏差”的场景，目标是在不改变业务事实的前提下，使维度定义、编码和时间/单位等口径一致化，消除因口径差异造成的统计偏差。

一、目标与问题界定
- 目标：统一维度口径，保证事实数据在相同维度切片下的可比性和可复算性。
- 常见偏差来源：
  - 维度定义差异（如“新客”“活跃用户”“渠道”定义不同）
  - 枚举/编码不一致（如渠道=APP/app/APP官方）
  - 标识符冲突或缺失（多源用户ID、订单ID不一致）
  - 时间口径不一致（事件时间 vs 入库时间；时区；周/月定义）
  - 单位口径不一致（币种、重量/体积单位）
  - 层级口径差异（区域/组织层级版本不一致，历史变更未管理）

二、治理前提（必须产出并冻结）
- 统一口径文档：对每个维度及其属性给出业务定义、取值范围、规则与边界条件。
- 统一维表与映射表：
  - 主数据/参考数据表（如渠道、区域、产品、组织）
  - 源值到标准值的映射表（含版本与生效区间）
- SCD策略：为维表确定缓慢变化维策略（Type 1/Type 2），并定义生效时间字段（effective_from/effective_to）。
- 数据契约：事实表必须以标准维的代理键（surrogate key）关联，不再直接使用源系统枚举。

三、数据清洗总体流程
1) 诊断与差异量化
- 以指标在不同维度切片上的差异作为入口，定位导致偏差的维度及来源系统。
- 计算偏差率：bias = |metric_sourceA - metric_sourceB| / metric_reference。
- 输出“维度-来源-偏差”矩阵，按影响度排序。

2) 标准化设计
- 为问题维度制定标准定义、合法取值、层级结构和时间/单位口径。
- 产出映射规则和冲突解决优先级。

3) 构建标准维与映射
- 建立统一维表（含代理键、自然键、SCD字段）。
- 构建源值到标准值的映射表（含来源、原始值、标准值、版本、有效期、置信度）。

4) 源数据清洗与对齐
- 在入仓层进行基础规范化（去空白、大小写、字符集、无效符号）。
- 在集成层进行口径映射、单位转换、时区/日历对齐、层级归并。

5) 事实表重键与回填
- 以标准维代理键替换事实表的源维值。
- 重算聚合层，标注版本号，分批回填历史数据。

6) 验证与回归
- 建立结构化校验（约束、域值、参照完整性、分布/稀疏度、度量平衡）。
- 前后版本A/B对比与抽样复核。

7) 持续监控与预防
- 指标口径漂移监控、映射覆盖率、未知/未映射比率、时区/单位异常报警。
- 映射变更审批与审计。

四、关键清洗步骤与技术要点
1) 标识符与实体对齐
- 用户/商户/商品等多源ID合并：优先使用权威主键；无法对齐时建立交叉映射（crosswalk）。
- 模糊匹配仅作为备选，需设阈值与人工复核通道；保存匹配分数与证据。
- 去重策略：确定性规则优先（同证件/同手机号+同姓名），概率模型作为补充。

2) 枚举与编码标准化
- 规范化操作：trim、lower/upper、全半角统一、同义词/别名归一。
- 非法值处理：映射失败的值标记为“UNKNOWN”并入库，但不得丢弃；输出异常清单推动源端修复。
- 映射表版本管理：新增、停用、合并需要记录生效区间，避免历史重算时口径穿越。

3) 单位与币种口径统一
- 明确标准单位与币种（如重量kg、金额CNY）。
- 转换规则需版本化：汇率取自权威源，标注基准时间与来源；物理单位转换须保留精度和舍入策略。
- 事实表保存原值与标准值，便于追溯。

4) 时间口径对齐
- 统一时间基准：统一到UTC存储，展现层按区域时区转换；或统一使用业务时区。
- 明确统计窗口：自然周/ISO周/账期、自然月/账月；定义T+N延迟规则和截数时间点。
- 明确事件时间字段优先级：event_time > business_time > ingestion_time；迟到数据采用水位线策略并触发补算。

5) 层级与版本管理
- 区域/组织/产品层级使用SCD2管理；事实表按发生时点关联当时有效层级。
- 层级变更影响聚合结果时，采用“历史重算”或“静态回溯口径”二选一，并在指标字典中明示。

6) 参照完整性与覆盖率
- 事实表外键必须在维表中可解（包括UNKNOWN代理键）；记录解码失败率并设阈值报警。
- 对关键维度（如渠道、区域）要求100%映射覆盖率，非关键维可设最大未知占比上限。

五、验证与回归策略
- 约束类：
  - 维表唯一性：unique(channel_code_std)
  - 事实-维参照：FK(fact.channel_sk -> dim_channel.channel_sk)
  - 域值合法性：channel_std in mapping_table
- 分布类：
  - 类别分布漂移：PSI/KL对比前后版本；阈值例如 PSI < 0.2
  - 空值/未知占比阈值：unknown_rate <= 0.5%
- 业务对账：
  - 总量/金额守恒：清洗前后订单数、GMV差异在容许阈值内（如≤0.1%），差异需可解释（重复移除、无效枚举剔除）
  - 核心指标回归：以周/月级为粒度做A/B对比并抽样核验

六、监控与告警
- 口径漂移：同一维度下核心指标的同比/环比异常波动告警（排除季节性）
- 映射完整性：新增源值未被映射的累计数和7日触发告警
- 时间对齐：迟到数据占比、跨时区异常
- 单位对齐：币种/单位字段异常值比例

七、实施顺序建议
1) 冻结口径与维表模型
2) 建立映射表与标准维，完成历史映射覆盖
3) 事实表外键重构并小流量回填验证
4) 全量回填与聚合重建
5) 上线监控与问题回溯机制

八、示例SQL片段
1) 渠道值标准化与映射
- 预清洗
  - select distinct trim(upper(replace(channel_raw,'　',' '))) as channel_norm from stg_orders;
- 源值到标准值
  - select o.order_id,
           m.channel_sk,
           coalesce(m.channel_std, 'UNKNOWN') as channel_std
    from stg_orders o
    left join ref_channel_mapping m
      on trim(upper(o.channel_raw)) = m.channel_raw_norm
     and o.event_time between m.effective_from and m.effective_to;

2) 时区与周口径统一
- with x as (
    select order_id,
           (event_time at time zone 'UTC' at time zone 'Asia/Shanghai') as event_time_cst
    from stg_orders
  )
  select order_id,
         date_trunc('week', event_time_cst)::date as iso_week_start
  from x;

3) SCD2 维度关联（按事实发生时间）
- select f.fact_id, d.channel_sk
  from fact_orders f
  join dim_channel d
    on f.channel_nk = d.channel_nk
   and f.event_time >= d.effective_from
   and f.event_time <  d.effective_to;

4) 质量校验示例
- 未映射占比
  - select count(*) filter (where channel_std='UNKNOWN')::decimal / count(*) as unknown_rate
    from dwd_orders;
- 对账差异
  - select abs(sum_amt_new - sum_amt_old)/nullif(sum_amt_old,0) as diff_rate
    from (
      select sum(amount) as sum_amt_old from dwd_orders_old
    ) a cross join (
      select sum(amount) as sum_amt_new from dwd_orders
    ) b;

九、常见陷阱与规避
- 映射表无生效期导致历史穿越：必须SCD化并按事实时间关联。
- 在事实层直接写死口径逻辑：应使用标准维代理键与映射表，避免逻辑分散。
- 丢弃未知/不合法数据：应纳入UNKNOWN并监控占比，推动源端治理。
- 单位与币种未标注来源与时间：导致回溯不可复现，需版本化。

以上步骤可作为统一维度口径的数据清洗与验证蓝本。实际实施时应先在受影响最大的维度上试点，验证后再扩展至全量维度和历史数据。

以下为面向“投放与CRM数据存在重复与缺失，ID映射混乱”的数据清洗与校验步骤。内容以可实施的技术流程为主，覆盖标准化、去重、缺失处理与ID映射重建，并配套验证与监控策略。

一、数据盘点与分层
- 划分数据域与对象：
  - 投放域：平台事件（曝光、点击、转化）、活动/素材维表（campaign_id、ad_id、creative_id）。
  - CRM域：客户/线索/联系人（customer_id/lead_id）、商机、订单。
- 建立暂存层（staging）承接原始数据；处理层（curation）输出清洗结果；成品层（gold）供分析与报表使用。
- 定义关键主键与外键：
  - 事件主键：platform_event_id 或由来源ID+时间+去重哈希构成的唯一键。
  - 实体主键：建议引入全局唯一ID（Global ID，GID），如 global_customer_id、global_campaign_id。
  - 外键约束：事实表中的 customer_id、campaign_id 必须指向维表存在记录。

二、统一ID策略与映射字典构建
- 设立ID映射表（ID Dictionary）：
  - 字段示例：global_customer_id、source_system、source_id、start_ts、end_ts、status（active/merged/retired）、confidence_score、last_verified_ts。
  - 约束：同一（source_system, source_id）唯一映射；支持历史别名与有效期。
- 规范ID格式与校验：
  - 验证ID模式（正则/长度/字符集）；去除前后空格、零前缀、大小写不一致。
  - 对邮箱、手机号进行标准化：邮箱小写化、去除别名“+tag”；手机号统一国家码与格式。
- 明确身份粒度：
  - 区分“个人”“设备”“Cookie”“账号（平台/CRM）”四类标识，避免跨粒度硬映射。
  - 仅在有确定性证据（同邮箱、同手机号、平台统一账号）时将不同标识合并至同一 global_customer_id。

三、字段标准化与基础清洗
- 文本规范：去空格、统一大小写、清除不可打印字符、标准化分隔符与缩写。
- 日期与时区：统一ISO格式与时区；将投放事件与CRM时间线统一到UTC并保留原始时区。
- 枚举值：统一渠道、平台、活动命名（UTM参数、渠道枚举）；建立字典并做值合法性校验。

四、重复数据识别与去重
- 事件级去重（投放日志）：
  - 规则优先级：platform_event_id 完全一致 → 同源ID+时间戳在短窗口内（如5分钟）重复 → 全字段哈希重复。
  - 处理策略：幂等写入（唯一索引）；滑窗去重；保留首条或最新一条并记录去重原因。
- 客户/线索级去重（CRM与投放跨域）：
  - 阻塞（Blocking）：按规范化邮箱、手机号、姓名+邮编、device_id/cookie_id 分组。
  - 匹配规则：
    - 确定性匹配（优先）：同邮箱或同手机号完全一致；同平台账号ID一致。
    - 概率匹配（补充）：姓名相似度（Jaro-Winkler/Levenshtein）、地址标准化相似度、邮箱本地部分相似且域一致；设定阈值如 ≥0.9 自动合并、0.8–0.9人工复核。
  - 生存者（Survivorship）与字段级冲突处理：
    - 优先来源：CRM > 自投放平台 > 第三方聚合；或使用最近更新时间/更高数据质量评分。
    - 字段级合并：在来源优先级下，保留最完整值（非空、格式合法、最新）。
  - 合并写法：软合并（保留原记录、建立别名关系到同一 global_customer_id）；写审计日志与理由。

五、缺失数据处理
- 划分关键与非关键字段：
  - 关键字段：ID、事件时间、平台/活动ID、客户唯一识别信息（邮箱/手机号/账号）。禁止推断或填充；缺失即标记为不可用并进入回填流程。
  - 非关键字段：地域、设备、UTM参数、来源渠道等可按规则补全或推断。
- 回填与补全：
  - 通过跨表关联（如同一平台账号或同邮箱）补齐缺失字段。
  - 标准化来源标签（如渠道/活动名称）从字典回填。
- 缺失原因与质量标签：
  - 对缺失字段记录 null_reason（not_collected、system_error、parse_error、unknown）。
  - 计算字段完整率并设阈值（如邮箱完整率<70%触发告警与流程调整）。

六、ID映射清理与重建
- 冲突检测：
  - 一对多冲突：同（source_system, source_id）映射到多个 global_customer_id → 置为冲突状态，进入人工或规则解歧。
  - 多对一过度合并：短期内大量不同 source_id 映射到同一 global_customer_id → 检查误合并，必要时回滚。
- 有效期与漂移：
  - 记录映射有效期；当ID变更（账号迁移、邮箱更新）时关闭旧映射并建立新映射，防止历史事件断链。
- 置信度治理：
  - 确定性=1.0，概率匹配按分数写入；低于阈值仅保留候选关系，不用于主连接。
- 参照完整性：
  - 每日检查事实表外键是否都能在维表或映射表找到对应条目；孤儿记录入隔离区并驱动回填。

七、跨域对账与一致性校验（投放↔CRM）
- 关键校验：
  - 转化事件数量与CRM新建线索/商机数量在时间窗口内的匹配率（设目标如≥95%）。
  - 投放渠道/活动在CRM中归因一致性（活动ID/名称对齐、UTM映射命中率）。
  - 去重前后指标对比：重复率下降、合并后唯一客户数稳定、无异常断崖。
- 误差排查：
  - 时区差异、窗口期选择、平台重试造成的重复；CRM延迟入库；多设备跨账号导致分裂身份。

八、落库与审计
- 采用不可破坏写入策略：保留原始数据与清洗结果，两者可追溯。
- 审计日志内容：去重/合并规则、匹配分数、来源优先级、操作时间、操作者或作业ID。
- 版本化：维表与映射表支持版本号，便于回滚与重处理。

九、监控指标与阈值
- 完整性：关键字段缺失率、UTM/渠道字段填充率。
- 唯一性：客户/线索重复率；事件重复率；ID冲突数。
- 一致性：事实表外键命中率（孤儿率）；跨域对账匹配率。
- 漂移与稳定性：映射变更率（global_customer_id变更占比）、高频合并回滚率。
- 及时性：数据延迟分布（P95落地时延）。
- 告警规则：超阈值自动告警并输出问题样本用于复核。

十、工具与实现建议（示例）
- 规则引擎与相似度计算：SQL/UDF配合文本相似度库；或使用开源实体解析工具进行阻塞与匹配。
- 幂等与唯一约束：在事件表对 platform_event_id 建唯一索引；在映射表对（source_system, source_id）建唯一约束。
- 数据字典与参考表：渠道、平台、活动命名标准化表；邮箱域名白名单/常见别名处理表。

通过上述清洗与治理流程，可系统性地降低重复与缺失、修复ID映射混乱，最终实现投放与CRM数据的准确对齐与可追溯管理。建议先在样本数据上跑通阻塞与匹配规则，确定阈值与生存者策略，再推广至全量并建立持续监控。