temporal 테이블 사용 시나리오

적용 대상: SQL Server

시스템 버전 관리 temporal 테이블은 데이터 변경 기록을 추적하는 데 필요한 시나리오에서 유용합니다. 주요 생산성 혜택을 위해 다음 사용 사례에서 temporal 테이블을 고려하는 것이 좋습니다.

데이터 감사

변경된 내용과 시기를 추적하고 언제든지 데이터 포렌식 작업을 수행해야 하는 중요한 정보를 저장하는 테이블에서 temporal 시스템 버전 관리를 사용할 수 있습니다.

Temporal 테이블을 사용하면 개발 주기의 초기 단계에서 데이터 감사 시나리오에 대해 계획하고 필요할 때 기존 애플리케이션이나 솔루션에 데이터 감사를 추가할 수 있습니다.

다음 다이어그램에서는 현재(파란색으로 표시됨) 및 기록 행 버전(회색으로 표시됨)을 포함한 데이터 샘플이 있는 Employee 테이블 시나리오를 보여 줍니다.

다이어그램의 오른쪽 부분은 시간 축의 행 버전과 SYSTEM_TIME 절이 있거나 없는 temporal 테이블에서 다양한 유형의 쿼리를 사용하여 선택한 행을 시각화합니다.

데이터 감사를 위해 새 테이블에서 시스템 버전 관리 사용

데이터 감사가 필요한 정보를 확인한 경우 데이터베이스 테이블을 시스템 버전 관리 temporal 테이블로 만듭니다. 다음 예제에서는 가상 HR 데이터베이스에서 호출된 Employee 테이블이 있는 시나리오를 보여 줍니다.

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

temporal 시스템 버전 관리 테이블을 만드는 다양한 옵션은 시스템 버전 관리 temporal 테이블 만들기에 설명되어 있습니다.

데이터 감사를 위해 기존 테이블에서 시스템 버전 관리 사용

기존 데이터베이스에서 데이터 감사를 수행해야 하는 경우 ALTER TABLE을 사용하여 비 temporal 테이블을 확장하여 시스템 버전이 되도록 합니다. 애플리케이션의 호환성이 손상되는 변경이 되지 않도록 하려면 시스템 버전 관리 temporal 테이블 만들기에서 설명한 대로 기간 열을 HIDDEN으로 추가합니다.

다음 예제에서는 가상 HR 데이터베이스의 기존 Employee 테이블에서 시스템 버전 관리 기능을 사용하도록 설정하는 방법을 보여 줍니다. 두 단계로 Employee 테이블에서 시스템 버전 관리가 가능합니다. 먼저 새 기간 열을 HIDDEN으로 추가합니다. 그런 다음 기본 기록 테이블을 만듭니다.

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

중요

datetime2 데이터 형식의 정밀도는 원본 테이블에서 시스템 버전 관리 기록 테이블의 정밀도와 동일해야 합니다.

이전 스크립트를 실행하면 모든 데이터 변경 내용이 기록 테이블에서 투명하게 수집됩니다. 일반적인 데이터 감사 시나리오에서는 관심 있는 기간에 개별 행에 적용된 모든 데이터 변경에 대해 쿼리합니다. 이 사용 사례를 효율적으로 보여줄 수 있도록 기본 기록 테이블은 클러스터형 행-저장소 B-트리로 만들어집니다.

참고 항목

설명서는 인덱스를 지칭할 때 B-트리라는 용어를 사용합니다. rowstore 인덱스에서 데이터베이스 엔진은 B+ 트리를 구현합니다. 이는 columnstore 인덱스나 메모리 최적화 테이블 인덱스에는 적용되지 않습니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

데이터 분석 수행

이전 방법 중 하나를 사용하여 시스템 버전 관리를 사용하도록 설정한 후에는 데이터 감사는 하나의 쿼리에 불과합니다. 다음 쿼리는 2021년 1월 1일과 2022년 1월 1일 사이(상한선 포함)의 일부 기간 동안 활성화되었던 EmployeeID = 1000가 포함된 Employee 테이블의 레코드에 대한 행 버전을 검색합니다.

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;

