SCD Type 2 History Lost in Qlik Compose after Full Load (Source Table Re-creation Scenario)

[korsikov]

Hello everyone,

Video Demonstration: https://youtu.be/jA1dnwi--UQ

Problem Overview

I am encountering an issue where accumulated SCD Type 2 history in the Data Warehouse (DWH) is completely wiped out after performing a Full Load in Qlik Compose. This happens in a specific scenario where the source system drops and recreates tables during software updates, changing the Object ID and requiring a re-sync.

The Scenario

• Source: MS  SQL Server.  Table w/o PK, Index avalable - use MS-CDC

  USE [mysource]
  GO
  /****** Object:  Table [dbo].[t1]    Script Date: 30.12.2025 15:33:52 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[t1](
  [key_id] [nchar](10) NOT NULL,
  [index] [nvarchar](50) NULL,
  [data] [nvarchar](255) NULL
  ) ON [PRIMARY]
  GO

• CDC/Ingestion: Qlik Replicate with Global Transformation rules to capture Commit Timestamp and Operation Type. and add  two field
  Operation 
  CASE
  WHEN $AR_H_STREAM_POSITION = ''
  THEN NULL
  ELSE operation_indicator("d", "u", "i" )
  END
  OperationDateTime
  
  CASE WHEN
  operation_indicator("Y","Y","Y") = 'Y'
  THEN
  $AR_H_COMMIT_TIMESTAMP
  ELSE NULL END
  
  
  

• DWH Automation: Qlik Compose with a field (e.g., data) set to History Type 2 to track changes over time.

Steps to Reproduce

1. Initial Setup: Perform an initial full load to DWH. After that make  several updates (e.g., update2, update3, update4) to build a history and run CDC task in Qlik Compose  of the record in the DWH.

2. Verify History: At this stage, the DWH correctly shows multiple historical rows with valid From Date and To Date intervals for value in data

3. Source Change: Simulate a source software update where the table is renamed/recreated, resulting in a new Object ID.

4. Full Load: Re-run the Full Load task in Qlik Compose to synchronize with the "new" source table.
   
   
   

The Issue (Actual Result)

After the Full Load finishes, all intermediate historical records (the update versions) are deleted from the DWH table. Only the very first record (initial load) and the latest record from the recent Full Load remain. All historical context in between is lost.

Questions for the Community

1. Is this "history wiping" expected behavior during a Full Load for SCD Type 2 tables in Qlik Compose?

2. How can I perform a Full Load from a recreated source table while preserving the existing historical rows already stored in the DWH?

3. Are there specific History Settings or Task Settings in Compose that allow "appending" or "merging" new Full Load data into existing Type 2 history without deleting intermediate states?

[TimGarrod · Accepted solution]

ok - so my suggestion was backwards.  Sorry - doing this on text and not in person.   So all the header timestamps are using local time - so you should set Compose to use local server time (not UTC).

[korsikov]

thanks a lot for attention to my issue. I will check everything again. the OperationDatetime in feature. I think it's will be fixed anyway. 

unfortunalty problem is not solved. 

it's chagnes in t1_ct table

this table [TDWH_t1_S01] is after Compose_CDC task 

run reload table in Replicate and after that immediately run Compose Full_Load Task with handle dublicate. 

and what i see now again. I see only FullLoad data. all update between Replicate FullLoad are missed.  Only data what loaded by FullLoad only with handle Dublicate enabled. 

Note. FD and TD time now in UTC 

i think i miss something important. 

[korsikov]

Reset my DWH project and Try again. 
It's works only in this way. 
If I want to make FullReload table in replicate i need. 
Stop relicate CDC task. 
Run Compose_CDC task 
Make ONLY Reload table in Replicate. CDC task in replicate must be stoped. 

Run Compose_FullLoad task with handle dublicate. 
After that resume Replicate CDC task
And run Compose_CDC TASK

if Replicate CDC task catch any update after Fullreload  - Compose_FullLoad task with handle dublicate load lasted data in DWH. And After Compose_CDC TASK i see uncorrect date 
see to FD-TD for abc3 value 
read - after Compose_FullLoad task with handle dublicate 
green After Compose_CDC TASK

and here is correct order in ct table 

[korsikov]

@TimGarrod 
Thanks a lot for your attention to my case. I'm check on two enviroment from scatch. Yes - my root problem was header timestamps are using local time - so I should set Compose to use local server time