Microsoft Fabric Warehouse: The Complete Practical Guide — T-SQL, Tables, Views, Stored Procedures, Security, and Building Your Gold Layer

You know what a Fabric Warehouse IS and when to use it vs a Lakehouse. Now it is time to BUILD one. This post is the hands-on guide — creating a warehouse, building tables with T-SQL, loading data, writing stored procedures, creating views for Power BI, implementing security (row-level and column-level), and building a complete star schema Gold layer.

If the Lakehouse is the workshop where engineers build with PySpark, the Warehouse is the showroom where analysts shop with T-SQL. This post teaches you to stock the showroom, organize the shelves, control who sees what, and serve customers efficiently.

Table of Contents

• Creating a Warehouse
• The Warehouse Interface
• Creating Tables with T-SQL
• Data Types in Fabric Warehouse
• Loading Data into the Warehouse
• Load from Lakehouse (Cross-Database Query)
• Load from Pipeline (Copy Activity)
• Load with T-SQL MERGE (Upsert)
• Load with Stored Procedure
• T-SQL Operations: INSERT, UPDATE, DELETE
• The MERGE Statement (SCD Patterns)
• SCD Type 1 with MERGE
• SCD Type 2 with MERGE
• Views for Power BI
• Standard Views
• Views with Business Logic
• Stored Procedures
• Procedure for Dimension Loading
• Procedure for Fact Loading
• Procedure for Full ETL
• Schemas for Organization
• Table Cloning
• Security in Fabric Warehouse
• Object-Level Security (GRANT / DENY)
• Row-Level Security (RLS)
• Column-Level Security (CLS)
• Dynamic Data Masking
• Cross-Database Queries (Warehouse + Lakehouse)
• Monitoring and Performance
• Building a Complete Star Schema
• The Full Gold Layer Example
• Common Mistakes
• Interview Questions
• Wrapping Up

Creating a Warehouse

Step by Step

1. Open your Fabric workspace
2. Click + New item
3. Select Warehouse
4. Name: sales_warehouse
5. Click Create

The warehouse opens with a SQL query editor — familiar to anyone who has used SSMS or Azure Data Studio.

The Warehouse Interface

┌─────────────────────────────────────────────────────────────────┐
│  WAREHOUSE: sales_warehouse                                      │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ┌─────────────────┐   ┌────────────────────────────────────┐   │
│  │ OBJECT EXPLORER  │   │  SQL QUERY EDITOR                  │   │
│  │                  │   │                                     │   │
│  │ ▼ Schemas        │   │  SELECT d.department_name,          │   │
│  │   ▼ dbo          │   │         COUNT(*) AS headcount       │   │
│  │     ▼ Tables     │   │  FROM dim_department d               │   │
│  │       dim_cust   │   │  JOIN fact_sales f ON d.dept_key =  │   │
│  │       dim_prod   │   │       f.dept_key                     │   │
│  │       fact_sales │   │  GROUP BY d.department_name;         │   │
│  │     ▼ Views      │   │                                     │   │
│  │       vw_revenue │   │  ┌─────────────────────────────┐    │   │
│  │     ▼ Procedures │   │  │ RESULTS                      │    │   │
│  │       usp_load   │   │  │ department_name | headcount │    │   │
│  │   ▼ staging      │   │  │ Engineering    | 3          │    │   │
│  │   ▼ gold         │   │  │ Analytics      | 3          │    │   │
│  └─────────────────┘   │  └─────────────────────────────┘    │   │
│                          └────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────┘

Three panels: Object Explorer (left — browse schemas, tables, views, procedures), Query Editor (top right — write and run T-SQL), Results (bottom right — query output).

Creating Tables with T-SQL

Basic Table Creation

-- Create a dimension table
CREATE TABLE dbo.dim_customer (
    customer_key    INT IDENTITY(1,1) NOT NULL,    -- Surrogate key (auto-increment)
    customer_id     VARCHAR(20) NOT NULL,           -- Natural/business key
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(100),
    city            VARCHAR(50),
    country         VARCHAR(50),
    tier            VARCHAR(20) DEFAULT 'Standard',
    is_active       BIT DEFAULT 1,
    start_date      DATE NOT NULL,
    end_date        DATE DEFAULT '9999-12-31',
    created_at      DATETIME2 DEFAULT GETUTCDATE(),
    updated_at      DATETIME2 DEFAULT GETUTCDATE()
);

