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

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）

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

まとめ

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

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

参考

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

Clarifying Data Warehouse Design with Historical Dimensions

この記事が気に入ったらサポートをしてみませんか？

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

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

マスタテーブル

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

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

最新テーブル

履歴テーブル

カラム作成方法

前処理段階でカラム追加

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

まとめ

参考