Designing Azure reporting solution for PostgreSQL database

An app team in our company reached out to us asking for help in designing a reporting solution hosted in Azure. Their business requirements are

1. Send data from on-prem PostgreSQL database to Azure on daily basis.
2. Build reporting solution on top of the data sent from on-prem database.

We can go with different design approaches for this requirement. But, the challenge lies in choosing the optimal approach which addresses all the business requirements and probably costs less.

In this blog post, you can know about the different design approaches we developed for the app team.

Design approach 1:

In this design approach, we build ADF copy pipelines to get the data from on-prem PostgreSQL database to Azure database for PostgreSQL. Then, we can build reporting solution on top of the Azure database using Power BI.

Pros:

1. Easy to implement.
2. Probably costs less.

Cons:

1. If schema changes in on-prem database, ADF pipelines and Azure database have to be updated.
2. This design addresses only the current requirement, and may not be suitable for future enhancements or changes. E.g., If the app team wants a data warehouse solution in future involving the same database, a new reference architecture would be needed.

Design approach 2:

In this design approach, we build ADF copy pipelines to get the data from on-prem PostgreSQL database to ADLS Gen2 storage account (in parquet file format). From there, data goes into Synapse Analytics SQL pool. Then, we can build reporting solution on top of the SQL pool using Power BI.

Pros:

1. This design handles future business requirements or enhancements.

Cons:

1. This solution would cost more.
2. This solution may not handle the schema changes at the source (Power BI reporting will fail if the parquet files generated on daily basis have different schema in them).

Design approach 3:

In this design approach, we build ADF copy pipelines to get the data from on-prem PostgreSQL database to Cosmos DB container. From there, data goes into Synapse Analytics SQL pool through Synapse link (check out my old blog post about Cosmos DB Synapse link). Then, we can build reporting solution on top of the SQL pool using Power BI.

Pros:

1. This design handles future business requirements or enhancements.
2. Cosmos DB is schema agnostic. So, no updates would be needed in Azure when schema changes happen in the source on-prem database.

Cons:

1. This solution would cost more.

Disclaimer: The posts here represent my personal views and not those of my employer or any specific vendor. Any technical advice or instructions are based on my own personal knowledge and experience.