-- Create a fact table
CREATE TABLE dbo.fact_sales (
    sale_key        BIGINT IDENTITY(1,1) NOT NULL,
    date_key        INT NOT NULL,
    customer_key    INT NOT NULL,
    product_key     INT NOT NULL,
    store_key       INT NOT NULL,
    order_number    VARCHAR(20),                     -- Degenerate dimension
    quantity        INT NOT NULL,
    unit_price      DECIMAL(10,2) NOT NULL,
    discount        DECIMAL(10,2) DEFAULT 0,
    total_amount    DECIMAL(12,2) NOT NULL
);

-- Create a date dimension
CREATE TABLE dbo.dim_date (
    date_key        INT NOT NULL,                    -- Format: YYYYMMDD
    full_date       DATE NOT NULL,
    year            INT NOT NULL,
    quarter         INT NOT NULL,
    month           INT NOT NULL,
    month_name      VARCHAR(20) NOT NULL,
    day_of_week     INT NOT NULL,
    day_name        VARCHAR(20) NOT NULL,
    is_weekend      BIT NOT NULL,
    is_holiday      BIT DEFAULT 0
);

Populate the Date Dimension

-- Generate 10 years of dates (2020-2030)
DECLARE @start DATE = '2020-01-01';
DECLARE @end DATE = '2030-12-31';

;WITH DateRange AS (
    SELECT @start AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM DateRange WHERE dt < @end
)
INSERT INTO dbo.dim_date (date_key, full_date, year, quarter, month, month_name,
                           day_of_week, day_name, is_weekend, is_holiday)
SELECT
    CAST(FORMAT(dt, 'yyyyMMdd') AS INT) AS date_key,
    dt AS full_date,
    YEAR(dt) AS year,
    DATEPART(QUARTER, dt) AS quarter,
    MONTH(dt) AS month,
    DATENAME(MONTH, dt) AS month_name,
    DATEPART(WEEKDAY, dt) AS day_of_week,
    DATENAME(WEEKDAY, dt) AS day_name,
    CASE WHEN DATEPART(WEEKDAY, dt) IN (1, 7) THEN 1 ELSE 0 END AS is_weekend,
    0 AS is_holiday
FROM DateRange
OPTION (MAXRECURSION 5000);

SELECT COUNT(*) AS total_dates FROM dbo.dim_date;
-- 4018 dates (2020-01-01 to 2030-12-31)

Loading Data into the Warehouse

Load from Lakehouse (Cross-Database Query)

The most powerful pattern — query Lakehouse tables directly from the Warehouse:

-- Read from silver_lakehouse and insert into warehouse
INSERT INTO dbo.dim_customer (customer_id, name, email, city, country, start_date)
SELECT
    customer_id,
    name,
    email,
    city,
    country,
    CAST(GETUTCDATE() AS DATE) AS start_date
FROM silver_lakehouse.dbo.customers_clean
WHERE customer_id NOT IN (SELECT customer_id FROM dbo.dim_customer);

No pipelines, no Copy activities — direct T-SQL query across Lakehouse and Warehouse in the same workspace.

Load from Pipeline (Copy Activity)

Pipeline: PL_Load_Warehouse
  Copy Activity:
    Source: Lakehouse → silver_lakehouse → customers_clean
    Destination: Warehouse → sales_warehouse → staging.stg_customers

Load with T-SQL MERGE (Upsert)

-- Load staging data, then MERGE into dimension
MERGE INTO dbo.dim_customer AS target
USING staging.stg_customers AS source
ON target.customer_id = source.customer_id AND target.is_active = 1

WHEN MATCHED AND (
    target.name != source.name OR
    target.email != source.email OR
    target.city != source.city
) THEN
    UPDATE SET
        target.name = source.name,
        target.email = source.email,
        target.city = source.city,
        target.updated_at = GETUTCDATE()

