基于BigQuery的SCD数据集市设计：维度表独立于事实表插入方案咨询

核心设计思路 #

采用代理键+SCD Type 2的经典维度建模模式，通过业务键与代理键解耦，实现维度表与事实表的独立写入，适配流式数据提前到达的场景。

具体表结构设计 #

事实表（以销售场景为例） #

CREATE TABLE `project.dataset.fact_sales` (
  sales_surrogate_key STRING NOT NULL PRIMARY KEY, -- 可选，用UUID或自增ID作为事实主键
  customer_surrogate_key STRING NOT NULL, -- 关联客户维度代理键
  product_surrogate_key STRING NOT NULL, -- 关联产品维度代理键
  sales_amount NUMERIC NOT NULL, -- 核心度量值
  sales_timestamp TIMESTAMP NOT NULL, -- 业务发生时间
  load_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() -- 数据加载时间
)
PARTITION BY DATE(sales_timestamp)
CLUSTER BY customer_surrogate_key, product_surrogate_key;

客户维度表（SCD Type 2） #

CREATE TABLE `project.dataset.dim_customer` (
  customer_surrogate_key STRING NOT NULL PRIMARY KEY, -- 代理键，UUID或自增ID
  customer_business_key STRING NOT NULL, -- 业务唯一键（如客户ID）
  customer_name STRING,
  customer_segment STRING,
  valid_from TIMESTAMP NOT NULL, -- 版本生效时间
  valid_to TIMESTAMP NOT NULL DEFAULT TIMESTAMP('9999-12-31'), -- 版本失效时间
  is_current BOOLEAN NOT NULL DEFAULT TRUE, -- 当前版本标记
  load_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
)
CLUSTER BY customer_business_key, is_current;

注：给customer_business_key加唯一约束，确保同一业务键的当前版本唯一：

CREATE UNIQUE INDEX idx_customer_business_current ON `project.dataset.dim_customer`(customer_business_key) WHERE is_current = TRUE;

产品维度表（SCD Type 2） #

CREATE TABLE `project.dataset.dim_product` (
  product_surrogate_key STRING NOT NULL PRIMARY KEY, -- 代理键
  product_business_key STRING NOT NULL, -- 业务唯一键（如产品SKU）
  product_name STRING,
  product_category STRING,
  valid_from TIMESTAMP NOT NULL,
  valid_to TIMESTAMP NOT NULL DEFAULT TIMESTAMP('9999-12-31'),
  is_current BOOLEAN NOT NULL DEFAULT TRUE,
  load_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
)
CLUSTER BY product_business_key, is_current;

同样给product_business_key加当前版本唯一约束。

流式数据写入逻辑（Dataflow + BigQuery） #

维度表独立写入逻辑 #

通过Dataflow处理PubSub维度消息时，用BigQuery MERGE语句实现原子性的新增/版本更新：

MERGE `project.dataset.dim_customer` AS target
USING (
  SELECT
    GENERATE_UUID() AS customer_surrogate_key, -- 生成新的代理键
    'CUST_123' AS customer_business_key, -- 消息中的业务键
    '张三' AS customer_name,
    '企业客户' AS customer_segment,
    CURRENT_TIMESTAMP() AS valid_from
) AS source
ON target.customer_business_key = source.customer_business_key AND target.is_current = TRUE
WHEN MATCHED AND (target.customer_name != source.customer_name OR target.customer_segment != source.customer_segment) THEN
  UPDATE SET
    valid_to = CURRENT_TIMESTAMP(),
    is_current = FALSE
WHEN NOT MATCHED THEN
  INSERT (customer_surrogate_key, customer_business_key, customer_name, customer_segment, valid_from)
  VALUES (source.customer_surrogate_key, source.customer_business_key, source.customer_name, source.customer_segment, source.valid_from);

• 维度数据可随时写入，无需等待事实表数据，完全独立
• 自动处理维度属性变更，生成新的历史版本

事实表写入逻辑 #

处理PubSub事实消息时，先通过业务键查询维度表的当前版本代理键，再写入事实表：

1. 用BigQuery查询获取代理键：

   SELECT customer_surrogate_key FROM `project.dataset.dim_customer` WHERE customer_business_key = 'CUST_123' AND is_current = TRUE;
   

2. 如果查询到代理键：直接插入事实表，关联对应代理键
3. 如果未查询到（极端情况，维度数据延迟到达）：
   • 方案1：将事实数据暂存到BigQuery临时表，通过Dataflow定时任务重试查询代理键，成功后写入正式事实表
   • 方案2：事实表中代理键字段允许NULL，后续维度数据补全后更新事实表（不推荐，事实表建议仅追加）

关键优化点 #

• 代理键用GENERATE_UUID()生成，避免分布式场景下的自增ID冲突，确保全局唯一
• 维度表按业务键+当前版本标记聚类，提升查询代理键的性能
• 事实表按业务时间分区，降低流式写入的存储成本
• Dataflow中设置合理的窗口（如1分钟滑动窗口），批量处理消息，减少BigQuery的写入次数，提升效率

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