DWD层清洗了什么？面试官说你只会去重和过滤——清洗规则怎么管理？

-- DWD用户付费事件清洗SQL（8个清洗动作全覆盖）
INSERT OVERWRITE TABLE dwd_user_payment_di PARTITION(dt='${bizdate}')
SELECT
    -- ① 去重：用ROW_NUMBER按event_id去重，保留最早一条
    t.event_id,
 
    -- ② 空值处理：user_id为空填'unknown'
    COALESCE(t.user_id, 'unknown') AS user_id,
 
    -- ③ 格式标准化：渠道名统一大写
    UPPER(t.channel) AS channel,
 
    -- ④ 字段映射：支付方式编码→可读含义
    CASE t.pay_type
        WHEN '1' THEN 'credit_card'
        WHEN '2' THEN 'paypal'
        WHEN '3' THEN 'google_pay'
        WHEN '4' THEN 'apple_pay'
        ELSE 'other'
    END AS pay_method,
 
    -- ⑤ 维度退化：关联国家维度表
    dim_country.country_name,
    dim_country.timezone,
    dim_country.currency AS local_currency,
 
    -- ⑥ 类型转换：金额从STRING转DECIMAL
    CAST(t.pay_amount AS DECIMAL(10,2)) AS pay_amount,
 
    -- ⑧ 数据脱敏：邮箱脱敏（保留@前2字符）
    CONCAT(SUBSTR(t.email, 1, 2), '****@',
        SPLIT(t.email, '@')[1]) AS email_masked,
 
    -- 时间标准化
    TO_TIMESTAMP(t.event_time, 'yyyy-MM-dd HH:mm:ss') AS event_ts,
    t.server_time AS server_ts
 
FROM (
    -- ① 去重子查询
    SELECT *, ROW_NUMBER() OVER(
        PARTITION BY event_id ORDER BY server_time ASC) AS rn
    FROM ods_user_payment_di
    WHERE dt = '${bizdate}'
      -- ⑦ 异常过滤
      AND event_id IS NOT NULL
      AND CAST(pay_amount AS DECIMAL(10,2)) > 0     -- 金额必须>0
      AND event_time >= '2020-01-01'                 -- 过滤异常早期时间
      AND event_time <= '${bizdate} 23:59:59'        -- 不能有未来时间
) t
LEFT JOIN dim_country ON t.country_code = dim_country.country_code
WHERE t.rn = 1;  -- 去重：只取第一条

清洗规则配置化

上面的SQL里，很多清洗规则（比如pay_type的映射关系、异常过滤的阈值）是硬编码的。一旦规则变了（比如新增了一种支付方式），就要改SQL、测试、上线。更好的做法是把规则配置化：

-- 方案：清洗规则放在配置表里，SQL动态引用
 
-- 配置表：dwd_clean_rules（存在MySQL/Hive中）
-- | rule_id | table_name       | field    | rule_type | rule_value      |
-- |---------|------------------|----------|-----------|-----------------|
-- | R001    | user_payment     | pay_type | mapping   | 1=credit_card   |
-- | R002    | user_payment     | pay_type | mapping   | 2=paypal        |
-- | R003    | user_payment     | amount   | range     | 0,999999        |
-- | R004    | user_payment     | email    | mask      | prefix:2,suffix |
 
-- DWD SQL中引用配置表做字段映射
SELECT
    t.event_id,
    COALESCE(mapping.mapped_value, 'other') AS pay_method
FROM ods_user_payment_di t
LEFT JOIN (
    -- 从配置表动态加载映射关系
    SELECT
        SPLIT(rule_value, '=')[0] AS original_value,
        SPLIT(rule_value, '=')[1] AS mapped_value
    FROM dwd_clean_rules
    WHERE table_name = 'user_payment'
      AND field = 'pay_type'
      AND rule_type = 'mapping'
) mapping ON t.pay_type = mapping.original_value;

配置化的价值：新增支付方式时，只需要在配置表INSERT一行，不需要改DWD的SQL。我们在某短剧出海公司，支付方式从4种增加到8种，全程没有修改DWD代码，只是在配置表加了4行数据。

面试高频追问

追问一：去重为什么用ROW_NUMBER而不是DISTINCT？

因为DISTINCT是按所有字段去重，但重复数据可能部分字段不同（比如两条相同event_id但server_time不同的记录）。ROW_NUMBER可以指定去重的key（event_id）和保留策略（保留server_time最早的一条），控制力更强。另外在Hive/MaxCompute里，DISTINCT会触发全数据Shuffle，而ROW_NUMBER配合PARTITION BY只在分区内排序，性能更好。

追问二：ODS层为什么不做清洗，非要多一层DWD？

核心原因是'可追溯'。ODS保留原始数据，如果DWD的清洗逻辑有bug，可以从ODS重跑修复。如果在ODS层就清洗了，原始数据就丢了，发现问题后只能找上游重推——而上游的数据可能已经过期被删除了。另一个原因是'关注点分离'——ODS只管安全落地，DWD专注数据质量，职责清晰，排查问题时知道该看哪一层。

追问三：清洗过程中被过滤掉的数据怎么处理？直接丢了吗？

不能直接丢。我们把被过滤的异常数据写入一张'垃圾桶表'（dwd_rejected_events），记录被过滤的原因（哪个规则命中的）。这些数据有两个用途：① 统计异常率——如果某天异常数据突然暴增，说明上游可能出了问题；② 排查数据丢失——如果业务方反馈某条数据查不到，可以先在垃圾桶表里找，看看是不是被清洗规则误杀了。

实战踩坑

在某短剧出海公司遇到过一次'清洗过度'的事故。我们的异常过滤规则有一条是'金额大于10000的订单视为异常过滤'——因为我们短剧的单笔消费一般在1-99美元之间。但有一天土耳其区域上线了'年度VIP'套餐，定价14999土耳其里拉（约500美元），折算成美元后低于10000，但我们的pay_amount字段存的是当地币种金额——14999里拉直接被过滤了。

结果土耳其区域当天的付费数据全部丢失，运营发现土耳其收入为0才开始排查。修复方案：① 紧急把过滤阈值从10000调到999999（临时放开）；② 从垃圾桶表恢复被误杀的数据；③ 长期方案是改成按美元等值金额过滤——先做币种转换，再判断是否异常。

-- 踩坑：过滤规则没考虑多币种
 
-- 错误写法（直接用原始金额判断）
WHERE pay_amount > 0 AND pay_amount < 10000  -- ❌ 14999里拉被过滤了
 
-- 正确写法（先转换成美元，再判断）
WHERE pay_amount > 0
  AND pay_amount * exchange_rate < 10000      -- ✅ 14999 * 0.033 = 495美元，正常
 
-- 更好的写法（规则配置化，不同币种不同阈值）
WHERE pay_amount > 0
  AND pay_amount < (
    SELECT threshold FROM dwd_clean_rules
    WHERE table_name = 'user_payment'
      AND field = 'pay_amount'
      AND rule_type = 'max_threshold'
      AND currency = t.currency       -- 按币种取不同阈值
  )

总结

DWD层清洗的完整动作是8项：去重、空值处理、格式标准化、字段映射、维度退化、类型转换、异常过滤、数据脱敏。面试时不要只说'去重和过滤'——要能列出完整的清洗动作，并给每个动作举一个具体的业务例子。更高级的回答是讲清洗规则的配置化管理和异常数据的处理机制（垃圾桶表），这展示了你在数据治理方面的思考深度。

DWD清洗完了，下一步是DWS汇总层——汇总什么、按什么粒度汇总、怎么避免过度建设？下一篇聊DWS层的设计方法论。

你们DWD层有多少条清洗规则？有没有遇到过清洗规则误杀数据的情况？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长