WHEN NOT MATCHED BY TARGET THEN
    INSERT (customer_id, name, email, city, country, start_date)
    VALUES (source.customer_id, source.name, source.email,
            source.city, source.country, CAST(GETUTCDATE() AS DATE));

SELECT @@ROWCOUNT AS rows_affected;

The MERGE Statement (SCD Patterns)

SCD Type 1 with MERGE (Overwrite Changes)

-- SCD1: Just update the current record — no history
MERGE INTO dbo.dim_product AS target
USING staging.stg_products AS source
ON target.product_id = source.product_id

WHEN MATCHED AND (
    target.product_name != source.product_name OR
    target.category != source.category OR
    target.price != source.price
) THEN
    UPDATE SET
        target.product_name = source.product_name,
        target.category = source.category,
        target.price = source.price,
        target.updated_at = GETUTCDATE()

WHEN NOT MATCHED BY TARGET THEN
    INSERT (product_id, product_name, category, price, created_at)
    VALUES (source.product_id, source.product_name, source.category,
            source.price, GETUTCDATE());

SCD Type 2 with MERGE (Track History)

-- Step 1: Expire changed records
UPDATE dbo.dim_customer
SET end_date = CAST(GETUTCDATE() AS DATE),
    is_active = 0,
    updated_at = GETUTCDATE()
WHERE customer_id IN (
    SELECT s.customer_id
    FROM staging.stg_customers s
    JOIN dbo.dim_customer d ON s.customer_id = d.customer_id AND d.is_active = 1
    WHERE s.name != d.name OR s.email != d.email OR s.city != d.city
);

-- Step 2: Insert new version of changed records + brand new records
INSERT INTO dbo.dim_customer (customer_id, name, email, city, country,
                               is_active, start_date, end_date, created_at)
SELECT
    s.customer_id, s.name, s.email, s.city, s.country,
    1 AS is_active,
    CAST(GETUTCDATE() AS DATE) AS start_date,
    '9999-12-31' AS end_date,
    GETUTCDATE() AS created_at
FROM staging.stg_customers s
WHERE s.customer_id IN (
    -- Changed records (just expired above)
    SELECT customer_id FROM dbo.dim_customer
    WHERE is_active = 0 AND end_date = CAST(GETUTCDATE() AS DATE)
)
OR s.customer_id NOT IN (
    -- Brand new customers
    SELECT customer_id FROM dbo.dim_customer
);

Views for Power BI

Standard Views

-- Monthly revenue by product
CREATE VIEW dbo.vw_monthly_revenue AS
SELECT
    dd.year,
    dd.month,
    dd.month_name,
    dp.product_name,
    dp.category,
    SUM(f.total_amount) AS revenue,
    SUM(f.quantity) AS units_sold,
    COUNT(DISTINCT f.customer_key) AS unique_customers
FROM dbo.fact_sales f
JOIN dbo.dim_date dd ON f.date_key = dd.date_key
JOIN dbo.dim_product dp ON f.product_key = dp.product_key
GROUP BY dd.year, dd.month, dd.month_name, dp.product_name, dp.category;

Views with Business Logic

-- Customer 360 view (single view of everything about a customer)
CREATE VIEW dbo.vw_customer_360 AS
SELECT
    dc.customer_id,
    dc.name,
    dc.email,
    dc.city,
    dc.country,
    dc.tier,
    COUNT(DISTINCT f.sale_key) AS total_orders,
    SUM(f.total_amount) AS lifetime_revenue,
    AVG(f.total_amount) AS avg_order_value,
    MIN(dd.full_date) AS first_order_date,
    MAX(dd.full_date) AS last_order_date,
    DATEDIFF(DAY, MAX(dd.full_date), GETUTCDATE()) AS days_since_last_order,
    CASE
        WHEN SUM(f.total_amount) >= 10000 THEN 'Platinum'
        WHEN SUM(f.total_amount) >= 5000 THEN 'Gold'
        WHEN SUM(f.total_amount) >= 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS calculated_tier
