ディメンショナルモデリングに入門しよう！Snowflakeとdbt Cloudで「Building a Kimball dimensional model with dbt」をやってみた

特集

さがら

2024.01.23

さがらです。

ここ２年ほどの間にdbtが日本でも急速に拡大し、様々な情報が日本語の記事でも見かけられるようになってきました。

dbtを採用してある程度活用を進めていくと、「より効率よくガバナンスを持ってデータを管理するにはどうすればいいんだろうか」といったデータの管理方法に悩む場面が出てくると思います。

そんなときに色々調べていくと、データを効率よく管理する手法として「データモデリング」が必要だとわかり、ディメンショナルモデリングやData Vaultなどの手法に行き着くのではないでしょうか。

そしてこれらのデータモデリングの手法の内、ディメンショナルモデリングについてdbtを用いて実践された記事がありまして、それが「Building a Kimball dimensional model with dbt」です！

この「Building a Kimball dimensional model with dbt」ですが、dbt CoreとDuckDB/PostgreSQLという組み合わせで内容が書かれています。

しかし、実際に使用するデータはdbt seedコマンドでリポジトリ上のCSVから生成しているだけですし、dbt Coreでのみ使えるPythonライブラリを使っているわけでもないので、dbt Cloudと他のDWHにも展開可能な内容です。

そこで、Snowflakeとdbt Cloudを使って「Building a Kimball dimensional model with dbt」をやってみたので、本記事でその内容をまとめてみます。

前置き：ディメンショナルモデリングの知識について

「Building a Kimball dimensional model with dbt」はディメンショナルモデリングを実践する際のプロセスと、dbtでどのように実装するかに重きを置いており、ディメンショナルモデリング自体の知識については深く書かれておりません。

そのため、ディメンショナルモデリングの知識をどのように習得すればよいか、参考になる記事や本について一例を記載しておきます。

ぺいさんの各種スライドやZennの記事

私も色んな情報を見ましたが、日本語で一番わかりやすくまとまっていると感じたのはぺいさん(@pei0804)のスライドやZennの記事です。

まずはこれらのスライドや記事を見ることで、ディメンショナルモデリングはどういったものか、どのように役立つのか、どのようにモデリングを進めていけばよいか、がわかると思います。

Star Schema: The Complete Reference

こちらは書籍となるのですが、「Star Schema: The Complete Reference」が私がXでフォローしている方々から良い評判を聞きます。（実は私も読めていないので、今後読んでいかなければと思っています…）

※ディメンショナルモデリングについては他にも色々な本があるので、あくまで参考までに。

検証環境

  • Snowflake：Ver8.2.0
  • dbtの開発・実行環境：dbt Cloud
  • 使用するdbtのバージョン：1.7 ※Environmentsで指定

１．リポジトリ・データベース・dbt projectのセットアップ

実際の内容に入る前に、リポジトリ・Snowflake・dbt Cloud、それぞれ準備をする必要がありますのでその内容について記載します。

※今回はあくまで検証のため、簡単にセットアップを行います。実際に業務で用いる環境としては、設計したレイヤー分けに応じたデータベースやスキーマの準備、対象のデータベース・スキーマにのみ権限を持つSnowflakeのロールの準備、などが必要となりますのでご注意ください。

リポジトリのfork

「Building a Kimball dimensional model with dbt」の内容はGitHub上にPublicのリポジトリとして公開されているため、この内容をforkして使えるようにしておきます。

Snowflakeでの検証用データベース・ウェアハウスの準備

dbtで処理を行う際に必要となるデータベースとウェアハウスをSnowflakeで作成します。以下のようなクエリを実行すればOKです。

use role sysadmin;

create database <任意のデータベース名>;

create or replace warehouse <任意のウェアハウス名> with
    warehouse_size = 'xsmall'
    warehouse_type = 'standard'
    auto_suspend = 60
    auto_resume = true
    min_cluster_count = 1
    max_cluster_count = 1
    scaling_policy = 'standard'
    initially_suspended = true;