FOR SYSTEM_TIME BETWEEN...AND를 FOR SYSTEM_TIME ALL로 바꿔 해당 특정 직원에 대한 데이터 변경 내용의 전체 기록을 분석합니다.

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

기간(외부가 아님) 내에서만 활성화된 행 버전을 검색하려면 CONTAINED IN을 사용합니다. 이 쿼리는 기록 테이블만 쿼리하므로 효율적입니다.

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;

마지막으로 일부 감사 시나리오에서는 과거의 어느 시점에서든 전체 테이블이 어떻게 생겼는지 확인할 수 있습니다.

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

시스템 버전 관리 temporal 테이블에서는 UTC 표준 시간대의 기간 열에 대한 값이 저장됩니다. 하지만 데이터를 필터링하고 결과를 표시하기 위해서는 현지 표준 시간대를 사용하여 작업하는 것이 좀 더 편리할 수 있습니다. 다음 코드 예제에서는 SQL Server 2016(13.x)에 도입된 AT TIME ZONE을 사용하여 현지 표준 시간대에 지정되었다가 UTC로 변환된 필터링 조건을 적용하는 방법을 보여 줍니다.

/* Add offset of the local time zone to current time*/
DECLARE @asOf 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;

AT TIME ZONE을 사용하는 것은 시스템 버전 관리된 테이블이 사용되는 다른 모든 시나리오에 도움이 됩니다.

FOR SYSTEM_TIME이 포함된 temporal 절에 지정된 필터링 조건은 SARG-able입니다. SARG는 검색 인수를 의미하며, SARG-able은 SQL Server가 기본 클러스터형 인덱스를 사용하여 스캔 작업 대신 검색을 수행할 수 있음을 의미합니다. 자세한 내용은 SQL Server 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

기록 테이블을 직접 쿼리하는 경우 <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC' 형식으로 필터를 지정하여 필터링 조건도 SARG-able인지 확인합니다.

기간 열에 AT TIME ZONE을 적용하는 경우 SQL Server는 테이블 또는 인덱스 검사를 수행하므로 비용이 많이 들 수 있습니다. 쿼리에서 다음과 같은 유형의 조건은 피하세요.

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

자세한 내용은 시스템 버전 관리 temporal 테이블의 데이터 쿼리를 참조하세요.

지정 시간 분석(시간 이동)

시간 이동 시나리오에서는 개별 레코드의 변경 내용에 집중하는 대신 시간에 따라 전체 데이터 집합이 어떻게 변경되는지 보여 줍니다. 경우에 따라 시간 이동에는 분석하려는 몇 가지 관련 temporal 테이블이 포함되며 각 테이블은 독립적인 속도로 변경됩니다.

• 기록 및 현재 데이터의 중요한 지표에 대한 추세
• 과거의 특정 시점(어제, 한 달 전 등) "현재" 전체 데이터의 정확한 스냅샷
• 관심 있는 두 지정 시간(예: 한 달 전과 3개월 전) 간의 차이

실제로 시간 이동 분석이 필요한 시나리오는 많습니다. 이 사용 시나리오를 보여줄 수 있도록 자동 생성 기록을 사용하는 OLTP에 대해 살펴보겠습니다.

자동 생성된 데이터 기록이 있는 OLTP

트랜잭션 처리 시스템에서는 일반적으로 시간의 흐름에 따라 중요한 메트릭이 변경되는 방식을 분석할 수 있습니다. 이상적으로는 데이터의 최신 상태에 액세스할 때 대기 시간과 데이터 잠금이 최소한으로 발생되어야 하므로 OLTP 애플리케이션에서는 기록 분석으로 인해 성능이 저하되면 안 됩니다. 시스템 버전 관리된 temporal 테이블을 사용하여 전체 변경 내역을 나중에 분석할 수 있도록 현재 데이터와는 별도로 투명하게 유지하여 주 OLTP 작업에 미치는 영향을 최소화할 수 있습니다.

