Microsoft Fabric: The Complete Guide — OneLake, Lakehouse, Pipelines, Medallion Architecture, and the Full Workshop

---

For two decades, building a modern data platform meant assembling a collection of separately licensed, separately configured, separately monitored services. Azure Data Factory for ingestion, Azure Data Lake Storage Gen2 for raw storage, Azure Synapse Analytics for the warehouse, Azure Databricks for Spark engineering, Power BI Premium for reporting, and Azure Purview for governance. Each service was excellent at its job. Together they created an operational puzzle requiring specialist expertise to wire up, maintain, and govern.

Microsoft Fabric changes that equation. Launched in general availability in November 2023, Fabric is an end-to-end analytics platform delivered as a single SaaS product. It unifies data engineering, data warehousing, real-time analytics, data science, and business intelligence under one roof, all sharing a single logical data lake called OneLake.

If Azure was a set of best-in-class tools purchased from different shops, Fabric is those same tools redesigned to work together from a single factory. The data does not need to move. The security model is shared. Billing is consolidated. Every engineer, analyst, and data scientist works on the same copy of the truth.

1 Introduction: What Is Microsoft Fabric? Beginner

This guide is written for a wide audience: SQL Server DBAs exploring cloud-native analytics, data engineers new to Fabric, architects evaluating the platform, and advanced practitioners looking for a single comprehensive reference. The workshop section is structured in progressive stages so readers can stop at whichever level matches their current needs.

Microsoft Fabric reached general availability in November 2023. At its core, every workload reads from and writes to OneLake, a single logical data lake for the entire Fabric tenant. There is no copying data between a lake, a staging area, and a warehouse. When a data engineer transforms a Delta table in a notebook, that same table is immediately visible to a SQL developer in the Warehouse and to a Power BI report through Direct Lake.

2 Why Fabric? The Business and Technical Case Beginner

Unified Storage: One Copy of the Truth

Every Fabric workload reads from and writes to OneLake. There is no copying data between a lake, a staging area, and a warehouse. When a data engineer transforms a Delta table in a notebook, that same table is immediately visible to a SQL developer in the Warehouse and to a Power BI report through Direct Lake.

Open Formats by Default

OneLake stores everything in Delta Parquet format: the same open format used by Apache Spark, Databricks, and the broader data engineering ecosystem. There is no proprietary binary format requiring migration tools to escape.

SaaS Simplicity

Fabric is delivered as software-as-a-service. Purchase capacity measured in Capacity Units (F-SKUs), assign workspaces to that capacity, and start building. There is no infrastructure to provision, no Spark clusters to size, no storage accounts to configure. The platform manages the compute layer automatically.

Microsoft 365 Integration

Fabric is built into the Microsoft 365 trust boundary. Every workspace is backed by Entra ID. Sensitivity labels from Microsoft Purview Information Protection flow automatically through the platform.

3 Why NOT Fabric? Honest Trade-offs Beginner

Scenario	Why Fabric May Not Fit	Alternative
Heavy custom Spark engineering with fine-grained cluster control	Fabric Spark is managed. Cannot pin specific Spark versions, install arbitrary native libraries, or use custom Docker images.	Azure Databricks
Strict multi-cloud or cloud-agnostic requirements	Fabric is Microsoft-first. While OneLake supports S3 shortcuts, the control plane is Azure.	Databricks on AWS/GCP
Ultra-low latency OLTP workloads under 5ms response	Fabric Warehouse and Lakehouse SQL endpoints are analytical engines, not OLTP databases.	Azure SQL Database, SQL Server
Legacy SSIS packages that cannot be rewritten	Fabric does not run .dtsx files natively.	Azure Data Factory SSIS Integration Runtime
Strict on-premises data residency with no cloud allowance	Fabric is cloud-only with fixed regional anchoring per capacity.	SQL Server on-premises with SSAS and SSRS
Budget too small for paid capacity	Trial capacities expire and free tiers are limited. Data Agents require F2 or higher and do not work on trial capacities.	Azure Synapse Analytics Serverless

4 Every Major Fabric Component Explained Beginner

Fabric is organized into workloads: logical groupings of related capabilities. Below is every major workload and the key item types within each one.