-- 任意：dbtをsysadmin以外のロールで実行する場合には、そのロールに対して必要な権限をgrantしてください。以下ではわかりやすくownershipをgrantしています。
grant ownership on database <任意のデータベース名> to role <任意のロール名>;
grant ownership on warehouse <任意のウェアハウス名> to role <任意のロール名>;

dbt Cloudでのdbt projectのセットアップ

前述の工程で準備したリポジトリとSnowflakeのデータベースを用いて、dbt Cloudでdbt projectのセットアップを行います。

こちらの手順については、下記のブログも参考になると思います。

１つ注意点としては、対象のリポジトリのサブディレクトリadventureworksにdbtの各種ファイルが保存されているため、以下のセットアップ画面ではProject subdirectoryにadventureworks/と入力してください。

dbt seedを実行しデータを生成

これまでの工程を終えたら、あとはdbt Cloudで作成したdbt projectのIDEにおいてdbt seedコマンドを実行すれば、モデリング前のテーブルを生成してくれます！

※各seedのyamlファイルでschema: personのようなconfigが書かれており、generate_schema_nameマクロもoverride_default_schema_name.sqlで書き換えているため、configの値に応じたスキーマが自動で作られ、そのスキーマにテーブルが作られます。ただし、このリポジトリのoverride_default_schema_name.sqlは実際の本番運用には適さないマクロとなっているためご注意ください。本番運用に適したgenerate_schema_nameマクロの書き換えについては、こちらのブログも参考にしてください。

モデリング前のデータの内容を確認する

先程dbt seedで作られたテーブルですが、以下のようなリレーションとなっています。（ブログより引用）

実際のモデリング前には、「どんなデータを持つテーブルがあるか」「各テーブルの主キー・外部キーはなにか」「リレーションはどうなっているか」などを確認すると良いでしょう。

また、実際にクエリを発行することでどんなデータがあるか見ることも出来ます。

２．ビジネスプロセスの選択

ということで元データが揃ったので「モデリングの設計と実装するぞー！」となりがちですが、これは間違いです。

まず、今回モデリング対象のビジネスについての理解を深める必要があります。基本的には、対象のデータを実際に業務に活用しているビジネスユーザーにヒアリングをすることになります。

以下は一例ですが、このようなことをビジネスユーザーにヒアリングしておくと良いでしょう。

  • どういったことを実現したいのか
  • なぜそのデータが必要なのか
  • 現在はどのように対応しているのか（データの加工をExcelなどで頑張っているケースも多いので、そのExcelについて詳細にヒアリングしても良い）
  • etc

今回はブログに沿って進めるため、AdventureWorks社のCEOから以下の情報を得たとします。

  • AdventureWorks社は自転車を製造し、消費者 (B2C) および企業 (B2B) に販売しています。自転車は世界中の顧客に出荷されます。CEOとして、2011年までに当社が生み出した収益の内訳を知りたいと思っています。
  • 収益の内訳については、以下の分類で知りたいです。
    • Product category and subcategory
    • Customer
    • Order status
    • Shipping country, state, and city

この情報から、収益とその内訳を知るための、Salesプロセスに関するモデリングを行えば良いということがわかりました。

３．ファクトテーブルとディメンションテーブルの特定

前述のヒアリングした内容に基づいて、ファクトテーブルとディメンションテーブルを特定していきます。

ファクトテーブルの特定

まずファクトテーブルですが、ビジネスプロセスにおける数値や指標を表すテーブルです。具体的には、「アイテムの販売数」「ウェブサイトのクリック数」などの具体的な値を持つテーブルとなります。

今回は「収益とその内訳を知るための、Salesプロセスに関するモデリング」ですので、ファクトは「収益」となります。

その収益に関連するテーブルはどのようなものがあるか調査すると、sales.salesorderheaderテーブルとsales.salesorderdetailテーブルがあることがわかりました。

  • sales.salesorderheaderテーブル：
    • 注文に使用されたクレジット カード、配送先住所、および顧客に関する情報が含まれています。このテーブルの各レコードは、1 つ以上の注文の詳細を含む注文ヘッダーを表します。
  • sales.salesorderdetailテーブル：
    • 注文された製品、注文数量、単価に関する情報が含まれており、収益の計算に使用できます。このテーブルの各レコードは、単一の注文の詳細を表します。

