Architecture Help for Internal reporting using PowerBI

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 , 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

v-karpurapud 

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
Questions

A. 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 basic

Also 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

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

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

1. Set up Analytics account and private VPC; configure IAM roles.
2. Deploy MWAA and Airflow DAGs; define bronze/silver/gold layers in S3.
3. Load data into Aurora; optionally integrate dbt Core.
4. 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 ,

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.