Fabric Data Warehouse

↑ Back to top

6.1 Warehouse

6.1.1 What is a Fabric Data Warehouse?

A Fabric Data Warehouse is a SQL-first analytical data store in Microsoft Fabric. It is designed for structured enterprise data, complex SQL queries, dimensional modeling, reporting, and BI workloads.

Think of it as:

A cloud-based analytical database where you primarily work with SQL/T-SQL.

A typical architecture is:

                    Source Systems
                         │
          ┌──────────────┼──────────────┐
          ▼              ▼              ▼
      SQL Server      REST API       ADLS/Blob
          │              │              │
          └──────────────┼──────────────┘
                         ▼
                Fabric Data Factory
                         │
                         ▼
                ┌─────────────────┐
                │ Fabric Warehouse│
                │                 │
                │ FactSales       │
                │ DimCustomer     │
                │ DimProduct      │
                │ DimDate         │
                └────────┬────────┘
                         │
                         ▼
                  Semantic Model
                         │
                         ▼

Power BI

6.1.2 Warehouse vs Lakehouse

Fabric provides both Lakehouse and Warehouse. They serve different primary purposes.

Simple decision

Need Spark + Files + Data Engineering

↓

Lakehouse

Need SQL + Relational Analytics + BI

↓

Warehouse

They can also be used together.

6.1.3 When Should You Use a Warehouse?

A Fabric Warehouse is a strong choice when:

Data is primarily structured.

Your team is comfortable with SQL.

You need complex analytical queries.

You are building a star schema.

You need fact and dimension tables.

Business users need SQL access.

The primary consumers are BI/analytics applications.

For example:

ERP
 │
 ▼
Data Factory
 │
 ▼
Fabric Warehouse
 │
 ├── FactSales
 ├── DimCustomer
 ├── DimProduct
 └── DimDate
 │
 ▼

Power BI

6.1.4 Creating a Warehouse

In your Fabric workspace:

Workspace
   │
   ▼

+ New

   │
   ▼

Warehouse

Give it a meaningful name:

WH_Sales

After creation, you can work with the warehouse through the Fabric SQL experience.

6.1.5 Warehouse Structure

A typical warehouse contains:

WH_Sales
│
├── Tables
│     ├── FactSales
│     ├── DimCustomer
│     ├── DimProduct
│     └── DimDate
│
├── Views
│     ├── VW_CustomerSales
│     └── VW_MonthlySales
│
└── SQL objects

The exact set of supported database objects and capabilities can evolve, so check the current Fabric experience when implementing production workloads.

6.1.6 Star Schema

A very common Warehouse design is the star schema.

                    DimCustomer
                         │
                         │
                         ▼
DimDate ───────────► FactSales ◄────────── DimProduct
                         ▲
                         │
                         │

DimStore

The central table is the fact table.

The surrounding tables are dimension tables.

6.1.7 Fact Table

A fact table contains measurable business events.

Example:

FactSales
--------------------------------

SalesKey

DateKey

CustomerKey

ProductKey

StoreKey

Quantity

SalesAmount

CostAmount

DiscountAmount

Example data:

6.1.8 Dimension Table

Dimensions provide descriptive information.

Example:

DimCustomer
------------------------

CustomerKey

CustomerID

CustomerName

City

State

Country

CustomerSegment

Data:

6.1.9 Surrogate Keys

Warehouse dimensions commonly use surrogate keys.

Example:

CustomerID = C001

CustomerKey = 1001

CustomerID is the business/source key.

CustomerKey is the warehouse surrogate key.

This becomes particularly important for SCD Type 2.

Example:

CustomerKey | CustomerID | City

------------|------------|----------

1001 | C001 | Hyderabad

1055 | C001 | Bengaluru

The same business customer can therefore have multiple historical versions.

6.1.10 Grain of a Fact Table

Before creating a fact table, define its grain.

For example:

One row in FactSales represents one order line.

This means:

OrderID + ProductID

may identify one fact record.

Example:

Order 1001
 ├── Product A
 ├── Product B
 └── Product C

creates three fact rows.

Understanding grain is critical to avoiding double counting.

6.1.11 Creating a Dimension

Example:

CREATE TABLE dbo.DimCustomer

(

CustomerKey INT,

CustomerID VARCHAR(50),

CustomerName VARCHAR(200),

City VARCHAR(100),

State VARCHAR(100),

Country VARCHAR(100),

CustomerSegment VARCHAR(50)

);

6.1.12 Creating a Fact Table

CREATE TABLE dbo.FactSales

(

SalesKey BIGINT,

DateKey INT,

CustomerKey INT,

ProductKey INT,

Quantity INT,

SalesAmount DECIMAL(18,2),

CostAmount DECIMAL(18,2),

DiscountAmount DECIMAL(18,2)

);

For monetary values, use a suitable DECIMAL precision/scale rather than floating-point types.

6.1.13 Loading Data into Warehouse

A common Fabric pattern is:

Source
  │
  ▼
Data Factory
  │
  ▼
Staging / Transformation
  │
  ▼

Warehouse

For example:

SQL Server
    │
    ▼
Fabric Data Factory
    │
    ▼
WH_Sales
    │
    ├── DimCustomer
    ├── DimProduct
    └── FactSales

Fabric Data Factory can orchestrate the ingestion and transformation workflow.

6.1.14 Warehouse + Lakehouse Architecture

You don't necessarily have to choose only one.

A larger Fabric solution could look like:

                    Sources
                       │
                       ▼
                 Data Factory
                       │
                       ▼
                Bronze Lakehouse
                       │
                       ▼
                Silver Lakehouse
                       │
                       ▼
                 Curated Data
                       │
              ┌────────┴────────┐
              ▼                 ▼
          Gold Lakehouse    Warehouse
                                │
                                ▼
                         Semantic Model
                                │
                                ▼

Power BI

This pattern is useful when Spark/data engineering workloads and SQL-serving workloads have different requirements.

6.1.15 Warehouse Tables vs Lakehouse Tables

Suppose you have:

Customers

Products

Orders

Lakehouse

You might process them using:

df = spark.read.table(
    "Silver_Customers"

)

and write Delta tables.

Warehouse

You primarily interact with the data using SQL:

SELECT

CustomerID,

CustomerName

FROM dbo.DimCustomer;

This difference in primary experience is important.

6.1.16 Querying a Warehouse

Basic query:

SELECT *

FROM dbo.FactSales;

Better:

SELECT

SalesKey,

CustomerKey,

ProductKey,

Quantity,

SalesAmount

FROM dbo.FactSales;

6.1.17 Joining Fact and Dimension

SELECT
    f.SalesKey,
    c.CustomerName,
    p.ProductName,
    f.Quantity,
    f.SalesAmount
FROM dbo.FactSales f
JOIN dbo.DimCustomer c
    ON f.CustomerKey = c.CustomerKey
JOIN dbo.DimProduct p
    ON f.ProductKey = p.ProductKey;

This produces business-readable analytical data.

6.1.18 Aggregating Warehouse Data

Revenue by country:

SELECT
    c.Country,
    SUM(f.SalesAmount) AS Revenue
FROM dbo.FactSales f
JOIN dbo.DimCustomer c
    ON f.CustomerKey = c.CustomerKey
GROUP BY
    c.Country
ORDER BY
    Revenue DESC;

Example result:

6.1.19 Warehouse and SCD Type 2

Suppose DimCustomer uses SCD Type 2:

A historical fact can reference:

CustomerKey = 1001

while a newer fact references:

CustomerKey = 1055

This allows historical reporting.

6.1.20 Warehouse for Reporting

A typical Power BI architecture:

                  Fabric Warehouse
                         │
            ┌────────────┼────────────┐
            ▼            ▼            ▼
        FactSales    Dimensions     Views
            │            │            │
            └────────────┼────────────┘
                         ▼
                  Semantic Model
                         │
                         ▼

Power BI

The Warehouse becomes the structured serving layer for analytics.

6.1.21 Example: Complete Sales Warehouse

Let's design a simple sales warehouse.

Dimensions

DimDate

DimCustomer

DimProduct

DimStore

Fact

FactSales

Architecture:

                       DimDate
                          │
                          │
                          ▼
DimCustomer ───────► FactSales ◄────── DimProduct
                          ▲
                          │
                          │

DimStore

6.1.22 DimDate

Example:

CREATE TABLE dbo.DimDate

(

DateKey INT,

FullDate DATE,

DayNumber INT,

MonthNumber INT,

MonthName VARCHAR(20),

QuarterNumber INT,

YearNumber INT

);

Example:

6.1.23 DimProduct

CREATE TABLE dbo.DimProduct

(

ProductKey INT,

ProductID VARCHAR(50),

ProductName VARCHAR(200),

Category VARCHAR(100),

Brand VARCHAR(100)

);

6.1.24 FactSales

CREATE TABLE dbo.FactSales

(

SalesKey BIGINT,

DateKey INT,

CustomerKey INT,

ProductKey INT,

Quantity INT,

SalesAmount DECIMAL(18,2),

CostAmount DECIMAL(18,2)

);

6.1.25 Business Query

Now calculate profit by category:

SELECT
    p.Category,
    SUM(f.SalesAmount) AS Revenue,
    SUM(f.CostAmount) AS Cost,
    SUM(f.SalesAmount - f.CostAmount) AS Profit
