📘
DBTの導入と半年間の運用の振り返り（課題と解決策）
はじめに
dbt-core を導入して数ヶ月経ち知見も溜まってきました。
一度これまであった課題とその解決策について書き残しておきたいと思います。
記事の想定読者
- dbt の基礎を理解している
dbtディレクトリ構成(参考までに)
ディレクトリ構成を記載
dbtのベストプラクティスに則りディレクトリの構造を処理毎のレイヤーに分割してます。
-
- ソースデータからデータモデルを構築する最小単位を作成する。DRYの原則に則って実装を行う。
- やること
- rename
- 型変換
- その他、プロジェクト全体で普遍的な変換(ex. yen -> dollar, 日付の形式変換)
- カテゴリーの変換(caseロジック)
- やらないこと
- ソースもとのデータのJOIN(例外)
- 集計処理
- やること
- ソースデータからデータモデルを構築する最小単位を作成する。DRYの原則に則って実装を行う。
-
- Staging で作成したモデルを使用してドメイン、ビジネスロジックを当てていく
-
- Intermediate のモデルをJOINしエンドユーザーに提供するモデルを作成する
-
Utilities(Optional)
- データモデルを作成する際に広く必要とされるデータモデルを格納(ex. data spine)
CASE1: Default の Generic Test だけだと物足りない
- ✅ dbt-utils の generic_test を使用する。
- ✅ dbt-expectations を使用する。
CASE2: docs の description で冗長性を排除したい
- ✅ 共通カラムの description を作成し各モデルから参照
common_descption.md
{% docs model_a_description %}
This is model_a description
{% enddocs %}
{% docs model_b_description %}
This is model_b description
{% enddocs %}
{% docs type %}
| value | description |
| ---- | ---- |
| A | A type |
| B | B type |
{% enddocs %}
schema.yml
version: 2
models:
- name: model_a
description: '{{ doc("model_a_description") }}'
columns:
- name: type
# 共通のdescriptionを参照する。
description: '{{ doc("type") }}'
- name: model_b
description: '{{ doc("model_b_description") }}'
columns:
- name: type
# 共通のdescriptionを参照する。
description: '{{ doc("type") }}'
CASE3: test coverage を向上させたい
- ✅ dbt_meta_testing
- model に特定の generic test / description の記載を強要できます。
- ✅ dbt-coverage
- test/docs の coverage を計測することが可能です。
- CICD で実施し特定の閾値を coverage が下回ったらエラーが生じるように設定します。
CASE4: model 単位での unit test を行いたい
- ✅ dbt_unit_testing
- 入力の model/source を mock 化することで想定通りの挙動になるかをチェックします。
CASE5: 複雑な caseロジック / 正規表現ロジックの unittest を行いたい
- ✅ ロジックを macro 化して、 unittest を行います。
sample_macro.sql
{% macro sample_macro(column_a, column_b) %}
CASE
WHEN column_b THEN 'ten'
WHEN column_a = 1 THEN 'one'
WHEN column_a = 2 THEN 'two'
WHEN column_a = 3 THEN 'three'
END
{% macro endmacro %}
tests/test_sample_macro.sql
WITH
test_data AS (
SELECT
1 AS column_a
, false AS column_b
, 'one' AS expected_value
UNION ALL
SELECT
2 AS column_a
, false AS column_b
, 'two' AS expected_value
UNION ALL
SELECT
3 AS column_a
, false AS column_b
, 'three' AS expected_value
UNION ALL
SELECT
1 AS column_a
, true AS column_b
, 'ten' AS expected_value
)
,test_result AS (
SELECT
{{ sample_macro('column_a','column_b') }} as actual_value
, expected_value
FROM
test_data
)
SELECT * FROM test_result
WHERE
expcted_value != actual_value
CASE6: formatter を導入したい
- ✅ dbt Cloud でも導入されている sqlfluff を使います。
CASE7: incremental model の修正時のみ full refresh させたい
- ✅ fileの差分を検知してCICD上で full refresh を行います。
CASE8(dbt*snowflake): コストを可視化したい
- ✅ まずは dbt-snowflake-monitoring を導入するか検討します。
- ✅ dbt-snowflake-monitoring が要件に合わない場合。
dbt-snowflake-monitoring の github コードを参考に自分でコストモデルを作成します。
以下のブログでで、コスト計算ロジックが解説されています。
CASE9: dbt Labs のベストプラクティスに則ってリファクタしたい
- ✅ dbt_project_evaluator を使用する。
CASE10: 環境別で DB の SCHEMA 名を変更したい。
- ✅ generate_schema_name macro を作って対応する。
generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
{%- set default_schema = target.schema -%}
# target.nameによってスキーマのロジックを変更する。
{%- if target.name not in ['production'] and custom_schema_name is not none -%}
{{ default_schema }}_{{ custom_schema_name | trim }}
{%- else -%}
{{ default_schema }}
{%- endif -%}
{%- endmacro %}
CASE11: documents の overview を上書きしたい
- ✅ docs block を作成して上書きする。
models/overview.md
{% docs __overview__ %}
# This is a sample project
{% enddocs %}
CASE12: documents に表示するモデルを制御したい
- ✅ model にdocs fieldを追加する
models/schema.yml
version: 2
models:
- name: model_name
docs:
show: true | false
CASE13: model yml の作成を高速化したい
- ✅ ChatGPT request prompt を作成して使用する
step1. dbt-style YAMLをdbtに学習させる(Jaffle Shopのymlを拝借)
ChatGPT 学習用サンプルテキスト
Memorize this as an example of dbt-style YAML file;
version: 2
models:
- name: customers
description: This table has basic information about a customer, as well as some derived facts based on a customer's orders
columns:
- name: customer_id
description: This is a unique identifier for a customer
tests:
- unique
- not_null
- name: first_name
description: Customer's first name. PII.
- name: last_name
description: Customer's last name. PII.
- name: first_order
description: Date (UTC) of a customer's first order
- name: most_recent_order
description: Date (UTC) of a customer's most recent order
- name: number_of_orders
description: Count of the number of orders a customer has placed
- name: total_order_amount
description: Total value (AUD) of a customer's orders
- name: orders
description: This table has basic information about orders, as well as some derived facts based on payments
columns:
- name: order_id
tests:
- unique
- not_null
description: This is a unique identifier for an order
- name: customer_id
description: Foreign key to the customers table
tests:
- not_null
- relationships:
to: ref('customers')
field: customer_id
- name: order_date
description: Date (UTC) that the order was placed
- name: status
description: '{{ doc("orders_status") }}'
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
- name: amount
description: Total amount (AUD) of the order
tests:
- not_null
- name: credit_card_amount
description: Amount of the order (AUD) paid for by credit card
tests:
- not_null
- name: coupon_amount
description: Amount of the order (AUD) paid for by coupon
tests:
- not_null
- name: bank_transfer_amount
description: Amount of the order (AUD) paid for by bank transfer
tests:
- not_null
- name: gift_card_amount
description: Amount of the order (AUD) paid for by gift card
tests:
- not_null
step2. prompt を使用して dbt-style YAML を生成させる。
Write me a dbt style YAML file to describe the following table:
create or replace TRANSIENT TABLE STAGING.BASE.TEST_TABLE (
ID NUMBER(38,0),
CREATED AT TIMESTAMP_NTZ(9),
UPDATED AT TIMESTAMP_NTZ(9),
VERSION NUMBER(38,0),
NAME VARCHAR(16777216)
)
CASE14: dbdocs でドキュメントを管理したい
- ✅ dbterd を使用する
Discussion
素晴らしいですね、私たちは基本的に同じことをしています。ドキュメントの自動生成にはdbt-osmosisを使用しました。
dbt-osmosis良いですね！！カラムの自動追加も良いですが、何よりも上流モデルからカラムのドキュメントを受け渡せるのが素晴らしい！！