数据仓库提纲与笔记（实战导向）

1. 总览与定位

是什么：数据仓库是围绕分析与决策优化的、面向主题的、集成的、相对稳定的历史数据集合。强调“整合、历史性、可复用”。

与数据湖的关系：

• 数据湖（DL）偏“尽量存，一切先来”，存放原始明细与多类型数据。成本低、约束少。
• 数据仓库（DW）偏“结构化、可复用、稳定口径”，强调建模与治理。
• 湖仓一体（Lakehouse）在湖上提供仓的能力：ACID表格式（Delta/Iceberg/Hudi）、SQL优化、时间旅行、物化视图等。

典型业务目标：统一指标口径、提升分析迭代速度、支持自助BI、提供可审计与可追溯的决策依据。

常见分层：ODS（原始明细/轻加工）→ DWD（清洗明细/宽表）→ DWS（汇总公共层）→ ADS（应用服务层/报表层）。

2. 架构与方法论

Kimball（维度建模）：面向业务过程，构建事实表与维度表，追求易用与查询友好。适合快速出价值与自助分析。

Inmon（3NF/企业信息工厂）：先构建企业级3NF数据仓库，再面向应用做数据集市。强调自顶向下的一致性。

Data Vault 2.0：以Hub-Link-Satellite拆分“业务键”“关系”“属性”，适合多源、变更频繁与可追溯性强的场景。

现代湖仓架构：对象存储 + ACID表格式（Delta/Iceberg）+ 计算引擎（Spark/Trino/ClickHouse/StarRocks/云数仓）+ 编排（Airflow/Dagster）+ 语义层/BI（dbt/Looker/Power BI）。

选型建议：域驱动（Domain-Driven）优先；多团队协作倾向维度建模/语义层；强审计/可追溯可考虑Data Vault；历史资产已在3NF可保留核心域再铺维表。

3. 维度建模核心

基本概念：

• 事实表：承载度量（measure），如订单金额、浏览次数。分为事务型（事件发生即记录）、快照型（按周期快照）、累积型（一个流程的里程碑节点）。
• 维度表：描述事实的“切片”与“标签”，如用户、商品、时间、渠道等。
• 粒度：一条事实记录代表“多细”的事件单位（如“订单行”vs“订单”）。粒度先行，字段后定。

慢变维（SCD）：

• SCD1 覆盖更新；SCD2 拉链/版本；SCD3 当前+上一个；SCD6（1+2+3的组合）。
• 实务中常用SCD2（拉链）用于保留历史口径，配合业务生效时间和失效时间。

星型与雪花：

• 星型：维度去规范化到一张表，查询简单、性能友好。
• 雪花：维度继续规范化成多表，节省存储但查询复杂。

一致性维度（Conformed Dim）：跨主题复用，如统一“用户”“时间”“渠道”。

键设计：

• 代理键（surrogate key）替代自然键，避免业务键变更传播；事实表以代理键关联维表。
• 建议：维表保留自然键 + 代理键；事实表保存代理键、自然键与业务时间，便于追溯与回溯修复。

示例：电商订单域

• 粒度：订单行（order_line）。
• 事实表：fct_order_line（金额、数量、税费、折扣、订单状态码等）。
• 维度：dim_customer、dim_product、dim_date、dim_channel、dim_region。

4. 采集与集成

批处理 vs 流式：

• 批：T+1报表、日/小时级，成本低、简洁。
• 流：准实时，支持监控、在线特征。成本高、治理要求更严。

CDC（变更数据捕获）：

• 日志级（binlog/WAL）：完整、低侵入；
• 时间戳/版本列：简单，但可能漏变更；
• 触发器/应用层：侵入较高，适合小系统。

半结构化数据：JSON/Avro/Parquet，优先落地为列式（Parquet）、注册Schema，避免“随取随解析”的性能陷阱。

数据质量入口校验：Schema、主键、空值、枚举值、外键、唯一性与去重、时间戳合理性。

5. ETL/ELT 设计与工程实践

ELT优先：将计算下推到云数仓/MPP引擎，简化中间层服务。

编排与依赖：使用Airflow/Dagster，显式编码数据依赖与SLA；避免“意大利面”DAG，按域/层拆分子DAG。

dbt最佳实践：

• 模型命名：stg_（轻加工）、int_（中间聚合/宽表）、dim_/fct_（维/事实）、mart_（数据集市/ADS）。
• 文档化：schema.yml 描述列含义、测试（unique、not_null、referential_integrity、accepted_values）。
• 物化策略：table（小表/稳定）、incremental（大表）、view（轻量）、materialized view（引擎级）。
• 宏与可复用：通用SCD2、增量键、软删除处理宏。

