机场三字代码这件事，乍一看像是数据治理里最不起眼的那类活儿——不就是一张对照表嘛，IATA 代码三位的字母，跟城市、机场名一一对应，导进数据库建个索引就完事了。真上手做过的人都知道，这张表从"能查"到"能信"之间隔着一条很深的沟。我前后在两个数据团队里经手过航空主数据的建设，三字代码表每次都是绕不开的第一块砖，也每次都能暴露出建模和清洗上的一堆问题：同一个机场在不同系统里挂着不同的代码，历史代码和现行代码混在一张表里没人标注，城市和机场的层级关系被拍平成一张宽表，更别提那些因为航司基地调整、机场更名、代码复用而留下的脏数据。这篇东西就是把这几年踩过的坑和最后跑通的方案完整摊开讲一遍，从数据字典的原始形态一路推到可以作为业务主数据使用的稳定模型，中间涉及的表结构设计、清洗规则、校验逻辑和落地时的取舍，都会给到可以直接抄的细节。
1. 先搞清楚三字代码表到底在解决什么问题
很多人拿到这张表的第一反应是"建个字典表"，这个定位从一开始就偏了。字典表的核心诉求是"能查到"，主数据的核心诉求是"能作为业务事实被引用且长期稳定"。三字代码在航空业务链路里的角色远比字典重：它是航班计划、运价发布、航线收益、旅客行程、货运单、地面保障资源分配这些环节共同的连接键。一旦这个键不稳定，下游所有以它为外键的事实表都会跟着抖。
1.1 字典视角和主数据视角的差别在哪
用字典视角看，一张表有 code、name、city 三个字段就够了，查询的时候where code = 'XXX'能命中就行。用主数据视角看，问题立刻复杂起来：这个 code 是现行有效还是历史遗留？它对应的机场是否还在运营？同一个城市有多个机场时，城市维度和机场维度怎么拆？代码是否发生过变更、变更前后的映射关系是什么？这些问题不解决，下游做航线分析时把已停用机场的代码算进运力统计，结论就是错的。
我见过最典型的一个事故是某次航线收益复盘，分析师直接拿三字代码表 join 航班表做城市聚合，结果因为表里同时存在某城市新旧两个机场的代码且没有有效期字段，把已经转场停用的老机场数据也算进了当期，导致该城市吞吐量虚高了一截。问题不在分析师，在于这张表压根没承担起主数据该有的职责。
1.2 三字代码在业务链路里的真实引用点
把引用点列清楚，才能反推模型该长什么样。常见的引用场景大致这么几类：
| 引用场景 | 引用方式 | 对代码表的要求 |
|---|---|---|
| 航班计划与动态 | 以机场代码为外键 | 代码必须唯一且现行有效 |
| 航线与运力分析 | 机场代码聚合到城市 | 需要城市-机场层级关系 |
| 运价与规则发布 | 起降机场代码成对出现 | 需要代码有效性校验 |
| 旅客行程与中转 | 行程段机场代码 | 需要历史代码可追溯 |
| 货运单与地面保障 | 机场代码关联资源 | 需要机场属性（时区、跑道等） |
这张表不是孤立存在的，它上游连着航司和机场的官方发布，下游被十几个系统引用。建模时如果只按"查询方便"来设计，下游每个系统都得自己再补一层逻辑去处理有效性和层级，重复建设不说，口径还容易打架。
1.3 为什么不能直接拿现成的公开代码表用
公开渠道能拿到的三字代码表，最大的问题是缺少治理元数据。它们通常只给 code、机场名、城市名、国家这几个字段，没有有效期、没有代码状态、没有变更历史、没有城市与机场的显式层级。直接导入当主数据用，等于把一堆未经校验的原始数据当成了业务事实。更麻烦的是不同来源的公开表之间还存在冲突：同一个机场，A 来源写的是城市名，B 来源写的是机场所在行政区名，C 来源干脆把城市和机场合并成一个字段。这些冲突不解决，下游 join 出来的结果就是不可信的。
所以第一步的定位必须摆正：公开代码表只是原料，我们要做的是把它加工成带治理属性的主数据。这个认知转变决定了后面所有建模和清洗工作的方向。
2. 原始数据长什么样，脏在哪
在动手建表之前，我习惯先把原始数据完整过一遍，把脏数据的类型摸清楚。三字代码表的脏法很有特点，不是那种明显的乱码或空值，而是"看起来对但语义错"的隐蔽问题，这类问题最容易被忽略，也最容易在下游放大。
2.1 五类高频脏数据及其成因
按我处理过的几个数据源统计，问题主要集中在这么几类：
- 代码复用与历史遗留：某些三字代码在历史上被不同机场使用过，旧代码停用后新机场又启用了相同代码。原始表里往往只保留一条记录，看不出复用关系，直接导致历史航班数据被错误归属到新机场。
- 城市与机场字段混用：有的源把城市名填进机场名字段，有的把机场名填进城市字段，还有的把"城市名+机场名"拼在一起。这类问题在跨国数据源里尤其普遍，因为不同地区的命名习惯差异很大。
- 同一实体多条记录：同一个机场因为更名、翻译差异、数据源合并等原因出现多条记录，代码相同但名称不同，或者名称相同但代码不同（后者更危险，可能是代码录入错误）。
- 有效性缺失：没有任何字段标明机场是否在运营、代码是否现行有效。已关闭机场、规划中机场、军用转民用的机场全混在一起。
- 层级关系断裂：城市和机场的从属关系没有显式表达，多机场城市（一个城市有多个机场）的归属关系只能靠名称猜测。
2.2 用抽样比对把问题量化出来
光凭感觉说"数据脏"没有意义，得量化。我的做法是抽一批高频使用的代码（比如吞吐量前 100 的机场），拿两到三个独立来源做交叉比对，统计不一致率。实际操作中，这 100 个高频代码里通常会有 5 到 15 个存在名称或层级上的不一致，比例不算高，但这批恰恰是下游引用最频繁的，必须优先处理干净。
比对的时候我会建一张临时对照表，把每个来源的字段并排放，逐字段标差异：
-- 交叉比对临时表结构示意 CREATE TABLE tmp_code_compare ( code VARCHAR(3), src_a_name VARCHAR(128), src_b_name VARCHAR(128), src_c_name VARCHAR(128), src_a_city VARCHAR(64), src_b_city VARCHAR(64), diff_flag VARCHAR(32) -- 标记差异类型 );差异标记我一般分这么几档：NAME_DIFF（名称不一致）、CITY_DIFF（城市不一致）、MISSING（某源缺失）、DUP（重复记录）。跑完之后按 flag 分组统计，哪类问题占比高，清洗规则就优先往哪类倾斜。
2.3 一个容易被忽略的坑：字符编码与空白字符
这个坑不显眼但很致命。跨国数据源里经常混入全角空格、不间断空格（NBSP）、零宽字符，肉眼看不出来，但做字符串匹配和去重时会导致"看起来一样的两条记录匹配不上"。我吃过一次亏：两个来源的同一个机场名称，一个用了普通空格，一个用了 NBSP，去重脚本跑完发现没去重成功，排查了半天才定位到字符层面。
处理办法是在清洗入口统一做字符规范化：全角转半角、去除零宽字符、把各种空白字符统一替换成普通空格、首尾 trim。这一步放在所有清洗逻辑的最前面，属于"地基级"操作，不做的话后面所有基于字符串比对的规则都会失真。
3. 主数据模型怎么设计才扛得住下游引用
模型设计是整个工作的核心。我的原则是：维度拆干净、有效性显式化、变更可追溯。这三点做到了，下游引用才不会出乱子。下面这套模型是我在几个项目里迭代出来的，结构不算复杂，但每个字段都有明确的职责。
3.1 城市维度和机场维度必须拆开
最常见的错误设计是把城市和机场塞进一张表，用一行记录同时表达"这个机场属于这个城市"。单机场城市这么干没问题，但多机场城市立刻就出问题：一个城市有多个机场时，城市属性（比如城市名、城市所属区域）会在每个机场行里重复存储，一旦城市名变更就要改多行，容易改漏；更麻烦的是做城市级聚合时，得先对机场去重再聚合，逻辑绕。
正确的做法是拆成两张维度表：
-- 城市维度 CREATE TABLE dim_city ( city_id BIGINT PRIMARY KEY, city_code VARCHAR(8) NOT NULL, -- 内部城市编码 city_name_cn VARCHAR(64), city_name_en VARCHAR(128), country_code VARCHAR(2), region_code VARCHAR(8), valid_from DATE, valid_to DATE, status TINYINT -- 1现行 0停用 ); -- 机场维度 CREATE TABLE dim_airport ( airport_id BIGINT PRIMARY KEY, iata_code VARCHAR(3) NOT NULL, icao_code VARCHAR(4), airport_name_cn VARCHAR(128), airport_name_en VARCHAR(128), city_id BIGINT NOT NULL, -- 外键指向城市 airport_type TINYINT, -- 民用/军民合用/货运等 timezone VARCHAR(64), valid_from DATE, valid_to DATE, status TINYINT );拆开之后，城市级聚合直接走dim_city，机场级分析走dim_airport，层级关系通过city_id外键显式表达，不再依赖名称猜测。多机场城市的归属关系一目了然。
3.2 有效期和状态字段为什么不能省
valid_from、valid_to、status这三个字段是主数据的"生命线"。没有它们，表里就只有"当前快照"，历史查询和时点回溯全部做不了。举个实际场景：做去年同期航线对比时，如果某个机场在期间发生了代码变更或停用，没有有效期字段就没法正确还原去年那个时点的机场状态，对比结果直接失真。
我的处理方式是给每条记录都维护有效期区间，valid_to为空表示当前有效。代码变更时不覆盖原记录，而是把原记录的valid_to置为变更日期，同时插入一条新记录，valid_from设为变更日期。这样任何时点的查询都能通过valid_from <= 查询日期 < valid_to精确还原。
注意：有效期区间建议用左闭右开，即
valid_from <= d < valid_to，避免相邻区间在边界日期上重叠导致一条记录被查出两次。
3.3 代码变更历史单独建映射表
光靠有效期还不够，因为有些场景需要知道"这个代码以前是什么"。比如旅客行程里存的是历史代码，做数据修复时需要把历史代码映射到现行代码。这时候需要一张专门的变更映射表：
CREATE TABLE map_code_history ( old_code VARCHAR(3) NOT NULL, new_code VARCHAR(3) NOT NULL, change_type VARCHAR(16), -- RENAME/REUSE/TRANSFER change_date DATE, remark VARCHAR(256) );change_type区分变更类型很关键：RENAME是机场更名但代码不变（这种情况其实不需要映射，但记录变更历史有用），REUSE是代码被新机场复用，TRANSFER是机场转场导致代码归属变化。不同类型在下游修复时的处理逻辑不同，混在一起会出错。
3.4 给下游留一个稳定的查询视图
维度表拆得再干净，下游用起来如果还要自己 join 多层，照样会出问题。我的做法是在维度表之上再封一层宽视图，把常用的"代码+名称+城市+有效性"拼好，下游直接查视图，不用关心底层拆了几张表：
CREATE VIEW v_airport_current AS SELECT a.iata_code, a.airport_name_cn, a.airport_name_en, c.city_name_cn, c.city_name_en, c.country_code, a.timezone, a.status FROM dim_airport a JOIN dim_city c ON a.city_id = c.city_id WHERE a.status = 1 AND (a.valid_to IS NULL OR a.valid_to > CURRENT_DATE);这层视图的价值在于把"现行有效"这个最常用的过滤条件固化下来，下游不用每次都写一遍有效期判断，减少口径不一致的风险。需要历史数据的场景再单独提供带时间参数的查询接口。
4. 清洗规则怎么定，才能既干净又不误伤
清洗是整个流程里最考验经验的部分。规则定得太松，脏数据漏过去；定得太紧，正常数据被误删。我的经验是：规则分层、逐层收敛、每层可回滚。不要指望一条规则解决所有问题，而是把清洗拆成几个阶段，每个阶段只处理一类问题，处理完落一张中间表，方便回溯。
4.1 第一层：结构规范化
这一层只做机械性的格式统一，不涉及任何业务判断，所以可以放心大胆地跑：
- 字符规范化：全角转半角、去零宽字符、空白字符统一、首尾 trim。
- 代码字段统一大写、去空格。
- 名称字段去除多余标点、统一括号格式（中文括号转英文或反之，按团队规范定）。
- 日期字段统一格式，非法日期置空并打标。
这一层跑完，数据在"形态"上就整齐了，后面基于字符串比对的规则才可靠。我一般会把这一层的输出单独落一张stg_airport_norm表，作为后续所有清洗的基线。
4.2 第二层：去重与实体归并
去重不能简单按代码distinct，因为同一个代码可能对应多条记录（历史遗留），同一个实体也可能有多个代码（录入错误）。我的做法是分两步：
第一步按代码分组，组内如果名称完全一致，直接保留一条；如果名称不一致，进入人工复核队列，不自动合并。这一步保守一点，宁可留给人工也不自动拍板，因为代码相同名称不同的情况背后往往是代码复用或录入错误，自动合并风险太高。
第二步按名称相似度找"疑似同一实体不同代码"的记录。相似度用编辑距离加关键词匹配组合判断，比如名称高度相似但代码不同的，标记为疑似重复，同样进人工队列。这一步我实测下来误报率不低，所以只做标记不做自动处理。
4.3 第三层：业务规则校验
这一层开始涉及业务判断，规则要写得明确且可解释：
| 校验规则 | 处理方式 | 说明 |
|---|---|---|
| 代码长度必须为3且全大写字母 | 不合规记录隔离 | 硬性格式要求 |
| 机场必须关联到有效城市 | 无法关联的隔离 | 层级完整性 |
| 现行机场的有效期不得为空 | 补默认值并打标 | 有效性完整性 |
| 同一代码现行记录唯一 | 多条则隔离复核 | 唯一性约束 |
| 城市下至少有一个现行机场 | 否则城市标记异常 | 反向完整性 |
隔离出来的记录不删除，单独落一张err_airport表，带上隔离原因和隔离时间。这样既保证了主表的干净，又保留了问题数据的追溯线索。我特别反对直接delete脏数据，删了就再也查不到问题根源了。
4.4 清洗结果怎么验证
清洗跑完不能只看"处理了多少条"，得验证结果是否可信。我的验证清单一般是这么几项：
- 记录数守恒：原始记录数 = 主表记录数 + 隔离记录数 + 合并掉的重复数，对不上就说明有记录凭空消失或多出来。
- 高频代码抽查：抽吞吐量前 50 的代码，人工核对名称、城市、有效性是否正确。
- 下游 join 测试：拿一张真实的航班表去 join 清洗后的代码表，看匹配率是否达到预期（通常要求 99% 以上），未匹配的记录逐条分析原因。
- 时点回溯测试：随机选几个历史日期，验证按有效期查询能否正确还原当时的机场状态。
这套验证跑下来，基本能把清洗的遗漏问题兜住。我印象最深的一次是 join 测试发现匹配率只有 96%，排查下来是一批代码在原始表里带了尾部空格，第一层规范化没覆盖到某个数据源的特定格式，补了规则之后才达标。这种问题不做 join 测试根本发现不了。
5. 落地时那些文档里不会写的经验
模型和规则讲完了，最后说几个真正上手才会遇到的问题，这些在正式文档里基本看不到，但每一个都能让你多花好几天。
5.1 别指望一次清洗到位，要留迭代空间
我第一版清洗脚本写得很"完美"，规则一大堆，结果跑完发现误伤了不少正常数据，回滚重来。后来学乖了：第一版只做结构规范化和最基础的校验，先让数据能跑通下游，然后根据下游反馈的问题逐步加规则。清洗是个持续迭代的过程，不是一次性工程。每加一条规则，都要拿历史数据回跑一遍，确认没有误伤再上线。
5.2 人工复核队列一定要有，而且要有人真的去看
自动规则再完善，也总有边界情况处理不了。我坚持保留人工复核队列，但更关键的是得有人定期清队列。见过太多项目建了复核队列却没人看，问题数据越积越多，最后队列形同虚设。实际操作中，我会给复核队列设一个积压阈值，超过阈值就触发提醒，强制安排时间清理。复核的结论也要回写到规则里，能自动化的尽量自动化，减少重复人工。
5.3 和下游对齐口径比清洗本身更重要
有次清洗做完，数据质量明显提升，但下游反馈"数据不对"。排查半天发现是口径问题：下游一直把城市名当机场名用，清洗后两者拆开了，下游的旧逻辑取错了字段。这不是数据的问题，是沟通的问题。所以每次模型变更或清洗规则调整，我都会提前和主要下游系统对齐，把字段含义、层级关系、有效性判断方式讲清楚，必要时提供迁移指引。数据治理一半是技术，一半是沟通，这话不假。
5.4 版本管理要跟上
主数据是会变的，机场会新增、代码会调整、城市归属会变化。每次变更都要有版本记录，能回答"某个时间点的数据长什么样"。我的做法是维度表本身用有效期管理，同时维护一张变更日志表，记录每次变更的操作人、时间、变更内容和原因。出问题时能快速定位是哪次变更引入的，回滚也有依据。
5.5 监控比修复更省事
数据上线后不能就不管了，得有监控。我一般会配几个基础监控项：每日新增隔离记录数（突增说明上游数据源出问题了）、下游 join 匹配率（下降说明代码表可能缺了记录）、人工复核队列积压量（持续增长说明规则需要优化）。这几个指标配上告警，大部分问题能在影响下游之前被发现，比事后修复省事得多。
这套东西跑下来，三字代码表才算真正从一张"能查的字典"变成了"能信的主数据"。回头看，技术难度其实不算高，难的是把治理意识贯穿到建模、清洗、验证、监控的每个环节，并且在迭代中不断和下游对齐。数据治理这活儿，细节决定成败，耐心决定质量。
