In this lesson, you will learn how to choose the right level of detail for your fact tables to balance storage costs with analytical flexibility.
What is Granularity?
In data warehousing, granularity refers to the level of detail represented by a single row in a fact table. We often call this the "grain." For example, should your sales fact table record every individual item scanned at a register (atomic grain), or should it record the total daily sales per store (aggregated grain)?
The Atomic Advantage
In modern ELT paradigms, the best practice is to store data at the lowest level of granularity possible. This means keeping every single transaction as its own row. If your fact table is at the atomic level, you can aggregate it up to any level your business stakeholders require—by hour, by day, by region, or by product category.
If you store data at a pre-aggregated level (e.g., only storing "Total Daily Sales"), you lose the ability to perform drill-down analysis. You cannot see which specific hour or product drove those daily numbers.
The Storage Trade-off
While atomic data provides the most flexibility, it consumes more storage and can be slower to query for high-level summaries. However, because we use cloud data warehouses with decoupled storage and compute, we favor storing atomic data for its versatility. We then use views or materialized views to create "summary tables" for performance if a specific report is queried frequently.
Consider these two approaches for a retail store:
-- Atomic Grain: Every individual item sold
-- Best for deep analysis
SELECT * FROM fact_sales_atomic;
-- Aggregated Grain: Daily totals per store
-- Best for fast dashboard loading
CREATE VIEW daily_store_summary AS
SELECT date_key, store_id, SUM(sales_amount)
FROM fact_sales_atomic
GROUP BY 1, 2;
By keeping the underlying fact table at the atomic level, you ensure your data remains a "single source of truth" that can answer any future question, while using aggregation layers to keep your daily reports snappy.
Key Takeaway: Always design your fact tables at the lowest possible grain to maintain analytical flexibility, then use aggregation layers to optimize for query performance.