🙆‍♀️

【dbt Docs】Building a dbt Project - Models - dbt Models

2022/03/10に公開

dbt

tech

対象ページ

dbt Models

関連するリファレンス

Model Configurations

Available configrations
- Model-specific configurations
  dbt_project.yml
```
models:
<resource-path>:
  +materialized: <materialization_name>
  +sql_header: <string>
```
  models/properties.yml
```
version: 2

models:
  - name: [<model-name>]
    config:
      materialized: <materialization_name>
      sql_header: <string>
```
  models/<model_name>.sql Code Block
```
{{ config(
    materialized="<materialization_name>",
    sql_header="<string>"
) }}
```
  !
  
  適用の順番は、CodeBlock > Property file > Project file
  meterization_name : view, table, ephemeral, increamental
- General configurations
- Warehouse-spesific configrations
  - BigQuery configurations
  - Redshift configurations
  - Snowflake configurations
  - Spark configurations
Configuring models
- modelファイル(.sql）の中にJinja macroで、config() を用いる方法
- resouce propertyの .ymlファイルの中で config を用いる方法
- dbt_project.ymlに記載する方法　※全体への適用
Example

   name: dbt_labs
   models:
     dbt_labs:
       events:
         +enabled: true
         +materialized: view

         base:
           +materialized: ephemeral
       ...

もし１つのモデルだけに適用したい場合の記載方法はこちら

models/events/base/base_events.sql

 {{
   config(
       materialized = "table",
       sort = 'event_time',
       dist = 'event_id'
     )
   }}
   select * from ...

models/events/base/properties.yml

version: 2

models:
 - name: base_events
   config:
     materialized: table
     sort: event_time
     dist: event_id

Model properties

models/<filename>.yml

version: 2

  models:
    - name: <model name>
      description: <markdown_string>
      docs:
        show: true | false
      config:
        <model_config>: <config_value>
      tests:
        - <test>
        - ... # declare additional tests
      columns:
        - name: <column_name> # required
  	description: <markdown_string>
  	meta: {<dictionary>}
  	quote: true | false
  	tests:
  	  - <test>
  	  - ... # declare additional tests
  	tags: [<string>]
  
        - name: ... # declare properties of additional columns

run command

--full-refresh : incremental modelをtable modelで作り直す

$ dbt run --full-refresh

フラグがしている際に、FULL_REFRESH　を行う・・・といったことも可能

models/example.sql

select * from all_events

-- if the table already exists and `--full-refresh` is
-- not set, then only add new records. otherwise, select
-- all records.
{% if is_incremental() %}
   where collector_tstamp > (
     select coalesce(max(max_tstamp), '0001-01-01') from {{ this }}
   )
{% endif %}

global configs
argument(s)
- --select
- --exclude
- --selector
- --defer

ref functon
- dbtで最も重要な関数
- これなしでは複雑なモデルの構築ができない
- DAG（モデルの依存関係や構築順を決定する）を構築するのにとても重要
　　 ```sql:model_a.sql
select *
from public.raw_data
```
  
```sql:model_b.sql
select *
from {{ref('model_a')}}
```
!

refで使われるモデル名は、プロジェクトで一意である必要があるかも
two_argment.sql
```
select * from {{ ref('package_name', 'model_name') }}
```

Getting started

モデルは select ステートメントで。ファイルは、.sqlファイル（通常は、modelsディレクトリ内）で定義される。

.sqlファイルには、1モデル＝selectステートメント
ファイル名はモデル名として使用
モデルは、ディレクトリ内のサブディレクトリにネストできる。（階層構造にできる）

dbt runコマンドを実行すると、create veiw as や create table as が実行され、データウエアハウスにモデルが構築される。

models/customer.sql

with 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 jaffle_shop.orders
    group by 1
)

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 jaffle_shop.customers

left join customer_orders using (customer_id)

これを dbt runで実行すると、create view dbt_alice.customers as とラップされて実行される。

create view dbt_alice.customers as (
    with 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 jaffle_shop.orders

        group by 1
    )

    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 jaffle_shop.customers

    left join customer_orders using (customer_id)
)

dbtでは

view としてモデルを構築する（デフォルト、変更可能：table, ephemeral, increamental)
定義したターゲットスキーマでモデルを構築する
ファイル名をデータベース内のビューまたはテーブル名として使う

FAQs

dbtが実行されているSQLを確認するにはどうすればよいですか？
- dbt Cloud:
  - 実行出力内で、モデル名をクリックし、[詳細]を選択します
