Load Data into the Lakehouse with Copilot + Fabric MCP

Why we start with the lakehouse

Yes, I know, starting a Power BI series without opening Power BI Desktop feels wrong. But all started with a customer trying to build that "beautiful report" straight from CSVs in Blob and a product table in SQL. It worked for a week, then broke: two definitions of sales, slow refreshes, and no one knew which ProductKey was right.

Enterprises usually want what my customer wanted here too, one copy of the data with real access control, not five copies emailed around. That's why we start in Fabric, not in Desktop. This series follows the order I wish I'd shown them on day one:

To start I'm assuming you already have a Fabric workspace with a trial or F2 capacity, if you don't, get one first, the rest won't work without it. Learn more here: Fabric trial docs. Notice I'm talking about the Fabric lakehouse of today (OneLake + shortcuts), not the old ADLS Gen2 + Data Factory V1 pattern, the concepts rhyme, the clicks don't.

A Fabric Lakehouse on OneLake works a bit like those Service Principals from my old SQL article, one identity, one place to grant access, instead of credentials scattered everywhere. Notebooks, pipelines, warehouses and Power BI all see the same files. GitHub Copilot, through Fabric MCP, just writes the boring ingestion code for you.

Set up Fabric MCP + Copilot

You need a Fabric capacity (trial works), a GitHub Copilot subscription, and VS Code with the Fabric MCP Server extension. No service principal needed for local work, the extension uses your signed-in user identity.

Option 1: VS Code extension (recommended)

1. Install GitHub Copilot Chat + Microsoft Fabric MCP Server extensions
2. Run Azure: Sign In from the Command Palette (or az login in terminal)
3. Switch Copilot to Agent mode, refresh tools, Fabric tools appear. Done.

The extension is local-first and registers itself automatically, no mcp.json secrets to manage.

Option 2: Manual npx setup (any MCP client)

# Requires Node 20+
npx -y @microsoft/fabric-mcp@latest server start --mode all

// .vscode/mcp.json, minimal, user auth via Azure CLI
{
  "servers": {
    "fabric": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "@microsoft/fabric-mcp@latest", "server", "start", "--mode", "all"],
      "env": {
        "FABRIC_WORKSPACE_ID": "${env:FABRIC_WORKSPACE_ID}"
      }
    }
  }
}

// MCP Call: fabric.listOneLakeItems(workspaceId)

Create your lakehouse & folder structure

// MCP Call: fabric.createLakehouse(workspaceId, { name: "SalesLakehouse" })
// MCP Call: fabric.createFolders(workspaceId, ["Files/bronze","Files/silver","Files/gold","Files/notebooks"])

📁 SalesLakehouse/Files/
  📁 bronze/raw_sales    ← Source A shortcut to Blob CSV (bronze as shortcut)
  📁 bronze/dim_product  ← Source B Delta snapshot from Azure SQL
  📁 silver/             ← cleaned + joined tables (Part 2)
  📁 gold/               ← star schema (Part 2)
  📁 notebooks/          ← engineering code (Part 2)

Source A (shortcut): sales CSVs as bronze, zero-copy

Sales transactions live as daily CSVs in Azure Blob (sales-raw container). They're immutable drops, large, and already partitioned by date, ideal for a OneLake shortcut. The shortcut is our bronze for this source: no copy, always fresh. We go straight shortcut to Silver in Part 2.

// MCP Call: fabric.createOneLakeShortcut({
  lakehouse: "SalesLakehouse",
  path: "Files/bronze/raw_sales",
  source: { type: "BlobStorage", container: "sales-raw", path: "daily/" }
})

Source B (ingested): SQL product master to bronze Delta

Product details live in Azure SQL (sql-contoso-prod.database.windows.net / SalesDW / dim.Product) with ProductKey, Category, Subcategory, Brand, UnitCost. We deliberately do NOT shortcut this one:

• Small dimension, copy is cheap, query is fast as Delta
• OLTP source, we don't want Spark scans hammering it; take a daily snapshot
• Needs history/offline availability even if SQL is down
• Joinable to Source A on ProductKey, the bridge between the two sources

// MCP Call: fabric.createPipeline(workspaceId, {
  name: "Bronze Product Snapshot",
  activities: [{
    type: "Copy",
    source: { type: "AzureSQL", server: "sql-prod.database.windows.net",
      database: "SalesDW", table: "dim.Product" },
    sink: { type: "OneLakeDelta", table: "Files/bronze/dim_product",
      writeMode: "Overwrite" }
  }],
  schedule: "0 3 * * *",
  retry: { attempts: 2, intervalMin: 15 }
})

Verify both sources + join keys with Copilot

// MCP: fabric.readCSV(ws, "Files/bronze/raw_sales/daily/2026/09/15/sales.csv", { sample: 5 })
// MCP: fabric.readDeltaTable(ws, "Files/bronze/dim_product", { sample: 5 })

What's next in Part 2

Raw is not ready, and two sources is not one dataset. In Part 2 we do data engineering with Copilot: read shortcut CSVs + SQL snapshot Delta, validate the ProductKey join, quarantine orphans, and build silver to gold star schema.

• Silver notebook joining Source A (shortcut) + Source B (SQL snapshot)
• Referential-integrity checks + quarantine pattern
• Gold star-schema tables with conformed DimProduct