Cenários de uso da tabela temporal

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do AzureSQL database in Microsoft Fabric

Tabelas temporais versionadas pelo sistema são úteis em cenários que exigem o rastreamento do histórico de alterações nos dados. Recomendamos que você considere as tabelas temporais nos seguintes casos de uso visando obter mais benefícios de produtividade.

Auditoria de dados

Você pode usar controle de versão temporal do sistema em tabelas que armazenam informações críticas para as quais você precisa acompanhar o que foi alterado e quando e para executar análise forense de dados em qualquer ponto no tempo.

Use tabelas temporais para planejar cenários de auditoria de dados nos estágios iniciais do ciclo de desenvolvimento. Você pode adicionar auditoria de dados a aplicações ou soluções existentes quando precisar.

O diagrama a seguir mostra uma tabela Employee com a amostra de dados, incluindo as versões de linha atual (marcada com a cor azul) e de histórico (marcada com cor cinza).

A parte direita do diagrama visualiza as versões das linhas em um eixo temporal, e as linhas que você seleciona com diferentes tipos de consulta em uma tabela temporal, com ou sem a SYSTEM_TIME cláusula.

Diagrama que mostra o primeiro cenário de Uso Temporal.

Habilitar o controle de versão do sistema em uma nova tabela para auditoria de dados

Se você identificar informações que precisem de auditoria de dados, crie tabelas de banco de dados como tabelas temporais versionadas pelo sistema. O exemplo a seguir ilustra um cenário com uma tabela chamada Employee em um banco de dados hipotético de RH:

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

Várias opções para criar uma tabela temporal versão do sistema são descritas em Criar uma tabela temporal versão do sistema.

Habilitar o controle de versão do sistema em uma tabela existente para fins de auditoria de dados

Se você precisar realizar auditoria de dados em bancos de dados existentes, use ALTER TABLE para estender tabelas não temporais para que se tornem versionadas pelo sistema. Para evitar alterações incompatíveis em seu aplicativo, adicione colunas de período como HIDDEN, conforme explicado em Criar uma tabela temporal com controle de versão do sistema.

O exemplo a seguir ilustra a habilitação do controle de versão do sistema em uma tabela Employee existente em um banco de dados de RH hipotético. Ele habilita o controle de versão do sistema na tabela Employee em duas etapas. Primeiro, novas colunas de período são adicionadas como HIDDEN. Em seguida, ele cria a tabela de histórico padrão.

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

Important

A precisão do tipo de dados datetime2 deve ser a mesma na tabela de origem e na tabela de histórico com controle de versão do sistema.

Depois de executar o script anterior, a tabela de histórico coleta de forma transparente todas as alterações de dados. Em um cenário típico de auditoria de dados, você consulta todas as alterações de dados aplicadas a uma linha individual dentro de um período de tempo de interesse. A tabela de histórico padrão é criada com uma árvore B de armazenamento por linhas clusterizada, para lidar com esse caso de uso de forma eficiente.

Note

A documentação usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o Mecanismo de Banco de Dados implementa uma árvore B+. Isso não se aplica a índices columnstore nem a índices em tabelas otimizadas para memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.

Realizar análise de dados

