Uniqueness Validation
Check that key values appear once before duplicate rows turn a normal report into fiction with columns.
Uniqueness validation checks that a field, or a combination of fields, appears only once in an ETL load. Advanced ETL Processor runs this check inside a visual ETL workflow, routes duplicate rows, logs the rejected keys, and keeps the original source unchanged.
Uniqueness validation protects business keys
A value can be present, correctly formatted, and still dangerous. If an order number appears twice, the import may update the wrong record, load the same transaction twice, or leave two systems disagreeing politely across a database table.
Uniqueness validation catches that problem before the load reaches the target. It protects scheduled imports when the source system does not enforce keys properly, or when files arrive from spreadsheets, exports, partners, portals, and other places where discipline goes to have a lie down.
Choose the fields that must be unique
Start with the field that identifies the record. In some workflows, that is a single primary key. In others, it is a business key made from several fields.
Single-field uniqueness
Check that `customer_id`, `invoice_number`, `order_id`, or another identifier appears once in the input batch.
Composite uniqueness
Check a combination such as customer, date, product, and reference number when no single field is reliable enough on its own.
Target uniqueness
Check whether a value already exists in the target or lookup table before inserting a new record.
Batch uniqueness
Check duplicates inside the current file before the workflow touches the destination system.
The validation flow fails loudly
The simplest workflow is direct: read the source, check the key fields, route duplicates, then load only records that pass. If a duplicate appears, log the duplicate key and enough source context to investigate it later.
Do not silently keep the first record unless that is the agreed business rule. Do not silently keep the last record either. Silent duplicate handling is how support tickets grow legs and begin walking around the office.
If the source sends corrected records, define the rule clearly. For example, keep the latest row by timestamp, reject older duplicates, or route all conflicting rows for review. The ETL workflow follows that rule every time.
Use database checks when the duplicate may already exist
Batch uniqueness catches duplicates inside the incoming file. Target uniqueness catches duplicates against data that has already been loaded. In many production workflows, you need both.
A database lookup checks whether the incoming key already exists. If it does, Advanced ETL Processor rejects the row, updates the existing record, or sends it to a separate path according to the rule you define. The correct action depends on whether the feed contains new records, corrections, or a mixture of both.
Related validation guides include file duplication validation, referential integrity validation, and lookup validation.
When uniqueness validation is more than you need
If you are importing a one-off spreadsheet into a temporary table for manual inspection, a database query after loading may be enough. Not every file deserves a ceremony.
Use automated uniqueness validation when the process is scheduled, the data feeds reports, or duplicate records would affect customers, finance, stock, orders, or compliance. In those cases, finding the duplicate before loading is cheaper than explaining it afterwards.
Video
Uniqueness validation questions
What is uniqueness validation in ETL?
Uniqueness validation checks that a value or combination of values appears only once in the incoming data. It is usually applied to primary keys, invoice numbers, order IDs, customer references, and other business keys.
What is the difference between uniqueness validation and duplicate detection?
Uniqueness validation focuses on the fields that must be unique, such as an ID or business key. Duplicate detection can compare entire rows or wider sets of columns to find repeated records.
Should uniqueness be checked before loading the database?
Yes, when duplicate values can break inserts, overwrite records, inflate totals, or make reports unreliable. Database constraints are useful, but ETL-side validation gives clearer logs and better handling of rejected rows.
What should happen when uniqueness validation fails?
Failed rows should be rejected, logged, or sent to an exception workflow. Do not silently pick a winner unless the business has approved a clear rule, such as keeping the newest record by timestamp.
Check the key before the load
Use Advanced ETL Processor to validate the key fields first, route duplicates clearly, and keep the original source unchanged. That gives you clean data and a trail when the source file starts telling stories.
Duplicate keys are not extra data. They are extra trouble with matching ID badges.