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.