Designing Aggregation Tables

In this lesson, you will learn how to design pre-summarized aggregation tables to dramatically speed up your executive-level dashboards.

The "Summary Layer" Philosophy

When dashboards query raw fact tables containing millions of rows, they often perform the same calculations—like SUM(sales) or COUNT(orders)—every time a user clicks "refresh." This is computationally expensive and slow. Aggregation tables act as a permanent, pre-calculated "summary layer" that sits between your granular fact tables and your BI tools.

Instead of scanning five years of transaction logs, the dashboard queries a pre-aggregated table that contains only the totals by day, store, and product category.

Designing the Structure

To design an effective aggregation table, you should group your data by the dimensions most commonly used in filters (like date, region, or category) and store the pre-calculated metrics as columns.

Consider this example where we move from a massive transactions table to a streamlined daily_sales_summary:

-- Creating the aggregation table
CREATE TABLE daily_sales_summary AS
SELECT 
    date_id, 
    region_id, 
    product_category_id,
    SUM(total_amount) AS daily_revenue,
    COUNT(transaction_id) AS total_transactions
FROM transactions
GROUP BY date_id, region_id, product_category_id;

-- Now, dashboards query this small table instead
SELECT * FROM daily_sales_summary 
WHERE region_id = 5 
AND date_id BETWEEN 20230101 AND 20230131;

Strategic Considerations

When designing these tables, keep these two rules in mind:

1. Granularity: Always aggregate to the highest level required by your most frequent dashboard reports. If nobody needs to see hourly data, don't include an hour column; it keeps the table smaller and faster.
2. Maintenance: Since these tables are snapshots, you must manage their updates. Use your previously learned Materialized View Refresh Policies or scheduled procedures to ensure the summary layer stays in sync with your raw fact tables as new data flows in.

By offloading the heavy lifting of aggregation to a pre-summarized layer, you shift the "work" from query time to load time, providing instant responses to your stakeholders.

Key Takeaway: Aggregation tables replace expensive real-time calculations with high-speed lookups by storing pre-summarized metrics at the exact granularity your dashboard requires.