Fact Table Design for Rapid Aggregation

Model transaction history to support fast, slice-and-dice queries.

In this lesson, you will learn how to structure your fact tables to enable lightning-fast "slice-and-dice" analytical queries.

When we talk about rapid aggregation, the goal is to minimize the computational work the database engine performs when a user asks, "What was the total revenue for this product category in Q3?" If your fact table is overloaded with descriptive text or too many granular details, the database has to scan more data than necessary.

The Principle of Fact Table Granularity

To support fast aggregation, your fact table should focus on two types of columns: Degenerate Dimensions (like order IDs) and Metrics (like price, quantity, or tax). You should move all other descriptive attributes—such as customer names or product descriptions—into related dimension tables. By keeping your fact table "skinny" (fewer columns and smaller data types), more rows fit into each memory page, significantly increasing scan speed.

Pre-Aggregation Strategies

Sometimes, even a clean schema isn't enough for massive datasets. You can design your fact table to include pre-computed aggregates at specific levels of interest. For instance, if your business constantly queries daily sales by region, you might include a daily_region_summary table that acts as a specialized fact table.

Consider this simplified fact table design optimized for performance:

CREATE TABLE fact_sales (
    date_key INT,          -- Foreign key for fast filtering
    product_key INT,       -- Foreign key
    region_key INT,        -- Foreign key
    quantity_sold INT,     -- Metric for SUM()
    extended_price DECIMAL(18, 2) -- Metric for SUM()
);

-- Indexing foreign keys allows the engine to join 
-- and aggregate in record time.
CREATE INDEX idx_sales_date_product ON fact_sales(date_key, product_key);

By using integer keys instead of strings, the database performs fixed-length arithmetic during join operations, which is significantly faster than string comparisons. Furthermore, by placing your most frequently filtered columns (like date_key) as the leading edge of your composite index, you guide the engine to access only the precise rows required for the query, bypassing the need for a full table scan.

Key Takeaway: Keep your fact tables "skinny" by using integer-based surrogate keys and storing only numeric metrics, ensuring that the database engine spends its energy aggregating values rather than shuffling descriptive text.