How to replicate Store Changes headers in target tables without changing field names
Hello everyone,
I'm trying to replicate specific headers defined in the Store Changes option directly into the target tables in Snowflake, in addition to their presence in the __CT (Change Tables). Specifically, I’d like to have the Store Changes headers (e.g., header__change_seq, header__timestamp) added as new columns to the target tables. These new columns should have the same names of the headers defined for the Change Tables.
I tried using Global Rules within the task to add these headers as new columns, referencing their values from the Expression Builder (e.g., $AR_H_TIMESTAMP). However, when the task starts, the tables are suspended because Qlik Replicate attempts to create the Change Tables with duplicate fields, as these headers are already part of the Change Tables structure.
Is it possible, and if so, how, to filter out the Change Tables when applying Global Rules? This would allow the headers to be added as new columns exclusively to the target tables and not to the Change Tables.
I’ve found a few workarounds, but they are not without issues. I’m listing them below for the sake of clarity:
1st workaround: First, I disable the Global Rules. Then I start a Full Reload of the task with the “Keep old changes and store new changes in existing change table” option enabled in Store Changes. After that, I enable the Global Rules and start another Full Reload. In this case the headers are added to the target tables and no error is encountered for the Change Tables since they have been already created.
Problems:
At least Two full Reloads are needed.
The addition of new tables in the replication task requires careful management of the Global Rules and multiple table reloads.
2nd workaround: First, I remove the headers from the Change Tables by unchecking them in the Store Changes tab. Then I enable the Global Rules for all tables (including the Change Tables).
Problems:
The Change Tables are defined without primary key. This may impact the replication performance.
Tables cannot contain LOBs.
I’m also adding that our setup involves an Oracle source database and a Snowflake target.
I'm not sure why you want to add those special header columns to the target table with the same name. Would you consider creating a view to join these two tables instead?
If you must add those special columns to the target table, consider using your second workaround and then manually adding a primary key to the CT table. However, please note that the primary key constraint is not enforced for Snowflake standard tables.
From my understanding, a primary key is required for LOB columns because Qlik Replicate may need it to retrieve the LOB data from the source table, but it should not rely on the primary key of the CT.
Additionally, you need to change the "Change Table Creation" so that the CT table won't be recreated.
I have never heard of other customers using it this way, so you will need to test it thoroughly.
Regards, Desmond
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
I'm not sure why you want to add those special header columns to the target table with the same name. Would you consider creating a view to join these two tables instead?
If you must add those special columns to the target table, consider using your second workaround and then manually adding a primary key to the CT table. However, please note that the primary key constraint is not enforced for Snowflake standard tables.
From my understanding, a primary key is required for LOB columns because Qlik Replicate may need it to retrieve the LOB data from the source table, but it should not rely on the primary key of the CT.
Additionally, you need to change the "Change Table Creation" so that the CT table won't be recreated.
I have never heard of other customers using it this way, so you will need to test it thoroughly.
Regards, Desmond
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
