Microsoft Fabric

Create Your First Fabric Warehouse with T-SQL

A hands-on Microsoft Fabric Warehouse tutorial: create a Warehouse, build a schema and tables with T-SQL, insert, join, aggregate, update and delete rows, create a view, use transactions, query a Lakehouse and grant access.

Par JaviPublié Mis à jour 18 min de lectureDébutant

So far in this path you have written data with Spark: a Lakehouse, a notebook, PySpark and Delta tables. This lesson switches engines. You build a Fabric Warehouse and do everything in T-SQL, the way a SQL developer would expect to work.

If you know SELECT, INSERT and JOIN, you already know most of the language. What is new is the platform around it: where a Warehouse fits in Fabric, what it does differently from SQL Server, and why it is not the same thing as the SQL analytics endpoint you saw on your Lakehouse.

The conceptual comparison lives in Microsoft Fabric Lakehouse vs Warehouse. This article is the hands-on part. If you come from SQL Server and want to know first what replaces the instance, the database, SQL Agent and Query Store, read SQL Server to Microsoft Fabric: What Maps to What?.

What you will build

By the end of this lesson you will have:

  • a Warehouse called wh_learning in the jb-fabric-dev workspace;
  • a sales schema;
  • three tables: sales.Customer, sales.Product and sales.SalesOrder;
  • a handful of sample rows: 3 customers, 4 products, 6 orders;
  • a JOIN query with a calculated line total;
  • revenue by customer with SUM and GROUP BY;
  • one UPDATE and one safe, validated DELETE;
  • a view, sales.vwCustomerSales, ready for analysts and Power BI;
  • a small multi-table transaction;
  • a cross-database query that reads the Lakehouse table from earlier in the path;
  • a GRANT that gives one person read access to one schema.

The dataset is deliberately tiny. The point is to see every operation and its result, not to test performance. The workspace and Lakehouse names match the earlier steps, but you can use your own.

When should you use a Fabric Warehouse?

A Fabric Warehouse is a relational data warehouse developed primarily with T-SQL. You create schemas, tables, views, functions and stored procedures, and you change data with INSERT, UPDATE, DELETE and MERGE, inside multi-table ACID transactions when you need them.

Choose a Warehouse when:

  • SQL is the team’s main language. The people building and maintaining the tables write T-SQL, not Python.
  • The data is structured. It arrives as tables or clean files, not as raw JSON or images.
  • Relational modelling matters. You are building facts, dimensions, star schemas or data marts.
  • Analysts and BI users need SQL access to governed tables and views.
  • Changes must be transactional. Several tables have to change together, or not at all.

A Lakehouse is the better fit when Spark does the work: raw and semi-structured files, heavy Python transformations, machine learning, or engineers who want notebooks and control over file layout.

Need Warehouse Lakehouse
Write and transform data with T-SQL Yes, natively No. Writes go through Spark
Write and transform data with PySpark Through the Spark connector only Yes, natively
Raw files (CSV, JSON, images) in their original form No Yes, in the Files area
Multi-table transactions Yes No. Each Delta table has its own transactions
Star schemas and data marts built in SQL A natural fit Possible, built with Spark
Read-only T-SQL queries Yes Yes, through the SQL analytics endpoint
Table maintenance (compaction, cleanup) Managed by the service Your responsibility (OPTIMIZE, VACUUM)

Most real platforms use both. A common split is Lakehouse for landing and engineering, Warehouse for the relational serving layer. You do not have to choose one for the whole platform; choose per dataset.

Create your first Warehouse

You need a workspace on a Fabric capacity (trial or paid) and the Contributor role or higher. The workspace lesson covers both.

  1. Open the jb-fabric-dev workspace.
  2. Select + New item.
  3. Under Store data, select Warehouse. Do not pick Sample warehouse for this lesson; it loads a ready-made dataset you will not use here.
  4. Enter the name wh_learning and select Create.

Fabric creates an empty Warehouse and opens its editor. There is no server to size and no database file to place. The Warehouse runs on your workspace’s capacity, and its data is stored in OneLake, in the region of that capacity.

Two parts of the editor matter for this lesson:

  • Explorer, on the left, lists the Warehouse’s schemas and, inside them, tables, views, functions and stored procedures. New objects can take a moment to appear; refresh the Explorer if one is missing.
  • New SQL query, in the ribbon, opens a query tab. Write T-SQL there and select Run. Results appear in the Results pane below, and SQL messages in the Messages tab.

You can also connect with SQL Server Management Studio or Visual Studio Code with the MSSQL extension, using the SQL connection string from the Warehouse settings. Everything in this lesson works from the browser editor, so start there.

