Temporal Tables

Temporal Tables

Temporal Tables - use "AS OF" with date/time.
  In SQL Server since 2016.
  Usually table is built with ValidFrom and ValidTo.

Why temporal - Use Cases?
    1) Auditing all data changes and performing data forensics when necessary
    2) Reconstructing state of the data as of any time in the past
    3) Calculating trends over time
    4) Maintaining a slowly changing dimension for decision support applications
    5) Recovering from accidental data changes and application errors

How to create:
  CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
     ... Employee properties ...
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
  )
  WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Query Types:
   1) AS OF <date_time>
   2) FROM <start_date_time> TO <end_date_time>
   3) BETWEEN <start_date_time> AND <end_date_time>
   4) CONTAINED IN (<start_date_time>, <end_date_time>)
   5) ALL

Query Example:
  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;

your system time will be done using UTC time

Read the levels of SCDs (slowly changing dimensions) in SSIS vs PiT (point-in-time).