We have Appsync as our primary db. We are using Opensearch for aggregarion, visualisation and reporting purposes. But the Opensearch is showing some data anamoly like the values showing sql aggregation query is different from what coming on the visualised dashboard of opensearch. Also, the sql plugin of the opensearch has certain set of limitation that are not letting us to use the full potential of it. Like, if we put a limit of more than 10000 to fetch the records we are getting an error of "Shards failed". Also, the aggregations are not working properly on more than 2 joins and there are various other such limitations.
Currently we are using t3.medium.search instance with 5 nodes.
Can anyone please help me out with this.