Architecture Help for Internal reporting using PowerBI

Hi icassiem 

Here is the clarification regarding your questions:

1) Multi-Region Centralization

This 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.