“What was inventory this month?” can mean the closing balance, average daily balance, highest daily closing balance or total receipts. A system that generates SQL without distinguishing these meanings can return a neat, syntactically valid but misleading answer. This article develops an engineering method for daily snapshots, from definitions to failure tests. All examples are constructed; none represents an IDENIFE customer or an actual experiment.
Define balance grain and the dimensions over which it can be added
A balance describes a state at a point in time; receipts and issues describe changes during a period. Kimball calls facts that can be summed across only some dimensions semi-additive, with balances as a common example. Ratios generally require aggregating their components before calculating the result.[1] Inventory for the same product and unit can be combined across non-overlapping warehouses at the same instant. Adding successive daily balances does not produce period inventory.
Specify exactly what one snapshot row represents. For example, business date, warehouse, product, lot and inventory status jointly identify a row recording on-hand quantity at a common cutoff. Available, reserved and in-transit quantities have separate definitions and cannot be added casually. Business rules must establish whether lots are disjoint and whether transfers create duplicate ownership. Adding units of different products may be arithmetically possible but analytically unhelpful.
The business definitions in enterprise Text-to-SQL must precede query generation. Inventory requires an additional time-aggregation rule: closing balance, daily average, highest daily closing balance and flow require different operators. An interactive assistant should clarify an ambiguous request; an automated report should use an approved metric name and definition and display it with the result. The model should not improvise the meaning.
Four days reveal the denominator problem
Consider one product in one warehouse: closing inventory is 100 units on day 1, missing on day 2, 60 on day 3 and 80 on day 4. Summing known records gives 240, which has no direct meaning as inventory for the four-day period. Averaging the three known closing balances gives 80, but that describes three observed days, not automatically a four-day average.
Filling the missing day with zero produces 60. Carrying day 1 forward produces 85. Both depend on additional assumptions. A missing row could reflect collection failure or a source that records only changes; it proves neither zero stock nor unchanged stock. Without evidence for day 2, the complete four-day average is indeterminate. Report three observed days out of four expected days.
PostgreSQL 18 documents that AVG averages non-null inputs and SUM returns null, rather than zero, when there are no input rows.[3] Expanding missing dates into null rows and then calling AVG still shrinks the denominator. Exposing gaps with a complete date set and deciding how to treat those gaps are separate tasks. A business justification must precede replacing null with zero through COALESCE.
Select the time position before aggregating across entities
For an exact daily closing metric, verify a valid snapshot on the requested business date for every required warehouse and product before aggregation. Taking the maximum date across the entire table can make slower-reporting warehouses disappear. Taking each entity's latest record also does not establish an exact same-day total: the records may refer to different dates.
If the business accepts the most recent available value as of a cutoff, give that metric a distinct name indicating estimation or latest observation. Select the latest valid record no later than the cutoff for each entity, retain its source date and age, and mark it missing beyond the approved freshness limit. The business sets that limit; there is no universal threshold proposed here. Show excluded entities so successful reporters do not silently define the entire population.
The dbt measures documentation offers non_additive_dimension, window_choice and window_groupings to express dimensions that should not be aggregated directly and the selection window.[2] This shows how a semantic layer can encode such rules; the configuration does not guarantee completeness. Check the deployed version and generated query to establish whether dates are selected globally or per entity. Selecting the maximum date is not selecting the maximum stock quantity.
Daily averages and time-weighted averages answer different questions
For an arithmetic average of daily closing stock over a complete period, first obtain a valid total for the same scope each day, then divide by the number of expected days. If day 2 in the example is later verified as 120, the average is (100 + 120 + 60 + 80) / 4 = 90. Averaging only opening and closing values is a different simplified convention, not an average of daily observations.
When inputs are post-change states at irregular intervals, averaging records gives disproportionate influence to frequently changing periods. In a constructed 24-hour interval, suppose inventory is 100 units for 18 hours and 40 for six hours. The time-weighted average is (100 × 18 + 40 × 6) / 24 = 85 units; the arithmetic mean of the two recorded values is 70. They measure different things.
Time weighting requires a valid starting state, a complete change sequence, interval boundaries and durations. Forward filling cannot establish reliability when these are unknown. Daily closing samples cannot reconstruct all intraday movements either. A business needing daily closing averages need not pay for hourly precision; measuring exposure to stockouts requires events or observations fine enough to support that objective.
Break the query into inspectable steps
First resolve the request into an approved metric, period, business time zone, entity scope, unit and missing-data policy. The inventory cutoff may differ from midnight and should come from an approved rule. Second, construct the expected entity-date set from valid warehouse and product populations, respecting opening, retirement and effective dates. Entities that did not yet exist should not be expected to have historical snapshots.
Third, check snapshot uniqueness at the business key, resolve corrections and retain the selection rationale. Fourth, align snapshots to the expected set, distinguishing verified zero, valid nonzero, missing and stale states. Fifth, apply the approved closing or averaging rule and attach presentation dimensions. Historically versioned dimensions must match uniquely at the applicable time so a join cannot multiply a balance.
Sixth, return the definition, cutoff, coverage and exception counts alongside the value. Label partial warehouse coverage as a subtotal for reporting warehouses rather than company-wide inventory. The model can explain those fields; it should not introduce zero filling, change the denominator or redescribe a latest observation as an exact closing balance during explanation.
Without reliable snapshots, an alternative is reconstruction from a trusted opening balance plus complete receipts, issues and adjustments. This requires handling late events, reversals, corrections and duplicates, with periodic reconciliation against counts or authoritative balances. Both routes need traceable evidence. Choose according to the source system's actual guarantees, not according to which query a model finds easier to generate.
Align period and measurement basis before calculating turnover ratios
For inventory turnover, first identify the business's chosen definition. If it uses period issue cost divided by average inventory value for that period, both components need compatible costing, currency, entity scope and time coverage. This is an engineering-consistency discussion, not an accounting policy or a proposed industry threshold. Sales revenue divided by stock units is not an interchangeable metric.
Across warehouses, aggregate compatible numerators and denominators before calculating the ratio; do not simply average warehouse ratios.[1] A zero, negative or incomplete denominator needs an explicit state and an approved business rule. The model should not translate a division-by-zero error into “exceptionally high turnover.” Nor should an incomplete period be compared with a complete month without qualification.
Test both numeric answers and the ability to withhold an unjustified total
Build deterministic cases small enough for manual verification. Four complete daily closing balances of 100, 120, 60 and 80 should produce closing stock of 80 and a daily average of 90. Remove day 2: under the strict complete-period definition, the average becomes indeterminate and coverage is 3/4. Only a valid zero record for day 2 makes the average 60. Missing rows and verified zeros need different branches.
Add two warehouses: A reports 80 on the target date; B reports 20 only on the previous date. An exact closing total must flag B as missing instead of presenting 80 as complete. An approved latest-available metric may return 100, but must expose B's date and freshness status. A duplicate snapshot or multiplying dimension join should trigger uniqueness or cardinality checks rather than silently doubling the result.
Time-dimension tests should also establish that daily balances are not summed into a monthly closing balance. Across disjoint warehouses with common units and observation times, warehouse subtotals should reconcile to the overall balance. Reordering input records should not change the result. The constructed time-weighting example should yield 85, not 70. These are expected answers for examples, not measured system performance.
Monitor valid-snapshot coverage, stale-entity counts and differences after corrections separately. Coverage denominators must come from the expected entity-date set, not received rows. When a few entities hold substantial inventory value, an additional approved weighted-coverage measure can reveal exposure that row counts conceal. Encode these rules before exposing them to AI analytics so inventory answers remain stable and reviewable.