そして、これらのテーブルはリレーションを見ると下図のような関係となっています。

これらのテーブルをJOINすることで、各レコードが注文の詳細を表し、集計することで収益を知ることができるファクトテーブルとすることができます。

ここでは、テーブル名はfct_salesと定義します。

ディメンションテーブルの特定

続いてディメンションテーブルですが、ビジネスプロセスにおけるコンテキスト情報や説明情報を表すテーブルです。具体的には、「顧客の情報」「Webサイトでクリックされたときのクリック位置の情報」「注文された商品の分類や商品名などの詳細情報」などのデータを持つテーブルとなります。

改めて振り返ると、事前にヒアリングした情報では「以下の情報で収益の内訳を知りたい」とのことでした。

  • Product category and subcategory
  • Customer
  • Order status
  • Shipping country, state, and city

このヒアリングした情報を元に、関連するテーブルを洗い出すと以下のようなテーブルが出てきます。

  • person.address
  • person.countryregion
  • production.product
  • production.productcategory
  • sales.customer
  • sales.store
  • etc

上述のテーブルがディメンションテーブルとなるため、下図のようにこれらのテーブルをそのままディメンションテーブルとすることもできますが、正規化されているためクエリ実行の際に都度複数のJOINが発生することになってしまいます。

※余談ですが、このようなディメンションテーブルからディメンションテーブルが連なる構造をスノーフレークスキーマと呼びます。

そこで今回は、このJOINを減らすために、下図のようにディメンションテーブル同士の結合を事前に行い非正規化をして、各ディメンションテーブルから直接ファクトテーブルと結合するようにモデリングをします。

※余談ですが、このようなファクトテーブルを中心に非正規化したディメンションテーブルを１つずつ構成していく構造をスタースキーマと呼びます。

ここでは、他のテーブルについても同様の非正規化を行い、以下のディメンションテーブルを定義します。

  • dim_product : product、productsubcategory、productcategoryの３つのテーブルをJOINしたディメンションテーブル
  • dim_address : address、stateprovince、countryregionの３つのテーブルをJOINしたディメンションテーブル
  • dim_customer : customer、person、storeの３つのテーブルをJOINしたディメンションテーブル
  • dim_credit_card : creditcardテーブルを元にしたディメンションテーブル
  • dim_order_status : salesorderheaderテーブルからorder_status列だけを抽出して、重複レコードの削除を行ったディメンションテーブル
  • dim_date : dbt_datepackageを用いて、生成された日付属性を含むディメンションテーブル

４．ディメンションテーブルの実装

前述の内容で作成すべきファクトテーブルとディメンションテーブルの定義を終えたので、実際にそれぞれのテーブルを実装していきます。

まずはディメンションテーブルについてです。リポジトリ上にすでに各ファイルは出来上がっていますが、ブログに沿って、dbtでどのような記述を行いディメンショナルテーブルを実装していくのか、dim_productテーブルの実装手順を例に、書いていきます。

ステップ１．Modelファイルの作成

まず、Modelファイルを作成します。このブログでは、「adventureworks/models/marts/フォルダの中で、１つのModelに対して１つのyamlファイル」を作成する方法を取っています。

具体的に、dim_productというディメンションテーブルを作成する場合、以下の２つのファイルを作成します。

  • dim_product.sql：CTASとして実行される、SELECT文のSQLを記載する
  • dim_product.yml：dim_productテーブルに対するテストやDescriptionの内容を記載する

ステップ２．SQLの記述

ステップ２では、実際にdim_product.sqlにおいてどのようにコードを書いていくか説明していきます。

実際のdim_product.sqlのコードが下記になります。

with stg_product as (
    select *
    from {{ ref('product') }}
),

stg_product_subcategory as (
    select *
    from {{ ref('productsubcategory') }}
),

