Row Level Delete Tasks

Using a Row Level Delete Task

In this tutorial we want to clean up test data that we previously created in the qa2 Environment. The Environment already contains data from multiple test cases, and we now want to remove only the data that belongs to the Research department, including all related employee information, without touching any other departments.

A Row Level Delete Task is designed exactly for this situation:

  • It removes a consistent subset of rows that together form a test case or business scenario.

  • Like a Row Level Processor Task, a Row Level Delete Task uses the Data Relation Rules from the Application Model to identify all related tables and their dependent rows. This ensures that the same consistent set of rows is selected, but instead of being copied to a Target Environment, all selected rows are deleted in place while referential integrity is preserved.

  • It works on a single Environment, there is no separate source and target, because the selected rows are not copied but deleted in place.

By using a Row Level Delete Task, we can describe the test case once (start table, start condition, relations) and let XDM determine all rows that belong to this test case. We do not need to maintain complex manual SQL scripts for each table.

In our example, the start condition selects the Research department in the qa2 Environment. Based on the configured Data Relation Rules, XDM finds all related rows, such as employees working in this department and their related records. All these rows are deleted together, while unrelated data in other departments remains untouched.

Creating the Row Level Delete Task

To create the Delete Task Template:

  1. In the left sidebar, click the menu category Tasks to expand the tasks menu. Then click the menu item Task Templates.

  2. In the tab Row Level Processing, click the button Button with an arrow pointing to the right-hand direction on the left side of Row Level Delete Task Templates to expand the list. Initially, the list may be empty.

  3. Click the button + Create. A panel titled Create Row Level Delete Task Template opens. Enter the following information:

    Name

    Delete individual departments by name

  4. Click the button Create and edit.

  5. In the top panel, select the tab Custom Parameter. Click the button Configure Custom Parameters.

  6. In the dialog window Custom Parameter Activation, click the field Custom Parameter Definition section and select Department Name. (You may have to either scroll down the list of parameters or enter "Department Name" into the field filter to see the parameter.) Then click the button Apply.

  7. In the top panel, click the tab Start Conditions. Under Row Selection Mode, select Query.

    The field Start Condition already contains a blueprint for a SELECT query. We need to complete the query by adding a valid WHERE condition. Change the contents of the field so that it looks as follows:

    SELECT ${uniqueRowIdentifier}
FROM "${startTableSchema}"."${startTableName}" "T"
WHERE "T".dept_name = '${departmentName}'

  8. Click the button Save Changes.

Now that the Task Template has been created, we can define the actual task:

  1. In the bottom panel Tasks, select the tab Tasks and click the button + Create.

  2. Enter the following information:

    Department Name

    Research

    Environment

    Select Testing HR Environment qa2 from the drop-down box

    Name

    Delete individual departments from qa2

  3. Click the button Create and edit.

  4. Optional: In the field Description in the tab Task Information, enter a description for the new task.

  5. Click the button Save Changes.

The first Row Level Delete Task is now defined. Like in Row Level Processor Tasks, in Row Level Delete Tasks you do not need to add Mapping Rules to specify the schema of the target tables. The schema name is taken from the specified Target Environment.

Executing the Row Level Delete Task

To execute your first Row Level Delete Task:

  1. In the left sidebar, click the menu category Tasks to expand the tasks menu and select the menu item Task Templates.

  2. In the tab Row Level Processing, click the button Button with an arrow pointing to the right-hand direction on the left side of Row Level Delete Task Templates to expand the list. Click the button Button with an arrow pointing to the right-hand direction next to Delete individual departments by name to expand the list of tasks. Locate the task Delete individual departments from qa2 in the panel Tasks in the tab Tasks and click the button Button with an arrow pointing to the right-hand direction displayed on the left of the task name.

  3. In the right sidebar, click the button Execute. A dialog window opens. Leave Interrupt execution unchecked and click the button Execute and view. This will schedule the task for immediate execution and switch the main view of Executed Tasks.

  4. The task runs asynchronously in the background. Every five seconds, the status is refreshed automatically. Within a few minutes the task should finish.

  5. In the top left corner, the task status should show as COMPLETE.

You have now deleted the department (Research) with all its employees and all related information, including the job title history and salary history, from the qa2 schema.

Previous section: Data Shops Next section: Task Stage Hooks