Zenn
🕌

Snowflake x dbtやってみた〜マテリアライゼーション編〜

2022/11/28に公開
Snowflake
dbt
tech

前回記事ではモデルからViewを作成しました。
今回はモデルからViewを作成したマテリアライゼーション機能を深掘りしていきます。

マテリアライゼーションについて

4つのマテリアライゼーションがあります。

マテリアライゼーション ユースケース バットユースケース
View（デフォルト） ・軽量な表現が必要な場合
・データをあまり再利用しない		 ・同じモデルから何度も読み込む
Table ・このモデルから何度も読み込む ・単一使用モデルの構築
・モデルへのデータ投入はインクリメンタルに行う
Incremental ・ファクトテーブル
・テーブルに追加される		 ・過去のレコードを更新したい
Ephemeral ・単に日付の別名が欲しいだけ ・同じモデルから何度も読み込んでいる

View

Viewのマテリアライゼーションは前回記事でご紹介しましたので割愛します。

Table

dbt_project.ymlでTable用設定追加

dbt_project.ymlファイルに最終行の2行を追記します。
これはdimディレクトリ配下のSQLはTableとして作成する設定になります。

models:
  dbtlearn:
    +materialized: view
  dim:
    +materialized: table

作成用ディレクトリ作成とSQLの設置

modelsディレクトリ配下にdimというTable作成用ディレクトリを作成し、そこに下記クエリを設置します。

dim_listings_cleansed.sql
WITH src_listings AS (
  SELECT
    *
  FROM
    {{ ref('src_listings') }}
)
SELECT
  listing_id,
  listing_name,
  room_type,
  CASE
    WHEN minimum_nights = 0 THEN 1
    ELSE minimum_nights
  END AS minimum_nights,
  host_id,
  REPLACE(
    price_str,
    '$'
  ) :: NUMBER(
    10,
    2
  ) AS price,
  created_at,
  updated_at
FROM
  src_listings

dbt runの実行

dbt run

テーブルにDIM_LISTINGS_CLEANSEDが作られたことがわかります。

Incremental

作成用ディレクトリ作成とSQLの設置

modelsディレクトリ配下にfctというディレクトリを作成し、下記SQLを設置します。

fct_reviews.sql
{{
  config(
    materialized = 'incremental',
    on_schema_change='fail'
    )
}}
WITH src_reviews AS (
  SELECT * FROM {{ ref('src_reviews') }}
)
SELECT * FROM src_reviews
WHERE review_text is not null

{% if is_incremental() %}
  AND review_date > (select max(review_date) from {{ this }})
{% endif %}

dbt runの実行

dbt run

Snowflake上でFCT_REVIEWSテーブルが作成されたので、SELECT句でID：3176で絞って参照してみます。

SELECT * FROM "AIRBNB"."DEV"."FCT_REVIEWS" WHERE listing_id=3176;

データの追加

さらに、このテーブルに値を追加したいとします。

INSERT INTO "AIRBNB"."RAW"."RAW_REVIEWS"　VALUES (3176, CURRENT_TIMESTAMP(), 'Zoltan', 'excellent stay!', 'positive');

下図の結果の通り、1つのデータが追加されたことが分かります。

では、その追加されたデータを参照してみます。

データを追加したにも関わらず、SELECT句でヒットしません。
ここでもう一度dbt runを実行してみます。
すると、ヒットしました。

Ephemeral

作成用ディレクトリ作成とSQLの設置

dimディレクトリ配下に下記SQLを設置します。

dim_listings_w_hosts.sql 
WITH
l AS (
    SELECT
        *
    FROM
        {{ ref('dim_listings_cleansed') }}
),
h AS (
    SELECT * 
    FROM {{ ref('dim_hosts_cleansed') }}
)

SELECT 
    l.listing_id,
    l.listing_name,
    l.room_type,
    l.minimum_nights,
    l.price,
    l.host_id,
    h.host_name,
    h.is_superhost as host_is_superhost,
    l.created_at,
    GREATEST(l.updated_at, h.updated_at) as updated_at
FROM l
LEFT JOIN h ON (h.host_id = l.host_id)

dbt runの実行

dbt run

先ほど作成したテーブルを確認できます。

dbt_project.ymlでEphemeral用設定追加

dbt_project.ymlファイルに最終行の2行を追記します。
これはsrcディレクトリ配下のSQLはEphemeralとして作成する設定になります。

models:
  dbtlearn:
    +materialized: view
    dim:
      +materialized: table
    src:
      +materialized: ephemeral

再度dbt runの実行

dbt run

srcで作成したViewを削除します

DROP VIEW AIRBNB.DEV.SRC_HOSTS;
DROP VIEW AIRBNB.DEV.SRC_LISTINGS;
DROP VIEW AIRBNB.DEV.SRC_REVIEWS;

Snowflake上から削除されました。

もう一度dbt runを実行します

dbt run

dbt runを実行しても削除したViewは再作成されません。
これがEphemeralマテリアライゼーションです。

Discussion