增量与幂等：以业务时间或自增ID为边界，拉齐窗口（time-window）+ 去重，保证重复重跑不产生重复数据。

数据测试：开发期单元测试 + 管道级契约测试 + 生产期数据质量监控。

失败恢复：检查点（checkpoint）、小批量重放、幂等UPSERT（MERGE）。

6. 存储与计算引擎要点

列存与MPP：列式压缩、向量化执行、并行分片。注意数据倾斜（skew）与小文件（small files）。

分区/聚簇/分桶：

• 分区（Partition）：常用日期/业务键；过细会导致元数据爆炸。
• 聚簇/排序键（Cluster/Sort Key）：提高范围扫描效率，避免全表扫描。
• 分桶（Bucket）：加速Join与聚合，但数量难以后改，需谨慎评估。

表格式：Hive（基础）、Delta/Iceberg（ACID+时间旅行+优化）。

索引与加速：物化视图、结果缓存、汇总表、Z-order/主键索引（视引擎而定）。

7. 语义层与指标治理

指标四要素：口径定义（业务公式）+ 粒度 + 过滤条件 + 时间窗（快照/累积）。

一致口径：将指标上收至语义层（LookML/dbt metrics/内部语义服务），生成字段与聚合逻辑即“单一事实来源（SSOT）”。

拉链表 & 快照：

• 维度SCD2拉链：生效/失效时间 + is_current标识。
• 事实定期快照：生成每日/每小时状态，便于时间点分析。

血缘与版本化：产出—消费链路可视化；指标/模型版本与变更审计，避免“悄悄改口径”。

8. 性能优化与成本控制

查询优化：

• 选择最小必要粒度；
• 预聚合（DWS/物化视图）；
• 谨慎使用SELECT *；
• 合理Join顺序与Broadcast策略；
• 维护统计信息。

存算优化：生命周期（冷热分层/TTL）、压缩编码、文件大小控制（128–512MB），小文件合并。

成本可观测：按项目/域/人维度打标签；建立“查询成本排行榜”；设定预算与告警。

9. 安全、隐私与合规

权限模型：RBAC（角色）与ABAC（属性）结合；最小权限原则。

细粒度控制：行级/列级权限、动态脱敏（如对PII字段做hash/掩码）。

合规要点：数据最小化、可删除权（Right to be forgotten）、访问审计、数据跨境策略。

隐私增强：

• 伪匿名化（pseudonymization）与去标识化；
• K匿名、L多样性（统计发布场景）；
• 差分隐私（DP）用于报表/共享场景的噪声注入（仅在理解影响后采用）。

10. 可观测性与SRE

指标：SLA/SLO定义（数据准备时间、延迟、质量阈值）。

监控：

• 任务层：运行时长、重试次数、失败率；
• 数据层：空值率、唯一性、分布漂移、行数与增量比；
• 成本层：查询/作业费用、扫描量。

告警：噪声抑制、聚合告警、值班轮值；提供Runbook与一键回滚/重跑脚本。

11. 项目落地路线图（里程碑）

1. 发现与域划分：梳理核心业务流程（下单、支付、履约、售后）。
2. MVD（最小可用数据集）：优先交付TOP5指标与关键看板，1–2个主题域起步。
3. 数据契约：与源系统约定Schema与变更流程；引入契约测试。
4. 建模与开发：确立粒度与键；dbt+编排；测试与文档并行。
5. 上线与验收：SLA、质量阈值、回滚方案；与业务共测指标口径。
6. 迭代与治理：成本看板、血缘图、技术债清单；培训自助分析。

12. 反模式与常见坑

• 粒度未定义：事实表混合多粒度，导致重复与不可聚合。
• 过度雪花：查询极其复杂，用户放弃使用。
• 指标漂移：多人多处定义相似指标，报表打架。
• 无幂等：重跑产生重复/缺失；缺少MERGE与去重策略。
• 小文件风暴：对象存储海量小文件拖垮扫描性能。
• 强实时执念：全量实时化成本高、收益低；按需选择近实时/微批。

13. 端到端示例：订单域（简化）

源表：orders（订单头）、order_items（行）、payments、customers、products。

ODS层：轻清洗、字段规范化、保留变更列（操作类型/变更时间）。

DWD层（宽明细）：

• Key策略：order_item_id为代理键，保留order_id自然键；
• 时间：业务时间order_created_at + 处理时间ingested_at；
• 去重：按业务时间+自然键窗口取最新。

DWS层（公共汇总）：

• 粒度：天/周/月；维度：用户、商品、渠道、地区；
• 指标：GMV、订单数、件数、退货率、转化率。

ADS层（应用报表）：

• 看板：高层指标（趋势与同比环比）；
• 主题报表：用户复购、商品动销、渠道ROI。

