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)

Data Types in Fabric Warehouse

Category	Data Type	Use For
Integer	INT, BIGINT, SMALLINT, TINYINT	IDs, counts, keys
Decimal	DECIMAL(p,s), NUMERIC(p,s)	Money, precise calculations
Float	FLOAT, REAL	Scientific, approximate values
String	VARCHAR(n), CHAR(n), NVARCHAR(n)	Names, codes, descriptions
Date/Time	DATE, TIME, DATETIME2, DATETIMEOFFSET	Dates, timestamps
Boolean	BIT	True/false flags
Binary	VARBINARY(n)	Hashes, binary data
Unique ID	UNIQUEIDENTIFIER	GUIDs

Best practices: Use VARCHAR not NVARCHAR unless you need Unicode. Use DECIMAL(10,2) not FLOAT for money. Use DATE not DATETIME2 when time is not needed. Use INT for surrogate keys, BIGINT only for billion-row fact tables.

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 Stored Procedure

-- Load staging table from Lakehouse via stored procedure
CREATE PROCEDURE staging.usp_load_stg_customers
AS
BEGIN
    TRUNCATE TABLE staging.stg_customers;
    
    INSERT INTO staging.stg_customers (customer_id, name, email, city, country)
    SELECT customer_id, name, email, city, country
    FROM silver_lakehouse.dbo.customers_clean;
    
    PRINT 'Loaded ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';
END;

-- Call from pipeline: Stored Procedure Activity → EXEC staging.usp_load_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;

T-SQL Operations: INSERT, UPDATE, DELETE

-- INSERT: Add new rows
INSERT INTO gold.dim_product (product_id, product_name, category, price)
VALUES ('PROD-001', 'Laptop Pro 15', 'Electronics', 1299.99);

-- INSERT from SELECT
INSERT INTO gold.dim_product (product_id, product_name, category, price)
SELECT product_id, product_name, category, price
FROM staging.stg_products
WHERE product_id NOT IN (SELECT product_id FROM gold.dim_product);

-- UPDATE: Modify existing rows
UPDATE gold.dim_customer
SET email = 'new@company.com', updated_at = GETUTCDATE()
WHERE customer_id = 'CUST-001';

-- UPDATE with JOIN
UPDATE d SET d.tier = 'Platinum'
FROM gold.dim_customer d
JOIN staging.stg_upgrades u ON d.customer_id = u.customer_id;

-- DELETE: Remove rows
DELETE FROM staging.stg_customers;
DELETE FROM gold.dim_customer WHERE is_active = 0;

-- TRUNCATE: Fastest way to empty a table
TRUNCATE TABLE staging.stg_customers;

The MERGE Statement (SCD Patterns)

SCD Type 1 with MERGE

-- SCD1: Overwrite changes — no history kept
MERGE INTO gold.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

-- SCD2: Track history — two-step process

-- Step 1: Expire changed records
UPDATE gold.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 gold.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 + brand new customers
INSERT INTO gold.dim_customer
    (customer_id, name, email, city, country, is_active, start_date, end_date)
SELECT customer_id, name, email, city, country,
       1, CAST(GETUTCDATE() AS DATE), '9999-12-31'
FROM staging.stg_customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM gold.dim_customer WHERE is_active = 1
);

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 Fact Loading

CREATE PROCEDURE gold.usp_load_fact_sales
AS
BEGIN
    BEGIN TRY
        INSERT INTO gold.fact_sales
            (date_key, customer_key, product_key, order_number, quantity, unit_price, total_amount)
        SELECT
            CAST(FORMAT(o.order_date, 'yyyyMMdd') AS INT),
            dc.customer_key,
            dp.product_key,
            o.order_number,
            o.quantity,
            o.unit_price,
            o.quantity * o.unit_price
        FROM staging.stg_orders o
        JOIN gold.dim_customer dc ON o.customer_id = dc.customer_id AND dc.is_active = 1
        JOIN gold.dim_product dp ON o.product_id = dp.product_id
        WHERE o.order_number NOT IN (SELECT order_number FROM gold.fact_sales);
        
        PRINT 'Loaded ' + CAST(@@ROWCOUNT AS VARCHAR) + ' new facts';
    END TRY
    BEGIN CATCH
        PRINT 'ERROR: ' + ERROR_MESSAGE();
        THROW;
    END CATCH;
END;

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;

Monitoring and Performance

-- Recent query history
SELECT query_text, start_time, duration_ms, row_count, status
FROM queryinsights.exec_requests_history
ORDER BY start_time DESC;

-- Long-running queries (optimization targets)
SELECT * FROM queryinsights.long_running_queries ORDER BY duration_ms DESC;

-- Frequently run queries (cache candidates)
SELECT query_hash, execution_count, avg_duration_ms
FROM queryinsights.frequently_run_queries ORDER BY execution_count DESC;

Create statistics on frequently filtered columns, enable result set caching, and use Warehouse Advanced for COPY INTO, CTAS, and DMVs.

The Full Gold Layer Example

-- Complete Gold layer build script

-- Schemas
CREATE SCHEMA staging;
CREATE SCHEMA gold;
CREATE SCHEMA reports;

-- Staging tables
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
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, SUM(f.total_amount) AS revenue
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;

CREATE VIEW reports.vw_top_customers AS
SELECT dc.name, dc.city, 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;

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.