Posting answer to my question after a while, hoping it helps someone. The below approach helped me achieve the desired results.
After doing a lot of research and going through the product documentation, community & KB articles it is recommendended to use the pagination technique.
To do this, the offset values needs to be adjusted by the third party system that is using the endpoints. they will have to develop a logic that involves loops to query the data in batches.
In my project scenario, as there are more number and records and the data size is also huge we suggested the third party tool member to pull the data in chunks by using pagination.
Please follow below document and use the method by adding sysparm limit and offset to your query for achieving this requirement.
KB0727636 - Exporting Bulk Data from Servicenow via REST Web Service Pagination
In addition to the above solution, I would like to add one more thing related to the same case - Is there a way where we can figure out the total number of records that exists in a table using the endpoint to set the sys_parm_limit value. Because setting the sys_parm_limit manually might be helpful initially, but as the record keeps increasing it would be good if have any OOB parameter that could be used for getting the dynamic count which could be used in the logic which sets the sys_parm_limit.
For the 2nd scenario mentioned above, it can be acheived using the aggregate API.
Please refer below doc.
https://docs.servicenow.com/bundle/vancouver-api-reference/page/integrate/inbound-rest/concept/c_Agg...
Kindly mark this solution as helpful if it helps you or mark as solution accepted.
Regards,
Shubha S
