Copy Data Activity failed on Upsert

Hi olivs​ 

My 2 cents on how i would approach the issue.

Step 1. Isolate the failing row

Set the ForEach to sequential with batch count 1, and turn off continue-on-error. Run it again and note which config row dies. If one table fails and the rest succeed, it's a type or data problem on that specific table. Carry on to step 2.

If every table fails, the problem is in the framework rather than in any one table. That points at the mapping types in your config table or at a connector-level setting, so go straight to step 5.

Step 2. Find out what created the target table

On the failing table, run:

spark.sql("DESCRIBE HISTORY <schema>.<table>").show(truncate=False)
spark.sql("DESCRIBE DETAIL <schema>.<table>").show(truncate=False)

If the most recent writer is anything other than the copy activity, such as a notebook. The table carries a schema the connector never produced, and Upsert is the first operation that has had to read it. Overwrite has been papering over this the whole time.

If the last writer is the copy activity, the schema came from an earlier Overwrite version of your mapping. Still a schema problem, just a self-inflicted one. Keep the DESCRIBE DETAIL output for step 5.

Step 3. Compare the stored schema against the Warehouse

spark.read.format("delta").load("Tables/<schema>/<table>").printSchema()

SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>';

• Start with ordernummer itself. The key column gets cast first, inside the merge predicate, which is exactly where this exception surfaces. A string in Delta against an int or bigint in the Warehouse, or the reverse, will do it.
• Then decimals. A Warehouse decimal(18,2) landing in a Delta decimal(38,18) is a common drift, and the docs explicitly warn that "editing the destination type currently is not supported when your source is decimal type".
• Then datetimes. The connector maps DateTime to timestamp. A table written by Spark can land timestamp_ntz instead, and there is no cast path between the two.
• Finally, check DESCRIBE DETAIL for delta.columnMapping.mode. The docs list name as supported for destination writes. id isn't on that list.

Step 4. Rebuild the table from the connector's own schema

Drop the target table. Run that single config row with Overwrite so the connector builds the Delta schema itself. Then flip the same row back to Upsert and run it again. If it now succeeds, the old table schema was the cause, confirmed. The question becomes how many of your other targets have the same history, and whether a one-off rebuild of all of them is acceptable.  If it still fails on a table the connector just created, the table was never the problem. the mapping is. Go to step 5.

Step 5. Take the explicit mapping out

Remove the mapping for that row from the config table and let the copy activity auto-map by name.

If it now succeeds, the declared sink types in your config table have drifted away from reality. Under Overwrite those types create the table, so they're always self-consistent and the drift is invisible. Under Upsert they get validated against what's actually stored. This is the single most likely explanation when step 1 showed every table failing.

If it still fails with no mapping and a freshly created table, you're into Delta writer feature territory. Go back to the DESCRIBE DETAIL output from step 2 and check the reader and writer versions and tableFeatures against what the connector supports.

Step 6. Check the data regardless

Independent of the cast error, look for NULLs in ordernummer and for duplicate keysral source rows match one target row, the MERGE is a correctness problem even afterthe cast is fixed, and Copy activity gives you no dedupe hook.

Separate from the immediate fix, I'd argue Copy activity Upsert is the wrong primitive for a metadata-driven framework. You get a black-box MERGE with no control over the match condition, no source dedupe, no soft-delete or SCD handling, and errors like this one. The merge logic also ends up buried in pipeline JSON instead of in Git, which hurts at ALM time.

What I'd suggest instead:

• Copy activity with Overwrite into a staging table (staging.<table> or stg_<table>). Always schema-consistent, fast, and it never hits this class of error.
• One parameterised notebook driven by the same config table, doing DeltaTable.forPath(...).merge(...) with key columns, change columns and delete handling. One notebook covers N tables, the errors are readable, and the logic is version-controlled.

One question: why copy Warehouse to Lakehouse neLake. If the point is just making those tables readable from Spark, a OneLakeshortcut from the Lakehouse to the Warehouse tables does that with no copy and no CU cost for movement. The pipeline earns its place only if you're building a silver or gold layer on top with history and merge semantics. If you're not, it shouldn't exist.

Did I answer your question, please mark the post as a solution or consider a like.

Hi olivs​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,
Chaithanya

Hi olivs​,

One detail explains why Overwrite works and Upsert doesn't, and points straight at where to look:

Overwrite -> table is (re)created from the incoming schema
             -> source types win, nothing to cast
Upsert    -> rows are merged into the EXISTING table schema
             -> every incoming column (and the key) must cast
                into the target type -> "Specified cast is not valid"

So the error is almost always a type mismatch between what the copy delivers and what the target Delta table already has, not a problem with the upsert keys themselves. With a config-driven mapping, the usual suspects are:

key column type differs (e.g. varchar in Warehouse, int/long in Lakehouse)
decimal precision/scale differs (decimal(18,2) vs decimal(38,10))
datetime2 vs date, or a timestamp stored as string
explicit "type" entries in the mapping JSON that don't match the target

A quick way to compare both sides for one failing table:

-- in the Warehouse (source)
SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Orders'
ORDER BY ORDINAL_POSITION;

# in a notebook attached to the Lakehouse (target)
for f in spark.table("dbo.orders").schema.fields:
    print(f.name, f.dataType.simpleString())

Pay special attention to ordernummer: if it is a string on one side and a number on the other, Overwrite hides it every time, and the first Upsert exposes it.

Two ways to fix it, depending on what you find:

1. make the types match
   -> cast in the source query (e.g. CAST(ordernummer AS BIGINT))
   -> or remove the "type" entries from the mapping in your config
      table so the copy maps by name to the existing types

2. reset the target once
   -> run one Overwrite with the corrected source query
   -> switch back to Upsert for the following runs

If you keep explicit types in the mapping, enabling type conversion in the translator lets the copy handle compatible conversions instead of failing:

"translator": {
  "type": "TabularTranslator",
  "mappings": [ ... from your config table ... ],
  "typeConversion": true,
  "typeConversionSettings": {
    "allowDataTruncation": false,
    "treatBooleanAsNumber": false
  }
}

I would keep allowDataTruncation off, so a genuine mismatch still fails loudly instead of silently cutting values.

So my overall approach would be:

compare source vs target types for the failing table
-> check the key column first
-> align types in the source query or drop mapping "type" entries
-> one Overwrite to reset the schema, then Upsert from there

That usually makes the config-driven upsert stable across all tables, not just the one that failed.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi olivs​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,
Chaithanya