FROM dbo.FactSales f
JOIN dbo.DimProduct p
    ON f.ProductKey = p.ProductKey
GROUP BY
    p.Category
ORDER BY
    Revenue DESC;

6.1.26 Warehouse Views

Instead of exposing complex SQL repeatedly, create a view:

CREATE VIEW dbo.VW_ProductPerformance

AS

SELECT
    p.ProductID,
    p.ProductName,
    p.Category,
    SUM(f.Quantity) AS UnitsSold,
    SUM(f.SalesAmount) AS Revenue,
    SUM(f.CostAmount) AS Cost,
    SUM(f.SalesAmount - f.CostAmount) AS Profit
FROM dbo.FactSales f
JOIN dbo.DimProduct p
    ON f.ProductKey = p.ProductKey
GROUP BY
    p.ProductID,
    p.ProductName,
    p.Category;

Then:

SELECT *

FROM dbo.VW_ProductPerformance;

6.1.27 Warehouse Security Concept

A production warehouse should follow least-privilege principles.

For example:

Data Engineer
     │
     ├── Build/modify data
     │
     ▼

Warehouse

Analyst
     │
     ├── Read curated data
     │
     ▼

Views / Tables

Business User
     │
     ▼

Semantic Model / Power BI

Don't expose sensitive columns unnecessarily.

For example:

Customer
├── CustomerID
├── Name
├── Country
├── Email
└── Phone

A reporting layer may only need:

CustomerID

Name

Country

6.1.28 Warehouse Best Practices

1. Define the grain

Always document what one fact row represents.

2. Use a star schema where appropriate

Fact
 +

Dimensions

3. Use meaningful names

FactSales

DimCustomer

DimProduct

4. Use appropriate data types

Especially for:

Dates

Decimal values

IDs

Text

5. Avoid unnecessary columns

Don't bring every source column into analytical tables.

6. Separate ingestion and presentation logic

Don't mix raw ingestion logic with business reporting logic.

7. Validate row counts

For example:

Source Orders

↓

Warehouse FactSales

Compare expected counts and totals.

8. Monitor data quality

Check:

NULLs

Duplicates

Missing dimension keys

Invalid dates

Unexpected amounts

6.1.29 Common Mistakes

Mistake 1 — Treating Warehouse like a raw data lake

Warehouse should generally contain curated structured analytical data.

Mistake 2 — No defined fact grain

This can cause incorrect aggregations.

Mistake 3 — Joining on the wrong key

Always understand business keys and surrogate keys.

Mistake 4 — Ignoring SCD

If historical dimension changes matter, design for them.

Mistake 5 — Building one giant table

A well-designed dimensional model can be easier to maintain and query.

Mistake 6 — Using floating-point types for money

Prefer an appropriate decimal type.

6.1.30 Interview Questions

What is a Fabric Warehouse?

A SQL-first analytical data store in Microsoft Fabric designed for structured analytical and BI workloads.

Warehouse vs Lakehouse?

Lakehouse → Spark + Files + Delta + Data Engineering

Warehouse → SQL + Relational Analytics + BI

What is a fact table?

A table containing measurable business events.

What is a dimension table?

A table containing descriptive attributes used to analyze facts.

What is a star schema?

A central fact table connected to multiple dimension tables.

What is fact table grain?

The precise definition of what one row in the fact table represents.

Why use surrogate keys?

To uniquely identify dimension versions and support historical modeling such as SCD Type 2.

What is a view?

A reusable logical SQL definition over underlying data.

6.1.31 Hands-On Exercise

Create a Fabric Warehouse named:

WH_Sales

Create these tables:

DimDate

DimCustomer

DimProduct

FactSales

Build this model:

                 DimDate
                    │
                    ▼
DimCustomer ───► FactSales ◄─── DimProduct

Then write the following queries:

Exercise 1 — Total Revenue

SELECT
    SUM(SalesAmount) AS TotalRevenue
FROM dbo.FactSales;

Exercise 2 — Revenue by Country

SELECT
    c.Country,
    SUM(f.SalesAmount) AS Revenue
FROM dbo.FactSales f
JOIN dbo.DimCustomer c
    ON f.CustomerKey = c.CustomerKey
GROUP BY c.Country;

Exercise 3 — Revenue by Product Category

SELECT
    p.Category,
    SUM(f.SalesAmount) AS Revenue
FROM dbo.FactSales f
JOIN dbo.DimProduct p
    ON f.ProductKey = p.ProductKey
GROUP BY p.Category;

Exercise 4 — Profit

SELECT
    SUM(SalesAmount - CostAmount) AS Profit
FROM dbo.FactSales;

Exercise 5 — Top Products

SELECT TOP 10
    p.ProductName,
    SUM(f.SalesAmount) AS Revenue
FROM dbo.FactSales f
JOIN dbo.DimProduct p
    ON f.ProductKey = p.ProductKey
GROUP BY
    p.ProductName
ORDER BY
    Revenue DESC;

6.1.32 Key Takeaway

Think of Fabric Warehouse as the SQL-first analytical serving layer:

                 DATA SOURCES
                      │
                      ▼
               DATA INGESTION
                      │
                      ▼

LAKEHOUSE

              Bronze → Silver
                      │
                      ▼
              Curated / Gold
                      │
                      ▼
             FABRIC WAREHOUSE
                      │
          ┌───────────┼───────────┐
          ▼           ▼           ▼
       Facts     Dimensions     Views
          │           │           │
          └───────────┼───────────┘
                      ▼
               SEMANTIC MODEL
                      │
                      ▼

POWER BI

In one sentence:

Fabric Warehouse is where structured, business-ready data can be modeled and queried using SQL/T-SQL for enterprise analytics and BI.

↑ Back to top

↑ Back to top

6.2 T-SQL

6.2.1 What is T-SQL?

T-SQL (Transact-SQL) is Microsoft's extension of SQL used for querying and manipulating data in Microsoft data platforms.

In Microsoft Fabric Warehouse, T-SQL is the primary language used to:

Query tables

Filter data

Join tables

Aggregate data

Create tables

Create views

Transform data

Perform analytical calculations

Build reporting datasets

Think of it as:

SQL + Microsoft-specific capabilities for working with relational data.

6.2.2 T-SQL in Fabric Warehouse

A typical workflow is:

Fabric Warehouse
      │
      ├── Tables
      │     ├── FactSales
      │     ├── DimCustomer
      │     └── DimProduct
      │
      ├── Views
      │
      └── SQL Query
             │
             ▼

Results

For example:

SELECT

CustomerID,

CustomerName,

Country

FROM dbo.DimCustomer;

6.2.3 Basic SELECT

The SELECT statement retrieves data.

SELECT *

FROM dbo.DimCustomer;

However, in production code, avoid unnecessary SELECT *.

Prefer:

SELECT

CustomerID,

CustomerName,

City,

Country

FROM dbo.DimCustomer;

6.2.4 Column Aliases

You can rename output columns.

SELECT

CustomerID AS [Customer ID],

CustomerName AS [Customer Name],

Country AS [Customer Country]

FROM dbo.DimCustomer;

Or simpler aliases:

SELECT

CustomerID,

CustomerName,

Country

FROM dbo.DimCustomer;

6.2.5 DISTINCT

Returns unique values.

SELECT DISTINCT

Country

FROM dbo.DimCustomer;

Example result:

India

USA

UK

Australia

6.2.6 WHERE

WHERE filters rows.
SELECT
    CustomerID,
    CustomerName,
    Country
FROM dbo.DimCustomer
WHERE Country = 'India';

Multiple conditions:

SELECT *
FROM dbo.DimCustomer
WHERE Country = 'India'

AND City = 'Hyderabad';

6.2.7 AND / OR

SELECT *
FROM dbo.DimCustomer
WHERE Country = 'India'

AND City = 'Hyderabad';

Using OR:

SELECT *
FROM dbo.DimCustomer
WHERE City = 'Hyderabad'

OR City = 'Bengaluru';

Use parentheses when combining AND and OR:

SELECT *
FROM dbo.DimCustomer
WHERE Country = 'India'

AND (

        City = 'Hyderabad'
        OR City = 'Bengaluru'
      );

6.2.8 IN

Instead of:

WHERE City = 'Hyderabad'

OR City = 'Bengaluru'

OR City = 'Chennai'

use:

WHERE City IN

(

'Hyderabad',

'Bengaluru',

'Chennai'

);

6.2.9 BETWEEN

SELECT *

FROM dbo.FactSales

WHERE SalesAmount BETWEEN 1000 AND 5000;

For dates:

SELECT *

FROM dbo.FactSales

WHERE OrderDate BETWEEN

'2026-01-01'

AND

'2026-01-31';

For timestamp columns, be careful with BETWEEN because the upper boundary can unintentionally exclude later times on the final day. A half-open interval is often safer:

WHERE OrderDate >= '2026-01-01'

AND OrderDate < '2026-02-01';

6.2.10 LIKE

Search for patterns.

SELECT *

FROM dbo.DimCustomer

WHERE CustomerName LIKE 'Sri%';

Meaning:

Sri

Sreehari

Srinivas

Sridhar

...

Other patterns:

LIKE '%hari'

LIKE '%ree%'

LIKE 'S%'

