In this lesson, you will learn how to determine the "atomic level" of your fact tables to ensure your data warehouse remains flexible for any future business question.
Defining the "Grain"
In data modeling, the grain represents the most detailed, individual level of information captured in a fact table. Think of it as the "lowest common denominator" of your business process. If you decide that one row in your fact_sales table represents a single item scanned at a register, you have achieved the most granular (atomic) level of detail.
Why does this matter? Because you can always aggregate detailed data to create higher-level summaries (like daily or monthly totals), but you can never reverse-engineer details from a summary. If you store your data at a monthly aggregate level, you lose the ability to analyze individual customer behavior or specific product performance.
Example: Finding the Atomic Level
Imagine you are building a system for an e-commerce store. You have two options for your fact_sales table:
- Daily Aggregate:
date,total_revenue - Atomic Transaction:
sale_id,product_id,customer_id,timestamp,quantity,price
If you choose the Daily Aggregate, your analysis is limited to "How much did we make today?" If a stakeholder asks, "Which product is most popular on Tuesdays?", you cannot answer it.
If you choose the Atomic Transaction, you have the flexibility to answer the daily total question and the specific product question. You simply aggregate the rows based on the requirements of the query:
-- Calculating daily totals from atomic data
SELECT
DATE(timestamp) AS sale_date,
SUM(quantity * price) AS daily_revenue
FROM fact_sales
GROUP BY 1;
By keeping the fact table at the atomic grain, you preserve the raw "truth" of the business process. This provides the maximum analytical flexibility, as your data is ready to support any dimension or filtering requirement that stakeholders might come up with next month or next year.
Key Takeaway: Always design your fact tables at the lowest possible level of detail to ensure you can answer any analytical question without losing information.