• OneLake: The single logical data lake for the entire Fabric tenant. All data stored by any Fabric item lives in OneLake in Delta Parquet format. Supports Shortcuts to external data sources (ADLS, S3, Google Cloud Storage, SharePoint, OneDrive, Dataverse) without copying data.
• Lakehouse: A data store combining a data lake with database capabilities (Delta tables, SQL analytics endpoint). The primary compute surface for data engineers. Supports Spark notebooks, pipelines, and SQL read access via an automatically generated SQL Analytics Endpoint.
• Data Warehouse: A fully transactional T-SQL engine running over OneLake. Supports DML, DDL, views, stored procedures, and cross-database queries. Now includes GPU acceleration for analytical query performance.
• Data Factory (Pipelines): The orchestration and ingestion engine. Supports 200+ native connectors, Copy Data activities, conditional branching, scheduling, and triggers.
• Dataflow Gen2: A Power Query-based low-code ETL tool for analysts and business users.
• Notebooks: Browser-based interactive code environments supporting Python (PySpark), Scala, SQL, and R.
• Semantic Models: The metadata and calculation layer between data and reports. Supports Direct Lake, Import, and DirectQuery modes.
• Real-Time Intelligence: KQL Databases, Eventstreams, and Real-Time Hub for event-driven workloads.
• Data Science: Notebooks with integrated MLflow experiment tracking, model registry, and scoring.
• Mirroring: Replicates data from external operational databases (Azure SQL, Cosmos DB, Snowflake, Databricks Unity Catalog) into OneLake in near real-time.
• Microsoft Purview (Governance): Data catalog, lineage tracking, sensitivity labels, and policy enforcement across the entire Fabric tenant.
• Fabric Data Agent: Conversational AI layer allowing plain-English questions against Fabric data sources. Generally available as of March 2026.
• Fabric IQ: The semantic intelligence layer providing shared business context for AI agents and analytical applications. Core capabilities generally available as of June 2026.

5 OneLake: The Foundation of Everything Beginner

OneLake is the most important concept in all of Microsoft Fabric. Understanding OneLake makes everything else make sense.

The “OneDrive for Data” Analogy

Microsoft describes OneLake as “OneDrive for data.” Just as OneDrive gives a single place for all documents that every Microsoft 365 app can access, OneLake gives an organization a single place for all data that every Fabric workload can access. One workspace, one Lakehouse, every tool reads the same files. There is no copying data between systems because there are no separate systems.

Technical Architecture

OneLake is built on Azure Data Lake Storage Gen2, inheriting its hierarchical namespace, security model, and API compatibility. Existing tools that speak ADLS Gen2 including Azure Databricks, Azure Synapse, and custom ADLS SDK applications can connect directly to OneLake without modification. The storage hierarchy is: Tenant, Workspace, Item (Lakehouse or Warehouse), Tables or Files.

Delta Parquet: The Universal Format

All tabular data in OneLake is stored in Delta Parquet format. Delta Lake adds a transaction log on top of Parquet files enabling ACID transactions, versioning, time travel, and schema enforcement. A Spark notebook, a T-SQL query, and a Power BI Direct Lake model can all read the same table without any conversion step.

OneLake Shortcuts

A Shortcut is a reference to data stored somewhere else: another Lakehouse, an external Azure Data Lake, an Amazon S3 bucket, a Google Cloud Storage bucket, SharePoint, OneDrive, or Dataverse. Shortcuts behave like symbolic links. They appear as a folder in the Lakehouse but contain no actual data copy. When Spark or SQL queries a Shortcut, the storage layer transparently fetches the data from the source with intelligent caching to reduce egress costs.

V-Order Optimization

When Fabric writes Delta tables it can apply V-Order: a Microsoft-developed Parquet write-time optimization that sorts, compresses, and encodes data specifically for the VertiPaq engine used by Power BI. V-Order writes are slightly slower but reads are dramatically faster, often eliminating the need for explicit pre-aggregation of large tables.

6 Lakehouse: Where Data Lives and Transforms Beginner

A Lakehouse in Microsoft Fabric combines the flexibility and scale of a data lake with the querying convenience of a database.

The Two Storage Areas

• Files area: Raw object storage. Land CSV, Parquet, JSON, images, binary blobs. This is the Bronze landing zone. Files here are not queryable via SQL until converted to Delta tables.
• Tables area: Delta tables registered in the metastore. Any Delta table written here is automatically visible to Spark notebooks, SQL queries, and Power BI. This is where Silver and Gold layer data lives.

SQL Analytics Endpoint

Every Lakehouse automatically generates a SQL Analytics Endpoint: a read-only T-SQL connection point exposing all Delta tables in the Tables area. Connect SQL Server Management Studio, Azure Data Studio, or any TDS-compatible tool directly to this endpoint and query lake data without writing a single line of Spark.

7 Data Warehouse: T-SQL at Lake Scale Intermediate

The Fabric Data Warehouse is a fully transactional T-SQL engine that separates compute from storage and stores data in Delta Parquet format on OneLake. Every table in the Warehouse is also readable by Spark notebooks and other OneLake-connected tools.

