ETL staging多源学生数据关联一致性问题咨询

核心解决思路：用「源系统标识+业务唯一键」组合唯一标记数据 #

不同大学的源主键重复是硬伤，必须给每条staging数据加上两个关键标识：

• SourceSystemID：固定值标记数据来源（比如1=大学1，2=大学2）
• SourceStudentKey：存储源系统里能唯一识别学生的业务键（比如两所大学的MatrNr，这是学生学籍号，一般不会重复）

通过这两个字段的组合，就能在staging区唯一锁定每个学生的身份，彻底解决关联混乱的问题。

第一步：修改Staging区表结构，增强关联能力 #

给现有staging表补充关键字段，同时加约束避免重复数据：

-- 学生位置表：添加源标识和学生业务键
CREATE TABLE [stage].[Stage_StudentLocation]
(
  [ID] INT NOT NULL IDENTITY PRIMARY KEY,
  [SourceSystemID] TINYINT NOT NULL, -- 标记数据来源
  [SourceStudentKey] VARCHAR(100) NOT NULL, -- 源系统学生唯一业务键
  [Country] VARCHAR(100) NOT NULL,
  [City] VARCHAR(100) NOT NULL,
  -- 同一来源的同一学生只能有一条位置记录
  CONSTRAINT UQ_StageStudentLocation_SourceStudent UNIQUE (SourceSystemID, SourceStudentKey)
)

-- 成绩表：添加源标识和学生业务键，同时关联位置表的源键组合
CREATE TABLE [stage].[Stage_Performance]
(
  [ID] INT NOT NULL IDENTITY PRIMARY KEY,
  [SourceSystemID] TINYINT NOT NULL,
  [SourceStudentKey] VARCHAR(100) NOT NULL,
  [Grade] INT NOT NULL,
  [ECTS] FLOAT NOT NULL,
  [StudentLocation_ID] INT FOREIGN KEY REFERENCES [stage].[Stage_StudentLocation](ID),
  -- 额外添加基于源键的外键，避免依赖自增ID的关联风险
  CONSTRAINT FK_StagePerformance_StudentLocation_Source 
  FOREIGN KEY (SourceSystemID, SourceStudentKey) 
  REFERENCES [stage].[Stage_StudentLocation](SourceSystemID, SourceStudentKey)
)

第二步：从源系统加载到Staging区的SQL逻辑 #

大学1数据加载（关联学生和地址表） #

假设大学1有成绩表dbo.Performance关联MatrNr，直接通过学籍号关联学生和地址：

-- 加载学生位置
INSERT INTO [stage].[Stage_StudentLocation] (SourceSystemID, SourceStudentKey, Country, City)
SELECT 
  1 AS SourceSystemID,
  s.MatrNr AS SourceStudentKey,
  sa.Country,
  sa.City
FROM [University1].[dbo].[Student] s
JOIN [University1].[dbo].[StudentAddress] sa ON s.MailAddress = sa.StudentAddressID
-- 避免重复插入
WHERE NOT EXISTS (
  SELECT 1 FROM [stage].[Stage_StudentLocation]
  WHERE SourceSystemID = 1 AND SourceStudentKey = s.MatrNr
)

-- 加载成绩数据
INSERT INTO [stage].[Stage_Performance] (SourceSystemID, SourceStudentKey, Grade, ECTS, StudentLocation_ID)
SELECT
  1 AS SourceSystemID,
  p.MatrNr AS SourceStudentKey,
  p.Grade,
  p.ECTS,
  sl.ID
FROM [University1].[dbo].[Performance] p
JOIN [stage].[Stage_StudentLocation] sl 
  ON sl.SourceSystemID = 1 AND sl.SourceStudentKey = p.MatrNr
WHERE NOT EXISTS (
  SELECT 1 FROM [stage].[Stage_Performance]
  WHERE SourceSystemID = 1 AND SourceStudentKey = p.MatrNr AND Grade = p.Grade AND ECTS = p.ECTS
)

大学2数据加载（解析整串地址） #

