For Reporting/data warehouse purposes we plan to populate data in star schema.
All our current data resides in Redshift, Since star schema follows traditional relationships between tables. should I opt for an RDBMS or building star schema on Redshift itself ?
Can somebody explain the pros and cons of choosing RDBMS vs Redshift for Star Schema?
Both will support a star schema. Since your data is already in Redshift, you eliminate extra work and the risk of data loss/corruption of moving your data into a SQL database by staying with that platform.
How your current data is organized in Redshift, the amount of data and the type of queries you will run may impact query performance. As you did not start with the intent of a star schema, your Redshift instance may not be optimized for a star schema. Amazon has a blog post on this here. What tips you can implement depends on your current environment, but at a minimum you should be able to use the data compression and workload management suggestions. This guide to Redshift architecture and capabilities may also help you implement the Amazon optimization suggestions.