Key Capabilities

• Full DML support: INSERT, UPDATE, DELETE, MERGE
• DDL: CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SCHEMA
• Cross-database queries against other Warehouses and Lakehouses in the same workspace
• Result set caching for repeated analytical queries
• Data clustering for physical data organization
• COPY INTO from OneLake, ADLS, and external storage
• OPENROWSET for ad-hoc file querying without loading

Use Case	Lakehouse	Warehouse
PySpark / Scala transformations	Primary tool	Not applicable
T-SQL DML (UPDATE/DELETE/MERGE)	Read-only endpoint	Full support
Stored procedures and business logic in SQL	Not supported	Supported
Row-level security via SQL roles	Limited	Full RBAC
Serving Gold layer to SQL-heavy BI tools	Acceptable	Preferred
Cross-database join to multiple sources	Via Spark	Native T-SQL syntax
GPU-accelerated analytical queries	Not applicable	Now available

8 Pipelines and Data Factory: Orchestrating the Flow Intermediate

Fabric Pipelines are the orchestration engine for everything that needs to run in sequence, on a schedule, or conditionally. SSIS or Azure Data Factory experience makes Pipelines feel immediately familiar.

Core Pipeline Activities

• Copy Data: Ingests data from 200+ supported connectors (SQL Server, Oracle, Salesforce, REST APIs, SFTP, SAP, SharePoint, etc.) into a Lakehouse or Warehouse.
• Notebook activity: Executes a Fabric Notebook as a pipeline step with parameter passing.
• Dataflow Gen2 activity: Runs a Dataflow Gen2 as an orchestrated step.
• Stored Procedure activity: Calls a stored procedure in a Warehouse for post-load transformations or data quality checks.
• Get Metadata, If Condition, ForEach, Until: Control flow activities for conditional branching and loops.
• Fail activity: Explicitly fails a pipeline with a custom error message when a validation check fails.

SSIS vs Pipelines: The Migration Reality

Fabric does not run .dtsx SSIS packages natively. Three practical migration paths exist: rebuild as Pipelines plus Dataflow Gen2 (the recommended long-term path), lift to Azure Data Factory SSIS Integration Runtime to run existing packages in Azure while gradually rebuilding, or hybrid where SSIS lands files to OneLake-connected storage and Fabric pipelines ingest and process from there.

9 Dataflow Gen2: Low-Code Transformations Beginner

Dataflow Gen2 is a Power Query-based low-code ETL tool built into Fabric. Analysts and business users can connect to sources, apply transformation steps using a familiar Excel-like interface, and land results in a Lakehouse or Warehouse without writing code.

When to Use Dataflow Gen2

• Connecting to Excel, SharePoint lists, or CSV files with cleaning and reshaping requirements
• Flattening nested JSON or XML structures without Spark
• Business analyst-owned transformations that do not need PySpark
• Rapid prototyping of ingestion logic before formalizing in notebooks
• Teams with Power BI dataflow experience migrating to Fabric

10 Notebooks and Spark: Code-First Engineering Intermediate

Fabric Notebooks are browser-based interactive coding environments attached to a Lakehouse, running on managed Apache Spark. They support Python (PySpark), Scala, SQL magic commands, and R.

PySpark Patterns Used Every Day

from pyspark.sql.functions import col, trim, to_date, when, lit

df_raw = spark.read.option("header", True).option("inferSchema", True).csv("Files/bronze/sales/raw/")

df_silver = (
    df_raw
    .withColumn("order_date", to_date(col("order_date"), "yyyy-MM-dd"))
    .withColumn("customer_name", trim(col("customer_name")))
    .withColumn("status", when(col("status").isNull(), lit("Unknown")).otherwise(col("status")))
    .dropDuplicates(["order_id"])
    .filter(col("order_id").isNotNull())
)

df_silver.write.mode("overwrite").format("delta").saveAsTable("silver_sales_orders")

spark.sql("OPTIMIZE silver_sales_orders ZORDER BY (order_date)")
spark.sql("VACUUM silver_sales_orders RETAIN 168 HOURS")

11 Direct Lake: BI Without Import Refresh Intermediate

Direct Lake is one of the most technically significant innovations in the Fabric platform.

The Problem: Import vs DirectQuery

• Import mode copies data into Power BI’s VertiPaq engine. Reports are fast but data goes stale between scheduled refreshes. Large datasets hit memory limits.
• DirectQuery mode queries the source live on every report interaction. Always fresh but slow for complex analytical models at scale.

Direct Lake: The Best of Both

