I did this a while back at my previous employer/startup (it got acquired), we used AWS, PostgreSQL, Hadoop/Hive, using SQL + custom Ruby script for ETL + processing. You can read some of that writing here https://engineering.viki.com/blog/2014/data-warehouse-and-an...
If I can distill them into bullet points, there probably are:
- SQL is great, stick with it.
- We started with PostgreSQL and scales a long way with it (using techniques like table partitioning, unlogged table, etc), then slowly split into Redshift, and later Hadoop/Hive.
- Handling events/behavioural data (JSON/semi-structure at large volume) are very different from handling transactional data (structured, lower volume).
- Take a more lean/incremental approach to it: get a basic DW setup, load data that you can immediately act on, act on it (build reports, run analysis, show to management), then repeat.
You can also check out my startup: https://www.holistics.io , where I turned those experiences above into a data platform that automating DW + BI.
We allow you to do "lean ETL" on top of customers' DW infrastructure (be it Redshift, PostgreSQL, BigQuery or others). We work with small startup to unicorn tech companies.