Detailed Granularity Fact Tables (DWD): Types, Design Principles, and Comparison

Detailed-granularity fact tables (DWD) are categorized into three types: transaction fact tables, periodic snapshot fact tables, and cumulative snapshot fact tables.

Transaction Fact Table describes business processes, tracking metric events at a specific point in space or time, storing the most atomic data, also known as an atomic fact table.

Periodic Snapshot Fact Table records facts at regular, predictable time intervals.

Cumulative Snapshot Fact Table captures key step events between the start and end of a process, covering the entire lifecycle with multiple date fields to record critical timestamps.

Fact Table Design Principles

Transaction Fact Table : Record data related to a single transaction or event, such as product types, amount, purchase time, requiring clear event type, occurrence time, and related keys.

Periodic Fact Table : Record recurring events over a time span (daily, weekly, monthly), requiring definition of the periodic type, start/end times, and associated information.

Cumulative Snapshot Fact Table : Record accumulated metrics over a long period (e.g., monthly sales, quarterly customer count), needing clear start and end times and accumulation rules.

Comparison of the Three Fact Tables

Simple Understanding

Transaction Fact Table (generally not used): analogous to Binlog logs, each change creates a new row.

Periodic Snapshot Fact Table (commonly used): records facts at regular intervals such as daily, weekly, monthly, often based on business processes like order receipt.

Cumulative Snapshot Fact Table (essential): describes key steps throughout a process lifecycle (order creation, payment, shipment, receipt) with separate fields for each timestamp to calculate intervals.