Direct Lake is a third mode unique to Fabric. The VertiPaq engine reads Delta Parquet files in OneLake directly: not via a database query and not via a pre-loaded import. The result is import-speed query performance on always-current lake data with no scheduled refresh required.

12 Real-Time Intelligence: Streaming and Event Data Intermediate

For IoT sensors, financial feeds, application logs, and user clickstreams where data freshness needs to be in seconds, Real-Time Intelligence in Fabric provides the required infrastructure.

• Eventstream: An ingestion pipeline for streaming data. Connect to Azure Event Hubs, IoT Hub, Kafka, or custom REST sources. Apply real-time transformations and route to a KQL Database or Lakehouse table.
• KQL Database (Kusto): A time-series optimized analytical store supporting Kusto Query Language. Ingest millions of events per second with sub-second query latency.
• Real-Time Hub: A tenant-wide catalog of all streaming data sources. Discover, connect to, and share streams across workspaces without duplicating setup.
• Activator: A rules-based alerting engine. Define conditions on streaming data and trigger Power Automate flows, Teams messages, or pipeline runs automatically.

13 The Medallion Architecture: Deeply Explained Intermediate

The Medallion Architecture is the most important design pattern for organizing data in Microsoft Fabric. Microsoft recommends it as the standard approach for Fabric lakehouses.

The Core Idea

Data quality degrades at the source and improves with each transformation. The Medallion model formalizes this by dividing the data lake into three distinct quality layers named after precious metals.

• Bronze: Raw Data. Data in its original format exactly as it arrived from the source. No transformation, no cleansing, no schema enforcement. The immutable archive and source of truth for reprocessing.
• Silver: Cleansed and Conformed. Validated, deduplicated, standardized data. Nulls handled, data types enforced, dates normalized, business keys validated. Still row-level, not yet aggregated.
• Gold: Business-Ready. Aggregated, modeled, and business-contextualized data. Star schema dimension and fact tables. Pre-computed KPIs. Subject-area data marts.

Why Three Layers Instead of One?

• No recovery path if transformation logic has a bug and the original data is overwritten.
• Schema coupling causes report breakage every time the source system changes its schema.
• Mixing concerns makes raw ingestion, cleansing rules, and analytical aggregation impossible to isolate.
• Regulatory requirements frequently require proving what raw data looked like before transformation.

Three Recommended Physical Patterns

Pattern 1: Three Lakehouses (Pure Lake). Create separate Lakehouses for Bronze, Silver, and Gold, each in its own workspace. Best for Spark-heavy teams.

Pattern 2: Two Lakehouses plus One Warehouse (Recommended). Bronze and Silver as Lakehouses, Gold as a Warehouse. Full T-SQL capability in the Gold serving layer while preserving Spark flexibility for engineering. SQL Server DBAs will feel at home here.

Pattern 3: Single Lakehouse with Folder Structure. A single Lakehouse with subfolders for each layer. Simpler for small teams but without layer-level governance or separate access control. Recommended for proof of concept only.

Attribute	Bronze	Silver	Gold
Data quality	Raw, unvalidated	Validated, conformed	Aggregated, modeled
Format	Files or Delta	Delta tables	Delta tables
Schema enforcement	None	Enforced	Enforced, curated
Who writes it	Pipelines (raw ingest)	Notebooks, Dataflow Gen2	Notebooks, Warehouse T-SQL
Who reads it	Data engineers only	Engineers, data scientists	Analysts, BI reports, business users
Reprocessable?	Is the source	Yes, from Bronze	Yes, from Silver
Fabric item type	Lakehouse (Files area)	Lakehouse (Tables area)	Lakehouse or Warehouse

14 Hands-On Workshop: Beginner to Advanced Beginner

This workshop builds a complete Medallion lakehouse from scratch. Complete stages in order as each depends on the previous one. The result is a working Bronze to Silver to Gold pipeline connected to a Power BI Direct Lake report.

Stage 1: Set Up Workspace and Lakehouses

Go to app.fabric.microsoft.com. Click Workspaces, then New Workspace. Name it fabric_workshop. Assign a Fabric capacity or select Trial. Click Apply. Then create three Lakehouses: lakehouse_bronze, lakehouse_silver, and lakehouse_gold. Open lakehouse_bronze to explore the Lakehouse Explorer. The left pane shows Tables (Delta tables) and Files (raw object storage). Upload a sample sales CSV to Files/sales/raw/ with columns: order_id, order_date, customer_id, customer_name, product, quantity, amount, country.

Stage 2: First Pipeline and Bronze Ingestion

Create a new Data Pipeline named pipeline_bronze_ingest. Add a Copy Data activity. Configure the source connector and set the destination to lakehouse_bronze, Files/sales/raw/. Enable a daily schedule. Bronze ingestion is now automated.

