add filter to CDC

[david_lange]

Looking for recommendation, how to do an incremental load of a large table from oracle to snowflake.

We are using qlik replicate version 2022.11.0.208

I would like to performe a one-time full load with daily incremental.

The incremental load would use a WHERE clause to select changed rows.

Select'ing only rows that have changed or are new within the last day. I am using the Oracle coalesce function to return the first not null date, then checking if its new within the last day.

      where coalesce(last_updated, created_date) > sysdate -1

I am struggling to figure out how to enter this where clause in the:  "Table Settings" | "Filter" option on QLIK replicate.

Please advise on the best approach.

Thanks

-Dave

[Dana_Baldwin · Accepted solution]

Hi @David ,

Here is a link to a knowledge article on how to use filtering in Qlik Replicate. The pass through filter is for full load only, but the record selection method also works with CDC:

Filter for last 90 days of data in Qlik Replicate - Qlik Community - 1880761

Another option you might consider if it meets your use case, is to simply resume the task once per day when you want the new records applied. Qlik Replicate will read the source redo / archive logs and apply any updates/inserts/deletes since it last ran, this is the default behavior when change processing is enabled. One downside to this approach is it will apply changes until all are applied or until you stop the task again - so if you want it to only apply changes up to a certain point then this won't work for you.

Thanks,

Dana

[suvbin]

Does record selection works for full load + cdc task.

[john_wang]

Hi @suvbin ,

Yes, I ever summarized for you in article Pass thru filter.

Hope it helps.

John.

[Heinvandenheuvel]

Hmm, it appears you do NOT want to use the CDC function. That seems odd.

CDC could collect changes ongoing potentially reducing the load on the source. I wrote potentially because you might only be interested in a smaller subset of tables form the source and scanning those may be more efficient thank monitoring the redo/arch log all day.

I could also see that one desires a stable consistent daily snapshot versus ongoing changes during the day.

Please consider using CDC to a helper table on target with 'apply changes' and error handling set to ignore duplicates and insert if not exist on update.

Or use the store changes method to __ct tables.

For either method run a daily script on target to move from helper/__CT tables to main tables deleting/truncating/recreating helper tables as you see fit.

Hein.