FROM dbo.dim_customer dc
LEFT JOIN dbo.fact_sales f ON dc.customer_key = f.customer_key
LEFT JOIN dbo.dim_date dd ON f.date_key = dd.date_key
WHERE dc.is_active = 1
GROUP BY dc.customer_key, dc.customer_id, dc.name, dc.email,
         dc.city, dc.country, dc.tier;

Power BI connects to these views via Direct Lake — dashboards always fresh.

Stored Procedures

Procedure for Dimension Loading

CREATE PROCEDURE dbo.usp_load_dim_customer
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

            -- Step 1: Expire changed records
            UPDATE dbo.dim_customer
            SET end_date = CAST(GETUTCDATE() AS DATE), is_active = 0
            WHERE customer_id IN (
                SELECT s.customer_id FROM staging.stg_customers s
                JOIN dbo.dim_customer d ON s.customer_id = d.customer_id
                    AND d.is_active = 1
                WHERE s.name != d.name OR s.email != d.email OR s.city != d.city
            );

            DECLARE @expired INT = @@ROWCOUNT;

            -- Step 2: Insert new versions + new customers
            INSERT INTO dbo.dim_customer
                (customer_id, name, email, city, country, is_active, start_date)
            SELECT customer_id, name, email, city, country, 1, CAST(GETUTCDATE() AS DATE)
            FROM staging.stg_customers
            WHERE customer_id NOT IN (
                SELECT customer_id FROM dbo.dim_customer WHERE is_active = 1
            );

            DECLARE @inserted INT = @@ROWCOUNT;

        COMMIT;
        PRINT 'dim_customer loaded: ' + CAST(@expired AS VARCHAR) + ' expired, '
              + CAST(@inserted AS VARCHAR) + ' inserted';
    END TRY
    BEGIN CATCH
        ROLLBACK;
        PRINT 'ERROR: ' + ERROR_MESSAGE();
        THROW;
    END CATCH;
END;

-- Execute
EXEC dbo.usp_load_dim_customer;

Procedure for Full ETL

CREATE PROCEDURE dbo.usp_daily_etl
AS
BEGIN
    PRINT '=== Starting Daily ETL: ' + CAST(GETUTCDATE() AS VARCHAR) + ' ===';

    -- Step 1: Load dimensions
    PRINT 'Loading dim_customer...';
    EXEC dbo.usp_load_dim_customer;

    PRINT 'Loading dim_product...';
    EXEC dbo.usp_load_dim_product;

    -- Step 2: Load fact table
    PRINT 'Loading fact_sales...';
    EXEC dbo.usp_load_fact_sales;

    -- Step 3: Refresh summary views (if materialized)
    PRINT 'ETL complete.';
END;

-- Call from Pipeline: Stored Procedure activity → EXEC dbo.usp_daily_etl

Schemas for Organization

-- Create schemas
CREATE SCHEMA staging;    -- Temporary landing zone for pipeline loads
CREATE SCHEMA gold;       -- Star schema (dimensions + facts)
CREATE SCHEMA reports;    -- Views optimized for Power BI

-- Create tables in schemas
CREATE TABLE staging.stg_customers (...);
CREATE TABLE staging.stg_orders (...);

CREATE TABLE gold.dim_customer (...);
CREATE TABLE gold.dim_product (...);
CREATE TABLE gold.fact_sales (...);

-- Create views in reports schema
CREATE VIEW reports.vw_monthly_revenue AS ...;
CREATE VIEW reports.vw_customer_360 AS ...;

sales_warehouse/
  ├── staging/           ← Pipeline loads raw data here
  │     ├── stg_customers
  │     └── stg_orders
  ├── gold/              ← Star schema (dimensions + facts)
  │     ├── dim_customer
  │     ├── dim_product
  │     ├── dim_date
  │     └── fact_sales
  └── reports/           ← Views for Power BI
        ├── vw_monthly_revenue
        └── vw_customer_360

Table Cloning

Fabric Warehouse supports zero-copy table cloning — create a copy instantly without duplicating data:

-- Clone a table (instant, zero-copy)
CREATE TABLE gold.dim_customer_backup
AS CLONE OF gold.dim_customer;

-- Clone at a point in time (time travel)
CREATE TABLE gold.dim_customer_yesterday
AS CLONE OF gold.dim_customer
AT '2026-05-24T00:00:00Z';

