Data Environment Options Follow-Up

Hi icassiem ,

1. ERD / Architecture Example.

AWS (API / S3 / RDS / Mongo)
|
Fabric Notebook (Python ingest + transform, remove PII)
|
Lakehouse Tables (Silver Layer only)
|
SQL Views (Semantic Layer)
|
Power BI (Direct Lake / DirectQuery)

Ex: fact_usage_metrics, dim_client, dim_product and dim_date

2. Motivation + Risks: Replace Azure Function ($150) and Potential DB / ETL tools with single Fabric capacity.

Note: Reusable data for reports, Centralized semantic layer and Shared across consulting + clients. Risks you must call out, Capacity limits (F2) as Small compute (2 CUs) Can slow if too many users and large joins

Solution: Pre-aggregate data and Small models

3. Medallion — Do you need it?

Try below architecture.

API --> Python (clean + aggregate + remove PII) --> Silver tables
|
SQL Views (Gold)

Note: No Bronze (save cost), Silver = your storage and Gold = SQL views (no extra storage)

4. Orchestration: Best option is Fabric Pipelines, You can Schedule notebook runs, Retry handling and No extra cost (included in F2).

5. Query using SSMS: YES , this is supported, Please refer below steps.

Go to Lakehouse
Open SQL Endpoint
Copy connection string
Connect via SSMS (SQL Auth / AAD)

Note: Fabric exposes T-SQL endpoint over Lakehouse

6. Dev Access + Power BI Connectivity:

Development: You can use Fabric web portal (primary), VS Code (optional for notebooks) and Git integration available.

Power BI: Direct Lake Connect to Lakehouse tables and No gateway needed.

Note: Gateway only needed if you connect to on-prem data.

7. Why NOT AWS RDS:

AWS RDS --> Operational DB not analytics, Power BI , RDS --> Security risk (prod exposure), Scaling --> Expensive + manual, Transformation -->No built-in ETL.

Note: Fabric contains Separate analytics layer, No impact on prod, Built-in ETL + BI and Lower total cost.

8. Copilot usage: Works in Power BI (narratives, DAX, Q&A) and Fabric notebooks (code assist)

Note: It is not full pipeline automation and Not replacing ETL. It is a“Enhancement layer, not core architecture”.

9. Python vs SQL roles: Python Ingest APIs, Transform, Aggregate and Remove PII. SQL contains Views, Joins and Semantic layer.

10. Ingestion Pattern (overwrite vs CDC): Simple approach (recommended for F2) Overwrite or append and Use timestamps. Better approach: Incremental loads (by date) and Partition tables.

Note: CDC Overkill for your case and Requires more complexity. You do NOT need Azure Data Factory. No extra infra needed Everything is SaaS and no hidden infra costs.

11. Backup Plan (Power BI only approach): Yes you can, but No central data store, Repeated API calls, Not reusable and Not scalable.

Note: It acceptable only for Very small MVP only. Power BI-only creates datasets and is not a reusable data platform.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh