Why is an account balance called a semi-additive fact, and how should a report roll it up?

middlemust knowfreq 70%

answer

a level or a flow?
which single dimension misbehaves?
adding Monday and Tuesday counts what twice?
summable across accounts, not across days
last value, first value, or average

basics

~20 s

A balance is a level, not a flow: it can be summed across accounts, products or regions but not across time, because each period restates the same money. Roll it up over time with the period-end value or an average, never a sum.

solid answer

~50 s

Fact measures come in three grades. Additive measures — sales amount, quantity — sum correctly across every dimension. Non-additive measures — ratios, percentages, unit prices — sum across none. Semi-additive measures sit between: they sum across all dimensions except time. Balances, inventory on hand, headcount and open-position counts are the classic examples.

The reason is that a balance is a level measured at an instant, not a flow accumulated over an interval. Adding Monday's balance to Tuesday's counts the same money twice, so the monthly "total" scales with the number of days rather than the business.

Correct time aggregation depends on the question: take the last period value for a closing figure, the first for an opening figure, or an average of the period values for average balance or average inventory. Encode that rule where the query runs — a semantic layer or the mart's aggregate view — because nothing in the column type stops someone summing it.

Fact measures come in three grades. **Additive** measures — sales amount, quantity — sum correctly across every dimension. **Non-additive** measures — ratios, percentages, unit prices — sum across none. **Semi-additive** measures sit between: they sum across all dimensions *except* time. Balances, inventory on hand, headcount and open-position counts are the classic examples. The reason is that a balance is a *level* measured at an instant, not a *flow* accumulated over an interval. Adding Monday's balance to Tuesday's counts the same money twice, so the monthly "total" scales with the number of days rather than the business. Correct time aggregation depends on the question: take the **last period value** for a closing figure, the **first** for an opening figure, or an **average of the period values** for average balance or average inventory. Encode that rule where the query runs — a semantic layer or the mart's aggregate view — because nothing in the column type stops someone summing it.

code

sql · 10 lines

sql

-- one snapshot table, three legitimate time rollups
SELECT account_key,
       MAX(ending_balance) AS peak_balance,
       AVG(ending_balance) AS avg_daily_balance,
       MAX(CASE WHEN snapshot_date_key = 20260331
                THEN ending_balance END) AS closing_balance,
       SUM(interest_accrued) AS interest_for_month  -- flow: additive
FROM fct_account_balance_daily
WHERE snapshot_date_key BETWEEN 20260301 AND 20260331
GROUP BY account_key;

go deeper

for a junior

Recall the three grades — additive, semi-additive, non-additive — and one example of each. Balance and inventory on hand are the examples that will be asked for.

for a middle

Explain why time is the dimension that breaks: a level restates the same position each period. Show the correct rollups — last value, first value, average — and when each applies.

for a senior

Demonstrate that you make the rule enforceable: naming, published views or semantic-layer aggregation rules, and additive companion measures so analysts have a safe path to a total.

for a principal

Own the governance angle. Decide who declares aggregation rules for published measures, how they are tested, and how a mart is reconciled when a semi-additive measure has already been summed downstream.

The three grades of additivity

Every numeric column in a fact table falls into one of three grades, and the grade is a property of the measure, not of the database:

Additive — summable across every dimension in the model. Sales amount, quantity sold, tax collected, minutes streamed. These are flows: quantities accumulated over an interval, so adding two intervals gives a valid longer interval.
Semi-additive — summable across every dimension except time. Account balance, inventory quantity on hand, headcount, number of open policies, cash position. These are levels: a state measured at an instant.
Non-additive — summable across no dimension. Ratios, percentages, unit prices, averages already computed, distinct counts.

Semi-additive is the grade interviewers probe, because it is the only one where a wrong aggregation still produces a number that looks like money.

Why a balance breaks across time

Suppose account 42 has these daily ending balances:

text

Mar 01  1,000.00
Mar 02  1,000.00
Mar 03  1,500.00

Summing gives 3,500.00, which corresponds to nothing. The 1,000 on the second is the same thousand dollars as on the first — the balance is a restatement of a position, not a new event. The error is proportional to the number of periods, so a monthly figure is roughly 30x too large and an annual one 365x, which is exactly why nobody spots it from the shape of the number.