stg_product_category as (
    select *
    from {{ ref('productcategory') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['stg_product.productid']) }} as product_key,
    stg_product.productid,
    stg_product.name as product_name,
    stg_product.productnumber,
    stg_product.color,
    stg_product.class,
    stg_product_subcategory.name as product_subcategory_name,
    stg_product_category.name as product_category_name
from stg_product
left join stg_product_subcategory on stg_product.productsubcategoryid = stg_product_subcategory.productsubcategoryid
left join stg_product_category on stg_product_subcategory.productcategoryid = stg_product_category.productcategoryid

このコードにおけるポイントは以下になります。

  • SQLの書き方として、Common Table Expressions (CTE)を採用している
  • 上流に位置するテーブルはref関数を使用して参照する
  • ファクトテーブルと結合するためのキーは、dbt_utilsパッケージのgenerate_surrogate_keyマクロを用いて代理キーを生成する
    • 代理キーを生成することで、ユーザーがカラム名を見て直感的にファクトテーブルとディメンションテーブルのJOINを行うことができる
    • dbt_utilsパッケージのgenerate_surrogate_keyマクロを使うことで、各テーブルで一意となるカラムの値をハッシュ化することができる
  • 最終的にselectするカラムは、後でファクトテーブルとJOINして収益の内訳を知るために役立つカラムのみにする

ステップ３．yamlの記述

続いてステップ３ですが、実際にdim_product.ymlでどのようにテストやDescriptionを記述しているか説明していきます。

実際のdim_product.ymlのコードが下記になります。product_keyとproductidという代理キー・ナチュラルキーのカラムについてはnot_nullとuniqueのテストを入れています。

version: 2

models:
  - name: dim_product
    columns:
      - name: product_key 
        description: The surrogate key of the product
        tests:
          - not_null
          - unique
      - name: productid 
        description: The natural key of the product
        tests:
          - not_null
          - unique
      - name: product_name 
        description: The product name
        tests:
          - not_null

５．ファクトテーブルの実装

次はファクトテーブルについてです。ブログに沿って、dbtでどのような記述を行いファクトテーブルを実装していくのか、fct_salesテーブルの実装手順を例に、書いていきます。

ステップ１．Modelファイルの作成

まず、Modelファイルを作成します。ディメンションテーブルと同様に「adventureworks/models/marts/フォルダの中で、１つのModelに対して１つのyamlファイル」を作成していきます。

具体的に、fct_salesというディメンションテーブルを作成する場合、以下の２つのファイルを作成します。

  • fct_sales.sql：CTASとして実行される、SELECT文のSQLを記載する
  • fct_sales.yml：fct_salesテーブルに対するテストやDescriptionの内容を記載する

ステップ２．SQLの記述

ステップ２では、実際にfct_sales.sqlにおいてどのようにコードを書いていくか説明していきます。

実際のfct_sales.sqlのコードが下記になります。

with stg_salesorderheader as (
    select
        salesorderid,
        customerid,
        creditcardid,
        shiptoaddressid,
        status as order_status,
        cast(orderdate as date) as orderdate
    from {{ ref('salesorderheader') }}
),

stg_salesorderdetail as (
    select
        salesorderid,
        salesorderdetailid,
        productid,
        orderqty,
        unitprice,
        unitprice * orderqty as revenue
    from {{ ref('salesorderdetail') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['stg_salesorderdetail.salesorderid', 'salesorderdetailid']) }} as sales_key,
    {{ dbt_utils.generate_surrogate_key(['productid']) }} as product_key,
    {{ dbt_utils.generate_surrogate_key(['customerid']) }} as customer_key,
    {{ dbt_utils.generate_surrogate_key(['creditcardid']) }} as creditcard_key,
    {{ dbt_utils.generate_surrogate_key(['shiptoaddressid']) }} as ship_address_key,
    {{ dbt_utils.generate_surrogate_key(['order_status']) }} as order_status_key,
    {{ dbt_utils.generate_surrogate_key(['orderdate']) }} as order_date_key,
    stg_salesorderdetail.salesorderid,
    stg_salesorderdetail.salesorderdetailid,
    stg_salesorderdetail.unitprice,
    stg_salesorderdetail.orderqty,
    stg_salesorderdetail.revenue
