If I'm doing simple aggregation dashboards, what's the minimum amount of data which justifies building a full blown data processing pipeline? Is it reasonable to build a complicated pipeline with Mysql -> Hadoop -> Redshift and then analytics and visualizations on top of redshift if my database only has about 10million transactions per month. Would it be better to just run something like ReDash visualizations on top of my unaggregated data in MySql?
You should only complicate it as much as necessary to meet your requirements.
- What kind of aggregations are you doing? Most DBs can handle basic counts pretty easily. 10 million in a month sounds like something a large MySQL instance could handle.
- How quickly does everything have to update? If the users only expect the summarized data to be updated monthly, you have a lot of time. If you have to update it more often, it might make sense to have MySQL collecting it but Redshift presenting it. Redshift's analytical focus and columnar storage means you may not need pre-aggregated data until your volume gets much higher than what would require aggregation in MySQL. I was using a 4 node Redshift cluster with the smallest instance types and it could do a count across >300 million rows in 17 seconds.
Can you not build aggregate tables in MySQL? What is making you want to put Hadoop in between MySQL and Redshift?