6.2.11 NULL Handling

Find NULL values:

SELECT *

FROM dbo.DimCustomer

WHERE Country IS NULL;

Find non-NULL:

SELECT *

FROM dbo.DimCustomer

WHERE Country IS NOT NULL;

Incorrect:

WHERE Country = NULL

Correct:

WHERE Country IS NULL

6.2.12 COALESCE

Replace NULL with a value.

SELECT
    CustomerID,
    COALESCE(Country, 'Unknown') AS Country
FROM dbo.DimCustomer;

Example:

NULL

↓

Unknown

6.2.13 CASE

CASE is one of the most useful T-SQL transformation features.

SELECT
    SalesAmount,
    CASE
        WHEN SalesAmount >= 10000
            THEN 'High'
        WHEN SalesAmount >= 5000
            THEN 'Medium'
        ELSE 'Low'
    END AS SalesCategory
FROM dbo.FactSales;

Result:

6.2.14 Aggregate Functions

Common aggregate functions:

SUM()
COUNT()
AVG()
MIN()
MAX()

Example:

SELECT
    SUM(SalesAmount) AS Revenue,
    AVG(SalesAmount) AS AverageSale,
    MIN(SalesAmount) AS MinimumSale,
    MAX(SalesAmount) AS MaximumSale,
    COUNT(*) AS RecordCount
FROM dbo.FactSales;

6.2.15 COUNT Variations

Count rows

SELECT COUNT(*) AS TotalRows
FROM dbo.FactSales;

Count non-NULL values

SELECT COUNT(CustomerKey)
FROM dbo.FactSales;

Count unique customers

SELECT COUNT(DISTINCT CustomerKey)
FROM dbo.FactSales;

6.2.16 GROUP BY

Group data before aggregation.

Revenue by country:

SELECT
    c.Country,
    SUM(f.SalesAmount) AS Revenue
FROM dbo.FactSales f
JOIN dbo.DimCustomer c
    ON f.CustomerKey = c.CustomerKey
GROUP BY
    c.Country;

Result:

6.2.17 HAVING

HAVING filters groups after aggregation.

SELECT
    c.Country,
    SUM(f.SalesAmount) AS Revenue
FROM dbo.FactSales f
JOIN dbo.DimCustomer c
    ON f.CustomerKey = c.CustomerKey
GROUP BY
    c.Country

HAVING

SUM(f.SalesAmount) > 10000000;

Important difference:

WHERE

↓

Filters individual rows

GROUP BY

↓

Creates groups

HAVING

↓

Filters groups

6.2.18 ORDER BY

SELECT

CustomerName,

Country

FROM dbo.DimCustomer

ORDER BY CustomerName;

Descending:

ORDER BY CustomerName DESC;

Multiple columns:

ORDER BY

Country ASC,

CustomerName ASC;

6.2.19 TOP

Retrieve a limited number of rows.

SELECT TOP 10

ProductID,

ProductName

FROM dbo.DimProduct;

Usually combine with ORDER BY when you want a meaningful top/bottom result:

SELECT TOP 10

ProductID,

ProductName,

SalesAmount

FROM dbo.FactSales

ORDER BY SalesAmount DESC;

6.2.20 INNER JOIN

Returns matching records.

SELECT

f.SalesKey,

c.CustomerName,

f.SalesAmount

FROM dbo.FactSales f

INNER JOIN dbo.DimCustomer c

ON f.CustomerKey = c.CustomerKey;

6.2.21 LEFT JOIN

Keeps all records from the left table.

SELECT

f.SalesKey,

c.CustomerName,

f.SalesAmount

FROM dbo.FactSales f

LEFT JOIN dbo.DimCustomer c

ON f.CustomerKey = c.CustomerKey;

This is particularly useful when you want to detect missing dimension records.

6.2.22 Multiple Joins

SELECT

f.SalesKey,

c.CustomerName,

p.ProductName,

p.Category,

f.Quantity,

f.SalesAmount

FROM dbo.FactSales f

LEFT JOIN dbo.DimCustomer c

ON f.CustomerKey = c.CustomerKey

LEFT JOIN dbo.DimProduct p

ON f.ProductKey = p.ProductKey;

6.2.23 FULL OUTER JOIN

Useful for reconciliation.

SELECT

s.CustomerID AS SourceCustomer,

t.CustomerID AS TargetCustomer

FROM SourceCustomers s

FULL OUTER JOIN TargetCustomers t

ON s.CustomerID = t.CustomerID;

This helps identify:

Source only

Target only

Both

6.2.24 CTE — Common Table Expression

A CTE temporarily defines a query result that can be referenced by the following statement.

WITH CustomerSales AS

(

    SELECT
        CustomerKey,
        SUM(SalesAmount) AS Revenue
    FROM dbo.FactSales
    GROUP BY CustomerKey

)

SELECT
    c.CustomerName,
    cs.Revenue
FROM CustomerSales cs
JOIN dbo.DimCustomer c
    ON cs.CustomerKey = c.CustomerKey;

CTEs are excellent for making complex transformations readable.

6.2.25 Multiple CTEs

WITH Sales AS

(

    SELECT
        CustomerKey,
        SUM(SalesAmount) AS Revenue
    FROM dbo.FactSales
    GROUP BY CustomerKey

),

CustomerInfo AS

(

SELECT

CustomerKey,

CustomerName,

Country

FROM dbo.DimCustomer

)

SELECT
    c.CustomerName,
    c.Country,
    s.Revenue
FROM Sales s
JOIN CustomerInfo c
    ON s.CustomerKey = c.CustomerKey;

6.2.26 Subqueries

A subquery is a query inside another query.

SELECT

CustomerKey,

SalesAmount

FROM dbo.FactSales

WHERE SalesAmount >

(

    SELECT AVG(SalesAmount)
    FROM dbo.FactSales

);

This returns sales above the average sale amount.

6.2.27 EXISTS

Check whether a matching record exists.

SELECT

c.CustomerID,

c.CustomerName

FROM dbo.DimCustomer c

WHERE EXISTS

(

    SELECT 1
    FROM dbo.FactSales f
    WHERE f.CustomerKey = c.CustomerKey

);

This returns customers who have at least one sales record.

6.2.28 NOT EXISTS

Find customers with no sales:

SELECT

c.CustomerID,

c.CustomerName

FROM dbo.DimCustomer c

WHERE NOT EXISTS

(

    SELECT 1
    FROM dbo.FactSales f
    WHERE f.CustomerKey = c.CustomerKey

);

This is a useful data-quality pattern.

6.2.29 Window Functions

T-SQL supports analytical window functions.

Customer total

SELECT
    CustomerKey,
    SalesAmount,
    SUM(SalesAmount) OVER
    (
        PARTITION BY CustomerKey
    ) AS CustomerRevenue
FROM dbo.FactSales;

Unlike GROUP BY, this keeps individual rows.

6.2.30 ROW_NUMBER

Find the latest record per customer:

WITH RankedCustomers AS

(

    SELECT
        CustomerID,
        CustomerName,
        UpdatedDate,
        ROW_NUMBER() OVER
        (
            PARTITION BY CustomerID
            ORDER BY UpdatedDate DESC
        ) AS rn
    FROM dbo.CustomerStage

)

SELECT *
FROM RankedCustomers
WHERE rn = 1;

This is a very common data-engineering pattern.

6.2.31 RANK and DENSE_RANK

SELECT

ProductID,

SalesAmount,

RANK() OVER

(

        ORDER BY SalesAmount DESC
    ) AS SalesRank,

DENSE_RANK() OVER

(

        ORDER BY SalesAmount DESC
    ) AS DenseSalesRank

FROM dbo.FactSales;

Remember:

ROW_NUMBER()

→ 1, 2, 3, 4

RANK()

→ 1, 1, 3, 4

DENSE_RANK()

→ 1, 1, 2, 3

6.2.32 LAG and LEAD

Previous sale:

SELECT
    OrderDate,
    SalesAmount,
    LAG(SalesAmount) OVER
    (
        ORDER BY OrderDate
    ) AS PreviousSales
FROM dbo.DailySales;

Next sale:

SELECT
    OrderDate,
    SalesAmount,
    LEAD(SalesAmount) OVER
    (
        ORDER BY OrderDate
    ) AS NextSales
FROM dbo.DailySales;

Useful for:

Month-over-month analysis

Change detection

Time-series analysis

SCD processing

6.2.33 Running Total

SELECT
    OrderDate,
    SalesAmount,
    SUM(SalesAmount) OVER
    (
        ORDER BY OrderDate
        ROWS BETWEEN
            UNBOUNDED PRECEDING
            AND CURRENT ROW
    ) AS RunningRevenue
FROM dbo.FactSales;

Example:

6.2.34 Creating Tables

CREATE TABLE dbo.DimProduct

(

ProductKey INT,

ProductID VARCHAR(50),

ProductName VARCHAR(200),

Category VARCHAR(100),

Brand VARCHAR(100)

);

6.2.35 INSERT

INSERT INTO dbo.DimProduct

(

ProductKey,

ProductID,

ProductName,

Category,

Brand

)

VALUES

(

101,

'P001',

'Laptop',

'Electronics',

'ABC'

);

6.2.36 INSERT from SELECT

Very useful in ETL/ELT workflows.

INSERT INTO dbo.Gold_ProductSales

(

ProductKey,

Revenue

)

SELECT
    ProductKey,
    SUM(SalesAmount)
FROM dbo.FactSales
GROUP BY ProductKey;

6.2.37 UPDATE

Example:

UPDATE dbo.DimProduct

SET

    Category = 'Computers'
WHERE ProductID = 'P001';

Be very careful with the WHERE clause.

Without it:

UPDATE dbo.DimProduct

SET Category = 'Computers';

you could modify every row.

6.2.38 DELETE

DELETE FROM dbo.DimProduct
WHERE ProductID = 'P001';

Again, always validate the filter before executing destructive operations.

6.2.39 MERGE and Upsert

A common ETL requirement is:

If record exists → UPDATE

If record doesn't exist → INSERT

Conceptually:

MERGE INTO dbo.DimProduct AS target

USING dbo.StageProduct AS source

ON target.ProductID = source.ProductID

WHEN MATCHED THEN

    UPDATE SET
        target.ProductName = source.ProductName,
        target.Category = source.Category

WHEN NOT MATCHED THEN

INSERT

(

ProductID,

ProductName,

Category

)

VALUES

(

source.ProductID,

source.ProductName,

source.Category

);

For production Fabric Warehouse development, verify the current supported DML/MERGE behavior and limitations for your specific scenario before standardizing on it.

6.2.40 T-SQL and SCD Type 2

Suppose:

DimCustomer

contains:

Source changes:

C001 → Bengaluru

Type 2 requires:

1. Expire Hyderabad

2. Insert Bengaluru

Expire:

UPDATE dbo.DimCustomer

SET

    EndDate = '2026-08-21',
    IsCurrent = 0
WHERE CustomerID = 'C001'

AND IsCurrent = 1;

Then insert the new version:

INSERT INTO dbo.DimCustomer

(

CustomerKey,

CustomerID,

City,

StartDate,

EndDate,

IsCurrent

)

VALUES

(

1055,

'C001',

'Bengaluru',

'2026-08-22',

NULL,

1

);

Production SCD implementations should also address surrogate-key generation, idempotency, late-arriving changes, and concurrent processing.

6.2.41 Temporary/Staging Logic

Data engineering workflows often use staging objects to prepare data before loading curated tables.

Conceptually:

Source

↓

Stage

↓

Transform

↓

Warehouse

For example:

StageCustomer

↓

Deduplicate

↓

Validate

↓

DimCustomer

The exact staging mechanism should follow the capabilities and recommended patterns available in your current Fabric Warehouse environment.

6.2.42 T-SQL for Data Quality

Find duplicate Customer IDs

SELECT
    CustomerID,
    COUNT(*) AS RecordCount
FROM dbo.DimCustomer
GROUP BY CustomerID

HAVING COUNT(*) > 1;

Find NULL customer names

SELECT *

FROM dbo.DimCustomer

WHERE CustomerName IS NULL;

Find orphan fact records

SELECT

f.CustomerKey

FROM dbo.FactSales f

LEFT JOIN dbo.DimCustomer c

ON f.CustomerKey = c.CustomerKey

WHERE c.CustomerKey IS NULL;

These checks can be incorporated into pipeline validation.

6.2.43 T-SQL for Reconciliation

Suppose you need to compare source and target counts:

SELECT
    COUNT(*) AS WarehouseCount
FROM dbo.FactSales;

You can compare:

Source count

↓

Target count

↓

Difference

For monetary reconciliation:

SELECT
    SUM(SalesAmount) AS TotalSales
FROM dbo.FactSales;

You can compare this against the source system's total.

6.2.44 Query Execution Order

One of the most important SQL concepts is the logical processing order.

Although we write:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

the logical order is approximately:

FROM

↓

JOIN

↓

WHERE

↓

GROUP BY

↓

HAVING

↓

SELECT

↓

ORDER BY

Understanding this explains many SQL behaviors.

For example, this generally doesn't work:

SELECT

SalesAmount AS Revenue

FROM dbo.FactSales

WHERE Revenue > 5000;

because WHERE is logically evaluated before the SELECT alias is established.

6.2.45 CTE vs Subquery

Both can solve similar problems.

Subquery

SELECT *

FROM

(

    SELECT
        CustomerKey,
        SUM(SalesAmount) AS Revenue
    FROM dbo.FactSales
    GROUP BY CustomerKey

) s

WHERE Revenue > 100000;

CTE

WITH Sales AS

(

    SELECT
        CustomerKey,
        SUM(SalesAmount) AS Revenue
    FROM dbo.FactSales
    GROUP BY CustomerKey

)

SELECT *

FROM Sales

WHERE Revenue > 100000;

For complex transformations, CTEs are often easier to read.

6.2.46 T-SQL Complete Example

Suppose you need:

Find the top 5 customers by revenue in India.

WITH CustomerRevenue AS

(

    SELECT
        c.CustomerKey,
        c.CustomerID,
        c.CustomerName,
        c.Country,
        SUM(f.SalesAmount) AS Revenue
    FROM dbo.FactSales f
    INNER JOIN dbo.DimCustomer c
        ON f.CustomerKey = c.CustomerKey
    WHERE c.Country = 'India'
    GROUP BY
        c.CustomerKey,
        c.CustomerID,
        c.CustomerName,
        c.Country

),

RankedCustomers AS

(

    SELECT
        *,
        ROW_NUMBER() OVER
        (
            ORDER BY Revenue DESC
        ) AS rn
    FROM CustomerRevenue

)

SELECT
    CustomerID,
    CustomerName,
    Country,
    Revenue
FROM RankedCustomers
WHERE rn <= 5
ORDER BY Revenue DESC;

This combines:

JOIN

↓

WHERE

↓

GROUP BY

↓

CTE

↓

Window Function

↓

Filtering

↓

ORDER BY

6.2.47 T-SQL in a Fabric Data Engineering Pipeline

A practical Fabric architecture could be:

                  SQL Server
                      │
                      ▼
                Data Factory
                      │
                      ▼
               Bronze Lakehouse
                      │
                      ▼
               Silver Lakehouse
                      │
              Clean / Transform
                      │
                      ▼
               Fabric Warehouse
                      │
          ┌───────────┼───────────┐
          ▼           ▼           ▼
        Facts      Dimensions    Views
          │           │           │
          └───────────┼───────────┘
                      │
                    T-SQL
                      │
                      ▼
                Semantic Model
                      │
                      ▼

Power BI

6.2.48 T-SQL Best Practices

Use explicit columns

Prefer:

SELECT

CustomerID,

CustomerName

FROM dbo.DimCustomer;

instead of:

SELECT *

FROM dbo.DimCustomer;

Use aliases

FROM dbo.FactSales f

JOIN dbo.DimCustomer c

Format SQL

Readable SQL is easier to maintain.

Validate joins

Check:

1:1

1:Many

Many:1

Many:Many

before joining.

Filter appropriately

Don't process unnecessary data.

Use CTEs for complex logic

They make transformation steps easier to understand.

Handle NULLs deliberately

Don't let NULL behavior produce incorrect business results.

Validate output

Check:

Row count

Revenue totals

Duplicate records

NULL values

Referential integrity

6.2.49 Common T-SQL Mistakes

Mistake 1 — WHERE column = NULL

Wrong:

WHERE Country = NULL

Correct:

WHERE Country IS NULL

Mistake 2 — Forgetting GROUP BY columns

If you select a non-aggregated column, it generally needs to be appropriately included in the grouping.

Mistake 3 — Accidental Cartesian join

Bad or missing join conditions can produce huge row counts.

Mistake 4 — Using INNER JOIN when unmatched records are needed

This silently removes unmatched rows.

Mistake 5 — Double counting after joins

Always understand table grain and cardinality.

Mistake 6 — Updating without a WHERE clause

Potentially modifies the entire table.

Mistake 7 — Using SELECT * everywhere

Makes pipelines fragile when schemas change and can increase unnecessary data processing.

6.2.50 Interview Questions

1. What is T-SQL?

Microsoft's SQL language extension used extensively for querying and manipulating relational data.

2. What is the difference between WHERE and HAVING?

WHERE → filters rows

HAVING → filters groups

3. What is a CTE?

A named temporary query expression available to the statement that follows it.

4. What is a window function?

A function that calculates across related rows without collapsing them.

5. Difference between RANK and DENSE_RANK?

RANK → gaps after ties

DENSE_RANK → no gaps after ties

6. How do you find duplicates?

SELECT
    CustomerID,
    COUNT(*)
FROM dbo.DimCustomer
GROUP BY CustomerID

HAVING COUNT(*) > 1;

7. How do you find orphan records?

Use LEFT JOIN + IS NULL, or LEFT ANTI where supported in the relevant SQL environment.

8. What is a CTE useful for?

Breaking a complex query into readable logical stages.

9. What is the difference between GROUP BY and a window function?

GROUP BY

→ reduces rows

Window function

→ keeps original rows

10. Why is table grain important?

Because incorrect grain can produce duplicate rows and incorrect aggregations.

6.2.51 T-SQL Cheat Sheet

SELECT

↓

Retrieve data

WHERE

↓

Filter rows

JOIN

↓

Combine tables

GROUP BY

↓

Create groups

HAVING

↓

Filter groups

ORDER BY

