原始数据清洗与 schema 契约

目标

完成一个完整周期：对完全由字符串构成的源表进行数据剖析，规范化格式，并将数据分别加载到清洗表和拒绝表中。

为什么重要

数据流水线中最危险的事故不是失败，而是静默丢失。如果直接跳过加载失败的行，系统不会报任何错误，只是数据量略微减少。几个月后才发现时，就无法追查从何时起遗漏了哪些数据。

因此，清洗流水线必须遵守守恒定律。清洗记录数与拒绝记录数之和必须恰好等于源数据记录数，不能有任何行既不属于清洗表，也不属于拒绝表。将这项验证放进流水线后，数据一旦丢失就会立即暴露。

还必须同时记录拒绝原因。没有原因就被丢弃的行无法恢复。也请记住，没有值与数值 0 并不相同。一旦把空金额填成 0，“信息缺失”这一事实本身就被抹去了。

步骤

目标是 staging.orders_raw 表。

1. 创建 t_raw_profile 表。列为 col, bad_count，且必须恰好有 3 行。
   • customer_email——值为空（NULL）的行数
   • amount——值为 NULL 或仅包含空格的行数
   • order_date——格式不是 YYYY-MM-DD 的行数
2. 创建 v_raw_dates 视图。列为 raw_id, order_date_parsed（date 类型），所有行都必须成功解析。日期格式有四种：YYYY-MM-DD, MM/DD/YYYY, YYYY.MM.DD, YYYYMMDD。
3. 创建 v_raw_amount 视图。列为 raw_id, amount_num（numeric 类型）；删除货币符号和千位分隔逗号，并将空字符串保留为无值，而不是 0。
4. 创建 v_raw_status 视图。列为 raw_id, status_norm；删除首尾空格，并统一转换为小写。结果应有 3 种。
5. 创建 orders_clean 表。列依次为 raw_id, order_ref, customer_email, order_date, amount, status；只加载有电子邮件且金额不为空的行，并使用正确的数据类型。
6. 创建 orders_reject 表。列为 raw_id, reason；第 5 步中被排除的行连同原因一起写入该表。原因不得为空。
7. 确认 orders_clean 的记录数与 orders_reject 的记录数之和等于 staging.orders_raw 的记录数，并确认没有任何行同时进入两个表。
8. 创建 schema_contract 表。列为 column_name, data_type，原样保存 orders_clean 的列及其类型。

参考

• 正则表达式匹配：order_date ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
• 按格式解析：to_date(order_date, 'MM/DD/YYYY')
• 将空字符串转为无值：nullif(btrim(값), '')
• 查询目录：SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders_clean'
• 常见错误 1：空字符串并非 NULL，因此只使用 IS NULL 无法找出它。
• 常见错误 2：把 MM/DD/YYYY 按 DD/MM/YYYY 读取时，在日期不超过 12 日的情况下会悄然出错，甚至不会报错。

统计源数据中的缺陷

创建 t_raw_profile 表。列为 col, bad_count，且必须恰好有 3 行。

• customer_email——值为空（NULL）的行数
• amount——值为 NULL 或仅包含空格的行数
• order_date——格式不是 YYYY-MM-DD 的行数

不同列对缺陷的定义不同。请注意，空字符串不是 NULL。

解析四种日期格式

创建 v_raw_dates 视图。列为 raw_id, order_date_parsed（date 类型），所有行都必须成功解析。日期格式有四种：YYYY-MM-DD, MM/DD/YYYY, YYYY.MM.DD, YYYYMMDD。

先用正则表达式判断格式，再分别应用不同的解析规则。其中一种格式的月和日顺序不同。

删除金额中的符号和逗号

创建 v_raw_amount 视图。列为 raw_id, amount_num（numeric 类型）；删除货币符号和千位分隔逗号，并将空字符串保留为无值，而不是 0。

删除字符后再转换为数值。空字符串必须保留为无值，不能改成 0。

规范化状态值

创建 v_raw_status 视图。列为 raw_id, status_norm；删除首尾空格，并统一转换为小写。结果应有 3 种。

删除首尾空格并统一大小写后，确认状态减少为多少种。

加载到清洗表

创建 orders_clean 表。列依次为 raw_id, order_ref, customer_email, order_date, amount, status；只加载有电子邮件且金额不为空的行，并使用正确的数据类型。

排除没有电子邮件或金额为空的行。各列必须采用正确的数据类型。

连同原因保留在拒绝表中

创建 orders_reject 表。列为 raw_id, reason；第 5 步中被排除的行连同原因一起写入该表。原因不得为空。

必须记录丢弃原因。原因为空时，日后将无人能够恢复这些数据。

确认守恒定律

确认 orders_clean 的记录数与 orders_reject 的记录数之和等于 staging.orders_raw 的记录数，并确认没有任何行同时进入两个表。

清洗记录与拒绝记录之和必须等于源数据记录数，且不能有任何行同时进入两边。

保留模式契约

创建 schema_contract 表。列为 column_name, data_type，原样保存 orders_clean 的列及其类型。

从目录中直接读取列名和类型并写入表中更加安全。