Across non-time dimensions the same balance behaves perfectly: summing the ending balance of every account in a branch on a given day is a real number — the branch's deposits that day. That asymmetry is the whole definition.

Correct aggregations over time

Which one is correct depends on the business question, and this is the part to say out loud:

Closing balance / ending inventory — take the value at the last period in the range: LAST_VALUE over an ordered window, or a join to the max snapshot date in range.
Opening balance — the first period in the range.
Average balance (the basis for interest, or for inventory turns) — the arithmetic mean of the period values, which is why daily granularity matters: an average of month-end balances is a different, coarser number than an average of daily balances.
Peak / trough exposure — MAX or MIN over the period.

Across other dimensions you still sum normally, and you can combine: sum over accounts within a day, then take the last day of the month.

sql

-- closing balance for a region across a date range
SELECT d.region_key, SUM(f.ending_balance) AS closing_balance
FROM fct_account_balance_daily f
JOIN dim_account d ON d.account_key = f.account_key
WHERE f.snapshot_date_key = (
        SELECT MAX(snapshot_date_key)
        FROM fct_account_balance_daily
        WHERE snapshot_date_key BETWEEN 20260301 AND 20260331)
GROUP BY d.region_key;

Where the rule should live

The database will happily sum a semi-additive column, so the protection has to be modelled deliberately:

Name the column so the grade is visible. ending_balance, qty_on_hand, headcount_at_period_end invite the right question; balance and amount do not.
Declare the time-aggregation rule in the semantic layer or the published view so ad-hoc tools apply last-value or average automatically instead of SUM.
Expose the additive companion where one exists. A snapshot table usually carries both a level (ending_balance) and flows over the period (deposits, withdrawals, interest_accrued). The flows are fully additive; steering analysts toward them answers most "total" questions safely.
Do not pre-aggregate a semi-additive measure to a coarser time grain by summing. If you build a monthly table, its measure must be the month-end level or an average, and the column name must say which.

Other semi-additive measures

Anything that is a stock rather than a flow behaves the same way: inventory units on hand, number of subscribers, employees on payroll, open support tickets, unfilled orders, hospital beds occupied, contract notional outstanding. If the answer to "can this be measured at a single instant?" is yes, expect semi-additivity.

A useful confirmation test: if the measure's units contain "per period" it is a flow and additive; if the units are a bare quantity at a point, it is a level and semi-additive.

Common mistakes

Calling a balance non-additive is the most frequent near-miss — it is additive across accounts, and saying otherwise means you cannot compute a branch total. The opposite error, storing a monthly balance as the sum of daily balances, quietly corrupts a mart in a way that reconciles against nothing. And averaging month-end balances when the business means average daily balance produces a defensible-looking number that is wrong for interest calculations.

