Microsoft Fabric reached general availability at Ignite last week. I've been exploring it in preview since the summer, and the first question most SQL Server people ask is: "Which item do I actually use?" When you open a Fabric workspace you see Lakehouse, Warehouse, SQL analytics endpoint, KQL Database, Semantic model and more, and it isn't obvious where your T-SQL skills fit.
This post is the map I wish I'd had on day one. I'll use delegated authority bordereaux as the example, since that's the world I work in. Coverholders send monthly risk, premium and claims bordereaux under binding authority contracts, and a typical model treats them as facts against brokers, coverholders and contract sections.
First, OneLake
Everything in Fabric stores its data in OneLake, a single logical data lake per tenant, built on ADLS Gen2. The important point is that tables are stored as Delta Lake (Parquet) files, whichever engine wrote them. A table written by Spark can be read by the SQL engine, by Power BI in Direct Lake mode, and by another workspace through a shortcut, all without copying it.
If you take one idea from this post, take this one: in Fabric, the storage format is shared and the compute engines are interchangeable. That's very different from SQL Server, where the engine owns its files.
Lakehouse
A Lakehouse is a container with two areas:
- Files – raw files of any type. This is where extracts from the bordereaux platform land.
- Tables – managed Delta tables.
You write to a Lakehouse mainly with Spark (notebooks, Spark job definitions), Dataflows Gen2, or pipelines. Every Lakehouse automatically gets a SQL analytics endpoint, so you can query its tables with T-SQL straight away.
1: # In a Fabric notebook attached to the bronze Lakehouse 2: from pyspark.sql import functions as F 3: 4: df = (spark.read.option("header", True) 5: .csv("Files/landing/premium_bordereaux/2023/10/*.csv") 6: .withColumn("source_file", F.input_file_name())) 7: 8: df.write.mode("append").format("delta").saveAsTable("premium_bordereau_raw")
SQL analytics endpoint
This is the part that confuses people. The SQL analytics endpoint is read-only for Lakehouse tables. You can SELECT, create views, create functions and set up security, but you can't INSERT, UPDATE or DELETE rows in Lakehouse tables through it. Data changes go through Spark (or another writer).
You can connect to it from SSMS or Azure Data Studio with the connection string from the item's settings, which helps a lot with adoption: underwriting MI analysts and the DA oversight team keep their tools.
Warehouse
The Fabric Warehouse is the one that will feel most familiar. It's a full T-SQL engine with DML support and multi-table transactions, and its data is also stored as Delta in OneLake. You load it with COPY INTO, INSERT...SELECT, CREATE TABLE AS SELECT, pipelines, or stored procedures.
1: CREATE TABLE dbo.dim_contract_section 2: ( 3: contract_section_key INT NOT NULL, 4: umr VARCHAR(30) NOT NULL, -- Unique Market Reference 5: section_no VARCHAR(10) NOT NULL, 6: coverholder_key INT NOT NULL, 7: class_of_business VARCHAR(100) NULL, 8: year_of_account SMALLINT NOT NULL, 9: gpi_limit_gbp DECIMAL(18,2) NULL, -- Gross Premium Income limit 10: commission_pct DECIMAL(9,4) NULL, 11: inception_date DATE NOT NULL, 12: expiry_date DATE NOT NULL 13: ); 14: 15: COPY INTO dbo.stg_broker 16: FROM 'https://mystorage.blob.core.windows.net/reference/broker/*.parquet' 17: WITH (FILE_TYPE = 'PARQUET');
It isn't SQL Server, though. As of GA, some surface area is missing or different. For example, MERGE and IDENTITY columns aren't supported yet, data types are a subset (no NVARCHAR storage, for instance, and DATETIME2 is limited to precision 6), and there are no indexes to tune, because the engine handles that. Check the T-SQL surface area page before you port an existing codebase.
A simple decision guide
| If your team... | Start with |
|---|---|
| Is mostly SQL developers building a star schema | Warehouse |
| Is mostly data engineers using Python/Spark | Lakehouse |
| Receives messy bordereaux files in many layouts | Lakehouse (Files), cleaned with Spark |
| Needs multi-table transactions in T-SQL | Warehouse |
| Wants analysts to query Spark output with SQL | Lakehouse + SQL analytics endpoint |
A layout that works well is a mix: a medallion layout where bronze and silver are Lakehouses loaded by notebooks (Spark handles the cleansing and conformance work well), and gold is a Warehouse holding the star schema that a SQL team owns. Because both are Delta in OneLake, the Warehouse can read Lakehouse tables in the same workspace with three-part names:
1: INSERT INTO dbo.fact_premium_transaction 2: (contract_section_key, coverholder_key, broker_key, bordereau_month, 3: transaction_type, gross_premium_gbp, commission_gbp, brokerage_gbp) 4: SELECT cs.contract_section_key, 5: cs.coverholder_key, 6: b.broker_key, 7: p.bordereau_month, 8: p.transaction_type, -- NEW, RENEWAL, MTA, CANCELLATION 9: p.gross_premium_gbp, 10: p.gross_premium_gbp * cs.commission_pct / 100, 11: p.brokerage_gbp 12: FROM silver_lakehouse.dbo.premium_bordereau AS p 13: JOIN dbo.dim_contract_section AS cs 14: ON cs.umr = p.umr AND cs.section_no = p.section_no 15: JOIN dbo.dim_broker AS b 16: ON b.broker_code = p.broker_code;
Cross-database queries without linked servers. I enjoyed that more than I'd like to admit.
Where Power BI fits
Every Lakehouse and Warehouse gets a default semantic model, and the new Direct Lake mode lets Power BI read the Delta files directly, without import refreshes and without DirectQuery's speed penalty. For a DA oversight dashboard showing GWP against GPI limits by coverholder, that means the numbers are current as soon as the bordereaux are loaded. It deserves its own post.
Capacity and cost
Fabric is billed by capacity (F SKUs, or a Power BI Premium P SKU), and all workloads share the same capacity units. A runaway Spark notebook can slow your Power BI reports, so watch the Fabric Capacity Metrics app from the start. Bordereaux processing tends to bunch up in the first week of each month, so that's when capacity gets tested. Smoothing and bursting help, but they won't save you from a cross join.
My advice
Don't try to use every item. Pick Lakehouse or Warehouse based on your team's skills, learn how OneLake and Delta work underneath, and add the rest as you need it. Next I'll look at OneLake shortcuts, which let you query data sitting in Amazon S3 from Fabric without moving it.
