Hi everyone 
,
I’m currently working on an inventory management reporting pipeline using dbt, and I’m exploring the best practice approach for point-in-time inventory tracking and comparison across multiple dates.
Here’s the core use case:
We want to reconstruct our inventory position “as at” a given date — say, April 17, 2025 — and then compare this inventory snapshot to a different date (e.g., April 18, 2025) to measure changes in stock position (volume, count, etc.).
We want this to be flexible enough to:
- Compare any two dates (e.g., today vs yesterday, this week vs last week)
- Calculate changes in stock levels over n time periods (e.g., daily, weekly, monthly deltas)
- Expose these deltas in our dashboarding layer (Sisense in our case, but principles should be tool-agnostic)
Additional thoughts & Questions:
Is the snapshot model appropriate for inventory-style as-at reporting, or is it better suited for slowly changing dimensions (SCDs)?
Are there good examples or open-source projects where this style of point-in-time inventory comparison has been done well in dbt?