📊 Series: Modern Data Platforms Part 10 of 10 — 🏗️ Build Real (Hands-On)

"Reading about Fabric is understanding. Building with Fabric is confidence. After this guide, you will have done both."

What You Will Build

CSV file (sales data)
       ↓
Upload to OneLake (Fabric Lakehouse Files section)
       ↓
Dataflow Gen2 — clean and load to Delta table
       ↓
Delta table in Lakehouse (SQL Analytics Endpoint)
       ↓
Power BI report via Direct Lake (no refresh needed)

Time to complete: approximately 45-60 minutes.

What you need before starting

  • ✅ Microsoft 365 work or school account (personal accounts have limitations)
  • ✅ Microsoft Fabric free trial — sign up at app.fabric.microsoft.com
  • ✅ OneDrive configured (required for file uploads in Fabric trial)
  • ✅ The sample CSV file — download link and structure below
⚠️ Trial Note The Fabric free trial gives you 60 days of Fabric capacity (F64 equivalent). This is more than enough for this entire guide. You will NOT be charged if you stay within the trial. The trial does require a work or school email — personal Gmail/Outlook accounts have capacity limitations.

Phase 1 — Set Up Your Fabric Environment

Step 1: Start the Fabric free trial

  1. Go to app.fabric.microsoft.com
  2. Sign in with your Microsoft 365 work account
  3. If prompted, click "Start trial" or "Try Microsoft Fabric free"
  4. Accept the trial terms — no credit card needed
  5. Wait for trial activation (usually instant, sometimes 2-3 minutes)

Step 2: Create a Fabric Workspace

  1. In the left sidebar, click Workspaces (grid icon)
  2. Click "+ New workspace"
  3. Name: ModernDataPlatformDemo
  4. Under Advanced, set License mode to Trial (or Fabric capacity if you have one)
  5. Click Apply
📌 What just happened You created a Fabric workspace. This workspace has its own OneLake storage automatically. Every item you create inside (Lakehouse, Pipeline, Report) stores data in this workspace's OneLake. No separate storage account needed.

Step 3: Create a Lakehouse

  1. Inside your workspace, click "+ New item"
  2. Under Data Engineering, select "Lakehouse"
  3. Name: SalesLakehouse
  4. Click Create
  5. The Lakehouse opens — you see two sections: Tables (Delta tables) and Files (raw files)

Phase 2 — Prepare and Upload Sample Data

Step 4: Create the sample CSV file

Open Notepad (or any text editor) and paste this data. Save as sales-data.csv:

OrderID,OrderDate,Region,Product,Category,Quantity,UnitPrice,CustomerID
1001,2024-01-05,North,Laptop Pro,Electronics,2,45000,C101
1002,2024-01-06,South,Office Chair,Furniture,5,8500,C102
1003,2024-01-07,East,Laptop Pro,Electronics,1,45000,C103
1004,2024-01-08,West,Standing Desk,Furniture,2,15000,C104
1005,2024-01-09,North,Wireless Mouse,Electronics,10,1200,C105
1006,2024-01-10,South,Laptop Pro,Electronics,3,45000,C101
1007,2024-01-11,East,Office Chair,Furniture,4,8500,C106
1008,2024-01-12,West,Monitor 27",Electronics,6,22000,C107
1009,2024-01-13,North,Wireless Mouse,Electronics,15,1200,C108
1010,2024-01-14,South,Standing Desk,Furniture,1,15000,C109
1011,2024-01-15,East,Monitor 27",Electronics,2,22000,C110
1012,2024-01-16,West,Laptop Pro,Electronics,4,45000,C111
1013,2024-01-17,North,Office Chair,Furniture,3,8500,C112
1014,2024-01-18,South,Wireless Mouse,Electronics,20,1200,C113
1015,2024-01-19,East,Standing Desk,Furniture,2,15000,C114

Step 5: Upload CSV to OneLake Files

  1. In your SalesLakehouse, find the Files section in the left explorer
  2. Click the … (more options) next to Files
  3. Select "New subfolder" → name it raw-sales
  4. Click … next to raw-sales → Upload → Upload files
  5. Select your sales-data.csv file → click Upload
  6. You should see sales-data.csv appear inside raw-sales folder
📌 What just happened Your CSV file is now in OneLake. It is stored in Azure Data Lake Storage Gen2 under the hood. The file path shown in Properties is the actual ABFS path — any Spark notebook or external tool can read it directly from here.

Phase 3 — Transform Data with Dataflow Gen2

Step 6: Create a Dataflow Gen2

  1. Go back to your workspace (click workspace name in breadcrumb)
  2. Click "+ New item"
  3. Under Data Factory, select "Dataflow Gen2"
  4. Name: TransformSalesData
  5. The Power Query editor opens

Step 7: Connect to your CSV file in OneLake

  1. In Power Query, click "Get data"
  2. Search for "Lakehouse" and select Microsoft Fabric Lakehouse
  3. Select your SalesLakehouse
  4. Navigate to Files → raw-sales → select sales-data.csv
  5. Click Connect
  6. A preview of your CSV data appears — 15 rows, 8 columns

Step 8: Apply transformations

Apply these transformations — each click is recorded as an Applied Step:

  1. Set column types: Select OrderDate column → click Data Type → Date
  2. Set column types: Select Quantity, UnitPrice columns → Data Type → Whole Number / Decimal
  3. Add calculated column: Click Add Column → Custom Column
    • Name: TotalRevenue
    • Formula: [Quantity] * [UnitPrice]
    • Click OK
  4. Rename query: Right-click the query name (left panel) → Rename → SalesData

Step 9: Set destination to Lakehouse Delta table

  1. In the lower-right corner, find Data destination
  2. Click "+ Add data destination" → select Lakehouse
  3. Select your SalesLakehouse
  4. Under destination table, select New table
  5. Table name: SalesData
  6. Update method: Replace (for this demo)
  7. Click Save settings

Step 10: Publish and run the Dataflow

  1. Click "Publish" in the top right
  2. Go back to your workspace — you see TransformSalesData with a loading spinner
  3. Wait 2-3 minutes for the Dataflow to run
  4. When complete, the spinner disappears and Last refresh shows the time
  5. Open SalesLakehouse — under Tables you now see SalesData Delta table
✅ Checkpoint Click SalesData table — you should see your 15 rows with the TotalRevenue column calculated. The data is now stored as a Delta table in OneLake. ACID transactions, schema enforcement, and time travel are automatically enabled.

Phase 4 — Connect Power BI via Direct Lake

Step 11: Create a semantic model from the Lakehouse

  1. In SalesLakehouse, switch to SQL analytics endpoint view (dropdown at top right)
  2. Click "New semantic model"
  3. Name: SalesDataModel
  4. Select the SalesData table checkbox
  5. Click Confirm
  6. The semantic model is created in Direct Lake mode — it reads live from OneLake

Step 12: Create a Power BI report

  1. The semantic model opens automatically
  2. Click "Open data model" to add measures if needed
  3. Or click "New report" directly
  4. The Power BI report canvas opens with SalesData in the Fields pane

Step 13: Build a simple sales dashboard

  1. Visual 1 — Total Revenue card:
    • Insert → Card visual
    • Drag TotalRevenue to Fields well
    • This shows total revenue across all orders
  2. Visual 2 — Revenue by Region bar chart:
    • Insert → Clustered bar chart
    • Y-axis: Region
    • X-axis: TotalRevenue (Sum)
  3. Visual 3 — Revenue by Category:
    • Insert → Donut chart
    • Legend: Category
    • Values: TotalRevenue
  4. Click File → Save → name: Sales Dashboard
✅ Direct Lake Confirmed In the Report → View → Performance Analyzer, run the visuals. The storage mode shown is Direct Lake — not Import and not DirectQuery. This means Power BI is reading directly from OneLake Delta tables with no data copy and no refresh cycle.