Depois de habilitar o controle de versão do sistema usando qualquer uma das abordagens anteriores, a auditoria de dados pode ser feita com apenas uma consulta. A consulta a seguir procura versões de linhas para registros na tabela Employee, com EmployeeID = 1000 que estavam ativos durante pelo menos parte do período entre 1º de janeiro de 2021 e 1º de janeiro de 2022 (incluindo o limite 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;

Substitua FOR SYSTEM_TIME BETWEEN...AND por FOR SYSTEM_TIME ALL para analisar todo o histórico de alterações de dados desse funcionário específico:

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

Para pesquisar versões de linha que ficaram ativas apenas durante um período (e não fora dele), use CONTAINED IN. Essa consulta é eficiente porque consulta apenas a tabela de histórico:

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 fim, em alguns cenários de auditoria, você pode querer ver como toda a tabela era em algum momento do passado:

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

As tabelas temporais com controle de versão do sistema armazenam valores para colunas de período no fuso horário UTC, mas você pode achar mais conveniente trabalhar em seu fuso horário local, tanto para filtrar dados quanto para exibir resultados. O exemplo de código a seguir mostra como aplicar uma condição de filtragem, que é especificada no fuso horário local e depois convertida para 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;

Usar AT TIME ZONE é útil em todos os outros cenários em que tabelas versionadas pelo sistema são usadas.

As condições de filtragem especificadas em cláusulas temporais com FOR SYSTEM_TIMEsão SARGable.

Note

O termo SARGable em bancos de dados relacionais refere-se a um predicado Search ARGumentable que pode usar um índice para acelerar a execução da consulta. Para obter mais informações, consulte o guia de arquitetura e design de índices do SQL Server e do SQL do Azure.

Se você consultar a tabela de histórico diretamente, certifique-se de que sua condição de filtragem também seja compatível com SARG, especificando filtros no formato <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Se você aplicar AT TIME ZONE a colunas de período, o SQL Server realizará uma varredura de tabela ou de índice, o que pode ser caro. Evite esse tipo de condição em suas consultas:

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

Para obter mais informações, consulte Consultar dados em uma tabela temporal com versão controlada pelo sistema.

Análise pontual (viagem no tempo)

Em vez de focar em mudanças em registros individuais, cenários de viagem no tempo mostram como conjuntos de dados inteiros mudam ao longo do tempo. Às vezes, a viagem no tempo inclui várias tabelas temporais relacionadas, cada uma mudando em um ritmo independente, para as quais você deve analisar:

  • Tendências dos indicadores importantes nos dados atuais e históricos
  • Instantâneo exato de todo o conjunto de dados em qualquer momento no passado ("ontem", há um mês etc.)
  • As diferenças entre dois pontos no tempo de interesse (por exemplo, um mês atrás versus três meses atrás)

Muitos cenários do mundo real exigem análise de viagem no tempo. Para ilustrar esse cenário de uso, vamos analisar o processamento de transações online (OLTP) com histórico gerado automaticamente.

OLTP com histórico de dados gerado automaticamente

Em sistemas de processamento de transações, é possível analisar como métricas importantes sofrem alterações ao longo do tempo. Idealmente, analisar o histórico não deve comprometer o desempenho da aplicação OLTP, onde o acesso ao estado mais recente dos dados deve ocorrer com latência mínima e bloqueio de dados. É possível usar tabelas temporais com controle da versão do sistema para manter o histórico completo de alterações para análise posterior, transparentemente e separadamente de dados atuais, com o mínimo de impacto sobre a carga de trabalho de OLTP principal.

Para cargas de trabalho de processamento transacional de alta intensidade no SQL Server e no Instância Gerenciada de SQL do Azure, recomendamos que você utilize tabelas temporais versionadas para o sistema com tabelas otimizadas para memória, que permitem armazenar dados atuais na memória e o histórico completo de alterações no disco de forma econômica.

Para a tabela de histórico, recomendamos que você use um índice columnstore clusterizado pelos seguintes motivos:

  • A análise de tendência típica se beneficia do desempenho de consulta fornecido por um índice columnstore clusterizado.

  • A tarefa de liberação de dados com tabelas com otimização de memória, sob carga de trabalho OLTP pesada, é melhor executada quando a tabela de histórico tem um índice columnstore clusterizado.

  • Um índice columnstore clusterizado oferece excelente compactação, especialmente em cenários em que nem todas as colunas são alteradas simultaneamente.

O uso de tabelas temporais com o OLTP em memória reduz a necessidade de manter todo o conjunto de dados em memória e permite distinguir facilmente entre dados quentes e frios.

Exemplos dos cenários reais que se enquadram bem nesta categoria são o gerenciamento de estoque e o câmbio de moedas, entre outros.

O diagrama a seguir mostra um modelo de dados simplificado usado para o gerenciamento de estoque:

Diagrama que mostra um modelo de dados simplificado usado para gerenciamento de estoque.

O exemplo de código a seguir cria ProductInventory como uma tabela temporal versionada pelo sistema na memória, com um índice columnstore clusterizado na tabela de histórico (que substitui o índice rowstore criado por padrão):

Note

Verifique se seu banco de dados permite a criação de tabelas com otimização de memória. Consulte Criando uma tabela com otimização de memória e um procedimento armazenado compilado nativamente.

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 o modelo anterior, veja como poderia ser o procedimento de manutenção de estoque:

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;

O procedimento armazenado spUpdateInventory insere um novo produto no estoque ou atualiza a quantidade de produtos para a localização específica. A lógica de negócio é simples e se concentra em manter sempre preciso o estado mais recente, incrementando/decrementando o campo Quantity por meio da atualização da tabela, enquanto as tabelas versionadas pelo sistema adicionam, de forma transparente, uma dimensão de histórico aos dados, como mostrado no diagrama a seguir.

Diagrama que mostra o uso ao longo do tempo, com o uso atual em memória e o uso histórico em um columnstore em cluster.

Agora, você pode consultar eficientemente o estado mais recente do 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];