示例SQL片段（SCD2维表合并）：

MERGE INTO dim_customer t
USING stg_customer s
ON t.natural_key = s.customer_id AND t.is_current = true
WHEN MATCHED AND (
  t.name != s.name OR t.level != s.level OR t.region != s.region
) THEN
  UPDATE SET t.valid_to = s._ingest_ts, t.is_current = false
WHEN NOT MATCHED BY TARGET THEN
  INSERT (surrogate_key, natural_key, name, level, region, valid_from, valid_to, is_current)
  VALUES (GENERATE_UUID(), s.customer_id, s.name, s.level, s.region, s._ingest_ts, TIMESTAMP '9999-12-31', true);

dbt模型分层命名示例：

• stg_orders.sql、stg_order_items.sql
• int_order_line_enriched.sql
• dim_customer.sql（SCD2）
• fct_order_line.sql
• mart_sales_daily.sql

数据质量测试示例（dbt schema.yml）：

models:
  - name: dim_customer
    columns:
      - name: surrogate_key
        tests: [unique, not_null]
      - name: natural_key
        tests: [not_null]
      - name: valid_from
        tests: [not_null]
      - name: is_current
        tests:
          - accepted_values:
              values: [true, false]

14. Checklist（上线前&治理｜详细版）

这是一张可交付、可验收、可回滚的上线前&治理清单。每一条都给出“说明｜验收标准｜产出物｜责任人”。配好小表情，提醒更到位～ 🧩✅

14.1 上线前准备（Release Readiness）

• [ ] 事实粒度与主键已冻结 🔒
  说明：所有事实表的最小分析单位与主键/去重策略明确；跨域一致维度对齐。
  验收标准：任一事实表能用主键定位唯一记录；跨表Join不出现重复放大。
  产出物：《粒度与键设计说明》、样例SQL。
  责任人：数据建模Owner。
• [ ] 口径与指标文档通过评审 📘🤝
  说明：核心指标（GMV、转化、留存、拒付率等）口径写入语义层/文档并版本化。
  验收标准：业务、数据、BI三方签名；语义层内可查到同名指标且单一来源。
  产出物：《指标口径文档vX.Y》、LookML/dbt metrics定义。
  责任人：指标治理Owner、业务代表。
• [ ] 增量/重跑幂等 🔁🧪
  说明：增量边界（时间/ID）与去重策略固定；支持窗口重放不产生重复或遗漏。
  验收标准：回放N天窗口，结果与基线一致（±0）；MERGE/UPSERT带唯一键与去重。
  产出物：回放脚本、基线校验SQL。
  责任人：数据工程Owner。
• [ ] 任务SLA/SLO与告警策略 ⏰🚨
  说明：明确D+1/D当日产出时间、重试与超时；告警分级与收敛。
  验收标准：SLA例：D+1 07:00前完成；SLO ≥ 99%；延迟>15min触发P2告警并路由值班。
  产出物：《运行SLA/SLO清单》《告警路由与Runbook》。
  责任人：数据平台SRE。
• [ ] 权限与脱敏 🛡️🗝️
  说明：PII/敏感列清单，行/列级权限、动态脱敏规则与密钥管理。
  验收标准：非授权用户访问敏感列得到掩码；审计日志可追溯“谁在何时看了什么”。
  产出物：《敏感数据台账》《权限矩阵》与审计样例。
  责任人：数据安全Owner。
• [ ] 成本预算与配额 💸📊
  说明：作业与查询预算、队列/池配额、标签计费。
  验收标准：成本看板可按“项目/域/人”切片；本月预算与阈值告警生效。
  产出物：《成本与配额策略》、成本仪表盘链接。
  责任人：FinOps/平台Owner。
• [ ] 回滚/应急预案演练 🧯📦
  说明：时间旅行/快照点、回滚脚本、影子发布/灰度切换。
  验收标准：在演练环境10分钟内完成回滚；影子产出与线上一致（±0）。
  产出物：《回滚Runbook》《演练记录》。
  责任人：数据工程Owner、SRE。

14.2 性能与成本（Performance & FinOps）

• [ ] 分区/聚簇命中率 🧭
  目标：分区裁剪命中率 ≥ 80%；聚簇/排序键命中 ≥ 70%。
  检查：查询剖析/Explain中扫描的分区与行数；命中率低的SQL列入优化池。
• [ ] 小文件治理 🧱➡️📦
  目标：平均文件大小 128–512MB；单分区文件数 < 200。
  动作：定时OPTIMIZE/COMPACT；写入端批量化、合并Flush。