from stg_salesorderdetail
inner join stg_salesorderheader on stg_salesorderdetail.salesorderid = stg_salesorderheader.salesorderid

このコードにおけるポイントは以下になります。

  • SQLの書き方として、Common Table Expressions (CTE)を採用している
  • 上流に位置するテーブルはref関数を使用して参照する
  • ファクトテーブルの１行の粒度はSalesOrderDetailテーブルの１つのレコードと同じ粒度のため、salesorderheaderテーブルとsalesorderdetailテーブルは内部結合でJOINする
  • ファクトテーブルの各行を一意に識別するための代理キーは、dbt_utilsパッケージのgenerate_surrogate_keyマクロを用いて代理キーを生成する
    • generate_surrogate_key(['stg_salesorderdetail.salesorderid', 'salesorderdetailid'])とすることで、引数に２つのカラムを指定した上でハッシュ化できる
  • ディメンションテーブルと結合するための外部代理キーは、dbt_utilsパッケージのgenerate_surrogate_keyマクロを用いて代理キーを生成する
    • 前工程で定義したディメンションテーブルの代理キーと、カラム名を合わせるようにする
  • 今回のモデリングの目的でもある「収益」を計算するために、必要なorderqty列とunitprice列を選択した上で、収益額を表すrevenue列を追加

ステップ３．yamlの記述

続いてステップ３ですが、実際にfct_sales.ymlでどのようにテストやDescriptionを記述しているか説明していきます。

実際のfct_sales.ymlのコードが下記になります。代理キーであるsales_keyにはnot_nullとuniqueのテストを、外部代理キーの各カラムと実際の値を持つ各カラムについてはnot_nullのテストを入れています。

version: 2

models:
  - name: fct_sales
    columns:

      - name: sales_key
        description: The surrogate key of the fct sales
        tests:
          - not_null
          - unique

      - name: salesorderid
        description: The natural key of the saleorderheader
        tests:
          - not_null

      - name: salesorderdetailid
        description: The natural key of the salesorderdetail
        tests:
          - not_null
      
      - name: product_key
        description: The foreign key of the product
        tests:
          - not_null

      - name: customer_key
        description: The foreign key of the customer
        tests:
          - not_null 
      
      - name: ship_address_key
        description: The foreign key of the shipping address
        tests:
          - not_null
      
      - name: creditcard_key
        description: The foreign key of the creditcard. If no creditcard exists, it was assumed that purchase was made in cash.
        test:
          - not_null

      - name: order_date_key
        description: The foreign key of the order date
        tests:
          - not_null 
      
      - name: order_status_key
        description: The foreign key of the order status 
        tests:
          - not_null  

      - name: unitprice
        description: The unit price of the product 
        tests:
          - not_null

      - name: orderqty
        description: The quantity of the product 
        tests:
          - not_null

      - name: revenue
        description: The revenue obtained by multiplying unitprice and orderqty

６．開発したModelのビルド

これまでのプロセスでディメンションテーブルとファクトテーブルのModelの開発を終えたので、ビルドをして実際にSnowflakeに反映させてみます。

また、今回のリポジトリではdbt_project.ymlで下記のように記述しているため、全てのModelがテーブルとして生成されます。実際にはファクトテーブルは行数が多くなる傾向にあるため、必要に応じてincrementalのModelもご検討ください。

models:
  adventureworks:
    marts:
      +materialized: table
      +schema: marts

ということで、dbt buildコマンドを実行してみます！

下図のように、MARTSスキーマに各ディメンション・ファクトのテーブルが出来ればOKです！

７．実装後の参照方法

ディメンショナルモデルを行った後の参照方法ですが、ここではブログに沿って、２パターンに分けてご紹介します。

使用するツールが動的なJOINに対応している場合