One detail to know from day one: each time you select Run, the editor sends the selected text as an independent batch in a new session. A GO separator splits a script into separate batches, and each of those batches also runs in its own new session. Nothing carries over between them except the objects you created. This matters for transactions, later in the lesson.

Create a schema

Open a New SQL query tab and run:

Requête SQL: Create a schemaT-SQL (Fabric Warehouse)
CREATE SCHEMA sales;
T-SQL (Fabric Warehouse)1 ligneAffichage en lecture seule. Aucune connexion à une base de données.

Every Warehouse has a dbo schema, and you could put everything there. Do not. Schemas pay for themselves quickly:

  • Organization. sales.Customer, finance.Invoice and staging.CustomerLoad tell you what an object is for before you open it.
  • Security. You can grant access to a whole schema in one statement instead of table by table. You will do that at the end of this lesson.
  • Naming. Two teams can each own a Customer table without colliding, as long as they live in different schemas.

Schema names cannot contain / or \ or end with a period. Short, lowercase names such as sales, staging and reporting work well.

Create the tables

Run the three CREATE TABLE statements together:

Requête SQL: Create the tablesT-SQL (Fabric Warehouse)
CREATE TABLE sales.Customer
(
    CustomerId    int          NOT NULL,
    CustomerName  varchar(100) NOT NULL,
    Country       varchar(50)  NOT NULL
);

CREATE TABLE sales.Product
(
    ProductId    int           NOT NULL,
    ProductName  varchar(100)  NOT NULL,
    UnitPrice    decimal(10,2) NOT NULL
);

CREATE TABLE sales.SalesOrder
(
    SalesOrderId  int  NOT NULL,
    CustomerId    int  NOT NULL,
    ProductId     int  NOT NULL,
    Quantity      int  NOT NULL,
    OrderDate     date NOT NULL
);
T-SQL (Fabric Warehouse)22 lignesAffichage en lecture seule. Aucune connexion à une base de données.

This looks like SQL Server, and it is close. A few choices are deliberate, because the Warehouse supports a subset of SQL Server’s data types for stored tables:

  • varchar, not nvarchar. nchar and nvarchar are not supported in Warehouse tables. Warehouse text uses UTF-8 collations, so varchar stores Unicode text.
  • decimal, not money. money and smallmoney are not supported. Use decimal with an explicit precision and scale.
  • date and datetime2, not datetime. datetime and smalldatetime are not supported. datetime2 and time are limited to 6 digits of fractional seconds.
  • No tinyint. Use smallint instead.

There are no primary or foreign keys here. The Warehouse lets you declare them only as NOT ENFORCED: they help the optimizer and modelling tools, but the engine does not reject duplicates or orphan rows. For a first lesson, leaving them out keeps that clear. Uniqueness is your load process’s job, the same lesson as for Delta tables in a Lakehouse.

You might also expect an IDENTITY column for the IDs. The Warehouse supports IDENTITY on bigint columns, but values are unique, not sequential, and you cannot set a seed or increment. Explicit IDs keep this lesson’s results predictable.

After the statements run, refresh the Explorer. The sales schema now holds three tables.

Insert sample rows

Insert the rows with multi-row VALUES lists:

Requête SQL: Insert sample rowsT-SQL (Fabric Warehouse)
INSERT INTO sales.Customer (CustomerId, CustomerName, Country)
VALUES
    (1, 'Lumen Cycles',      'Spain'),
    (2, 'Alpine Outfitters', 'France'),
    (3, 'Harbor Supply Co',  'Ireland');

INSERT INTO sales.Product (ProductId, ProductName, UnitPrice)
VALUES
    (1, 'Road Helmet',    79.00),
    (2, 'Trail Gloves',   24.50),
    (3, 'LED Bike Light', 39.90),
    (4, 'Water Bottle',    9.50);

INSERT INTO sales.SalesOrder (SalesOrderId, CustomerId, ProductId, Quantity, OrderDate)
VALUES
    (101, 1, 1,  2, '2026-09-01'),
    (102, 1, 3,  4, '2026-09-03'),
    (103, 2, 2, 10, '2026-09-05'),
    (104, 2, 4, 12, '2026-09-08'),
    (105, 3, 1,  1, '2026-09-10'),
    (106, 3, 3,  3, '2026-09-12');
T-SQL (Fabric Warehouse)21 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Always list the columns in an INSERT. It documents what each value means and keeps the statement working if someone adds a column later.

This is fine for a dozen rows. It is not how you load real data. Microsoft’s guidance is to avoid ingesting data with many small, singleton INSERT statements, because they hurt query and update performance. Real loads use COPY INTO, INSERT ... SELECT, CREATE TABLE AS SELECT or pipelines. More on that in Production considerations.