• [ ] 数据倾斜监测 ⚖️
  目标：最大分区扫描量/中位数 < 5x；Join热点Key占比 < 20%。
  动作：盐值（salting）、重分桶/重分区、Broadcast小表。
• [ ] 统计信息与物化命中 🧮📈
  目标：统计信息更新 ≤ 24h；物化视图/结果缓存命中率 > 60%。
  动作：计划任务刷新统计；为Top N重查询设计预聚合。
• [ ] 成本阈值与节流 🪫
  目标：单查询扫描量/费用阈值；超限自动Kill或降级。
  动作：资源队列、任务优先级与限流，冷/热分层与TTL。

14.3 治理与可观测（Governance & Observability）

• [ ] 数据契约与Schema变更流程 🤝📜
  标准：向后兼容优先；破坏性变更需RFC与灰度；CI中加入契约测试。
  产出：《数据契约清单》《Schema变更SOP》。
• [ ] 数据质量监控 🧪📊
  指标：not_null/unique/fk_integrity/accepted_values、行数偏差、分布漂移（PSI）。
  阈值：关键表强失败（阻断）、非关键表软失败（标记+降级）。
  产出：DQ仪表盘、告警样例与处置SLA。
• [ ] 血缘与版本化 🧬
  标准：模型/指标/作业与Git/Registry双向关联；每次发布自动产出血缘快照。
  产出：血缘图链接、变更Changelog。
• [ ] 指标治理与变更公告 📰
  标准：新增/变更指标需审批与冷启动宣讲；下线前提供替代方案与过渡期。
  产出：《指标RFC模板》《公告模版》。
• [ ] 事件响应与复盘 🧭📝
  标准：SEV分级、值班轮值、战情室流程；事后48小时内复盘并沉淀长期修复项。
  产出：《Incident Runbook》《Postmortem 模板》。

14.4 一页式上线卡（可复制模板）📎

14.5 表情包Tips（友好又不失严谨）

• 🚦 红灯：破坏性Schema变更未走RFC/灰度；禁止上线。
• 🧨 危险：无回滚脚本或快照点；先补再发。
• 🪙 省钱：重查询不上物化=烧钱；纳入预聚合。
• 🧲 口径磁铁：指标不进语义层=口径漂移；立刻收口。

15. 命名与字段规范（模板）

• 表前缀：stg_/int_/dim_/fct_/mart_；
• 时间字段：*_date（日期）、*_ts（时间戳，UTC）、*_at（本地时间）；统一时区策略。
• 布尔：is_*；枚举：status/type + 字典表。
• 主键：*_sk（代理键），自然键保留*_nk或原名。
• 审计列：_ingest_ts、_batch_id、_op（I/U/D）。

16. 学习路径与参考（简）

• 维度建模：Kimball《The Data Warehouse Toolkit》；
• 数据治理与目录：DataHub/Amundsen 文档；
• dbt 官方文档与最佳实践；
• 现代湖仓：Delta/Iceberg 设计论文与社区指南；
• 语义层与指标：LookML/dbt metrics 相关文档与案例；
• 数据可靠性：数据契约、可观测性与SRE文章。

17. 练习建议

1. 选一个熟悉的业务域，画出事件流，确定事实粒度与一致维度；
2. 写出3个业务关键指标及其口径与窗口；
3. 用SQL实现一次SCD2更新与一次增量合并；
4. 在编排器中定义SLA与失败重跑策略；
5. 为最重查询设计物化视图或预聚合表并对比成本。

18. 领域知识：电商（专业汇总）

18.1 事件域/事实表建议
PV/UV（fct_page_view）、曝光（fct_product_impression）、点击（fct_product_click）、加购（fct_add_to_cart）、开启结账（fct_checkout_start）、下单（fct_order/fct_order_line）、支付尝试/成功/失败（fct_payment 带 status）、发货/签收（fct_shipment）、退款/退货（fct_refund/fct_return）、库存快照（fct_inventory_snapshot）、优惠券事件（fct_coupon_event）、评价与客服（fct_review/fct_ticket）。

18.2 核心维度（含 SCD2 点）
dim_customer（等级/会员/地区/设备画像，SCD2）、dim_product（SKU/SPU、类目树、品牌、价格/成本属性，SCD2）、dim_category（多级类目，SCD2）、dim_campaign（UTM 五件套：source/medium/campaign/content/term）、dim_channel、dim_device、dim_region、dim_merchant、dim_warehouse、dim_promotion、dim_experiment（实验分层与随机化单元）。

18.3 指标体系（口径模板）

