Variance aggregation across partitions without bad averages

Data Modeling

To combine variance across partitions, do not average the partition variances. Convert each partition into sufficient statistics: count, mean, and sum of squared deviations from that partition mean. Then add both the within-partition variation and the between-partition variation caused by different group means.

The safe rule for variance aggregation across partitions

The safe rule is simple: variance must be combined from counts, means, and squared deviations, not from variance values alone.

For each partition, keep three values:

• n: the number of observations in the partition.
• mean: the average value inside the partition.
• M2: the sum of squared deviations from that partition mean.

If a partition stores population variance, its M2 is n times variance. If it stores sample variance, its M2 is n minus 1 times variance. Once every partition has been converted to M2, you can combine partitions safely.

For all partitions together, compute the global mean as the weighted mean: sum of n times partition mean, divided by total n.

Then compute total M2 as: sum of partition M2 plus sum of n times the squared difference between partition mean and global mean.

Finally, divide total M2 by total n for population variance, or by total n minus 1 for sample variance.

Why averaging partition variances fails

Variance measures spread around a mean. A partition variance only measures spread around that partition's own mean. If two partitions have different means, the combined dataset has additional spread between those means.

A simple average of variances loses that between-partition spread. Even a weighted average of variances only captures the within-partition spread. It still misses the distance between each partition mean and the overall mean.

This is why variance behaves differently from additive facts such as revenue, cost, or event count. You can add revenue across regions. You cannot add, average, or roll up variance without carrying extra statistical state.

Counterexample: both partitions have zero variance, but the total does not

Consider this hypothetical dataset split into two partitions.

• Partition A contains 0 and 0. Its mean is 0. Its variance is 0.
• Partition B contains 10 and 10. Its mean is 10. Its variance is 0.

If you average the two partition variances, the result is 0. That says the combined dataset has no spread.

But the combined dataset is 0, 0, 10, 10. Its global mean is 5. The population variance is 25, because every observation is 5 units away from the global mean. The sample variance is 33.333..., because the same total squared deviation is divided by 3 instead of 4.

The partition variances were not wrong. They answered a narrower question: how much spread exists inside each partition. The combined variance asks a different question: how much spread exists across all observations.

Store sufficient statistics, not just display metrics

If a model needs to support later aggregation, do not store only a formatted variance. Store the components needed to recombine the statistic.

At minimum, store count, mean, and M2 at the partition grain. This lets downstream models combine partitions without returning to raw observations for every query.

In some warehouse designs, it is also reasonable to store sum and sum of squares instead of mean and M2. That can work, but it is more exposed to numeric stability problems when values are large and variance is small. The durable modeling point is the same: store additive or recombinable components, not only the final variance.

The grain matters. If a row represents a customer-day, then count, mean, and M2 describe observations inside that customer-day. If a row represents a batch, they describe observations inside that batch. A later rollup must know exactly what observations each row summarizes.

Calculation recipe for combining partition variance

Use this recipe when each partition has n, mean, and either population variance or sample variance.

1. Normalize each partition to M2. If the stored variance is population variance, M2 equals n times variance. If the stored variance is sample variance, M2 equals n minus 1 times variance.
2. Compute total count. Total n equals the sum of all partition counts.
3. Compute global mean. Global mean equals the sum of n times partition mean divided by total n.
4. Add within-partition variation. Start with the sum of all partition M2 values.
5. Add between-partition variation. For each partition, add n times the squared difference between partition mean and global mean.
6. Choose the final denominator. Divide total M2 by total n for population variance, or by total n minus 1 for sample variance.

The key is that the denominator choice happens at the end. Do not average a mixture of sample and population variances. Convert to M2 first, combine M2, then apply the final definition.

Worked example with incompatible group means

Suppose a metric is summarized in two partitions.

• Partition A has values 8, 10, and 12. Its n is 3, mean is 10, sample variance is 4, and M2 is 8.
• Partition B has values 20 and 20. Its n is 2, mean is 20, sample variance is 0, and M2 is 0.

The total count is 5. The global mean is ((3 times 10) plus (2 times 20)) divided by 5, which equals 14.

Now combine M2. The within-partition M2 is 8 plus 0, which equals 8.

The between-partition contribution for A is 3 times (10 minus 14) squared, which equals 48. The between-partition contribution for B is 2 times (20 minus 14) squared, which equals 72.

Total M2 is 8 plus 48 plus 72, which equals 128.

