Aggregating Time Series Data With Fact Tables

Learn to pre-aggregate high-volume event data into summary fact tables to improve performance for time-series dashboard visualization.

In this lesson, you will learn how to improve dashboard performance by pre-aggregating high-volume event data into summary fact tables.

The Performance Problem

When you store raw event data—such as every single click, sensor reading, or transaction—your fact tables can grow into billions of rows. If a dashboard user wants to see the "Total Sales by Day for the Last Year," querying that raw table forces your database to scan millions of records every time the page refreshes. This creates latency, high compute costs, and a sluggish user experience.

The Solution: Summary Fact Tables

Instead of forcing the reporting tool to calculate totals on the fly, you create a Summary Fact Table (or aggregate table). You run an ETL process at a set interval—perhaps once an hour or once a day—to roll up the granular data into a higher level of detail.

For example, if your raw table fact_transactions has individual timestamps, you can create a fact_daily_sales_summary table:

INSERT INTO fact_daily_sales_summary (date_key, product_key, total_sales_amount, transaction_count)
SELECT 
    date_key, 
    product_key, 
    SUM(sale_amount), 
    COUNT(*)
FROM fact_transactions
WHERE transaction_date = CURRENT_DATE - INTERVAL '1 day'
GROUP BY date_key, product_key;

Why This Matters

By moving from millions of individual rows to one summary row per product per day, your dashboard query now scans only a few thousand rows instead of millions.

While the raw data remains available for deep-dive analysis, the summary table acts as a "fast path" for your time-series visualizations. This is a common strategy in data engineering to decouple the heavy ingestion of raw events from the high-speed requirements of business intelligence tools. You keep the historical detail for audits or complex reporting, but you serve the daily executive dashboards from the optimized summary table.

Key Takeaway: Pre-aggregating high-volume time-series data into summary fact tables drastically reduces query latency for dashboards by shifting the heavy computational lifting from query-time to load-time.