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