Copy with Icebox and delete target

Overview

This guide describes two example workflows that combine a Row Level Icebox with a Row Level Delete Task to implement a safe subset refresh.

In this guide, the first workflow exists only to create an Icebox Generation that can be used by the refresh workflow. In a real project, this To Icebox workflow typically runs at the start of a test cycle, after important business events, or as a regular backup. The refresh workflow can be executed many times later, even long after the original backup was taken.

First, a subset of the data is copied into an Icebox Generation. The rows that belong to this subset are determined by the start condition of the Row Level To Icebox Task and by additional data, such as a list of IDs to be copied.

When restoring an Icebox Generation, a Row Level From Icebox Task identifies the Icebox Generation to be restored. It reads the start condition and the additional information stored in the Icebox Generation.

A Row Level Delete Task then uses this information to clear the target environment. It searches for data that matches the same start condition and deletes exactly this data.

After that, a second Row Level From Icebox Task with fill mode DELETE is used. This task takes the data rows from the Icebox Generation and deletes exactly these rows from the target environment if they still exist.

In the last step, after the target environment has been cleared, the data from the Icebox Generation is written into the target environment.

Combining a Row Level Delete Task and a Row Level From Icebox Task with fill mode DELETE ensures that:

all data in the target environment that can be found for the specified start condition is deleted,
additional related data that was created later in the target environment and is still directly linked to the Icebox data is also deleted,
data from the Icebox Generation is deleted directly in the target environment, even if it has lost its relation to the original start condition.

Resulting Delete Behavior

The Icebox Generation acts as the fixed reference point for the cleanup.

All business objects that are part of the Icebox Generation itself are deleted in the target environment and then reloaded from the Icebox.
Additional data in the target environment that is not part of the Icebox Generation, but is directly related to these Icebox objects, is also deleted. This includes, for example, dependent records, child objects, or linked test cases that were created later. This data is not reloaded, because it was never stored in the Icebox Generation.
Data in the target environment that has no relationship to the Icebox Generation remains untouched.

This behavior prevents typical side effects of a blind reload, such as duplicated records, outdated relationships, or partially overwritten test cases, while still protecting completely unrelated data in the target environment.

Use Case

The use case for this example is a safe refresh of test data for a specific set of employees.

Source: A production environment with the full data set.
Target: A test environment with a large data set where a specific employee subset needs to be refreshed.

At the beginning of a regression phase, the selected employee data is stored in an Icebox Generation.

Before the next regression phase, the team wants to:

reuse exactly the same employees from the snapshot, and
restore a clean and reproducible state for this subset in the test environment.

Simply loading the Icebox Generation on top of the current test data is risky because existing target data may cause duplicates, outdated relations, or inconsistent test results.

Prerequisites

For this example, the following configuration is assumed.

Application Models

Two application models are available:

Employees, version V1
Department, version V1

They use the tables from the tutorial sample environment.

Environments

Two environments reference these application models:

ProductionCRM — source environment with the full data set.
TestingCRM — target environment where the subset will be refreshed.

Custom Parameter

A custom parameter collects the list of employees to copy.

The parameter is used in:

the Row Level To Icebox task,
the Row Level From Icebox task with fill mode DELETE,
the Row Level Delete task,
the workflow.

The parameter is attached as a property to the relevant task templates so that its value can be stored in and restored from the Icebox Generation.

Display name

Employee ID

variableName

employee

Type

COLLECTION

Option entry connection

Sample production database

Option entry expression

SELECT
  first_name || ' ' || last_name || ' (' || emp_no || ')' AS name,
  CAST(emp_no AS VARCHAR) AS value
FROM production.employees
ORDER BY emp_no
LIMIT 100

Dynamic option entry

true

The option entry query creates a value list where the display name shows the employee name and ID. The value is the emp_no that is later used in the start condition.

Steps

Step 1: Create the Row Level To Icebox Task

Create a Row Level To Icebox task template that extracts the selected employees and their related data from ProductionCRM and stores the result in an Icebox Generation.

Settings:

Task Template Name: XDM Example to Icebox
Source environment: ProductionCRM
Start table: production.employees
Row selection mode: QUERY
Custom Parameter: Employee ID

Example start condition:

