如何在Snowflake中清洗规范化Salesforce LEAD表的JOB TITLE字段？

25万行的量级在Snowflake里做全字段清洗完全不需要额外外接工具，全链路在数仓内完成即可，稳定性和执行效率都有保障，按下面三步做基本能覆盖95%以上的同名异写问题：

第一步：基础格式归一，先消弭无意义的格式差异 #

这一步可以100%解决你举的空格、标点、大小写差异问题，不需要任何人工配置，全靠内置函数就能跑：

• 先统一转小写，用lower("JOB TITLE")抹除所有大小写带来的差异，比如Senior Analyst、SENIOR ANALYST先统一成小写格式
• 清除所有非字母、非空格的特殊字符，用regexp_replace(上一步结果, '[^a-z\\s]', '')把职位名里的点、斜杠、特殊符号全删掉，比如sr. analyst处理后就变成sr analyst
• 合并连续空格并去除首尾空白，用regexp_replace(上一步结果, '\\s+', ' ')把多个连续空格合并成单个，再套一层trim()去掉首尾空格，这步走完你举的sr analyst、sr analyst、sr. analyst就会全部统一成sr analyst

第二步：缩写/同义词映射替换，解决称谓差异问题 #

格式统一后，剩下的差异基本都是缩写、同义词带来的，比如sr和senior、mgr和manager、vp和vice president这类，用映射表解决最稳妥，不会出现误匹配：

• 先建一张轻量的token映射维表job_title_token_mapping，就两列：raw_token存原始缩写/别称，standard_token存对应的标准词，一开始不用追求全覆盖，先把Top100高频的职位相关缩写录进去就行，比如：
  • sr → senior
  • jr → junior
  • mgr → manager
  • eng → engineer
  • acct → account
• 把第一步清洗完的职位名按空格拆成单个词，逐个匹配映射表替换成标准词，再重新拼接成完整的标准职位名，核心参考SQL如下：

with base_clean as (
    select
        ID,
        "JOB TITLE" as raw_job_title,
        trim(regexp_replace(regexp_replace(lower("JOB TITLE"), '[^a-z\\s]', ''), '\\s+', ' ')) as format_clean_title
    from SALESFORCE.LEAD
),
token_matched as (
    select
        b.ID,
        b.raw_job_title,
        b.format_clean_title,
        listagg(coalesce(m.standard_token, t.value), ' ') within group (order by t.index) as standard_job_title
    from base_clean b,
    lateral split_to_table(b.format_clean_title, ' ') t
    left join job_title_token_mapping m 
        on t.value = m.raw_token
    group by b.ID, b.raw_job_title, b.format_clean_title
)
select * from token_matched;

这步跑完，sr analyst就会自动统一成senior analyst，和原始的senior analyst完全对齐。

第三步：模糊匹配兜底，补全剩余的拼写误差 #

前两步跑完，至少80%的职位名都能完成标准化，剩下的问题基本是拼写错误、冷门缩写导致的，用Snowflake内置的相似度函数处理即可，不需要上复杂的NLP模型：

• 先把已经确认的标准职位名整理成基准列表，用editdistance()计算待匹配职位和基准职位的编辑距离，编辑距离≤1的（比如少打一个字母、多打一个字母的笔误）直接自动归一
• 编辑距离在2-3区间的，再用jaccard_index()计算两个职位名拆词后的token集合相似度，相似度≥0.8的自动归到对应标准职位
• 剩下相似度达不到阈值的职位，单独导出成待确认清单，每周批量补一次映射表就行，25万行全量跑一次相似度计算在Snowflake里也就几分钟，算力成本几乎可以忽略。

落地注意点 #

• 清洗生成的标准职位名存在单独的新列里，不要覆盖原始的JOB TITLE字段，保留原始值方便后续回溯校验
• 后续新增的LEAD数据直接套上面的清洗逻辑即可，遇到映射表没有的新职位自动进待确认队列，维护成本很低
• 不要一开始就上大模型、第三方NLP服务，这类方案成本高、结果不可控，对于职位名这种规则性很强的字段，上面的方案准确率能到98%以上，性价比最高

内容的提问来源于stack exchange，提问作者greeble