目的

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.yml

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）を使用しています。

generate_surrogate_key.sql

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を利用し数値型のサロゲートキーを生成可能にするマクロを作成しました。

generate_dense_rank_surrogate_key .sql

{% 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 %}

こんな使い方!

int_dim_customers.sql

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の重複をチェックすると良いです。

int_dim_customers.yml

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で評価可能にします。

tests/generic/validate_line_total_test.sql

/*
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 %}

model/marts/obt_sales.yml

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 --target option when issuing a dbt command.

dbtコマンド実行時に--targetオプションにより実行先の環境を切り替えられますが、env_var関数を使うことで同じことを実現できます。
targetをシステムの環境変数に登録し、env_var関数をprofiles.ymlに組み込みます。

profiles.yml

 target: "{{ env_var('DBT_ENV', 'dev') }}"

export DBT_ENV=staging
dbt run

full_refreshオプション

dbt_project.yml

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の環境構築はこちらの記事を参考にさせてもらいました！ありがとうございます！