Transaction: one row per change
Periodic snapshot: one row per entity per period
Accumulating snapshot: milestone state
Combination: event evidence + point query
Choose by question, not storage size
“What inventory moved today?” needs transactions; “What was closing stock yesterday?” fits a daily snapshot. Current state cannot explain history, while transactions require reconstruction.
Many systems keep transactions as evidence and snapshots for analysis, then reconcile both.
Transactions preserve sequence
Record entity, event type, event and ingestion time, delta, source, and business key. Late, reversed, and duplicate events need idempotency and version rules.
Balance reconstruction requires a starting point, ordering, and missing-event policy.
Periodic snapshots stabilize state analysis
Generate one row per entity per day or month. Define time zone, cutoff, unchanged entities, nulls, and reruns.
Do not silently carry an old value after a failed snapshot; label quality and rebuilding scope.
Accumulating snapshots fit milestone processes
Orders, procurement, and tickets can store created, approved, shipped, and completed milestones for stage duration.
Reopening or repeated stages lose detail, so retain event transactions as evidence.
Accept with conservation checks
Verify opening + inflow - outflow = closing, including cross-day, reversal, late, and backfill cases. Route transaction/snapshot differences to quality handling.
Evaluate incremental builds, backfill, versions, quality flags, and recomputation, not only table creation.
Public references
Build an AI-ready data foundation
Download BuildTable or talk with us about your data modeling scenario.
Download BuildTable