SuperAPI AI 工具集

data-warehousing

数据与分析

查看项目

更新于 10-26 16:16

技能介绍

数据仓库技能是一套用于设计、构建和优化分析型数据仓库的实用框架,以维度建模为核心方法。它的覆盖范围包括星型与雪花模式设计、缓慢变化维度(SCD)模式,以及针对 Snowflake 和 BigQuery 的平台特定指导。整体目标是在查询性能、存储成本和可维护性之间做出正确的建模决策,服务于下游分析消费者。

适用场景:当用户设计面向分析工作负载的星型或雪花模式、实现缓慢变化维度(类型 1、2、3 或混合型)、构建事实表(事务型、周期快照型或累积快照型)、编写面向 Snowflake 或 BigQuery 的分析 SQL、规划将数据加载进仓库的 ETL/ELT 管道、创建跨多个事实表共享的一致性维度、优化仓库查询性能(聚簇、分区、物化视图),或为新项目在 Snowflake 与 BigQuery 之间做选择时,应触发此技能。不适用于 OLTP 模式设计或事务数据库调优,也不适用于 Airflow、dbt 等数据管道编排工具本身。

关键原则:为查询而建模,而非为源系统建模——相比 OLTP 应积极反规范化,避免分析人员需要连接七张表才能回答基本问题;粒度是最重要的决策——每张事实表必须明确声明粒度(一行代表一笔交易、每位客户每天一行等),混合粒度会导致难以调试的重复计数和聚合错误;一致性维度支撑跨业务流程分析——先构建共享的 dim_customer 或 dim_date,使分析人员无需对账即可跨业务过程下钻;缓慢变化维度必须是显式的设计选择——每个维度属性都会随时间变化,应事先决定是覆盖(类型 1)、追踪历史(类型 2)还是存储前值(类型 3);按访问模式分区与聚簇——云仓库按扫描数据量计费,按日期分区并按高基数过滤列聚簇可在建表时将成本和查询时间降低 10 至 100 倍。

核心概念:维度建模实体包括事实表(存储可度量的业务事件,如 fct_orders、fct_page_views)、维度表(存储事实的描述性上下文,如 dim_customer、dim_product、dim_date)、代理键(仓库生成的整数或哈希主键,如 customer_sk 相对于自然键 customer_id)、退化维度(直接存放在事实表上的维度属性,如 fct_order_items 上的 order_number)、一致性维度(在多个事实表间完全共享的维度,如 dim_date、dim_geography)。

星型模式以事实表为中心、反规范化维度表向外辐射,从事实到任意维度只需一次连接;雪花模式将维度规范化到子维度(如 dim_product -> dim_category -> dim_department)。大多数分析工作负载优先采用星型模式,因为连接最少且更易被 BI 工具消费;雪花模式节省存储但增加连接复杂度,仅当维度表极大(1 亿行以上)且子维度被多个父级共享时使用。

事实表类型:事务型事实表每笔事件一行(如 fct_orders),最常见,捕获原子事件;周期快照型每实体每周期一行(如 fct_daily_inventory),用于定期状态测量;累积快照型每个流程生命周期一行(如 fct_order_fulfillment),追踪里程碑(已下单、已发货、已送达);无事实事实表没有度量、只有维度键(如 fct_student_attendance),记录某事件确实发生。

缓慢变化维度类型:类型 0 永不变化(如出生日期、原始注册日期);类型 1 覆盖旧值,简单但丢失历史;类型 2 新增行并做版本追踪,保留完整历史,是分析中最常用的类型;类型 3 增加前值列,仅追踪一个先前值,通常不足;类型 6(1+2+3 混合)在类型 2 行基础上增加当前值列,兼得历史与便捷的当前状态查询。

使用方式上,技能给出常见任务的实现示例:设计零售销售领域的星型模式,创建 dim_date、dim_customer、dim_product 与 fct_sales,并要求在注释或文档中显式声明粒度(如“每天每订单行项目一行”),全体成员在下游报表构建前须就粒度达成一致;在 Snowflake 中通过 MERGE 实现 SCD 类型 2,匹配属性变化的现行行使其过期(设置 expiry_date 与 is_current),无匹配则插入新客户,再第二遍为变化记录插入新的现行行;在 BigQuery 中因不支持对同一行多动作的 MERGE,采用 MERGE + INSERT 模式;Snowflake 性能优化包括为大型事实表设置聚簇键(按过滤频率排序)、用 SYSTEM$CLUSTERING_DEPTH 监控聚簇深度(接近 1.0 为良好,大于 5 需重聚簇)、设置 AUTO_SUSPEND = 60 避免空闲仓库计费、为 ETL 与分析分离仓库,以及为昂贵聚合建物化视图;BigQuery 优化包括按日期分区、按最多四个高频过滤列聚簇、用 dry run 预检扫描字节数、使用自动刷新的物化视图,并通过 INFORMATION_SCHEMA.JOBS 监控每查询成本;生成日期维度方面,给出 BigQuery 中用 GENERATE_DATE_ARRAY 生成日期 spine 并计算 date_sk、星期、月份、季度、财年与周末标志的示例。

常见反模式包括:事实表混合粒度(同时放日级与月级聚合,求和时重复计数,应按粒度拆分事实表);用自然键作事实表外键(自然键会变,连接静默断裂,应使用代理键);没有日期维度(丢失财年日历与节假日标志,应建共享 dim_date);所有属性默认 SCD 类型 1(丢失历史,无法回答上季度客户所属细分);大型事实表不分区(全表扫描导致云成本暴涨);维度过度规范化(深雪花增加连接复杂度,BI 工具难以处理 5 条以上连接路径,除非维度超 1 亿行否则展平为星型)。

注意事项:混合粒度是最难发现的缺陷,会产生数值上貌似合理但错误的答案,应在 CREATE TABLE 注释中声明粒度并用唯一性测试强制;SCD 类型 2 的 MERGE 若在 UPDATE 后、INSERT 前失败会留下孤立的“当前”行,应将更新包裹在事务中并在每对 MERGE + INSERT 后校验行数;BigQuery 仅在 WHERE 子句出现分区列时才裁剪分区,若只在 JOIN ON 中以 customer_id 连接分区表会全表扫描,抵消成本收益,务必在 WHERE 中包含分区列;Snowflake 开发仓库若未启用自动挂起会持续累积计算成本,非生产仓库应设 AUTO_SUSPEND = 60。

技能还提供参考文件:references/snowflake-patterns.md 涵盖 stages、streams、tasks、time travel、零拷贝克隆与仓库规模策略;references/bigquery-patterns.md 涵盖嵌套/重复字段、联邦查询、BI Engine、槽位与按需定价及定时查询;references/scd-patterns.md 深入讲解各类 SCD 的完整 SQL 实现、混合模式与类型间迁移策略。仅当当前任务需要时才加载参考文件,因为它们较长且消耗上下文。首次激活时会检查已安装的配套技能,若 frontmatter 中 recommended_skills 存在缺失项会提示一次并提供安装命令。