本文接上篇,从概念走向实操:基于本项目的 5 个模型,讲清楚分层架构的设计原则和每个模型的 SQL 怎么写。
前 3 篇讲了概念、对比和配置:
dbt_project.yml。分层架构(raw / staging / marts)是 dbt 社区的事实标准。它不是 dbt 强制的,但几乎所有 dbt 项目都这么干。原因很简单:把"读源数据"和"算业务指标"分开,数仓才不会变成一锅炖。本文就按这套分层,把每层的 SQL 拆开讲。
为简化示例，本文采取精简版的数据分层。生产环境请勿参考本文的分层策略。
传统存储过程式数仓长这样:一个超大 SQL,直接从业务库读表,中间 join、过滤、聚合、再 join,最后落到一张报表表。问题:
把流程切成三层,每层只做一件事:
| 层 | 职责 | 本项目实现 | 物化 |
|---|---|---|---|
| raw | 原样落库,源系统数据照搬 | dbt seed 加载 CSV | seed (表) |
| staging | 1:1 投影源表,只做重命名+类型规范,不加业务 | stg_customers / stg_orders / stg_payments | view |
| marts | 面向业务消费,做聚合/关联,产出 dim/fact | dim_customers / fct_orders | table |
+-------------+ +----------------+ +----------------+
| raw 层 | | staging 层 | | marts 层 |
| (源系统落地) | | (1:1 规范投影) | | (业务聚合) |
+-------------+ +----------------+ +----------------+
| raw_customers|----->| stg_customers |----->| dim_customers |
| raw_orders |----->| stg_orders |----->| fct_orders |
| raw_payments |----->| stg_payments | | |
+-------------+ +----------------+ +----------------+
dbt seed ref()/view ref()/table
每一层只读上一层,绝不跨层引用。这就是分层建模的核心约束。
在写 staging 之前,得先告诉 dbt"源表在哪"。这就是 [sources.yml](file:///Users/wadesong/Documents/trae_projects/dbtms/models/staging/sources.yml) 的作用。
version: 2
sources:
- name: raw
# seeds 落在 dbt_dev_raw schema (target.schema=dbt_dev + custom_schema=raw)
schema: dbt_dev_raw
description: "源系统原始数据, 由 dbt seed 加载."
tables:
- name: raw_customers
description: "客户主数据原始表."
- name: raw_orders
description: "订单原始表."
- name: raw_payments
description: "支付流水原始表."
如果直接写 from dbt_dev_raw.raw_customers,表名是硬编码的字符串,dbt 完全不知道它的存在。用 source('raw', 'raw_customers') 则有三个好处:
ref staging 时,整条链路从源表到最终表全可见。loaded_at_field 和 freshness,dbt 能检查源表是不是过期了(本项目暂未启用,但接口预留)。name: raw:source 组名,后续 source('raw', ...) 的第一个参数。schema: dbt_dev_raw:实际落在哪个 schema。本项目用 dbt-core 标准拼接,target.schema=dbt_dev + custom_schema=raw → dbt_dev_raw。description:组级别的描述,文档用。tables:本组下的源表清单。
name: raw_customers:表名,source('raw', 'raw_customers') 的第二个参数。description:表级别描述。cast 把字符串/不规范的类型规范成标准类型。-- staging 层: 对 raw_customers 做字段重命名与类型规范,
-- 保持 1:1 投影, 不做业务过滤与聚合.
select
cast(id as int) as customer_id,
first_name,
last_name
from {{ source('raw', 'raw_customers') }}
逐行解读:
cast(id as int) as customer_id:把源表通用的 id 字段重命名为业务可读的 customer_id,同时显式转成 int。这一步让下游模型不再操心类型。first_name / last_name:原名已经规范,原样保留。from {{ source('raw', 'raw_customers') }}:用 Jinja 的 source() 引用源表。编译时 dbt 会把它替换成 dbt_dev_raw.raw_customers。-- staging 层: 对 raw_orders 做字段重命名与类型规范.
select
cast(id as int) as order_id,
cast(user_id as int) as customer_id,
cast(order_date as date) as order_date,
status
from {{ source('raw', 'raw_orders') }}
-- staging 层: 对 raw_payments 做字段重命名与类型规范.
select
cast(id as int) as payment_id,
cast(order_id as int) as order_id,
payment_method,
cast(amount as numeric(18, 2)) as amount,
status
from {{ source('raw', 'raw_payments') }}
类型转换的差异化处理:
| 字段 | 源类型(推测) | cast 目标 | 原因 |
|---|---|---|---|
order_date |
datetime / varchar | date |
业务只关心日期,不需要时间部分,统一为 date |
amount |
varchar / float | numeric(18, 2) |
金额必须用定点数,避免浮点误差 |
id / user_id / order_id |
bigint / int | int |
统一主键类型,join 时类型必须一致 |
注意 user_id → customer_id 这种重命名:源系统叫 user,业务口径叫 customer,staging 这层就把术语对齐,下游永远不用关心"user"这个词。
看 [dbt_project.yml]的配置:
models:
dbt_sqlserver_dw:
staging:
+materialized: view
+schema: staging
marts:
+materialized: table
+schema: marts
这里只是为了讲解技术细节所以使用View。实际项目中，我强烈建议这一层用table，方便后续数据加载问题的回溯。
marts 是面向业务消费的层,做两件事:
本项目 marts 采用星型模型,产出两类表:
dim_customers 描述客户。fct_orders 描述订单。-- 客户维度表: 汇总每个客户的首末订单时间、订单数、累计消费金额 (LTV).
with customers as (
select * from {{ ref('stg_customers') }}
),
orders as (
select
customer_id,
min(order_date) as first_order_date,
max(order_date) as most_recent_order_date,
count(order_id) as number_of_orders
from {{ ref('stg_orders') }}
group by customer_id
),
payments as (
select
o.customer_id,
sum(p.amount) as lifetime_value
from {{ ref('stg_orders') }} o
inner join {{ ref('stg_payments') }} p
on o.order_id = p.order_id
where p.status = 'completed'
group by o.customer_id
)
select
c.customer_id,
c.first_name,
c.last_name,
o.first_order_date,
o.most_recent_order_date,
o.number_of_orders,
coalesce(p.lifetime_value, 0) as lifetime_value
from customers c
left join orders o
on c.customer_id = o.customer_id
left join payments p
on c.customer_id = p.customer_id
-- 订单事实表: 每个订单一行, 关联客户与已完成支付的金额.
with orders as (
select * from {{ ref('stg_orders') }}
),
payments as (
select
order_id,
sum(amount) as total_amount
from {{ ref('stg_payments') }}
where status = 'completed'
group by order_id
)
select
o.order_id,
o.customer_id,
o.order_date,
o.status,
coalesce(p.total_amount, 0) as amount
from orders o
left join payments p
on o.order_id = p.order_id
事实表设计要点:
amount——订单的已完成支付总额。customer_id——指向 dim_customers 的外键。order_id 聚合支付(一个订单可能有多笔支付),同样只计 completed。amount 兜底 0。注意 dim_customers 和 fct_orders 共享同一份 stg_orders 和 stg_payments 的引用——分层后,源数据只在 staging 落地一次,marts 层各自消费,不重复读源表。
with ... as 是 Common Table Expression(CTE)。dbt 社区强烈推荐用 CTE,而不是嵌套子查询。
看 dim_customers 的结构:
with customers as (...),
orders as (...),
payments as (...)
select ... from customers
left join orders ...
left join payments ...
自上而下,每个 CTE 是一个逻辑步骤,命名清晰:customers / orders / payments。读 SQL 时,先看三个 CTE 各做什么,再看最后的 SELECT 怎么拼。逻辑是线性的,不用在脑子里维护嵌套层级。
customers / orders / payments),不用 t1 / t2 / subquery。传统写法常常是嵌套子查询:
select c.customer_id, c.first_name, o.first_order_date, ...
from (select * from dbt_dev_raw.raw_customers) c
left join (
select customer_id, min(order_date) as first_order_date, ...
from (select * from dbt_dev_raw.raw_orders) o
group by customer_id
) o on c.customer_id = o.customer_id
...
这种写法:
CTE 把嵌套拍平,每一步都命名,可读性碾压。
前面所有 marts 模型都用 {{ ref('stg_customers') }}。ref() 是 dbt 最核心的函数,做三件事:
ref('stg_customers') 编译后变成实际的 schema 限定表名:
ref('stg_customers') → dbt_dev_staging.stg_customers
schema 拼接规则来自 dbt_project.yml 的 +schema: staging,叠加 target.schema=dbt_dev,得到 dbt_dev_staging。你只写 ref('stg_customers'),dbt 替你算出全路径。
dbt 扫描模型里的 ref() 和 source(),自动构建有向无环图(DAG)。本项目的 DAG:
source:raw.raw_customers ──> stg_customers ──┐
├──> dim_customers
source:raw.raw_orders ────> stg_orders ────┬─┤
│ ├──> fct_orders
source:raw.raw_payments ──> stg_payments ─┤
│
(stg_orders ─┘
stg_payments 也被 dim_customers
的 payments CTE 引用)
改了 stg_orders,运行 dbt run 时:
ref),不声明"先跑谁"——这就是声明式的好处。传统存储过程要靠 Agent 作业里的步骤顺序,改顺序得改作业配置;dbt 里改了依赖,执行顺序自动更新。错误示例:
-- stg_orders 里写聚合, 违反 1:1 原则
select customer_id, count(*) as order_cnt
from {{ source('raw', 'raw_orders') }}
group by customer_id
问题:staging 失去"源表投影"的语义,下游想看订单明细就没了。聚合永远放 marts,staging 保持 1:1。
错误示例:
-- fct_orders 里直接读源表, 跳过 staging
select * from {{ source('raw', 'raw_orders') }}
问题:跳过 staging 等于绕过了类型规范和重命名,marts 层要自己处理类型转换,逻辑混在一起。而且 source 被多次直接引用,血缘和新鲜度测试失效。正确做法:marts 永远 ref staging。
错误示例:把 dim_customers 和 fct_orders 塞进一个 customer_orders_summary.sql,既算维度又算事实。
问题:职责混乱,下游想用维度还得解析这个大表。星型模型的核心是 dim 和 fact 分开,各司其职。一个模型只产出一个业务实体或业务事件。
错误示例:
select * from (
select * from (
select * from {{ ref('stg_orders') }}
) a where status = 'completed'
) b
问题:可读性差、难维护、难测试。dbt 模型一律用 CTE,每个 CTE 一个逻辑步骤。
分层建模的核心要点:
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。