Cenários de uso de tabelas temporais

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Base de Dados SQL do Azure Azure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

As tabelas temporais com versão do sistema são úteis em cenários que exigem o histórico de acompanhamento de alterações de dados. Recomendamos que você considere tabelas temporais nos seguintes casos de uso, para obter grandes benefícios de produtividade.

Auditoria de dados

Você pode usar o controle de versão temporal do sistema em tabelas que armazenam informações críticas, para acompanhar o que mudou e quando, e para executar perícia de dados a qualquer momento.

Utilize tabelas temporais para planear cenários de auditoria de dados nas fases iniciais do ciclo de desenvolvimento. Pode adicionar auditoria de dados a aplicações ou soluções existentes quando precisar.

O diagrama a seguir mostra uma tabela Employee com o exemplo de dados, incluindo versões de linha atuais (marcadas com uma cor azul) e versões de linha históricas (marcadas com uma cor cinza).

A parte direita do diagrama visualiza as versões das linhas num eixo temporal, e as linhas que se selecionam com diferentes tipos de consulta numa tabela temporal, com ou sem a SYSTEM_TIME cláusula.

Diagrama mostrando o primeiro cenário de Uso Temporal.

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

Se identificar informações que precisam de auditoria de dados, crie tabelas de base de dados como tabelas temporais versionadas pelo sistema. O exemplo seguinte ilustra um cenário com uma tabela chamada Employee numa base de dados hipotética 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 auditoria de dados

Se você precisar executar a auditoria de dados em bancos de dados existentes, use ALTER TABLE para estender tabelas não temporais para se tornarem versionadas pelo sistema. Para evitar alterações interruptivas na sua aplicação, adicione as colunas de período como HIDDEN, conforme explicado em Criar uma tabela temporal com controlo de versões do sistema.

O exemplo a seguir ilustra a habilitação do versionamento do sistema numa tabela Employee existente em um banco de dados de Recursos Humanos hipotético. Ele permite o controle de versão do sistema na tabela Employee em duas etapas. Primeiro, novas colunas de período são adicionadas na forma de 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));

Importante

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

Depois de executar o script anterior, a tabela de histórico recolhe de forma transparente todas as alterações de dados. Num cenário típico de auditoria de dados, consulta-se todas as alterações de dados aplicadas a uma linha individual num período de tempo de interesse. A tabela de histórico padrão é criada com uma árvore B de armazenamento de linhas clusterizada, para resolver 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 armazenamento em linha, o Mecanismo de Base de Dados implementa uma árvore B+. Isso não se aplica a índices de armazenamento em colunas ou a índices em tabelas com otimização de memória. Para mais informações, consulte o guia de arquitetura e estrutura de índices do SQL Server e do 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 está a apenas uma consulta de distância. A consulta a seguir procura versões de linha para registros na tabela Employee, com EmployeeID = 1000 que estiveram ativas pelo menos por uma 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 procurar versões de linha que estavam ativas apenas dentro de um período (e não fora dele), use CONTAINED IN. Esta 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, pode querer ver como era a tabela inteira em qualquer momento do passado:

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

As tabelas temporais com 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 seguinte 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 o AT TIME ZONE é útil em quaisquer outros cenários em que são usadas tabelas versionadas pelo sistema.

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

Note

O termo SARGable em bancos de dados relacionais refere-se a um predicadocapaz de Search ARGque pode usar um índice para acelerar a execução da consulta. Para obter mais informações, consulte SQL Server e SQL do Azure index architecture and design guide.

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

Se aplicar AT TIME ZONE a colunas de período, o SQL Server executa um varrimento da tabela ou do índice, o que pode ser dispendioso. Evite este tipo de condição nas suas consultas:

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

Para obter mais informações, consulte Consultar dados numa tabela temporal com versão do sistema.

Análise pontual no tempo (retrocesso temporal)

Em vez de se focarem em alterações a registos individuais, cenários de viagem no tempo mostram como conjuntos de dados inteiros mudam ao longo do tempo. Por vezes, a viagem no tempo inclui várias tabelas temporais relacionadas, cada uma mudando a um ritmo independente, em relação às quais se pretende analisar:

  • Tendências para os indicadores importantes nos dados históricos e atuais
  • Instantâneo exato de todos os dados "a partir de" qualquer ponto no tempo no passado (ontem, um mês atrás, etc.)
  • Diferenças entre dois pontos no tempo de interesse (há um mês vs. há três meses, por exemplo)

Muitos cenários reais exigem análise de viagens no tempo. Para ilustrar este cenário de utilização, vejamos o processamento de transações online (OLTP) com histórico gerado automaticamente.

