Copy Data Activity failed on Upsert

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.