Need advice on architecture and pipelines
I recently transitioned into a data analytics role where I report directly to the CFO. Before this, I worked in a sustainability consultancy as an analyst/consultant, so I’ve used tools like Power BI and Tableau quite a bit.
At my current company, everything runs on the Zoho ecosystem; CRM, Books (5 - 6 orgs), etc. And they use Zoho Analytics. The problem is it is limiting.
• Data transformation is painful
• Zoho DataPrep is basically unusable at scale because of quota limits (one job eats a huge chunk of the monthly allowance)
• Query tables are restrictive (limited support for CTEs, no real flexibility for complex transformations)
On top of that, we also have multiple external data sources:
• Zoho Marketing and Meta ads, etc.
• Restaurant POS systems
• Other operational tools
Management now wants a unified view across everything. It makes the current setup even more frustrating.
So I’m trying to move us toward a more modern data stack but, I’m a bit overwhelmed. There are so many moving parts: ingestion tools, data warehouses, data lakes, transformation layers, etc.
What I think I want:
• Extract data from Zoho apps (CRM, Books, People, etc.) + external sources
• Load it into a central warehouse
• Do proper transformations + data modeling there
• Then connect it to Power BI for reporting
But I’m not sure what the “right” architecture looks like, especially coming from a BI-first background.
Would really appreciate advice from people who’ve dealt with similar setups:
• What tools would you recommend for ingestion?
• What warehouse would make sense for a mid-sized company?
• How would you structure the transformation layer?
TL;DR:
Moved into a data analytics role using Zoho stack, but it’s too limiting for transformations and scaling. Need advice on building a modern data pipeline (Zoho + other sources → warehouse → transform → Power BI) without overcomplicating things.