見出し画像

金融データのスタースキーマ実装方法

zono

はじめに

本記事では、金融データの管理・分析に有効なスタースキーマの概要と実装方法を紹介します。
データレイクに格納されたテーブルからスタースキーマを作成する具体的な方法について説明します。ただし、理解しやすさを優先したので非常にシンプルかつ簡易的な設計になっています。

スタースキーマ

スタースキーマは、データウェアハウスやデータマートでよく使用されるデータモデルの一種です。その特徴はシンプルで直感的な構造でありながら、高いパフォーマンスと柔軟性を提供します。

スタースキーマは、中心に「ファクトテーブル」と呼ばれるテーブルを配置し、その周囲に「ディメンションテーブル」と呼ばれる複数の関連テーブルを配置する形式を取ります。

分析前のテーブル

以下のような主要テーブルが存在すると仮定します。

  1. 顧客（Customer）

  2. 口座（Account）

  3. 支店（Branch）

  4. 口座取引（AccountTransaction）

（下記のSQLは、BigQueryを想定しています。）

顧客テーブル（customer）

CREATE TABLE `your_project.your_dataset.customers` (
    customer_id STRING NOT NULL,
    first_name STRING,
    last_name STRING,
    date_of_birth DATE,
    email STRING,
    phone_number STRING,
    address STRING,
    city STRING,
    state STRING,
    postal_code STRING,
    country STRING,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

口座テーブル（account）

CREATE TABLE `your_project.your_dataset.accounts` (
    account_id STRING NOT NULL,
    customer_id STRING NOT NULL,
    account_type STRING,
    balance NUMERIC,
    currency STRING,
    status STRING,
    opened_at TIMESTAMP,
    closed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

支店テーブル（branch）

CREATE TABLE `your_project.your_dataset.branchs` (
    branch_id STRING NOT NULL,
    branch_name STRING NOT NULL,
    address STRING,
    created_at TIMESTAMP,
    updated_at TIMESTAMP

口座取引テーブル（account_transaction）

CREATE TABLE `your_project.your_dataset.account_transactions` (
    transaction_id STRING NOT NULL,
    account_id STRING NOT NULL,
    transaction_type STRING,
    amount NUMERIC,
    currency STRING,
    transaction_date TIMESTAMP,
    description STRING,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

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

  1. ビジネスプロセスの理解：

    • 銀行の取引データを分析するために、どのような視点から分析したいのかを理解します。

  2. ビジネス側へのインタビュー：

    • アナリストやマーケターに対して、取引データをどのように分析したいかを尋ねます。

    • 例：「顧客セグメント別に取引金額を分析したい」、「支店別の取引件数を見たい」など。

  3. ファクトテーブルの定義：

    • 取引に関する主要なメトリクスを定義します。

    • ここでは、取引金額や取引件数といった取引データになります。

  4. ディメンションの特定：

    • ファクトテーブルに関連する属性を洗い出し、それらを整理してディメンションテーブルを特定します。

  5. 属性の整理：

    • 各ディメンションテーブルに属性を整理し、テーブルを定義します。

スタースキーマの作成

ディメンションテーブル

  • 顧客ディメンション (CustomerDim): このテーブルには、顧客の基本情報が格納されます。例えば、顧客ID、氏名、誕生日、連絡先情報、住所、登録日時、更新日時などが含まれます。顧客テーブルは顧客に関する詳細な情報を提供し、他のテーブルとの関連性を確立します。

CREATE TABLE `your_project.your_dataset.AccountDim` (
    customer_id STRING NOT NULL,
    first_name STRING,
    last_name STRING,
    date_of_birth DATE,
    email STRING,
    phone_number STRING,
    address STRING,
    city STRING,
    state STRING,
    postal_code STRING,
    country STRING,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (customer_id)
);

-- 既存のデータからディメンションを作成
INSERT INTO `your_project.your_dataset.CustomerDim` 
SELECT * FROM `your_project.your_dataset.customers`;

  • 口座ディメンション (AccountDim): 口座情報を管理するテーブルで、口座ID、顧客ID、口座タイプ、残高、通貨、口座ステータス、開設日時、閉鎖日時、作成日時、更新日時などの属性が含まれます。口座テーブルは、顧客が保持する各口座の詳細を記録し、口座取引などのイベントと結び付ける役割を果たします。

CREATE TABLE `your_project.your_dataset.AccountDim` (
    account_id STRING NOT NULL,
    customer_id STRING NOT NULL,
    account_type STRING,
    balance NUMERIC,
    currency STRING,
    status STRING,
    opened_at TIMESTAMP,
    closed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 既存のデータからディメンションを作成
INSERT INTO `your_project.your_dataset.AccountDim` 
SELECT * FROM `your_project.your_dataset.accouts`;

  • 支店ディメンション (BranchDim): 各支店の基本情報を管理するテーブルです。このテーブルには、支店ID、支店名、住所、作成日時、更新日時などの属性が含まれます。支店ディメンションは、各支店の識別情報や所在地情報を提供し、他のテーブル（例えば、取引事実テーブル）との関連性を確立します。

CREATE TABLE `your_project.your_dataset.BranchDim` (
    branch_id STRING NOT NULL,
    branch_name STRING NOT NULL,
    address STRING,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (branch_id)
);

-- 既存のデータからディメンションを作成
INSERT INTO `your_project.your_dataset.BranchDim` 
SELECT * FROM `your_project.your_dataset.branches`;

  • 日付ディメンション（DateDim）: 分析に必要な日付に関する詳細な情報を提供するテーブルです。このテーブルには、日付ID、日付、年、四半期、月、日、曜日、年の週番号、週末フラグなどの属性が含まれます。日付ディメンションは、時間に基づく分析や集計に使用され、取引事実テーブルなどの他のテーブルと結び付けられます。

CREATE TABLE `your_project.your_dataset.DateDim` (
    date_id INT NOT NULL,
    full_date DATE,
    year INT,
    quarter INT,
    month INT,
    day INT,
    day_of_week INT,
    week_of_year INT,
    is_weekend BOOLEAN,
    PRIMARY KEY (date_id)
);

-- 日付ディメンションを生成するためのデータを挿入するSQLスクリプト
WITH RECURSIVE DateRange AS (
    SELECT
        DATE '2000-01-01' AS full_date
    UNION ALL
    SELECT
        DATE_ADD(full_date, INTERVAL 1 DAY)
    FROM
        DateRange
    WHERE
        full_date < DATE '2050-12-31'
)
INSERT INTO `your_project.your_dataset.DateDim` (
    date_id,
    full_date,
    year,
    quarter,
    month,
    day,
    day_of_week,
    week_of_year,
    is_weekend
)
SELECT
    CAST(FORMAT_DATE('%Y%m%d', full_date) AS INT) AS date_id,
    full_date,
    EXTRACT(YEAR FROM full_date) AS year,
    EXTRACT(QUARTER FROM full_date) AS quarter,
    EXTRACT(MONTH FROM full_date) AS month,
    EXTRACT(DAY FROM full_date) AS day,
    EXTRACT(DAYOFWEEK FROM full_date) AS day_of_week,
    EXTRACT(WEEK FROM full_date) AS week_of_year,
    CASE
        WHEN EXTRACT(DAYOFWEEK FROM full_date) IN (1, 7) THEN TRUE
        ELSE FALSE
    END AS is_weekend
FROM
    DateRange
ORDER BY
    full_date;

ファクトテーブル

  • 口座取引ファクトテーブル (AccountTransactionFact): このファクトテーブルは、実際の取引データを保持します。口座取引ID、口座ID、顧客ID、取引タイプ、金額、通貨、取引日時、取引の説明、作成日時、更新日時などの属性が含まれます。口座取引ファクトテーブルは、顧客の口座やと関連づけられ、具体的な金融取引の詳細を提供します。

CREATE TABLE `your_project.your_dataset.AccountTransactionFact` (
    transaction_id STRING NOT NULL,
    date_id INT,
    account_id STRING,
    customer_id STRING,
    branch_id STRING,
    transaction_type STRING,
    amount NUMERIC,
    currency STRING,
    description STRING,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
);

-- 既存のデータからファクトテーブルを作成
INSERT INTO `your_project.your_dataset.AccountTransactionFact` (
    transaction_id, date_id, account_id, customer_id, branch_id, transaction_type, amount, currency, description, created_at, updated_at)
SELECT 
    at.transaction_id,
    DATE_FORMAT(at.transaction_date, '%Y%m%d') AS date_id,
    at.account_id,
    a.customer_id,
    NULL AS branch_id, 
    at.transaction_type,
    at.amount,
    at.currency,
    at.description,
    at.created_at,
    at.updated_at
FROM 
    `your_project.your_dataset.account_transaction` at
JOIN
    `your_project.your_dataset.account` a ON at.account_id = a.account_id;

ディメンションテーブルは、それぞれ顧客、口座、支店の情報を保持し、ファクトテーブルは取引情報を保持します。これにより、スタースキーマの構造が完成します。

以下の記事はスタースキーマの設計において非常に参考になる記事かと思います。今回、私はサロゲートキーを使用しておりませんが、使用するとクエリが簡素化します。

まとめ

スタースキーマの概要と利点を紹介し、ディメンションテーブルとファクトテーブルの構造や作成方法を説明しました。

ただし、業務で登場するテーブルはもっと膨大な数であり、それぞれのリレーションも複雑になります。更には、月日が経つにつれて、カラム数もテーブル数も常に増加・変更が入ります。
上記のような変更を分析基盤に取り入れるとなると、その度に設計の変更をする必要が出てきます。

いつの日か、変更の難しさについても書きたいと思います。

関連書籍

データアーキテクチャの設計やETLパイプライン、データモデリングについての記載があります。この本の内容を理解できれば、データエンジニアリング業務で必要な単語や概念は一通り浚うことができるはず。

データモデリングを学ぶ1冊目には丁度良い本です。分析基盤だけでなく、基幹系システムで数十年間、不変な技術として存在し続けています。


いいなと思ったら応援しよう！

zono
よろしければ応援お願いします！ いただいたチップはデータエンジニアとしての活動費や勉強代、教育に使わせていただきます！