Forum Discussion
Looking for guidance on designing an Azure data analytics pipeline for reporting
I’m working on modernizing an old reporting workflow that currently runs on a few on-premises databases and scheduled scripts.
The current process collects operational data from multiple systems, performs some basic transformation and aggregation, and then generates reports for different business teams. As the data volume is growing, the existing setup is becoming difficult to maintain and slow to refresh.
I’m looking for an Azure-based architecture that can ingest data from different sources, store both raw and processed data, run scheduled transformations, and make the final datasets available for reporting tools like Power BI.
Would appreciate any suggestions on the recommended architecture, especially around data storage, transformation, refresh performance, and cost control.
Thanks
3 Replies
Hi Selina,
I agree with the recommendations above. One thing I'd add is to separate your architecture into ingestion, storage, transformation, and serving layers rather than building a single end-to-end pipeline. That makes the solution much easier to scale and maintain over time. I'd also recommend implementing incremental loads or Change Data Capture (CDC) wherever possible instead of full refreshes. As data volumes grow, this can significantly reduce processing time and compute costs.
Lastly, don't overlook governance and operations. Services like Microsoft Purview for data cataloging and lineage, Azure Key Vault for secret management, and Azure Monitor/Log Analytics for pipeline monitoring can save a lot of operational effort in production.
If you're building a new analytics platform today, Microsoft Fabric is definitely worth evaluating because it brings ingestion, transformation, storage, and Power BI into a unified platform. If you're already invested in Synapse or Databricks, those are still excellent options, the best choice often depends on your team's skills, existing architecture, and licensing.
The modern way I thought, learn.microsoft.com/en-us/azure/architecture/example-scenario/analytics/enterprise-bi-microsoft-fabric
- Start with Fabric Lakehouse for raw + curated data.
- Use Dataflows Gen2 for scheduled transformations.
- Build semantic models in Power BI for consistent reporting.
- Enable Direct Lake mode to improve refresh speed and reduce costs.
Hi, for a modern Azure reporting pipeline I would usually start with ADLS Gen2 as the landing zone and keep a bronze/silver/gold layout for raw, cleaned, and curated data. Use Data Factory or Fabric Data Factory for ingestion, then Databricks, Synapse, or Fabric notebooks depending on your team skills. Put secrets in Key Vault, monitor the pipelines with Azure Monitor, and keep Power BI pointed at curated tables instead of raw operational data.
