03-fact-table-patterns

Measure additivity classifies fact table measures as additive, semi-additive, or non-additive to prevent incorrect dashboard calculations. You distinguish between measures that sum across all dimensions, specific dimensions only, or require complex aggregation.

An accumulating snapshot fact table is needed when modeling processes with defined milestones, like order fulfillment. It updates a single row over time as events occur, contrasting with transactional patterns that add new rows for each event.

Handling late-arriving facts and dimensions requires specific Gold layer design strategies to incorporate data arriving after its intended reporting period. You design patterns that correctly update historical aggregates without corrupting existing reporting periods.

Factless fact tables record business events or coverage without numeric measures. You use them to track event occurrences, such as student attendance or product promotions, enabling analysis of activity frequency and relationships between dimensions.

Consolidated fact tables combine multiple business processes into a single table, limiting granular analysis per process. You should evaluate whether the query performance benefits outweigh the potential risks of mixing grain and complicating measure additivity.