Phase 5 — Test the Live Pipeline

Step 14: Add new data and see it update

  1. Open your sales-data.csv and add 3 new rows at the bottom: 
    1016,2024-01-20,North,Laptop Pro,Electronics,2,45000,C115
1017,2024-01-21,South,Monitor 27",Electronics,3,22000,C116
1018,2024-01-22,East,Wireless Mouse,Electronics,25,1200,C117
  2. Save the CSV
  3. Upload the updated file to OneLake (same location, replace existing)
  4. Go to TransformSalesData Dataflow → click Refresh now
  5. Wait for refresh to complete
  6. Open your Sales Dashboard report — TotalRevenue card updates automatically

You have just seen the complete pipeline working end-to-end: new data in CSV → OneLake → Delta table → Power BI report updated via Direct Lake. No manual steps. No import refresh scheduled. The pipeline is the architecture in action.

Troubleshooting — Common Issues

  • Dataflow fails with "capacity exceeded" — Free trial capacity has limits. Wait 5-10 minutes and retry. If persistent, reduce table size or upgrade capacity.
  • "Table not found" when creating semantic model — Ensure Dataflow published successfully and the Delta table appears under Tables in the Lakehouse before creating the semantic model.
  • Power BI shows Import mode instead of Direct Lake — Check that you created the semantic model from the Lakehouse SQL Analytics Endpoint, not from Power BI Desktop. Desktop connections use Import or DirectQuery. Direct Lake requires creating the model inside Fabric.
  • CSV upload fails — Ensure OneDrive is configured on your account. Fabric trial uses OneDrive as the underlying upload mechanism. Sign in to onedrive.com once to activate it.
  • Dataflow cannot find CSV after upload — In Dataflow Get data, navigate using the Lakehouse connector, not the file path. The file path in Dataflow must use the OneLake path, not a local path.

🎯 Series Final Quiz

Q1: In this pipeline, why is Direct Lake faster than Import mode for Power BI?

Show Answer

Direct Lake reads Delta tables stored in OneLake directly — no data is copied into Power BI's in-memory engine. Import mode copies ALL data into Power BI's VertiPaq engine on each refresh, which is time-consuming and has dataset size limits. Direct Lake gets the freshness of DirectQuery (live data) with performance close to Import (columnar Delta format is optimised for analytics).

Q2: If you add a new column to the CSV file, what steps are needed to see it in the Power BI report?

Show Answer

Three steps: (1) Update the Dataflow Gen2 to include the new column in the query (Add Column or verify it is included in the source), (2) Republish and refresh the Dataflow — this updates the Delta table schema, (3) In the semantic model, click Refresh to pick up the new column. The report then shows the new field in the Fields pane. This is schema evolution — Delta Lake handles it without recreating the table.

Q3: How does what you built here map to the Medallion architecture discussed in Post 4?

Show Answer

The CSV upload to OneLake Files = Bronze layer (raw, unprocessed). The Dataflow Gen2 transformation (adding TotalRevenue, setting types) writing to the Delta table = Silver/Gold layer (cleaned, business-ready). In a production system, you would add a Bronze Lakehouse (raw copy preserved), a Silver Lakehouse (cleaned, standardised), and a Gold Lakehouse (aggregated for reporting). This demo simplified to two steps for clarity.

Series Complete — What You Built and Learned

  • Set up a Microsoft Fabric workspace with Lakehouse using the free trial — no Azure subscription needed
  • Ingested CSV data to OneLake and transformed it with Dataflow Gen2 into a Delta table
  • Connected Power BI via Direct Lake — live data, no refresh cycle, no data copy
  • Tested the live pipeline end-to-end — updated source data, refreshed Dataflow, report updated automatically
  • This is the foundation of every modern enterprise data platform — the same pattern scales from 15 rows to 15 billion rows
📚 Continue Learning