Query the Warehouse

Start with the simplest possible check:

Requête SQL: Query the WarehouseT-SQL (Fabric Warehouse)
SELECT *
FROM sales.SalesOrder;
T-SQL (Fabric Warehouse)2 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Six rows. SELECT * is fine for a quick look at a table you just loaded. In saved queries and views, name the columns you need.

Now make the data useful. An order row holds IDs; people want names and amounts. Join the three tables and calculate a line total:

Requête SQL: Order lines with namesT-SQL (Fabric Warehouse)
SELECT
    so.SalesOrderId,
    so.OrderDate,
    c.CustomerName,
    p.ProductName,
    so.Quantity,
    p.UnitPrice,
    so.Quantity * p.UnitPrice AS LineTotal
FROM sales.SalesOrder AS so
INNER JOIN sales.Customer AS c
    ON c.CustomerId = so.CustomerId
INNER JOIN sales.Product AS p
    ON p.ProductId = so.ProductId
ORDER BY so.SalesOrderId;
RésultatsRésultat vérifié: the CREATE TABLE and INSERT statements of this lesson, run unchanged with DuckDB (not in Fabric)
#SalesOrderIdOrderDateCustomerNameProductNameQuantityUnitPriceLineTotal
11012026-09-01Lumen CyclesRoad Helmet279.00158.00
21022026-09-03Lumen CyclesLED Bike Light439.90159.60
31032026-09-05Alpine OutfittersTrail Gloves1024.50245.00
41042026-09-08Alpine OutfittersWater Bottle129.50114.00
51052026-09-10Harbor Supply CoRoad Helmet179.0079.00
61062026-09-12Harbor Supply CoLED Bike Light339.90119.70
T-SQL (Fabric Warehouse)14 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Notes

  • ligne 8LineTotal is calculated from the product's current price, not stored.

A few things to notice:

  • Aliases (so, c, p) keep the query readable and make it obvious which table each column comes from. Always prefix columns with an alias once a query joins more than one table.
  • Relationships live in the ON clauses. SalesOrder.CustomerId points to Customer, and SalesOrder.ProductId points to Product. Nothing enforces these relationships, so the query is also your first data-quality check: an order with an unknown CustomerId would silently disappear from an INNER JOIN.
  • LineTotal is calculated, not stored. It uses the product’s current price. That is fine for a lesson. A real sales fact table stores the price at the time of sale, because prices change and history should not.

Aggregate sales

Revenue by customer is the first question most people ask of a sales table:

Requête SQL: Revenue by customerT-SQL (Fabric Warehouse)
SELECT
    c.CustomerName,
    c.Country,
    COUNT(*)                       AS OrderCount,
    SUM(so.Quantity * p.UnitPrice) AS Revenue
FROM sales.SalesOrder AS so
INNER JOIN sales.Customer AS c
    ON c.CustomerId = so.CustomerId
INNER JOIN sales.Product AS p
    ON p.ProductId = so.ProductId
GROUP BY c.CustomerName, c.Country
ORDER BY Revenue DESC;
RésultatsRésultat vérifié: the CREATE TABLE and INSERT statements of this lesson, run unchanged with DuckDB (not in Fabric)
#CustomerNameCountryOrderCountRevenue
1Alpine OutfittersFrance2359.00
2Lumen CyclesSpain2317.60
3Harbor Supply CoIreland2198.70
T-SQL (Fabric Warehouse)12 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Every non-aggregated column in the SELECT list appears in the GROUP BY. ORDER BY Revenue DESC can use the alias because ORDER BY is evaluated after the SELECT list.

This is the shape of most warehouse queries: a fact table (orders) joined to dimension tables (customers, products), grouped by dimension attributes and summed over measures. The same shape scales from six rows to six billion; what changes is how you load the data and how you design the tables.

One Fabric-specific detail: new Warehouses use the case-sensitive collation Latin1_General_100_BIN2_UTF8 by default. WHERE c.Country = 'spain' returns nothing, because the stored value is 'Spain'. The collation is set when the Warehouse is created, from the workspace default, and cannot be changed afterwards. Keep identifier and value casing consistent and you will rarely notice it.

Update data

The customer data has a mistake: Alpine Outfitters is based in Switzerland, not France. Check the row first, then fix it:

Requête SQL: Update dataT-SQL (Fabric Warehouse)
SELECT CustomerId, CustomerName, Country
FROM sales.Customer
WHERE CustomerId = 2;

