如何使用Snowflake清理存在多类数据问题的Location表？

一、先搭好权威参考数据基础 #

要解决匹配类问题，首先得有靠谱的层级参考表：

• 导入ISO标准国家列表（包含全称、缩写、常用别名）到Snowflake，命名为country_reference；
• 再导入对应国家的州/省、地区、城市层级数据，比如state_reference（关联country_id）、city_reference（关联state_id），确保数据是最新的官方版本。

二、搞定列值错位问题（国家列填城市这类情况） #

先交叉匹配所有列内容与参考表，识别放错位置的值：

1. 用JAROWINKLER_SIMILARITY函数，把每个列的内容和国家参考表比对，找出相似度高的内容，将其移到country列，同时标记原列的错误值；
2. 确定国家后，再把其他列中属于该国家州/城市的内容，移到对应列。
   示例代码片段：

-- 识别country列中的城市值，并修正到city列
UPDATE Location
SET city = country, country = NULL
WHERE EXISTS (
    SELECT 1 FROM city_reference cr
    WHERE JAROWINKLER_SIMILARITY(UPPER(Location.country), UPPER(cr.city_name)) >= 0.9
);

三、修正拼写错误 #

针对每个层级用模糊匹配修正：

• 国家层：和country_reference做模糊匹配，设置相似度阈值（比如0.8），替换拼写错误的名称；
• 州/城市层：先匹配到正确国家，再在对应国家的参考数据里做模糊匹配，避免跨国家匹配错误。
  示例：

SELECT
    studentId,
    COALESCE(ref.country_name, l.country) AS cleaned_country
FROM Location l
LEFT JOIN country_reference ref
    ON JAROWINKLER_SIMILARITY(UPPER(l.country), UPPER(ref.country_name)) >= 0.8;

四、处理缺失值和无法匹配的记录 #

1. 缺失值：如果知道下级信息（比如已知城市），通过参考表关联补全上级（州、国家）；如果只有上级信息，下级留空或标记为待补充，按业务需求处理；
2. 无法匹配的记录：把这类数据单独导出到location_unmatched表，包含studentId和所有原始列值，后续安排人工审核，或结合第三方数据补充。

五、百万级数据的批量优化 #

• 用Snowflake的MERGE语句批量更新，避免单行操作拖慢效率；
• 给Location表按country或studentId范围设置分区键，加速查询与匹配；
• 把清理逻辑封装成视图或存储过程，重复执行更便捷。
  批量更新示例：

-- 先创建清理后的目标表
CREATE TABLE cleaned_location LIKE Location;

-- 用MERGE批量写入清理后的数据
MERGE INTO cleaned_location ct
USING (
    SELECT
        l.studentId,
        COALESCE(cr.country_name, l.country) AS cleaned_country,
        COALESCE(sr.state_name, l.state) AS cleaned_state,
        COALESCE(dr.district_name, l.district) AS cleaned_district,
        COALESCE(cityr.city_name, l.city) AS cleaned_city
    FROM Location l
    LEFT JOIN country_reference cr ON JAROWINKLER_SIMILARITY(UPPER(l.country), UPPER(cr.country_name)) >= 0.8
    LEFT JOIN state_reference sr ON UPPER(l.state) = UPPER(sr.state_name) AND sr.country_id = cr.country_id
    LEFT JOIN district_reference dr ON UPPER(l.district) = UPPER(dr.district_name) AND dr.state_id = sr.state_id
    LEFT JOIN city_reference cityr ON UPPER(l.city) = UPPER(cityr.city_name) AND cityr.district_id = dr.district_id
) cl
ON ct.studentId = cl.studentId
WHEN NOT MATCHED THEN INSERT (studentId, country, state, district, city)
VALUES (cl.studentId, cl.cleaned_country, cl.cleaned_state, cl.cleaned_district, cl.cleaned_city);

六、长期维护 #

• 定期更新参考数据集，覆盖新增地区、改名城市等情况；
• 给入库数据加校验规则，插入前先做层级匹配，发现错位或无法匹配的直接打标记，提前拦截脏数据。

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