Stage 3: Silver Transformation with PySpark

from pyspark.sql.functions import col, trim, to_date, when, lit, upper
from pyspark.sql.types import DecimalType

bronze_path = "Files/sales/raw/"
df_raw = spark.read.option("header", True).option("inferSchema", True).csv(bronze_path)
print(f"Bronze row count: {df_raw.count()}")

df_silver = (
    df_raw
    .withColumn("order_date", to_date(col("order_date"), "yyyy-MM-dd"))
    .withColumn("customer_name", trim(col("customer_name")))
    .withColumn("product", trim(col("product")))
    .withColumn("country", upper(trim(col("country"))))
    .withColumn("quantity", when(col("quantity").isNull(), lit(0)).otherwise(col("quantity").cast("int")))
    .withColumn("amount", col("amount").cast(DecimalType(18,2)))
    .dropDuplicates(["order_id"])
    .filter(col("order_id").isNotNull())
)

silver_table_path = "abfss://fabric_workshop@onelake.dfs.fabric.microsoft.com/lakehouse_silver.Lakehouse/Tables/silver_sales_orders"
df_silver.write.mode("overwrite").option("overwriteSchema", "true").format("delta").save(silver_table_path)
spark.sql(f"OPTIMIZE delta.`{silver_table_path}` ZORDER BY (order_date, customer_id)")
print("Silver write complete.")

Stage 4: Gold Layer with Star Schema

df_sales = spark.read.format("delta").load(
    "abfss://fabric_workshop@onelake.dfs.fabric.microsoft.com/lakehouse_silver.Lakehouse/Tables/silver_sales_orders"
)
gold_base = "abfss://fabric_workshop@onelake.dfs.fabric.microsoft.com/lakehouse_gold.Lakehouse/Tables/"

# Dim Customer
df_dim_customer = df_sales.select("customer_id","customer_name","country") \
    .dropDuplicates(["customer_id"]) \
    .withColumnRenamed("customer_id","customer_key")
df_dim_customer.write.mode("overwrite").format("delta").save(gold_base + "dim_customer")

# Dim Date
from pyspark.sql.functions import year, month, quarter, date_format
df_dim_date = (df_sales.select("order_date").dropDuplicates()
    .withColumn("year", year("order_date"))
    .withColumn("month", month("order_date"))
    .withColumn("quarter", quarter("order_date"))
    .withColumn("month_name", date_format("order_date","MMMM"))
    .withColumn("year_month", date_format("order_date","yyyy-MM")))
df_dim_date.write.mode("overwrite").format("delta").save(gold_base + "dim_date")

# Fact Sales
df_fact_sales = df_sales.select(
    col("order_id").cast("string"),
    col("order_date"),
    col("customer_id").alias("customer_key"),
    col("product"),
    col("quantity").cast("int"),
    col("amount").cast("decimal(18,2)"))
df_fact_sales.write.mode("overwrite").format("delta").save(gold_base + "fact_sales")
spark.sql(f"OPTIMIZE delta.`{gold_base}fact_sales` ZORDER BY (order_date, customer_key)")
print("Gold layer complete.")

Stage 5: Master Pipeline

Create pipeline_master_etl. Add three Notebook activities in sequence: nb_bronze_to_silver, then nb_silver_to_gold on success of step 1. Connect with success arrows so each step only runs if the previous succeeded. Add Fail activities on each notebook failure path and configure email notifications in Monitoring settings.

Stage 6: Direct Lake Semantic Model and Power BI

Open lakehouse_gold SQL Analytics Endpoint. Click New Semantic Model. Select fact_sales, dim_customer, dim_date. Fabric creates the model in Direct Lake mode. Create relationships: fact_sales[customer_key] to dim_customer[customer_key] (Many-to-One) and fact_sales[order_date] to dim_date[order_date] (Many-to-One). Add DAX measures:

Total Revenue   = SUM(fact_sales[amount])
Order Count     = DISTINCTCOUNT(fact_sales[order_id])
Avg Order Value = DIVIDE([Total Revenue], [Order Count])
Revenue YTD     = TOTALYTD([Total Revenue], dim_date[order_date])

Stage 7: Incremental Processing and Watermarking

from pyspark.sql.functions import max as spark_max, lit
from datetime import datetime

watermark_table = "abfss://fabric_workshop@onelake.dfs.fabric.microsoft.com/lakehouse_silver.Lakehouse/Tables/etl_watermark"

try:
    df_watermark = spark.read.format("delta").load(watermark_table)
    last_processed = df_watermark \
        .filter(col("table_name") == "silver_sales_orders") \
        .select("last_updated_ts").collect()[0][0]
