I am new to Terra Data. I have complex queries that are retrieving data from different tables and using different joins. Also, i have to apply some conditions to clean the duplicates and time period checks.
Would it be better to make views on the tables according to my requirements and then join the views and get my results. Because, in my case, it is about millions of records, so I want to optimize my queries and server load as well.
It may make it easier to query for the users, but would it be better to clean your data before putting it in the Datawarehouse? Its the option I would always go for. It is better to use CPU and resource getting the data right as you put it in the database, so CPU and resource can be saved when the users start to query it.
In general performance is never going to improve if you have complicated join predicates in your views. Its very hard for anyone here to give a firm answer though without understanding what the data looks like or the view.
We had/have issues with views that do not have predicates, and are almost identical to the base table apart from a cast function on one of the keys. Now that affected performance significantly, but made it easier for the users to write queries ;-)