Forum Discussion
Advocate IIIGold Layer Design for Fabric Data Agents: Denormalization vs. Semantic Models
Hi Fabric Community,
We're building a Microsoft Fabric data platform for an AI agent that investigates leads using Salesforce and Marketo data.
The agent will search across Lead, Contact, Account, Opportunity, Case, Campaign, and Marketo Activity data to summarize related opportunities, inquiries, engagement history, and other relevant information.
We are considering the following architecture:
- Bronze: Raw Salesforce and Marketo data
- Silver: Reusable integrated data with type conversion, deduplication, entity matching, Salesforce–Marketo integration, etc.
- Gold: Data curated for AI agent consumption
- Semantic Model: Defines relationships and business meaning across Gold tables
Our main question is how to divide responsibilities between denormalization in Gold and relationship modeling in a Semantic Model.
For Fabric Data Agent scenarios, which approach generally provides better accuracy and reliability?
- Pre-resolve joins in Gold and create wide denormalized tables combining Account, Opportunity, Case, Activity, etc.
- Keep these Gold tables relatively separate and define their relationships and business meaning through a Semantic Model
I understand the benefit of pre-resolving ambiguous joins in Gold. However, I'm concerned that denormalizing one-to-many relationships such as Account–Opportunity or Lead/Contact–Activity could introduce duplication, unclear table grain, and additional maintenance complexity.
We are therefore also considering:
Silver → AI-friendly Gold Views → Semantic Model → Fabric Data Agent
The Gold views would hide unnecessary technical columns and expose AI-friendly business fields, while relationships between tables would be defined in the Semantic Model.
I'd appreciate any guidance on the following:
- Is it effective to use a Semantic Model as the agent's "comprehension layer" while minimizing wide denormalized Gold tables?
- From an Fabric Data Agent accuracy perspective, how much join logic should still be pre-resolved in Gold?
Any examples or experience from real-world Fabric Data Agent implementations would be greatly appreciated.
Thank you!
Hi RMori95610
Great question, and your instinct is right. For Fabric Data Agent scenarios, the recommended pattern is Option 2 (leaner Gold + Semantic Model as the comprehension layer), not wide denormalized tables.
When a Data Agent is pointed at a Power BI semantic model, it uses an NL2DAX generation tool, and DAX is optimized for a star schema with clear fact and dimension tables. Microsoft's own semantic- model checklist explicitly flags this:"Use star schema… avoid flat, denormalized, or pivoted data structures." Wide, denormalized tables reintroduce exactly the problems you're worried about, ambiguous grain and duplication from one-to-many relationships (Account–Opportunity, Contact–Activity, which lead to confidently wrong answers rather than visibly wrong ones.
How to split responsibilities:
Silver → Gold: Pre-resolve only the things a relationship can't cleanly express: data-quality fixes, type conversion, Salesforce–Marketo entity matching/surrogate keys, and any genuinely ambiguous join logic. Keep dimensions (Account, Contact, Lead, Campaign) and facts (Opportunity, Case, Marketo Activity) as conformed star-schema tables. AI-friendly Gold views to hide technical columns and expose business-friendly names is a solid approach.Semantic Model = comprehension layer: Define relationships, explicit DAX measures (avoid implicit ones), business-friendly names, descriptions + synonyms, and default summarization. Then use Prep for AI (AI data schema, verified answers, AI instructions) to scope and guide the agent. This is where accuracy actually comes from, not from flattening.
On "how much join logic in Gold?": Only pre-resolve joins that are ambiguous or non-navigable via relationships (e.g., fuzzy SF↔Marketo identity resolution, multi-hop bridges). Straightforward 1:many relationships (Account→Opportunity, Contact→Activity) should stay as relationships in the model, letting the agent navigate them, don't bake them into wide tables. Also keep scope tight (Fabric caps ~5 data sources) and consolidate duplicate/overlapping measures so the agent isn't guessing between "Total Revenue / Net Sales / Gross Sales."
Your proposed flow: Silver → AI-friendly Gold Views → Semantic Model → Fabric Data Agent is the right architecture. Reserve Gold for cleansing + entity resolution, let the star-schema semantic model carry relationships and business meaning.
Hope this helps, would love to hear how the Salesforce–Marketo agent performs once it's live!
Reference:-
Semantic model best practices for data agent - Microsoft Fabric | Microsoft Learn
Fabric data agent creation - Microsoft Fabric | Microsoft Learn
6 Replies
- trivedisunita
Continued Contributor
Hi RMori95610 ,
Thanks for sharing this.
For this use case, I would keep the Gold layer structured with a clear grain rather than combining everything into one wide table just for the Data Agent.
For this scenario, the approach you mentioned looks reasonable:
Silver -> AI-friendly Gold tables/views -> Semantic Model -> Fabric Data Agent
Silver: Handle cleansing, deduplication, Salesforce-Marketo matching, and integration logic.
Gold: Keep clean, business-friendly tables/views with a clear grain.
Semantic Model: Define the relationships, measures, business terms, descriptions, synonyms, and Prep for AI configuration.
Anything complex or ambiguous is better resolved in upstream. For example, if matching a Marketo person to a Salesforce Lead or Contact requires specific business rules, I would handle that before the Semantic Model rather than leaving it for the agent to work out.
For simpler relationships like Account -> Opportunity or Contact -> Activity, one option is to keep the entities separate and define the relationships in the Semantic Model. This can help avoid unnecessary duplication and keep the grain of each table clear.
So, a simple way to think about it is- Resolve ambiguity upstream, but don't pre-join everything.
The Semantic Model can then provide the relationships and business context. With Prep for AI, you can also keep the schema focused on the tables, columns, and measures that are relevant to the questions you expect the Data Agent to answer.
Hope this helps clarify the approach.
Reference- Semantic model best practices for data agent - Microsoft Fabric | Microsoft Learn
https://github.com/microsoft/fabric-toolbox/blob/main/samples/data_agent_checklist_notebooks
RMori95610
Hi, trivedisunita,
Thank you very much for the clear and helpful explanation.The principle of “resolve ambiguity upstream, but don't pre-join everything” was especially helpful and made the division of responsibilities much clearer.
Based on your advice, we will move forward with the approach of keeping Gold tables/views at a clear grain, resolving ambiguous matching and integration logic upstream, and using the Semantic Model to define relationships and business context.
I also appreciate the references you shared. Thank you again for your help!
Asmita_27Resolver II
Hi RMori95610
Great question, and your instinct is right. For Fabric Data Agent scenarios, the recommended pattern is Option 2 (leaner Gold + Semantic Model as the comprehension layer), not wide denormalized tables.
When a Data Agent is pointed at a Power BI semantic model, it uses an NL2DAX generation tool, and DAX is optimized for a star schema with clear fact and dimension tables. Microsoft's own semantic- model checklist explicitly flags this:"Use star schema… avoid flat, denormalized, or pivoted data structures." Wide, denormalized tables reintroduce exactly the problems you're worried about, ambiguous grain and duplication from one-to-many relationships (Account–Opportunity, Contact–Activity, which lead to confidently wrong answers rather than visibly wrong ones.
How to split responsibilities:
Silver → Gold: Pre-resolve only the things a relationship can't cleanly express: data-quality fixes, type conversion, Salesforce–Marketo entity matching/surrogate keys, and any genuinely ambiguous join logic. Keep dimensions (Account, Contact, Lead, Campaign) and facts (Opportunity, Case, Marketo Activity) as conformed star-schema tables. AI-friendly Gold views to hide technical columns and expose business-friendly names is a solid approach.Semantic Model = comprehension layer: Define relationships, explicit DAX measures (avoid implicit ones), business-friendly names, descriptions + synonyms, and default summarization. Then use Prep for AI (AI data schema, verified answers, AI instructions) to scope and guide the agent. This is where accuracy actually comes from, not from flattening.
On "how much join logic in Gold?": Only pre-resolve joins that are ambiguous or non-navigable via relationships (e.g., fuzzy SF↔Marketo identity resolution, multi-hop bridges). Straightforward 1:many relationships (Account→Opportunity, Contact→Activity) should stay as relationships in the model, letting the agent navigate them, don't bake them into wide tables. Also keep scope tight (Fabric caps ~5 data sources) and consolidate duplicate/overlapping measures so the agent isn't guessing between "Total Revenue / Net Sales / Gross Sales."
Your proposed flow: Silver → AI-friendly Gold Views → Semantic Model → Fabric Data Agent is the right architecture. Reserve Gold for cleansing + entity resolution, let the star-schema semantic model carry relationships and business meaning.
Hope this helps, would love to hear how the Salesforce–Marketo agent performs once it's live!
Reference:-
Semantic model best practices for data agent - Microsoft Fabric | Microsoft Learn
Fabric data agent creation - Microsoft Fabric | Microsoft Learn
- RMori95610
Hi, Asmita_27,
Thank you very much for the detailed explanation.
Your point about keeping the Gold layer at the appropriate grain and using a star-schema Semantic Model as the comprehension layer, rather than flattening the data for AI, was especially helpful.
The distinction between pre-resolving ambiguous joins such as Salesforce–Marketo entity matching, while keeping straightforward one-to-many relationships such as Account–Opportunity in the Semantic Model, gives us a clear direction for our design.
We will proceed with the Silver → AI-friendly Gold Views → Semantic Model → Fabric Data Agent approach and use Prep for AI to further optimize the model for the agent.
Thank you also for the references and practical guidance!
- v-kathullac
Community Support
Hi RMori95610 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
- v-kathullac
Hi RMori95610 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
