Microsoft Fabric Is GA: A SQL Server Developer's Map to Lakehouse, Warehouse and SQL Endpoint

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.

Snowflake Zero-Copy Cloning and Time Travel: Dev Environments in Seconds

When I worked mostly with SQL Server, refreshing a dev or test environment from production meant a backup, a file copy, a restore, and usually a long wait. On an insurance data warehouse with years of bordereaux history behind it, that was an overnight job. The first time I ran CREATE DATABASE ... CLONE in Snowflake and it came back in a few seconds, I ran it again because I assumed something had gone wrong.

This post covers two Snowflake features that changed how I set up test environments: Zero-Copy Cloning and Time Travel. They work together, and once you understand how they work you'll use them every week.

How zero-copy cloning works

Snowflake stores table data in immutable micro-partitions. A clone doesn't copy those micro-partitions. It creates new metadata that points at the same ones. So the clone is instant and, at first, costs nothing extra in storage.

Once you change data in either the source or the clone, Snowflake writes new micro-partitions for the changed rows, and only those new partitions add storage. The two objects drift apart over time, but you only pay for the difference.

1: -- Clone the whole DA warehouse for a developer
2: CREATE DATABASE da_dev CLONE da_prod;
3: 
4: -- Clone just the bordereaux staging schema for UAT
5: CREATE SCHEMA da_prod.bdx_staging_uat CLONE da_prod.bdx_staging;
6: 
7: -- Clone a single fact table before a risky change
8: CREATE TABLE da_prod.dw.fact_premium_transaction_bkp
9:   CLONE da_prod.dw.fact_premium_transaction;

That last one has become a habit. Before I run a big UPDATE or DELETE in production, such as reprocessing a corrected delivery from the bordereaux feed, I clone the table first. It takes a second and costs almost nothing, and it gives me a rollback point that doesn't depend on anyone's restore process.

Time Travel: querying the past

Time Travel lets you query a table as it was at an earlier point, within the retention period. On Standard edition the retention is 1 day; on Enterprise and above you can set it up to 90 days per object.

 1: -- What did the premium fact look like an hour ago?
 2: SELECT * FROM dw.fact_premium_transaction AT(OFFSET => -3600);
 3: 
 4: -- As of month-end close
 5: SELECT coverholder_key, SUM(gross_premium_gbp) AS gwp
 6: FROM   dw.fact_premium_transaction
 7:        AT(TIMESTAMP => '2023-10-02 18:00:00'::TIMESTAMP_LTZ)
 8: WHERE  bordereau_month = '2023-09-01'
 9: GROUP  BY coverholder_key;
10: 
11: -- Just before a specific statement ran (great after a bad UPDATE)
12: SELECT * FROM dw.dim_contract_section
13: BEFORE(STATEMENT => '01af3c2e-0001-2a3b-0000-0004b2c1d0e5');

The month-end query is the one auditors and the finance team love. "What did we report for September before the late bordereaux arrived?" becomes a single query instead of a hunt through archived extracts. You can find the query ID of a bad statement in Query History in Snowsight, or with INFORMATION_SCHEMA.QUERY_HISTORY().

Combining the two: clone the past

This is where the two features work best together. You can clone an object as it was at a point in time:

1: -- Someone updated commission_pct on every contract section at 10:15
2: -- (the WHERE clause was missing). Rebuild the dimension as it was at 10:14.
3: CREATE OR REPLACE TABLE dw.dim_contract_section_restored
4:   CLONE dw.dim_contract_section
5:   AT(TIMESTAMP => '2023-10-11 10:14:00'::TIMESTAMP_LTZ);
6: 
7: -- Check it, then swap it in
8: ALTER TABLE dw.dim_contract_section SWAP WITH dw.dim_contract_section_restored;

SWAP WITH is an atomic rename of the two tables, so users never see a half-restored state. Once you're happy, drop the bad copy.

And if someone drops a table entirely:

1: UNDROP TABLE dw.dim_coverholder;
2: UNDROP SCHEMA bdx_staging;
3: UNDROP DATABASE da_dev;

Setting retention deliberately

Retention isn't free: changed and deleted micro-partitions are kept for the whole retention period, then for 7 more days of Fail-safe (which only Snowflake Support can recover from). Bordereaux staging tables get reloaded every month, so a 90-day retention on them mostly stores data you'll never look at again.

My usual approach:

• Core facts and dimensions (risk, premium and claim transactions, coverholder, broker, contract section): 30 days, so a month-end can always be reproduced until the next one closes.
• Bordereaux landing and staging tables that get truncated and reloaded: TRANSIENT tables with 0 or 1 day. Transient tables have no Fail-safe, which saves storage.
• Dev databases made from clones: 1 day. If you lose dev data, clone production again.

1: ALTER TABLE da_prod.dw.fact_claim_transaction SET DATA_RETENTION_TIME_IN_DAYS = 30;
2: 
3: CREATE TRANSIENT TABLE bdx_staging.risk_bordereau_landing (...)
4:   DATA_RETENTION_TIME_IN_DAYS = 1;

Gotchas I've run into

• Grants don't follow a cloned database the way you expect. Child objects keep their privileges, but the clone itself is owned by whoever created it. For a single table, add COPY GRANTS if you want the source table's grants copied.
• Named internal stages aren't cloned. If feed files sit in an internal stage, the cloned database won't have them.
• Pipes are only cloned when they point at an external stage, and cloned pipes start paused. Check them before you assume dev is ingesting bordereaux.
• Long-lived clones stop being cheap. If the premium fact gets rebuilt each month, a 6-month-old clone ends up holding its own full copy of the data. Re-clone regularly.
• Sequences in a cloned database carry on from their current value, so surrogate keys in dev and prod will overlap over time. Never merge data back from a clone using sequence-generated keys.

A dev-refresh script I use

 1: -- Run as a role that owns the dev database
 2: CREATE OR REPLACE DATABASE da_dev CLONE da_prod;
 3: 
 4: -- Mask personal data in dev: insured and claimant details
 5: UPDATE da_dev.dw.dim_insured
 6: SET    insured_name = SHA2(insured_name),
 7:        insured_postcode = LEFT(insured_postcode, 2);
 8: 
 9: UPDATE da_dev.dw.fact_claim_transaction
10: SET    claimant_name = NULL;
11: 
12: GRANT USAGE ON DATABASE da_dev TO ROLE developer;
13: GRANT USAGE ON ALL SCHEMAS IN DATABASE da_dev TO ROLE developer;
14: GRANT SELECT ON ALL TABLES IN DATABASE da_dev TO ROLE developer;

Wrap that in a stored procedure and a Task, and every developer gets fresh production-shaped bordereaux data each Monday morning without anyone opening a ticket. Personal data about insureds and claimants never reaches dev in readable form.

Wrapping up

If you're coming from on-premises SQL Server, cloning and Time Travel are the features that make Snowflake feel different day to day. Environment refreshes, safe reprocessing of bordereaux, reproducing a month-end and recovering from accidental changes all get much easier. Just set retention on purpose so the storage bill doesn't surprise you.