Calculating Derived Metrics within Fact Tables

In this lesson, you will learn how to decide whether to pre-calculate business metrics during your ETL process or defer those calculations to your BI layer at query time.

When designing your fact tables, you will often need metrics that aren't stored directly in your source systems, such as Total_Tax_Amount (derived from Price * Tax_Rate) or Net_Profit (derived from Revenue - Cost). You have two main approaches to handling these "derived metrics."

Pre-calculating During ETL (Storage vs. Speed)

If you calculate these metrics while moving data into your fact table, you are trading storage space for query speed.

• Pros: The math is done once during the data pipeline. When an analyst runs a report, the database simply fetches a pre-computed number, making the query extremely fast.
• Cons: You increase the size of your fact table (storage costs), and if the business logic for that metric changes (e.g., a tax law update), you must re-process historical records.

-- ETL approach: Calculating during load
INSERT INTO fact_sales (date_key, product_key, revenue, cost, net_profit)
SELECT 
    date_key, 
    product_key, 
    revenue, 
    cost, 
    (revenue - cost) AS net_profit -- Calculated at rest
FROM stage_sales;

Calculating at Query Time (Flexibility vs. Performance)

Alternatively, you can keep your fact table "lean" by storing only the base attributes and calculating the metrics on the fly using a SQL View or a BI tool.

• Pros: You maintain a smaller footprint in your database, and your data is more flexible. If the business changes how Net_Profit is defined, you only update the View definition, not the entire fact table.
• Cons: Every time a user runs a dashboard, the database performs the math. On massive datasets, this can lead to slower performance.

Which one to choose?

As a general rule:

1. Use Pre-calculation for complex math that is expensive to execute or essential for high-performance dashboards that are accessed by hundreds of users.
2. Use Query-time Calculation for simple math that changes frequently or when data storage efficiency is a priority.

Key Takeaway: Pre-calculate metrics during ETL to prioritize query performance, but use query-time calculation to prioritize flexibility and storage efficiency.