-- Useful for: backup before ETL, testing, auditing

Security in Fabric Warehouse

Object-Level Security (GRANT / DENY)

-- Grant SELECT on specific tables to a user/role
GRANT SELECT ON gold.dim_customer TO [analyst@company.com];
GRANT SELECT ON gold.fact_sales TO [analyst@company.com];
DENY SELECT ON staging.stg_customers TO [analyst@company.com];

-- Grant on entire schema
GRANT SELECT ON SCHEMA::reports TO [analyst_group];
-- Analysts can query any view in the reports schema

Row-Level Security (RLS)

Control which ROWS a user can see:

-- Step 1: Create a filter function
CREATE FUNCTION dbo.fn_security_filter(@region VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @region = USER_NAME()
   OR USER_NAME() = 'admin@company.com';    -- Admin sees all

-- Step 2: Create a security policy
CREATE SECURITY POLICY dbo.RegionFilter
ADD FILTER PREDICATE dbo.fn_security_filter(region)
ON gold.fact_sales
WITH (STATE = ON);

-- Now:
-- east_manager@company.com sees only rows WHERE region = 'east_manager@company.com'
-- admin@company.com sees ALL rows

Column-Level Security (CLS)

Control which COLUMNS a user can see:

-- Grant SELECT on specific columns only
GRANT SELECT ON gold.dim_customer (customer_id, name, city, country) TO [analyst@company.com];
-- Analyst can see name and city but NOT email or salary

-- Deny specific columns
DENY SELECT ON gold.dim_customer (email, phone) TO [marketing_team];
-- Marketing cannot see PII columns

Dynamic Data Masking

Show masked data to unauthorized users:

-- Add masking to columns
ALTER TABLE gold.dim_customer
ALTER COLUMN email ADD MASKED WITH (FUNCTION = 'email()');
-- Analysts see: nXXX@XXXX.com instead of naveen@email.com

ALTER TABLE gold.dim_customer
ALTER COLUMN phone ADD MASKED WITH (FUNCTION = 'partial(3, "XXX-XXX-", 4)');
-- Analysts see: 416-XXX-XXX-1234

-- Grant unmask to specific users
GRANT UNMASK ON gold.dim_customer TO [hr_admin@company.com];
-- HR admin sees full unmasked data

Cross-Database Queries (Warehouse + Lakehouse)

-- From the Warehouse, query a Lakehouse table directly
SELECT w.customer_id, w.name, w.lifetime_revenue,
       l.churn_score, l.predicted_churn
FROM gold.dim_customer w
JOIN ml_lakehouse.dbo.churn_predictions l
    ON w.customer_id = l.customer_id
WHERE l.predicted_churn = 1;
-- Combines warehouse dimension data with lakehouse ML predictions

This is the power of Fabric — no data movement needed between Lakehouse and Warehouse in the same workspace.

Building a Complete Star Schema

-- The complete Gold layer in one script

-- Schemas
CREATE SCHEMA IF NOT EXISTS staging;
CREATE SCHEMA IF NOT EXISTS gold;
CREATE SCHEMA IF NOT EXISTS reports;

-- Staging tables (populated by pipeline Copy activities)
CREATE TABLE staging.stg_customers (
    customer_id VARCHAR(20), name VARCHAR(100), email VARCHAR(100),
    city VARCHAR(50), country VARCHAR(50)
);

CREATE TABLE staging.stg_orders (
    order_id INT, customer_id VARCHAR(20), product_id VARCHAR(20),
    quantity INT, unit_price DECIMAL(10,2), order_date DATE
);

-- Dimension: Customer (SCD Type 2)
CREATE TABLE gold.dim_customer (
    customer_key INT IDENTITY(1,1), customer_id VARCHAR(20),
    name VARCHAR(100), email VARCHAR(100), city VARCHAR(50), country VARCHAR(50),
    is_active BIT DEFAULT 1, start_date DATE, end_date DATE DEFAULT '9999-12-31'
);

-- Dimension: Product
CREATE TABLE gold.dim_product (
    product_key INT IDENTITY(1,1), product_id VARCHAR(20),
    product_name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2)
);

