在Snowflake中用SQL校验美国地址真实可投递性

看起来干净的地址仍然无法投递。以下是如何捕获它们

如何在 Snowflake 中使用 SQL 验证和标准化美国地址

一个来源写的是“26 S Chestnut St.”。另一个来源写的是“26 s chstnt, vntura, californa”，邮编也错了。两者都是 Ventura 的同一栋建筑，而两者都会破坏连接、去重规则或邮寄任务。

SQL 中的格式化规则可以让字符串看起来相似。它们无法告诉你一个地址是否是真实的投递点，或者是否缺少承运人所需的套房号。这需要根据邮政参考数据进行验证，而在大多数技术栈中，这意味着要导出、使用 API 客户端并重新导入来维护。

在本教程中，你将使用 SQL 提交地址进行验证，查看结果，并决定每条记录如何处理。你将：

1. 从 Marketplace 安装 Service Objects 的 US Address Validation and Verification for Snowflake 应用。
2. 将你的地址列整理成应用期望的输入格式，而不更改你的源表。
3. 用一次 CALL 验证一批记录。
4. 读取结果并用 SQL 路由每条记录。

下面的每张截图和结果都来自 Snowsight 工作表中的一次真实运行。

架构图

该应用安装到你的 Snowflake 账户中，并公开一个存储过程。你向它传递一个 SELECT 语句和一个输出表。它读取这些行，将地址字段发送到 Service Objects API 进行验证，并将结果写入你指定的表中。从那里开始，结果就是普通的 Snowflake 数据。

外部访问集成是数据离开你账户的唯一一点。该应用通过一个你授予和撤销的外部访问集成将地址数据发送到 Service Objects API，并且不保留任何内容。数据在传输过程中通过 HTTPS 加密。这个边界值得一开始就向你的安全审查人员展示。

为什么格式化规则不够

REPLACE(address, ‘Street’, ‘St’) 可以处理可预测的差异。它无法回答决定记录下一步如何处理的问题：

Delivery Point Validation (DPV) 是回答这些问题的信号。它告诉你一个地址是否是公认的投递点，并区分“已确认”、“有效建筑、错误单元”和“有效建筑、缺失单元”。这些在管道中是不同的操作，因此本教程后面的路由逻辑是围绕 DPV 代码构建的。

第 1 步：安装应用并激活试用

1. 打开 Snowflake Marketplace 上的 US Address Validation and Verification for Snowflake 列表，然后选择 Try Now。
2. Snowflake 显示应用所需的权限：CREATE EXTERNAL ACCESS INTEGRATION，用于调用 Service Objects API。选择 Agree & continue。
3. 在 Options 下，命名应用程序。下面的示例使用 AV3。然后选择 Start trial。
4. 安装完成后，在 External connections 屏幕上查看外部访问集成，然后选择 Proceed to app。
5. 在应用中，打开 Licensing 选项卡，输入你的联系方式，然后选择 Activate License。状态会变为试用计划，显示你的交易限制和到期日期。

免费试用期为 90 天或 1,000 条记录，以先到者为准，无需信用卡，也不会自动扣费。

Snowflake 试用账户的一个注意事项。外部访问在 Snowflake 自己的试用账户上受到限制，仅批准权限并不能解除这一限制。请使用付费账户，或先请 Snowflake 在你的试用账户上启用外部访问。

第 2 步：整理输入

该过程期望按此顺序提供七列。每一列都必须出现在你的 SELECT 中，即使该记录没有值。

你的 CRM 不会使用这些名称。与其修改源表，不如创建一个视图来映射你的列：

CREATE OR REPLACE VIEW MY_DB.PUBLIC.CUSTOMERS_FOR_VALIDATION AS
SELECT
  CUSTOMER_ID     AS CONSUMER_ROW_ID,
  COMPANY_NAME    AS BUSINESSNAME,
  STREET_ADDRESS  AS ADDRESS,
  UNIT            AS ADDRESS2,
  CITY,
  STATE,
  ZIP_CODE        AS POSTALCODE
FROM MY_DB.PUBLIC.CUSTOMERS;

要在没有真实数据的情况下跟随操作，请创建一个包含本文所用四条记录的测试表：

CREATE OR REPLACE TABLE MY_DB.PUBLIC.TEST_ADDRESSES (
  CONSUMER_ROW_ID NUMBER,  BUSINESSNAME VARCHAR,
  ADDRESS VARCHAR,         ADDRESS2 VARCHAR,
  CITY VARCHAR,            STATE VARCHAR,
  POSTALCODE VARCHAR
);

INSERT INTO MY_DB.PUBLIC.TEST_ADDRESSES VALUES
  (1, NULL, '26 S Chestnut', NULL, 'Ventura', 'CA', '93033'),
  (2, NULL, '26 s chstnt', NULL, 'vntura', 'californa', '93101'),
  (3, NULL, '908 N 11th St', NULL, NULL, NULL, '58501'),
  (4, NULL, '10000 E Cota St', NULL, NULL, NULL, '93101');

第 3 步：用一次调用进行验证

该过程接受两个参数：选择记录用的 SELECT，以及结果表的完全限定名称。在 SELECT 内部也使用完全限定表名，如示例所示。对第一个参数使用美元引号可以避免在其内部转义引号：

