Description:

The Run ETL Validation Job step enables a FlowWright workflow to execute an ETL validation definition and evaluate the quality of the processed data before continuing workflow execution.

Unlike a standard ETL execution step, this component is specifically intended for validation scenarios. After the ETL validation completes, the workflow follows one of three execution paths depending on whether all records passed validation, all records failed validation, or a combination of valid and invalid records was detected.

The step can optionally pass selected workflow variables and global variables to the ETL definition, allowing ETL validation jobs to process dynamic input such as file paths, customer identifiers, import parameters, or runtime configuration values.

For auditing and monitoring purposes, the ETL instance identifier can also be stored in a workflow variable or global variable for later use in reporting, logging, or troubleshooting.

Typical uses include:

• Validating imported CSV files
• Pre-processing customer data
• Invoice validation
• Employee record verification
• Product catalog validation
• Data migration quality checks
• Master data validation before system integration

This step allows workflows to make business decisions based on ETL validation results before performing downstream processing.

Usage:

The Run ETL Validation Job step is typically used before importing data into business systems or before executing critical workflow operations.

During execution:

1. Select the ETL validation definition.
2. Pass any required workflow variables or global variables.
3. Execute the ETL validation job.
4. Store the ETL instance ID if configured.
5. Evaluate the validation outcome.
6. Continue workflow execution through the appropriate return path.

A typical workflow might look like this:

Typical workflow scenarios include:

• Validating uploaded spreadsheets before importing
• Checking mandatory fields
• Detecting duplicate records
• Validating reference data
• Enforcing business rules
• Performing migration quality checks
• Preventing invalid data from entering downstream systems

Example:

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

• Create a new process definition named “clsExecuteETLValidationJobDef” and open it in designer mode. 
• Drag the “clsExecuteETLValidationJob and placeholder” step to the canvas. 
• Connect the dots between the “Start” and “clsExecuteETLValidationJob” steps, as shown above. 
• Select the connection lines and configure the appropriate workflow paths for the step returns: “AllRowsGood, AllBadRows, and GoodBadRows”.
• Define a variable or a global to store the ETL Instance ID and the output file path. 
• Click the "clsExecuteETLValidationJob" step to configure its "Required" properties. Provide a name for the step, then select the ETL definition from the drop-down list. Click the Save button. Note: Click the "AI Predict" button to have the Copilot add new process steps that match your process description. 

•  Click the "clsExecuteETLValidationJob" step to configure its "Optional" properties. Click the button to configure multiple variables and globals that will be passed to the ETL instance at execution. Provide the variable or global reference to store the ETL instance ID. Then click the Save button. 

• Click the button to configure variables to pass to the ETL instance, as shown below. A pop-up window opens for configuration. Click the Add Row (+) button to insert an empty row. Select a variable from the drop-down list. Click the Save button. You may add multiple variables by using the Add Row button. 

• Click the button to configure the globals to pass to the ETL instance, as shown below. A pop-up window opens for configuration. Click the Add Row (+) button to insert an empty row. Select a global from the drop-down list. Click the Save button. You may add multiple globals by using the Add Row button. 

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

• Navigate to the Process Definition page. Save the process definition, create a new instance, and execute it. Render the process instance. This “clsExecuteETLValidationJob” step shall validate the configured ETL job. 

• Navigate to the ETL Instances page. Render the instance shown above. The ETL process execution is shown below.  

Tips:

• Design ETL validation definitions to verify business rules before importing production data.
• Pass only the workflow variables required by the ETL definition.
• Store the ETL instance ID to simplify auditing, reporting, and troubleshooting.
• Route each return path to appropriate business logic rather than treating all validation outcomes identically.
• Use descriptive names for ETL validation definitions to simplify workflow maintenance.
• Review ETL execution logs whenever validation failures occur.
• Combine this step with Upload File, CSV Input File, Validate Data, Generate Report, Send Email, Loop Each, or Create Process Instance steps to build complete data validation workflows.

Notes:

• A valid ETL definition must be selected before the workflow can execute.
• Passing workflow variables and global variables is optional and depends on the ETL definition requirements.
• The ETL instance ID can be stored for later auditing, reporting, or diagnostic purposes.
• The three return paths represent the overall validation result produced by the ETL validation job.
• Workflow designers should implement handling for all three outcomes.
• Detailed validation information is available through the ETL execution logs and validation reports generated by the ETL definition.
• Execution details and validation results are recorded in the FlowWright workflow execution log.

Run ETL Validation Job vs. Run ETL Job:

Both workflow steps execute FlowWright ETL definitions, but they are intended for different purposes.

As a general guideline:

• Use Run ETL Validation Job when workflows need to evaluate data quality before continuing business processing.
• Use Run ETL Job when the workflow should execute a standard ETL process to transform, migrate, or load data.

Definition Sample:

You may download the sample workflow definition from the link provided and import it into your FlowWright environment.

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

• ETL validation definition
• Workflow variables
• Global variables
• ETL instance ID storage variable
• Workflow branching for AllRowsGood, GoodBadRows, and AllBadRows
• Validation reporting and notification logic

After verifying the configuration, save and publish the workflow before execution.

Click here to download the Process sample file.

Click here to download the ETL Process sample file.