Choosing the Right Granularity: A Guide to Fact Table Design in Data Modeling
In my previous article, I gave you the 5 steps to follow to build a data model. Declaring the granularity is the pivotal step in a dimensional design !
After identifying the business process, granularity must be declared before defining any dimension or fact table, because every dimension and fact must be consistent with the grain. Specifying the grain means indicating exactly what an individual fact table row represents. The granularity determines the level of detail associated with the fact table measurements. Basically, it answers the following question: How do you describe a single row in the fact table? Keep in mind that the granularity should be based on the physical realities of the operational system that captures the business process’s events. For instance, if you’re working in the retail industry, in the sales department and you need to develop a data model, you should understand how the transactional data is captured at the source. In general, for each transaction, you have a receipt where you have one row per product bought alongside the quantity sold and the unit price. You should keep that logic in your fact table meaning that one row describes one transaction per product alongside the quantity and its unit price:
transaction_id | product_id | transaction_date | quantity | unit_price
12347839422384 | 1882737382 | 13/01/2025 | 5 | 20
52683839404872 | 7836484638 | 12/01/2025 | 2 | 10My strongest advice is to start by focusing on atomic-grained data. Atomic grain refers to the most detailed level at which data is captured by a given business process. It is important to start at the atomic level to make sure that you can answer any business use case. By choosing the lowest granularity at first, you make sure that you’ll be able to answer the majority of your analytical use cases !
Once the granularity is chosen, you can define the fact table at the center of your star schema alongside its dimensions. Different grains must not be combined in the same fact table. Different grains must not be mixed in the same fact table.
Sources: