Data Warehousing in Fabric — Complete 2026 Guide

Why Data Warehousing in Fabric?

The Fabric Data Warehouse is not another cloud SQL service layered on top of object storage. It is built lake-native from the ground up — data lives as open Delta Parquet files in OneLake, which means the same rows that power your T-SQL queries are immediately accessible to Spark notebooks, Power BI Direct Lake models, and Fabric Data Agents without any ETL, copy, or export step.

That architecture eliminates the storage duplication and pipeline complexity that drives up cost and staleness in traditional warehouse-plus-lake setups. One copy of data, governed in one place, served to every consumer through the compute engine best suited to their work.

Architecture — Warehouse, Lakehouse & SQL Analytics Endpoint

Fabric has two primary warehousing surfaces. Understanding which to use — and when to combine them — is the most important architectural decision you will make early in a Fabric deployment.

For a deeper comparison including cost trade-offs, see Fabric Lakehouse vs Data Warehouse — Architecture Decision Guide.

Schema Design — Medallion Architecture & Modeling Patterns

Structure your data across clear layers in OneLake and Warehouse. Each layer has a distinct purpose — preserving raw lineage while delivering clean, governed data to BI consumers.

1. Raw / LandingPreserve original files immutably — CSV, JSON, Parquet — in Lakehouse or mirrored sources. Never transform in this layer. It is your compliance and replay foundation.
2. Bronze / StagingLight cleaning: standardize schema, enforce basic types, tag source system and ingestion timestamp. Feed downstream transformations from here.
3. Silver / RefinedNormalize, deduplicate, apply business rules. Typically Lakehouse tables populated by Spark notebooks or Dataflow Gen2. Most complex transformation logic lives here.
4. Gold / WarehouseDenormalized Warehouse tables — facts and dimensions — tuned for T-SQL queries, materialized views, and Power BI semantic models. This is your governed serving layer.

Modeling Best Practices

Use star schemas with narrow fact tables and conformed dimensions. This simplifies DAX, reduces query complexity for Power BI, and keeps fact table scans fast. Partition facts by date or business period to align with common filter patterns and optimize Delta file scan ranges.

When ingesting from multiple source systems, use surrogate keys in dimensions and maintain business keys for lineage. Adopt a consistent naming convention — for example fact_ prefix for fact tables and dim_ for dimensions — to improve discoverability in the Fabric item catalog.

Implementation — T-SQL, Delta Lake & Spark Patterns

Create a curated fact table with CTAS

Use CREATE TABLE AS SELECT to build initial fact tables from refined Silver layer data. Fabric handles distribution automatically — do not include Synapse-style DISTRIBUTION or CLUSTERED COLUMNSTORE INDEX syntax.

CREATE TABLE dw.fact_sales
AS
SELECT
  order_id,
  customer_id,
  product_id,
  CAST(sale_date  AS DATE)           AS sale_date,
  CAST(quantity   AS INT)            AS quantity,
  CAST(amount     AS DECIMAL(18,2))  AS amount
FROM refined.sales_transactions
WHERE amount >= 0;

MERGE for idempotent incremental loads

Use MERGE for upsert logic on incremental fact table loads from CDC or staged updates.

MERGE INTO dw.fact_sales AS target
USING staging.sales_updates AS source
  ON target.order_id = source.order_id
WHEN MATCHED THEN
  UPDATE SET
    target.quantity  = source.quantity,
    target.amount    = source.amount,
    target.sale_date = source.sale_date
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, product_id, sale_date, quantity, amount)
  VALUES (source.order_id, source.customer_id, source.product_id,
          source.sale_date, source.quantity, source.amount);

Hybrid — Spark notebook + Warehouse CTAS

Use Spark for heavy distributed transformations in the Silver layer, then CTAS in the Warehouse for the governed Gold table. Both read the same Delta files in OneLake.

# PySpark in Fabric notebook — build Silver layer
df_raw = spark.read.table("landing.sales_raw")
df_clean = (
    df_raw
    .dropDuplicates(["order_id"])
    .filter("amount >= 0")
)
df_clean.write.format("delta").mode("overwrite")     .saveAsTable("refined.sales_clean")
# Fabric Warehouse then uses refined.sales_clean as source for CTAS

For Spark transformation patterns in detail, see Transform Data Using Notebooks in Fabric.

Performance Tuning & Cost Optimization

Fabric’s distributed SQL engine provides automatic workload management for standard workloads. Physical design and query patterns still determine cost and latency at scale.

1. Partition by date for fact tablesPartition large fact tables by the date column that aligns with your most common query filters. This allows the engine to skip entire Delta file partitions rather than full scans.
2. OPTIMIZE and Z-ORDERRun OPTIMIZE periodically to compact small Delta files created by incremental loads. Z-ORDER on your most selective filter columns — such as customer_id or product_id — to co-locate related rows and reduce I/O.
3. Materialized views for hot aggregationsCreate materialized views for expensive aggregations that power high-concurrency dashboards. The engine refreshes them incrementally without manual refresh scheduling.
4. Use Direct Lake for Power BIConnect Power BI semantic models to Warehouse tables via Direct Lake mode rather than Import. This eliminates dual storage, removes import refresh latency, and reduces capacity consumption.
5. Monitor with Capacity MetricsReview query history and the Fabric Capacity Metrics app regularly to identify long-running queries and workloads that should be rescheduled off-peak.

-- Compact Delta files and Z-ORDER on selective filter column
OPTIMIZE dw.fact_sales
ZORDER BY (customer_id);

-- Materialized view for a dashboard aggregation
CREATE MATERIALIZED VIEW dw.mv_sales_by_region
AS
SELECT
  region,
  CAST(sale_date AS DATE)  AS sale_date,
  SUM(amount)              AS total_revenue,
  COUNT(*)                 AS order_count
FROM dw.fact_sales f
JOIN dw.dim_customer c ON f.customer_id = c.customer_id
GROUP BY region, CAST(sale_date AS DATE);

For deeper capacity planning and pause/resume strategies, see Fabric Data Warehouse Optimization and Fabric Capacity Optimization.

GPU-Accelerated Warehouse & AI Functions — June 2026

GPU-Accelerated Fabric Data Warehouse

At Microsoft Build 2026, Microsoft announced GPU-accelerated Fabric Data Warehouse as an early access preview, entering early access in June 2026 with GA targeted for July 2026.

AI Functions in Fabric Data Warehouse — June 2026

As of June 2026, AI Functions in Fabric Data Warehouse default to gpt-5-mini with low reasoning enabled. For more complex transformations, configure gpt-5.1 and tune the reasoning_effort parameter. AI Functions support sentiment analysis, text classification, summarization, and entity extraction directly within T-SQL queries and Dataflow Gen2.

Operationalizing — Pipelines, Monitoring & CI/CD

Production data warehouses require reliable orchestration, testable deployments, and observable pipelines. Fabric provides native tooling for all three.

Orchestration with Data Pipelines

Use Data Pipelines to orchestrate Warehouse stored procedures, notebook executions, and Dataflow Gen2 runs. Configure retry policies and failure alerts per activity. Pass run_date, environment, and source system parameters into activities so the same pipeline works across dev, test, and prod workspaces.

-- Audit table: log row counts after every load
INSERT INTO dw.load_audit (table_name, load_ts, row_count, status)
SELECT
  'dw.fact_sales',
  SYSDATETIME(),
  COUNT(*),
  'Succeeded'
FROM dw.fact_sales;

CI/CD and Git Integration

Connect your Fabric workspace to a Git repository — Azure DevOps or GitHub. Warehouse DDL scripts, stored procedures, and pipeline definitions are version-controlled. Use pull request approvals and automated integration tests — row count checks, constraint validation, DAX measure assertions — before merging schema changes to production.

For step-by-step orchestration examples, see Data Pipelines in Microsoft Fabric.