부하가 높은 트랜잭션 처리 작업을 위해서는 메모리 최적화 테이블이 포함된 시스템 버전 관리 temporal 테이블을 사용하는 것이 좋습니다. 이렇게 하면 현재 메모리 내 데이터와 디스크의 전체 변경 기록을 비용 효율적인 방식으로 저장할 수 있습니다.

기록 테이블의 경우 다음과 같은 이유로 클러스터형 columnstore 인덱스를 사용하는 것이 좋습니다.

• 일반적인 추세 분석은 클러스터형 columnstore 인덱스에서 제공하는 쿼리 성능의 이점입니다.

• 메모리 최적화 테이블이 있는 데이터 플러시 작업은 기록 테이블에 클러스터형 columnstore 인덱스가 있는 경우 과도한 OLTP 워크로드에서 가장 잘 수행됩니다.

• 클러스터형 columnstore 인덱스는 특히 모든 열이 동시에 변경되지 않는 시나리오에서 뛰어난 압축을 제공합니다.

메모리 내 OLTP와 함께 temporal 테이블을 사용하면 전체 데이터 집합을 메모리에 유지할 필요가 줄어들고 핫 데이터와 콜드 데이터를 쉽게 구분할 수 있습니다.

이 범주에 잘 맞는 실제 시나리오의 예로는 재고 관리 또는 통화 거래 등이 있습니다.

다음 다이어그램은 인벤토리 관리에 사용되는 간소화된 데이터 모델을 보여 줍니다.

다음 코드 예제에서는 기록 테이블에 클러스터형 columnstore 인덱스가 있는 메모리 내 시스템 버전 temporal 테이블로 ProductInventory를 만듭니다(기본적으로 생성된 행 저장소 인덱스를 실제로 대체함).

참고 항목

데이터베이스에서 메모리 최적화 테이블 만들기가 허용되어야 합니다. 메모리 액세스에 최적화된 테이블 및 고유하게 컴파일된 저장 프로시저 만들기를 참조하세요.

USE TemporalProductInventory;
GO

BEGIN
    --If table is system-versioned, SYSTEM_VERSIONING must be set 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,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (
        ProductId,
        LocationId
    )
)
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);

이전 모델의 경우 인벤토리 유지 관리를 위한 절차는 다음과 같습니다.

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
            SET @quantityIncrement = 0

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

spUpdateInventory 저장 프로시저에서는 인벤토리에 새 제품을 삽입하거나 특정 위치에 대한 제품 수량을 업데이트합니다. 비즈니스 논리는 간단하며, 테이블 업데이트를 통해 Quantity 필드를 증분/감소하여 항상 최신 상태를 정확하게 유지하는 데 중점을 둡니다. 반면 시스템 버전 관리 테이블은 다음 다이어그램에 표시된 대로 데이터에 기록 차원을 투명하게 추가합니다.

이제 최신 상태에 대한 쿼리를 네이티브 컴파일 모듈에서 효율적으로 수행할 수 있습니다.

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

EXEC [dbo].[spQueryInventoryLatestState];

시간의 흐름에 따라 데이터 변경을 분석하는 작업은 다음 예에서와 같이 FOR SYSTEM_TIME ALL 절을 사용하면 간편해집니다.

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;

다음 다이어그램은 파워 쿼리, Power BI 또는 유사한 비즈니스 인텔리전스 도구에서 이전 보기를 가져와 쉽게 렌더링할 수 있는 제품 한 개에 대한 데이터 기록을 보여 줍니다.

이 시나리오에서는 temporal 테이블을 사용하여 과거의 지정 시간을 기준(AS OF)으로 인벤토리 상태를 다시 생성하거나 다른 시간에 속해 있는 스냅샷을 비교하는 작업과 같이 다른 형식의 시간 이동 분석을 수행합니다.