CALL AV3.APP_CODE.AV3_GET_BEST_MATCHES(
  $$SELECT CONSUMER_ROW_ID, BUSINESSNAME, ADDRESS, ADDRESS2,
           CITY, STATE, POSTALCODE
    FROM MY_DB.PUBLIC.TEST_ADDRESSES$$,
  'MY_DB.PUBLIC.AV3_RESULTS'
);

该调用返回运行的单行摘要。以下是我们 36 条记录测试的结果：

结果会准确落入你指定的表中。由于输入是一个查询，你可以控制范围和消耗：先验证少量记录，然后验证生产数据样本，最后验证全集。

第 4 步：读取结果

SELECT CONSUMER_ROW_ID, ADDRESS1, ADDRESS2, CITY, STATE, ZIP,
       DPV, DPV_DESC, CORRECTIONS_DESC, IS_RESIDENTIAL,
       ERROR_TYPE, ERROR_DESC
FROM MY_DB.PUBLIC.AV3_RESULTS
ORDER BY CONSUMER_ROW_ID;

以下是第 2 步中四条记录返回的内容：

有两件事值得注意。拼错的第二条记录解析到与第一条相同的投递点，因此两者现在匹配。而第三条记录到达时只有街道和邮政编码，返回时已填入城市、州和 ZIP+4。

结果表的列比这更多。以下是你可以据以构建规则的典型列：

DPV 是一个文本列。其记录值为：

第 5 步：用 SQL 路由记录

应用生成信号。你的团队决定每个信号的含义。以下是一种将其编码为视图的方法，优先处理错误，因为它们不带 DPV 值：

CREATE OR REPLACE VIEW MY_DB.PUBLIC.ADDRESS_ROUTING AS
SELECT
  r.CONSUMER_ROW_ID,
  r.ADDRESS1, r.ADDRESS2, r.CITY, r.STATE, r.ZIP,
  r.DPV, r.DPV_DESC, r.CORRECTIONS_DESC, r.ERROR_DESC,
  CASE
    WHEN r.ERROR_TYPE IS NOT NULL THEN 'FIX_AT_SOURCE'
    WHEN r.DPV = '1'              THEN 'ACCEPT'
    WHEN r.DPV IN ('3', '4')      THEN 'NEEDS_UNIT'
    ELSE                               'INVESTIGATE'
  END AS ROUTE
FROM MY_DB.PUBLIC.AV3_RESULTS r;

快速计数可以告诉你每个队列有多少工作：

SELECT ROUTE, COUNT(*) AS RECORDS
FROM MY_DB.PUBLIC.ADDRESS_ROUTING
GROUP BY ROUTE
ORDER BY RECORDS DESC;

对于审核，连接回源表，以便审核人员看到客户输入的内容与验证返回的内容并列。原始值永远不会被覆盖：

SELECT
  c.CUSTOMER_ID,
  c.STREET_ADDRESS  AS ORIGINAL_ADDRESS,
  r.ADDRESS1        AS VALIDATED_ADDRESS,
  COALESCE(r.ERROR_DESC, r.DPV_DESC) AS REASON,
  r.ROUTE
FROM MY_DB.PUBLIC.CUSTOMERS c
JOIN MY_DB.PUBLIC.ADDRESS_ROUTING r
  ON r.CONSUMER_ROW_ID = c.CUSTOMER_ID
WHERE r.ROUTE <> 'ACCEPT';

界线的划定取决于工作负载。一次邮寄任务可能会扣留除 ACCEPT 之外的所有记录。客户主数据作业可能会接受 NEEDS_UNIT 记录进行匹配，同时标记它们以便外联。

在生产环境中运行

• 只验证发生变化的内容。 输入是一个查询，因此可以过滤它。本次运行只提取过去一天创建或编辑的记录：

CALL AV3.APP_CODE.AV3_GET_BEST_MATCHES(
  $$SELECT v.*
    FROM MY_DB.PUBLIC.CUSTOMERS_FOR_VALIDATION v
    JOIN MY_DB.PUBLIC.CUSTOMERS c
      ON c.CUSTOMER_ID = v.CONSUMER_ROW_ID
    WHERE c.UPDATED_AT >= DATEADD(day, -1, CURRENT_TIMESTAMP())$$,
  'MY_DB.PUBLIC.AV3_RESULTS_DAILY'
);

• 保留稳定的行标识符。每个结果都通过 CONSUMER_ROW_ID 关联回其来源，因此请使用在多次运行之间不会变化的键。
• 将结果存储在源数据旁边，而不是覆盖它。保留原始值使得可以审计变更、向客户解释变更，并进行回滚。
• 为每个结果指定一个去向。错误、缺少单元号和无效单元号在真实数据中都很常见。在上线之前，先确定每一种结果分别去哪里。
• 了解验证不会告诉你什么。有效的投递点确认该地址存在且可以接收邮件。它并不确认谁住在那里，也不确认客户是否已经搬走。
• 与安全团队一起审查边界。带你的审查人员走一遍外部访问集成、跨越该边界的地址字段，以及如何撤销它。

后续步骤

运行上面的四条测试记录，然后再运行几百条你自己的记录，覆盖干净地址、缺少单元号和已知的问题行。在决定路由视图应如何处理每种结果之前，先检查 DPV 值和错误的分布情况。

该应用已在 Snowflake Marketplace 上架，名称为 US Address Validation and Verification for Snowflake。

看起来干净的地址仍然无法完成投递。以下是如何在 Snowflake 中捕获它们 最初发表于 Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science（Medium），人们正在那里通过高亮和回应这个故事来继续对话。