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 , v-karpurapud , rohit1991 Hi, any update on my last reply above, please?
For security, do I set up a separate account within the one region but outside of the product VPC so that i can run MWAA/glue accross regions into the one database and then it is easier/less risk and answer the security officers push back, to expose the db for users to connect to the db from their pc and dont require to connect to an EC2 to access the db+semantic layer of views?
also can the MWAA/glue do ingest/access as its mostly api/rds across the regions+accounts to the one region new account outside of prod/source data?
if i keep it the one account same a prod product and one region i cant expose the db and have to let uses connect to the EC2, which is not really ideal for Self service cntralised environement
please help
Hi icassiem
The cleanest way to meet your security requirements and still centralize reporting is to run everything in a dedicated AWS account set up specifically for analytics. This account would sit in one region and host all of your reporting infrastructure: MWAA or Fargate to handle orchestration and API ingestion, a central S3 bucket for raw and transformed data, and an Aurora or RDS instance to hold your curated datasets and semantic views. Keeping this separate from your production accounts isolates any reporting activity from operational systems, reduces security risk, and gives you a clear, compliant boundary for data access.
Data from both regions can flow into this account without replicating the entire environment. In a push model, small extract jobs in each source account write directly into the reporting S3 bucket using a cross-account IAM role that only allows that one action. In a pull model, MWAA or Fargate in the reporting account uses STS to assume a read-only role in the source accounts and fetch from APIs, RDS replicas, or S3. Either way, all transformation, denormalization, and business logic happen in the reporting account, so you’re no longer relying on Lambda/PySpark or juggling jobs across multiple regions.
Analysts would connect to the curated database in a way that satisfies security without forcing an EC2 gateway. The easiest path is AWS Client VPN, which gives them private network access to the reporting VPC and supports corporate authentication, or PrivateLink if you prefer to route through your existing corporate network. The database stays private, fully inside AWS, and only authorized users can reach it. This keeps the design AWS-native, simple to manage, cost-controlled, and flexible enough to expand later with features like historical snapshots or client-facing APIs when the business is ready.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.
v-karpurapud Thank You so much, i really apprectiate your patience, knowledge sharing and efforts to helping and guiding me. Initially i thought presenting 3 options, but this one sounds/feels like the best fit. Thank You
v-karpurapud Thank you for your patience
Please check if I understand it correctly, as I have a few ques, the proposed architecture flow is:
1. A Separate AWS account "Analytics" from the Product Account in the one main region
2. Setup Analytics account read access to the two Regions Prod (rds, api, s3)
3. MWAA or Fargate to both job orch + ingest, no need for glue (cross region RDS, API, S3)?
> your recommendation that provides simplest/best gui dev?
> can this do loops of api with params?
4. Source destination of ETL = Stageing S3 Bucket
5. Data Transformation to (Aurora or RDS)
> what is your preference on the DB for denormalise/user access layer/works best for PBI?
> how would i automate/schedule transformation from the S3 Stageing to Semantic Layer of (Aurora or RDS) tables, could i shcedule sql scripts/procs on (Aurora or RDS) to read the staging S3 then write to a table or schedule or a tool/DBT etc?
> I think Aurora +RDS can be serverless, im just thinking which one offers the best sql tramsformation jobs becuase my senior does not want views/dw etc?
6. User Connectivity & report development directly to DB and not EC2
> No EC2 connect ofr db connectivity
> Setup in Analytics acc security of AWS Client VPN or PrivateLink> which one you recommend for users connecting from desktop to the RDS/Aurora?
7. Don't we need a On-Prem Gateway on a EC2?
Regards
v-karpurapud , jaineshp , rohit1991 any update to my response of yesterday: proposed architecture flow please?
Hi icassiem
Apologies for the delayed response. Kindly review the following recommendations:
1. Target Landing Zone & Security
Establish a dedicated Analytics AWS account within a single home region, implementing cross-account, least-privilege IAM roles for read-only access to source accounts and regions (APIs, RDS replicas/snapshots, S3). This approach separates analytics from production and streamlines auditing processes.
2. Orchestration & Ingestion
Utilize MWAA (Managed Airflow) for centralized orchestration and ETL, leveraging DAG-based scheduling, retries, logging, and alerts. API loops can be managed with PythonOperator/HttpHook, supporting Python, SQL, or dbt jobs. For smaller pipelines, Step Functions with Lambda is a suitable alternative.
3. Staging & Data Layout
Organize data in S3 using a bronze/silver/gold Parquet model: bronze for immutable raw data, silver for cleaned and typed data, and gold for analytics-ready extracts, including Aurora and optional Athena views.
4. Transform & Semantic Layer
Leverage Aurora PostgreSQL Serverless v2 for curated tables and light transformations, loading data from S3 via aws_s3 extension or Airflow COPY. Store the gold layer as materialized tables with thin views for Power BI integration.
5. Snapshots / Time Travel
Maintain bronze data as append-only in S3 and implement SCD2 or daily snapshot fact tables in Aurora to support history and audit requirements.
6. Multi-Region Centralization
Centralize hosting in the home region and ingest cross-region data using S3 replication, Airflow STS AssumeRole pulls, RDS read replicas, or centralized API calls.
7. User Connectivity & Power BI
Enable Power BI Desktop access via AWS Client VPN, and Power BI Service through a small EC2 Windows Gateway in the Analytics account or via on-prem VPN. Ensure no public database exposure.
8. Cost Guardrails (≤$1,000/month)
Estimated costs: MWAA $150–$250, Aurora several hundred, S3 + Athena tens, EC2 Gateway $30–$60, all well within the target budget.
9. Two Curated Options
Lean: Overwrite silver/gold layers, no long-term snapshots; minimal overhead and rapid implementation.
With History: Append-only bronze, dbt lineage/tests, SCD2/time series for comprehensive audit and historical tracking.10. Implementation Checklist
- Set up Analytics account and private VPC; configure IAM roles.
- Deploy MWAA and Airflow DAGs; define bronze/silver/gold layers in S3.
- Load data into Aurora; optionally integrate dbt Core.
- Configure VPN and Gateway; publish secured Power BI datasets.
Regards,
KarpurapuD.v-karpurapud Awesome, Thank You
Please Last few questions:
1. Is "6. Multi-Region Centralization" required, if 2. Orchestration & Ingestion pulls from LZ region and the region 2, meaning the first layer silver extracts pulls from the main region both regions source data of ( S3, API, RDS)?
2. For "3. Staging & Data Layout", the layers are Silver S3 = the First Stage extract from Source with MWAA (DAG/API Python) orch/ingest to Stage , Gold S3 = Second transformed/manipulate from Silver with MWAA orch/ingest (DBT/Python)?3. Then Aurora/PostgreSQL only automatically Reads the refreshed Gold S3 directly via (aws_s3 extension/Airflow COPY) as user connect layer?
4. For 4. Transform & Semantic Layer, is your preference between Aurora/PostgreSQL in the sense of Point 4 easily automatically reads/refreshes from Gold layer and ease of PowerBI connect?
5. For the EC2 gateway, i think it requires a windows server - is there a preference EC2 type/size?6. Also is DBT worth it or stick to the python, was looking for gui interface that i dont depend on external partners but i though DBT was purely for Data Warehouse and is it native integrated in AWS? your preference please?
@v-karpurapud please any update on my final questions yesterday/above, to finalize my understanding please
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.Hi icassiem ,
Glad to hear it helps. If you have any other queries, please feel free to raise a new post in the community. We are always happy to assist you.
Thank You.