이 사용 시나리오의 경우 Product 및 Location 테이블을 확장하여 temporal 테이블로 설정할 수도 있습니다. 이렇게 하면 UnitPrice 및 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));

이제 데이터 모델에는 여러 temporal 테이블이 포함되므로 AS OF 분석의 모범 사례는 관련 테이블에서 필요한 데이터를 추출하는 보기를 만들고 FOR SYSTEM_TIME AS OF를 보기에 적용하는 것입니다. 이렇게 하면 전체 데이터 모델의 상태 재구성이 크게 간소화됩니다.

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

다음 스크린샷에서는 SELECT 쿼리에 대해 생성된 실행 계획을 보여 줍니다. 이는 temporal 관계 처리의 모든 복잡성이 SQL Server 엔진에서 완전히 처리됨을 보여 줍니다.

다음 코드를 사용하여 제품 인벤토리의 상태를 두 시점(하루 전과 한 달 전) 간에 비교합니다.

DECLARE @dayAgo DATETIME2 = DATEADD (DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo 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;

이상 감지

변칙 검색(또는 이상 값 검색)은 예상되는 패턴을 준수하지 않는 항목 또는 데이터 세트의 다른 항목 ID입니다. temporal 쿼리를 활용하면 특정 패턴을 쉽게 찾을 수 있으므로 시스템 버전 관리된 temporal 테이블을 사용하여 주기적으로 또는 불규칙적으로 발생하는 변칙을 검색할 수 있습니다. 어떤 변칙은 수집하는 데이터 형식과 비즈니스 논리에 따라 달라집니다.

다음 예제에서는 "급증" 판매량을 검색하는 간단한 논리를 보여 줍니다. 구매한 제품의 기록을 수집하는 temporal 테이블을 사용한다고 가정해 봅니다.

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

다음 다이어그램은 시간에 따른 구매를 보여 줍니다.

평소에는 구매한 제품 수의 차이가 적다고 가정할 때 다음 쿼리에서는 싱글톤 이상값이 식별됩니다. 주변 샘플은 서로 차이가 크지 않은 반면에(20% 미만) 바로 인접해 있는 샘플과 비교할 때 차이가 큰(2배) 샘플이 이와 같은 이상값에 해당합니다.

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;

참고 항목

이 예제는 의도적으로 간소화되었습니다. 프로덕션 시나리오에서는 고급 통계 방법을 사용하여 일반적인 패턴을 따르지 않는 샘플을 식별할 수 있습니다.

느린 변경 차원

데이터 웨어하우징의 차원은 일반적으로 지리적 위치, 고객 또는 제품과 같은 엔터티에 대한 상대적으로 정적인 데이터를 포함합니다. 그러나 일부 시나리오의 경우 차원 테이블에서도 데이터 변경 사항을 추적해야 합니다. 차원의 수정이 예측할 수 없는 방식으로 그리고 팩트 테이블에 적용되는 일반 업데이트 일정 외에 훨씬 덜 자주 발생한다는 점을 감안할 때 이러한 유형의 차원 테이블은 SCD(느린 변경 차원)라고 합니다.

변경 기록이 유지되는 방법에 따라 느린 변경 차원 범주가 몇 가지 있습니다.

차원 유형	세부 정보
유형 0	기록이 유지되지 않습니다. 차원 특성은 원래 값을 반영합니다.
유형 1	차원 특성은 최신 값을 반영함(이전 값을 덮어씀)
유형 2	일반적으로 유효 기간을 나타내는 열이 있는 테이블의 별도 행으로 표시되는 차원 멤버의 모든 버전
유형 3	동일한 행의 추가 열을 사용하여 선택한 특성에 대한 제한된 기록 유지
유형 4	원래 차원 테이블에서 최신(현재) 차원 멤버 버전을 유지하는 동안 별도의 테이블에서 기록을 유지합니다.

SCD 전략을 선택할 때 주로 많은 코드와 복잡한 유지 관리가 필요한 차원 테이블을 정확하게 유지하는 책임은 ETL(추출-변환-로드) 계층에 있습니다.

시스템 버전 관리된 temporal 테이블을 사용하면 데이터 기록이 자동으로 유지되므로 코드의 복잡성을 획기적으로 줄일 수 있습니다. 두 테이블을 사용하는 구현 방법을 고려해볼 때 temporal 테이블은 SCD 유형 4에 가장 가깝습니다. 그러나 temporal 쿼리를 사용하면 현재 테이블만 참조할 수 있으므로 SCD 유형 2 사용을 계획하는 환경에서 temporal 테이블을 고려해볼 수도 있습니다.

일반 차원을 SCD로 변환하려면 새 테이블을 만들거나 기존 테이블을 시스템 버전 관리 temporal 테이블로 변경하면 됩니다. 기존 차원 테이블에 기록 데이터가 있는 경우 별도 테이블을 만들고 기록 데이터를 이동하여 현재(실제) 차원 버전을 원래 차원 테이블에서 유지합니다. 그런 다음, ALTER TABLE 구문을 사용하여 차원 테이블을 미리 정의된 기록 테이블이 있는 시스템 버전 temporal 테이블로 변환합니다.

다음 예제에서는 해당 프로세스에 대해 설명하고, DimLocation 차원 테이블에 ETL에서 채워지는 datetime2의 null을 허용하지 않는 열로 ValidFrom과 ValidTo가 있다고 가정합니다.

/* Move "closed" row versions into newly created history table*/
SELECT * INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

/* Create clustered columnstore index which is a very good choice in DW scenarios*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory;

/* Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

/* Add period definition*/
ALTER TABLE DimLocation
ADD PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

/* Enable system-versioning and bind history table to the DimLocation*/
ALTER TABLE DimLocation
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

SCD를 생성한 후에는 데이터 웨어하우스 로드 프로세스 중에 SCD를 유지 관리하기 위해 추가 코드가 필요하지 않습니다.

다음 그림은 SCD 2개(DimLocation과 DimProduct)와 팩트 테이블 1개가 포함된 간단한 시나리오에서 temporal 테이블을 사용하는 방법을 보여 줍니다.

보고서에서 이전 SCD를 사용하려면 쿼리를 효과적으로 조정해야 합니다. 예를 들어 지난 6개월 동안의 총 판매액과 1인당 평균 판매 제품 수를 계산할 수 있습니다. 분석에 중요한 특성을 변경했을 수도 있는 팩트 테이블과 차원에서의 데이터의 상관 관계가 두 메트릭에 모두 필요합니다(DimLocation.NumOfCustomers, DimProduct.UnitPrice). 다음 쿼리에서는 필요한 메트릭을 올바르게 계산합니다.

DECLARE @now DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo 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 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;

고려 사항

데이터베이스 트랜잭션 시간을 기반으로 계산된 유효성 검사 기간이 비즈니스 논리에 적합한 경우 SCD에 대한 시스템 버전 관리 temporal 테이블을 사용할 수 있습니다. 데이터를 로드할 때 지연 시간이 상당한 경우 트랜잭션 시간이 허용되지 않을 수 있습니다.

기본적으로 시스템 버전 관리된 temporal 테이블에서는 로딩 후에는 기록 데이터를 변경할 수 없습니다(SYSTEM_VERSIONING을 OFF로 설정한 후 기록 수정 가능). 기록 데이터를 정기적으로 변경하는 경우 제한이 있을 수 있습니다.

temporal 시스템 버전 관리 테이블은 열 변경에 대해 행 버전을 생성합니다. 특정 열 변경 시 새 버전을 표시하지 않으려면 ETL 논리에서 해당 제한 사항을 통합해야 합니다.

SCD 테이블에서 많은 수의 기록 행이 예상되는 경우 클러스터형 columnstore 인덱스를 기록 테이블에 대한 기본 스토리지 옵션으로 사용하는 것이 좋습니다. columnstore 인덱스를 사용하면 기록 테이블 공간이 줄어들고 분석 쿼리 속도가 빨라집니다.

행 수준 데이터 손상 복구

시스템 버전 관리 temporal 테이블에서 기록 데이터를 사용하여 개별 행을 이전에 캡처된 상태로 빠르게 복구할 수 있습니다. temporal 테이블의 이 속성은 영향을 받는 행을 찾거나 원하지 않는 데이터 변경 시간을 알고 있을 때 유용합니다. 이 지식을 통해 백업을 처리하지 않고도 효율적으로 복구를 수행할 수 있습니다.

이 방법의 몇 가지 장점은 다음과 같습니다.

• 복구 범위를 정확하게 제어할 수 있습니다. 영향을 받지 않는 레코드는 최신 상태로 유지되어야 하며, 종종 중요하게 요구됩니다.

• 작업이 효율적이며 데이터를 사용하는 모든 작업에 대해 데이터베이스가 온라인 상태로 유지됩니다.

• 복구 작업 자체의 버전이 지정됩니다. 복구 작업 자체에 대한 감사 내역이 있으므로 필요한 경우 나중에 발생한 작업을 분석할 수 있습니다.

복구 작업은 비교적 쉽게 자동화할 수 있습니다. 다음 코드 예제는 데이터 감사 시나리오에 사용되는 Employee 테이블에 대해 데이터 복구를 수행하는 저장 프로시저를 보여 줍니다.

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 by using N-th row version from history (default is 1 - i.e. last version) */
UPDATE Employee
SET [Position] = H.[Position],
    [Department] = H.Department,
    [Address] = H.[Address],
    AnnualSalary = H.AnnualSalary
FROM Employee E
INNER JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber
WHERE E.EmployeeID = @EmployeeID;

이 저장 프로시저는 @EmployeeID 및 @versionNumber를 입력 매개 변수로 사용합니다. 이 절차는 기본적으로 기록에서 마지막 버전으로 행 상태를 복원합니다(@versionNumber = 1).

다음 그림은 프로시저 호출 전후 행의 상태를 보여 줍니다. 빨간색 사각형은 잘못된 현재 행 버전을 표시하고 녹색 사각형은 기록에서 올바른 버전을 표시합니다.

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

이 복구 저장 프로시저는 행 버전 대신 정확한 타임스탬프를 허용하도록 정의할 수 있습니다. 이렇게 하면 행이 제공된 지정 시간에 활성 상태였던 버전으로 복원됩니다(즉, 지정 시간 기준(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;

동일한 데이터 샘플에 대해 다음 그림은 시간 조건이 포함된 복구 시나리오를 보여 줍니다. @asOf 매개 변수, 제공된 지정 시간에 실제로 있었던 기록에서 선택한 행, 그리고 복구 작업 후 현재 테이블의 새 행 버전이 강조 표시되어 있습니다.

데이터 수정은 데이터 웨어하우징 및 보고 시스템에서 자동화된 데이터 로드의 일부가 될 수 있습니다. 새로 업데이트된 값이 올바르지 않으면 여러 시나리오에서 기록에서 이전 버전을 복원하는 것이 문제를 완화시킬 수 있는 충분한 방법입니다. 다음 다이어그램은 이 프로세스를 자동화하는 방법을 보여 줍니다.

관련 콘텐츠

• temporal 테이블
• ‬시스템 버전 관리 temporal 테이블 시작
• temporal 테이블 시스템 일관성 확인
• temporal 테이블을 사용한 파티션
• temporal 테이블 고려 사항 및 제한 사항
• temporal 테이블 보안
• 메모리 최적화 테이블이 포함된 시스템 버전 관리 temporal 테이블
• temporal 테이블 메타데이터 보기 및 함수