Historization
Create and maintain historization definitions to retain the record history required by your warehouse. These guides cover the initial wizard and the detailed editor settings.
Start with the wizard when creating a definition. Use the configuration guide when reviewing column policies, filters, variables, or scripts in an existing historization.
Create and configure historization
Create a historization with Historization Wizard
Select the source and target, choose history behavior, and create a historization definition for review in the editor.
Configure historization columns, filters, variables, and scripts
Review and save an existing historization’s column rules, filters, variables, and script configuration.
Design considerations
- Avoid duplicate historization when the source already supplies reliable history that meets the reporting requirement.
- Track meaningful attribute changes. Frequently changing attributes can create unnecessary history rows; selective tracking reduces storage and processing costs.
- Allow for potential additional processing when downstream joins use snapshots.
- Maintain historization behavior through its metadata configuration so changes remain part of the generated logic.
Example of a tracked customer change
The following table sketches and SQL illustrate how a tracked customer change creates a new version.
Assume a staging table contains customer data:
stg_customer
(
customer_id,
name,
city
)
A historized table generated from it may look like this:
pst_customer_history
(
sats_id bigint,
customer_id int,
name nvarchar(100),
city nvarchar(100),
date_from datetime,
date_to datetime
)
A representative SCD Type 2 pattern is:
-- close current row when tracked attributes changed
UPDATE tgt
SET date_to = @load_ts
FROM pst_customer_history tgt
JOIN stg_customer src
ON tgt.customer_id = src.customer_id
AND tgt.date_to IS NULL
WHERE
ISNULL(tgt.name, '') <> ISNULL(src.name, '')
OR ISNULL(tgt.city, '') <> ISNULL(src.city, '');
-- insert new current row
INSERT INTO pst_customer_history
(
customer_id,
name,
city,
date_from,
date_to
)
SELECT
src.customer_id,
src.name,
src.city,
@load_ts,
NULL
FROM stg_customer src
LEFT JOIN pst_customer_history tgt
ON tgt.customer_id = src.customer_id
AND tgt.date_to IS NULL
WHERE
tgt.customer_id IS NULL
OR ISNULL(tgt.name, '') <> ISNULL(src.name, '')
OR ISNULL(tgt.city, '') <> ISNULL(src.city, '');
This pattern shows the core behavior: current rows are closed when tracked attributes change, and a new current row is inserted.
