在做数仓开发时,为了保存缓慢变化维度的历史信息,需要用到拉链表。以下是使用 dbt——数据加工大师的 snapshot 生成拉链表的办法。
测试环境
1、Postgres 14
2、dbt 1.5
生成测试源表
创建源表的model
: <dbt_project>/models/dim_employee.sql
with source_data as (
select 1 as id, '张三' as name, '数据开发' as job, '数据部' as department, '2023-01-01'::date as updated_dt
union all
select 2 as id, '李四' as name, '前端开发' as job, '开发部' as department, '2023-01-02'::date as updated_dt
union all
select 3 as id, '王舞' as name, 'UI设计' as job, '设计部' as department, '2023-01-03'::date as updated_dt
)
select * from source_data
执行 dbt run
,在postgres
中生成源表
dbt run -s dim_employee
初始化拉链表
创建 snapshot
:<dbt_project>/snapshots/dim_employee_hist.sql
{#
1. 需要用 {% snapshot <table_name> %}{% endsnapshot %} 包裹
2. * target_schema, 指定目标 schema
3. * unique_key, 指定源表中的主键字段
4. * strategy, 指定快照策略, timestamp, 按照更新时间生成快照; check, 指定需要开链的字段
5. updated_at, 指定来源的更新时间字段
#}
{% snapshot dim_employee_hist %}
{{ config(
target_schema='test_exp',
updated_at='updated_dt',
strategy='timestamp',
unique_key='id'
)}}
select * from {{ ref('dim_employee') }}
{% endsnapshot %}
执行dbt snapshot
,初始化目标拉链表后,得
可以看到,拉链表中新增了以下字段:
dbt_scd_id
,代理主键。dbt_updated_at
,更新时间。dbt_valid_from
,拉链开始时间。dbt_valid_to
,拉链结束时间。
变更源表
修改源表dim_employee
中的张三行记录
生成拉链
再次运行 dbt snapshot -s dim_employee_hist
,可以看到dim_employee_hist
表中,张三的记录正确开链。
针对指定字段做开链
如果有些字段不需要开链的,可以:
1、对dim_employee_hist.sql
做以下配置
strategy='check'
:设置快照策略为check
。check_cols=['job', 'name']
:设置需要开链的字段为job
,name
。
{% snapshot dim_employee_hist %}
{{ config(
target_schema='test_exp',
updated_at='updated_dt',
strategy='check',
unique_key='id',
check_cols=['job','name']
)}}
select * from {{ ref('dim_employee') }}
{% endsnapshot %}
- 重新生成源表和初始化拉链表,将李四的
department
修改为数据部,张三的job
改为后端开发,同时修改updated_dt
- 执行
dbt snapshot -s dim_employee_hist
后,dim_employee_hist
对张三修改做了开链,李四的修改没有开链,正确。