• GMV：含税/不含税、含券/不含券、下单口径/支付口径；明确退单口径（含退/不含退）。
• 转化漏斗：曝光→点击→加购→下单→支付；各环节转化率与中位时长。
• 客单价（AOV） = 支付金额 / 支付订单数。
• 留存与复购：D1/D7/D30 留存、复购率、回购间隔。
• LTV/CAC/ROI/ROAS：定义折现/归因窗口；多触点归因（首/末次、时间衰减、位置、Markov/Shapley）。
• 商品与供给：动销率、缺货率、上/下架时延、价格弹性近似（Δ销量/Δ价格）。
• 履约：准时发货率、签收时效、逆向物流时长、退款率/退货率。
• 体验：NPS/CSAT、投诉率。

18.4 建模要点

• 多币种：dim_currency_rate 驱动标准币种归一，避免“历史汇率回填”污染。
• 税费与优惠分摊：订单头/行两层，分摊到行以便商品维度分析。
• 会话与身份解析：跨设备 ID 图谱（cookie、设备指纹、手机号/邮箱、登录ID）。
• 事件时间 vs 处理时间：统一 event_ts 与 ingested_ts；迟到事件“保守窗口 + 补写策略”。
• 类目树 SCD2：保留历史类目归属，便于历史重算。
• 机器人与异常流量：黑名单、UA/速度阈值、IP信誉维度。

18.5 归因与实验

• 维护 fct_attribution：每个订单/会话的触点列表与分配权重；记录模型版本。
• 实验：随机化单元（user/device/session）、CUPED 调整、护栏指标（转化、退货、时延、稳定性）。

18.6 推荐/特征（用于特征仓/在线特征）

• 用户：R/F/M、近N天加购/下单次数、品牌/类目偏好、多样性指数。
• 商品：近N天曝光/点击/转化率、价格波动、库存可卖天数、退货率。
• 匹配：用户×商品 CTR/ATC/Conv 统计、协同过滤嵌入。
• 实时：近1/5/30分钟窗口的点击/加购速度特征（流式聚合）。

18.7 标准看板
增长总览、转化漏斗、复购与LTV、商品动销、库存与履约、营销与归因、体验与服务。

19. 领域知识：金融风控（专业汇总）

19.1 子域与事件

• KYC/AML：开户/实名、制裁/名单筛查、交易监控。
• 反欺诈：登录/注册/绑卡/支付/提现/转账/促销滥用、账户接管（ATO）。
• 信用风控：授信/审批/定价/放款、贷中预警、贷后催收与核销。
• 关键事件事实表：fct_auth_event、fct_payment_txn、fct_chargeback、fct_dispute、fct_withdrawal、fct_transfer、fct_bonus_abuse、fct_loan_application、fct_loan_account、fct_disbursement、fct_repayment、fct_delinquency_snapshot、fct_collection_action。

19.2 维度与字典
dim_customer_kyc（证件类型/状态，SCD2）、dim_device（指纹、风控评分）、dim_ip（ASN/代理/地理）、dim_card（BIN/发卡行/国家/3DS 能力）、dim_merchant、dim_counterparty、dim_risk_model（版本/阈值）、dim_rule（规则ID/策略包/生效区间）、dim_black_white_list、dim_geo、dim_collection_strategy。

19.3 标签/样本构建（避免泄漏）

• 欺诈正负样本：以拒付/确认欺诈为正类；常用T=事件时刻，窗口 W=120天 汇聚是否发生拒付：

-- 120天拒付标签（支付欺诈）
SELECT txn_id,
       CASE WHEN EXISTS (
         SELECT 1 FROM chargeback cb
         WHERE cb.original_txn_id = t.txn_id
           AND cb.cbk_date <= t.event_ts + INTERVAL '120 days'
       ) THEN 1 ELSE 0 END AS y_cbk_120d
FROM payment_txn t
WHERE t.event_ts BETWEEN :start AND :end;

• 信用违约（PD）标签：12个月内发生 30+ DPD 视为坏账；按申请时刻冻结特征，切时间分层训练/验证。

-- 申请样本的 12M 30+DPD 标签
SELECT app_id,
       MAX(CASE WHEN d.days_past_due >= 30 THEN 1 ELSE 0 END) AS y_12m_dpd30
FROM loan_app a
LEFT JOIN delinquency d ON d.loan_id = a.loan_id
  AND d.snapshot_date BETWEEN a.app_time AND a.app_time + INTERVAL '12 months'
GROUP BY app_id;

• LGD/EAD：以回收曲线估计；窗口与观测期要与业务核算对齐。

19.4 特征工程体系

