03-fact-table-patterns

Measure additivity classification distinguishes between additive measures that sum across all dimensions, semi-additive measures that sum across some dimensions, and non-additive measures that cannot be summed. This guides correct aggregation logic in fact tables.

A factless fact table captures relationships or events without numeric measures. It is needed in dimensional modeling when tracking coverage, attendance, or eligibility events that have no associated quantitative values to aggregate.

Handling late-arriving facts and dimensions involves updating Gold layer fact tables when delayed source data arrives. This specialized pattern ensures historical accuracy by properly associating delayed fact records with their correct dimensional context.

Accumulating snapshot fact tables track a predictable workflow with multiple date stamps, while consolidated fact tables combine multiple business processes into a single table. Both advanced patterns serve distinct dimensional modeling scenarios.

NULL handling in fact table measures requires distinguishing between true zero values and missing data. Proper NULL handling in dimensional modeling prevents silent data aggregation errors and ensures accurate business reporting.

Header and line fact table patterns are used when a business transaction has a summary header and multiple detail lines. This dimensional modeling approach separates grain levels to optimize query performance for both summary and detailed reporting.