- dbt CLI:
  - target/compiledディレクトリにSelect句のSQL
  - target/run/ ディレクトリに Create区のSQL
  - logs/dbt.logに詳細ログがある
dbtを実行する前にターゲットスキーマを作成する必要がありますか？

必要なし。もし存在しない場合は新規に作成されます。
dbtを再実行した場合、モデルが再構築されるときにダウンタイムは発生しますか？

ダウンタイムは発生しない。dbtがバックグラウンドで生成するSQLにより
クエリのSQLが悪い場合はどうなりますか？

Databaseから返すエラーを返し、モデルの下流にあるモデルもスキップされる

SQLの方言（？　DWHそれぞれに特有の書き方）によった記述をしないといけない？

対応するDWHごとにSQLを生成しているので気にしなくて良い

models/test_model.sql

select 1 as my_column

は、Redshift、BigQuery、Snowflakeでは下記のように

redshift

-- you can't create or replace on redshift, so use a transaction to do this in an atomic way
begin;
create table "dbt_alice"."test_model__dbt_tmp" as (
    select 1 as my_column
);
alter table "dbt_alice"."test_model" rename to "test_model__dbt_backup";
alter table "dbt_alice"."test_model__dbt_tmp" rename to "test_model"
commit;
begin;
drop table if exists "dbt_alice"."test_model__dbt_backup" cascade;
commit;

bigquery

create or replace table `dbt-dev-87681`.`dbt_alice`.`test_model` as (
  select 1 as my_column
);

snowflake

create schema if not exists analytics.dbt_alice;

create or replace table analytics.dbt_alice.test_model as (
    select 1 as my_column
);

Configuring models

モデルの設定は、

dbt_project.yml
モデルのファイル（.sql）に　　configブロックで記載する

設定例としては

モデルのマテリアライゼーションを変更する（　view, table, ephemeral, increamental )
モデルを個別のスキーマに構築する（＝生成先のスキーマを指定）
モデルに tag　を適用する
- tag : https://docs.getdbt.com/reference/resource-configs/tags
  - dbt run --select tag:my_tag
  - dbt seed --select tag:my_tag
  - dbt snapshot --select tag:my_tag
  dbt_project.yml
```
models:
  jaffle_shop:
   +tags: "contains_pii"
   staging:
     +tags:
       - "hourly"
   marts:
     +tags:
       - "hourly"
       - "published"
   metrics:
     +tags:
       - "daily"
       - "published"
```
!

上記だと、dbt run --select tag:hourlyで、タグの付いているもの「だけ」を実行するという使い方・・・かな？

モデル構成の例は

dbt_project.yml

name: jaffle_shop
config-version: 2
...

models:
  jaffle_shop: # this matches the `name:`` config
    +materialized: view # this applies to all models in the current project
    marts:
      +materialized: table # this applies to all models in the `marts/` directory
      marketing:
        +schema: marketing # this applies to all models in the `marts/marketing/`` directory

models/customer.sql

{{ config(
    materialized="view",
    schema="marketing"
) }}

with customer_orders as ...

FAQs

dbtで利用できるマテリアライゼーションは何ですか？

view, table, incremental, ephemeral
必要に応じて独自のカスタムマテリアライゼーションを作ることもできるが、高度機能
他にどのようなモデル構成がありますか？
- 簡単な分類とグラフ選択をサポートするタグ
- モデルを複数のスキーマに分割するためのカスタムスキーマ
- ビュー/テーブル名がファイル名と異なる必要がある場合のエイリアス
- モデルの開始時または終了時に実行されるSQLのスニペット（フックと呼ばれる）
- パフォーマンスのためのウェアハウス固有の構成（例：Redshift、BigQuerysortのキー

Building dependencies between models

モデル間での依存関係

ref関数を使うことで、モデル間の依存関係を構築することができる。
また、ref関数の置き換えは、環境によってかえることもできる（ devとproductionで違うDB、スキーマとか）

models/customer.sql

with customers as (

    select * from {{ ref('stg_customers') }}

),

orders as (

    select * from {{ ref('stg_orders') }}

),