大学2的地址是FullAddress整串，这里假设地址格式是「街道, 城市, 国家」，可根据实际格式调整字符串函数：

-- 加载学生位置（解析地址）
INSERT INTO [stage].[Stage_StudentLocation] (SourceSystemID, SourceStudentKey, Country, City)
SELECT
  2 AS SourceSystemID,
  s.MatrNr AS SourceStudentKey,
  -- 提取国家：取最后一个逗号后的内容
  SUBSTRING(s.FullAddress, CHARINDEX(', ', s.FullAddress, CHARINDEX(', ', s.FullAddress)+1)+2, LEN(s.FullAddress)) AS Country,
  -- 提取城市：取两个逗号之间的内容
  SUBSTRING(s.FullAddress, CHARINDEX(', ', s.FullAddress)+2, CHARINDEX(', ', s.FullAddress, CHARINDEX(', ', s.FullAddress)+1) - CHARINDEX(', ', s.FullAddress)-2) AS City
FROM [University2].[dbo].[Student] s
WHERE NOT EXISTS (
  SELECT 1 FROM [stage].[Stage_StudentLocation]
  WHERE SourceSystemID = 2 AND SourceStudentKey = s.MatrNr
)

-- 加载成绩数据
INSERT INTO [stage].[Stage_Performance] (SourceSystemID, SourceStudentKey, Grade, ECTS, StudentLocation_ID)
SELECT
  2 AS SourceSystemID,
  p.MatrNr AS SourceStudentKey,
  p.Grade,
  p.ECTS,
  sl.ID
FROM [University2].[dbo].[Performance] p
JOIN [stage].[Stage_StudentLocation] sl 
  ON sl.SourceSystemID = 2 AND sl.SourceStudentKey = p.MatrNr
WHERE NOT EXISTS (
  SELECT 1 FROM [stage].[Stage_Performance]
  WHERE SourceSystemID = 2 AND SourceStudentKey = p.MatrNr AND Grade = p.Grade AND ECTS = p.ECTS
)

第三步：从Staging区加载到DWH星型架构 #

维度表加载（以学生位置维度为例） #

维度表需要去重，用MERGE语句插入新的唯一位置：

MERGE INTO [dwh].[Dim_StudentLocation] AS target
USING (
  SELECT DISTINCT Country, City FROM [stage].[Stage_StudentLocation]
) AS source ON target.Country = source.Country AND target.City = source.City
WHEN NOT MATCHED THEN
  INSERT (Country, City) VALUES (source.Country, source.City);

事实表加载（以成绩事实表为例） #

通过staging的「源标识+学生业务键」关联位置，再匹配维度表的代理键：

INSERT INTO [dwh].[Fact_Performance] (StudentLocationKey, Grade, ECTS, SourceSystemID, SourceStudentKey)
SELECT
  dl.LocationKey, -- 维度表的代理键
  sp.Grade,
  sp.ECTS,
  sp.SourceSystemID,
  sp.SourceStudentKey
FROM [stage].[Stage_Performance] sp
JOIN [stage].[Stage_StudentLocation] sl 
  ON sp.StudentLocation_ID = sl.ID
JOIN [dwh].[Dim_StudentLocation] dl 
  ON sl.Country = dl.Country AND sl.City = dl.City
WHERE NOT EXISTS (
  SELECT 1 FROM [dwh].[Fact_Performance]
  WHERE SourceSystemID = sp.SourceSystemID AND SourceStudentKey = sp.SourceStudentKey AND Grade = sp.Grade AND ECTS = sp.ECTS
);

适配半自动化SQL生成的优化建议 #

• 建一个dbo.SourceSystems配置表，存储SourceSystemID、大学名称、源数据库名，半自动化工具读取这个表生成对应SQL
• 把地址解析规则存在dbo.AddressParsingRules配置表（比如分隔符、字段位置），生成SQL时动态拼接解析逻辑
• 把重复的「插入+去重」逻辑做成模板，工具只需要替换源表名、字段映射、过滤条件即可

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