A balance is additive across entities, not usually across time

Inventory across three warehouses on one day can be totaled; adding one warehouse balance for 30 days does not produce monthly inventory.

The metric contract must name additive dimensions and its time behavior.

Declare whether snapshots are dense or sparse

A dense snapshot has one row per entity per day. A sparse snapshot records only changes and needs the most recent valid value.

No row can mean unchanged, uncollected, or nonexistent, and must not default silently to zero.

Ending, average, and movement are separate measures

Ending balance selects the window endpoint. Average may use daily observations or time weighting. Receipts and issues are flows that can sum through time.

Do not infer movement only from balance change because adjustments, returns, and corrections intervene.

Put temporal aggregation in the semantic contract

Record default grain, last-non-null behavior, timezone, close-of-day rule, and valid cross-entity aggregation.

Generated queries should use that contract rather than applying SUM to every numeric column.

Accept across time boundaries

Test day, weekend, month-end, unchanged dates, missing dates, new entities, and timezone close while reconciling ending, average, and flow.

BuildTable can be evaluated for metric semantics; last-value, time weighting, snapshot filling, and query generation need POC confirmation.

Create an additivity matrix before choosing SUM

Declare whether each measure is additive across product, customer, location, organization, and time. A balance can aggregate across entities at one instant but not through dates.

Ending, opening, average, and movement are different metrics over the same source field. Their temporal behavior belongs in the semantic contract.

Dense and sparse snapshots need different last-value logic

Dense snapshots select the endpoint. Sparse snapshots require an as-of lookup per entity. Absence can mean unchanged, uncollected, or nonexistent and must not default to zero.

Select each entity’s last valid value before summing. A single global max date drops entities that did not update on that date.

Average balances need time weighting

Arithmetic mean works for equally spaced complete daily observations. Sparse changes require weighting each value by its duration; otherwise a late spike receives half the weight in a two-record month.

Report coverage days and align opening, closure, timezone, and day-end semantics across entities.

Do not substitute balance change for flow

Net ending-minus-opening cannot separate receipt, sale, return, transfer, disposal, and adjustment. Use transaction facts for flow and reconcile them to snapshots.

Inventory turnover needs aligned cost-of-sales and average inventory. Aggregate numerator and denominator before division; do not average entity ratios.

Test windows and verify BuildTable scope

Exercise single day, weekend, month-end, missing data, new and closed entities, timezone, negative balance, and late backfill across ending, average, flow, and change.

BuildTable may be evaluated for metric semantics, while last-value, as-of join, time weighting, snapshot fill, and generated aggregation require POC confirmation. Unknown completeness must remain visible.

Why should account and inventory balances not be summed across days?