Zero: observed quantity is zero
Unknown: expected but unavailable
Not collected: source has no value
Not applicable: field does not apply
Pending: event has not happened
No row: fact record is absent
Technical null does not explain business meaning
Zero inventory is known absence; unknown inventory prevents a decision. A blank delivery date might mean not shipped yet or never collected.
Define the business state first, then choose storage, display, and calculation behavior.
Retain evidence with the value
Critical attributes may need a value, status, source timestamp, and quality flag. An explicit unknown dimension member keeps unmatched facts from disappearing in joins.
Give unknown and not applicable distinct codes and document them in the semantic model.
Aggregation depends on state
Observed zero belongs in sums and averages, while unknown usually must not be converted to zero. An incomplete period may be excluded from a completion metric or shown separately.
Distinguish row count, populated count, known count, and applicable count, and expose the denominator.
AI responses should surface uncertainty
For “products with no sales,” distinguish confirmed zero sales, missing sales feeds, and products not yet launched. Clarify or segment the response when intent is ambiguous.
Quality state must travel into query results and explanations instead of remaining in a monitoring dashboard.
Test combinations of missingness
Create zero, null, unknown member, not applicable, late fact, missing date, and unmatched join cases, then inspect filters, totals, charts, and language.
BuildTable can support evaluation of AI-friendly fields and relations; state types, filling, propagation, and response behavior need POC verification.
Public references
Build an AI-ready data foundation
Contact us to discuss your data modeling scenario and access BuildTable Desktop.
Contact us