Description:

The Validate Data step verifies the integrity and quality of records flowing through an ETL pipeline before they are processed further.

This transformation allows designers to validate datasets using one or more validation techniques, including logical expressions, column data type validation, and regular expression (RegEx) pattern matching. Based on the validation results, records follow either the Valid or Invalid execution path, allowing ETL definitions to separate compliant records from those requiring correction, logging, or rejection.

The Validate Data step helps ensure that downstream transformations and destination systems receive only data that meets business rules and structural requirements, reducing processing errors and improving data quality.

This step is commonly placed after data extraction and initial transformations, but before aggregation, loading, or synchronization with external systems.

The step supports:

• Expression-based validation
• Column data type validation
• Regular expression (RegEx) validation
• Data quality enforcement
• Conditional ETL routing
• Data cleansing workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Data quality validation
• Import verification
• Customer data validation
• Financial data checks
• Data migration projects
• Master data management
• Enterprise ETL automation

Usage:

The Validate Data step is typically inserted after data has been imported and optionally transformed, but before additional business processing or loading into destination systems.

During execution, the step evaluates each record against the configured validation rules. Records that satisfy all configured criteria continue through the Valid path, while records that fail one or more checks are routed through the Invalid path, where they can be logged, corrected, quarantined, or exported for review.

A typical ETL pipeline might look like this:

Typical validation scenarios include:

• Ensuring mandatory values are populated
• Verifying that numeric fields contain valid numbers
• Confirming dates are correctly formatted
• Validating email address formats
• Checking identifier patterns
• Applying custom business rules
• Preventing invalid records from reaching destination systems

Prerequisite:

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

Example:

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

• Create a new ETL definition named “clsValidateDataDef” and open it in designer mode.
• Drag the “clsInputCSV, clsValidateData, clsErrorCSV, and clsOutputCSV” steps to the canvas. 
• Connect the dots between “clsInputCSV” and the other steps, as shown above.
• Define a variable or a global to hold the virtual path. 
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsValidateData" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns to transform. Then click the Save button. 

• Click the "clsValidateData" step to configure its "Required" properties. Click the column button to open the transform list configuration. A pop-up window will appear. Click the Add Row (+) button to insert an empty row. Select the column from the drop-down list. Select the operator. Enter the value for the expression validation. Select the “and/or” operator to configure multiple expressions. Click the Save button. You can configure multiple expression lists for transformation using the Add Row button. 

• Click the "clsValidateData" step to configure its "Optional" properties. Click the button to configure column types. A pop-up window will appear for configuration. Click the Add Row (+) button to insert an empty row. Select the column from the drop-down list. Select the data type to validate from the drop-down list. Click the Save button. You may add multiple columns for transformation by using the Add Row button. 

• Click the "clsValidateData" step to configure its "Optional" properties. Click the button to configure validation with regular expressions. A pop-up window will appear for configuration. Click the Add Row (+) button to insert an empty row. Select the column from the drop-down list. Enter the regular expression to validate. Click the Save button. You may add multiple columns for transformation by using the Add Row button. 

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• Click the "clsErrorCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsErrorCSV" step to configure its "Optional" properties. Provide a name for the step and specify the variable or global reference to hold the virtual output path. 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.

• Save the ETL Definition. Execute the ETL process. During execution, each record is evaluated against the configured expression, data type validation, and regular expression rules. Records passing all validation checks follow the Valid path for further processing. Records failing one or more validation checks follow the Invalid path, where they can be logged, corrected, or reviewed before reprocessing.

Tips:

• Use expression validation to enforce business-specific rules.
• Validate column data types before performing calculations or aggregations.
• Use regular expressions to verify structured values such as email addresses, phone numbers, postal codes, account numbers, or product identifiers.
• Keep validation rules as simple and maintainable as possible.
• Route invalid records to separate ETL branches for auditing or correction instead of discarding them.
• Combine multiple validation techniques to improve overall data quality.
• Use this transform together with JSON Input File, CSV Input, Filter Transform, Lookup Transform, Derived Column, Aggregate Transform, or SQL Output components to build comprehensive ETL pipelines.

Notes:

• Validation expressions are evaluated for each record processed by the ETL pipeline.
• Column type validation compares values against the configured ETL schema.
• Regular expression validation verifies the format of selected column values.
• Records are routed through either the Valid or Invalid execution path depending on the validation outcome.
• Invalid records are not automatically corrected; additional ETL logic should be implemented if corrective actions are required.
• Improperly configured expressions or invalid regular expression syntax may cause ETL execution errors.
• Data validation should be performed before loading records into production databases or external applications.
• This transform is commonly used in data migration, business intelligence, reporting, customer data management, financial processing, regulatory compliance, and enterprise integration workflows.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

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

• Validation expressions
• Column type validation rules
• Regular expression mappings
• Valid and Invalid branch connections
• Destination mappings
• Environment-specific configuration

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

Click here to download the sample file.