Instantánea de inventario en Data Warehouse: paso a paso

Este tutorial muestra cómo crear una instantánea de inventario en Data Warehouse para registrar, día a día, el saldo de cada producto. Una tabla de instantáneas facilita informes históricos, análisis de roturas de stock y optimiza consultas en lugar de recalcular saldos a partir de transacciones. En escenarios reales, donde pueden existir 100k productos y 10M transacciones por año, una instantánea diaria reduce el tiempo de respuesta de informes críticos de minutos a milisegundos.

Requisitos previos

• Conocimientos básicos de SQL (T-SQL o SQL similar).
• Una base de datos para el Data Warehouse (por ejemplo Azure SQL, SQL Server).
• Tablas fuente: inventory_transactions (transacciones) y products (catálogo).
• Proceso ETL/ELT para programar cargas diarias (por ejemplo Azure Data Factory, SQL Agent).
• Buen entendimiento de retención de datos — por ejemplo mantener instantáneas diarias por 2 años y luego consolidar a mensuales.

Paso 1: Concepto de la instantánea de inventario

Una instantánea registra el saldo al final de cada día para cada producto: una fila por fecha+producto. Esto evita cálculos on-the-fly sobre millones de transacciones. Decida la granularidad (diaria, semanal) según la necesidad del negocio: para operaciones logísticas la granularidad diaria es común; para análisis ejecutivos puede bastar semanal. Las columnas esenciales son snapshot_date, product_id, quantity, load_ts, y campos de auditoría como loaded_by o source_batch_id si necesita trazabilidad. Planifique también el crecimiento: si tiene 100k productos y guarda instantáneas diarias, tendrá 36.5M de filas por año — considerar compresión y particionamiento se vuelve obligatorio.

Paso 2: Crear la tabla de instantáneas

Creé una tabla en el Data Warehouse para almacenar las instantáneas. Es importante definir una clave natural (snapshot_date + product_id) para garantizar unicidad y facilitar UPSERTs. Ejemplo en T-SQL (ajuste tipos conforme a su plataforma):

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 producto
CREATE INDEX IX_inventory_snapshot_product ON dbo.inventory_snapshot(product_id, snapshot_date);

Si el SGBD lo soporta, defina particionamiento por snapshot_date (por ejemplo por mes) para acelerar mantenimiento y limpieza. Configure compresión de datos (ROW o PAGE) cuando esté disponible: típicamente reduce el espacio en disco 3x-10x para este tipo de tabla densa.

Paso 3: Carga inicial (full) de la instantánea de inventario

Para la carga inicial, calcule el saldo acumulado hasta cada fecha para cada producto. Un método simple usa un conjunto de fechas (días) y un CROSS JOIN con productos y luego un subselect para sumar transacciones hasta el final del día. Este enfoque es fácil de entender pero puede ser costoso: por ejemplo, generar 365 días x 100k productos = 36.5M filas, y cada subselect puede implicar scans; use por tanto agregaciones previas cuando sea posible.

-- 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;

Notas: para mejorar el rendimiento, primero agregue inventory_transactions por día y producto, y después haga un cumulative sum por producto. En muchos escenarios una operación en batch que toma 30-90 minutos para la carga inicial es aceptable; si tarda más, divida por lotes o haga paralelismo.

Paso 4: ETL incremental diario para la instantánea de inventario

El objetivo diario es calcular el saldo al final del día corriente y hacer UPSERT en la tabla de instantáneas. Use MERGE (T-SQL) para insertar/actualizar de forma idempotente. Primero, calcule el saldo de ese día (ej.: @target_date). Para 100k productos, una ejecución bien optimizada debe completarse en unos minutos; si tarda mucho, verifique índices y agregaciones previas.

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());

Programe este script diariamente en su scheduler de ETL/ELT (Azure Data Factory, SQL Agent, etc.). Para idempotencia, asegure que el proceso puede reejecutarse sin crear duplicados — por eso usamos MERGE y clave primaria.

Paso 5: Optimización y buenas prácticas

Para grandes volúmenes, considere:

• Pre-agregar transacciones por día y producto antes del MERGE para reducir scans (p.ej. reducir 10M filas a 100k agregaciones diarias).
• Particionar la tabla por snapshot_date (si el SGBD lo soporta) para mejorar limpieza y rendimiento — por ejemplo por mes o por trimestre.
• Mantener una tabla de fechas para evitar generación dinámica y facilitar joins de calendario.
• Monitorizar tiempo de ejecución, bloqueos y utilización de I/O. Piense en realizar cargas fuera de pico y en usar índices de cobertura.
• Definir política de retención: mantener diarias 2 años, luego consolidar a mensuales, reduciendo el espacio en 12x para datos antiguos.

Verificar el resultado

Para confirmar que la instantánea de inventario es correcta:

• Consultar algunos productos y fechas y comparar con la suma de inventory_transactions hasta esa fecha: SELECT SUM(qty_change) .... Haga muestreos (10-20 productos) y validaciones automatizadas.
• Verificar que hay exactamente una fila por snapshot_date + product_id (la clave primaria garantiza esto).
• Probar el proceso incremental reenviando el mismo día y confirmando que es idempotente (no crea duplicados y actualiza load_ts).
• Medir rendimiento: comparar tiempo de informe con y sin instantánea; es común observar una reducción del 70-99% en el tiempo de ejecución.

Conclusión

Crear una instantánea de inventario en Data Warehouse simplifica análisis históricos y acelera informes al evitar recomputación a partir de transacciones. Próximos pasos: automatice con su ETL, implemente particionamiento, evalúe compresión/retención de datos y cree pruebas de regresión. Consejo: compare el rendimiento entre calcular saldos on-demand y usar la tabla de instantáneas para su informe más crítico — las ganancias suelen justificar el esfuerzo de implementación y operación.