## The three grades of additivity Every numeric column in a fact table falls into one of three grades, and the grade is a property of the *measure*, not of the database: - **Additive** — summable across every dimension in the model. Sales amount, quantity sold, tax collected, minutes streamed. These are flows: quantities accumulated over an interval, so adding two intervals gives a valid longer interval. - **Semi-additive** — summable across every dimension *except* time. Account balance, inventory quantity on hand, headcount, number of open policies, cash position. These are levels: a state measured at an instant. - **Non-additive** — summable across no dimension. Ratios, percentages, unit prices, averages already computed, distinct counts. Semi-additive is the grade interviewers probe, because it is the only one where a wrong aggregation still produces a number that looks like money. ## Why a balance breaks across time Suppose account 42 has these daily ending balances: ```text Mar 01 1,000.00 Mar 02 1,000.00 Mar 03 1,500.00 ``` Summing gives 3,500.00, which corresponds to nothing. The 1,000 on the second is the *same* thousand dollars as on the first — the balance is a restatement of a position, not a new event. The error is proportional to the number of periods, so a monthly figure is roughly 30x too large and an annual one 365x, which is exactly why nobody spots it from the shape of the number. Across non-time dimensions the same balance behaves perfectly: summing the ending balance of every account in a branch on a given day is a real number — the branch's deposits that day. That asymmetry is the whole definition. ## Correct aggregations over time Which one is correct depends on the business question, and this is the part to say out loud: - **Closing balance / ending inventory** — take the value at the *last* period in the range: `LAST_VALUE` over an ordered window, or a join to the max snapshot date in range. - **Opening balance** — the *first* period in the range. - **Average balance** (the basis for interest, or for inventory turns) — the arithmetic mean of the period values, which is why daily granularity matters: an average of month-end balances is a different, coarser number than an average of daily balances. - **Peak / trough exposure** — MAX or MIN over the period. Across other dimensions you still sum normally, and you can combine: sum over accounts within a day, then take the last day of the month. ```sql -- closing balance for a region across a date range SELECT d.region_key, SUM(f.ending_balance) AS closing_balance FROM fct_account_balance_daily f JOIN dim_account d ON d.account_key = f.account_key WHERE f.snapshot_date_key = ( SELECT MAX(snapshot_date_key) FROM fct_account_balance_daily WHERE snapshot_date_key BETWEEN 20260301 AND 20260331) GROUP BY d.region_key; ``` ## Where the rule should live The database will happily sum a semi-additive column, so the protection has to be modelled deliberately: 1. **Name the column so the grade is visible.** `ending_balance`, `qty_on_hand`, `headcount_at_period_end` invite the right question; `balance` and `amount` do not. 2. **Declare the time-aggregation rule in the semantic layer or the published view** so ad-hoc tools apply last-value or average automatically instead of SUM. 3. **Expose the additive companion where one exists.** A snapshot table usually carries both a level (`ending_balance`) and flows over the period (`deposits`, `withdrawals`, `interest_accrued`). The flows are fully additive; steering analysts toward them answers most "total" questions safely. 4. **Do not pre-aggregate a semi-additive measure to a coarser time grain by summing.** If you build a monthly table, its measure must be the month-end level or an average, and the column name must say which. ## Other semi-additive measures Anything that is a stock rather than a flow behaves the same way: inventory units on hand, number of subscribers, employees on payroll, open support tickets, unfilled orders, hospital beds occupied, contract notional outstanding. If the answer to "can this be measured at a single instant?" is yes, expect semi-additivity. A useful confirmation test: if the measure's units contain "per period" it is a flow and additive; if the units are a bare quantity at a point, it is a level and semi-additive. ## Common mistakes Calling a balance non-additive is the most frequent near-miss — it *is* additive across accounts, and saying otherwise means you cannot compute a branch total. The opposite error, storing a monthly balance as the sum of daily balances, quietly corrupts a mart in a way that reconciles against nothing. And averaging month-end balances when the business means average daily balance produces a defensible-looking number that is wrong for interest calculations.

Give a semi-additive measure that is not money, and one measure people wrongly call semi-additive.
Inventory quantity on hand and headcount are semi-additive: sum them across warehouses or departments, never across days. A commonly miscalled one is a distinct count — distinct customers is not semi-additive, it is non-additive, because it does not sum correctly across product or region either; overlapping sets have to be recounted from the atomic rows.
How would you build a monthly summary from a daily balance snapshot without corrupting the measure?
Never SUM the level. Take the month-end row for a closing figure, or the mean of the daily rows for an average balance, and name the resulting column accordingly — month_end_balance or avg_daily_balance. Additive companions such as deposits or interest accrued can be summed across the days normally, and should be carried alongside so most totals questions have a safe answer.
Why does summing a semi-additive measure across time so often reach production undetected?
Because the result is dimensionally plausible: it is a currency figure of roughly the right shape, it moves with the business, and it reconciles against nothing in particular. The error scales with the number of periods in the filter, so a wider date range simply produces a bigger wrong number rather than an obvious break.

saying these in an interview costs you the question

Says a balance is non-additive, so no dimension can be summed
Sums daily balances to get a monthly total
Thinks the database or column type prevents an invalid SUM
Averages month-end balances when the business means average daily balance
Cannot distinguish a level measured at an instant from a flow

Why is an account balance called a semi-additive fact, and how should a report roll it up?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The three grades of additivity

Why a balance breaks across time

Correct aggregations over time

Where the rule should live

Other semi-additive measures

Common mistakes

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics4 topics

The three grades of additivity

Why a balance breaks across time

Correct aggregations over time

Where the rule should live

Other semi-additive measures

Common mistakes

answer

go deeper

context