How to properly replace data when using Upsert for Output?

alzwang99

Hello, I am currently testing out Upsert for the Update Method of the output dataset and I am confused about how it functions.

From what I understand, it serves as both an append and replace update method where you can append on the current data to the historical data that exists.

The issue I am facing is that I wanted to replace values within a column that contained a specific value into a new value. For example, 'R' to 'Regular' or 'O' to 'Overtime'. Which I then tried to run and unfortunately now the column contains entries for both 'R' and 'Regular', therefore duplicating rows instead of replacing the column's value.

I therefore was hoping a way to remove entries that contain 'Regular' and start from scratch again, however, no matter if I implemented filters, it won't remove the data.

With dataflows, I know you can go to a previous version, however it did not affect the output.

I hope this is clear. Thank you.

rco

Upsert works exactly like Append except that any pre-existing rows with the same key value (the value in the specified key column(s)) as a newer row will be removed. It isn't currently possible (in Magic ETL) to remove rows with a given key value entirely, nor is it possible to update the key value of a row.

Reverting to an older version of a DataFlow doesn't affect its outputs at all until it executes. After you execute the reverted DataFlow, if all of the Output DataSet tiles are in "Replace" mode, you're probably back to exactly the state you were in the past (with a few exceptions, like when the DataFlow references the current date or time in its definition). However, when one or more Output DataSet tiles are in Upsert, Partition, or Append mode, you need to take into account what was already in the DataSet to determine what will be in it thereafter. If you want to start over, you should set the Output DataSet tiles to Replace mode for just one execution, and then put them back in the mode you intend going forward.

rco

You could preserve your historical data by making a new DataFlow that uses the problematic output as an input, and then copy its contents to a new DataSet. Then you could bring that copied DataSet in as a secondary input to your original DataFlow and merge the new data with the historical data in any way you choose (Append Rows, Join, etc.).

rco

Upsert works exactly like Append except that any pre-existing rows with the same key value (the value in the specified key column(s)) as a newer row will be removed. It isn't currently possible (in Magic ETL) to remove rows with a given key value entirely, nor is it possible to update the key value of a row.

Reverting to an older version of a DataFlow doesn't affect its outputs at all until it executes. After you execute the reverted DataFlow, if all of the Output DataSet tiles are in "Replace" mode, you're probably back to exactly the state you were in the past (with a few exceptions, like when the DataFlow references the current date or time in its definition). However, when one or more Output DataSet tiles are in Upsert, Partition, or Append mode, you need to take into account what was already in the DataSet to determine what will be in it thereafter. If you want to start over, you should set the Output DataSet tiles to Replace mode for just one execution, and then put them back in the mode you intend going forward.

alzwang99

It sucks that is the solution as it does have me lose historical data if I make incorrect adjustments through bad practices and go back to using replace. Thank you for taking the time to write this explaination.

rco

You could preserve your historical data by making a new DataFlow that uses the problematic output as an input, and then copy its contents to a new DataSet. Then you could bring that copied DataSet in as a secondary input to your original DataFlow and merge the new data with the historical data in any way you choose (Append Rows, Join, etc.).