Escenarios de uso de tablas temporales

Aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceBase de datos SQL en Microsoft Fabric

Las tablas temporales versionadas por el sistema son útiles en situaciones en las que es necesario realizar un seguimiento del historial de cambios en los datos. Debido a las enormes ventajas de productividad, se recomienda tener en cuenta las tablas temporales en los siguientes casos de uso.

Auditoría de datos

Puede usar el control de versiones del sistema temporal en tablas que almacenan información crítica, para realizar el seguimiento de qué ha cambiado y cuándo, y para realizar análisis forense de datos en cualquier momento.

Utiliza tablas temporales para planificar escenarios de auditoría de datos en las primeras etapas del ciclo de desarrollo. Puedes añadir auditoría de datos a aplicaciones o soluciones existentes cuando la necesites.

En el siguiente diagrama se muestra una tabla Employee con una muestra de datos que incluye versiones de fila actuales (marcadas en color azul) e históricas (marcadas en color gris).

La parte derecha del diagrama visualiza las versiones de filas en un eje de tiempo y las filas que seleccionas con diferentes tipos de consulta en una tabla temporal, con o sin la SYSTEM_TIME cláusula.

Habilitación del control de versiones del sistema en una nueva tabla para auditoría de datos

Si identifica datos que requieren auditoría, cree las tablas de la base de datos como tablas temporales versionadas por el sistema. El siguiente ejemplo ilustra un escenario con una tabla llamada Employee en una base de datos hipotética de RRHH:

CREATE TABLE Employee
(
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR (100) NOT NULL,
    [Position] VARCHAR (100) NOT NULL,
    [Department] VARCHAR (100) NOT NULL,
    [Address] NVARCHAR (1024) NOT NULL,
    [AnnualSalary] DECIMAL (10, 2) NOT NULL,
    [ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Diversas opciones para crear una tabla versión temporal del sistema se describen en Crear una tabla temporal versionada por sistema.

Habilitación del control de versiones del sistema en una tabla existente para auditoría de datos

Si necesita realizar una auditoría de datos en bases de datos existentes, utilice ALTER TABLE para ampliar las tablas no temporales de modo que pasen a estar versionadas por el sistema. Para evitar cambios rotos en tu aplicación, añade columnas de periodo como HIDDEN, como se explica en Crear una tabla temporal versionada por sistema.

En el ejemplo siguiente se muestra cómo habilitar el control de versiones del sistema en una tabla Employee existente en una hipotética base de datos de recursos humanos. Habilita el control de versiones del sistema en la tabla Employee en dos pasos. En primer lugar, las nuevas columnas de periodo se agregan como HIDDEN. Después, se crea la tabla de historial predeterminada.

ALTER TABLE Employee
ADD
    ValidFrom DATETIME2 (2) GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 (2) GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Employee
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

Después de ejecutar el script anterior, la tabla de historial recopila de forma transparente todos los cambios de datos. En un escenario típico de auditoría de datos, se consulta todos los cambios de datos aplicados a una fila individual dentro de un periodo de tiempo de interés. La tabla de historial predeterminada se crea con un árbol B de almacén de filas agrupado para tratar eficazmente este caso de uso.

Realización de análisis de datos

Después de habilitar el control de versiones mediante cualquier de los enfoques anteriores, basta una consulta para realizar la auditoría de datos. La siguiente consulta busca versiones de fila para los registros de la tabla Employee con EmployeeID = 1000 que estaban activas al menos durante una parte del periodo comprendido entre el 1 de enero de 2021 y el 1 de enero de 2022 (incluido el límite superior):

SELECT *
FROM Employee FOR SYSTEM_TIME
    BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Reemplace FOR SYSTEM_TIME BETWEEN...AND por FOR SYSTEM_TIME ALL para analizar todo el historial de cambios de datos de ese empleado en particular:

SELECT *
FROM Employee FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Para buscar versiones de fila que estaban activas solo dentro de un período (y no fuera de él), use CONTAINED IN. Esta consulta es eficaz porque solo consulta la tabla de historial:

SELECT *
FROM Employee FOR SYSTEM_TIME
    CONTAINED IN ('2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000')
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Por último, en algunos escenarios de auditoría, quizá quieras ver cómo era toda la tabla en algún momento del pasado:

SELECT *
FROM Employee FOR SYSTEM_TIME
    AS OF '2021-01-01 00:00:00.0000000';

Las tablas temporales versionadas por el sistema almacenan valores para las columnas de período en la zona horaria UTC, aunque puede resultarle más conveniente trabajar con su zona horaria local, tanto para filtrar datos como para mostrar los resultados. El siguiente ejemplo de código muestra cómo aplicar una condición de filtrado, que se especifica en la zona horaria local y luego se convierte a UTC usando AT TIME ZONE:

/* Add offset of the local time zone to current time*/
DECLARE @asOf AS DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, -9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
       [Name],
       Position,
       Department,
       [Address],
       [AnnualSalary],
       ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
       ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

El uso de AT TIME ZONE es útil para todos los demás escenarios donde se usan tablas con versión del sistema.

Las condiciones de filtrado especificadas en cláusulas temporales con FOR SYSTEM_TIME son SARGables.

Si consultas directamente la tabla de historial, asegúrate de que tu condición de filtrado también sea SARGable especificando filtros en forma de <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Si se aplica AT TIME ZONE a las columnas de período, SQL Server realiza un examen de tabla o índice, lo que puede ser costoso. Evite este tipo de condición en las consultas:

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition.

Para más información, consulte Consultar datos en una tabla temporal con control de versiones del sistema.

Análisis a un momento dado (viaje en el tiempo)

En lugar de centrarse en los cambios en registros individuales, los escenarios de viaje en el tiempo muestran cómo cambian conjuntos de datos completos a lo largo del tiempo. A veces el viaje en el tiempo incluye varias tablas temporales relacionadas, cada una cambiando a un ritmo independiente, para las cuales quieres analizar:

• Tendencias de los indicadores importantes en los datos históricos y actuales
• Instantánea exacta de todos los datos "a partir de" cualquier momento dado del pasado (ayer, hace un mes, etc.)
• Diferencias entre dos momentos dados de interés (hace un mes frente a hace tres meses, por ejemplo)

Muchos escenarios del mundo real requieren análisis de viajes en el tiempo. Para ilustrar este escenario de uso, veamos el procesamiento de transacciones en línea (OLTP) con historial autogenerado.

OLTP con historial de datos generado automáticamente

En sistemas de procesamiento de transacciones, puede analizar cómo cambian las métricas importantes en el tiempo. Idealmente, analizar el historial no debería comprometer el rendimiento de la aplicación OLTP, donde el acceso al estado más reciente de los datos debe producirse con una latencia mínima y un bloqueo de datos. Puede usar tablas temporales con versión del sistema a fin de mantener de forma transparente el historial completo de cambios para su análisis posterior, independientemente de los datos actuales, con un impacto mínimo en la carga de trabajo OLTP principal.

Para cargas de trabajo de procesamiento transaccional elevadas en SQL Server y Azure SQL Managed Instance, recomendamos que utilices tablas temporales versionadas para System con tablas optimizadas para memoria, que permiten almacenar datos actuales en memoria y el historial completo de cambios en disco de forma rentable.

Para la tabla de historial, se recomienda utilizar un índice de almacén de columnas agrupado por las razones siguientes:

• El análisis de tendencias típico aprovecha el rendimiento de las consultas que proporciona un índice de almacén de columnas agrupado.

• La tarea de vaciado de datos con tablas optimizadas para memoria funciona mejor con mucha carga de trabajo OLTP cuando la tabla de historial tiene un índice de almacén de columnas agrupado.

• Un índice de almacén de columnas agrupado proporciona una compresión excelente, especialmente en escenarios donde no todas las columnas se cambian al mismo tiempo.

Utilizar tablas temporales con OLTP en memoria reduce la necesidad de mantener todo el conjunto de datos en memoria y permite distinguir fácilmente entre datos calientes y fríos.

Ejemplos de escenarios reales que encajan bien en esta categoría son la administración de inventarios o la compraventa de divisas, entre otros.

El siguiente diagrama muestra un modelo de datos simplificado utilizado para la gestión de inventarios:

El siguiente ejemplo de código crea ProductInventory como una tabla temporal con control de versiones del sistema en memoria, con un índice columnstore agrupado en la tabla de historial (que reemplaza al índice de almacenamiento por filas creado de forma predeterminada):

USE TemporalProductInventory;
GO

BEGIN

    --If the table is system-versioned, set SYSTEM_VERSIONING to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
            SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];

END
GO

CREATE TABLE [dbo].[ProductInventory]
(
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
    ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

Para el modelo anterior, este puede ser el aspecto del procedimiento para mantener el inventario:

CREATE PROCEDURE [dbo].[spUpdateInventory] (
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    UPDATE dbo.ProductInventory
        SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId;

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location
    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
        BEGIN
            SET @quantityIncrement = 0;
        END

        INSERT INTO [dbo].[ProductInventory]
        (
            [ProductId],
            [LocationID],
            [Quantity]
        )
        VALUES (
            @productId,
            @locationId,
            @quantityIncrement
        );
    END
END;

El procedimiento almacenado spUpdateInventory inserta un nuevo producto en el inventario o actualiza la cantidad de productos para la ubicación específica. La lógica de negocio es sencilla y se centra en mantener el estado más reciente siempre preciso incrementando o decrementando el Quantity campo mediante la actualización de la tabla, mientras que las tablas versionadas por sistema añaden de forma transparente una dimensión de historial a los datos, como se muestra en el siguiente diagrama.

Ahora, puedes consultar eficientemente el estado más reciente del módulo compilado nativamente:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT ProductId,
           LocationID,
           Quantity,
           ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId;
END;
GO

EXECUTE [dbo].[spQueryInventoryLatestState];

El análisis de los cambios de datos en el tiempo pasa a ser una tarea sencilla con la cláusula FOR SYSTEM_TIME ALL, como se muestra en el ejemplo siguiente:

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory AS
    SELECT ProductId,
           LocationId,
           Quantity,
           ValidFrom,
           ValidTo
    FROM [dbo].[ProductInventory] FOR SYSTEM_TIME ALL;
GO

SELECT *
FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

En el diagrama siguiente se muestra el historial de datos de un producto que se puede representar fácilmente si se importa la vista anterior en Power Query, Power BI o una herramienta de inteligencia empresarial similar:

En este escenario puedes usar tablas temporales para realizar otros tipos de análisis de viajes en el tiempo, como reconstruir el estado del inventario AS OF en cualquier momento del pasado o comparar instantáneas que pertenecen a diferentes momentos en el tiempo.

Para este escenario de uso, también se pueden extender las Product tablas y Location para que se conviertan en tablas temporales y así permitir un análisis posterior del historial de cambios de UnitPrice y NumberOfEmployee.

ALTER TABLE Product
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Product
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location]
ADD
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DFValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE [Location]
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

Dado que el modelo de datos ahora incluye varias tablas temporales, la mejor práctica para el análisis con AS OF es crear una vista que extraiga los datos necesarios de las tablas relacionadas y aplicar FOR SYSTEM_TIME AS OF a la vista, ya que esto simplifica enormemente la reconstrucción del estado completo del modelo de datos:

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
       PrInv.LocationId,
       p.ProductName,
       l.LocationName,
       PrInv.Quantity,
       p.UnitPrice,
       l.NumberOfEmployees,
       p.ValidFrom AS ProductStartTime,
       p.ValidTo AS ProductEndTime,
       l.ValidFrom AS LocationStartTime,
       l.ValidTo AS LocationEndTime,
       PrInv.ValidFrom AS InventoryStartTime,
       PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
    INNER JOIN dbo.Product AS p
        ON PrInv.ProductId = p.ProductID
    INNER JOIN dbo.Location AS l
        ON PrInv.LocationId = l.LocationID;
GO

SELECT *
FROM vw_ProductInventoryDetails
    FOR SYSTEM_TIME AS OF '2022-01-01';

En la captura de pantalla siguiente se muestra el plan de ejecución generado para la consulta SELECT. Esto ilustra que el Motor de base de datos gestiona toda la complejidad al tratar con las relaciones temporales:

Utiliza el siguiente código para comparar el estado del inventario de productos entre dos momentos (hace un día y hace un mes):

DECLARE @dayAgo AS DATETIME2 = DATEADD(DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo AS DATETIME2 = DATEADD(MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
       inventoryDayAgo.ProductName,
       inventoryDayAgo.LocationName,
       inventoryDayAgo.Quantity AS QuantityDayAgo,
       inventoryMonthAgo.Quantity AS QuantityMonthAgo,
       inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
       inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
     INNER JOIN vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
         ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

Detección de anomalías

La detección de anomalías, o detección de valores atípicos, identifica elementos que no se ajustan a un patrón esperado u otros elementos de un conjunto de datos. Puedes usar tablas temporales versionadas por sistema para detectar anomalías que ocurren periódica o de forma irregular, utilizando consultas temporales para localizar rápidamente patrones específicos. Lo que cuenta como una anomalía depende del tipo de datos que recopiles y de tu lógica de negocio.

En el ejemplo siguiente se muestra una lógica simplificada para detectar "picos" en las cifras de ventas. Supongamos que trabaja con una tabla temporal que recopila el historial de los productos comprados:

CREATE TABLE [dbo].[Product]
(
    [ProdID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] VARCHAR (100) NOT NULL,
    [DailySales] INT NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

El diagrama siguiente muestra las compras a lo largo del tiempo:

Suponiendo que durante los días habituales el número de productos comprados presenta una variación pequeña, la siguiente consulta identifica los valores atípicos aislados: muestras cuya diferencia con respecto a sus vecinos inmediatos es significativa (2 veces mayor), mientras que las muestras circundantes no presentan diferencias significativas (menos del 20 %):

WITH CTE (ProdId, PrevValue, CurrentValue, NextValue, ValidFrom, ValidTo)
AS (SELECT ProdId,
           LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
           DailySales,
           LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
           ValidFrom,
           ValidTo
    FROM Product FOR SYSTEM_TIME ALL)
SELECT ProdId,
       PrevValue,
       CurrentValue,
       NextValue,
       ValidFrom,
       ValidTo,
       ABS(PrevValue - NextValue) / CONVERT (FLOAT, (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END)) AS PrevToNextDiff,
       ABS(CurrentValue - PrevValue) / CONVERT (FLOAT, (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END)) AS CurrentToPrevDiff,
       ABS(CurrentValue - NextValue) / CONVERT (FLOAT, (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END) < 0.2
      AND ABS(CurrentValue - PrevValue) / (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END) > 2
      AND ABS(CurrentValue - NextValue) / (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END) > 2;

Dimensiones de variación lenta

Las dimensiones de almacenamiento de datos normalmente contienen datos relativamente estáticos sobre entidades como ubicaciones geográficas, clientes o productos. Sin embargo, algunos escenarios también requieren realizar el seguimiento de cambios de datos en tablas de dimensiones. Dado que las modificaciones en las dimensiones ocurren con mucha menos frecuencia, de manera impredecible y fuera del calendario regular de actualizaciones que se aplica a las tablas de hechos, este tipo de tablas de dimensiones se denominan dimensiones que cambian lentamente (SCD).

Existen varias categorías de dimensiones que cambian lentamente según cómo se conserva la historia de los cambios:

Cuando se elige una estrategia de DVL, es responsabilidad de la capa ETL, (extracción, transformación y carga) mantener la precisión de las tablas de dimensiones, para lo que normalmente se necesita código más complejo y un mantenimiento adicional.

Puedes usar tablas temporales versionadas por sistema para reducir drásticamente la complejidad de tu código, porque el historial de datos se conserva automáticamente. Dada su implementación con dos tablas, las tablas temporales son las más próximas a DVL de tipo 4. Pero como las consultas temporales solo permiten hacer referencia a la tabla actual, también puede plantearse el uso de tablas temporales en entornos donde piensa usar DVL de tipo 2.

Para convertir tu dimensión normal a SCD, puedes crear una nueva o modificar una existente para convertirla en una tabla temporal versión del sistema. Si tu tabla de dimensiones existente contiene datos históricos, crea una tabla separada y mueve allí los datos históricos y mantén las versiones actuales (reales) de las dimensiones en tu tabla de dimensiones original. A continuación, utilice la sintaxis ALTER TABLE para convertir la tabla de dimensión en una tabla temporal con versión administrada por el sistema con una tabla de historial predefinida.

El siguiente ejemplo ilustra el proceso y asume que la DimLocation tabla de dimensiones ya tiene ValidFrom y ValidTo como datatime2 columnas no anulables, que el proceso ETL llena:

• Mueve las versiones de fila cerrada a la nueva tabla de historial:

  SELECT *
  INTO DimLocationHistory
  FROM DimLocation
  WHERE ValidTo < '9999-12-31 23:59:59.99';
  GO
  

• Crear un índice de almacén de columnas en clúster, una buena opción en escenarios de almacén de datos:

  CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
      ON DimLocationHistory;
  

• Elimina versiones anteriores de DimLocation, que se convierte en la tabla actual en la configuración temporal de versionado del sistema:

  DELETE FROM DimLocation
  WHERE ValidTo < '9999-12-31 23:59:59.99';
  

• Añadir definición de periodo:

  ALTER TABLE DimLocation
      ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
  

• Habilita el control de versiones del sistema y asocia la tabla de historial a la DimLocation:

  ALTER TABLE DimLocation
      SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));
  

No necesitas código extra para mantener un SCD durante el proceso de carga del data warehouse después de crearlo.

La siguiente ilustración muestra cómo puedes usar tablas temporales en un escenario básico que involucra dos SCDs (DimLocation y DimProduct) y una tabla de hechos.

Para usar los SCD anteriores en informes, necesitas ajustar eficazmente las consultas. Por ejemplo, puede que le interese calcular la cantidad total de ventas y el promedio de productos vendidos per cápita durante los últimos seis meses. Las dos métricas necesitan la correlación de datos de la tabla de hechos y las dimensiones que podrían haber cambiado sus atributos importantes para el análisis (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

La siguiente consulta calcula correctamente las métricas requeridas:

DECLARE @now AS DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo AS DATETIME2;

SET @sixMonthsAgo = DATEADD(month, -12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
       DimLocation_History.LocationId,
       SUM(f.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
       AVG(f.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales AS f
     /* find corresponding record in SCD history in last 6 months, based on matching fact */
     INNER JOIN DimLocation FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
         ON DimLocation_History.LocationId = f.LocationId
        AND f.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
     /* find corresponding record in SCD history in last 6 months, based on matching fact */
     INNER JOIN DimProduct FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
         ON DimProduct_History.ProductId = f.ProductId
        AND f.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE f.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

Considerations

Utilizar tablas temporales versionadas por sistema para SCD es aceptable si el periodo de validez calculado en función del tiempo de transacción de la base de datos funciona para la lógica de tu negocio. Si cargas datos con un retraso significativo, el tiempo de transacción puede no ser aceptable.

De manera predeterminada, las tablas temporales versionadas por el sistema no permiten modificar los datos históricos una vez cargados (puede modificar el historial después de cambiar SYSTEM_VERSIONING a OFF). Esto podría ser una limitación en casos donde el cambio de datos históricos se produce con regularidad.

Las tablas versionadas temporalmente por sistema generan una versión de fila en cualquier cambio de columna. Si quieres suprimir nuevas versiones en un determinado cambio de columna, necesitas incorporar esa limitación en la lógica ETL.

Si esperas un número significativo de filas históricas en las tablas SCD, considera usar un índice de columnstore agrupado como opción principal de almacenamiento para la tabla de historial. Usar un índice de almacén de columnas reduce el espacio que ocupa la tabla de historial y acelera las consultas analíticas.

Reparar la corrupción de datos a nivel de fila

Puede confiar en los datos históricos de las tablas temporales versionadas por el sistema para restaurar rápidamente filas individuales a cualquiera de los estados registrados previamente. Esta propiedad de las tablas temporales es útil cuando puede localizar filas afectadas o cuando conoce la hora del cambio de datos no deseado. Este conocimiento le permite realizar reparaciones de forma eficaz sin trabajar con copias de seguridad.

Este enfoque tiene varias ventajas:

• Es posible controlar el ámbito de la reparación de manera precisa. Los registros que no se ven afectados deben permanecer en el estado más reciente, que suele ser un requisito crítico.

• La operación es eficaz y la base de datos permanece en línea para todas las cargas de trabajo que usan los datos.

• La propia operación de reparación tiene versiones. Tienes un registro de auditoría de la operación de reparación, así que puedes analizar lo que ocurrió más adelante si es necesario.

Puedes automatizar la acción de reparación con relativa facilidad. El siguiente ejemplo de código muestra un procedimiento almacenado que realiza la reparación de datos para la tabla Employee utilizada en un escenario de auditoría de datos.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord (
    @EmployeeID INT,
    @versionNumber INT = 1
)
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID
        ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999
          AND Employee.EmployeeID = @EmployeeID)

/* Update current row using N-th row version from history
(default is 1, that is, the last version) */
UPDATE Employee
    SET [Position] = h.[Position],
        [Department] = h.Department,
        [Address] = h.[Address],
        AnnualSalary = h.AnnualSalary
FROM Employee AS e
     INNER JOIN History AS h
         ON e.EmployeeID = h.EmployeeID
        AND RN = @versionNumber
WHERE e.EmployeeID = @EmployeeID;

Este procedimiento almacenado toma @EmployeeID y @versionNumber como parámetros de entrada. Por defecto, restaura el estado de la fila a la última versión desde el historial (@versionNumber = 1).

La siguiente imagen muestra el estado de la fila antes y después de la invocación del procedimiento. El rectángulo rojo marca la versión actual de la fila que es incorrecta, mientras que el rectángulo verde marca la versión correcta del historial.

EXECUTE sp_RepairEmployeeRecord
    @EmployeeID = 1,
    @versionNumber = 1;

Este procedimiento almacenado de reparación puede definirse para aceptar una marca de tiempo exacta en lugar de la versión de fila. Restaura la fila a cualquier versión que estuviera activa en el momento especificado (es decir, el momento AS OF).

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf (
    @EmployeeID INT,
    @asOf DATETIME2
)
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
    SET [Position] = History.[Position],
        [Department] = History.Department,
        [Address] = History.[Address],
        AnnualSalary = History.AnnualSalary
FROM Employee AS e
     INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
         ON e.EmployeeID = History.EmployeeID
WHERE e.EmployeeID = @EmployeeID;

Para el mismo ejemplo de datos, en la siguiente imagen se ilustra un escenario de reparación con una condición de tiempo. Se resaltan el @asOf parámetro, la fila seleccionada en el historial que estaba vigente en el momento proporcionado y la nueva versión de la fila en la tabla actual tras la operación de reparación:

La corrección de datos puede convertirse en parte de la carga de datos automatizada en el almacenamiento de datos y los sistemas de informes. Si un valor recién actualizado no es correcto, en muchos escenarios, la restauración de la versión anterior a partir de historial es una mitigación suficientemente buena. En el siguiente diagrama se muestra cómo se puede automatizar este proceso:

Contenido relacionado

• Tablas temporales
• Primeros pasos con las tablas temporales con control de versiones del sistema
• Comprobaciones de coherencia del sistema de la tabla temporal
• Creación de particiones con tablas temporales
• Consideraciones y limitaciones de las tablas temporales
• Seguridad de la tabla temporal
• Tablas temporales versionadas por el sistema con tablas optimizadas para la memoria
• Funciones y vistas de metadatos de la tabla temporal