数据仓库（Data Warehouse，简称 DW 或 DWH）是一个面向主题的、集成的、非易失的、随时间变化的数据集合，用于支持管理决策。
此笔记使用的数据库为SQLServer，相应的示例脚本都围绕于此，其它数据库的相应实现会略有不同。
数据仓库的定义来自 Bill Inmon（数据仓库之父），四个核心特征解释如下：
| 特征 | 说明 |
|---|---|
| 面向主题（Subject-Oriented） | 围绕业务主题组织数据，如销售、客户、产品，而非围绕应用系统 |
| 集成（Integrated） | 将来自多个异构数据源的数据统一整合，消除不一致性 |
| 非易失（Non-Volatile） | 数据一旦进入仓库，通常不会被修改或删除，只会追加 |
| 随时间变化（Time-Variant） | 保存历史数据，支持时间维度的分析 |
数据仓库也是一种数据库，那么它跟我们平时说的有什么区别呢？
这里就需要引入两个概念，OLTP和OLAP。
┌─────────────────────────────────────────────────────────────┐
│ OLTP（操作型数据库） │
│ 目的：支持日常业务操作（增删改查） │
│ 特点：高并发、低延迟、数据量小、范式化设计 │
│ 示例：订单系统、用户系统、库存系统 │
└─────────────────────────────────────────────────────────────┘
↓ ETL
┌─────────────────────────────────────────────────────────────┐
│ OLAP（数据仓库） │
│ 目的：支持分析决策（聚合、趋势、报表） │
│ 特点：低并发、大数据量、历史数据、反范式设计 │
│ 示例：销售分析、用户行为分析、财务报表 │
└─────────────────────────────────────────────────────────────┘
1970s ── 决策支持系统（DSS）萌芽，数据分析需求出现
1980s ── 信息中心（Information Center）概念，数据抽取工具出现
1988 ── Barry Devlin & Paul Murphy 首次提出"数据仓库"概念
1990 ── Bill Inmon 正式定义数据仓库，被称为"数据仓库之父"
1993 ── Ralph Kimball 提出维度建模（Dimensional Modeling）
1996 ── Ralph Kimball 出版《The Data Warehouse Toolkit》
2000s ── ETL 工具成熟（Informatica、DataStage），数据仓库普及
2006 ── Hadoop 出现，大数据时代开始
2010s ── 云数据仓库兴起（Redshift 2012、BigQuery 2010、Snowflake 2014）
2015 ── Data Vault 2.0 发布（Dan Linstedt）
2020s ── 数据湖仓一体（Lakehouse）架构兴起（Delta Lake、Iceberg）
没有数据仓库的世界：
┌──────────┐ ┌──────────┐ ┌──────────┐
│ 销售系统 │ │ 财务系统 │ │ CRM系统 │
│ (MySQL) │ │ (Oracle) │ │ (SQL Svr)│
└──────────┘ └──────────┘ └──────────┘
↓ ↓ ↓
各自为政，数据孤岛，无法跨系统分析，报表打架
根据我的经验，没有数据仓库的话会有一个大坑。比如你要跨这些系统去做一个统计，涉及到的数据很大，比如需要五分钟，那么在这五分钟之内，源业务系统就被阻塞了，进行不了任何业务。这个损失是很大的，而且你不可能一天只运行一次查询，每次查询都会导致系统被阻塞。
有了数据仓库的世界：
┌──────────┐ ┌──────────┐ ┌──────────┐
│ 销售系统 │ │ 财务系统 │ │ CRM系统 │
└──────────┘ └──────────┘ └──────────┘
└──────────────┼───────────────┘
↓
┌───────────────┐
│ 数据仓库 │
│ 统一、清洗、 │
│ 整合、历史 │
└───────────────┘
↓
BI报表 / 数据分析 / 机器学习
有了数据仓库，数据都是为了报表提前聚合好的，而且在数据仓库上怎么折腾，也不会拖累到业务系统的性能。
所以总体来说，数据仓库的核心价值就在于：
数据源层 采集层 存储层 消费层
───────── ────── ────── ──────
业务数据库 ──┐
日志文件 ──┤ ETL/ELT ┌── PSA层 ──┐
API接口 ──┤ ──────────► │ DV层 │──► BI工具
Excel文件 ──┤ │ DM层 │──► 报表系统
爬虫数据 ──┘ └─────────────┘──► 数据科学
本教程使用 SQL Server，采用每层一个独立数据库的架构。
┌──────────────────────────────────────────────────────────────────┐
│ SQL Server 实例 │
│ │
│ ┌──────────────┐ │
│ │ business_db │ ← OLTP 业务系统（演示用源系统） │
│ └──────────────┘ │
│ ↓ Extract（直接抽取） │
│ ┌──────────────┐ │
│ │ psa_db │ ← PSA 层：持久化暂存，保留全部历史 │
│ └──────────────┘ │
│ ↓ Transform（维度建模，SCD Type 2） │
│ ┌──────────────┐ │
│ │ star_db │ ← Star Schema 层：事实表 + 维度表 │
│ └──────────────┘ │
│ ↓ Aggregate（按业务域聚合） │
│ ┌──────────────┐ │
│ │ mart_db │ ← Data Mart 层：面向业务的数据集市 │
│ └──────────────┘ │
│ │
│ ┌──────────────┐ │
│ │ etl_db │ ← ETL 日志库（跨层共享） │
│ └──────────────┘ │
│ │
│ 跨库引用方式：database.dbo.table │
└──────────────────────────────────────────────────────────────────┘
| 优势 | 说明 |
|---|---|
| 安全隔离 | 每层可独立设置访问权限，用户只能访问授权的层 |
| 性能隔离 | ETL 写入不影响分析查询，IO 互不干扰 |
| 备份恢复 | 可按层独立备份/恢复，粒度更细 |
| 存储管理 | 不同层可放在不同文件组/磁盘，优化 IO |
| 迁移灵活 | 可将某一层迁移到其他服务器，不影响其他层 |
-- ============================================================
-- 在 SQL Server 中创建数据仓库各层数据库
-- ============================================================
USE master;
GO
-- ----------------------------------------------------------
-- 1. 业务系统数据库（OLTP 源系统，演示用）
-- ----------------------------------------------------------
CREATE DATABASE business_db;
GO
-- ----------------------------------------------------------
-- 2. PSA 层数据库（Persistent Staging Area，持久化暂存区）
-- 直接接收源系统数据，保留完整历史
-- ----------------------------------------------------------
CREATE DATABASE psa_db;
GO
-- ----------------------------------------------------------
-- 4. Star Schema 层数据库（维度建模层）
-- 事实表 + 维度表
-- ----------------------------------------------------------
CREATE DATABASE star_db;
GO
-- ----------------------------------------------------------
-- 5. Data Mart 层数据库（数据集市层）
-- 面向特定业务域的聚合数据
-- ----------------------------------------------------------
CREATE DATABASE mart_db;
GO
-- ----------------------------------------------------------
-- 6. ETL 日志数据库（跨层共享）
-- ----------------------------------------------------------
CREATE DATABASE etl_db;
GO
ETL 日志表放在独立数据库中，供所有层共享写入：
USE etl_db;
GO
-- ETL 执行日志表
CREATE TABLE dbo.etl_log (
log_id BIGINT IDENTITY(1,1) PRIMARY KEY,
batch_id VARCHAR(50) NOT NULL,
layer_name VARCHAR(20) NOT NULL, -- psa/dv/star/mart
db_name VARCHAR(50) NOT NULL, -- 对应的数据库名
table_name VARCHAR(100) NOT NULL,
start_time DATETIME NOT NULL DEFAULT GETDATE(),
end_time DATETIME,
rows_extracted BIGINT DEFAULT 0,
rows_inserted BIGINT DEFAULT 0,
rows_updated BIGINT DEFAULT 0,
rows_rejected BIGINT DEFAULT 0,
status VARCHAR(20) DEFAULT 'RUNNING', -- RUNNING/SUCCESS/FAILED
error_message NVARCHAR(MAX),
created_at DATETIME DEFAULT GETDATE()
);
GO
-- 创建索引
CREATE NONCLUSTERED INDEX idx_etl_log_batch
ON dbo.etl_log(batch_id);
CREATE NONCLUSTERED INDEX idx_etl_log_status
ON dbo.etl_log(status);
CREATE NONCLUSTERED INDEX idx_etl_log_layer
ON dbo.etl_log(layer_name);
GO
工具函数放在 etl_db 中，供各层 ETL 调用：
USE etl_db;
GO
-- ============================================================
-- 获取当前 ETL 批次时间戳
-- ============================================================
CREATE FUNCTION dbo.get_etl_timestamp()
RETURNS DATETIME
AS
BEGIN
RETURN GETDATE();
END;
GO
-- ============================================================
-- 生成 Hash Key（用于业务键哈希）
-- 输入：业务键文本
-- 输出：32 位 MD5 哈希字符串（大写）
-- ============================================================
CREATE FUNCTION dbo.generate_hash_key(@input_text NVARCHAR(MAX))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @result VARCHAR(32);
SET @result = SUBSTRING(
CONVERT(VARCHAR(32), HASHBYTES('MD5', UPPER(LTRIM(RTRIM(ISNULL(@input_text, N'NULL'))))), 2),
1, 32
);
RETURN @result;
END;
GO
-- ============================================================
-- 生成复合 Hash Key（用于差异检测）
-- 输入：以 '||' 分隔的多个字段拼接字符串
-- 输出：32 位 MD5 哈希字符串（大写）
-- ============================================================
CREATE FUNCTION dbo.generate_composite_hash(@combined_text NVARCHAR(MAX))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @result VARCHAR(32);
SET @result = SUBSTRING(
CONVERT(VARCHAR(32), HASHBYTES('MD5', UPPER(LTRIM(RTRIM(ISNULL(@combined_text, N'NULL'))))), 2),
1, 32
);
RETURN @result;
END;
GO
-- ============================================================
-- 生成日期键（YYYYMMDD 整数格式，用于维度表）
-- ============================================================
CREATE FUNCTION dbo.generate_date_key(@input_date DATE)
RETURNS INT
AS
BEGIN
RETURN YEAR(@input_date) * 10000 + MONTH(@input_date) * 100 + DAY(@input_date);
END;
GO
-- ============================================================
-- 验证所有数据库已创建
-- ============================================================
USE master;
GO
SELECT
name AS database_name,
database_id,
create_date,
state_desc AS state
FROM sys.databases
WHERE name IN (
N'business_db',
N'psa_db',
N'dv_db',
N'star_db',
N'mart_db',
N'etl_db'
)
ORDER BY name;
GO
-- ============================================================
-- 测试工具函数
-- ============================================================
USE etl_db;
GO
-- 测试 Hash Key 生成
SELECT etl_db.dbo.generate_hash_key(N'CUSTOMER-001') AS customer_hash;
SELECT etl_db.dbo.generate_composite_hash(N'C001||P001||2024-01-15') AS composite_hash;
-- 测试日期键生成
SELECT etl_db.dbo.generate_date_key('2024-01-15') AS date_key; -- 返回 20240115
-- 测试时间戳函数
SELECT etl_db.dbo.get_etl_timestamp() AS etl_ts;
GO
在 SQL Server 中，跨库访问使用 三段式 命名：
[数据库名].[schema名].[对象名]
本教程中的常用跨库引用示例：
-- 从业务库抽取数据到 PSA 层（直接抽取）
INSERT INTO psa_db.dbo.orders (...)
SELECT ... FROM business_db.dbo.orders;
-- 从 PSA 层转换到 Data Vault 层
INSERT INTO dv_db.dbo.hub_customer (...)
SELECT ... FROM psa_db.dbo.customers;
-- 从 Data Vault 层构建 Star Schema
INSERT INTO star_db.dbo.fact_order (...)
SELECT ... FROM dv_db.dbo.hub_customer h
JOIN dv_db.dbo.sat_customer s ON h.hub_customer_key = s.hub_customer_key;
-- 从 Star Schema 聚合到 Data Mart
INSERT INTO mart_db.dbo.sales_summary (...)
SELECT ... FROM star_db.dbo.fact_order f
JOIN star_db.dbo.dim_date d ON f.order_date_key = d.date_key;
注意：跨库引用要求所有数据库在同一 SQL Server 实例下。如需跨实例访问，需配置链接服务器（Linked Server）。
┌─────────────────────────────────────────────────────────────────────┐
│ 数据仓库分层架构 │
│ │
│ business_db OLTP 业务系统（演示用源系统） │
│ │ orders, customers, products │
│ │ │
│ ↓ Extract（直接抽取到 PSA） │
│ │ │
│ psa_db PSA 层 —— 持久化暂存，保留全部历史 │
│ │ orders, customers, products + 版本控制字段 │
│ │ │
│ ↓ Transform（Fact & Dim 建模） │
│ │ │
│ star_db Star Schema 层 —— 维度模型 │
│ │ fact_order, dim_customer, dim_product, dim_date... │
│ │ │
│ ↓ Aggregate（按业务域聚合） │
│ │ │
│ mart_db Data Mart 层 —— 数据集市 │
│ sales_summary, customer_rfm, finance_report... │
│ │
│ etl_db ETL 日志库（跨层共享） │
│ etl_log, 工具函数 │
└─────────────────────────────────────────────────────────────────────┘
| 概念 | 要点 |
|---|---|
| 数据仓库定义 | 面向主题、集成、非易失、时变 |
| 与 OLTP 区别 | 分析 vs 操作，历史 vs 当前，大查询 vs 小事务 |
| 历史演进 | 1988年提出 → 1990s成熟 → 2000s工具化 → 2010s云化 |
| 核心价值 | 统一口径、历史保存、性能隔离、决策支持 |
| 分库架构 | business_db → psa_db → dv_db → star_db → mart_db |
| 跨库引用 | database.dbo.table 三段式命名 |
| ETL 日志 | 独立 etl_db，所有层共享写入 |
| 工具函数 | etl_db.dbo.generate_hash_key / generate_composite_hash / generate_date_key |
数据仓库的方法论，大方向都差不多，但是实现细节以及各个地方的叫法并不是非常的统一，这个系列是以我个人的经验总结配合AI的整理而成，如果您遇到的方法论有所不同，也欢迎在这里进行分享。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。