A análise das alterações nos dados ao longo do tempo torna-se extremamente fácil com a cláusula FOR SYSTEM_TIME ALL, conforme mostrado no seguinte exemplo:

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;

O seguinte diagrama mostra o histórico de dados para um produto que pode ser renderizado facilmente com a importação da exibição anterior no Power Query, Power BI ou ferramenta de business intelligence semelhante:

Diagrama que mostra o histórico de dados de um produto.

Você pode usar tabelas temporais nesse cenário para realizar outros tipos de análise de viagem no tempo, como reconstruir o estado do inventário AS OF em qualquer momento do passado ou comparar instantâneos que pertencem a diferentes momentos no tempo.

Para esse cenário de uso, você também pode estender as Product tabelas e Location para se tornarem tabelas temporais, permitindo uma análise posterior do histórico das mudanças de UnitPrice e 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));

Como o modelo de dados agora envolve múltiplas tabelas temporais, a melhor prática para AS OF análise é criar uma visualização que extraia os dados necessários das tabelas relacionadas e aplique FOR SYSTEM_TIME AS OF à visualização, pois isso simplifica muito a reconstrução do estado de todo o modelo de dados:

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

A captura de tela a seguir mostra o plano de execução gerado para a consulta SELECT. Isso ilustra que o mecanismo de banco de dados é capaz de acomodar toda a complexidade envolvida ao lidar com relações temporais:

Diagrama que mostra o plano de execução gerado para a consulta SELECT que ilustra como o Mecanismo de Banco de Dados do SQL Server lida com toda a complexidade ao trabalhar com relações temporais.

Use o código a seguir para comparar o estado do estoque de produtos entre dois momentos no tempo (um dia atrás e um mês atrás):

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;

Detecção de anomalias

A detecção de anomalias, ou detecção de valores atípicos, identifica itens que não seguem um padrão esperado ou outros itens em um conjunto de dados. Você pode usar tabelas temporais versionadas pelo sistema para detectar anomalias que ocorrem periodicamente ou de forma irregular, utilizando consultas temporais para localizar rapidamente padrões específicos. O que conta como uma anomalia depende do tipo de dados que você coleta e da sua lógica de negócios.

O exemplo a seguir mostra a lógica simplificada para detectar "picos" em números de vendas. Vamos supor que você trabalhe com uma tabela de tempo que coleta o histórico dos produtos 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
    )
);

O diagrama a seguir mostra as compras ao longo do tempo:

Diagrama que mostra as compras ao longo do tempo.