UPDATE sales.Customer
SET Country = 'Switzerland'
WHERE CustomerId = 2;
T-SQL (Fabric Warehouse)7 lignesAffichage en lecture seule. Aucune connexion à une base de données.

The SELECT returns exactly one row, so the UPDATE with the same WHERE clause changes exactly one row. Run the revenue query again and Alpine Outfitters now appears with Switzerland.

This is the operation that separates the two SQL surfaces you have met in this path. In a Warehouse, UPDATE is ordinary T-SQL. Through a Lakehouse’s SQL analytics endpoint, the same statement fails: the endpoint can read Lakehouse tables, but it cannot change them. To update a Lakehouse table you go back to Spark, as in the Delta tables lesson.

Delete data safely

Order 106 was entered twice in the source system and should not be in the warehouse. Deleting it is one line. Deleting the wrong rows is also one line, so build the habit now:

1. Write the WHERE clause as a SELECT and read the result.

Requête SQL: Delete data safelyT-SQL (Fabric Warehouse)
SELECT SalesOrderId, CustomerId, ProductId, Quantity, OrderDate
FROM sales.SalesOrder
WHERE SalesOrderId = 106;
T-SQL (Fabric Warehouse)3 lignesAffichage en lecture seule. Aucune connexion à une base de données.

One row, the one you expect.

2. Reuse exactly the same WHERE clause in the DELETE.

Requête SQL: Delete data safely (2)T-SQL (Fabric Warehouse)
DELETE FROM sales.SalesOrder
WHERE SalesOrderId = 106;
T-SQL (Fabric Warehouse)2 lignesAffichage en lecture seule. Aucune connexion à une base de données.

3. Check the result.

Requête SQL: Delete data safely (3)T-SQL (Fabric Warehouse)
SELECT COUNT(*) AS OrderCount
FROM sales.SalesOrder;
T-SQL (Fabric Warehouse)2 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Five orders remain, and Harbor Supply Co’s revenue drops to 79.00.

The pattern is simple: the SELECT is your preview, and the DELETE uses the identical predicate. Copy the WHERE clause; do not retype it. Never run a DELETE or UPDATE without a WHERE clause unless you really mean every row. To empty a table on purpose, TRUNCATE TABLE states that intent more clearly.

Create a view

The join from earlier is something analysts will want again and again. Instead of every analyst rewriting it, put it in a view:

Requête SQL: sales.vwCustomerSalesT-SQL (Fabric Warehouse)
CREATE VIEW sales.vwCustomerSales
AS
SELECT
    so.SalesOrderId,
    so.OrderDate,
    c.CustomerId,
    c.CustomerName,
    c.Country,
    p.ProductId,
    p.ProductName,
    so.Quantity,
    p.UnitPrice,
    so.Quantity * p.UnitPrice AS LineTotal
FROM sales.SalesOrder AS so
INNER JOIN sales.Customer AS c
    ON c.CustomerId = so.CustomerId
INNER JOIN sales.Product AS p
    ON p.ProductId = so.ProductId;
T-SQL (Fabric Warehouse)18 lignesAffichage en lecture seule. Aucune connexion à une base de données.

CREATE VIEW must be the only statement in its batch. Run it on its own, or put GO before it in a longer script.

Query it like a table:

Requête SQL: Create a viewT-SQL (Fabric Warehouse)
SELECT CustomerName, SUM(LineTotal) AS Revenue
FROM sales.vwCustomerSales
GROUP BY CustomerName
ORDER BY Revenue DESC;
T-SQL (Fabric Warehouse)4 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Views earn their place in a warehouse because they:

  • Encapsulate joins. The relationship logic lives in one place. When it changes, you change the view, not fifty reports.
  • Give analysts a stable interface. They query vwCustomerSales and do not need to know how the tables are split.
  • Feed Power BI. A semantic model on a few well-named views is easier to build and maintain than one on raw tables. Semantic models in Microsoft Fabric covers that layer.
  • Support security. You can grant access to a reporting schema of views without exposing every base table.

The editor also has Save as view: highlight a SELECT and save it as a view from the results bar. It is handy for exploration. For anything you will keep, write the CREATE VIEW yourself so the definition can live in source control.

Warehouse transactions

The Warehouse supports ACID transactions with the usual BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION. A transaction can span several tables, and if any statement in it fails, the whole transaction is rolled back.

Suppose you add a new product and its first order. Either both rows should exist, or neither:

Requête SQL: Warehouse transactionsT-SQL (Fabric Warehouse)
BEGIN TRANSACTION;

INSERT INTO sales.Product (ProductId, ProductName, UnitPrice)
VALUES (5, 'Bike Lock', 32.00);

