dbtとSnowflakeでKimballのディメンショナルモデリングに入門

## 目的
[Building a Kimball dimensional model with dbt](https://docs.getdbt.com/blog/kimball-dimensional-model)を通して、ディメンショナルモデリングを体験しながら、構築にあたり工夫した点などを備忘録的に整理します。
データはダミーデータを利用し、チュートリアルを参考にしつつも一からディメンショナルモデリングに挑戦していきたいと思います。


## 何をつくるか
下図のように正規化されたテーブル群からスタースキーマを構築していきます。
モデリング後のER図は[dbterdでER図を作成する](#dbterdでer図を作成する)を参照してください。

```mermaid
erDiagram

    PRODUCTS {
        int product_id PK
        string product_name
        string category
        float price
    }

    CUSTOMERS {
        int customer_id PK
        string customer_name
        int age
        string gender
        string postal_code
    }

    DATES {
        date date
        string day_of_week
        int month
        int year
    }

    ADDRESSES {
        string postal_code
        string street
        string city
        string state
        string country
    }

    ORDER_HEADERS {
        int order_id PK
        int customer_id FK
        int order_date_id FK
        float total_amount
    }

    ORDER_DETAILS {
        int order_detail_id PK
        int order_id FK
        int product_id FK
        int quantity
        float line_total
    }

    PRODUCTS ||--o{ ORDER_DETAILS : "product_id"
    CUSTOMERS ||--o{ ORDER_HEADERS : "customer_id"
    DATES ||--o{ ORDER_HEADERS : "order_date_id"
    ORDER_HEADERS ||--o{ ORDER_DETAILS : "order_id"
    CUSTOMERS ||--o{ ADDRESSES : "postal_code"
```

 
### 動作環境
DBはSnowflakeを利用します。
```wsl
> wsl -l -v
  NAME            STATE           VERSION
* Ubuntu-20.04    Running         2
```

```sh
$ 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のディレクトリ構成
チュートリアルから少し変更した構成になっています。
``` sh
$ 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
```
生成されているクエリも期待通りです!
``` sql
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') }}" 
```
``` sh
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`の内容からリレーションを生成しています。

https://github.com/datnguye/dbterd

モデリング後のER図は、mermaidで下図のように生成されました！

```mermaid
erDiagram
  "MODEL.DBT_MODELING.INT_DIM_CUSTOMERS" {
    number customer_key
    number customer_id
    text customer_name
    number age
    text gender
    text postal_code
    text street
    text city
    text state
    text country
  }
  "MODEL.DBT_MODELING.INT_DIM_DATES" {
    number date_key
    date date
    text day_of_week
    number month
    number year
  }
  "MODEL.DBT_MODELING.INT_DIM_PRODUCTS" {
    number product_key
    number product_id
    text product_name
    text category
    float price
  }
  "MODEL.DBT_MODELING.INT_FACT_SALES" {
    number order_key
    number customer_key
    number product_key
    number date_key
    number order_id
    number order_detail_id
    number quantity
  }
  "MODEL.DBT_MODELING.INT_FACT_SALES" }|--|| "MODEL.DBT_MODELING.INT_DIM_CUSTOMERS": customer_key
  "MODEL.DBT_MODELING.INT_FACT_SALES" }|--|| "MODEL.DBT_MODELING.INT_DIM_DATES": date_key
  "MODEL.DBT_MODELING.INT_FACT_SALES" }|--|| "MODEL.DBT_MODELING.INT_DIM_PRODUCTS": product_key
```

## 最後に
チュートリアルをなぞるだけでなく自分で構築してみると、もっとこうしたい！があふれてきますね。
dbtについて勉強したい気持ちがより高まりました。

今後はフォルダ構成や命名規則等の[ベストプラクティス](https://docs.getdbt.com/best-practices
)についても学びたいです。
また、他のOSSと統合してより良いメタデータ管理方法についても勉強していきたいです。

本記事が少しでも参考になれば幸いです。

## リファレンス
チュートリアル：[Building a Kimball dimensional model with dbt](https://docs.getdbt.com/blog/kimball-dimensional-model)

dbtオプション: 
* [full_refresh](https://docs.getdbt.com/reference/resource-configs/full_refresh)
* [env_var](https://docs.getdbt.com/reference/dbt-jinja-functions/env_var)

## スペシャルサンクス
dbterdの環境構築はこちらの記事を参考にさせてもらいました！ありがとうございます！

https://zenn.dev/kyami/articles/6c86f3e8ff39c0