↓

Sort results

CASE

↓

Conditional logic

CTE

↓

Organize complex queries

WINDOW FUNCTIONS

↓

Ranking / running totals /

previous-next analysis

INSERT

↓

Add records

UPDATE

↓

Modify records

DELETE

↓

Remove records

CREATE TABLE

↓

Create warehouse table

CREATE VIEW

↓

Create reusable SQL layer

Final Mental Model

             T-SQL
               │
      ┌────────┼─────────┐
      ▼        ▼         ▼
   Query     Transform   Model
      │        │         │
      ▼        ▼         ▼

SELECT CASE CREATE TABLE

JOIN CTE CREATE VIEW

GROUP BY Window etc.

 WHERE
      │
      ▼
Fabric Warehouse
      │
      ▼
Semantic Model
      │
      ▼

Power BI

For Fabric Data Engineers, T-SQL is the core SQL skill for querying, transforming, validating, and serving structured data from a Fabric Warehouse.

↑ Back to top

↑ Back to top

↑ Back to top

↑ Back to top

↑ Back to top

6.5 Performance 6.5 Performance in Microsoft Fabric Data Warehouse

When we talk about Performance in Fabric Data Warehouse, we mean:

How efficiently the Warehouse can load, process, and query data while using Fabric capacity effectively.

For example, suppose this query takes 2 minutes:

SELECT *

FROM FactSales;

After improving the query and data model, the required business query might take 10 seconds.

That improvement is performance optimization.

For Fabric Warehouse, I recommend understanding performance in five areas:

PERFORMANCE
    │
    ├── 1. Data Model
    ├── 2. SQL Queries
    ├── 3. Statistics
    ├── 4. Data Loading
    └── 5. Capacity / Concurrency

1. First understand Fabric Warehouse architecture

A simplified architecture is:

                    Users / Power BI
                           │
                           ▼
                        T-SQL
                           │
                           ▼
                ┌────────────────────┐
                │  Fabric Warehouse  │
                │                    │
                │ Distributed Query  │
                │ Processing         │
                └─────────┬──────────┘
                          │
                          ▼
                       OneLake
                          │
                          ▼

Warehouse Data

Fabric Warehouse is a SaaS analytical warehouse.

Microsoft handles much of the underlying infrastructure that you would traditionally have to manage yourself.

Therefore, Fabric performance tuning is somewhat different from traditional SQL Server tuning.

2. Traditional SQL Server vs Fabric Warehouse performance

If you're coming from SQL Server or Azure Synapse, this distinction is important.

In traditional SQL Server, DBAs frequently think about:

Indexes

Memory

CPU

TempDB

Disk

Filegroups

Partitions

Statistics

Execution Plans

Server Configuration

In Fabric Warehouse, much of the infrastructure management is handled by the service.

Your focus shifts more toward:

Good Data Model
       +
Good SQL
       +
Statistics
       +
Efficient Data Loading
       +
Capacity Management
       +

Concurrency Management

3. Performance starts with the data model

One of the biggest mistakes is thinking:

"My query is slow, so I need to tune the query."

Sometimes the real problem is the data model.

Suppose you have one huge table:

Sales_All_Data

OrderID

OrderDate

CustomerID

CustomerName

CustomerAddress

CustomerCountry

ProductID

ProductName

ProductCategory

SupplierName

Quantity

SalesAmount

StoreName

Region

...

Millions or billions of rows repeatedly containing descriptive text isn't an ideal warehouse model.

Instead, use a Star Schema.

                         DIM_DATE
                            │
                            │
                            ▼
DIM_CUSTOMER ─────────► FACT_SALES ◄──────── DIM_PRODUCT
                            ▲
                            │
                            │

DIM_STORE

This is important for both warehouse organization and downstream BI performance.

4. Fact and Dimension tables

A typical fact table:

FACT_SALES
─────────────────

SalesKey

DateKey

CustomerKey

ProductKey

StoreKey

Quantity

SalesAmount

DiscountAmount

ProfitAmount

Dimension:

DIM_CUSTOMER
─────────────────

CustomerKey

CustomerID

CustomerName

Country

Region

Segment

Another dimension:

DIM_PRODUCT
─────────────────

ProductKey

ProductID

ProductName

Category

Brand

The fact table contains primarily:

Keys + Measures

Dimensions contain:

Descriptive attributes

This structure helps analytical queries.

5. Avoid SELECT *

One of the simplest performance practices is:

Retrieve only the columns you actually need.

Bad:

SELECT *

FROM FactSales;

Suppose FactSales has:

50 columns

500 million rows

but your report needs only:

OrderDate

ProductKey

SalesAmount

Then write:

SELECT

OrderDate,

ProductKey,

SalesAmount

FROM FactSales;

Conceptually:

SELECT *

50 columns
    │
    ▼
More data processed
    │
    ▼

More work

SELECT required columns

3 columns
    │
    ▼

Less unnecessary processing

This is especially relevant to analytical columnar systems.

6. Filter data early

Suppose FactSales contains 10 years of data:

2017

2018

...

2025

2026

Your report needs only 2026.

Avoid processing all historical rows unnecessarily.

Use an appropriate filter:

SELECT
    ProductKey,
    SalesAmount
FROM FactSales
WHERE OrderDate >= '2026-01-01'

AND OrderDate < '2027-01-01';

The principle is:

Huge Dataset
     │
     ▼
Filter Early
     │
     ▼
Smaller Dataset
     │
     ▼

Join / Aggregate

7. Write SARGable predicates

This is a useful SQL-performance concept.

Suppose you want 2026 orders.

You may see:

WHERE YEAR(OrderDate) = 2026

A generally better predicate is:

WHERE OrderDate >= '2026-01-01'

AND OrderDate < '2027-01-01'

Why?

Applying functions directly to filtered columns can sometimes make optimization harder.

Remember:

Filter the column directly where possible instead of wrapping it in unnecessary functions.

8. Reduce data before JOIN operations

Suppose:

FactSales

500 million rows

DimCustomer

10 million rows

Don't carry unnecessary data through complicated joins.

Conceptually, prefer:

FactSales
    │
    ▼
Filter
    │
    ▼
Required Sales
    │
    ▼
JOIN
    │
    ▼

DimCustomer

rather than performing unnecessary processing over the entire dataset first.

9. Understand JOIN performance

Warehouse queries frequently involve joins.

Example:

SELECT
    c.Country,
    SUM(f.SalesAmount) AS TotalSales
FROM FactSales AS f
JOIN DimCustomer AS c
    ON f.CustomerKey = c.CustomerKey
GROUP BY
    c.Country;

This is a natural Star Schema query.

FactSales
   │
   │ CustomerKey
   ▼
DimCustomer
   │
   ▼

Country

Good warehouse design makes these relationships straightforward.

10. Avoid unnecessary joins

Suppose you write:

SELECT
    c.CustomerName,
    SUM(f.SalesAmount)
FROM FactSales f

JOIN DimCustomer c
    ON f.CustomerKey = c.CustomerKey

JOIN DimProduct p
    ON f.ProductKey = p.ProductKey

JOIN DimStore s
    ON f.StoreKey = s.StoreKey

JOIN DimDate d
    ON f.DateKey = d.DateKey

GROUP BY
    c.CustomerName;

But the query only requires customer information.

Then:

DimProduct

DimStore

DimDate

may not be needed for that particular query.

Don't join tables simply because they're available.

11. Data types affect performance

Data types matter.

Bad design:

CustomerKey VARCHAR(100)

ProductKey VARCHAR(100)

DateKey VARCHAR(100)

when the keys are numeric.

Better:

CustomerKey BIGINT / INT

ProductKey BIGINT / INT

DateKey INT

Choose the appropriate data type for the data.

Don't automatically make every string:

VARCHAR(8000)

if:

VARCHAR(50)

is sufficient.

Correct data types improve storage efficiency and simplify processing.

12. Avoid implicit conversions

Suppose:

CustomerKey

=

INT

but your query compares it against incompatible text values.

Type mismatches can cause conversions.

Conceptually:

INT
 │
 ▼
Conversion
 │
 ▼

Comparison

Better:

Same Data Type
      │
      ▼

Comparison

Make join keys and filtering columns use compatible data types.

13. Statistics

Statistics are extremely important for SQL query optimization.

Think of statistics as information that helps the optimizer understand the shape and distribution of the data.

Suppose:

FactSales

500,000,000 rows

The optimizer needs to estimate questions such as:

How many rows exist?

How selective is this filter?

How many rows will this join produce?

Which processing strategy should be used?

Statistics help answer those questions.

14. Why statistics matter

Suppose:

SELECT *
FROM FactSales
WHERE CountryCode = 'IN';

Imagine:

Total rows

100,000,000

India

40,000,000

USA

30,000,000

UK

10,000,000

Others

20,000,000

Statistics help the optimizer estimate that:

CountryCode = 'IN'

≈ 40 million rows

That estimate can influence the execution strategy.

15. Bad statistics can lead to bad plans

Conceptually:

Query
  │
  ▼
Statistics
  │
  ▼
Cardinality Estimate
  │
  ▼
Query Plan
  │
  ▼

Execution

If the estimates are poor:

Poor Statistics

↓

Poor Estimates

↓

Less Efficient Plan

↓

Slow Query

So statistics are a key part of Warehouse performance.

16. Don't think only about query performance

Performance also includes data-loading performance.

Suppose you need to load:

100 million rows

A poor pattern might repeatedly perform tiny operations:

Insert Row

Insert Row

Insert Row

Insert Row

...

For analytical warehouses, set-based and bulk-oriented processing is usually preferable.

Think:

Many tiny operations

✕

Batch / Set-Based Processing

✓

17. Full Load vs Incremental Load

Suppose FactSales contains:

1 billion rows

Every day, only:

2 million

records are new or changed.

A full reload would process:

1,000,000,000 rows

An incremental process might process:

2,000,000 rows

Architecture:

Source
  │
  ▼
Changed Data
  │
  ▼
Stage
  │
  ▼
MERGE / Load
  │
  ▼

Warehouse

For large systems, incremental processing can make a huge difference.

18. Staging tables

For complex loads, consider a staging pattern.

Source
   │
   ▼
STG_SALES
   │
   ▼
Validate
   │
   ▼
Transform
   │
   ▼

FACT_SALES

For example:

STG_CUSTOMER

STG_PRODUCT

STG_ORDER

STG_SALES

Then load:

STG_CUSTOMER

↓

DIM_CUSTOMER

STG_PRODUCT

↓

DIM_PRODUCT

STG_SALES

↓

FACT_SALES

This can make ETL logic easier to control and troubleshoot.

19. Avoid excessive row-by-row processing

SQL performs best when you think in sets.

Avoid patterns conceptually like:

Read Row 1

Update Row 1

Read Row 2

Update Row 2

Read Row 3

Update Row 3

...

Prefer:

UPDATE Sales

SET Status = 'Closed'

WHERE OrderDate < '2026-01-01';

One statement handles the set.

Remember:

SQL is a set-based language.

20. Views and performance

Views can simplify complex SQL.

For example:

CREATE VIEW vw_SalesSummary

AS

SELECT
    d.Year,
    p.Category,
    SUM(f.SalesAmount) AS TotalSales
FROM FactSales f
JOIN DimDate d
    ON f.DateKey = d.DateKey
JOIN DimProduct p
    ON f.ProductKey = p.ProductKey
GROUP BY
    d.Year,
    p.Category;

Then users query:

SELECT *

FROM vw_SalesSummary;

But remember:

A view does not automatically make a slow query fast.

The SQL behind the view still needs good design.

21. Stored procedures and performance

Stored procedures are useful for reusable SQL processing.

For example:

CREATE PROCEDURE LoadSales

AS

BEGIN

INSERT INTO FactSales

SELECT

DateKey,

CustomerKey,

ProductKey,

SalesAmount

FROM StagingSales;

END;

Stored procedures can help centralize logic, but:

Putting inefficient SQL inside a stored procedure doesn't magically make it efficient.

You still need good SQL design.

22. Avoid returning unnecessary rows to Power BI

Suppose your warehouse contains:

500 million detailed records

but the report needs:

Monthly Sales

by

Region and Product Category

Don't automatically send every detailed row to downstream reporting.

Where appropriate, design:

Detailed Fact
      │
      ▼
Business Model
      │
      ▼
Required Analytical Data
      │
      ▼

Power BI

The whole architecture matters—not only one SQL statement.

23. Concurrency

Performance isn't only about one query.

Imagine:

09:00 AM

User 1 → Query

User 2 → Query

User 3 → Query

Power BI → Query

ETL → Loading

Pipeline → Running

Dataflow → Running

All of these workloads can consume resources.

                  Fabric Capacity
                         │
       ┌─────────────────┼─────────────────┐
       │                 │                 │
       ▼                 ▼                 ▼
   Warehouse         Power BI          Lakehouse
       │
   ┌───┼────┐
   ▼   ▼    ▼

Query Query ETL

This leads to the next critical topic: capacity.

24. Fabric Capacity and performance

Fabric workloads run against Fabric capacity.

For example:

                 Fabric Capacity
                       │
          ┌────────────┼────────────┐
          │            │            │
          ▼            ▼            ▼
      Warehouse     Lakehouse     Power BI
          │
          ▼

Queries

If many resource-intensive workloads execute simultaneously, they can compete for available capacity.

Therefore, performance tuning isn't always:

Slow Query

=

Bad SQL

It could also involve:

Slow Performance
       │
       ├── Poor SQL
       ├── Poor Data Model
       ├── Huge Data Volume
       ├── Poor Statistics
       ├── Too Much Concurrency
       └── Capacity Pressure

25. Example

Suppose a query normally takes:

10 seconds

But every morning at 9 AM it takes:

60 seconds

Why?

At 9 AM:

                   Fabric Capacity
                          │
        ┌─────────────────┼────────────────┐
        │                 │                │
        ▼                 ▼                ▼
     ETL Load        Power BI Refresh   User Queries
        │                 │                │
        └─────────────────┼────────────────┘
                          ▼

Resource Pressure

The SQL may not have changed at all.

This is why monitoring capacity and workload concurrency matters.

26. Performance troubleshooting methodology

When someone says:

"Fabric Warehouse is slow."

Don't immediately start changing SQL.

Use a systematic approach:

1. Identify the slow query

↓

2. Compare normal vs slow duration

↓

3. Check rows/data volume

↓

4. Review query logic

↓

5. Review filters and joins

↓

6. Review data types

↓

7. Review statistics/plan information

↓

8. Check concurrent workloads

↓

9. Check Fabric capacity

↓

10. Optimize and retest

This is closer to real production troubleshooting.

27. Example optimization

Suppose someone writes:

SELECT *
FROM FactSales f
JOIN DimCustomer c
    ON f.CustomerKey = c.CustomerKey
JOIN DimProduct p
    ON f.ProductKey = p.ProductKey
WHERE YEAR(f.OrderDate) = 2026;

Potential problems:

SELECT *

Unnecessary columns

Possibly unnecessary joins

Function on filtering column

Large amount of data

A more focused query could be:

SELECT
    c.Country,
    p.Category,
    SUM(f.SalesAmount) AS TotalSales
FROM FactSales f
JOIN DimCustomer c
    ON f.CustomerKey = c.CustomerKey
JOIN DimProduct p
    ON f.ProductKey = p.ProductKey
WHERE f.OrderDate >= '2026-01-01'

AND f.OrderDate < '2027-01-01'

GROUP BY

c.Country,

p.Category;

Now the query requests only the data needed for the business question.

28. Before vs After

Before

SELECT *
FROM FactSales
WHERE YEAR(OrderDate) = 2026;

Conceptually:

Huge Table

↓

Process

↓

Apply Function

↓

Return Many Columns

Better

SELECT
    CustomerKey,
    ProductKey,
    SalesAmount
FROM FactSales
WHERE OrderDate >= '2026-01-01'

AND OrderDate < '2027-01-01';

Conceptually:

Huge Table

↓

Efficient Filter

↓

Required Columns

↓

Smaller Result

29. Performance checklist

When reviewing a slow Fabric Warehouse query, ask:

DATA MODEL
──────────

Is it modeled appropriately?

Do we have a clear fact/dimension design?

QUERY
─────

Am I using SELECT *?

Am I reading unnecessary rows?

Are filters applied appropriately?

Are all joins necessary?

DATA TYPES
──────────

Are join columns compatible?

Are strings unnecessarily large?

STATISTICS
──────────

Does the optimizer have useful statistics?

LOAD
────

Are we doing full loads unnecessarily?

Can we use incremental processing?

Are operations set-based?

WORKLOAD
────────

Are many queries running simultaneously?

Is Power BI refreshing?

Is ETL running?

CAPACITY
────────

Is the Fabric capacity under pressure?

30. What should you NOT focus on first?

If you're learning Fabric Warehouse after working with traditional SQL systems, don't start by assuming every performance problem requires:

Create Index

↓

Create More Indexes

↓

Change Server Memory

↓

Change Disk

↓

Change SQL Server Configuration

Fabric is a SaaS analytical platform.

Microsoft manages much of the underlying infrastructure.

Your main responsibility becomes:

YOUR RESPONSIBILITY

                      Data Model
                          │
                          ▼
                       SQL
                          │
                          ▼
                    Data Loading
                          │
                          ▼
                    Statistics
                          │
                          ▼
                Workload Management
                          │
                          ▼

Capacity Monitoring

31. The 7 rules I would memorize

For your Fabric syllabus, remember these seven points:

Design a good Star Schema — Fact tables for measures and keys; dimensions for descriptive attributes.

Read only required data — Avoid unnecessary SELECT *, columns, rows, and joins.

Filter efficiently — Reduce data as early as practical and avoid unnecessary functions on filter columns.

Use correct data types — Especially for join keys and frequently filtered columns.

Maintain useful statistics — The optimizer needs good information to create efficient plans.

Load efficiently — Prefer batch/set-based and incremental processing over unnecessary full reloads or row-by-row operations.

Monitor capacity and concurrency — A good query can still slow down when many Fabric workloads compete for capacity.

32. Interview answer

What is performance optimization in Fabric Warehouse?

Performance optimization in Microsoft Fabric Warehouse is the process of improving query and data-processing efficiency through good dimensional modeling, efficient T-SQL, appropriate filtering and joins, correct data types, useful statistics, efficient data-loading patterns, and monitoring workload concurrency and Fabric capacity consumption. Because Fabric Warehouse is a SaaS platform, Microsoft manages much of the underlying infrastructure, allowing developers to focus primarily on data modeling, SQL optimization, workload design, and capacity utilization.

33. Easy way to remember

               FABRIC WAREHOUSE
                      │
                      ▼
                 PERFORMANCE
                      │
      ┌───────────────┼───────────────┐
      │               │               │
      ▼               ▼               ▼
 DATA MODEL          SQL            LOAD
      │               │               │
 Star Schema      Filters/Joins    Incremental
      │               │               │
      └───────────────┼───────────────┘
                      ▼
                 STATISTICS
                      │
                      ▼
               QUERY OPTIMIZER
                      │
                      ▼
                  CAPACITY
                      │
                      ▼

FAST ANALYTICS

Most important takeaway

Don't think of Fabric Warehouse performance as only "SQL query tuning."

Think of it as:

Performance = Data Model + Efficient SQL + Statistics + Efficient Loading + Capacity + Concurrency

For your 6.5 Performance syllabus, those are the concepts I would understand first before moving into deeper topics such as query plans, statistics management, capacity monitoring, caching, and detailed Warehouse performance troubleshooting.

↑ Back to top

↑ Back to top

6.6 Security in Microsoft Fabric Data Warehouse

Security in Microsoft Fabric Data Warehouse controls:

Who can access the Warehouse, what objects they can access, and what data they are allowed to see or modify.

The easiest way to understand Fabric Warehouse security is in layers:

                    USER
                      │
                      ▼
              Microsoft Entra ID
                      │
                      ▼
               FABRIC SECURITY
                      │
        ┌─────────────┼─────────────┐
        ▼             ▼             ▼
    Workspace      Item         SQL Security
      Roles      Permissions         │
                                   ┌─┼───────────┐
                                   ▼ ▼           ▼

GRANT/DENY RLS / CLS

For your syllabus, focus on these concepts:

1. Authentication

2. Workspace Roles

3. Item Permissions

4. SQL Permissions

5. Object-Level Access

6. Row-Level Security

7. Column-Level Security

8. Dynamic Data Masking

9. Least Privilege

1. Authentication vs Authorization

Before learning Fabric security, understand these two terms.

Authentication

Who are you?

For example:

User
 │
 ▼
Sign in
 │
 ▼
Microsoft Entra ID
 │
 ▼

Identity Verified

Microsoft Fabric uses Microsoft Entra ID identities for authentication.

Authorization

What are you allowed to do?

After authentication:

Sreehari
    │
    ▼
Authenticated
    │
    ▼

What permissions?

    │
    ├── Read Warehouse?
    ├── Create Table?
    ├── Execute Procedure?
    ├── Update Data?
    └── Administer Workspace?

Easy memory:

Authentication = Who are you? Authorization = What can you do?

2. Fabric security layers

Security doesn't exist at only one level.

Think of:

Microsoft Fabric
      │
      ▼
Workspace
      │
      ▼
Warehouse
      │
      ▼
Schema
      │
      ▼
Table / View / Procedure
      │
      ▼

Rows / Columns

Different controls can be applied at different layers.

3. Workspace Security

Fabric content is organized into Workspaces.

For example:

Sales Workspace
│
├── Sales_Lakehouse
├── Sales_Warehouse
├── Sales_Pipeline
└── Sales_Report

Users can be assigned workspace roles.

The main workspace roles are:

Admin

Member

Contributor

Viewer

4. Workspace roles

At a high level:

The exact permissions associated with each role should always be checked against current Fabric documentation, especially when designing production security.

The important architectural point is:

Workspace roles provide broad Fabric-level access.

5. Example workspace security

Suppose we have:

Finance Workspace
       │
       ├── Finance_Warehouse
       ├── Finance_Pipeline
       └── Finance_Report

We might have:

Fabric Admin
     │
     ▼

ADMIN

Data Engineer
     │
     ▼

MEMBER / CONTRIBUTOR

Business User
     │
     ▼

VIEWER

But workspace roles alone may be too broad for every requirement.

That's where more granular security becomes important.

6. Item-Level Permissions

You may want to give someone access to a particular Warehouse without giving them broad workspace permissions.

Conceptually:

Workspace
│
├── Warehouse A  ← User has access
│
├── Warehouse B  ← No access
│
├── Lakehouse
└── Pipeline

This is where item-level permissions/sharing become useful.

Think:

Workspace Role

=

Broad Workspace Access

Item Permission

=

Specific Fabric Item Access

7. SQL security

Inside Fabric Warehouse, SQL permissions provide more granular control.

Suppose:

Sales_Warehouse
│
├── Tables
│   ├── FactSales
│   ├── DimCustomer
│   └── EmployeeSalary
│
├── Views
│   └── vw_SalesSummary
│
└── Stored Procedures
    └── usp_LoadSales

Different users can require different permissions.

For example:

Data Engineer
    │
    ├── SELECT
    ├── INSERT
    ├── UPDATE
    └── DELETE

Analyst
    │
    └── SELECT

ETL Service
    │
    ├── SELECT
    ├── INSERT
    └── EXECUTE

8. GRANT

GRANT gives a user or principal permission.

For example:

GRANT SELECT ON dbo.FactSales TO [user@company.com];

Conceptually:

User
 │
 │ GRANT SELECT
 ▼
FactSales
 │
 ▼

Can Read

9. DENY

DENY explicitly prevents an operation where supported/applicable.

Conceptually:

User
 │
 │ DENY
 ▼
Sensitive Object
 │

✕

No Access

When designing SQL permissions, be careful with combinations of grants, denies, roles, and inherited permissions.

10. REVOKE

REVOKE removes a previously granted or denied permission entry.

For example:

REVOKE SELECT ON dbo.FactSales

FROM [user@company.com];

Don't confuse:

GRANT

=

Give Permission

DENY

=

Explicitly Refuse Permission

REVOKE

=

Remove GRANT/DENY

11. Table-Level Security

Suppose your Warehouse contains:

dbo.FactSales

dbo.DimCustomer

hr.EmployeeSalary

finance.FinancialTransactions

A Sales Analyst may need:

FactSales ✓

DimCustomer ✓

EmployeeSalary ✕

FinancialData ✕

You can use SQL permissions to control access to specific objects.

Example:

GRANT SELECT ON dbo.FactSales

TO [SalesAnalyst];

12. Schema-Level Security

For larger warehouses, granting permissions table by table can become difficult.

Suppose:

Warehouse
│
├── sales
│   ├── FactSales
│   ├── DimCustomer
│   └── DimProduct
│
├── finance
│   ├── FactGL
│   └── DimAccount
│
└── hr
    ├── Employee
    └── Salary

You can organize objects into schemas:

sales

finance

hr

Then security can be designed around schemas.

Conceptually:

Sales Team
    │
    ▼
sales schema
    │
    ├── FactSales
    ├── DimCustomer
    └── DimProduct

This is usually much easier to manage than hundreds of individual table permissions.

13. Row-Level Security — RLS

Row-Level Security controls which rows a user can see.

This is extremely important.

Suppose FactSales contains:

We have:

India Manager

USA Manager

Without RLS:

India Manager

India ✓

USA ✓

UK ✓

Germany ✓

But we want:

India Manager

India ✓

USA ✕

UK ✕

Germany ✕

That's Row-Level Security.

14. How RLS works conceptually

                FactSales
                    │
       ┌────────────┼────────────┐
       │            │            │
     India         USA          UK
       │            │            │
       ▼            ▼            ▼

India Manager USA Manager UK Manager

Same table.

Different users.

Different rows.

That's the key concept.

15. RLS example

Imagine:

Employee          Region
-------------------------

Ravi India

John USA

David UK

When Ravi queries:

SELECT *

FROM FactSales;

RLS can make the effective result:

Region | Sales

-------|-------

India | 100000

John might execute the same SQL:

SELECT *

FROM FactSales;

but receive:

Region | Sales

-------|-------

USA | 200000

Therefore:

RLS filters rows according to the user's security context.

16. Column-Level Security — CLS

Column-Level Security controls access to specific columns.

Suppose:

Employee
│
├── EmployeeID
├── EmployeeName
├── Department
├── Email
├── Salary
└── BankAccount

An analyst might be allowed to see:

EmployeeID ✓

EmployeeName ✓

Department ✓

Salary ✕

BankAccount ✕

This is a column-level security requirement.

17. RLS vs CLS

This distinction is important for interviews.

Row-Level Security

Controls:

Which records can I see?

India Records ✓

USA Records ✕

UK Records ✕

Column-Level Security

Controls:

Which fields can I see?

EmployeeName ✓

Department ✓

Salary ✕

BankAccount ✕

Easy memory:

RLS

=

ROWS

CLS

=

COLUMNS

18. RLS + CLS together

They can be combined.

Suppose:

Employee Table

Name | Region | Department | Salary

An India manager might have:

Rows
────

India only

Columns
───────

Name ✓

Region ✓

Department ✓

Salary ✕

So:

                Security
                   │
          ┌────────┴────────┐
          ▼                 ▼
         RLS               CLS
          │                 │

Which Rows? Which Columns?

19. Dynamic Data Masking

Another security concept is Dynamic Data Masking (DDM).