-- Dimension: Date (populated with recursive CTE above)
CREATE TABLE gold.dim_date (
    date_key INT, full_date DATE, year INT, quarter INT, month INT,
    month_name VARCHAR(20), day_name VARCHAR(20), is_weekend BIT
);

-- Fact: Sales
CREATE TABLE gold.fact_sales (
    sale_key BIGINT IDENTITY(1,1),
    date_key INT, customer_key INT, product_key INT,
    order_number VARCHAR(20), quantity INT,
    unit_price DECIMAL(10,2), total_amount DECIMAL(12,2)
);

-- Views for Power BI
CREATE VIEW reports.vw_daily_revenue AS
SELECT dd.full_date, dd.year, dd.month_name, dd.day_name,
       SUM(f.total_amount) AS revenue, SUM(f.quantity) AS units
FROM gold.fact_sales f
JOIN gold.dim_date dd ON f.date_key = dd.date_key
GROUP BY dd.full_date, dd.year, dd.month_name, dd.day_name;

CREATE VIEW reports.vw_top_customers AS
SELECT dc.name, dc.city, dc.country,
       COUNT(*) AS orders, SUM(f.total_amount) AS revenue
FROM gold.fact_sales f
JOIN gold.dim_customer dc ON f.customer_key = dc.customer_key
WHERE dc.is_active = 1
GROUP BY dc.customer_key, dc.name, dc.city, dc.country;

Common Mistakes

1. Not using schemas — everything in dbo is chaos. Use staging/gold/reports schemas.

2. Not using stored procedures for ETL — inline SQL scattered across pipeline activities is unmaintainable. Wrap ETL logic in stored procedures with TRY/CATCH.

3. Forgetting transactions in MERGE operations — SCD Type 2 requires expiring old records AND inserting new ones atomically. Without a transaction, a failure between steps leaves broken data.

4. Not implementing RLS for multi-tenant data — regional managers seeing other regions’ data is a compliance violation. Always implement RLS when different users should see different data.

5. Loading directly into gold tables without staging — always load into staging first, then MERGE into gold. This allows validation, retry, and idempotent loads.

6. Not creating a date dimension — every star schema needs dim_date. Without it, every query recalculates year, month, quarter, is_weekend. Pre-compute once, use everywhere.

Interview Questions

Q: How do you build a star schema in Fabric Warehouse? A: Create schemas (staging, gold, reports). Build dimension tables with surrogate keys (IDENTITY), natural keys, and SCD columns (is_active, start_date, end_date). Build fact tables with foreign keys to dimensions and numeric measures. Populate dim_date with a recursive CTE. Load data via staging tables and MERGE into dimensions. Create views in the reports schema for Power BI.

Q: How does security work in Fabric Warehouse? A: Four levels: Object-Level Security (GRANT/DENY on tables and schemas), Row-Level Security (filter predicate functions that restrict rows per user), Column-Level Security (GRANT SELECT on specific columns only), and Dynamic Data Masking (show masked values to unauthorized users). All are T-SQL based and familiar to anyone who knows SQL Server security.

Q: How do you load data from a Lakehouse into a Warehouse? A: Three methods: cross-database queries (SELECT FROM lakehouse.dbo.table directly in Warehouse T-SQL), Pipeline Copy Activity (Copy from Lakehouse to Warehouse staging table), or Stored Procedure (INSERT INTO warehouse.staging FROM lakehouse.dbo.table). Cross-database queries are the simplest for same-workspace access.

Wrapping Up

The Fabric Warehouse is your Gold layer powerhouse — full T-SQL support, stored procedures, views, transactions, and enterprise security. Load from Lakehouses via cross-database queries, build star schemas with MERGE, secure with RLS/CLS, and serve to Power BI through views.

The pattern is clear: Pipeline (ingest) → Lakehouse (Bronze/Silver with PySpark) → Warehouse (Gold with T-SQL) → Power BI (reports via Direct Lake).

Related posts: – Lakehouse vs Warehouse – Fabric Lakehouse Practical Guide – Normalization and Star Schema – SQL Transactions and ACID – SCD Types

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.