• 速度/频次：同设备/IP/卡/账户在 1/5/60 分钟、1/7/30 天内的尝试/失败/金额聚合。
• 图关系：邮箱/手机号/设备/IP 连接图，连通分量、度/三角计数、社团风险评分。
• 地理一致性：账单地址 vs IP vs 设备 GPS vs BIN 国家，距离/国家不一致标记。
• 序列特征：上次成功/失败到当前的时间、连续失败长度、夜间行为比例。
• 外部信号：黑名单、欺诈联盟、征信/消费评分、商户/国家风险指数。
• 文本/原因码：客服工单/争议说明的嵌入或关键词。
• 在线/离线 一致：特征仓（offline） + 在线特征服务（nearline），同一定义/同一代码生成。

19.5 在线风控引擎
请求→特征取数→模型打分→规则引擎→决策（通过/拒绝/人工复核/限额）→原因码→审计落盘。

• 延迟预算：P95 < 100ms；失败降级与断路器。
• 模型治理：版本化、灰度发布、冠军-挑战者（Champion–Challenger）。
• 可解释性：全量留存特征与打分；导出 Top-K 重要特征作为原因码（符合不利行动告知要求）。

19.6 合规/审计要点
KYC/AML 留痕、制裁名单更新频率、交易监控阈值；模型文档、训练/推理数据血缘、访问审计、数据保留与删除策略；公平性与偏差检测。

19.7 指标看板

• 反欺诈：欺诈率、拒付率、阻断率、误拒率、阈值效率曲线、AUC/KS、PSI/漂移。
• 支付：授权成功率、3DS 挑战率/通过率、撤销/退款率、通道成本。
• 信用：通过率、坏账率、DPD 分布、迁移矩阵（roll rate）、回收率、核销率。
• 催收：承诺还款履约率、阶段转化、回收成本/效能。
• 实时：端到端延迟、特征取数命中率、缓存命中/降级次数。

19.8 数据契约/质量
Schema 漂移告警、空值率/分布漂移监控、迟到率、外部名单/征信刷新 SLA；风险事件双写与幂等。

19.9 风险评分与决策表（Schema 示意）

• fct_risk_decision(txn_id, model_version, score, decision, reason_codes[], features_json, request_ts, latency_ms)
• dim_reason_code(reason_id, description, regulation_ref, severity)

20. 统一术语/口径对照（电商 × 风控）

21. 快速落地清单（电商/风控）

• 建 3 个一致维度：dim_date、dim_customer(SCD2)、dim_product(SCD2/仅电商) 或 dim_customer_kyc(风控)。
• 做 2 张关键事实：电商 fct_order_line/fct_payment；风控 fct_payment_txn/fct_risk_decision。
• 上线 1 套指标/告警：电商（GMV/转化/退货）、风控（拒付率/误拒率/延迟）。
• 固化 ELT + 契约测试 与 SCD2 拉链 模板；引入 成本/延迟 看板。
• 预置 实验/归因（电商）或 冠军-挑战者（风控）机制。

22. 引擎方言扩展：ClickHouse / StarRocks / SparkSQL（Delta & Iceberg）

目标：把前文的建模、增量、SCD、优化与治理落到三类常见引擎方言。每节给出DDL 模板、增量/幂等策略、SCD2 实现、预聚合/物化、统计与小文件治理。

22.1 ClickHouse（OLAP 列存）

22.1.1 表设计基准

• ODS（去重快照）：ReplacingMergeTree(version) 或 ReplacingMergeTree(_ingest_ts)；仅用于中间层去重，不用于SCD2历史保留。
• DWD/DIM（保历史）：MergeTree（普通），保留多版本行；查询时用 is_current=1 或 valid_from/valid_to 过滤。
• 分区/排序：按时间 toYYYYMM(event_ts) 分区；ORDER BY (business_key[, event_ts])；必要时加数据跳过索引（bloom）。

DDL：维度（SCD2）与事实（明细）

