Manufacturing operations leaders, supply chain managers, and executives need a single, trusted view of inventory, supply, and lead-time risk across distributed factories without waiting for engineering teams to build one-off reports. Operations leaders want to spot stockout risk before it disrupts production, supply chain managers need visibility into single-source dependencies and lead-time variability, and executives want a top-line read on factory health and service levels.
This was my inspiration for the Data Factory & Data Integration Community Challenge. I built a solution integrating batch-exported operational data from four U.S. factories, transforming it through a Medallion pattern, and surfacing production readiness intelligence through Power BI and an AI data agent on Microsoft Fabric. I also integrated GitHub is integrated at the Fabric workspace level for version and source control and used GitHub Copilot for some transformations as well.
Solution Architecture
Solution Approach
Step 1 — Bronze: Ingest
A PySpark notebook generates synthetic manufacturing data and lands 5 Delta tables in a Lakehouse (Lakehouse1) in the Bronze schema. The Data Ingestion Pipeline orchestrates the notebook and sends an Office 365 success email through the MicrosoftOutlook connector, notifying that the ingestion pipeline has run successful and used Copilot for troubleshooting pipeline failures and error messages.
A Copy Job then batch-loads the bronze tables into the Data Warehouse (Warehouse1) in the Bronze Schema. This segregates the downstream transformations to the Warehouse, utilizing the Lakehouse for ingestion and storing historical data.
Step 2 — Silver: Cleanse and Enrich
I used dbt Jobs to perform the Silver layer transformations Bronze-to-Silver dbt Job runs dbt build against the Bronze schema tables in Warehouse1 with 4 threads. It tightens types from varchar(8000)/float down to varchar(20–200)/decimal(18,2), adds surrogate keys (region_key, category_key), denormalizes facts with factory and product attributes, and computes derived metrics like value_impact_usd, inventory_position_units, and on_hand_value_usd. Each output table carries a silver_loaded_utc audit timestamp. The transformed and enriched data is stored as delta tables in the Silver schema in Warehouse1.
I also used Copilot in the Data Warehouse to generate a standard date dimension table is created in the Silver Schema for the downstream analytics using natural-language prompts. I also used Copilot to help generate descriptions, annotating the logic outlined in the date dimension table query.
Step 3 — Gold: Aggregate into KPIs
I also configured a dbt Job (Silver-to-Gold dbt Job) to perform the Gold layer transformations. produces five executive KPI models in the gold schema:
- kpi_factory_daily — factory-level daily rollup
- kpi_product_daily — product-level daily rollup
- kpi_inventory_health_index — composite 0–100 score
- kpi_supply_resilience — single-source risk analysis
- kpi_event_latency — PO → receipt lead-time analytics
For the Gold Layer transformations, I used the delta tables in the Silver Schema in Warehouse1 and aggregated them further, optimizing them further for downstream analytical workloads. Once complete, I saved transformations as delta tables in the Gold Schema in Warehouse1.
One of the newest features available in the Data Factory workload is the ability to configure pipelines to automate dbt jobs. Using this feature, I was able to curate the Bronze-Silver and Silver-Gold dbt jobs in a single pipepline to run sequentially via an automated schedule in Fabric.
Step 4 — GitHub Copilot: Build and Document the Semantic Layer
With the Gold transformations complete, I began to build my semantic model. I combined the tables in the Gold Schema with the Date Dimension table in the Silver schema and built my Product Inventory model. This model leverages DirectLake, allowing for fast performance traditionally associated with Import mode and low latency associated with DirectQuery. Once the semantic model was created and all the appropriate relationships were built, I utilized GitHub Copilot to review the semantic model and is objects. I prompted GitHub Copilot to proactively identify any poor modeling practices and red flags.
Following this, I prompted GitHub Copilot to adopt the persona of a Manufacturing Operations leader and identify key metrics and KPIs that I could visualize in a Power BI report. This was perhaps the most interesting aspect of the whole solution. GitHub Copilot reviewed my semantic model and suggested key metrics and KPIs that I could calculate and generated the relevant DAX code to calculate the measures. Taking it a step further, I prompted GitHub Copilot to review the semantic model once more and generate relevant descriptions for the semantic objects like tables, columns, and measures.
In order to build a schema optimized for AI, it is important to build a strong semantic foundation. This can be achieved by following modeling best practices like reducing model ambiguity, building a Star or a Snowflake Schema and reducing many-to-many relationships.
Another practice that demonstrates good model hygiene and helps build a strong AI foundation is adding descriptions and synonyms to the semantic objects. This practice helps provide Copilot with the necessary context about semantic objects, connecting the data to business concepts. It also helps map users' linguistic terms and phrasing to the appropriate semantic objects, ensuring that the right semantic object is referenced by Copilot when responding to users’ natural-language prompts. A strong AI foundation plays a key role in ensuring Copilot outputs are accurate and grounded in business context for Consumers.
Step 5 — Visualize: Semantic Model and Report
Once the semantic model was built and the AI schema was built utilizing the Prep your Data for AI feature capabilities, I began with building the visualization layer through a Power BI report. To start off, I used Copilot to help me get started and generate a simple wireframe based on my semantic model. I then used GitHub Copilot to further enhance the wireframe and help me storyboard the layout of the report. My goal was to build a report aligning to Executives and Operations leaders, providing insights into Product Inventory, Supply Resilience, Inventory Health, and Event Latency.
Once the report was complete, I used the Prep Your Data for AI feature capabilities to designate Verified Visuals. Verified Visuals designates pre-approved charts for anticipated questions, so common questions return trusted visuals instead of ad-hoc generations.
I also configured a Data Agent, curated with the Product Inventory Semantic Model called Inventory Insights. This way, the warehouse permissions, sensitivity labels, and the linguistic metadata from Prep Your Data for AI are inherited and carry through the Data Agent. I integrated the Inventory Insights data agent with M365 Copilot, extending the solution beyond the Fabric environment as well, enabling conversational analytics beyond the Fabric environment.
Step 6 — Consume: AI Experiences for Business Users
To share the Report and the Data Agent with consumers and business users, I configured an Org App. The Org App curates the Inventory Snapshots Report and the Inventory Insights Data Agent, allowing Business Users to access the report and the artifact in a central location.
With the semantic model prepared for AI, Business Users can use Copilot in the Power BI Reports to ask questions like "What is the Inventory Health Score Trend by Factory?" and receive grounded answers without running DAX queries or building visuals. Business Users can also generate summaries of report pages to circulate with their teams.
Through the Org App, Business Users could also access the Inventory Insights Agent to ask questions in natural language to identify and visualize trends and insights. Located in M365 Copilot, the Inventory Insights Agent can be accessed by Business Users outside the Fabric environment as well, enabling conversational analytics beyond the report.
Step 7 — Govern: Security and Source Control
Through this entire solution, security and governance was top of mind. Microsoft Purview applies Confidential \ All Employees sensitivity labels were added all artifacts. The labels propagate automatically to Excel exports as well, ensuring the necessary compliance.
Workspace roles (Admin / Member / Contributor / Viewer) could be mapped to Entra groups, ensuring the principle of least-privilege. The Warehouse authenticates via Entra ID only and DirectLake inherits warehouse permissions at query time for an added layer of security. While not a part of this solution, granular security could be configured within the Semantic Model and OneLake as well, ensuring that report consumers see only the data they are provisioned to consume.
GitHub was integrated at the Fabric Workspace as well, ensuring source and version control.
Microsoft Employee