Inventory snapshot in Data Warehouse: step by step

This tutorial shows how to create an inventory snapshot in a Data Warehouse to record, day by day, the balance of each product. A snapshot table eases historical reporting, stock-out analysis and optimizes queries instead of recalculating balances from transactions. In real scenarios, where there may be 100k products and 10M transactions per year, a daily snapshot reduces response time for critical reports from minutes to milliseconds.

Prerequisites

• Basic knowledge of SQL (T-SQL or similar SQL).
• A database for the Data Warehouse (for example Azure SQL, SQL Server).
• Source tables: inventory_transactions (transactions) and products (catalog).
• ETL/ELT process to schedule daily loads (for example Azure Data Factory, SQL Agent).
• Good understanding of data retention — for example keep daily snapshots for 2 years and then consolidate to monthly.

Step 1: Snapshot concept

A snapshot records the end-of-day balance for each product: one row per date+product. This avoids on-the-fly calculations over millions of transactions. Decide the granularity (daily, weekly) depending on business needs: for logistics operations daily granularity is common; for executive analyses weekly may be sufficient. Essential columns are snapshot_date, product_id, quantity, load_ts, and audit fields such as loaded_by or source_batch_id if you need traceability. Also plan for growth: if you have 100k products and store daily snapshots, you'll have 36.5M rows per year — consider compression and partitioning as mandatory.

Step 2: Create the snapshot table

Create a table in the Data Warehouse to store the snapshots. It is important to define a natural key (snapshot_date + product_id) to ensure uniqueness and facilitate UPSERTs. Example in T-SQL (adjust types according to your platform):

CREATE TABLE dbo.inventory_snapshot (
  snapshot_date date NOT NULL,
  product_id int NOT NULL,
  quantity bigint NOT NULL,
  load_ts datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
  PRIMARY KEY (snapshot_date, product_id)
);

-- Index para consultas por produto
CREATE INDEX IX_inventory_snapshot_product ON dbo.inventory_snapshot(product_id, snapshot_date);

If the RDBMS supports it, define partitioning by snapshot_date (for example by month) to speed maintenance and cleanup. Configure data compression (ROW or PAGE) when available: it typically reduces disk space 3x-10x for this type of dense table.

Step 3: Initial (full) load of the inventory snapshot

For the initial load, calculate the cumulative balance up to each date for each product. A simple method uses a set of dates (days) and a CROSS JOIN with products and then a subselect to sum transactions up to the end of the day. This approach is easy to understand but can be expensive: for example, generating 365 days x 100k products = 36.5M rows, and each subselect can imply scans; therefore use pre-aggregations when possible.

-- Exemplo: intervalo de datas (ajuste conforme necessário)
WITH days AS (
  SELECT CAST(MIN(transaction_ts) AS date) AS start_day,
         CAST(MAX(transaction_ts) AS date) AS end_day
  FROM dbo.inventory_transactions
), gen AS (
  SELECT DATEADD(day, n.number, d.start_day) AS day
  FROM days d
  JOIN ( -- gera números sequenciais; ajustar para mais dias
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS number
    FROM sys.objects
  ) n ON n.number <= DATEDIFF(day, d.start_day, d.end_day)
)
INSERT INTO dbo.inventory_snapshot(snapshot_date, product_id, quantity, load_ts)
SELECT g.day AS snapshot_date,
       p.product_id,
       (SELECT SUM(t.qty_change)
        FROM dbo.inventory_transactions t
        WHERE t.product_id = p.product_id
          AND CAST(t.transaction_ts AS date) <= g.day) AS quantity,
       SYSUTCDATETIME()
FROM gen g
CROSS JOIN dbo.products p;

Notes: to improve performance, first aggregate inventory_transactions by day and product, and then perform a cumulative sum by product. In many scenarios a batch operation that takes 30-90 minutes for the initial load is acceptable; if it takes longer, split into batches or use parallelism.

Step 4: Daily incremental ETL for the inventory snapshot

The daily goal is to calculate the end-of-day balance for the target day and perform an UPSERT into the snapshot table. Use MERGE (T-SQL) to insert/update idempotently. First, compute the balance for that day (e.g., @target_date). For 100k products, a well-optimized run should complete in a few minutes; if it takes too long, check indexes and pre-aggregations.

DECLARE @target_date date = CAST(GETUTCDATE() AS date);

WITH daily_balance AS (
  SELECT p.product_id,
         (SELECT SUM(t.qty_change)
          FROM dbo.inventory_transactions t
          WHERE t.product_id = p.product_id
            AND CAST(t.transaction_ts AS date) <= @target_date) AS quantity
  FROM dbo.products p
)
MERGE dbo.inventory_snapshot AS target
USING daily_balance AS src
ON target.snapshot_date = @target_date AND target.product_id = src.product_id
WHEN MATCHED THEN
  UPDATE SET quantity = src.quantity, load_ts = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
  INSERT (snapshot_date, product_id, quantity, load_ts)
  VALUES (@target_date, src.product_id, src.quantity, SYSUTCDATETIME());

Schedule this script daily in your ETL/ELT scheduler (Azure Data Factory, SQL Agent, etc.). For idempotency, ensure the process can be re-run without creating duplicates — which is why we use MERGE and a primary key.

Step 5: Optimization and best practices

For large volumes, consider:

• Pre-aggregating transactions by day and product before the MERGE to reduce scans (e.g., reduce 10M rows to 100k daily aggregations).
• Partitioning the table by snapshot_date (if the RDBMS supports it) to improve cleanup and performance — for example by month or quarter.
• Maintaining a dates table to avoid dynamic generation and facilitate calendar joins.
• Monitoring execution time, locks and I/O usage. Consider running loads off-peak and using covering indexes.
• Defining a retention policy: keep daily for 2 years, then consolidate to monthly, reducing space by 12x for older data.

Verify the result

To confirm the inventory snapshot is correct:

• Query some products and dates and compare with the sum of inventory_transactions up to that date: SELECT SUM(qty_change) .... Do sampling (10-20 products) and automated validations.
• Verify there is exactly one row per snapshot_date + product_id (the primary key enforces this).
• Test the incremental process by re-running the same day and confirming it is idempotent (does not create duplicates and updates load_ts).
• Measure performance: compare report time with and without snapshot; it is common to see a 70-99% reduction in execution time.

Conclusion

Creating an inventory snapshot in a Data Warehouse simplifies historical analysis and speeds up reporting by avoiding recomputation from transactions. Next steps: automate with your ETL, implement partitioning, evaluate compression/data retention and create regression tests. Tip: compare performance between on-demand balance calculation and using the snapshot table for your most critical report — the gains usually justify the implementation and operational effort.