Forum Discussion
Architecture Help for Internal reporting using PowerBI
Hi icassiem
Here is the clarification regarding your questions:
1) Multi-Region CentralizationThis is not required as a separate component but as a design choice. If MWAA in the Analytics account centrally pulls from both regions into the home region’s S3 silver layer, there is no need for additional S3 Cross-Region Replication. Simply choose one mechanism per source type: CRR or MWAA pulls for files, read replicas/snapshots/DMS or secure connections for RDS, and direct API calls from MWAA for APIs.
2) Staging & Data Layout
Yes, silver and gold represent the first and second transformation stages, but the bronze layer should not be skipped. Bronze holds immutable, append-only raw data for audit and replay. Silver stores cleaned and typed Parquet data created from bronze, and gold is optional for analytics extracts or Athena access, with Aurora serving as the main semantic layer. MWAA orchestrates all transitions (bronze → silver → gold).
3) Aurora Auto-Read from S3
Aurora does not auto-ingest from S3. Loads must be scheduled through Airflow using the aws_s3.table_import_from_s3 function or COPY/psycopg2 tasks. The DAG should include partition discovery and idempotent markers to only ingest new partitions reliably.
4) Aurora vs RDS for Semantic Layer
Both integrate with Power BI, but Aurora PostgreSQL Serverless v2 is preferred for auto-scaling, faster failover, and reduced operational overhead. RDS PostgreSQL is a good fit only if workloads are predictable and consistently low-cost. In either case, Airflow manages refreshes and transformations.
5) EC2 Gateway for Power BI
A Windows Server–based EC2 is required for the On-Premises Data Gateway. A t3a.large (2 vCPUs, 8 GiB RAM) is a safe default; smaller workloads may use t3a.medium (4 GiB). Place the instance in a private subnet, allow only outbound internet through NAT, use gp3 EBS (50–100 GB), and consider clustering for high availability.
6) dbt vs Python
dbt Core is recommended if you want a SQL-first approach with lineage, testing, and documentation. It runs well within Airflow but is not AWS-native. If transformations are lightweight and Python-based, Airflow with Python/SQL alone is sufficient. For a GUI or low-code option, AWS Glue DataBrew can complement the stack and be triggered from Airflow.
Regards,
Karpurapu D.
jaineshp would your reply of earlier be your response to my latest reply/request (30min ago)? Please advise, brainstorm and scrutinise my thinking as I will start putting in place the 3 options. I prefer links to back my case to show my homework done, etc..
Hey icassiem,
Thank you for expanding on the requirements—it really helps clarify the situation you're working with. Here's how I'd refine and validate the three options further, based on your latest inputs:
Confirmation: Yes — My Previous Reply Stands, With Some Refinements
Your thinking is very sound — especially your push for:
Central SQL-based data views
Alerting and monitoring
Avoiding PySpark/Lambda-heavy dependencies
Building a scalable, centralized data layer
That said, below is a revised breakdown with some scrutiny, suggestions, and extra links to strengthen your case when pitching to leadership.
Option 1: AWS Step Functions + RDS (PostgreSQL) + S3 + Athena
Still my top recommendation based on cost, flexibility, and simplicity
What works well:
RDS PostgreSQL gives you clean, view-based modeling with SQL (great for BI self-service)
Step Functions offers job orchestration without coding everything from scratch
You can still archive to S3 and query via Athena when needed
Very low Lambda dependency — just use them if absolutely necessary as small wrappers
SNS + CloudWatch for failure alerts and visibility
Scrutiny:
Step Functions state machine debugging can get tricky for large pipelines — use visual workflows & logging
RDS scaling should be monitored, especially with concurrent Power BI users (add read replicas if needed)
Resources to back it up:
Step Functions Intro
Using RDS PostgreSQL for BI
Athena CTAS & Views
Option 2: MWAA (Managed Airflow) + RDS + S3
Use if orchestration complexity will increase soon (e.g. parallel APIs, retries, conditional workflows)
Pros:
Airflow gives you granular DAG-level visibility, retries, logging
You can modularize your logic and control retries per task
Can be extended later to trigger ML models or external systems
Still SQL-centric with PostgreSQL as core transformation layer
Watch out for:
MWAA is more expensive to run even for small loads
Setup time is more than Step Functions, and operational overhead is real
Might feel like "overkill" unless workflows become more complex than they are now
Resources:
MWAA on AWS
Airflow DAG Concepts
Airflow + RDS use case
Option 3: Athena-Only with Better Scheduling + Metadata Layer
Viable if budget is the main blocker, and you don’t anticipate high data transformation needs
Strengths:
Zero infra — Athena on S3 is very cheap and scalable
You can define business views using SQL (via CTAS or Views)
Simple scheduling with EventBridge or Step Functions + Lambda/Python jobs
Weaknesses:
No central orchestration: managing failure, retries, dependencies becomes painful
Not ideal for layered business logic across tables (joins can get slow at >2TB)
Lack of state or metadata tracking without extra tooling (e.g., no “which client failed last?” out of the box)
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer
jaineshp , v-karpurapud , rohit1991 , apologies for a simple questiona, are there no additional perceptives to my updated response & jaineshp answer yesterday?
As i want to compile the best 3 options and want to be 200% confident everything and best solutions is provided. Putting my neck out here i would be implementing the archtetcure too so i really need to be sure i built a futurastic and modern archetcture, one that i can be proud of and comfrotable meets the objective and provides visible stakeholder change/improvements
jaineshp the option 1 of: AWS Step Functions + RDS (PostgreSQL) + S3 + Athena is very similar to the current legacy of AWS step function > glue > lambda > crawlers > s3 > athena > ec2 gateway
the symantic layer of view are probably in the postgress but the job management similar to sql job agent is missing where all jobs and tasks are mangeed with alerts etc,, i miss SSIS/Daat factory 🙂 thought there was tool integrated in AWS (DBT example, dont know) that could manage the space of scheduling/orch > etl > data management > build semantic layer > power bi connectthe option 1 also misses the key factor of the (2) multiple regions that we want to centralize and denomrlize the data from client api to various db stages for self service, so that we dont dupl the enviornment per region
Sorry for asking againHi icassiem
Thank you for getting back with us. To design a modern, scalable, and AWS-native internal reporting platform that eliminates current architectural pain points and supports centralized, self-service BI with Power BI, the most suitable approach is to build a centralized, SQL-driven architecture with simplified orchestration, data modeling, and governance.
Option 1: MWAA + RDS (PostgreSQL) + S3 + Athena — Future-Ready Architecture
This architecture presents a fully centralized, SQL-driven, and scalable solution designed for long-term growth and operational efficiency. AWS Managed Workflows for Apache Airflow (MWAA) is leveraged to orchestrate API ingestion, data transformation, retries, and alerting, offering complete visibility and control through DAGs. Raw data is stored in Amazon S3, while business logic is modeled using PostgreSQL views, which can be directly consumed by Power BI for reporting and analytics.
This approach eliminates the need for complex Lambda-based logic, supports multi-region data handling via S3 replication, and ensures centralized job management and failure tracking. It is particularly well-suited for organizations anticipating data growth beyond 2TB and seeking robust orchestration, SQL-based modeling, and future scalability.
Option 2: Step Functions + RDS (PostgreSQL) + S3 + Athena — Lightweight & Cost-Effective ArchitectureThis architecture offers a streamlined and cost-efficient solution for orchestrating data pipelines using AWS Step Functions. It enables lightweight orchestration of client API loops, data transformations, and alerting through CloudWatch and SNS, while storing raw data in Amazon S3. Business logic is modeled using PostgreSQL views, which can be directly accessed by Power BI for reporting and analytics.
The solution provides visual workflows, built-in error handling, and scheduling capabilities with significantly lower operational overhead compared to MWAA, making it ideal for smaller teams or simpler workloads. It supports centralized, SQL-driven modeling without the complexity of Airflow, and is well-suited for organizations prioritizing simplicity, low-code orchestration, and cost efficiency.Estimated Monthly Cost: Approximately $150–$250
Option 3: Athena + Glue DataBrew + EventBridge — Minimal & Budget-Friendly ArchitectureThis architecture is designed for teams managing small datasets with minimal orchestration requirements, offering a highly cost-effective and simplified solution. AWS Glue DataBrew enables no-code data preparation, while SQL-based modeling is achieved through Athena Views or CTAS (Create Table As Select). Scheduling is handled via EventBridge or optionally Step Functions, depending on workflow complexity. Although this setup does not provide full orchestration or built-in job monitoring, it is well-suited for use cases where simplicity, low maintenance, and budget constraints are key considerations. Manual intervention may be required for failure tracking and metadata management, but the overall operational overhead remains minimal.Estimated Monthly Cost: Less than $100
I hope this information is useful. If you need more assistance, please provide additional details.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.v-karpurapud , jaineshp , rohit1991 Thank you very much, and apologies for the silly questions here as I need to ensure I highlight the pros/cons when presenting
Option 1: MWAA + RDS (PostgreSQL) + S3 + Athena — Future-Ready Architecture
Option 2: Step Functions + RDS (PostgSQL) + S3 + Athena — Lightweight & Cost-Eff Arch
Option 3: Athena + Glue DataBrew + EventBridge — Minimal & Budget-Friendly Architecture
QuestionsA. Is the MWAA the orchestrates and ETL to extract sources (rds mysql/post, api, s3, mongodb), how would it handle data manipulation like we used pyspark/pyhton (denormalise, api loop, manipulate etc) and this eliminates Glue?
B. The raw data is stored in S3 and logic in postgresql views - does this mean that psotgresql and not athena connects directly on the S3 - what about the unstructred daat etc, i thought postgresql only works on rationalised data structures?
C. If the data is stored in S3 i would not be able to store snapshot data overtime correct, meaning it will be driven/refreshed from source only?
jaineshp with MWAA i looked into this some time back, and the only reason is i was looking for a tool that has many options packed, like v-karpurapud mentions it can do api loops/data manipulation etc hence i mentioned for example only DBT "i dont know" then i dont need different places to track etl/glue and step/functions for orch and then still pyspark/python to transform manipulate/loop lamnda api calls, but the job scheduling is very simple and basicAlso the elements we missing, are the 2 regions centralised & gateway - we dont want to replicate the environment on both regions, plus is the EC2/Gateway within the VPC needed as the prod product resides in same VPC hence security was not keen
Apologies, please help and provide a bit more detail, i recevied some more clarity yesterday it must be a native AWS self service env on possibly a transformed DB/S3 storage for data snapshot/or not "con if not" and semantic layer with views because the objective might in future expand for sharing data to clients via api & product reporting, the current objective is a draft strategy for internal PBI reporting but i know stakeholders expectations as theres already misalignment. I want to ensure it is simple (to dev/manage/alerts etc), cost effective (+/- $1000 monthly target), modern (design, data flow, tools etc) and yet Expandable, like the data storeage of Postgresql and the MWAA orch/etl I think cost is roughly $150-200 monthly, but if this is the key orch+etl+data maniputlation it is essentailly half the data flow i think i can justify it, but it must make my life much easier as i implement, future develop and maintain.
Me thinking out loud here 🙂
I want to narrow it down to 2 best recommended options, one with S3 storage for internal reporting on current data only with overwrites, no data snapshots, track over time or benchmarking etc data and the other a more upscale one of db store etc ?I think the challenge outside of the tools etc is the simplicity of dev.manage with data centralization