Cross-Table Validation
Compare rows against the tables they depend on before bad references wander into production wearing a fake moustache.
Cross-table validation compares data across different tables before the ETL workflow loads the row. Use it to confirm that a customer code exists, that the customer is active, and that related records are valid before the target database inherits the problem.
Cross-table validation checks the table behind the value
A source row can contain a customer code that looks perfectly reasonable. That does not mean the customer exists. It also does not mean the customer is active, approved, or allowed to receive the transaction.
Cross-table validation checks the related table before loading. The workflow can compare the incoming value with a customer, product, account, supplier, or reference table and decide what should happen when the match fails.
Customer code validation is the classic example
The rule is simple: the customer code in the source data must exist in the customer table and the customer must be active. If the code is missing or inactive, the row should not be treated as clean.
Valid customer
Source customer code `CUST-1042` exists in the customer table and its status is active. The record can continue.
Inactive customer
Source customer code `CUST-2048` exists but is marked inactive. The row needs an exception path, not blind optimism.
Missing customer
Source customer code `CUST-9999` does not exist in the customer table. The order is now waving at an empty chair.
Wrong relationship
The customer exists, but the account type or region does not match the transaction rule.
Active status matters as much as existence
A simple existence check is useful, but it is often not enough. Many business rules depend on the state of the related record.
An inactive customer may still exist for audit history. That does not mean new orders should be loaded against it. The same pattern appears with expired products, closed accounts, blocked suppliers, discontinued items, and inactive cost centres.
Customer table
Customer code exists and customer status is active.
Product table
Product code exists and the product is not discontinued.
Account table
Account exists, is open, and accepts the transaction type.
Supplier table
Supplier exists and is approved for the purchase category.
The workflow should keep failed matches visible
When a cross-table check fails, route the row somewhere useful. That may be a rejected-records table, an exception file, or a review queue with the failed lookup value and reason.
Do not create a new customer automatically unless the business has approved that behaviour. Surprise master data is how reporting systems develop trust issues.
Related guides include lookup validation, referential integrity validation, and consistency validation.
When cross-table validation is more than you need
If you are checking one small spreadsheet against a short customer list, a quick database query may be enough. Not every import needs a validation orchestra.
Automate cross-table validation when the feed repeats, the related table changes over time, or a missing reference would affect orders, invoices, stock, finance, or customer reporting.
Video
Cross-table validation questions
What is cross-table validation in ETL?
Cross-table validation compares source values with data in another table. It is commonly used to check whether customer codes, product codes, account IDs, or other references exist before loading rows.
What is a simple cross-table validation example?
A sales order contains a customer code. The ETL workflow checks the customer table to confirm that the code exists and that the customer is active before loading the order.
How is cross-table validation different from lookup validation?
Lookup validation usually checks that a value exists in a reference list. Cross-table validation often goes further by checking related attributes such as active status, date ranges, account type, or approval state.
Should rows fail when a related table value is missing?
In production workflows, missing or inactive related values should usually be rejected, logged, or routed to review. If new reference values are expected, use a controlled exception path instead of guessing.
Check the related table before the load
Define the source field, choose the related table, check existence and status, then route failed rows clearly. A customer code is only useful when it points to a customer you can actually use.
The code is not enough. The row needs a real, active record behind it.