Failed to index using upsert method

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?
Answers
-
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! **0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive