Failed to index using upsert method

Options
Winnie_1234
Winnie_1234 Member
in Magic ETL
image.png

Hi, during the initial stage, I had already loaded 6 months of data (hundreds of millions of records) with mapping using Magic ETL. I extracted the raw 6 months of data using Workbench, and Magic ETL connected to this raw dataset to perform the mapping.

Upon review, I noticed that I missed mapping one type of document — let's say type "AB". My Magic ETL includes many other document types as well.

Since only the rows with type "AB" are missing, I attempted to update just those rows using the upsert option. I selected the appropriate unique columns for the upsert in Magic ETL and confirmed that there were no empty columns among this upsert columns.

As shown in the screenshot, ALL steps loaded successfully — but it failed at the final stage, which is indexing.

Kindly advise.

Would concatenating the unique columns into a single new column as the upsert key work better, rather than selecting multiple columns as in the default setup?

Tagged:

Answers

  • ArborRose
    ArborRose Coach

    This isn't my area of expertise but maybe we can get some discussion going to help you solve it. Yes, I would think concatenating fields would help with the uniqueness and avoid your upsert issue. It would simplify the logic of what constitutes a unique record. Maybe create a new field with something like:

    CONCAT(`DocumentID`, '_', `DocType`, '_', `Date`)

    Make sure the field has no duplicates in the incoming data and use the new field as the only key in your upsert. You could be hitting some issues with non-empty fields, white spaces, etc. And a large dataset may cause issues for Domo when it's trying to match on multiple columns.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories