Improve Copy Job Validation Errors When Upsert Keys Are Missing

Product: Microsoft Fabric
Area: Data Factory – Copy Job
Category: Error Handling / User Experience

Problem

When adding new tables to an existing Microsoft Fabric Copy Job configured for Change Data Capture (CDC) with Upsert behavior, the designer allows users to configure tables without specifying the required key columns used for matching records.

When attempting to save the Copy Job, the following generic error is displayed:

"Validation of models failed. Fix it before saving."

Unfortunately, this message does not identify which table has an invalid configuration, what property is missing, or how to resolve the issue.

This becomes particularly frustrating when a Copy Job contains dozens of tables, because users must manually inspect each table's configuration to identify the problem.

Proposed Improvement

Improve Copy Job validation to provide actionable, table-specific error messages when required configuration properties are missing or invalid.

Specifically:

1. Validate Upsert Keys: When a destination table is configured with Upsert behavior, validate that at least one matching key column has been selected.
2. Identify the Table: Include the source or destination table name in the validation error.
3. Provide Actionable Guidance: Explain which configuration property is missing and how to correct it.
4. Highlight Invalid Tables: Display a validation indicator next to affected tables in the Copy Job designer.
5. Validate Before Save: Detect missing required properties when configuring a table rather than waiting until the user attempts to save the entire Copy Job.
6. Aggregate Validation Errors: When multiple tables have invalid configurations, display all identified issues so users can resolve them in one pass.

Example Improved Error Message

Copy Job validation failed: 2 tables require attention.

• dbo.Customer: Upsert requires at least one matching key column. Select a key column under Destination → Upsert settings.
• dbo.Invoice: Upsert requires at least one matching key column. Select a key column under Destination → Upsert settings.

Select the affected table to correct its configuration.

Expected Benefits

• Reduce troubleshooting time for Copy Jobs containing many tables.
• Make validation errors actionable instead of generic.
• Improve the experience of adding tables to existing CDC Copy Jobs.
• Reduce unnecessary support requests.
• Help users distinguish configuration validation errors from runtime execution failures.

Current Behavior vs. Expected Behavior

Current: A generic "Validation of models failed" error appears without identifying the invalid table or missing configuration.

Expected: The designer identifies the specific table, missing Upsert key configuration, and steps required to resolve the issue.

Additional Context

This issue was encountered when adding tables to an existing Azure SQL Database → Microsoft Fabric Data Warehouse CDC Copy Job. The existing Copy Job executed successfully, but saving changes failed because newly added tables did not have their Upsert matching keys configured.

The underlying configuration issue was straightforward to correct once identified, but the generic validation error provided no useful diagnostic information.