How would you architect an enterprise RAG + Text-to-SQL solution using Microsoft Fabric?

Hi Pradyumna,

Your existing router-based design is a good foundation. I would use Microsoft Fabric mainly as the governed data, transformation, analytics, and orchestration layer rather than replacing every component of the application.

A possible enterprise architecture could be:

User/Application → FastAPI or Agent Router → Document RAG / Structured-Data Agent → Azure OpenAI → Final Answer

On the Fabric side:

1. Document ingestion and preparation

Use Fabric Data Factory pipelines to ingest documents from SharePoint, Blob Storage, APIs, or databases into OneLake. Store raw documents in a Lakehouse, then use Fabric notebooks or Spark jobs for:

Text extraction and cleaning
Metadata enrichment
Document classification
Chunking
Duplicate removal
Embedding generation

Microsoft’s Fabric RAG guidance demonstrates storing content in a Lakehouse, processing and chunking it with Spark, generating embeddings through Azure OpenAI/SynapseML, and using Azure AI Search as the vector index.

2. Vector retrieval

For production RAG, I would generally use Azure AI Search rather than treating the Lakehouse itself as the vector-serving layer. Azure AI Search supports vector and hybrid retrieval, filtering, ranking, and RAG-oriented retrieval patterns. A OneLake indexer can also index Lakehouse files, detect updated content, and perform integrated chunking and vectorization.

3. Structured-data querying

Use a Fabric Warehouse when the data is curated, relational, and intended for governed BI or SQL analytics. Use the Lakehouse for raw, semi-structured, historical, or data-science-oriented data. In many enterprise solutions, both are useful:

Lakehouse: raw and transformed documents, logs, ML datasets and Delta tables
Warehouse: curated dimensions, facts, business rules and SQL-facing datasets

Fabric Data Agents are especially relevant here. They can route natural-language questions to Lakehouses, Warehouses, Power BI semantic models, KQL databases and other supported sources. For SQL sources, the agent generates, validates and runs read-only T-SQL through the SQL analytics endpoint.

4. PostgreSQL

I would not automatically replace PostgreSQL. Keep it when it serves operational transactions, application state, low-latency CRUD operations or existing business services. Fabric would normally complement it by ingesting or mirroring operational data for analytics and AI.

Replacing PostgreSQL could make sense only when its workload fits Fabric SQL Database or Warehouse requirements. Fabric SQL Database now supports AI application patterns involving Azure OpenAI, vector embeddings and RAG, but the choice should be based on transactional requirements rather than platform consolidation alone.

5. Orchestration and governance

I would use:

Data Factory pipelines for ingestion and scheduling
Lakehouse and OneLake for document and analytical storage
Notebooks/Spark for chunking, preprocessing and embeddings
Warehouse or Fabric SQL Database for structured data
Azure AI Search for production document retrieval
Fabric Data Agent for governed natural-language access to structured data
Azure OpenAI for routing, generation and response synthesis
Power BI for evaluation and operational monitoring

One important design principle is to keep the router outside the retrieval systems. It should classify the request as document-only, SQL-only, or hybrid. For hybrid questions, retrieve both document evidence and structured results, then ask the model to generate one answer with source citations.

For enterprise readiness, I would also add:

Microsoft Entra ID authentication
User-level authorization before retrieval
Row-level and object-level security
Prompt and query logging
SQL allow-lists and read-only execution
Retrieval and Text-to-SQL evaluation datasets
Citation validation
Cost, latency and token monitoring

Your current FastAPI layer can remain the application-facing API while Fabric becomes the governed data and analytics foundation. I would begin with a small hybrid implementation: Lakehouse + Azure AI Search for documents, Warehouse/Data Agent for structured questions, and Azure OpenAI as the final response layer.

Hi User,

This is a great enterprise architecture question. One approach I've seen work well is to let Microsoft Fabric complement Azure AI services rather than replace them.

A typical architecture could look like this:

• Use a Lakehouse as the central storage layer for raw and curated documents (PDFs, Word files, JSON, etc.).

• Use Data Factory Pipelines or Dataflows Gen2 to ingest and transform data before generating embeddings.

• Perform document preprocessing and embedding generation in Fabric Data Science (Spark Notebooks) or Azure AI, then store embeddings in your preferred vector database (or another supported vector store).

• Use a Warehouse for structured business data and analytical SQL workloads, while keeping operational databases such as PostgreSQL if they're already part of your application.

• Use OneLake as the unified data layer so both structured and unstructured data are governed in a single platform.

• Orchestrate ingestion, model execution, and refreshes using Fabric Data Factory.

• Continue using Azure OpenAI for LLM inference, routing, and Text-to-SQL generation, with Fabric providing the governed data platform behind it.

Regarding your questions:

• Lakehouse is generally preferred for unstructured documents and feature engineering, while Warehouse is ideal for curated relational data and analytics. Many enterprise solutions use both together.

• Fabric doesn't necessarily replace PostgreSQL. PostgreSQL often remains the operational database, while Fabric serves as the analytical and AI data platform.

• For RAG, a common pattern is: Ingest → Clean → Chunk → Generate embeddings → Store embeddings → Retrieve relevant context → Azure OpenAI generates the response.

• Fabric excels at data engineering, governance, orchestration, and analytics, while Azure AI services handle LLMs, embeddings, and AI inferencing.

For more information:

• Microsoft Fabric documentation: https://learn.microsoft.com/fabric/

• Fabric AI and Data Science: https://learn.microsoft.com/fabric/data-science/

• Microsoft Fabric Lakehouse: https://learn.microsoft.com/fabric/data-engineering/lakehouse-overview

• Azure OpenAI documentation: https://learn.microsoft.com/azure/ai-services/openai/

Could you also clarify:

• Is your goal to build a production enterprise solution or primarily a proof of concept?

• Will your Text-to-SQL target only PostgreSQL, or do you expect to query Fabric Warehouses/Lakehouses as well?

• Are you planning to keep ChromaDB, or are you evaluating alternative vector s

  torage options within the Microsoft ecosystem?

I'd also be interested to hear how others are combining Microsoft Fabric with Azure OpenAI for enterprise-scale RAG and Text-to-SQL implementations, especially around governance, MLOps, and multi-agent architectures.