Reference data keeps imports honest
Lookup validation is useful when source files contain codes that must already exist elsewhere. It prevents orphan records, bad categories, and reports grouped under values nobody recognises.
In Advanced ETL Processor, the lookup reads from a database, file, or repository object. Failed lookups are logged, rejected, or routed for correction.
Good lookup validation examples
- Product codes must exist in the product table.
- Customer IDs must exist before orders are loaded.
- Country or currency codes must match approved lists.
- Department codes must match the finance structure.
When not to reject immediately
If new reference values are expected, route them for review instead of blocking the entire file. Some workflows need a quarantine lane, not a brick wall. The data still gets controlled, and nobody has to restart the whole run because one new product appeared.
Useful references
See working with large value lists, Advanced ETL Processor benefits, and Microsoft's overview of keys and constraints.