dbt+SQLServer构建数据仓库(2)：与传统方案对比及迁移决策

dbt+SQLServer构建数据仓库(2)：与传统方案对比及迁移决策

本文接上篇的 dbt 定位与工作流程, 讲述:dbt 和 SQL Server 上传统数仓开发相比有哪些范式差异和取舍。

一、引言

上篇我们弄清了 dbt 是什么、一个 dbt 项目怎么跑起来。但"dbt 好不好"是个伪命题——好坏是相对的,得有参照系。这个参照系就是团队现在用的 SQL Server 传统数仓栈。

所以本文先描绘传统方案的典型形态和真实痛点,再从六个维度与 dbt 系统对比,最后给决策矩阵和迁移建议。本文不站队,目标是把两种范式的差异讲透,让你能根据团队情况做判断。

二、传统 SQL Server 数仓项目长什么样

2.1 典型技术栈

2.2 典型开发流程

一个新需求的传统流程:

2.3 传统方案的真实痛点

这些不是理论问题,是项目里实打实遇到的:

三、对比分析:dbt vs 传统 SQL Server 方案

下面从六个维度系统对比。每个维度都给出两者的具体形态和取舍。

3.1 范式差异:声明式 vs 命令式

这是最根本的差异。

传统存储过程(命令式):

CREATE PROCEDURE sp_build_dim_customer AS
BEGIN
TRUNCATE TABLE dim_customer;

INSERT INTO dim_customer (customer_id, first_name, last_name, ltv)
SELECT c.id, c.first_name, c.last_name, SUM(p.amount)
FROM raw_customer c
LEFT JOIN raw_order o ON c.id = o.user_id
LEFT JOIN raw_payment p ON o.id = p.order_id
WHERE p.status = 'completed'
GROUP BY c.id, c.first_name, c.last_name;

— 手动写日志
INSERT INTO etl_log (proc_name, rows_affected, run_at)
VALUES ('sp_build_dim_customer', @@ROWCOUNT, GETDATE());
END

你显式控制每一步:清表、插入、记日志、报错处理。灵活,但样板代码多,每个过程都要重复。

dbt model(声明式):

— models/marts/dim_customers.sql
with customers as (select * from {{ ref('stg_customers') }}),
payments as (
select customer_id, sum(amount) as ltv
from {{ ref('stg_payments') }}
where status = 'completed'
group by customer_id
)
select c.customer_id, c.first_name, c.last_name,
coalesce(p.ltv, 0) as ltv
from customers c
left join payments p on c.customer_id = p.customer_id

# dbt_project.yml 里声明物化
models:
my_project:
marts:
+materialized: table # dbt 自动处理 truncate + insert

你只声明转换逻辑和物化意图,dbt 负责:清表、建表、依赖顺序、日志、错误处理。

取舍:

• 声明式省心,但失去对细节的控制(如自定义事务、特殊锁提示)
• 命令式灵活,但样板代码膨胀,易出错
• dbt 仍可写 pre-hook/post-hook 注入命令式逻辑,两者并非水火不容

3.2 工程化能力对比

这一栏是 dbt 最大的优势所在。传统方案不是不能做这些事,而是每件都要自己搭:自己写测试框架、自己写文档生成器、自己搞 CI 脚本。dbt 开箱即用。

3.3 开发体验对比

开发体验的差异是最直观的。dbt 让数据工程师能像软件工程师一样工作——这是它能在创业公司和云原生团队迅速普及的根本原因。

3.4 性能与调度对比

结论:

• 调度成熟度:传统方案胜。SQL Server Agent 是企业级调度器,dbt 调度需外接
• 转换性能:两者本质都是跑 T-SQL,差异不大。传统方案对底层控制更细
• 增量加载:dbt 声明式更省心,但复杂增量场景(如多源 MERGE + 自定义逻辑)传统方案更灵活

3.5 SQL Server 方言适配

这是我曾经踩过的坑,值得单独提。

传统方案:直接写 T-SQL,方言就是方言,无适配问题。

dbt-sqlserver:dbt-core 是方言无关的,适配器层负责翻译。但 dbt-sqlserver 有几个 legacy 行为与 dbt-core 默认不一致:

我在实际使用的时候，就因为第一个坑(schema 拼接)第一次 dbt run 报错。解决方案是在 dbt_project.yml 加:

flags:
dbt_sqlserver_use_default_schema_concat: true

启示:跨适配器迁移时,generate_schema_name 这类 macro 是高风险点,务必审阅 dbt 输出的所有 WARNING。

3.6 学习曲线与团队适配

