I have configured a Log Stream task that reads from an Oracle JDE database, followed by a unidirectional task that replicates the data to Snowflake in CDC mode.
Occasionally, auditing is enabled on some source tables. This causes the tables to be renamed with the _ADT suffix, while the base tables (without this suffix) are converted into views of the corresponding audit tables.
In these cases, Qlik Replicate typically suspends the replication of the table with an error message such as: “Object ids of table 'DTA.F0401' are not correct”. I assume this is because the CDC replication task cannot replicate views, which in fact are not even selectable in the Log Stream task. At that point, we manually add the table with the _ADT suffix to the replication tasks.
We would like to manage these changes in a way that minimizes the impact on consumers that need to read these tables in Snowflake.
I was thinking of:
adding a table selection pattern to the replication tasks that automatically includes all tables with the _ADT suffix (using the %_ADT pattern under the relevant schemas and selecting the “use exact table name” option);
adding a global rule to the unidirectional task to Snowflake that renames tables with the %_ADT suffix by removing the last four characters.
This way, both the _ADT table and the base table would be replicated to the same target table in Snowflake. Moreover, the conversion of the base table into a view would have no significant impact on the replication, since the corresponding _ADT table would be automatically included in the replication instead. In any case, I assume that the task logs would still indicate that the base table is no longer available as a table.
Could this be a good solution or is there a better best practice for handling this type of situation?
Yes, your approach is reasonable and likely the cleanest solution.
Automatically select %_ADT tables in Qlik Replicate. Rename _ADT back to the original table name in Snowflake. Exclude/avoid replicating the converted Oracle view. My Milestone Card Test carefully when switching from the original table to _ADT, especially for CDC state and schema differences.