INSERT INTO sales.SalesOrder (SalesOrderId, CustomerId, ProductId, Quantity, OrderDate)
VALUES (107, 1, 5, 2, '2026-09-15');

COMMIT TRANSACTION;
T-SQL (Fabric Warehouse)9 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Select the whole block and run it in one Run, as a single batch with no GO inside it. Because each Run, and each GO-separated batch, in the browser editor is a separate session, a BEGIN TRANSACTION in one batch and a COMMIT in another do not belong to the same transaction; the commit fails and the changes are not committed. In SSMS or Visual Studio Code, sessions behave as you are used to.

What to know as a beginner:

  • Snapshot isolation, always. Readers do not block writers and writers do not block readers. SET TRANSACTION ISOLATION LEVEL is not supported; snapshot isolation applies regardless.
  • Conflicts are detected per table. Two transactions that update different rows of the same table can still conflict. The first to commit wins; the other fails with an update-conflict error and should be retried. Avoid concurrent UPDATE, DELETE and MERGE on the same table.
  • Some DDL can run inside a transaction, including CREATE TABLE, DROP TABLE, TRUNCATE TABLE and supported ALTER TABLE operations.
  • Not every SQL Server transaction feature exists. Savepoints, named transactions, marked transactions and distributed transactions are not supported.
  • Keep transactions short. DDL takes schema locks that block other work on the same table.

What Fabric is doing underneath

You have worked entirely in T-SQL, but the storage is not a SQL Server data file:

  • The data lives in OneLake. Each Warehouse table is stored as Parquet files with a Delta Lake transaction log, the same open format your Lakehouse uses.
  • The Warehouse SQL engine owns the writes. Every change goes through T-SQL or a supported ingestion path. Other engines, Spark included, can read the published Delta tables.
  • Compute and storage are managed by Fabric. Queries run on your workspace’s Fabric capacity. Compaction, checkpoints and cleanup of old files run as a background service.
  • There is little physical tuning to do. You do not create indexes, choose distributions, size files or manage partitions the way you would in SQL Server or a dedicated SQL pool. Statistics are created automatically; after a large load, update them so the optimizer sees the new data.

The practical result: you design tables, types and queries; Fabric manages the physical layer. That is a different deal from SQL Server, where indexes and file layout are your job.

Warehouse vs Lakehouse SQL analytics endpoint

This is the distinction beginners trip over most often, so it gets its own section.

Every Lakehouse comes with a SQL analytics endpoint. It runs on the same SQL engine as the Warehouse, opens in a very similar editor and accepts T-SQL. It is still a different thing: a read-only T-SQL surface over the Lakehouse’s Delta tables. The tables in it are created by Spark in the Lakehouse, and the endpoint picks them up automatically.

Capability Fabric Warehouse Lakehouse SQL analytics endpoint
SELECT Yes Yes
INSERT, UPDATE, DELETE, MERGE Yes No. Change the data with Spark in the Lakehouse
CREATE, ALTER, DROP TABLE Yes No. Tables come from Delta tables in the Lakehouse’s Tables area
Views Yes Yes
Stored procedures and functions Yes Yes, over read-only data
Explicit transactions that change data Yes, across tables No. There is nothing to write
GRANT, DENY, row-level and column-level security Yes Yes, for SQL access only
Who writes the data T-SQL, COPY INTO, pipelines, dataflows Spark notebooks and jobs, pipelines and dataflows landing in the Lakehouse
Table maintenance Managed by the service Your job in the Lakehouse (OPTIMIZE, VACUUM)
Primary experience T-SQL warehouse development T-SQL reads over Spark-written tables

The quick test: if you need to change data with T-SQL, you need a Warehouse. If you only need to read Lakehouse data with T-SQL, the endpoint already gives you that, and copying the table into a Warehouse adds nothing.

When you open an item, check what it is. The workspace list shows Warehouses and SQL analytics endpoints as different item types, and the endpoint sits next to its Lakehouse.

For the full design decision, including governance, performance and mixed architectures, read Microsoft Fabric Lakehouse vs Warehouse. The Lakehouse vs Warehouse cheat sheet is the one-page version.

Query Lakehouse data from the Warehouse

The two items do not have to live apart. A Warehouse can query tables in other Warehouses and Lakehouse SQL analytics endpoints in the same workspace with three-part names, without copying data first.

If you followed the Delta tables lesson, the lh_learning Lakehouse has a sales_orders table. Add it to the Warehouse editor:

  1. In the Warehouse Explorer, select + Warehouses.
  2. Choose the lh_learning SQL analytics endpoint and confirm.

It now appears in the Explorer next to wh_learning. Reference its tables as item.schema.table:

