In this lesson, you will learn how to classify metrics into additive and non-additive categories to ensure your data aggregations provide accurate business insights.
When you build a fact table, you are essentially storing measurements about your business processes. However, not all numbers are created equal; how you calculate them depends on their mathematical nature.
Additive Facts
An additive fact is a metric that can be meaningfully summed across any dimension. These are the most flexible metrics in your data model. If you store the sales_amount for an individual transaction, you can safely add those values together to calculate the daily, monthly, or regional revenue.
Non-Additive Facts
A non-additive fact cannot be summed across one or more dimensions. If you try to sum these, you will generate a "mathematically meaningless" number.
The most common example is a ratio or percentage. Consider a daily_inventory_percentage or a customer_churn_rate. If you have a 5% churn rate on Monday and a 10% churn rate on Tuesday, adding them to get a 15% "total" churn rate is incorrect. Instead, you must calculate the average or re-calculate the ratio based on the raw, additive components (the number of customers who left divided by the total number of customers).
Practical Illustration
Look at this snippet of a fact_sales table:
-- Additive: Totaling these makes sense
SELECT SUM(revenue) FROM fact_sales;
-- Non-Additive: Summing these produces garbage data
-- (A ratio must be averaged or recalculated)
SELECT AVG(profit_margin_percent) FROM fact_sales;
When you define your fact tables, you must label your columns carefully. If you attempt to sum a non-additive column in a dashboard, your stakeholders will receive misleading reports. Always ask yourself: "If I add these values across time or geography, does the resulting number represent a real-world business total?" If the answer is no, you are dealing with a non-additive fact that requires an AVG, MIN, MAX, or a specific calculation logic rather than a SUM.
Key Takeaway: Additive facts can be aggregated with a simple sum, while non-additive facts require more complex logic to prevent generating mathematically incorrect business metrics.