OLTP com histórico de dados gerados automaticamente

Em sistemas de processamento de transações, você pode analisar como as métricas importantes mudam 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. Você pode usar tabelas temporais com versão do sistema para manter de forma transparente o histórico completo de alterações para análise posterior, separadamente dos dados atuais, com um impacto mínimo na carga de trabalho OLTP principal.

Para cargas de trabalho intensas de processamento transacional no SQL Server e no Azure SQL Managed Instance, recomendamos que utilize tabelas temporais com controlo de versões pelo sistema e tabelas otimizadas para memória, que lhe permitem armazenar os dados atuais na memória e o histórico completo das alterações no disco de forma economicamente eficiente.

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

  • A análise típica de tendências beneficia-se do desempenho de consultas fornecido por um índice de armazenamento em colunas clusterizado.

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

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

A utilização de tabelas temporais com 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 de cenários do mundo real que se encaixam bem nesta categoria são a gestão de estoque ou a negociação de moedas, entre outros.

O diagrama seguinte mostra um modelo de dados simplificado utilizado para a gestão de inventário:

Diagrama mostrando o modelo de dados simplificado usado para gerenciamento de estoque.

O exemplo de código seguinte cria ProductInventory como uma tabela temporal com controlo de versões do sistema na memória, com um índice columnstore agrupado na tabela de histórico (que substitui o índice rowstore criado por defeito):

Note

Certifique-se de que seu banco de dados permite a criação de tabelas com otimização de memória. Consulte Criando uma tabela Memory-Optimized 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, é assim que o procedimento para manter o estoque pode parecer:

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 inventário ou atualiza a quantidade do produto para o local específico. A lógica de negócio é simples e centra-se em manter sempre exato o estado mais recente, incrementando/decrementando o campo Quantity por meio da atualização da tabela, enquanto as tabelas com controlo de versões do sistema adicionam, de forma transparente, uma dimensão de histórico aos dados, conforme ilustrado no diagrama seguinte.

Diagrama mostrando o uso temporal com o uso atual In-Memory e o uso histórico em um columnstore clusterizado.

Agora, 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];

Analisar as alterações de dados ao longo do tempo torna-se fácil com a cláusula FOR SYSTEM_TIME ALL, conforme mostrado no exemplo a seguir:

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 diagrama a seguir mostra o histórico de dados de um produto que pode ser facilmente renderizado importando o modo de exibição anterior no Power Query, Power BI ou ferramenta de business intelligence semelhante:

Diagrama mostrando o histórico de dados de um produto.

Pode usar tabelas temporais neste cenário para realizar outros tipos de análise de viagens 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 este cenário de utilização, também pode estender as Product tabelas e Location para se tornarem tabelas temporais, permitindo uma análise posterior do histórico das alterações 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 envolve agora várias tabelas temporais, a melhor prática para a análise AS OF é criar uma vista que extraia os dados necessários das tabelas relacionadas e aplicar FOR SYSTEM_TIME AS OF à vista, pois isso simplifica bastante 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 lida com toda a complexidade ao lidar com relações temporais:

Diagrama mostrando o plano de execução gerado para a consulta 'SELECT', ilustrando que o Mecanismo de Banco de Dados do SQL Server lida com toda a complexidade ao lidar com relações temporais.