例えば、LookerのExplore、dbt Semantic Layer、SnowflakeにRELY制約を設けたView、Tableauでのリレーションシップや結合などは、ファクトテーブルとすべてのディメンションテーブルのJOINの定義だけをしておけば、SELECTされたカラムに応じて動的に必要なJOINだけを行うことが可能です。

実際に参照するエンドユーザーがJOINに詳しくなくてもディメンショナルモデリングの効果を自然に得られるので、これらのBIツールやDWHを使っている方にはぜひ試していただきたいです！

使用するツールが動的なJOINに対応しておらず、ユーザーもJOINに詳しくない場合

ただ、エンドユーザーがJOINに詳しくなく、参照時のツールが動的なJOINに参照していない場合も多いと思います。

そんなときは、One Big Table(略してOBT。ワイドテーブル、大福帳とも呼ばれる）を１つdbtで実装してあげると良いと思います！

実際このブログでも１つOBTを実装しており、obt_sales.sqlで定義されています。クエリの内容としては、シンプルにすべてのファクトとディメンションをJOINしているだけです。

with f_sales as (
    select * from {{ ref('fct_sales') }}
),

d_customer as (
    select * from {{ ref('dim_customer') }}
),

d_credit_card as (
    select * from {{ ref('dim_credit_card') }}
),

d_address as (
    select * from {{ ref('dim_address') }}
),

d_order_status as (
    select * from {{ ref('dim_order_status') }}
),

d_product as (
    select * from {{ ref('dim_product') }}
),

d_date as (
    select * from {{ ref('dim_date') }}
)

select
    {{ dbt_utils.star(from=ref('fct_sales'), relation_alias='f_sales', except=[
        "product_key", "customer_key", "creditcard_key", "ship_address_key", "order_status_key", "order_date_key"
    ]) }},
    {{ dbt_utils.star(from=ref('dim_product'), relation_alias='d_product', except=["product_key"]) }},
    {{ dbt_utils.star(from=ref('dim_customer'), relation_alias='d_customer', except=["customer_key"]) }},
    {{ dbt_utils.star(from=ref('dim_credit_card'), relation_alias='d_credit_card', except=["creditcard_key"]) }},
    {{ dbt_utils.star(from=ref('dim_address'), relation_alias='d_address', except=["address_key"]) }},
    {{ dbt_utils.star(from=ref('dim_order_status'), relation_alias='d_order_status', except=["order_status_key"]) }},
    {{ dbt_utils.star(from=ref('dim_date'), relation_alias='d_date', except=["date_key"]) }}
from f_sales
left join d_product on f_sales.product_key = d_product.product_key
left join d_customer on f_sales.customer_key = d_customer.customer_key
left join d_credit_card on f_sales.creditcard_key = d_credit_card.creditcard_key
left join d_address on f_sales.ship_address_key = d_address.address_key
left join d_order_status on f_sales.order_status_key = d_order_status.order_status_key
left join d_date on f_sales.order_date_key = d_date.date_key

このようなOBTが１つあると、JOINに詳しくないユーザーも参照できるので良いと思います。ただ、OBTを作りすぎるとデータカオスとなるリスクもあるため、「１つのスタースキーマに対して１つのOBT」くらいから始めるのが良いと思います。

OBTも含めた、dbtで閲覧できるリネージは下図のようになりました。

最後に

Snowflakeとdbt Cloudで「Building a Kimball dimensional model with dbt」をやってみました。

リポジトリもあってforkすればすぐに動かせますし、ディメンショナルモデリングの入門としてとても良いチュートリアルだと感じました。ぜひお試しください！

*

関連記事

dbtで外部テーブルを参照するモデルを作成する

おざわ（じ）

2024.04.30

dbtの認定試験『dbt Analytics Engineering Certification Exam』が5月から新しくなるようです #dbt

しんや

2024.04.11

ストアドプロシージャからdbtへの移行手順 #dbt

しんや

2024.04.05

dbtでのSQLモデル記述時に利用推奨されている『共通テーブル式(CTE/Common Table Expression)』について

しんや

2024.03.31