Instead of completely denying access to a column, you can mask sensitive values for users who should not see the original data.

Suppose:

Customer

Name

Email

Phone

CreditCard

Actual:

Ravi

ravi@example.com

9876543210

1234-5678-9012-3456

A masked view might appear conceptually as:

Ravi

rXXX@example.com

XXXXXX3210

XXXX-XXXX-XXXX-3456

The underlying data isn't necessarily changed—the displayed value is masked according to permissions and masking rules.

20. Masking vs Encryption

Don't confuse these concepts.

Masking

Actual Data

9876543210

Displayed

XXXXXX3210

Used to reduce exposure of sensitive values.

Encryption

Plain Data
     │
     ▼
Encryption
     │
     ▼

Protected Representation

Used to protect data cryptographically.

They solve different security problems.

21. Encryption

Fabric provides platform-level protection for data, including encryption mechanisms managed as part of the Microsoft cloud/Fabric platform.

Think about two major states:

DATA AT REST
     │
     ▼
Stored Data
     │
     ▼

Encryption

DATA IN TRANSIT
     │
     ▼
Network Communication
     │
     ▼

Encrypted Transport

For your beginner syllabus, understand:

At-rest encryption protects stored data.

In-transit encryption protects data moving across network connections.

22. Views as a security layer

Views can also help expose only required data.

Suppose:

Employee

EmployeeID

Name

Department

Salary

BankAccount

Instead of allowing analysts direct table access, create:

CREATE VIEW reporting.vw_Employee

AS

SELECT

EmployeeID,

Name,

Department

FROM hr.Employee;

Then users see:

EmployeeID

Name

Department

but not:

Salary

BankAccount

Architecture:

Sensitive Table
      │
      ▼
    View
      │
      ▼
Safe Columns
      │
      ▼

Analyst

This is a common and understandable security pattern.

23. Stored procedures and security

Stored procedures can expose controlled operations.

Suppose users shouldn't directly update FactSales.

Instead of:

User
 │
 ▼

UPDATE FactSales

you might design:

User
 │
 ▼
Stored Procedure
 │
 ▼
Validated Logic
 │
 ▼

FactSales

For example, authorized users/services can receive EXECUTE permission on an approved procedure rather than broad modification permissions.

The exact security behavior depends on procedure design, permissions, and supported Fabric Warehouse T-SQL features.

24. Least Privilege Principle

This is one of the most important security principles.

Give users only the permissions they need to perform their job—nothing more.

Bad:

Business Analyst

ADMIN

if they only need reports.

Better:

Business Analyst

Read / SELECT

only required data

Another example:

ETL Account

Needs:

SELECT

INSERT

Doesn't need:

Workspace Admin

Don't grant Admin simply because it's easier.

25. Security using groups

In an enterprise environment, avoid managing hundreds of users individually where possible.

Instead of:

Ravi → SELECT

John → SELECT

Priya → SELECT

David → SELECT

...

prefer group-oriented administration:

          Entra Group
               │
               ▼
        Sales_Analysts
               │
       ┌───────┼───────┐
       ▼       ▼       ▼
     Ravi    John    Priya
               │
               ▼

Permissions

This makes onboarding and offboarding much easier.

26. Example enterprise security design

Imagine:

Sales Warehouse
│
├── sales.FactSales
├── sales.DimCustomer
├── sales.DimProduct
│
├── finance.FactRevenue
│
└── hr.EmployeeSalary

Groups:

Sales_Analysts

Finance_Analysts

HR_Analysts

Data_Engineers

Permissions:

This is much better than:

Everyone
    │
    ▼

Workspace Admin

27. Power BI and Warehouse security

Security must also be considered end-to-end.

Fabric Warehouse
       │
       ▼
Semantic Model
       │
       ▼
Power BI Report
       │
       ▼

Business User

You may have security at:

Warehouse
    +
Semantic Model
    +

Power BI

A common mistake is securing only the report while leaving direct data access overly broad.

Always think:

Can the user bypass the report and query the underlying Warehouse directly?

Security should be designed across the complete data path.

28. Example: Sales security architecture

                       USERS
                         │
                         ▼
                 Microsoft Entra ID
                         │
                         ▼
                 Entra Security Groups
                         │
                         ▼
                 Fabric Workspace
                         │
                         ▼
                     Warehouse
                         │
          ┌──────────────┼──────────────┐
          ▼              ▼              ▼
       Schema          Tables          Views
          │              │              │
          └──────────────┼──────────────┘
                         ▼
                   SQL Permissions
                         │
          ┌──────────────┼──────────────┐
          ▼              ▼              ▼
         RLS            CLS           Masking
          │              │              │
       Rows          Columns       Sensitive Data
                         │
                         ▼

Power BI

29. Real-world scenario

Suppose your company operates in:

India

USA

UK

Germany

The Warehouse contains:

FactSales

with:

Region

SalesAmount

Profit

CustomerID

Security requirement:

India Manager
      │
      ▼

India data only

USA Manager
      │
      ▼

USA data only

Global Director
      │
      ▼

All regions

This is a classic RLS use case.

Then suppose DimCustomer contains:

CustomerName

Email

Phone

CreditCard

Regular analysts should not see CreditCard.

That's a column/sensitive-data protection requirement.

Together:

                 FactSales
                     │
                     ▼
                    RLS
                     │

Region Filtering

                DimCustomer
                     │
                     ▼
               Column Security
                     │

Sensitive Fields

30. DEV, TEST and PROD security

Another important enterprise practice is separating environments.

DEV
 │
 ▼

Developers

Broad development access

TEST
 │
 ▼

Developers / Testers

Controlled access

PROD
 │
 ▼

Restricted access

Avoid:

Everyone
   │
   ▼

PROD Admin

Production should generally have stricter access controls than development.

31. Security best practices

For your Fabric Warehouse, remember these:

Use Microsoft Entra ID identities and groups for centralized identity management.

Follow least privilege — don't give Admin/Member permissions when read access is enough.

Use workspace roles for broad workspace access and more granular controls for specific requirements.

Use SQL permissions such as GRANT, DENY, and REVOKE appropriately.

Use schemas to organize objects and simplify permission management.

Use RLS when users should see different rows from the same table.

Protect sensitive columns using appropriate column permissions, views, masking, or downstream security controls.

Separate DEV, TEST, and PROD access and regularly review permissions.

32. Interview question — How is Fabric Warehouse secured?

A strong answer:

Microsoft Fabric Warehouse security is implemented in multiple layers. Microsoft Entra ID provides authentication, Fabric workspace roles and item permissions control access at the Fabric level, and SQL permissions provide granular access to warehouse objects. Additional controls such as row-level security, column-level security, views, and dynamic data masking can protect sensitive data. A good design follows least privilege and uses Entra groups rather than assigning broad permissions individually.

33. Interview question — RLS vs CLS

Row-Level Security controls which rows a user can access, while Column-Level Security controls which columns the user can access. For example, RLS can restrict a regional manager to India sales records, while CLS can prevent that manager from seeing sensitive columns such as salary or bank-account information.

34. Interview question — Workspace role vs SQL permission

A workspace role controls broad access and capabilities within a Fabric workspace, while SQL permissions provide more granular authorization within a Warehouse, such as allowing a user to SELECT from a particular schema or table or EXECUTE a stored procedure.

Think:

Workspace Role
      │
      ▼

"What can you do in Fabric workspace?"

SQL Permission
      │
      ▼

"What can you do inside the Warehouse?"

35. Quick revision

FABRIC WAREHOUSE SECURITY
================================
Authentication
     │
     ▼
Microsoft Entra ID
     │
     ▼
Workspace Security
     │
     ├── Admin
     ├── Member
     ├── Contributor
     └── Viewer
     │
     ▼
Item Security
     │
     ▼
Warehouse
     │
     ▼
SQL Security
     │
     ├── GRANT
     ├── DENY
     └── REVOKE
     │
     ▼
Object Security
     │
     ├── Schema
     ├── Table
     ├── View
     └── Procedure
     │
     ▼
Data Security
     │
     ├── RLS → Rows
     ├── CLS → Columns
     └── DDM → Mask values

One diagram to remember

                         USER
                           │
                           ▼
                  MICROSOFT ENTRA ID
                           │
                    Authentication
                           │
                           ▼
                  FABRIC WORKSPACE
                           │
                 Workspace Roles
                           │
                           ▼
                      WAREHOUSE
                           │
                  SQL Permissions
                           │
          ┌────────────────┼────────────────┐
          │                │                │
          ▼                ▼                ▼
        SCHEMA           TABLE            VIEW
          │                │                │
          └────────────────┼────────────────┘
                           ▼
                      DATA SECURITY
                           │
             ┌─────────────┼─────────────┐
             ▼             ▼             ▼
            RLS           CLS          MASKING
             │             │             │

ROWS COLUMNS VALUES

Key takeaway

For 6.6 Security, remember this sequence:

Entra ID → Workspace Roles → Item Permissions → SQL Permissions → RLS/CLS/Masking

And remember these three questions:

Authentication: Who are you? Authorization: What can you do? Data Security: What data are you allowed to see?

That is the foundation for understanding Microsoft Fabric Data Warehouse security.

↑ Back to top

↑ Back to top

↑ Back to top

↑ Back to top