Description:

This step marks records within an ETL dataset as pending deletion.

The Mark Records to Delete ETL step flags the current record for deletion during ETL processing. Rather than immediately removing the record from the dataset, the step marks it so that subsequent ETL components or destination connectors can perform the actual delete operation when synchronizing with the destination system.

This step is commonly used during data synchronization workflows where records that no longer exist in the source system must be removed from the destination database, application, or repository.

Because the step requires no user-configurable properties, it can be easily inserted into ETL pipelines wherever records need to be identified for deletion.

The step supports:

• Marking records for deletion
• Integration with ETL synchronization workflows
• Record lifecycle management
• Automated data cleanup
• Destination delete operations
• Integration with FlowWright ETL pipelines

This step can be used for:

• Data synchronization
• Database cleanup
• Incremental ETL processing
• Master data management
• Repository synchronization
• Legacy system migration
• Business process automation

Usage:

The Mark Records to Delete step is typically placed after data comparison or validation logic identifies records that should no longer exist in the destination system.

The ETL pipeline flags matching records for deletion, allowing later destination components to process the delete operation during synchronization.

A typical ETL workflow might look like this:

Once records have been marked for deletion, later ETL steps can:

• Synchronize destination databases
• Remove obsolete records
• Generate synchronization reports
• Log deleted records
• Audit data cleanup operations
• Continue ETL processing

The ETL pipeline should already have determined which records require deletion before this step executes.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsMarkRecordsToDeletetTransformDef” example.          

• Create a new ETL definition named “clsMarkRecordsToDeletetTransformDef” and open it in designer mode. 
• Drag “clsInputCSV, clsTransformCase, clsMarkRecordsToDeleteTransform, and clsOutputMSSQLDB” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Click the "clsInputCSV" step to configure its "Required" properties. Enter the path to the input CSV file. Select the data schema from the drop-down list that matches the SQL result set. Click the Save button. Click here to learn about ETL Data Schema Designer.

• The data schema is provided here for reference. 

• Click the "clsTransformCase" step to configure its "Required" properties. Enter the step name. Click the button to configure the case transform list. Then click the Save button. 

• Click the button to configure the case transform list. The pop-up window opens for configuration. Click the Add Row (+) button to insert an empty row. Select the column and case type from the drop-down list. Click the Save button. Click the Add Row button to insert multiple columns for case transform. 

• Click the "clsMarkRecordsToDeleteTransform" step to configure its "Required" properties. Enter a name for the step. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Click the "clsOutputMSSQLDB" step to configure its "Required" properties. Provide a name for the step. Select the SQL connection from the drop-down list. Specify the table name to create and store in the connection's database. Select “Yes” to create a new table. Select the primary key column from the drop-down list. Click the Save button. 

• Select the database operation from the drop-down list. You may configure the step to perform “Insert, Update, Insert and Update, Delete, and Replace All”. You must configure the step to perform the Delete function to remove the records marked for deletion. Click the Save button. 

• Save the ETL Definition. Execute the ETL process. When the ETL pipeline reaches the Mark Records to Delete step, FlowWright flags the current record as pending deletion. The ETL process then continues automatically to the next step, where destination components may physically delete the marked records during synchronization. Verify the destination system after execution to confirm that records marked for deletion have been processed in accordance with the configured ETL workflow.

Tips:

• Use this step only after identifying records that should no longer exist in the destination system.
• Place this step after comparison or lookup operations that determine deletion candidates.
• Combine this step with destination synchronization steps that support delete operations.
• Review deletion criteria carefully to avoid removing valid records.
• Test synchronization workflows in a non-production environment before deleting production data.
• Use ETL logging to record deleted records for auditing purposes.
• Combine this step with Lookup, Compare Records, Filter Rows, Database Destination, or Conditional Transform ETL steps to automate complete synchronization workflows.

Notes:

• This step marks records for deletion but does not itself physically delete data.
• Actual record deletion depends on subsequent ETL destination components and their configuration.
• The step contains no configurable properties.
• Processing automatically continues to the next ETL step after the current record has been marked.
• Deletion behavior depends on the destination connector's capabilities and configuration.
• Workflows should include appropriate auditing and backup procedures before performing large-scale delete operations.
• This step is intended for ETL synchronization workflows and does not define True or False return paths for workflows.

Definition Sample:

You may download the sample definition(s) from the link provided and later import them (drag-and-drop) into your FlowWright ETL Definition page.

Note: Verify and complete any missing configuration after importing the sample, including:

• Source data configuration
• Record comparison logic
• Destination delete configuration
• ETL transformation sequence
• Logging configuration
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.