except:
    last_processed = datetime(2000, 1, 1)

print(f"Last processed: {last_processed}")

# Read only new records
df_new = spark.read.format("delta") \
    .load("...lakehouse_bronze.Lakehouse/Tables/bronze_sales") \
    .filter(col("ingested_ts") > lit(last_processed))

# Merge to silver using DeltaTable
from delta.tables import DeltaTable
silver_dt = DeltaTable.forPath(spark, "...lakehouse_silver.Lakehouse/Tables/silver_sales_orders")
silver_dt.alias("target").merge(
    df_new.alias("source"),
    "target.order_id = source.order_id"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

# Update watermark
new_max_ts = df_new.select(spark_max("ingested_ts")).collect()[0][0]
spark.createDataFrame([("silver_sales_orders", new_max_ts)], ["table_name","last_updated_ts"]) \
    .write.mode("overwrite").format("delta").save(watermark_table)
print(f"Watermark updated to: {new_max_ts}")

15 Governance, Domains, and Security Intermediate

An analytics platform without governance is a liability. Fabric provides a layered governance model mapping onto how most enterprises are already organized.

Tenant-Level Administration

The Fabric Admin portal controls tenant-wide policies: which workload types are enabled, who can create workspaces, external sharing permissions, and data residency settings. For enterprise deployments, restrict workspace creation to approved groups and require capacity assignment review before any new workspace goes live.

Workspaces and Roles

Every workspace has four built-in roles: Admin, Member, Contributor, and Viewer. For Medallion deployments a common pattern is: data engineering team has Contributor on Bronze and Silver; BI team has Viewer on Silver and Contributor on Gold; data consumers have Viewer on Gold only.

Domains

Domains are a logical grouping mechanism above workspaces. A Finance domain might contain Finance Bronze, Silver, Gold, and Reports workspaces. Domains enable domain-level administrators, sensitivity policies, and discovery in the OneLake Catalog: the foundation of a data mesh architecture in Fabric.

Microsoft Purview Integration

Every Fabric item is automatically catalogued in Microsoft Purview. Sensitivity labels propagate automatically from Lakehouse tables to any Power BI report consuming them. Lineage graphs show exactly how data flows from source through Bronze, Silver, Gold, and into reports.

Row-Level Security

In the Warehouse, RLS is implemented via SQL security predicates: the same mechanism used in SQL Server. In Semantic Models, RLS is defined using DAX filter expressions. Both apply correctly to Direct Lake models.

16 Cost and Capacity Planning Intermediate

Fabric is billed by Fabric Capacity Units (CUs). Every workload including Spark, Warehouse queries, pipelines, and Power BI refreshes consumes CUs from the assigned capacity.

Key Cost Principles

• Capacity smoothing: Short peaks above capacity are allowed and smoothed over the following 24 hours rather than causing immediate throttling.
• Pause and resume: Pause capacities when not in use. No charges for paused capacity. Automate with the Fabric REST API or Azure Automation.
• OneLake storage: Billed separately per GB at low per-TB rates. Storage is rarely the dominant cost.
• Reservation discounts: 1-year and 3-year commitments reduce CU costs by 40 to 60 percent versus pay-as-you-go.
• P-SKU retirement: Microsoft is consolidating to F-SKUs and retiring Power BI Premium P-SKUs. New deployments should target F-SKU capacity.

SKU	CUs	Recommended for
F2	2	Proof of concept, individual developers. Minimum for Data Agents.
F4	4	Small team development
F8	8	Small production workloads
F16	16	Medium production analytics
F32	32	Full Direct Lake features enabled
F64	64	Enterprise analytics, Direct Lake at full performance
F128+	128+	Large-scale enterprise, multiple heavy workloads including GPU-accelerated Warehouse

17 Final Thoughts Beginner

Microsoft Fabric represents the most significant shift in the Microsoft data platform since Azure launched. It is not an incremental improvement over Synapse or ADF: it is a ground-up rethinking of how analytics infrastructure should work. Unified storage, shared compute, open formats, and a single governance model spanning every workload from raw ingestion to published report.

The Medallion Architecture gives Fabric deployments the structural discipline needed to stay maintainable as they grow. Bronze keeps raw data safe. Silver makes it technically trustworthy. Gold makes it business-useful. When something breaks, there is a clear recovery path at every layer.

For SQL Server and Azure SQL DBAs, Fabric is not a replacement for existing skills: it is an extension of them. The Warehouse speaks T-SQL. The SQL Analytics Endpoint in the Lakehouse speaks T-SQL. Direct Lake removes the import and refresh complexity that made large Power BI deployments painful. SQL expertise is directly transferable and immediately valuable.

If just getting started: create a trial, build a workspace, drop a CSV into a Lakehouse, write three lines of PySpark, and query the result in the SQL Analytics Endpoint. The entire loop takes under 30 minutes and everything learned scales directly to an enterprise deployment.

18 Fabric Data Agent: Conversational AI Over Data GA Intermediate

The Fabric Data Agent lets users ask plain-English questions about data stored in OneLake and receive accurate, governed answers without writing SQL, DAX, or KQL. The agent uses large language models to parse natural language questions, identify the most relevant data source using the user’s own credentials and permissions, generate the appropriate query (T-SQL, DAX, or KQL depending on the source), execute it, and return a plain-English answer alongside the underlying query for transparency.

Critically, the agent never bypasses security. It operates entirely within the requesting user’s existing row-level security, column-level security, and workspace permissions. If a user cannot see certain data directly, the agent cannot surface it either.

Supported Data Sources

A Fabric Data Agent supports up to five data sources in any combination, including Lakehouses, Warehouses, KQL Databases, Power BI Semantic Models, Ontologies (where configured), and Microsoft Graph. A single agent can answer questions spanning the Gold Lakehouse, Warehouse, and existing Power BI Semantic Models simultaneously without the user knowing or caring which system the answer came from. External databases must be mirrored into Fabric first before the agent can query them: direct external database connections are not supported.

Current Limitations

• Response cap: Responses are limited to a maximum of 25 rows and 25 columns. Previous chat history within the same session can influence subsequent responses. If results are unexpectedly limited from an earlier cap, start a new chat session.
• Response latency: Typically 10 to 30 seconds per query. Suitable for interactive question-and-answer but not real-time operational monitoring.
• No visualization output: Responses are text-based. Chart or graph generation is not built into the agent response. Use Power BI reports or semantic models for visual output.
• English language only: Queries must be in English. Multi-language support is not yet available.
• Read-only: The agent enforces read-only access to all data sources. No write-back or data modification is possible through the agent.
• Cross-region not supported: The Data Agent workspace and the data source workspace must share the same Fabric capacity region.
• F2 minimum capacity required: Trial capacities are not supported. F2 or higher (or Power BI Premium P1 or higher with Fabric enabled) is required.
• Maximum approximately 25 tables for best accuracy: Agent accuracy degrades with very large numbers of tables. Best results with approximately 25 or fewer tables per agent.
• Purview DLP interaction: If Purview Data Loss Prevention or access restriction policies apply to a source, the agent may return truncated or blocked results. Test DLP interaction before rollout.

Service Principal Authentication

As of June 2026, Fabric Data Agents support service principals, enabling developers to authenticate and run agents through app identities instead of requiring delegated user credentials. This unlocks secure enterprise integration scenarios including backend services, automated workflows, and custom applications built with Microsoft Foundry, Copilot Studio, and MCP-based agent frameworks. Service principal authentication is a preview feature as of July 2026.

Setting Up a Data Agent

In the workspace, click New Item and search for Fabric data agent. Give it a descriptive name reflecting its domain. The OneLake catalog opens automatically: select up to five data sources. Write plain-English Agent Instructions that tell the agent what it covers and how to interpret the data. For example:

You are a sales analytics assistant.
You have access to sales orders, customer data, and product tables.
- "Revenue" always means the sum of the amount column in fact_sales
- "Active customer" means a customer with at least one order in the last 90 days
- Always filter to the current fiscal year unless the user specifies otherwise
- Do not return individual customer PII -- aggregate only

Provide 5 to 10 sample questions with their expected SQL or DAX to improve the agent’s reasoning accuracy. Test with real questions from business users. Refine instructions based on gaps. Once satisfied, click Publish. Users only need Read permission on the underlying data sources, not workspace access.

Integration with Copilot, Teams, and Foundry

Once published, a Data Agent can be surfaced in multiple places. Business users can query it directly from Copilot in Power BI. Developers can connect it to Microsoft Copilot Studio or Microsoft Foundry to build custom organizational copilots. Foundry Observability now provides detailed telemetry for every call including latency, status, and error details when something goes wrong. The agent also supports MCP (Model Context Protocol), enabling interoperability with other AI agents across the Microsoft ecosystem.

19 Fabric IQ: The Semantic Intelligence Layer Advanced

Fabric IQ is the unified intelligence platform powered by semantic understanding and agentic AI in Microsoft Fabric. If the rest of Fabric is about storing, moving, and querying data, Fabric IQ is about giving that data meaning. The core problem it solves: even the best-engineered data platform stores data but not meaning. Gold Lakehouse tables are clean and queryable but the platform has no understanding of what a “Customer,” “Active Asset,” or “Revenue” means in the context of the business. That semantic knowledge lives in people’s heads and in inconsistent definitions scattered across reports and spreadsheets.

Fabric IQ Components and Current Status

Semantic Models GA

Fabric IQ’s foundation is Power BI Semantic Models, which are generally available and provide structured representations of data for trusted business intelligence. Semantic Models serve as the foundation for training agents and grounding AI responses in governed business definitions.

Graph in Fabric GA since June 2026

Graph in Fabric reached general availability at Microsoft Build 2026 in June 2026. Graph introduces a highly scalable, relationship-first model that connects business entities, systems, and signals so teams and agents can understand how changes propagate across the enterprise and act with full context. This is a live, queryable knowledge graph: AI agents can traverse it to understand multi-hop relationships such as a supply chain disruption affecting a supplier affecting components affecting products affecting orders.

Operations Agents GA since June 2026

Operations Agents reached general availability at Microsoft Build 2026 in June 2026. Operations Agents continuously monitor business conditions in real time, reason over live data, evaluate trade-offs against defined rules and policies, and automatically take actions to advance desired business outcomes. For example: an Operations Agent monitoring a logistics network can detect when delivery constraints are violated, evaluate alternative routes against business rules, and trigger the appropriate workflow without a human in the loop.

Planning in Fabric GA since June 2026

Planning in Fabric reached general availability later in June 2026. Planning removes the need for separate planning tools or spreadsheet-based workflows, allowing organizations to bring goals, plans, and actual results together on shared semantic models. Finance teams can build budgets, forecasts, targets, and scenario models directly on top of governed Fabric data. Projections write back securely and flow immediately through connected Power BI reports.

Ontology Preview

Ontology is the operational context layer that grounds agents in business meaning. It defines business entities (Customer, Product, Asset, Order), their properties, and the relationships between them, then binds those entities to actual tables in Lakehouses, Warehouses, and Semantic Models. Once bound, every AI agent, report, and query that touches those tables understands what they represent in business terms rather than just raw column names. According to Microsoft Build 2026 announcements, Ontologies in Fabric IQ are expected to reach general availability in the coming months. Ontology remains in preview as of July 2026.

How Fabric IQ Connects to Everything

OneLake is where data lives. The Medallion Architecture is how it is organized. Fabric IQ is the layer that gives it meaning. Semantic Models provide structure. Graph makes entity relationships explicit and queryable. Ontology (when configured) extends semantic understanding into operational context. Operations Agents act on that context in real time. Planning closes the loop between analytical insight and business execution. The Fabric Data Agent becomes dramatically more accurate when grounded in an Ontology: instead of guessing what columns mean from their names, it reads formally declared business definitions.

Who Should Start Exploring Fabric IQ Now?

Graph, Operations Agents, and Planning are production-ready and available now. Organizations that should start immediately include those already running a mature Medallion Lakehouse with clean Gold layer tables, those dealing with inconsistent business definitions across teams and reports, and those building AI agents that need to reason about business context rather than just query data.

Ontology evaluation is appropriate for organizations that want to formally model business entities and relationships as a foundation for future AI agent grounding, with the understanding that it remains in preview and may change before general availability.

References

• Microsoft Learn: What is Microsoft Fabric?
• Microsoft Fabric Blog: General Availability November 2023
• Microsoft Learn: OneLake Overview and Architecture
• Microsoft Learn: Governance and Compliance in Microsoft Fabric
• Microsoft Learn: OneLake Shortcuts
• Microsoft Learn: What is a Lakehouse in Microsoft Fabric?
• Microsoft Learn: What is Data Warehousing in Microsoft Fabric?
• Microsoft Learn: Data Factory in Microsoft Fabric
• Microsoft Learn: Dataflow Gen2 Overview
• Microsoft Learn: Fabric Notebooks
• Microsoft Learn: Direct Lake Overview
• Microsoft Learn: Real-Time Intelligence in Microsoft Fabric
• Microsoft Learn: Implement Medallion Lakehouse Architecture in Fabric
• Microsoft Learn: Microsoft Fabric Licenses and Capacity SKUs
• Microsoft Learn: Fabric Data Agent Overview (GA)
• Microsoft Learn: What’s New in Microsoft Fabric
• Azure Blog: Microsoft Build 2026: Building Agentic Apps with Microsoft Fabric (June 11, 2026)
• Microsoft Fabric Community: Fabric IQ GA Announcement (June 2, 2026)
• Microsoft Fabric Community: Fabric June 2026 Feature Summary
• Microsoft Learn: Create a Fabric Data Agent