Snowflake物化表列源表溯源方法咨询及valid_from/valid_to字段创建需求

一、获取物化表列的源表/源列映射（以CUSTOMERS.customer_name为例） #

Snowflake的INFORMATION_SCHEMA.COLUMNS和SNOWFLAKE.ACCOUNT_USAGE.COLUMNS不会自动存储列的血缘信息，需根据表的创建方式分别处理：

1. 目标表是物化视图（Materialized View） #

物化视图的定义会被记录在系统视图中，可通过解析定义SQL提取源映射：

步骤1：获取物化视图定义

SELECT definition
FROM INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE table_name = 'CUSTOMERS' 
  AND table_schema = '<你的Schema名>' 
  AND table_catalog = '<你的数据库名>';

步骤2：自动解析源映射（生成表格结果）

使用正则表达式提取指定列的源信息：

WITH mv_def AS (
    SELECT definition
    FROM INFORMATION_SCHEMA.MATERIALIZED_VIEWS
    WHERE table_name = 'CUSTOMERS'
)
SELECT
    'customer_name' AS current_name,
    -- 提取AS前的源列名
    REGEXP_SUBSTR(definition, '([a-zA-Z0-9_]+)\\s+AS\\s+customer_name', 1, 1, 'i', 1) AS source_name,
    -- 提取FROM后的源表名
    REGEXP_SUBSTR(definition, 'FROM\\s+([a-zA-Z0-9_\\.]+)', 1, 1, 'i', 1) AS source_table
FROM mv_def;

执行后得到的结果表：

2. 目标表是普通表（如CTAS/INSERT创建） #

普通表无内置血缘记录，需通过以下两种方式解决：

方法A：查询历史创建SQL

从查询历史中找到表的创建语句，手动或自动解析：

SELECT query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%CREATE TABLE CUSTOMERS%'
  AND query_type = 'CREATE_TABLE'
ORDER BY start_time DESC
LIMIT 1;

拿到SQL后，可复用上述正则方法提取源映射。

方法B：自定义元数据管理表

提前创建血缘记录表，在创建业务表时同步插入映射信息：

-- 创建元数据表
CREATE TABLE COLUMN_LINEAGE (
    current_table VARCHAR(100),
    current_name VARCHAR(100),
    source_table VARCHAR(100),
    source_name VARCHAR(100)
);

-- 创建CUSTOMERS表时同步插入映射
INSERT INTO COLUMN_LINEAGE VALUES ('CUSTOMERS', 'customer_name', 'SRC_CUSTOMERS', 'src_cust_name');

后续直接查询元数据表获取结果：

SELECT current_name, source_name, source_table
FROM COLUMN_LINEAGE
WHERE current_table = 'CUSTOMERS' AND current_name = 'customer_name';

二、从源表创建valid_from/valid_to字段（SCD2实现） #

若需基于源表构建带有效期的表（支持缓慢变化维度），可通过窗口函数实现：

示例SQL（创建普通表） #

假设源表SRC_CUSTOMERS包含src_cust_id（主键）、src_cust_name、src_record_time（记录更新时间戳）：

CREATE OR REPLACE TABLE CUSTOMERS AS
SELECT
    src_cust_name AS customer_name,
    -- 其他业务列...
    DATE_TRUNC('DAY', src_record_time) AS valid_from,
    -- 下一条记录的时间作为当前记录的失效时间，无后续记录则设为9999-12-31
    NVL(LEAD(DATE_TRUNC('DAY', src_record_time)) OVER (PARTITION BY src_cust_id ORDER BY src_record_time), '9999-12-31'::DATE) AS valid_to
FROM SRC_CUSTOMERS
ORDER BY src_cust_id, src_record_time;

物化视图版本 #

若需创建带有效期的物化视图：

CREATE OR REPLACE MATERIALIZED VIEW CUSTOMERS AS
SELECT
    src_cust_name AS customer_name,
    DATE_TRUNC('DAY', src_record_time) AS valid_from,
    NVL(LEAD(DATE_TRUNC('DAY', src_record_time)) OVER (PARTITION BY src_cust_id ORDER BY src_record_time), '9999-12-31'::DATE) AS valid_to
FROM SRC_CUSTOMERS;

注：如果源表无记录时间戳，初始valid_from可设为CURRENT_TIMESTAMP，后续通过MERGE语句更新valid_to字段。

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