I have a system which hits a API on daily basis and collects data. This data is divided into logical groups, with a logical group defined as all data belonging to a particular user. A user has 30 subaccounts and for each account I hit the API and insert data into the database. After each hit, I perform an insert so I have 30 hits for this ex.
Now sometimes the API throws some temporary error, say at hit 20, so if I put that in try catch data for that particular hit is skipped which essentially corrupts the database with malformed data for that particular day.
I want to insert all the data only if all the hits are successful and I cannot store the result for each hit in memory as that would too much memory intensive as each hit contains lots of data.
How should I design the system so that if an error occurred in between nothing is inserted?
This can be achieved via transactions at DB level, but I have a restriction that I use Elasticsearch and I cannot find transactional inserts in it. Somehow I need to handle it at the application level.