Supondo que, em dias normais, o número de produtos comprados apresente uma pequena variação, a seguinte consulta identifica outliers únicos: amostras cuja diferença em relação às suas vizinhas imediatas é significativa (2x), enquanto as amostras circundantes não diferem significativamente (menos de 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;

Note

Este exemplo é intencionalmente simplificado. Nos cenários de produção, você provavelmente usaria métodos estatísticos avançados para identificar amostras que não seguem o padrão comum.

Dimensões que Mudam Lentamente

Dimensões em um armazém de dados normalmente contêm dados relativamente estáticos sobre entidades como locais geográficos, clientes ou produtos. No entanto, alguns cenários exigem que você acompanhe as alterações de dados também nas tabelas de dimensões. Considerando que as modificações nas dimensões ocorrem com muito menos frequência, de maneira imprevisível e fora do cronograma regular de atualizações que se aplica às tabelas de fatos, esses tipos de tabelas de dimensões são chamadas de dimensões que mudam lentamente (SCD).

Existem várias categorias de dimensões que mudam lentamente, dependendo de como a história das mudanças é preservada:

Tipo de dimensão Detalhes
Tipo 0 O histórico não é preservado. Os atributos de dimensão refletem os valores originais.
Tipo 1 Os atributos de dimensão refletem os valores mais recentes (os valores anteriores são substituídos)
Tipo 2 Toda versão do membro da dimensão representado com uma linha separada na tabela, normalmente com colunas que representam o período de validade
Tipo 3 Manter um histórico limitado para atributos selecionados usando colunas adicionais na mesma linha
Tipo 4 manutenção do histórico na tabela separada, enquanto a tabela de dimensões original mantém as versões de membro da dimensão mais recentes (atuais)

Quando você escolhe uma estratégia SCD, é responsabilidade da camada de ETL (extração, transformação e carregamento) manter as tabelas de dimensões precisas, o que geralmente exige muito código e manutenção complexa.

Você pode usar tabelas temporais com versão controlada pelo sistema para reduzir drasticamente a complexidade do seu código, pois o histórico dos dados é preservado automaticamente. Considerando sua implementação usando duas tabelas, as tabelas temporais estão mais próximas do SCD Tipo 4. No entanto, como consultas temporais permitem que você referencie apenas a tabela atual, você também pode considerar o uso de tabelas temporais em ambientes nos quais você planeja usar o SCD Tipo 2.

Para converter sua dimensão regular em SCD, você pode criar uma nova ou alterar uma existente para que ela se torne uma tabela temporal com controle de versão pelo sistema. Se sua tabela de dimensões existente contém dados históricos, crie uma tabela separada e mova os dados históricos para lá, mantendo as versões atuais (reais) das dimensões na sua tabela de dimensões original. Em seguida, use a sintaxe ALTER TABLE para converter sua tabela de dimensão em uma tabela temporal versionada pelo sistema com uma tabela de histórico predefinida.

O exemplo a seguir ilustra o processo e assume que a DimLocation tabela de dimensões já possui ValidFrom e ValidTo como datatime2 colunas não anuláveis, que o processo ETL preenche:

  • Mova as versões de linha fechadas para a nova tabela de histórico:

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

  • Crie um índice columnstore clusterizado, uma boa opção em cenários de data warehouse:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Exclua versões anteriores de DimLocation, que se torna a tabela atual na configuração temporal de versionamento do sistema:

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

  • Adicionar definição de período:

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

  • Ative a versão do sistema e vincule a tabela de histórico à DimLocation:

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

Você não precisa de código extra para manter um SCD durante o processo de carregamento do data warehouse após criá-lo.

A ilustração a seguir mostra como você pode usar tabelas temporais em um cenário básico envolvendo dois SCDs (DimLocation e DimProduct) e uma tabela de fatos.

Diagrama que mostra como usar tabelas temporais em um cenário simples envolvendo 2 SCDs (DimLocation e DimProduct) e uma tabela de fatos.

Para usar os SCDs anteriores em relatórios, é necessário ajustar adequadamente as consultas. Por exemplo, você talvez queira calcular o total de vendas e o número médio de produtos vendidos per capita nos últimos seis meses. Ambas as métricas requerem a correlação de dados da tabela de fatos e dimensões cujos atributos importantes para a análise (DimLocation.NumOfCustomers, DimProduct.UnitPrice) podem ter sofrido alterações.

A consulta a seguir calcula corretamente as métricas necessárias:

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;

Considerações

O uso de tabelas temporais versionadas pelo sistema para SCD é aceitável se o período de validade calculado com base no tempo de transação do banco de dados funcionar para a lógica do seu negócio. Se você carregar dados com um atraso significativo, o tempo de transação pode não ser aceitável.

Por padrão, tabelas temporais com controle de versão do sistema não permitem a alteração de dados históricos após o carregamento (você pode modificar o histórico depois de definir SYSTEM_VERSIONING como OFF). Isso pode ser uma limitação em casos em que a alteração de dados históricos ocorre regularmente.

Tabelas temporais versionadas pelo sistema geram uma versão da linha a cada alteração de coluna. Se você quiser suprimir novas versões em uma determinada mudança de coluna, precisa incorporar essa limitação na lógica ETL.

Se você espera um número significativo de linhas históricas nas tabelas SCD, considere usar um índice clusterizado de columnstore como principal opção de armazenamento para a tabela de histórico. O uso de um índice columnstore reduz o volume da tabela de histórico e acelera suas consultas analíticas.

Reparar dados corrompidos em nível de linha

Você pode contar com dados históricos em tabelas temporais versionadas pelo sistema para restaurar rapidamente linhas individuais para qualquer um dos estados anteriormente capturados. Essa propriedade das tabelas temporais é útil quando você é capaz de localizar linhas afetadas e/ou quando você sabe a hora da alteração de dados indesejada. Esse conhecimento permite que você execute reparos com eficiência sem lidar com backups.

Essa abordagem apresenta várias vantagens:

  • É possível controlar o escopo do reparo com muita precisão. Os registros que não são afetados precisam permanecer no estado mais recente, o que é normalmente um requisito crítico.

  • A operação é eficiente e o banco de dados permanece online para todas as cargas de trabalho usando os dados.

  • A operação de reparo em si é versionada. Você tem uma trilha de auditoria da operação de reparo, então pode analisar o que aconteceu depois, se necessário.

Você pode automatizar a ação de reparo com relativa facilidade. O exemplo de código a seguir mostra um procedimento armazenado que realiza reparos de dados para a tabela Employee usada em um cenário de auditoria de dados.

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 procedimento armazenado usa @EmployeeID e @versionNumber como parâmetros de entrada. Ele restaura o estado da linha para a versão anterior a partir do histórico (@versionNumber = 1) por padrão.

A imagem a seguir mostra o estado da linha antes e depois da invocação do procedimento. O retângulo vermelho marca a versão atual da linha que está incorreta, enquanto o retângulo verde marca a versão correta do histórico.

Captura de tela que mostra o estado da linha antes e depois da invocação de procedimento. 

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

Captura de tela que mostra a linha corrigida.

Esse procedimento armazenado de reparo pode ser definido para aceitar um carimbo de data/hora exato, em vez da versão de linha. Ele restaura a linha para qualquer versão que estivesse ativa no ponto no tempo fornecido (ou seja, AS OF ponto no tempo).

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 a mesma amostra de dados, a imagem a seguir ilustra o cenário de reparo com a condição de tempo. Destacados estão o @asOf parâmetro, a linha selecionada no histórico que estava vigente no momento fornecido, e a nova versão da linha na tabela atual após a operação de reparo:

Captura de tela mostrando o cenário de reparo com a condição de tempo.

A correção de dados pode se tornar parte do carregamento automatizado de dados em sistemas de data warehouse e relatórios. Se um valor atualizado recentemente não estiver correto, em muitos cenários, restaurar a versão anterior do histórico será mitigação suficiente. O diagrama a seguir mostra como esse processo pode ser automatizado:

Diagrama que mostra como o processo pode ser automatizado.

Conteúdo relacionado

  • Last updated on