Security & Governance

Fabric centralises Warehouse, Lakehouse, and Power BI on OneLake — which makes consistent identity, access control, lineage, and monitoring essential for compliance and data trust.

1. Workspace RBACAssign Admin, Member, Contributor, or Viewer roles to control who can create or modify Warehouse items, pipelines, and notebooks.
2. Object-level securityUse T-SQL GRANT/REVOKE to control access to specific tables, views, and stored procedures. Workspace-level access does not automatically grant access to all tables.
3. Row-level securityApply row filters using T-SQL security predicates when different user groups must see different subsets of the same table.
4. Column maskingMask or hash PII columns in the Warehouse, or expose only masked views to downstream consumers. Enforce at the data layer — not in Power BI measure logic.
5. Purview integrationRegister Warehouse objects in Microsoft Purview for lineage, sensitivity labels, and DLP policies. Test DLP policies before rollout to confirm they do not block expected query patterns.

-- Read-only role for analytics team
CREATE ROLE dw_readonly;
GRANT SELECT ON SCHEMA::dw TO dw_readonly;
EXEC sp_addrolemember 'dw_readonly', 'analytics_team_group';

-- Row-level security: reps see only their region
CREATE FUNCTION dw.fn_rls_region (@region NVARCHAR(50))
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS result
WHERE @region = USER_NAME() OR IS_MEMBER('dw_admin') = 1;

CREATE SECURITY POLICY dw.region_filter
ADD FILTER PREDICATE dw.fn_rls_region(region) ON dw.fact_sales
WITH (STATE = ON);

For a dedicated governance guide including Purview setup, see Microsoft Fabric Data Governance Tutorial.

Synapse Dedicated SQL Pool to Fabric — Migration Guide

T-SQL compatibility between Synapse Dedicated SQL Pools and Fabric Warehouse is high. The differences are in DDL syntax related to distribution and storage, which Fabric manages automatically.

Step 1 — Remove Synapse-Specific DDL Syntax

Remove DISTRIBUTION (HASH, ROUND_ROBIN, REPLICATE) and CLUSTERED COLUMNSTORE INDEX from all CREATE TABLE statements. Fabric’s engine manages distribution and columnar storage automatically. Also remove REBUILD, REORGANIZE INDEX, and CREATE STATISTICS calls — Fabric handles these automatically via OPTIMIZE.

-- Synapse (remove the WITH clause for Fabric)
CREATE TABLE dbo.fact_sales
(
  order_id    INT            NOT NULL,
  customer_id INT            NOT NULL,
  sale_date   DATE           NOT NULL,
  amount      DECIMAL(18,2)
)
WITH (DISTRIBUTION = HASH(customer_id),
      CLUSTERED COLUMNSTORE INDEX);  -- remove this block

-- Fabric (clean DDL)
CREATE TABLE dw.fact_sales
(
  order_id    INT            NOT NULL,
  customer_id INT            NOT NULL,
  sale_date   DATE           NOT NULL,
  amount      DECIMAL(18,2)
);

Step 2 — Data Movement Strategy

1. Export from Synapse via CETASUse CREATE EXTERNAL TABLE AS SELECT in Synapse to export data to ADLS Gen2 as Parquet. This runs in parallel and is orders of magnitude faster than row-by-row extraction.
2. Create a Shortcut in OneLakeIn your Fabric Lakehouse, create a Shortcut pointing to the ADLS Gen2 container where Synapse exported the Parquet files. The data appears as a table in OneLake immediately — no copy.
3. Ingest to Warehouse with COPY INTOUse COPY INTO in Fabric Warehouse to load the Parquet data at high speed from the Shortcut. This is the fastest supported path for bulk historical data ingestion.

For the full feature comparison, see Microsoft Fabric vs Azure Synapse — Full Comparison & Limitations.

Common Errors & Fixes

FAQ — Data Warehousing in Microsoft Fabric

Official Resources & Further Reading