Error Handling and Validation

Validation rules and execution reports surface data-quality issues before they cause downstream failures. This article covers how to define validation rules, interpret execution reports, and resolve common error patterns.

Validation Rules on Migrate Records

Validation rules let you check each record before it is inserted or updated in the target system. Define rules on the Migrate Records step to catch data quality issues before they cause downstream failures.

Rule Types

• Required field -- Ensures a field is not null or empty. Use for fields that the target system mandates.
• Value range -- Checks that a numeric or date field falls within an acceptable range. Useful for catching obviously wrong values like dates in the year 1900.
• Regex match -- Validates that a field matches a pattern. Common uses include email format, phone number format, or ID conventions.
• Custom formula -- Evaluates a formula expression that returns true or false. Use for complex business rules that combine multiple fields.

Failure Actions

Each validation rule has a configurable failure action:

Action	Behavior	When to Use
Skip	Log the failure and skip the record. Continue processing.	Data quality issues you can tolerate. The record is excluded from the migration but does not stop execution.
Warn	Log a warning and continue processing the record.	Issues you want to track but that should not prevent migration. Useful during testing to identify potential problems.
Error	Log the error and stop execution immediately.	Critical data integrity violations that must be resolved before migration can proceed.

Start with warn during development to discover issues without blocking progress. Switch to skip or error for production runs once you understand the data quality landscape.

Reviewing Execution Reports

After every execution, review the results summary before considering the run successful. Even runs that complete without errors can have issues worth investigating.

Key Metrics to Check

• Extracted count -- Compare this against the expected number of records. A significantly lower count may indicate a filter is too restrictive; a higher count may indicate missing filter conditions.
• Inserted count -- New records created in the target.
• Updated count -- Existing records updated via key mapping matches.
• Skipped count -- Records excluded by validation rules. Review these to understand what data was left behind.
• Failed count -- Records that caused errors. Each failure includes the source record data and the error message.

If the skipped or failed count is unexpectedly high, investigate before running again. A high skip count may indicate a systemic data quality issue that should be addressed at the source.

Error Log Details

For each failed record, the execution report includes:

• The source record's key fields and data values.
• The specific error message from the target system or validation rule.
• The step and field where the error occurred.

Use this information to trace the root cause. In most cases, the error message points directly to the problem.

Common Error Patterns and Resolutions

"Record not found" on Lookup

Cause: A lookup field references a value that does not exist in the lookup table.

Resolution: Check that the lookup table contains all expected values. If the source data includes values not in the lookup (e.g., inactive users, deleted categories), either add those records to the lookup or configure a default value in the field mapping using if_null().

"Duplicate key" on Insert

Cause: The target system already contains a record with the same unique key, and the step is configured for insert only.

Resolution: Configure key mappings on the Migrate Records step so that existing records are updated instead of re-inserted. Key mappings compare a source field to a target field to determine if a record already exists.

"Invalid reference" on ID Mapping

Cause: A reference field mapping uses a Table Correlation, but the correlation table has no entry for the source ID. This typically means the parent record was never migrated.

Resolution: Verify that the parent migration procedure ran successfully before the child procedure. Check the parent procedure's execution report to confirm records were inserted and correlations were written.

API Timeout

Cause: The source or target system is responding too slowly, causing requests to exceed the timeout threshold.

Resolution: Reduce the batch size to send smaller requests. If timeouts persist, check whether the source system is under heavy load and consider scheduling the migration during off-peak hours.

"Field not found"

Cause: A field referenced in the mapping does not exist on the source or target table. This often happens when the source system's schema has changed since the table metadata was last loaded.

Resolution: Open the connection's Tables tab and refresh the table metadata. This pulls the current schema from the source system and updates the available fields.

Defensive Mapping Patterns

Build your field mappings to handle imperfect data gracefully.

Handle Null Values

Use if_null() to provide fallback values for fields that might be empty:

if_null([description], "No description provided")

Use first_with_value() when you have multiple candidate fields and want to use the first non-empty one:

first_with_value([work_notes], [comments], [description])

Fail Fast on Critical Data

Use conditional_error() in formulas to stop processing when a critical field contains invalid data:

conditional_error([sys_id] == None, "Source sys_id is null - cannot proceed")

This is preferable to letting a bad record propagate through the migration and cause harder-to-diagnose failures downstream.

Validate Before Full Migration

Before running a full migration, use record sampling to check that your defensive patterns work correctly. Look for:

• Fields that are unexpectedly null in the sample results.
• Default values being applied more often than expected (may indicate a mapping issue).
• Formula errors in the execution log.

Summary

• Define validation rules to catch data quality issues before they reach the target system.
• Review execution reports after every run, checking extracted, inserted, skipped, and failed counts.
• Learn the common error patterns and their resolutions to speed up debugging.
• Use if_null(), first_with_value(), and conditional_error() to build resilient field mappings.
• Start with warn-level validation during development, then tighten to skip or error for production.