-- 维度（SCD2 历史保留）
CREATE TABLE dw.dim_customer
(
  surrogate_key UUID,
  natural_key String,
  name String,
  level String,
  region String,
  valid_from DateTime,
  valid_to   DateTime,
  is_current UInt8,
  _ingest_ts DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(valid_from)
ORDER BY (natural_key, valid_from)
SETTINGS index_granularity = 8192;

-- 事实明细（去重版本，DWD 可采用 ReplacingMergeTree）
CREATE TABLE dw.fct_order_line
(
  order_item_id UInt64,
  order_id      UInt64,
  product_id    UInt64,
  user_id       UInt64,
  amount        Decimal(18,2),
  qty           UInt32,
  event_ts      DateTime,
  _version      UInt64,      -- 递增版本或事件时间戳
  _ingest_ts    DateTime
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(event_ts)
ORDER BY (order_item_id)
SETTINGS index_granularity = 8192;

-- 可选：数据跳过索引（示例：邮箱、IP等高基数字段）
CREATE INDEX idx_email ON dw.dim_customer (name) TYPE tokenbf_v1(1024) GRANULARITY 2;

22.1.2 SCD2（窗口法生成区间）

-- 假设 stg_customer_changes 汇集每次变更的“当前快照”，按业务键+事件时间去重
CREATE TABLE tmp.dim_customer_scd2 AS
SELECT
  natural_key,
  name,
  level,
  region,
  event_ts AS valid_from,
  COALESCE(
    lead(event_ts) OVER (PARTITION BY natural_key ORDER BY event_ts) - INTERVAL 1 SECOND,
    toDateTime('9999-12-31')
  ) AS valid_to,
  IF(lead(event_ts) OVER (PARTITION BY natural_key ORDER BY event_ts) IS NULL, 1, 0) AS is_current,
  now() AS _ingest_ts
FROM (
  SELECT DISTINCT natural_key, name, level, region, event_ts FROM stg_customer_changes
);

INSERT INTO dw.dim_customer SELECT generateUUIDv4(), * FROM tmp.dim_customer_scd2;

22.1.3 幂等增量（事实去重）

-- 以 (order_item_id, _version) 为幂等键，stg 端保证单键最大版本唯一
INSERT INTO dw.fct_order_line
SELECT * FROM stg_order_line_incr -- 已按 (order_item_id) 取最大 _version

22.1.4 预聚合/物化视图

CREATE MATERIALIZED VIEW mart.mv_sales_daily
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, product_id)
AS
SELECT toDate(event_ts) AS dt, product_id, sum(amount) AS gmv, sum(qty) AS qty
FROM dw.fct_order_line
GROUP BY dt, product_id;

22.1.5 统计与小文件治理

• OPTIMIZE TABLE db.tbl [PARTITION ...] FINAL：控制性触发合并；谨慎在大表频繁使用。
• 写入批量化（每批 100k+ 行）减少小分片；调整 max_partitions_per_insert_block 等写入参数（按环境）。
• 用 EXPLAIN / system.query_log 审计分区裁剪与扫描量；避免 FINAL 滥用。

22.2 StarRocks（MPP + 向量化 + 自带物化）

22.2.1 表模型选择

• 维度 SCD2：保历史建议 DUPLICATE KEY（按 natural_key, valid_from）；查询加 is_current=1。
• 事实 Upsert：优先 PRIMARY KEY 表，天然支持按主键更新（近实时）；或 UNIQUE KEY + sequence 列 选择最新版本。
• 分区/分桶：PARTITION BY RANGE (DATE_TRUNC('day'| 'month', ts)) + DISTRIBUTED BY HASH(key) BUCKETS N。

DDL：维度（SCD2）与事实（主键表）

-- 维度（SCD2）
CREATE TABLE dw.dim_customer (
  surrogate_key STRING,
  natural_key   STRING,
  name          STRING,
  level         STRING,
  region        STRING,
  valid_from    DATETIME,
  valid_to      DATETIME,
  is_current    TINYINT,
  _ingest_ts    DATETIME
)
DUPLICATE KEY(natural_key, valid_from)
PARTITION BY RANGE (DATE_TRUNC('month', valid_from))
DISTRIBUTED BY HASH(natural_key) BUCKETS 16
PROPERTIES (
  "replication_num" = "3",
  "dynamic_partition.enable" = "true",
  "dynamic_partition.time_unit" = "MONTH",
  "dynamic_partition.start" = "-24",
  "dynamic_partition.end" = "12"
);

-- 事实（主键 Upsert）
CREATE TABLE dw.fct_order_line (
  order_item_id BIGINT NOT NULL,
  order_id      BIGINT,
  product_id    BIGINT,
  user_id       BIGINT,
  amount        DECIMAL(18,2),
  qty           INT,
  event_ts      DATETIME,
  _version      BIGINT,
  _ingest_ts    DATETIME
)
PRIMARY KEY(order_item_id)
PARTITION BY RANGE (DATE_TRUNC('month', event_ts))
DISTRIBUTED BY HASH(order_item_id) BUCKETS 64
PROPERTIES (
  "replication_num" = "3",
  "enable_persistent_index" = "true"
);

22.2.2 SCD2（窗口法/批处理入仓）

INSERT INTO dw.dim_customer
SELECT
  UUID(), natural_key, name, level, region,
  valid_from,
  COALESCE(lead_valid_from - INTERVAL 1 SECOND, TIMESTAMP '9999-12-31 00:00:00') AS valid_to,
  IF(lead_valid_from IS NULL, 1, 0) AS is_current,
  NOW()
FROM (
  SELECT *,
         LEAD(event_ts) OVER (PARTITION BY natural_key ORDER BY event_ts) AS lead_valid_from
  FROM stg_customer_changes
) t;

22.2.3 幂等增量（事实 Upsert）

-- PRIMARY KEY 表：直接 INSERT 即 Upsert（以主键覆盖），_version 可用于审计
INSERT INTO dw.fct_order_line
SELECT * FROM stg_order_line_incr;  -- 需保证单主键取最新一版

22.2.4 物化视图（自动改写）

CREATE MATERIALIZED VIEW mv_sales_daily AS
SELECT DATE(event_ts) AS dt, product_id, SUM(amount) AS gmv, SUM(qty) AS qty
FROM dw.fct_order_line
GROUP BY dt, product_id;
-- 查询自动命中，视版本可支持 REFRESH 语句或自动刷新

22.2.5 统计与小文件治理

• ANALYZE TABLE db.tbl WITH SYNC; 定期跑全库/热点表；保持代价模型可信。
• 导入：批量 BROKER LOAD / Kafka ROUTINE LOAD；主键表支持近实时更新。
• 动态分区：开启 dynamic_partition 自动滚动；Tablet 数与 BUCKETS 结合并发与合并。

22.3 SparkSQL（Delta Lake & Apache Iceberg）

22.3.1 DDL 模板

-- Delta Lake（Databricks 或开源 Delta）
CREATE TABLE dw.dim_customer (
  surrogate_key STRING,
  natural_key   STRING,
  name          STRING,
  level         STRING,
  region        STRING,
  valid_from    TIMESTAMP,
  valid_to      TIMESTAMP,
  is_current    BOOLEAN,
  _ingest_ts    TIMESTAMP
) USING delta
PARTITIONED BY (date_trunc('MONTH', valid_from));

-- Iceberg（Spark 3.1+）
CREATE TABLE dw.fct_order_line (
  order_item_id BIGINT,
  order_id      BIGINT,
  product_id    BIGINT,
  user_id       BIGINT,
  amount        DECIMAL(18,2),
  qty           INT,
  event_ts      TIMESTAMP,
  _ingest_ts    TIMESTAMP
) USING iceberg
PARTITIONED BY (months(event_ts));

22.3.2 SCD2（MERGE INTO）

-- Delta / Iceberg 均支持 MERGE（视版本）
MERGE INTO dw.dim_customer t
USING stg_customer s
ON t.natural_key = s.customer_id AND t.is_current = true
WHEN MATCHED AND (
  t.name <> s.name OR t.level <> s.level OR t.region <> s.region
) THEN UPDATE SET
  t.is_current = false,
  t.valid_to   = s._ingest_ts
WHEN NOT MATCHED THEN INSERT (
  surrogate_key, natural_key, name, level, region, valid_from, valid_to, is_current, _ingest_ts
) VALUES (
  uuid(), s.customer_id, s.name, s.level, s.region, s._ingest_ts, TIMESTAMP '9999-12-31', true, s._ingest_ts
);

22.3.3 幂等增量（事实 Upsert）

-- 以 (order_item_id) 为主键，窗口内取最大事件时间，避免重复
MERGE INTO dw.fct_order_line t
USING (
  SELECT *
  FROM (
    SELECT s.*, ROW_NUMBER() OVER (PARTITION BY order_item_id ORDER BY event_ts DESC) AS rn
    FROM stg_order_line_incr s
  ) q WHERE rn = 1
) s
ON t.order_item_id = s.order_item_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

22.3.4 预聚合与维护

-- 预聚合表（替代物化视图）；用调度器定时刷新
CREATE TABLE mart.sales_daily USING delta AS
SELECT date(event_ts) AS dt, product_id, SUM(amount) AS gmv, SUM(qty) AS qty
FROM dw.fct_order_line
GROUP BY dt, product_id;

22.3.5 小文件治理 / 统计

• Delta：OPTIMIZE db.tbl [ZORDER BY (key...)]（如平台支持）；VACUUM db.tbl RETAIN 168 HOURS；DESCRIBE HISTORY 审计。
• Iceberg：CALL <catalog>.system.rewrite_data_files(table => 'db.tbl');、CALL <catalog>.system.rewrite_manifests(table => 'db.tbl');。
• Spark统计：ANALYZE TABLE db.tbl COMPUTE STATISTICS; 与 ... FOR COLUMNS col1, col2。
• 流式一致：Structured Streaming foreachBatch + MERGE，确保离线/近线特征口径一致。

22.3.6 质量与回滚

• 期望/约束：Delta Live Tables / 约束检查（失败隔离）；Iceberg 行级删除/回滚 TIME TRAVEL。
• 审计：Delta DESCRIBE HISTORY；Iceberg snapshots 元数据表查询变更轨迹。

22.4 方言速查（Cheat Sheet）