关键判断:如果团队是 DBA 主导、已有大量 SSIS 资产,全面迁移 dbt 不现实;如果是数据工程师/分析师主导、新项目起步,dbt 几乎是默认选择。

四、何时选 dbt,何时选传统方案

基于上面的对比,给一个决策矩阵:

4.1 选 dbt 的场景

• ✅ 新项目从零起步,没有历史包袱
• ✅ 团队以数据分析师/工程师为主,熟悉 git/CLI
• ✅ 数据已落库(ELT 模式),只需做 T
• ✅ 重视工程化(测试、CI、文档、代码审查)
• ✅ 未来可能跨云迁移(dbt 适配器机制让迁移成本可控)
• ✅ 数据模型迭代频繁,需要快速试错

4.2 选传统方案的场景

• ✅ 已有大量 SSIS 包 / 存储过程,迁移成本高于收益
• ✅ 团队是 DBA / SQL Server 老兵,dbt 学习成本高
• ✅ 需要重度 OLAP(SSAS 多维模型,dbt 不覆盖)
• ✅ 调度复杂度高(SQL Server Agent 的告警、重试、作业链成熟)
• ✅ 强依赖 SQL Server 特有功能(内存优化表、列存索引、PolyBase)
• ✅ 合规要求严格,微软官方支持更稳妥

4.3 决策树

是新项目吗?
├─ 是 → 团队背景?
│ ├─ 数据工程师/分析师为主 → dbt
│ └─ DBA 为主 → 传统方案 或 dbt(视学习意愿)
└─ 否(已有资产) → 资产规模?
├─ 小(<50 个过程/包) → 可考虑全量迁 dbt
├─ 中 (50-500) → 混合方案, 新需求用 dbt, 老的留着
└─ 大 (>500) → 传统方案维持, 仅新模块评估 dbt

五、混合方案:现实中最常见的落地

实际项目里,纯 dbt 或纯传统方案都少见,混合方案是主流:

┌─────────────────────────────────────────────────────┐
│ 数据源 (MySQL / API / 文件) │
└─────────────────────────────────────────────────────┘
│
│ SSIS / Airbyte 做 E+L (老资产复用)
▼
┌─────────────────────────────────────────────────────┐
│ SQL Server 原始库 (raw schema) │
└─────────────────────────────────────────────────────┘
│
│ dbt 做 T (转换层, 新方式)
│ dbt_project.yml + models/
▼
┌─────────────────────────────────────────────────────┐
│ SQL Server 数仓层 (staging/marts schema) │
└─────────────────────────────────────────────────────┘
│
│ SQL Server Agent / Airflow 调度 dbt
▼
┌─────────────────────────────────────────────────────┐
│ Power BI / SSRS 消费 │
└─────────────────────────────────────────────────────┘

落地策略:

这样既享受 dbt 的工程化红利,又不浪费已有投资。

六、迁移建议

如果团队决定从传统方案迁 dbt,建议:

6.1 渐进式,不要大爆炸

• 选一个独立的小业务域(如某个部门的报表)做试点
• 用 dbt 重建该域的转换,与老存储过程并行运行 1-2 个月
• 数据对齐后切换 BI,下线老过程

6.2 先建分层,再迁逻辑

迁移前先在 dbt 里搭好 raw / staging / marts 三层骨架。老存储过程通常是"一锅炖"——清洗、聚合、业务逻辑混在一起。迁移时趁机分层,质量会提升一档。

6.3 把测试当一等公民

迁一个 model 就配一个 schema.yml 测试。传统方案缺测试,迁移是补测试的最佳时机。没有测试的迁移等于没迁——你无法证明新逻辑和老逻辑等价。

6.4 适配器 flag 一次配齐

为避免踩 schema 拼接的坑。建议 dbt-sqlserver 项目初始就把三个 flag 配上:

flags:
dbt_sqlserver_use_default_schema_concat: true
dbt_sqlserver_use_dbt_transactions: true
dbt_sqlserver_use_native_string_types: true

避免后续逐个踩雷。

6.5 调度平滑过渡

第一阶段:SQL Server Agent 调 dbt run(命令行包一层)
第二阶段:引入 Airflow / Dagster,统一调度新老资产
第三阶段:老存储过程逐步迁成 dbt model,最终下线 Agent Job

七、总结

把全文浓缩成几条:

附:核心概念对照速查表

参考

• dbt 官方文档: https://docs.getdbt.com/
• dbt-sqlserver 适配器: https://github.com/dbt-msft/dbt-sqlserver
• dbt 工程化实践: https://docs.getdbt.com/best-practices
• SQL Server Integration Services: https://learn.microsoft.com/sql/integration-services/