Schema Drift for upsert action in Data Flow

Question

Schema Drift for upsert action in Data Flow

Anonymous

Adding a drived column in the dataflow that has a delta sink with upsert operation, I'm getting following error. Can you confirm if merge schema is not supported for upsert actions in dataflow.

Job failed due to reason: at Sink 'sinkEntity': cannot resolve target.column1 in UPDATE clause given columns

PRADEEPCHEEKATLA 91,576 Reputation points Moderator

2023-03-20T04:57:38.91+00:00

Hi zahid zaman,

We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

Our engineer has provided a detailed answer which has clear steps for which you are looking for.

If you wish, you may re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!

Regards,

PRADEEPCHEEKATLA-MSFT

2 answers

Your answer

PRADEEPCHEEKATLA 91,576 Reputation points Moderator

2023-03-20T04:57:38.91+00:00

Hi zahid zaman,

We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

Our engineer has provided a detailed answer which has clear steps for which you are looking for.

If you wish, you may re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!

Regards,

PRADEEPCHEEKATLA-MSFT

Answer 1

ShaikMaheer-MSFT 38,556 Microsoft Employee Moderator

Hi zahid zaman,

Thank you for posting query in Microsoft Q&A Platform.

From the error, it seems dataflow not able to resolve your column. This error may happen when source and sink columns are not mapped properly or columns datatypes mismatch. Kindly check your source and sink column mappings along with data types.

For some reason, if it's because of column datatype mismatch then make use of derived column transformation to convert the column datatype and map it.

Some its this error may also happen in case of identity columns, so you can consider setting identity insert on before processing data.

Hope this helps. If not, please share more details of your sink transformation and preview data along with screenshot to understand settings and error better and help.

Please consider hitting Accept Answer button. Accepted answers help community as well.

Anonymous

2023-03-05T23:33:45.01+00:00

@ShaikMaheer-MSFT

I think you missunderstood the query, I'm using auto-mapping in source & sink.

Following are the details for calrification

Source Settings

Source Data Sample

Alter Row Transfromation settings (Mark every row for upsert)

Sink Transformation

Sink Settings

Execute dataflow n-number of times works fine utill add a new column in the source or as drived columns.

Execution 1. (Sinked data in delta)

Now if I add a drived column sink data preview is like this

Triggering the dataflow again from the pipeline Failed with the error

In this case I have a delta sink already having data with 3 columns and now I'm adding additional column.
Anonymous

2023-03-05T23:38:19.26+00:00

@ShaikMaheer-MSFT I have executed the data flow and the sink(delta) had 3 columns. It works fine untill I add a new drived column in the dataflow (This means now the data to be sinked has 4 columns and existing delta files contains 3 columns), Schema merge is required that is failing, with the following error

{"message":"Job failed due to reason: at Sink 'sink1': cannot resolve target.UTCDATE in UPDATE clause given columns target.id, target.name, target.country;. Details:org.apache.spark.sql.AnalysisException: cannot resolve target.UTCDATE in UPDATE clause given columns target.id, target.name, target.country;\n\tat org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)\n\tat org.apache.spark.sql.catalyst.plans.logical.DeltaMergeInto$$anonfun$org$apache$spark$sql$catalyst$plans$logical$DeltaMergeInto$$resolveExprsOrFail$1$3.apply(deltaMerge.scala:262)\n\tat org.apache.spark.sql.catalyst.plans.logical.DeltaMergeInto$$anonfun$org$apache$spark$sql$catalyst$plans$logical$DeltaMergeInto$$resolveExprsOrFail$1$3.apply(deltaMerge.scala:258)\n\tat scala.collection.immutable.List.foreach(List.scala:392)\n\tat org.apache.spark.sql.catalyst.plans.logical.DeltaMergeInto$.org$apache$spark$sql$catalyst$plans$logical$DeltaMergeInto$$resolveExprsOrFail$1(deltaMerge.scala:258)\n\tat org.apache.spark.sql.catalyst.plans.logical.DeltaMergeInto$.org$apache$spark$sql$catalyst$plans$logical$DeltaMergeInto$$resolveOrFail$1(deltaMerge.scala:271)\n\tat org.apache.spark.s","failureType":"UserError","target":"DeltaSchemaDriftTest","errorCode":"DFExecutorUserError"}

Answer 2

ShaikMaheer-MSFT 38,556 Microsoft Employee Moderator

Hi zahid zaman,

Thank you for sharing more details on the ask.

Error is expected in this case, Since you have 4 columns coming from Source and Sink has 3 columns and you are trying to upsert.

To avoid this error, you can consider using Table action as Overwrite. That overwrites the table existing data and schema too.

I would suggest always make sure you have same set of schema in source and sink to avoid this kind of situations.

Consider using manual mapping (Not auto mapping) option also, that also help to avoid these kinds of errors.

Hope this helps.

Please consider hitting Accept Answer button and Click on Yes for Was answer helpful question. Accepted answers help community as well.

ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2023-03-20T10:03:56.7033333+00:00

Hi Poel van der, RE (Ron),

Just checking if above answer helps. If yes, Please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Share via

Schema Drift for upsert action in Data Flow

2 answers

Your answer