Requête SQL: Query Lakehouse data from the WarehouseT-SQL (Fabric Warehouse)
SELECT TOP (10)
    o.sales_order_id,
    o.status,
    o.grand_total
FROM lh_learning.dbo.sales_orders AS o
ORDER BY o.grand_total DESC;
T-SQL (Fabric Warehouse)6 lignesAffichage en lecture seule. Aucune connexion à une base de données.

You can also land the result of a Lakehouse query into a Warehouse table with CREATE TABLE AS SELECT:

Requête SQL: Query Lakehouse data from the Warehouse (2)T-SQL (Fabric Warehouse)
CREATE TABLE sales.LakehouseOrdersByStatus
AS
SELECT
    o.status,
    COUNT(*)           AS OrderCount,
    SUM(o.grand_total) AS Revenue
FROM lh_learning.dbo.sales_orders AS o
GROUP BY o.status;
T-SQL (Fabric Warehouse)8 lignesAffichage en lecture seule. Aucune connexion à une base de données.

The read side goes through the Lakehouse’s endpoint and the write side goes into the Warehouse, in one statement. This is how the two items work together in practice:

  • the Lakehouse handles open data and Spark work: raw files, parsing, cleansing, Python;
  • the Warehouse handles relational T-SQL work: dimensional models, curated tables, transactional changes;
  • cross-database queries connect them without an extra copy step.

Two limits to keep in mind. Cross-database queries work within one workspace; they are not a way to join items across workspaces. And items with different collations can return errors or unexpected results when joined, which is another reason to settle the workspace collation before you create items.

Basic permissions with GRANT

Access to a Warehouse starts outside SQL:

  • Workspace roles. Admin, Member and Contributor get full control of every Warehouse in the workspace. Viewer gets permission to connect and read data with T-SQL.
  • Item permissions. You can share a single Warehouse with someone who is not in the workspace. Read permission is the minimum required to connect at all.

Inside the Warehouse, standard T-SQL GRANT, DENY and REVOKE refine what a connected user can do. A typical first case: an analyst should query the sales schema and nothing else.

Requête SQL: Basic permissions with GRANTT-SQL (Fabric Warehouse)
GRANT SELECT ON SCHEMA::sales TO [analyst@contoso.com];
T-SQL (Fabric Warehouse)1 ligneAffichage en lecture seule. Aucune connexion à une base de données.

Replace the address with a real Microsoft Entra user, or better, an Entra group. There is no CREATE USER step: you cannot run CREATE USER in a Warehouse, and Fabric creates the database user automatically when you run GRANT. The person still needs Read on the Warehouse, for example by sharing it with them without extra data permissions, before they can connect.

Then check what was granted:

Requête SQL: Basic permissions with GRANT (2)T-SQL (Fabric Warehouse)
SELECT DISTINCT
    pr.name,
    pr.type_desc,
    pe.state_desc,
    pe.permission_name
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;
T-SQL (Fabric Warehouse)8 lignesAffichage en lecture seule. Aucune connexion à une base de données.

This query shows only permissions granted explicitly in SQL, not those that come from workspace roles or item sharing.

How the layers interact:

  • SQL permissions refine access for users with item permissions or the Viewer role. Workspace Admins, Members and Contributors already have full control, so a DENY does not restrict them.
  • A Viewer already has read access to all data. To limit someone to specific objects, share the Warehouse with them instead of adding them to the workspace, and grant only what they need.
  • Prefer groups to individuals, and schemas or views to individual tables. Least privilege is easier to keep when the grants are few and readable.

Row-level security, column-level security and dynamic data masking build on the same model. They are worth learning once your Warehouse holds data that not everyone should see.

Common beginner mistakes

  • Opening the Lakehouse SQL analytics endpoint and expecting a Warehouse. It looks similar and runs T-SQL, but INSERT, UPDATE, DELETE and CREATE TABLE fail there. Check the item type first.
  • Assuming every SQL Server feature exists. The supported T-SQL surface is large, but it is not identical to SQL Server. Triggers, synonyms, materialized views, recursive queries, CREATE USER, nvarchar and datetime columns are some of the gaps. Check the surface area before porting code.
  • Putting everything in dbo. It works on day one and becomes a junk drawer by month three. Create schemas for subject areas and layers from the start.
  • Forgetting to qualify schema names. Write sales.Customer, not Customer. Unqualified names resolve against your default schema and break the moment two schemas hold similar objects.
  • Running UPDATE or DELETE before validating the WHERE clause. Run the matching SELECT, read the rows, then reuse the same predicate.
  • Creating very wide tables. The limit is 1,024 columns per table, but the real problems appear long before that. Wide tables with many large varchar columns are harder to query and model. Split by subject and keep strings sized to the data.
  • Splitting a transaction across editor runs. BEGIN TRANSACTION and COMMIT must go in the same Run in the browser editor, with no GO between them.
  • Trusting declared keys. Primary and foreign keys are NOT ENFORCED. Duplicates are accepted unless your load prevents them.
  • Treating the Warehouse as “SQL Server in the cloud”. The language is familiar; the engine, storage, concurrency model and operations are not. No indexes to tune, table-level write conflicts, capacity-based compute, data in OneLake.

