dbtをちょっとさわってみる

# はじめに
データ分析処理のELTにおけるT（データストア内でのデータ変換）の実施をサポートするツールである`dbt`を触ってみる。

https://www.getdbt.com/

### dbtを使って解決したいこと
* 1SQLファイル内に複数のクエリが記述されているデータ処理を解読しやすくできないか
* 繰り返しクエリを修正しても、データ品質を担保できるか
* データ仕様を誰でも理解できる状態にできないか

# やってみた
とりあえず簡単に触ってみたかったので、dbt Cloud(Developerプラン)を利用する。

https://www.getdbt.com/pricing

事前にサンプルデータとして、BigQuery上に購入者データと購入データのテーブルを用意する。
* raw_customers
  * id (integer)
  * first_name (string)
  * last_name (string)
* raw_orders
  * id (integer)
  * user_id (integer)
  * order_date (date)
  * status (string)

### 1SQLファイル内に複数のクエリが記述されているデータ処理を解読しやすくできないか
dbtには、テーブルがどのテーブルから生成されたか、テーブル間の関係図を自動生成してくれる機能がある。

https://docs.getdbt.com/terms/data-lineage

`raw_customers`テーブルに`raw_orders`テーブルを使って、`customers`テーブルを作成するクエリを書いてみる。

```sql
-- /models/customers.sql
with customers as (
    select
        id as customer_id,
        first_name,
        last_name
    from {{data_set}}.raw_customers
),
orders as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status
    from {{data_set}}.raw_orders
),
customer_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 orders
    group by 1

),
final as (
    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders
    from customers
    left join customer_orders using (customer_id)
)

select * from final
```

この時のデータリネージは、次のようになる。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327850/7ba11c53-2706-3d4a-baf1-895bd5f8fc73.png" width=50%>

同一SQLファイル内にwith句を使って定義すると、テーブルの関係性は分からない。そこで、with句で定義しているテーブルを別モデルとして管理するように、SQLファイルを分けて書いてみる。

```sql
-- /models/_customers.sql
select
    id as customer_id,
    first_name,
    last_name
from {{data_set}}.raw_customers
```

```sql
-- /models/_orders.sql
select
    id as order_id,
    user_id as customer_id,
    order_date,
    status
from {{data_set}}.raw_orders
```

別モデルとして切り出したテーブルを`ref`機能を用いて参照する。

```sql
-- /models/customers.sql
with customers as (
    select * from {{ ref('_customers') }}
),
orders as (
    select * from {{ ref('_orders') }}
),
customer_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 orders
    group by 1
),
final as (
    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders
    from customers
    left join customer_orders using (customer_id)
)
select * from final
```

すると、データリネージは次のようになる。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327850/892dccb4-d4d2-faf5-d2a2-65d28c40221c.png" width=50%>


`_customers`テーブルと`_orders`テーブルを参照して、`customers`テーブルが生成される。このように可視化されることによって、各テーブルの依存関係を把握することができる。
1SQLファイル内に複数のクエリが記述されていて複雑なクエリほど、依存関係を把握するのに苦労する。クエリのリファクタリングが必要ではあるが、dbtのデータリネージを使えば把握しやすくなりそうだ。

### 繰り返しクエリを修正しても、データ品質を担保できるか
dbtには、各テーブルのデータが期待通りに動作するかテストする機能がある。

https://docs.getdbt.com/docs/build/data-tests

yamlファイルに、各テーブルのカラムにテストケースを追記する。

```yml
-- /models/schema.yml
version: 2

models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: id
```

* `unique`: 一意の値が入っているか
* `not_null`: null値が入っていないか
* `accepted_values`: 指定した値が入っているか
* `relationships`: `customers.customer_id`と結合できるか

今回は単一テストを試してみたが、自分で用意したテストクエリを実行して、結果を確認することもできるようだ。テストを通じてデータ仕様を満たしているか確認することができるため、よりクエリの修正をしやすくなりそうだ。

### データ仕様を誰でも理解できる状態にできないか
dbtは、各テーブルのドキュメントを共有する仕組みがある。

https://docs.getdbt.com/docs/collaborate/documentation

yamlファイルに、各テーブルやカラムの説明文を追記する。

```yml
version: 2

models:
  - name: customers
    description: One record per customer
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: first_order_date
        description: NULL when a customer has not yet placed an order.

  - name: _customers
    description: This model cleans up customer data
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null

  - name: _orders
    description: This model cleans up order data
    columns:
      - name: order_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
```

すると、dbtのGUI上でドキュメントが見られるようになる。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327850/599f128c-d432-148a-e52a-5b6c459bc33f.png" width=70%>

yamlファイルを一から用意すると手間がかかるので、dbtのパッケージ[codegen](https://hub.getdbt.com/dbt-labs/codegen/latest/)を利用すると便利そうだ。既存テーブルの情報をyamlファイルに自動で生成してくれる。

# まとめ
ツール導入によって既存処理や運用フローを見直することで、冒頭で挙げた点を解消することができそうである。dbtには他にも魅力的な機能があるため、引き続きdbtをどう利用するか探っていきたい。