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.
I am about to start with the strategy, and I want to present three options.
Azure & data warehouse environments like Redshift, Snowflake are not an option, our organisation spent a lot of money migrating to aws over the past 2 years
i need something better than what i currently have of an job schedules > glue > lambda > pyspark api loop > s3 > athena data lake > (no views or manipulation) as i need to prove why we need to move for a centralised self service space where users access the managed dataviews only for writing powerbi reports
if athena remains the best option, how do i expand and improve as i need a job management, exception hanlding/notification. was hoping there was something better than GUi glue, i also dont see the athena data view enviornment. Also note i know it is under 1tb at the moment but what if in 2 years it grows to 2TB for the Hybrid Push Strategy
Please guide me, please provide the 3 best options and in its order you highly advise please, with some detail and as to why/benefits links etc, even roughly cost brackets. the reason why i struggle with glue is that we use lambda's which i dont want to use or pyspark scripting as i need to then use a vendor which we do now and my space is small and dont need his dependency, i want to insert api and loop per table config example to get client api data for all clients and the a data management to manipulate the data, so do i go rds postgress or mysql or ? i would like snowflake or redshift but my datasize does not justify that cost as the purpose is purely internal management reporting on product with roughly 20 clients, data is small but i need to be able to build bussiness logic datasets with sql or ?
please help, i want to place the 3 options over a table to compare and provide my recommendation of the option priority, so that i can be open minded to my CTO that i present this best robust solution as my sources are rds, product api per client (loop), i need alreats and need views to mniputlate and track failures
sorry i know i am asking a lot, but me searching the net i will come know where close to whats best and i really want to add value in my new organization
Hey icassiem,
Thank you for the kind recognition - always happy to contribute to our community's success!
Best Regards,
Jainesh Poojara | Power BI Developer
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 simplicityWhat 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 & ViewsOption 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 caseOption 3: Athena-Only with Better Scheduling + Metadata Layer
Viable if budget is the main blocker, and you don’t anticipate high data transformation needsStrengths:
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 Developerjaineshp , 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 again