...
```sql:開発環境でのSQL
create view dbt_alice.customers as (
  with customers as (

      select * from dbt_alice.stg_customers

  ),

  orders as (

      select * from dbt_alice.stg_orders

  ),

  ...
)

...

本番でのSQL

create view analytics.customers as (
  with customers as (

      select * from analytics.stg_customers

  ),

  orders as (

      select * from analytics.stg_orders

  ),

  ...
)

...

Testing and documenting models

を参照すること

Additional FAQs

dbtモデルの例はありますか？
- Getting Started Tutorial https://docs.getdbt.com/tutorial/setting-up
- Jaffle Shop https://github.com/dbt-labs/jaffle_shop
- MRR Playbook
- GitLab
プロジェクトの `models` ディレクトリ以外のディレクトリにモデルを保存できますか？

model-paths: ["transformations"] で変更可能
ターゲットスキーマ以外のスキーマでモデルを構築できますか？
2つの方法がある。
- dbt_project.yｍｌで指定する方法
  dbt_project.yml
  
  name: jaffle_shop ... models: jaffle_shop: marketing: schema: marketing # seeds in the `models/mapping/ subdirectory will use the marketing schema
- .sqlのconfigで記載する方法
  models/customers.sql
  
  {{ config( schema='core' ) }}
モデル名は一意である必要がありますか？

一意である必要がある。（なので、Prefixをつけたりするのが推奨されているのだろう）
モデル間の依存関係を構築するには、関数を使用する必要がありrefます。このref関数は、モデル名（つまりファイル名）という1つの引数のみを取ります。その結果、これらのモデル名は、別個のフォルダーにある場合でも、一意である必要があります。
ユーザーがウェアハウス内で2つのモデルに同じ名前を付けて、別々のスキーマ（たとえばstripe.users、app.users）に分割したいために発生します。これを実現するためには、カスタムエイリアス、カスタムスキーマを使う必要がある。
データウェアハウスから削除されたモデルを削除するにはどうすればよいですか？

dbtプロジェクトからモデルを削除しても、dbtはスキーマからリレーションを自動的に削除しません。これは、 dbtが作成するスキーマに余分なオブジェクトが含まれる可能性があり、他のユーザーを混乱させる可能性があることを意味します。

（これは、モデルをビューまたはテーブルからエフェメラルに切り替えるときにも発生する可能性があります）

dbtプロジェクトからモデルを削除するときは、関連するリレーションをスキーマから手動で削除する必要があります。
より多くのモデルを作成するとき、プロジェクトをどのように整理しておく必要がありますか？モデルには何という名前を付ける必要がありますか？

プロジェクトを構築するための最良の方法はありません！すべての組織はユニークです。
モデルが `select`ステートメントのみである場合、レコードを挿入するにはどうすればよいですか？
ETL（Extract Transform Load）パラダイムから来ている人にとっては、変換insertをupdateステートメントとして記述したいという要望がよくあります。それに比べて、dbtはselectクエリをcreate table asステートメントでラップするため、逆効果に感じる可能性があります。
- パフォーマンス上の理由で処理量を減らす場合には、マテリアライザーション＝incremental を使う
- ソースデータが絶えず変化するためにステートメントを使用する場合insert（たとえば、「タイプ2の緩やかに変化するディメンション」を作成する場合）、ソースデータのスナップショットを作成し、スナップショットの上にモデルを構築することを検討してください。
トランスフォーメーションでDMLを記述できないのはなぜですか？
selectだけのほうが色んな人が触ることができる。良いDML（CreateやDrop）を書くのは難しい
- テーブルがすでに存在する場合はどうなりますか？または、このテーブルはすでにビューとして存在しますが、今はテーブルにしたいですか？
- スキーマがすでに存在する場合はどうなりますか？または、スキーマがすでに存在するかどうかを確認する必要がありますか？
- モデルをアトミックに置き換えるにはどうすればよいですか（誰かがテーブルをクエリするためのダウンタイムがないように）
- 開発環境でこれらの変換を実行できるようにスキーマをパラメーター化する場合はどうなりますか？
- これらのステートメントを実行するには、どのような順序が必要ですか？私が実行した場合、cascadeそれは他のものを壊しますか？
あとは、ベンダーロックイン＝SQLの方言　もあるのて、dbtでこの違いを吸収するのが良き
列タイプを指定するにはどうすればよいですか？
モデル内の正しいタイプに列をキャストするだけです。
```
select
    id,
    created::timestamp as created
from some_other_table
```
追加の列オプションを定義するためには
- 列に一意性とnull以外の制約を適用するのではなく、dbtのテスト機能を使用して、モデルに関するアサーションが真であることを確認します。
- 列のデフォルト値を作成するのではなく、SQLを使用してデフォルトを表現します（例coalesce(updated_at, current_timestamp()) as updated_at）
- 列を変更する必要があるエッジケース（Redshiftでの列レベルのエンコーディングなど）では、ポストフックを介してこれを実装することを検討してください。

対象ページ

dbt Models

Related reference docs

Getting started

FAQs

Configuring models

FAQs

Building dependencies between models

Testing and documenting models

Additional FAQs

Discussion