Working w/ Bulk Data in NetSuite

[jessica-lie]

[Nov 25, 2020] Joe Blanchett (Product Manager, Finance Systems at Seat Geek) posted:

We have a few recipes where we need to work with > 200 rows of data in NetSuite. The search records call maxes out at 200 results on the first page, so the only way I've come up with to get all results involves multiple steps that iterate through each page, and accumulating the results into a workato variable list along the way. As you can see below, this method involves 10 steps just to get all the data that's needed. Has anyone come up with a more elegant way to get high volumes of data from NetSuite? 

[jessica-lie]

[Nov 25, 2020] Gordon Hu from WeGalvanize replied:

Throwing my two cents here:

1. If the total number of pages is big – this recipe will run for a long time. It may also run into limit of the number of items Workato variable supported.

2. If you have a third party place to “park” your data (e.g., as simple as Workato lookup table, Box CSV, or DynamoDB, or RedShift), here is what we usually do:

• Create a “master recipe” to make initial call, upload the first batch. If there is more than one page, call the “callable_1”, pass the parameter (e.g., total number of pages, next page, etc)
• Callable_1: similar to master recipe, make the next page’s call and upload the next batch. If the current page is < total pages: call callable_2
• Callable_2: similar to Callable_1, same logic. If the current page is < total pages: call callable_1

Then, tweak Callable_1 and Callable_2 so that it knows “when to stop”.

There are a few more small things to note:

• This strategy can be used for any pagination involved api calls.
• The place where you park the data may not support large number of rows (e.g., lookup table)
• The place where you “park” the data may have some compliance issue (e.g., can you store customer’s data on box?)
• Once the data is parked, can workato “get it back” ? Does workato have ability to digest the large number of rows?

[jessica-lie]

[Nov 25, 2020] Joe Blanchett (Product Manager, Finance Systems at Seat Geek) replied:

Thanks for the feedback Gordon!

I totally agree with your points below. I haven’t ran into the variable list limit yet - and to be honest I’m not sure what that is. To avoid a 90 min timeout on the Workato side, I’ve typically used (1) recipe to just get all of the data and (2) a recipe and that processes each individual record. We’re currently processing up to 8K records daily. As we scale up past ~20K records we will use a Postgres DB to “park” the data like you suggested.

[jessica-lie]

[Nov 26, 2020] Jason Jho (CTO at Anvyl) replied:

If the data processing logic doesn't have to live in the body of your parent recipe, you could potentially leverage Bulk triggers instead. For instance, if you have a New/Updated Records in Bulk, a job will be created for each batch of 200 records. This way you don't have to maintain lists and pagination logic.

Hope this helps!

[jessica-lie]

[Nov 26, 2020] Brian Flood (Vice President, IT, Business Systems, & Data at Fastly) replied:

We've had to do essentially the same thing for results from BigQuery and Okta (https://app.workato.com/recipes/1148428?st=3318f9- this one gets ugly because of the way they give you the next page). 

I feel like this is an area where I disagree philosophically with Workato about how this should be handled. I can't think of a use case where a user would only want a single page of results, which leads me to think the pagination should just be built into the connector, rather than making the users figure out how to page through the results from the various APIs. In some cases, you wouldn't even know there were more pages since, like with Okta, the next page might be passed as a parameter in the response header which the OOB connector does not give you access to and there's no indicator to tell you that your results are partial. It might just seem odd you have exactly 1000 records. 

Is there a use case for not wanting every page of results (outside of testing)?