Learn how to design and build a production-ready dimensional model in Microsoft Fabric Warehouse using T-SQL. This lesson covers DDL for fact and dimension tables, view design patterns, and cross-database queries that join warehouse and lakehouse data without moving it.
You've got data landing in your Fabric environment — maybe from a pipeline, a Dataflow Gen2 load, or a Spark notebook writing Delta tables to the lakehouse. Now you need to model it properly: enforce schemas, build reusable business logic, and serve clean, performant data to your Power BI reports and downstream consumers. The Fabric Data Warehouse is where that work happens, and T-SQL is your primary tool for doing it.
The Fabric Warehouse isn't just "SQL Server in the cloud." It's a distributed query engine built on top of OneLake that stores data in Delta Parquet format and executes T-SQL against it at scale. That means you get a familiar SQL interface, full DDL/DML support, and the ability to query across warehouses and lakehouses in the same workspace — all without moving data between systems. Once you understand how those pieces fit together, you can build a production-grade warehouse layer that's both flexible and maintainable.
By the end of this lesson, you'll have built a working dimensional model inside a Fabric Warehouse: fact and dimension tables with proper constraints, views that encapsulate business logic, and cross-database queries that pull from a lakehouse without duplicating data.
What you'll learn:
Before working through this lesson, you should be comfortable with:
You'll also want a Fabric Lakehouse in your workspace with at least one Delta table to query. If you haven't built one yet, work through Building Your First Lakehouse in Microsoft Fabric: Files, Tables, and the SQL Analytics Endpoint first.
Before writing any DDL, you need a Warehouse item in your Fabric workspace. In your workspace, click + New item, scroll to the Data Warehousing section, and select Warehouse. Give it a meaningful name — we'll use sales_dw throughout this lesson. The warehouse is provisioned in seconds, and you'll land in a web-based SQL editor that's your primary authoring environment.
You'll notice the interface looks similar to Azure Data Studio. You get a query pane, an object explorer showing schemas and tables, and a results pane. Everything you do here is against a logical SQL database endpoint backed by OneLake storage.
Note
The Fabric Warehouse web editor is solid for ad-hoc development, but many teams connect via SQL Server Management Studio (SSMS) or Azure Data Studio using the connection string found under Settings > SQL connection string in the workspace. The TDS endpoint is fully compatible with both tools, so use whichever you prefer.
One thing to establish immediately is a schema structure. Don't dump everything into dbo. A common and maintainable pattern for a dimensional warehouse is:
CREATE SCHEMA raw; -- staging tables that mirror source systems
CREATE SCHEMA dim; -- dimension tables
CREATE SCHEMA fact; -- fact tables
CREATE SCHEMA rpt; -- reporting views
GO
This gives you clear ownership boundaries and makes permission management much simpler when you onboard consumers who should only read from rpt, not from raw.
Let's work with a realistic scenario: a retail company tracking sales transactions. Data arrives daily from an ERP system, lands in a lakehouse as Delta tables, and you need to model it into a star schema in the warehouse for reporting.
Start with your dimension tables. These are relatively small, change slowly, and form the backbone of every fact table join.
CREATE TABLE dim.customer (
customer_key INT NOT NULL,
customer_id VARCHAR(20) NOT NULL,
full_name VARCHAR(200) NOT NULL,
email VARCHAR(320) NULL,
city VARCHAR(100) NULL,
state_province VARCHAR(100) NULL,
country_code CHAR(2) NOT NULL DEFAULT 'US',
customer_segment VARCHAR(50) NULL,
effective_date DATE NOT NULL,
expiry_date DATE NULL,
is_current BIT NOT NULL DEFAULT 1,
inserted_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
CREATE TABLE dim.product (
product_key INT NOT NULL,
product_id VARCHAR(50) NOT NULL,
product_name VARCHAR(300) NOT NULL,
category VARCHAR(100) NULL,
subcategory VARCHAR(100) NULL,
brand VARCHAR(100) NULL,
unit_cost DECIMAL(10,2) NULL,
list_price DECIMAL(10,2) NULL,
is_active BIT NOT NULL DEFAULT 1,
inserted_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
CREATE TABLE dim.date (
date_key INT NOT NULL,
full_date DATE NOT NULL,
day_of_week TINYINT NOT NULL,
day_name VARCHAR(10) NOT NULL,
week_number TINYINT NOT NULL,
month_number TINYINT NOT NULL,
month_name VARCHAR(10) NOT NULL,
quarter_number TINYINT NOT NULL,
year_number SMALLINT NOT NULL,
is_weekend BIT NOT NULL,
is_holiday BIT NOT NULL DEFAULT 0,
fiscal_period VARCHAR(10) NULL
);
GO
The dim.date table deserves particular attention. Always pre-populate it for the full date range you'll ever need — generate it once, load it, and it becomes a static reference. We'll handle that population shortly.
CREATE TABLE fact.sales (
sales_key BIGINT NOT NULL,
order_id VARCHAR(50) NOT NULL,
order_line SMALLINT NOT NULL,
order_date_key INT NOT NULL,
ship_date_key INT NULL,
customer_key INT NOT NULL,
product_key INT NOT NULL,
store_id VARCHAR(20) NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
unit_discount DECIMAL(10,2) NOT NULL DEFAULT 0,
gross_sales_amount DECIMAL(14,2) NOT NULL,
discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
net_sales_amount DECIMAL(14,2) NOT NULL,
cost_amount DECIMAL(14,2) NULL,
margin_amount DECIMAL(14,2) NULL,
currency_code CHAR(3) NOT NULL DEFAULT 'USD',
source_system VARCHAR(50) NULL,
inserted_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
batch_id VARCHAR(100) NULL
);
GO
Notice the batch_id column. This is a practical addition that many tutorials skip: it lets you trace every row back to the pipeline execution or notebook run that loaded it, which is invaluable for debugging and reprocessing.
Here's where Fabric diverges meaningfully from SQL Server, and this catches people off guard. Let's talk about what works and what doesn't.
Primary keys and unique constraints are supported but are not enforced. You declare them, the engine records them as metadata, but it will happily insert duplicate values without error. The optimizer can use them as hints.
Foreign keys are similarly unenforced. You can declare them for documentation and tooling purposes (Power BI's relationship detection, for example, reads them), but no referential integrity check happens at insert time.
NOT NULL constraints are enforced.
DEFAULT constraints work correctly.
CHECK constraints are parsed but not enforced.
This is by design — enforcing constraints at insert time in a distributed columnar engine would create unacceptable write latency. The contract moves to your ETL process: your pipelines and notebooks are responsible for ensuring data quality before it reaches the warehouse.
-- These declarations are valid and useful for documentation/tooling,
-- but do NOT enforce uniqueness or referential integrity at runtime
ALTER TABLE dim.customer ADD CONSTRAINT pk_customer PRIMARY KEY NONCLUSTERED (customer_key) NOT ENFORCED;
ALTER TABLE dim.product ADD CONSTRAINT pk_product PRIMARY KEY NONCLUSTERED (product_key) NOT ENFORCED;
ALTER TABLE dim.date ADD CONSTRAINT pk_date PRIMARY KEY NONCLUSTERED (date_key) NOT ENFORCED;
ALTER TABLE fact.sales ADD CONSTRAINT pk_sales PRIMARY KEY NONCLUSTERED (sales_key) NOT ENFORCED;
ALTER TABLE fact.sales ADD CONSTRAINT fk_sales_customer FOREIGN KEY (customer_key) REFERENCES dim.customer(customer_key) NOT ENFORCED;
ALTER TABLE fact.sales ADD CONSTRAINT fk_sales_product FOREIGN KEY (product_key) REFERENCES dim.product(product_key) NOT ENFORCED;
ALTER TABLE fact.sales ADD CONSTRAINT fk_sales_date FOREIGN KEY (order_date_key) REFERENCES dim.date(date_key) NOT ENFORCED;
GO
Warning
Because constraints are unenforced, you must build data quality checks into your ingestion layer. A common pattern is to add a validation step in your data pipeline that counts NULLs in NOT NULL columns or checks for duplicate keys before the final INSERT. Catching bad data upstream is far cheaper than debugging incorrect reports downstream.
Here's a complete, production-ready date dimension population script. This is the kind of utility you write once and keep in your team's script library:
DECLARE @start_date DATE = '2020-01-01';
DECLARE @end_date DATE = '2030-12-31';
WITH date_series AS (
SELECT @start_date AS d
UNION ALL
SELECT DATEADD(DAY, 1, d)
FROM date_series
WHERE d < @end_date
)
INSERT INTO dim.date (
date_key, full_date, day_of_week, day_name, week_number,
month_number, month_name, quarter_number, year_number,
is_weekend, is_holiday, fiscal_period
)
SELECT
CAST(FORMAT(d, 'yyyyMMdd') AS INT) AS date_key,
d AS full_date,
DATEPART(WEEKDAY, d) AS day_of_week,
DATENAME(WEEKDAY, d) AS day_name,
DATEPART(WEEK, d) AS week_number,
MONTH(d) AS month_number,
DATENAME(MONTH, d) AS month_name,
DATEPART(QUARTER, d) AS quarter_number,
YEAR(d) AS year_number,
CASE WHEN DATEPART(WEEKDAY, d) IN (1, 7) THEN 1 ELSE 0 END AS is_weekend,
0 AS is_holiday,
'FY' + CAST(YEAR(d) AS VARCHAR) + '-Q' + CAST(DATEPART(QUARTER, d) AS VARCHAR) AS fiscal_period
FROM date_series
OPTION (MAXRECURSION 4000);
GO
The OPTION (MAXRECURSION 4000) is important — without it, the default limit of 100 recursion levels will fire long before you reach your end date.
Views are one of the most underused tools in warehouse design. They let you separate how data is stored from how data is consumed, which gives you enormous flexibility when business logic evolves. When your marketing team redefines "active customer" for the third time this quarter, you update one view instead of refactoring five reports.
CREATE VIEW rpt.v_sales_detail
AS
SELECT
fs.order_id,
fs.order_line,
dd.full_date AS order_date,
dd.month_name,
dd.quarter_number,
dd.year_number,
dd.fiscal_period,
dc.customer_id,
dc.full_name AS customer_name,
dc.customer_segment,
dc.city,
dc.state_province,
dc.country_code,
dp.product_id,
dp.product_name,
dp.category,
dp.subcategory,
dp.brand,
fs.quantity,
fs.unit_price,
fs.unit_discount,
fs.gross_sales_amount,
fs.discount_amount,
fs.net_sales_amount,
fs.cost_amount,
fs.margin_amount,
CASE
WHEN fs.gross_sales_amount > 0
THEN ROUND(fs.margin_amount / fs.gross_sales_amount * 100, 2)
ELSE NULL
END AS margin_pct,
fs.currency_code
FROM
fact.sales fs
INNER JOIN dim.date dd ON fs.order_date_key = dd.date_key
INNER JOIN dim.customer dc ON fs.customer_key = dc.customer_key
INNER JOIN dim.product dp ON fs.product_key = dp.product_key
WHERE
dc.is_current = 1; -- only current SCD2 records
GO
The WHERE dc.is_current = 1 filter is doing something subtle but important: it's hiding the slowly-changing dimension complexity from every downstream consumer. Any report built on this view automatically sees current customer attributes without the analyst needing to know about SCD Type 2 mechanics.
For high-level dashboards, you often want a pre-aggregated view that reduces the number of rows Power BI needs to process:
CREATE VIEW rpt.v_sales_monthly_summary
AS
SELECT
dd.year_number,
dd.month_number,
dd.month_name,
dd.fiscal_period,
dc.customer_segment,
dp.category,
dp.subcategory,
dc.country_code,
COUNT(DISTINCT fs.order_id) AS order_count,
SUM(fs.quantity) AS total_units,
SUM(fs.gross_sales_amount) AS total_gross_sales,
SUM(fs.discount_amount) AS total_discounts,
SUM(fs.net_sales_amount) AS total_net_sales,
SUM(fs.margin_amount) AS total_margin,
CASE
WHEN SUM(fs.gross_sales_amount) > 0
THEN ROUND(SUM(fs.margin_amount) / SUM(fs.gross_sales_amount) * 100, 2)
ELSE NULL
END AS margin_pct
FROM
fact.sales fs
INNER JOIN dim.date dd ON fs.order_date_key = dd.date_key
INNER JOIN dim.customer dc ON fs.customer_key = dc.customer_key
INNER JOIN dim.product dp ON fs.product_key = dp.product_key
WHERE
dc.is_current = 1
GROUP BY
dd.year_number,
dd.month_number,
dd.month_name,
dd.fiscal_period,
dc.customer_segment,
dp.category,
dp.subcategory,
dc.country_code;
GO
Tip
Views in Fabric Warehouse are computed at query time — there's no materialization unless you explicitly load results into a table. For expensive aggregations that Power BI hits frequently, consider a pattern where a pipeline refreshes a summary table nightly and the view reads from that table instead of aggregating the full fact table each time.
Here's a more analytically complex view that demonstrates how to use window functions inside views:
CREATE VIEW rpt.v_customer_lifetime_value
AS
WITH customer_orders AS (
SELECT
dc.customer_key,
dc.customer_id,
dc.full_name,
dc.customer_segment,
dc.country_code,
MIN(dd.full_date) AS first_order_date,
MAX(dd.full_date) AS last_order_date,
COUNT(DISTINCT fs.order_id) AS lifetime_orders,
SUM(fs.net_sales_amount) AS lifetime_revenue,
SUM(fs.margin_amount) AS lifetime_margin,
AVG(fs.net_sales_amount) AS avg_order_value
FROM
fact.sales fs
INNER JOIN dim.date dd ON fs.order_date_key = dd.date_key
INNER JOIN dim.customer dc ON fs.customer_key = dc.customer_key
WHERE
dc.is_current = 1
GROUP BY
dc.customer_key,
dc.customer_id,
dc.full_name,
dc.customer_segment,
dc.country_code
)
SELECT
customer_key,
customer_id,
full_name,
customer_segment,
country_code,
first_order_date,
last_order_date,
DATEDIFF(DAY, first_order_date, last_order_date) AS customer_lifespan_days,
lifetime_orders,
lifetime_revenue,
lifetime_margin,
avg_order_value,
NTILE(4) OVER (ORDER BY lifetime_revenue DESC) AS revenue_quartile,
CASE NTILE(4) OVER (ORDER BY lifetime_revenue DESC)
WHEN 1 THEN 'Platinum'
WHEN 2 THEN 'Gold'
WHEN 3 THEN 'Silver'
WHEN 4 THEN 'Bronze'
END AS customer_tier
FROM
customer_orders;
GO
This view computes customer tiers on the fly using NTILE. The quartile boundaries automatically recalibrate as new customers are added — no manual threshold maintenance required.
This is one of Fabric's most powerful features and the one that surprises people most when they first encounter it. Within a single Fabric workspace, you can query both warehouse tables and lakehouse tables in the same T-SQL statement using three-part naming: [database_name].[schema_name].[table_name].
The lakehouse's SQL Analytics Endpoint acts as a read-only SQL database. Your warehouse can reference it directly, which means you can keep raw and staging data in the lakehouse (where Spark transforms it efficiently) and join it against your warehouse dimension tables without any data movement.
Key insight
Cross-database queries in Fabric work across warehouses and lakehouse SQL Analytics Endpoints within the same workspace. Cross-workspace queries are not supported — if you need data from another workspace, you'll need to use a shortcut or a copy activity to bring it into your current workspace first.
Imagine your medallion architecture has a lakehouse called retail_lakehouse with a silver-layer table silver.erp_transactions that contains raw order lines not yet processed into the warehouse. You want to:
-- Check for transactions in the lakehouse not yet in the warehouse
SELECT
lh.transaction_id,
lh.transaction_date,
lh.customer_ref,
lh.product_ref,
lh.quantity,
lh.amount
FROM
retail_lakehouse.silver.erp_transactions lh
WHERE
lh.transaction_date >= CAST(GETDATE() AS DATE)
AND NOT EXISTS (
SELECT 1
FROM fact.sales fs
WHERE fs.order_id = lh.transaction_id
)
ORDER BY
lh.transaction_date DESC;
Notice the syntax: retail_lakehouse.silver.erp_transactions — the lakehouse name, the schema (in the SQL Analytics Endpoint, Delta tables appear under dbo by default unless you organize them otherwise), and the table name. No linked servers, no external data sources, no OPENROWSET — just a three-part name.
CREATE VIEW raw.v_staged_transactions
AS
SELECT
lh.transaction_id AS source_transaction_id,
lh.transaction_date,
CAST(FORMAT(lh.transaction_date, 'yyyyMMdd') AS INT) AS date_key,
dc.customer_key,
dp.product_key,
lh.quantity,
lh.unit_price,
lh.discount_pct,
ROUND(lh.quantity * lh.unit_price, 2) AS gross_amount,
ROUND(lh.quantity * lh.unit_price * (lh.discount_pct / 100), 2) AS discount_amount,
ROUND(lh.quantity * lh.unit_price * (1 - lh.discount_pct / 100), 2) AS net_amount,
lh.currency_code,
lh.source_system,
CASE
WHEN dc.customer_key IS NULL THEN 'UNKNOWN_CUSTOMER'
WHEN dp.product_key IS NULL THEN 'UNKNOWN_PRODUCT'
ELSE 'VALID'
END AS validation_status
FROM
retail_lakehouse.silver.erp_transactions lh
LEFT JOIN dim.customer dc
ON dc.customer_id = lh.customer_ref
AND dc.is_current = 1
LEFT JOIN dim.product dp
ON dp.product_id = lh.product_ref
AND dp.is_active = 1;
GO
This view is doing something important: it's performing the surrogate key lookup against your warehouse dimensions at query time. When your pipeline calls this view and filters for validation_status = 'VALID', it gets only rows where dimension members exist. Rows with UNKNOWN_CUSTOMER or UNKNOWN_PRODUCT can be routed to an error log table for investigation.
Once you have the staging view, inserting into the fact table is clean:
INSERT INTO fact.sales (
sales_key,
order_id,
order_line,
order_date_key,
customer_key,
product_key,
quantity,
unit_price,
unit_discount,
gross_sales_amount,
discount_amount,
net_sales_amount,
currency_code,
source_system,
batch_id
)
SELECT
NEXT VALUE FOR dbo.sq_sales_key AS sales_key, -- if using a sequence
source_transaction_id,
1 AS order_line, -- simplified; real systems have line items
date_key,
customer_key,
product_key,
quantity,
unit_price,
0 AS unit_discount,
gross_amount,
discount_amount,
net_amount,
currency_code,
source_system,
'BATCH_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') AS batch_id
FROM
raw.v_staged_transactions
WHERE
validation_status = 'VALID'
AND transaction_date >= @load_date;
GO
Note
Fabric Warehouse supports sequences (CREATE SEQUENCE) for generating surrogate keys, which is useful when your ETL runs in a single serial process. For parallel loads, consider a GUID or a hash-based key instead, since multiple concurrent sessions competing for a sequence can create bottlenecks.
Beyond the basic three-part name, here are patterns you'll actually need in production.
-- Find the high-water mark, then pull only new lakehouse records
DECLARE @max_loaded_date DATE;
SELECT @max_loaded_date = MAX(dd.full_date)
FROM fact.sales fs
INNER JOIN dim.date dd ON fs.order_date_key = dd.date_key;
SELECT COUNT(*) AS new_rows_available
FROM retail_lakehouse.silver.erp_transactions
WHERE transaction_date > @max_loaded_date;
Before loading, run a reconciliation query to make sure row counts align with what the source system reported:
SELECT
'Lakehouse (source)' AS layer,
COUNT(*) AS row_count,
SUM(amount) AS total_amount
FROM retail_lakehouse.silver.erp_transactions
WHERE CAST(transaction_date AS DATE) = '2024-11-15'
UNION ALL
SELECT
'Warehouse (loaded)' AS layer,
COUNT(*) AS row_count,
SUM(net_sales_amount) AS total_amount
FROM fact.sales fs
INNER JOIN dim.date dd ON fs.order_date_key = dd.date_key
WHERE dd.full_date = '2024-11-15';
Running this after every batch load and alerting on discrepancies will catch data issues before analysts notice them in reports.
If your organization has a separate warehouse for HR or finance data:
SELECT
s.customer_segment,
s.year_number,
s.total_net_sales,
h.headcount AS sales_headcount
FROM
rpt.v_sales_monthly_summary s
LEFT JOIN finance_dw.hr.v_headcount_monthly h
ON h.department = 'Sales'
AND h.year_number = s.year_number
AND h.month_number = s.month_number
ORDER BY
s.year_number, s.month_number, s.customer_segment;
This works exactly like joining within a single database — the query optimizer handles the distributed execution transparently.
The Fabric Warehouse is a distributed columnar engine, which means performance intuitions from row-store SQL Server don't always transfer directly.
Selective predicates on date columns. The engine stores data in sorted Delta Parquet files and maintains statistics. Queries filtered on order_date_key or full_date can skip large portions of data using Parquet row group elimination. Always filter on your date key when possible.
Column pruning. SELECT * forces the engine to read all columns from Parquet files. Selecting only what you need — especially in views that other views build on — can dramatically reduce I/O.
Predicate pushdown in cross-database queries. When you join a lakehouse table in a cross-database query, Fabric attempts to push the WHERE clause predicates down to the lakehouse scan. This means filtering on the lakehouse table's columns (rather than post-join) keeps the data transfer between systems minimal.
-- Better: filter pushed to lakehouse scan
SELECT ...
FROM retail_lakehouse.silver.erp_transactions lh
INNER JOIN dim.customer dc ON dc.customer_id = lh.customer_ref
WHERE lh.transaction_date >= '2024-01-01' -- this gets pushed down
AND dc.country_code = 'US';
-- Worse: filter only on joined result (harder to push down)
SELECT ...
FROM retail_lakehouse.silver.erp_transactions lh
INNER JOIN dim.customer dc ON dc.customer_id = lh.customer_ref
WHERE dc.country_code = 'US'
AND YEAR(lh.transaction_date) = 2024; -- function wrapping prevents pushdown
Scalar subqueries in SELECT lists. A correlated subquery in the SELECT clause executes once per row. At millions of rows, this is painful. Move the logic into a JOIN or CTE.
-- Anti-pattern: scalar subquery executes millions of times
SELECT
fs.order_id,
(SELECT dp.product_name FROM dim.product dp WHERE dp.product_key = fs.product_key) AS product_name
FROM fact.sales fs;
-- Better: join once
SELECT
fs.order_id,
dp.product_name
FROM fact.sales fs
INNER JOIN dim.product dp ON dp.product_key = fs.product_key;
Wide intermediate results in views on views. Building a view on top of a view on top of a view creates deeply nested query plans. Keep view nesting to two levels maximum, and materialize into a table if the chain gets deeper.
Tip
Fabric Warehouse doesn't yet support indexes or partitioning in the traditional SQL Server sense. Your primary tools for performance are table design (narrow fact tables, appropriate data types), query structure (selective predicates, avoiding functions on join keys), and caching in your reporting layer. Direct Lake mode in Power BI, covered in the Direct Lake Mode in Power BI article, can also reduce query pressure on the warehouse significantly.
Work through these tasks in sequence. Each builds on the previous.
Task 1: Create the schema and tables
In your sales_dw warehouse, create the four schemas (raw, dim, fact, rpt) and all three dimension tables plus the fact table from the DDL above. Declare primary keys and foreign keys using NOT ENFORCED.
Task 2: Populate the date dimension
Run the recursive CTE date population script. After it completes, verify with:
SELECT COUNT(*) AS total_days, MIN(full_date) AS earliest, MAX(full_date) AS latest
FROM dim.date;
You should see 4018 days for the 2020–2030 range.
Task 3: Load sample dimension data
Insert at least 10 customers, 20 products, and then insert 100 sample fact rows with manually constructed values. Make sure some rows have matching dimension keys and some deliberately don't (to test your validation view later).
Task 4: Create all four views
Create rpt.v_sales_detail, rpt.v_sales_monthly_summary, rpt.v_customer_lifetime_value, and raw.v_staged_transactions (adapting the lakehouse reference to match your actual lakehouse name, or pointing it at a second warehouse table if you don't have a lakehouse).
Task 5: Write a cross-database reconciliation query
If you have a lakehouse in the workspace, write a query that joins a lakehouse table with dim.customer using three-part naming. If you don't, create a second warehouse and query across both warehouses.
Task 6: Performance comparison
Run rpt.v_sales_detail with no filter and note the execution time. Then add a date filter (WHERE dd.year_number = 2024) and compare. With small sample data the difference will be minimal, but understand why it matters at scale.
"I declared a PRIMARY KEY but duplicate rows got inserted."
Expected behavior. Constraints are NOT ENFORCED in Fabric Warehouse. Add duplicate detection to your ETL logic. A common guard is:
INSERT INTO dim.customer (...)
SELECT src.*
FROM staging.customer_incoming src
WHERE NOT EXISTS (
SELECT 1 FROM dim.customer tgt WHERE tgt.customer_key = src.customer_key
);
"My cross-database query says the object doesn't exist."
The most common cause is a workspace name mismatch. The database name in a three-part query must exactly match the Fabric item name, including capitalization and spaces. If your lakehouse is named "Retail Lakehouse" with a space, quote it: [Retail Lakehouse].dbo.table_name. Also verify both items are in the same workspace — cross-workspace cross-database queries aren't supported.
"My view is slow even though the underlying table is small."
Check whether the view has deep nesting or correlated subqueries. Use EXPLAIN (or check the query plan in the Fabric monitoring hub) to see what's actually being executed. Sometimes the simplest fix is materializing the intermediate result into a table and refreshing it on a schedule.
"My date dimension population failed with 'Maximum recursion exceeded.'"
You need OPTION (MAXRECURSION 4000) at the end of the INSERT...SELECT statement. Without it, SQL Server's default limit of 100 will stop the recursion after 100 days.
"Inserting into fact.sales works, but the row count in SELECT doesn't match." This occasionally happens due to eventual consistency in how Fabric caches metadata. Wait a few seconds and recount. If counts remain inconsistent, check whether another session is running a concurrent DELETE or if the batch_id matches what you inserted.
"Power BI can't detect relationships automatically from my warehouse."
Make sure you've declared the foreign key constraints with NOT ENFORCED syntax. Power BI's auto-detect reads the catalog metadata for foreign key declarations. Without them, it's guessing based on column names.
You've built a production-ready dimensional model in a Fabric Warehouse: schemas with clear separation of concerns, fact and dimension tables with appropriate data types, views that expose clean business logic to consumers, and cross-database queries that bridge your warehouse and lakehouse without data movement.
The key ideas to carry forward:
From here, consider exploring how Dataflow Gen2 can populate your dimension tables with automated, schedule-driven loads, or how to wrap your warehouse loads in Fabric Data Pipelines with proper error handling and retry logic. When your model is ready for reporting, Direct Lake mode in Power BI will let you build dashboards that read directly from your warehouse's Delta tables at in-memory speed — no import schedule, no DirectQuery latency.