Production considerations

The sample is small on purpose. When you build a real Warehouse, a few habits matter more than any syntax:

  • Capacity is shared. The Warehouse uses the same Fabric capacity as your notebooks, pipelines, semantic models and reports. A heavy query or load competes with everything else in that capacity. Fabric Capacity and Compute Usage explains how that consumption is measured.
  • Load in sets, not rows. For files, use COPY INTO, which gives the highest ingestion throughput. Between items, use INSERT ... SELECT or CREATE TABLE AS SELECT. For orchestrated, repeatable loads, use pipelines. Thousands of single-row INSERT statements are the slowest option and leave the table in poor shape.
  • Load incrementally. Reloading everything every night works until it does not. Incremental Loads in Microsoft Fabric covers watermarks and change patterns.
  • Make loads safe to rerun. A failed load that is retried should not double the data. Staging tables plus MERGE or delete-and-insert inside a transaction are the usual tools.
  • Plan for write conflicts. Because conflicts are detected per table, schedule loads so two processes do not update the same table at the same time, and add retry logic.
  • Monitor from day one. Know which queries run, how long they take and what they cost. Monitoring Fabric pipelines and notebooks and designing an execution log apply to Warehouse loads too.
  • Keep the schema in source control. Warehouse objects can be versioned with Fabric Git integration, so table and view changes are reviewed like code.

The complete script

Here is the whole lesson as one script. It drops and recreates the view and tables, so you can run it again and get the same result.

The script assumes the sales schema already exists. You created it earlier in this lesson; in a fresh Warehouse, run this once, on its own, before the script:

Requête SQL: The complete scriptT-SQL (Fabric Warehouse)
CREATE SCHEMA sales;
T-SQL (Fabric Warehouse)1 ligneAffichage en lecture seule. Aucune connexion à une base de données.

CREATE SCHEMA must be the only statement in its batch, and it fails if the schema already exists. Keeping it out of the script is what makes the rest safe to rerun.

How the script runs in the browser editor: the GO separators split it into batches, and the Fabric SQL query editor sends each batch as an independent request in a new session. Nothing here depends on session state surviving a GO. Each batch works only with persisted objects (the schema, tables and view) that earlier batches created: there are no temp tables, variables, SET options or open transactions carried across batches. The batches exist because CREATE VIEW must start its own batch, and a table should not be dropped and created again in the same batch.

The transaction, the DELETE, the cross-database query and the GRANT are not included. They change state in ways that make reruns inconsistent or depend on other items.

Requête SQL: The complete scriptT-SQL (Fabric Warehouse)
/* ============================================================
   1. Clean up previous runs (safe to rerun)
   ============================================================ */
DROP VIEW IF EXISTS sales.vwCustomerSales;
DROP TABLE IF EXISTS sales.SalesOrder;
DROP TABLE IF EXISTS sales.Product;
DROP TABLE IF EXISTS sales.Customer;
GO

/* ============================================================
   2. Tables
   ============================================================ */
CREATE TABLE sales.Customer
(
    CustomerId    int          NOT NULL,
    CustomerName  varchar(100) NOT NULL,
    Country       varchar(50)  NOT NULL
);

CREATE TABLE sales.Product
(
    ProductId    int           NOT NULL,
    ProductName  varchar(100)  NOT NULL,
    UnitPrice    decimal(10,2) NOT NULL
);

CREATE TABLE sales.SalesOrder
(
    SalesOrderId  int  NOT NULL,
    CustomerId    int  NOT NULL,
    ProductId     int  NOT NULL,
    Quantity      int  NOT NULL,
    OrderDate     date NOT NULL
);
GO

/* ============================================================
   3. Sample data
   ============================================================ */
INSERT INTO sales.Customer (CustomerId, CustomerName, Country)
VALUES
    (1, 'Lumen Cycles',      'Spain'),
    (2, 'Alpine Outfitters', 'France'),
    (3, 'Harbor Supply Co',  'Ireland');

