Documentation

Incremental Query picks up all records instead of only new ones

Last update: Tue Jun 18 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

Learn how to fix the Adobe Campaign Classic issue where Incremental queries are not working as expected.

Description description

Environment

Campaign Classic

Issue/Symptoms

Incremental queries are not working as expected. Instead of only picking up new records since their last run, they are picking up all records each time like a normal query activity.

Resolution resolution

This issue is fixed in Adobe Campaign Classic 20.1.1 Release (build 9122 and onwards).

Workarounds that users can use:

Workaround 1: Stop the cleanup workflow and run it intermittently to clean up the Database and HDD until the fix is taken and available. It is not recommended if you don’t have a planned upgrade.

Workaround 2: Assume that the incremental query activity is impacted. Work around it by doing the same thing that the incremental query does by creating a persistent schema to hold the history table contents. Use a combination of query and update data activities to mimic the behaviour. This will need to be done for all the workflows requiring the incremental query.

Workaround 3:  Assume that the incremental query activity is impacted. Work around it by adding an audit field tsCreated/tsLastModified to the schema in question. Your incremental query will then be converted to a normal query activity with a where clause like tscreated< GetDate().

Workaround 4:

  1. Create a new sequence xtknewworkflowid and initialize it to something far away from the current workflowId ranges.
  2. Change the xtkworkflow schema to use this pkSequence.
  3. Ask the user to clone all the affected workflows and delete the original ones.
  4. Once the user is ready for an upgrade, remove this fix by reverting to xtknewId for the workflow creation (to avoid unwanted surprises).

Cause
The main issue is the cleanup workflow.

The incremental query workflow works this way:

  1. Maintains a history table with results from previous iterations.
  2. Fetches all the rows from the target query.
  3. Filters out all the rows present in the history table
  4. Adds the remaining results into the history table for the next iteration filtering.

The history worktable name is of the following notation:
wkfhisto<workflowid> <activityName>_

Now, for workflowIDs < 0 (for users where the xtknewid allows negative sequences), we see that it is actually:

wkfhisto<(uint)workflowid>``<activityName>_

Although this is okay for workflow execution.

So, for example, the incremental activity incremental1 of workflow ID=-1 will create a table wkfhisto4294967295_incremental1.

The thing which is missed is the CleanUp workflow.

Here, we have a code that tries to delete worktables of deleted workflows.

A dedicated code here lists all the wkfhisto tables, extracts the workflowId from their names (from the above convention), and deletes them all except the ones whose worklowIDs are found in the xtkworkflow table.

However, it misses the uint part.

So, it tries to look up a workflow with ID 4294967295 instead of casting this back to int. Since this workflow is not found, this table is deleted. Next time, when this workflow runs, the incremental query activity does not find an existing history table and creates it thinking of this as the first run ever.

recommendation-more-help
3d58f420-19b5-47a0-a122-5c9dab55ec7f