目的
Building a Kimball dimensional model with dbtを通して、ディメンショナルモデリングを体験しながら、構築にあたり工夫した点などを備忘録的に整理します。
データはダミーデータを利用し、チュートリアルを参考にしつつも一からディメンショナルモデリングに挑戦していきたいと思います。
何をつくるか
下図のように正規化されたテーブル群からスタースキーマを構築していきます。
モデリング後のER図はdbterdでER図を作成するを参照してください。
動作環境
DBはSnowflakeを利用します。
> wsl -l -v
NAME STATE VERSION
* Ubuntu-20.04 Running 2
$ dbt --version
Core:
- installed: 1.8.7
Plugins:
- snowflake: 1.8.4
packages:
- package: dbt-labs/dbt_utils
version: ["1.0.0"]
dbtのディレクトリ構成
チュートリアルから少し変更した構成になっています。
$ tree
.
├── analyses
├── dbt_packages
│ └── dbt_utils
├── dbt_project.yml
├── logs
├── macros
│ ├── generate_dense_rank_surrogate_key.sql
│ └── override_default_schema_name.sql
├── models
│ └── intermediate
│ │ ├── int_dim_customers.sql
│ │ ├── int_dim_customers.yml
│ │ ├── int_dim_dates.sql
│ │ ├── int_dim_dates.yml
│ │ ├── int_dim_products.sql
│ │ ├── int_dim_products.yml
│ │ ├── int_fact_sales.sql
│ │ ├── int_fact_sales.yml
│ └── marts
│ ├── obt_sales.sql
│ └── obt_sales.yml
├── packages.yml
├── seeds
│ ├── customers
│ │ ├── addresses.csv
│ │ ├── addresses.yml
│ │ ├── customers.csv
│ │ └── customers.yml
│ ├── dates
│ │ ├── dates.csv
│ │ └── dates.yml
│ ├── products
│ │ ├── products.csv
│ │ └── products.yml
│ └── sales
│ ├── order_details.csv
│ ├── order_details.yml
│ ├── order_headers.csv
│ └── order_headers.yml
├── snapshots
├── target
└── tests
└── generic
└──validate_line_total_test.sql
構築にあたり工夫した点
DENSE_RANKによるサロゲートキーの生成
公式チュートリアルでは代理キーを生成するために、dbt_utilsというパッケージで提供されているdbtマクロ（generate_surrogate_key）を使用しています。
select
{{ dbt_utils.generate_surrogate_key(['c.customer_id']) }} as customer_key
, customer_id
...
from int_dim_customers as c
left join int_dim_addresses as a on c.postal_code=a.postal_code
ただしこのマクロは対象カラムをstring型に変換しhash値を返却するため、テーブル間の結合コストの観点では改善の余地がありそうです。
そこで、window関数のDENSE_RANKを利用し数値型のサロゲートキーを生成可能にするマクロを作成しました。
{% macro generate_dense_rank_surrogate_key(table, partition_by=[], order_by_columns=[], use_partition=True) %}
DENSE_RANK() OVER (
{% if use_partition and partition_by %}
PARTITION BY {{ partition_by | join(', ') }}
{% endif %}
ORDER BY {{ order_by_columns | join(', ') }}
)
{% endmacro %}
こんな使い方!
select
{{ generate_dense_rank_surrogate_key(
table='int_dim_customers',
partition_by=[],
order_by_columns=['customer_id'],
use_partition=False
) }} AS customer_key
, customer_id
...
from int_dim_customers as c
left join int_dim_addresses as a on c.postal_code=a.postal_code
生成されているクエリも期待通りです!
select
DENSE_RANK() OVER (ORDER BY customer_id) AS customer_key
, customer_id
...
from int_dim_customers as c
left join int_dim_addresses as a on c.postal_code=a.postal_code
);
DENSE_RANKにより生成されたサロゲートキーのテスト
DENSE_RANKの挙動はスキップなく連続した順位が割り振られます。
| value | DENSE_RANK |
|---|---|
| 100 | 1 |
| 90 | 2 |
| 90 | 2 |
| 80 | 3 |
テストではnot_null, uniqueテストを必須とすることで、上記ケースのようなKEYの重複をチェックすると良いです。
version: 2
models:
- name: int_dim_customers
columns:
- name: customer_key
description: The surrogate key of the customer_id
tests:
- not_null
- unique
カスタムテスト
マートの作成時にline_totalという集計カラムを作成します(単価と数量を掛け合わせた行単位の金額)
このカラムは、int_fact_sales.quantity * int_dim_products.priceの式により算出します。
マートテーブルのline_totalが正しい式により算出されているかどうかを判定するカスタムテストを作成しdbt testで評価可能にします。
/*
description:
マートのline_total値と、
int_fact_sales.qty, int_dim_products.priceから生成されたline_totalが一致するかどうかを判定する
*/
{% test validate_line_total_test(model, column_name, quantity_column, price_column, fact_table, dim_table, join_condition) %}
{% if execute %}
with mart_line_total_data as (
select
f.{{ column_name }} as actual_line_total,
from {{ model }} as f
)
, target_line_total_data as (
SELECT
s.{{ quantity_column }} * p.{{ price_column }} as expected_line_total
from {{ fact_table }} as s
left join {{ dim_table }} as p on {{ join_condition }}
)
, mart_minus_target as (
select *
from mart_line_total_data
except select * from target_line_total_data
)
, target_minus_mart as (
select * from target_line_total_data
except select * from mart_line_total_data
)
select * -- 結果行数が0ならテストは成功、1行以上なら失敗
from (
select count(*) as count
from mart_minus_target
union all select * from target_minus_mart
) as t
where count != 0
{% endif %}
{% endtest %}
version: 2
models:
- name: obt_sales
...
columns:
- name: line_total
description: The revenue obtained by multiplying unitprice of int_dim_product and qty of int_fact_sales
tests:
- validate_line_total_test:
quantity_column: "quantity"
price_column: "price"
fact_table: "{{ ref('int_fact_sales') }}"
dim_table: "{{ ref('int_dim_products') }}"
join_condition: "s.product_key = p.product_key"
dbtコマンドのオプション
env_var関数によるtargetの切り替え
If you do have multiple targets in your profile, and want to use a target other than the default, you can do this using the
--targetoption when issuing a dbt command.
dbtコマンド実行時に--targetオプションにより実行先の環境を切り替えられますが、env_var関数を使うことで同じことを実現できます。
targetをシステムの環境変数に登録し、env_var関数をprofiles.ymlに組み込みます。
target: "{{ env_var('DBT_ENV', 'dev') }}"
export DBT_ENV=staging
dbt run
full_refreshオプション
seeds:
dbt_modeling:
+full_refresh: true
+full_refresh: true は dbt seed の実行時に既存のシードのテーブルを一度削除してから再度テーブルを作成するオプション設定です。dbt_project.ymlに組み込めます。
ただし、決まった時間までにデータを提供する必要がある場合、full refreshは時間がかかることが予想されるため、別アプローチを検討する必要がありそうです。
dbterdでER図を作成する
dbterdとは
dbtアーティファクトファイル(manifest.json, catalog.json)からER図をコード（DBML, Mermaidなど）で生成してくれるツールです。
dbtのrelationsip_testの内容からリレーションを生成しています。
モデリング後のER図は、mermaidで下図のように生成されました！
最後に
チュートリアルをなぞるだけでなく自分で構築してみると、もっとこうしたい！があふれてきますね。
dbtについて勉強したい気持ちがより高まりました。
今後はフォルダ構成や命名規則等のベストプラクティスについても学びたいです。
また、他のOSSと統合してより良いメタデータ管理方法についても勉強していきたいです。
本記事が少しでも参考になれば幸いです。
リファレンス
チュートリアル：Building a Kimball dimensional model with dbt
dbtオプション:
スペシャルサンクス
dbterdの環境構築はこちらの記事を参考にさせてもらいました！ありがとうございます！