INSERT INTO sales.Product (ProductId, ProductName, UnitPrice)
VALUES
    (1, 'Road Helmet',    79.00),
    (2, 'Trail Gloves',   24.50),
    (3, 'LED Bike Light', 39.90),
    (4, 'Water Bottle',    9.50);

INSERT INTO sales.SalesOrder (SalesOrderId, CustomerId, ProductId, Quantity, OrderDate)
VALUES
    (101, 1, 1,  2, '2026-09-01'),
    (102, 1, 3,  4, '2026-09-03'),
    (103, 2, 2, 10, '2026-09-05'),
    (104, 2, 4, 12, '2026-09-08'),
    (105, 3, 1,  1, '2026-09-10'),
    (106, 3, 3,  3, '2026-09-12');
GO

/* ============================================================
   4. JOIN with a calculated line total
   ============================================================ */
SELECT
    so.SalesOrderId,
    so.OrderDate,
    c.CustomerName,
    p.ProductName,
    so.Quantity,
    p.UnitPrice,
    so.Quantity * p.UnitPrice AS LineTotal
FROM sales.SalesOrder AS so
INNER JOIN sales.Customer AS c ON c.CustomerId = so.CustomerId
INNER JOIN sales.Product  AS p ON p.ProductId  = so.ProductId
ORDER BY so.SalesOrderId;

/* ============================================================
   5. Revenue by customer
   ============================================================ */
SELECT
    c.CustomerName,
    c.Country,
    COUNT(*)                       AS OrderCount,
    SUM(so.Quantity * p.UnitPrice) AS Revenue
FROM sales.SalesOrder AS so
INNER JOIN sales.Customer AS c ON c.CustomerId = so.CustomerId
INNER JOIN sales.Product  AS p ON p.ProductId  = so.ProductId
GROUP BY c.CustomerName, c.Country
ORDER BY Revenue DESC;

/* ============================================================
   6. UPDATE: validate, then change exactly one row
   ============================================================ */
SELECT CustomerId, CustomerName, Country
FROM sales.Customer
WHERE CustomerId = 2;

UPDATE sales.Customer
SET Country = 'Switzerland'
WHERE CustomerId = 2;
GO

/* ============================================================
   7. View for analysts and Power BI
   ============================================================ */
CREATE VIEW sales.vwCustomerSales
AS
SELECT
    so.SalesOrderId,
    so.OrderDate,
    c.CustomerId,
    c.CustomerName,
    c.Country,
    p.ProductId,
    p.ProductName,
    so.Quantity,
    p.UnitPrice,
    so.Quantity * p.UnitPrice AS LineTotal
FROM sales.SalesOrder AS so
INNER JOIN sales.Customer AS c ON c.CustomerId = so.CustomerId
INNER JOIN sales.Product  AS p ON p.ProductId  = so.ProductId;
GO

SELECT CustomerName, Country, SUM(LineTotal) AS Revenue
FROM sales.vwCustomerSales
GROUP BY CustomerName, Country
ORDER BY Revenue DESC;
T-SQL (Fabric Warehouse)129 lignesAffichage en lecture seule. Aucune connexion à une base de données.

The script leaves sales.LakehouseOrdersByStatus alone if you created it in the cross-database section. Drop it yourself when you no longer need it.

What you learned

  • A Fabric Warehouse is a T-SQL-first, read/write relational warehouse with DDL, DML and multi-table ACID transactions.
  • You create it from + New item in a workspace. It starts empty, runs on your Fabric capacity and stores its data in OneLake.
  • Schemas organize objects and simplify security. Always qualify object names.
  • Warehouse tables support a subset of SQL Server types: varchar instead of nvarchar, decimal instead of money, datetime2 instead of datetime.
  • SELECT, JOIN, GROUP BY, UPDATE, DELETE and CREATE VIEW work as you expect. Validate every UPDATE and DELETE with a matching SELECT first.
  • Transactions use snapshot isolation and detect conflicts per table. In the browser editor, run a whole transaction as one batch in one Run, with no GO inside it.
  • The Lakehouse SQL analytics endpoint reads Delta tables with T-SQL but does not write them. The Warehouse reads and writes.
  • Cross-database queries let a Warehouse read Lakehouse tables in the same workspace with three-part names.
  • Workspace roles and item permissions decide who can connect; GRANT refines what they can do inside.

What to learn next

The next Start Here step is the pipeline: orchestrating loads instead of running scripts by hand. The End-to-End Sales Analytics lab builds one with a real dataset, and Fabric Pipelines vs Notebooks explains when each tool fits.

After that, the topics that turn this lesson into a production Warehouse:

Étiquettes

  • Microsoft Fabric
  • Fabric Warehouse
  • T-SQL
  • SQL Endpoint
  • SQL Server