Use o seguinte código para comparar o estado do inventário de produtos entre dois momentos no tempo (há um dia e um mê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;

Deteção de anomalias

A deteção de anomalias, ou deteção de valores atípicos, identifica itens que não seguem um padrão esperado ou outros itens num conjunto de dados. Pode utilizar tabelas temporais com controlo de versão do sistema para detetar anomalias que ocorrem periodicamente ou de forma irregular, recorrendo a consultas temporais para localizar rapidamente padrões específicos. O que conta como uma anomalia depende do tipo de dados que recolhe e da lógica do seu negócio.

O exemplo a seguir mostra uma lógica simplificada para detetar "picos" nos números de vendas. Vamos supor que você trabalhe com uma tabela temporal que coleta o histórico dos produtos adquiridos:

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 mostrando as compras ao longo do tempo.

Assumindo que durante os dias normais o número de produtos comprados tem uma pequena variância, a seguinte consulta identifica outliers singulares: amostras cuja diferença em relação aos seus vizinhos imediatos é duas vezes maior, 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 em mudança lenta

As dimensões no data warehousing normalmente contêm dados relativamente estáticos sobre entidades, como localizações geográficas, clientes ou produtos. No entanto, alguns cenários exigem que você controle as alterações de dados em tabelas de dimensão também. Dado que as modificações nas dimensões ocorrem muito menos frequentemente, de forma imprevisível e fora do calendário regular de atualizações que se aplica às tabelas de factos, este tipo de tabelas de dimensões são chamadas 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 A história não é preservada. Os atributos de dimensão refletem valores originais.
Tipo 1 Os atributos de dimensão refletem os valores mais recentes (os valores anteriores são substituídos)
Tipo 2 Cada versão do membro da dimensão representada com uma linha separada na tabela, geralmente com colunas que representam o período de validade
Tipo 3 Mantendo histórico limitado para atributos selecionados usando colunas extras na mesma linha
Tipo 4 Manter o histórico numa tabela separada enquanto a tabela original de dimensão mantém as versões mais recentes (atuais) dos membros da dimensão.

Quando você escolhe uma estratégia SCD, é responsabilidade da camada ETL (Extract-Transform-Load) manter as tabelas de dimensões precisas, o que geralmente requer um código mais complexo e manutenção extra.

Pode usar tabelas temporais versionadas pelo sistema para reduzir drasticamente a complexidade do seu código, uma vez que o histórico dos dados é preservado automaticamente. Dada a sua implementação utilizando dois quadros, os quadros temporais estão mais próximos do SCD de Tipo 4. No entanto, como as consultas temporais permitem que você faça referência apenas à tabela atual, você também pode considerar tabelas temporais em ambientes onde planeja usar o SCD Tipo 2.

Para converter a sua dimensão regular para SCD, pode criar uma nova ou alterar uma existente para se tornar numa tabela temporal versão do sistema. Se a sua tabela de dimensões existente contiver dados históricos, crie uma tabela separada e mude 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 a sua tabela de dimensões numa tabela temporal com versão do sistema e com uma tabela de histórico previamente definida.

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

  • Mover as versões de linhas 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 em cluster, que é uma boa escolha em cenários de armazém de dados:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Apague as 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 o controlo de versões do sistema e associe a tabela de histórico à DimLocation:

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

Não precisas de código extra para manter um SCD durante o processo de carregamento do data warehouse depois de o criares.

A ilustração seguinte mostra como pode usar tabelas temporais num cenário básico envolvendo dois SCDs (DimLocation e DimProduct) e uma tabela de factos.

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

Para usar os SCDs anteriores nos relatórios, é necessário ajustar eficazmente as consultas. Por exemplo, talvez você queira calcular o valor total das 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 que podem ter alterado seus atributos importantes para a análise (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

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;

Considerations

A utilização de tabelas temporais versionadas pelo sistema para implementar SCD é aceitável se o período de validade calculado com base no momento da transação na base de dados se adequar à lógica do seu negócio. Se carregar dados com um atraso significativo, o tempo de transação pode não ser aceitável.

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

As tabelas versionadas temporalmente pelo sistema geram uma versão da linha sempre que ocorre uma alteração em qualquer coluna. Se quiseres suprimir novas versões numa determinada alteração de coluna, tens de incorporar essa limitação na lógica ETL.

Se 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 espaço ocupado pela tabela de histórico e acelera suas consultas analíticas.

Reparar corrupção de dados em nível de linha

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

Esta abordagem tem várias vantagens:

  • Você pode controlar o escopo do reparo com precisão. Os registros que não são afetados precisam permanecer no estado mais recente, o que geralmente é um requisito crítico.

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

  • A operação de reparo em si é versionada. Tem um registo de auditoria para a operação de reparação, por isso pode analisar o que aconteceu mais tarde, se necessário.

Pode automatizar a ação de reparação com relativa facilidade. O exemplo de código seguinte mostra um procedimento armazenado que realiza reparação de dados para a tabela Employee utilizada num 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 recebe @EmployeeID e @versionNumber como parâmetros de entrada. Restaura o estado da linha para a versão anterior a partir do histórico (@versionNumber = 1) por defeito.

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

Captura de tela mostrando o estado da linha antes e depois da chamada do procedimento. 

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

Captura de tela mostrando a linha corrigida.

Este procedimento armazenado de reparo pode ser definido para aceitar um carimbo de data/hora exato em vez de versão de linha. Ele restaura a linha para qualquer versão que estava ativa para o point-in-time fornecido (ou seja, AS OF point-in-time).

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 um cenário de reparo com uma condição de tempo. Destacam-se o @asOf parâmetro, a linha selecionada no histórico que estava atual no momento fornecido, e a nova versão da linha na tabela atual após a operação de reparação:

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

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

Diagrama mostrando como o processo pode ser automatizado.

Conteúdo relacionado

  • Last updated on