设计电商销售数据仓库模型并优化查询性能

题目

设计电商销售数据仓库模型并优化查询性能

信息

• 类型：问答
• 难度：⭐⭐

考点

维度建模, 性能优化, ETL设计, 数据质量, 业务需求分析

快速回答

设计电商销售数据仓库的核心要点：

• 采用星型/雪花模式建模，核心事实表包含订单事实，维度包括时间、产品、客户、店铺等
• 使用分区和分桶技术优化大表查询性能
• ETL流程需处理缓慢变化维（SCD）问题
• 建立数据质量监控机制（完整性、一致性校验）
• 为高频查询创建聚合表（如每日销售汇总）

1. 数据模型设计

星型模型示例：

-- 事实表
CREATE TABLE fact_sales (
  sale_id BIGINT,
  date_key INT,       -- 外键关联日期维度
  product_key INT,    -- 外键关联产品维度
  customer_key INT,   -- 外键关联客户维度
  store_key INT,      -- 外键关联店铺维度
  quantity INT,
  amount DECIMAL(10,2),
  discount DECIMAL(5,2)
) PARTITIONED BY (sale_date DATE);

-- 维度表示例（产品维度）
CREATE TABLE dim_product (
  product_key INT PRIMARY KEY,
  sku VARCHAR(50),
  product_name VARCHAR(255),
  category_id INT,
  price DECIMAL(10,2),
  start_date DATE,
  end_date DATE,      -- 用于SCD Type2
  is_current BOOLEAN
);

2. 性能优化策略

• 分区：按日期分区事实表，加速时间范围查询
• 分桶：对高频过滤字段（如product_key）分桶
• 聚合表：创建预聚合表提升报表性能

  CREATE TABLE agg_daily_sales (
    sale_date DATE,
    product_key INT,
    total_sales DECIMAL(15,2),
    total_quantity INT
  );

• 列式存储：使用Parquet/ORC格式减少I/O

3. ETL流程关键点

• SCD处理：产品维度变化时（如价格变更），采用Type2新增记录
• 增量抽取：基于时间戳或CDC捕获源系统变更
• 数据清洗：处理空值、异常值、重复记录

4. 数据质量保障

• 完整性校验：外键约束验证（如确保所有product_key存在于维度表）
• 一致性检查：定期比对源系统和数据仓库的汇总数据
• 监控指标：设置NULL值比例、数据新鲜度等阈值告警

5. 常见错误与解决方案

6. 扩展知识

• 渐变维度：Type1（覆盖）、Type2（新增行）、Type3（新增列）适用场景
• 现代架构：Lambda/Kappa架构处理实时与批量数据
• 云数据仓库：Snowflake/BigQuery的自动优化特性（如微分区）