分析基盤のためのテーブルとカラム

zono

2024年4月12日 07:55

データ分析基盤を構築する上で、テーブルとカラムの設計は非常に重要です。分析に必要なデータを効率的に取得・加工するために、適切なテーブル構成とカラム設計を行う必要があります。

マスタとトランザクション

マスタテーブルとトランザクションテーブルは、データベース設計で重要かつ基本的な構成です。
下記で紹介するテーブルは全て履歴テーブルです。

マスタテーブル

基本的に静的なマスターデータを格納するテーブルのことです。
様々なトランザクションシステムから参照されるため、履歴管理が不可欠で、データ品質と整合性が重視されます。

OLAPデータベースでは、特に以下のようなカラムを持つことがあります。

バージョン（履歴ID）
有効開始日
有効終了日
変更操作種別 (INSERT/UPDATE/DELETE)
変更者情報

トランザクションテーブル

業務アプリケーションにおける日々の取引や発生事象のデータを格納するテーブルのことです。
時系列データが蓄積されるため、データ量が肥大化しやすくなります。

OLAPデータベースでは、特に以下のようなカラムを持つことがあります。

連携元作成日時
連携元変更日時
変更操作種別 (INSERT/UPDATE/DELETE)
変更者情報
削除フラグ(論理削除)
パーティショニングカラム(年月日など)
（ソーストランザクションID）
（ソースシステムID）

SELECT
  id,
  created_at,
  CASE
    WHEN created_at IS NULL THEN NULL
    ELSE DATE_ADD(LAG(created_at) OVER (PARTITION BY id ORDER BY created_at), INTERVAL 1 DAY)
  END AS effective_start_date
FROM table;

また、パーティショニングカラム(年月日など)をSQLで追加する場合は以下のようなクエリを作成します。

SELECT
  id,
  updated_at,
  YEAR(updated_at) AS year,
  MONTH(updated_at) AS month,
  DAY(updated_at) AS day
FROM table;

上記のようにSQLで書くことができれば View(または materialized View )上でカラム追加を実現できます。

データ取得(連携)時に取得する

OLAPでは下記のように、どうしてもOLTP上から取得しないといけないカラムが存在します。

これらはCDC（変更データキャプチャ）やらツールやらでカラム追加を行う必要があります。

連携元作成日時
連携元変更日時
変更操作種別 (INSERT/UPDATE/DELETE)
変更者情報
（ソーストランザクションID）
（ソースシステムID）

この部分に関しては、別記事で紹介したいと思います。

スロー・チェンジ・ディメンション(Slowly Changing Dimensions)

スタースキーマを実装する場合、ソースデータの変更をどのように表現するかを決めることは重要で、ファクトに比べるとディメンションはゆっくりと変更されることから「スロー・チェンジ・ディメンション」と呼びます。

SCD(Slowly Changing Dimensions)は、全部でType 0 から Type 7 まであります。

he analysis of fact table performance metrics（Design Tip #152 Slowly Changing Dimension Types 0, 4, 5, 6 and 7）

上記で紹介したテーブルは下記のような対応になります。

Type1: 最新テーブル
Type2: 履歴テーブル

まとめ

本記事では、分析基盤における主要なテーブル構成と各テーブルに必要なカラムについて解説し、カラム作成方法やOLAP分析で必要となる追加カラムについても詳しく説明しました。

意外とこのあたりの話（どういうカラムを持たせるべきか等）は語られていないと思っており、この記事を書いてみました。どなたかの一助になれば幸いです。

参考

データエンジニアリングの基礎

Clarifying Data Warehouse Design with Historical Dimensions

Slowly Changing Dimensionの基礎と発展

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

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

分析基盤のためのテーブルとカラム

マスタとトランザクション

マスタテーブル

トランザクションテーブル

最新テーブルと履歴テーブル

最新テーブル

履歴テーブル

カラム作成方法

前処理段階でカラム追加

データ取得(連携)時に取得する

スロー・チェンジ・ディメンション(Slowly Changing Dimensions)

まとめ

参考

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