In previous chapters, we established the components of a Star Schema using standard event data. However, analytical requirements frequently demand more than just a log of individual transactions. You often need to analyze the state of a system at a specific point in time or track the duration of a workflow as it progresses through stages.

This chapter outlines the specific design patterns used to address these varying levels of data grain. We begin by distinguishing between the three core fact table types. Transactional fact tables capture discrete events at their finest level of detail. Periodic snapshot fact tables function differently, aggregating data over a set interval to capture status, such as inventory levels or account balances.

We will then examine accumulating snapshot fact tables. These are designed for processes with a defined lifecycle, such as order fulfillment, where a single row is updated as the business process moves toward completion. Additionally, we address factless fact tables. These structures are necessary for coverage analysis, answering questions where the intersection of dimensions is the primary data point rather than a numerical value.

Understanding these patterns allows you to handle semi-additive measures effectively. For instance, while you can sum sales across time ($Total = \sum sales$), you cannot simply sum account balances across a month. You must instead apply logic such as averaging or selecting the last value. By the end of this section, you will be able to select the correct table architecture to match the grain of the business process you are modeling.