SELECT ${uniqueRowIdentifier}
FROM "${startTableSchema}"."${startTableName}" "T"
WHERE "T".emp_no IN (<#list employee?replace("[","")?replace("]","")?split(",") as x>'${x?trim}'<#sep>, </#sep></#list>)

Step 2: Create the Row Level From Icebox Task with Insert Fill Mode

Create a Row Level From Icebox task template that restores data from the Icebox Generation into TestingCRM.

Settings:

Task Template Name: XDM Example from Icebox
Target environment: TestingCRM
Fill mode: Insert and on key collision discard source rows
Custom Parameter: Employee ID, attached but left empty
Row Level To Icebox Task Template: XDM Example to Icebox

Step 3: Add the ExtractIceboxGenerationInfo Task Stage Hook

Add a Groovy Task Stage Hook to the Row Level From Icebox task.

The hook reads all parameters from the current Icebox Generation and writes them to the task properties with the prefix gen_.

The workflow can then access these values, for example with:

load.get('gen_employee')
load.get('gen_startCondition')

Hook settings:

Task stage hook: ExtractIceboxGenerationInfo
Stage: Stage 1
Hook phase: post
Active: true

Hook code:

if (generation == null) {
    println "Hook was called without generation"
    return
}

def parameterNames = generation.keySet()
        .collect { it.toString() }

println "Using generation keys: ${parameterNames}"

generation.entrySet().each { entry ->
    def parameterName = entry.key.toString()
    def value = entry.value
    def targetValue = String.valueOf(value)

    properties.put("gen_" + parameterName, targetValue)

    println "Extract generation parameter: ${parameterName} = ${targetValue}"
}

Step 4: Create the Row Level Delete Task

Create a Row Level Delete task template that deletes all target data matching the original Icebox selection.

Settings:

Task Template Name: Search and Delete from Target
Environment: TestingCRM
Start table: qa1.employees
Row selection mode: QUERY
Custom Parameter: Employee ID, attached but left empty

The task uses the startCondition and employee values from the Icebox Generation.

The Row Level Delete task:

deletes rows for the selected employees in the start table,
follows modeled relations,
deletes dependent child rows in the correct order,
preserves referential integrity.

Step 5: Create the Row Level From Icebox Task with Delete Fill Mode

Create another Row Level From Icebox task template that deletes exactly the rows stored in the Icebox Generation from the target environment.

Settings:

Task Template Name: Use Content from Icebox and Delete
Target environment: TestingCRM
Fill mode: DELETE
Row Level To Icebox Task Template: XDM Example to Icebox

This task does not search relations. It deletes exactly the rows stored in the Icebox Generation if they exist in the target environment.

This is useful for removing residual data that may no longer be related to the original start condition.

Step 6: Create the Icebox Generation Workflow

Create a workflow that runs the To Icebox task and stores the selected employee subset in an Icebox Generation.

// Backup subset to Icebox
TaskTemplateRunner()
    .taskTemplate('XDM Example to Icebox')
    .taskNamePattern('Workflow of ${taskTemplate.displayName} at ${.now?iso_local}')
    .parameter('employee', workflow.employee)
    .run()

Step 7: Create the Refresh Workflow

Create a second workflow that cleans the target environment and reloads the Icebox data.

Workflow logic:

Run XDM Example from Icebox, Stage 1 only.
Let the hook extract the Icebox Generation parameters.
Run Search and Delete from Target using the extracted parameters.
Run Use Content from Icebox and Delete with fill mode DELETE.
Resume the original restore execution and load the data into the target environment.

// 1. Restore from Icebox, Stage 1 only.
// The hook extracts employee and startCondition from the Icebox Generation.
def load = TaskTemplateRunner()
    .taskTemplate('XDM Example from Icebox')
    .taskNamePattern('Workflow of ${taskTemplate.displayName} at ${.now?iso_local}')
    .stages('Stage1')
    .run()

// 2. Delete data in target using parameters from Icebox Generation
TaskTemplateRunner()
    .taskTemplate('Search and Delete from Target')
    .taskNamePattern('Workflow of ${taskTemplate.displayName} at ${.now?iso_local}')
    .parameter('startCondition', load.get('gen_startCondition'))
    .parameter('employee', load.get('gen_employee'))
    .run()

// 3. Delete residual rows that are stored in the Icebox Generation
TaskTemplateRunner()
    .taskTemplate('Use Content from Icebox and Delete')
    .taskNamePattern('Workflow of ${taskTemplate.displayName} at ${.now?iso_local}')
    .run()

// 4. Resume the original restore execution and load the data
def execution = load.get('execution') ?: load
TaskRunner()
    .execution(execution)
    .stages('Stage4')
    .run()

Benefits

This workflow pattern provides a safe and reproducible subset refresh.

The Icebox Generation acts as the fixed reference point for the cleanup.

The combination of Row Level Delete and From Icebox with fill mode DELETE ensures that:

all target data matching the original start condition is deleted,
additional related data created later in the target environment is also removed,
rows stored in the Icebox Generation are deleted directly from the target if they still exist,
unrelated data in the target environment remains untouched,
the subset can be restored in a clean and reproducible state.

This prevents common issues such as:

duplicated records,
outdated relationships,
partially overwritten test cases,
inconsistent test results,
side effects from blindly loading data on top of existing target data.

Conclusion

Use this workflow pattern when a selected subset of test data must be refreshed safely from an Icebox Generation.

The approach first removes existing and related target data, then reloads the stored Icebox data into the cleaned target environment.

For this guide, the standard structure is useful because it separates:

the purpose and use case,
the required setup,
the implementation steps,
the resulting benefits.

The current detailed structure does not need to be discarded completely. It can be kept as subsections inside the standard Steps section.