The combined sample variance is 128 divided by 4, which equals 32. The combined population variance is 128 divided by 5, which equals 25.6.

Averaging the sample variances would give 2 if unweighted, or 2.4 if weighted by count. Both are wrong for the combined observations because they ignore the distance between the partition means.

Keep sample variance and population variance separate

Many mistakes happen because teams treat sample variance and population variance as interchangeable. They are related, but they use different denominators.

• Population variance divides by n. Use it when the observations are the full population for the question being asked.
• Sample variance divides by n minus 1. Use it when the observations are a sample and you want the common unbiased estimator for population variance.

The aggregation method can support either definition, but the inputs must be interpreted correctly. A stored variance value without its definition is not enough metadata to combine safely.

In data models, include the variance type in the metric definition or column description. Better yet, expose M2 and count to internal metric logic, then publish only the final variance needed by the dashboard or analysis.

Data modeling implications for partitions, batches, and dashboards

Variance aggregation problems usually appear when a metric is first correct at one grain and later reused at another grain.

Common examples include:

• Daily latency variance rolled up to weekly or monthly latency variance.
• Store-level basket-size variance rolled up to regional variance.
• Batch-level processing-time variance rolled up to pipeline-level variance.
• Cohort-level revenue variance rolled up to a broader segment.

In each case, the dashboard may look plausible while being mathematically wrong. The error is not a charting issue. It is a modeling issue: the intermediate table did not preserve the information required for the later question.

A practical dimensional modeling rule is to store numeric facts at the lowest useful grain when possible, especially for durations, measurements, and behavioral groupings. When storage or performance requires summarization, store recombinable components alongside the display metric.

Common failure modes to watch for

The most common failure mode is the harmless-looking average of averages pattern applied to variance. The query groups by one dimension, computes variance, then another query averages those results at a higher level.

Other failure modes include:

• Mixing denominators. Some partitions store sample variance while others store population variance.
• Dropping small partitions incorrectly. A one-row partition has no sample variance, but it still contributes to count, mean, and between-partition variation.
• Using equal weights for unequal partitions. A partition with 10 observations should not have the same influence as a partition with 10,000 observations unless the business question is explicitly about partitions rather than observations.
• Rolling up formatted dashboard values. Rounded variance or standard deviation values are not safe intermediate state.
• Confusing standard deviation with variance. Standard deviation is the square root of variance. You cannot average standard deviations and square the result to recover combined variance.
• Losing grain metadata. A row labeled only by date or segment may not explain what observations were summarized.

Operator checklist before publishing an aggregated variance metric

Before publishing a variance metric that rolls up partitions, ask these questions.

• What is the observation grain: event, order, user, session, batch, customer-day, or something else?
• What is the partition grain of the intermediate table?
• Does each partition store count?
• Does each partition store mean or enough information to compute mean?
• Does each partition store M2, or enough information to compute M2 safely?
• Is the variance definition population or sample?
• Are one-row and zero-row partitions handled deliberately?
• Are rounded dashboard values excluded from downstream aggregation?
• Does the metric definition say whether weighting is by observation count or by partition?
• Has the combined result been checked against raw observations on a small example?

If you cannot answer these questions, the metric may still be useful at its original grain, but it is not yet safe to roll up.

When to recompute from raw observations instead

Combining sufficient statistics is useful when raw observations are large, slow, or unavailable in downstream layers. But it is not always the best first choice.

Recompute from raw observations when the metric is new, the grain is disputed, filters are complex, or the source partitions are not trusted. Raw recomputation is also useful as a validation method for a small representative slice before relying on summarized components.

Use summarized variance components when the metric is stable, the observation grain is documented, and the same rollups are needed repeatedly. The goal is not to avoid raw data forever. The goal is to avoid mathematically invalid shortcuts.

Key takeaways

• Do not average partition variances when you need variance across all observations.
• The correct rollup uses count, mean, and M2 for each partition.
• Weighted average variance still misses between-partition spread unless all partition means are the same.
• Keep sample variance and population variance definitions separate until the final denominator is applied.
• Model variance components at the same grain as the summarized observations, and validate against raw data on a small example.

Next step

Take one existing variance or standard deviation metric in your warehouse or BI layer. Trace whether it is ever rolled up from partition-level results. If it is, check whether the intermediate model stores count, mean, and M2. If it does not, build a small manual example and compare the dashboard result with the raw-observation calculation.