Gowtham Potureddi

Posted on Jul 29

Microsoft Fabric & OneLake Deep Dive for Data Engineers: Lakehouse + Warehouse + Real-Time in One Portal

#python #sql #interview #dataengineering

microsoft fabric is the pick-one architectural decision that finally collapses Microsoft's decade-long sprawl of data products — Data Factory, Synapse Analytics, Power BI, Purview, Azure Data Explorer, Azure ML — into a single SaaS portal with one storage layer, one billing surface, one identity model, and four native compute engines sharing the same Delta tables. Every ETL job your organisation runs against ADLS Gen2, every warehouse dashboard you ship in Power BI, every real-time telemetry stream you land in Data Explorer, and every notebook your data scientists spin up in Synapse now has a single unified home called Fabric — and the engineering trade-off does not live in "should we adopt Fabric" for Microsoft-heavy shops, but in which of the four workloads owns which slice of the pipeline and how the F-SKU capacity meter, Direct Lake mode, and OneLake shortcuts change the design decisions you'd make in Databricks or Snowflake.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through OneLake and why it's not just another ADLS wrapper", or "when does Direct Lake mode beat Import for Power BI?", or "explain F-SKU capacity smoothing and how throttling actually kicks in." It walks through the Fabric architecture — the OneLake tenant-wide storage layer (Delta Lake by default, shortcuts that bridge external ADLS/S3/GCS without copying, V-Order write optimisation, domains + workspaces + items for governance) — the four native workloads (Lakehouse with Spark, Warehouse with T-SQL, Real-Time Intelligence with KQL Database and Eventstream, Data Factory with pipelines and Dataflows Gen2), the F-SKU capacity model (F2 to F2048, smoothing → throttling → auto-pause, per-second billing, Copilot in Fabric charged from the same pool), the Direct Lake semantic-model story for Power BI, and the four-way decision matrix that separates a Fabric win from a Databricks / Snowflake / legacy-Synapse win. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse system design against the design practice library →, and sharpen the streaming axis with the streaming practice library →.

On this page

Why Microsoft Fabric matters in 2026
OneLake — the "OneDrive for data"
Fabric workloads — Lakehouse, Warehouse, Real-Time Intelligence
Compute + pricing — Capacities
Fabric vs Databricks vs Snowflake vs Synapse + interview signals
Cheat sheet — Fabric recipes
Frequently asked questions
Practice on PipeCode

1. Why Microsoft Fabric matters in 2026

One portal, one storage layer, one bill — the SaaS collapse of Microsoft's data estate that changes every architecture decision

The one-sentence invariant: Microsoft Fabric is the single SaaS portal that unifies Data Factory, Synapse (Warehouse + Spark + Real-Time), Power BI, and Purview onto one storage layer called OneLake, one billing surface called F-SKU capacity, and one identity model called Microsoft Entra — so the pattern you pick in year one binds every downstream dashboard, every ETL pipeline, every ML notebook, and every real-time feed to a shared metadata catalog whose semantics you cannot escape without a full re-platform. The pattern you commit to in month one becomes the pattern you fight to migrate away from in year three, because every workload, dashboard, and reconciliation job hard-codes assumptions about the shape of the OneLake namespace, the workload split, the capacity SKU sizing, and the Direct Lake vs Import trade-off — decisions that look reversible on day one and turn out to be load-bearing by year two.

The four axes interviewers actually probe.

Microsoft-shop reality. Fabric wins overwhelmingly when the organisation already runs Microsoft 365, Azure AD (now Entra), Power BI premium, and has SQL-heavy analysts. Interviewers open with this because it separates architects who understand vendor gravity from those who evaluate on features in isolation. If the shop is Google Workspace + AWS + Looker, Fabric is almost never the right answer.
Workload boundaries. Fabric ships four native compute engines on top of one OneLake storage — Lakehouse (Spark + Delta), Warehouse (T-SQL over Delta with a data-warehouse UX), Real-Time Intelligence (Eventstream + KQL Database), Data Factory (pipelines + Copy activity + Dataflows Gen2). Naming which workload owns which slice of the pipeline — and why that boundary is drawn where it is — is the load-bearing senior-signal question.
F-SKU capacity math. Capacity is billed in Capacity Units (CU) via F-SKU meters (F2, F4, F8, F16, F32, F64, F128, F256, F512, F1024, F2048). One capacity backs every workload in the associated workspaces; overage triggers smoothing (up to 24 h) then throttling then rejection. Sizing a capacity wrong is the single most common Fabric cost mistake.
Direct Lake vs Import vs DirectQuery. Direct Lake is Fabric's Power BI mode that queries Delta files in OneLake directly, without an Import copy and without a DirectQuery round-trip. It's the differentiator that most Databricks / Snowflake stacks cannot match. Interviewers probe this because it changes the semantic-model design, the refresh strategy, and the capacity sizing all at once.

The 2026 reality — Fabric hit GA May 2023, matured through 2024-2026 into Microsoft's default data platform.

Fabric — GA May 2023, initial workloads (Lakehouse, Warehouse, Power BI, Data Factory, Data Science, Real-Time Analytics) rolled out through 2023-2024. 2025 added Real-Time Intelligence (superseding Real-Time Analytics), workload identities, mirroring from Snowflake / Databricks / Cosmos DB / SQL, and Copilot in every workload. 2026 focus is on cost governance (capacity metrics app), OneLake shortcut transformations, and open Iceberg support alongside native Delta.
Databricks — the Delta origin, cross-cloud (AWS, Azure, GCP), Unity Catalog governance, MLflow-native. Wins for ML-heavy workloads and multi-cloud stacks. Loses to Fabric when Power BI + Microsoft 365 are the analyst UX.
Snowflake — pure SQL warehouse, Cortex AI, cross-cloud, Snowpark for Python. Wins for warehouse-first workloads with heavy semi-structured JSON and cross-cloud governance. Loses to Fabric on Direct Lake and native Real-Time Intelligence.
Azure Synapse Analytics — the predecessor to Fabric; still exists as a distinct product for dedicated-SQL-pool workloads, but Microsoft's clear roadmap is that Synapse Serverless / Spark / Data Explorer functionality is subsumed by Fabric. Greenfield Synapse deployments are increasingly rare; migration to Fabric is the norm for new work.

What interviewers listen for.

Do you name all four Fabric workloads without prompting — Lakehouse, Warehouse, Real-Time Intelligence, Data Factory — plus Power BI and Data Science? — senior signal.
Do you say "OneLake is the storage layer, not another lake" in the first sentence when OneLake comes up? — required answer.
Do you name F-SKU capacity smoothing + throttling as the cost-governance model, not just "pay for what you use"? — senior signal.
Do you name Direct Lake mode as the Fabric-only Power BI differentiator, not "just DirectQuery"? — senior signal.
Do you describe Fabric as "one storage + four engines + one capacity + one identity" rather than as "Microsoft's rebrand of Synapse"? — required answer.

Worked example — the four-axis Fabric comparison table

Detailed explanation. The single most useful artifact for a Fabric interview is a memorised 4×4 comparison table across Fabric, Databricks, Snowflake, and legacy Synapse. Every senior data-platform discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical Microsoft-heavy retail organisation that currently runs Synapse dedicated pools + Power BI Premium and is evaluating a re-platform.

Current stack. Synapse dedicated SQL pool (3-year commitment), Power BI Premium P2, Data Factory pipelines, ADLS Gen2 landing zones, Azure DevOps for orchestration.
Downstream. ~200 Power BI reports, ~50 analysts on T-SQL, ~10 data engineers on Spark for ML feature engineering.
Constraints. Azure-only, Microsoft 365 shop, Entra IDs, SOC 2 audit obligation, ~$400k/year data-platform budget.
Requirements. Sub-second Power BI report latency for exec dashboards, weekly ML feature refresh, real-time stock-feed ingestion (~10k events/sec), one governance surface auditors can point at.

Question. Build the four-way comparison for the retail organisation and pick the platform each requirement drives you toward.

Input.

Platform	Storage layer	Workloads	Governance	Best fit
Microsoft Fabric	OneLake (Delta)	Lakehouse + Warehouse + RTI + DF	Purview + workspace RBAC	Microsoft-shop + Power BI + Direct Lake
Databricks	Delta on cloud storage	Spark + SQL + ML + Streaming	Unity Catalog	Cross-cloud + ML-heavy + Delta origin
Snowflake	Native FDN + Iceberg	SQL warehouse + Snowpark	Snowflake RBAC + Horizon	Pure SQL warehouse + cross-cloud
Azure Synapse	ADLS Gen2	Dedicated SQL + Serverless + Spark	Purview	Legacy; subsumed by Fabric

Code.

# Fabric workspace config — greenfield retail workspace
workspace:
  name: retail-analytics-prod
  capacity: F64                    # 128 CU; ~$8k/month at PAYG, ~$5.5k reserved
  identity:
    tenant: retail.onmicrosoft.com
    workspace_admins:
      - group:data-platform-admins
    workspace_members:
      - group:analytics-engineers
      - group:data-scientists
  items:
    lakehouse:
      - name: bronze_landing
      - name: silver_conformed
      - name: gold_marts
    warehouse:
      - name: dw_finance
    real_time:
      - eventstream: stock-ticks
      - kql_database: telemetry
    data_factory:
      - pipeline: ingest_pos_daily
  onelake:
    domain: retail
    sensitivity_default: internal
    shortcuts:
      - source: adls://raw@retaillake.dfs.core.windows.net/pos/
        target: /bronze_landing/Files/pos_shortcut

Step-by-step explanation.

The comparison table forces the four axes into a single view: storage layer, native workloads, governance, and best fit. Fabric wins on Microsoft-shop fit because the identity, licensing, and Power BI story compound; Databricks wins on ML depth and cross-cloud reach; Snowflake wins on pure warehouse UX and Iceberg-native storage; Synapse is the legacy option that Fabric is actively replacing.
For the retail scenario, the Microsoft-shop reality collapses the decision — Entra IDs, Power BI Premium already paid for, T-SQL analysts, Azure-only footprint. Fabric's F64 capacity ($8k/month PAYG, ~$5.5k reserved) collapses the Synapse dedicated pool + Power BI Premium bills into one meter.
Lakehouse handles the Spark ML feature engineering (10 DEs) with notebooks over Delta. Warehouse handles the T-SQL analyst layer (50 analysts) with a familiar SQL Server-shaped UX over Delta. Real-Time Intelligence handles the stock-tick stream via Eventstream + KQL Database. Data Factory pipelines handle the POS batch ingest. Power BI reports run Direct Lake against the gold_marts Lakehouse — no Import refresh, no DirectQuery round-trip.
Every workload writes to OneLake — one namespace, one governance surface (Purview), one audit trail. The auditor's question "who read this table when?" has one answer instead of four.
Databricks would have won if the shop were multi-cloud or ML-first. Snowflake would have won if the workload were 90% SQL warehouse with heavy JSON. Synapse would have won if there were an existing 3-year dedicated-pool commitment that survives migration; even then, greenfield stays in Fabric.

Output.

Requirement	Chosen platform	Why
Power BI executive dashboards (sub-second)	Fabric	Direct Lake mode — no import refresh, no DirectQuery hop
ML feature engineering (weekly)	Fabric Lakehouse	Spark notebooks over Delta; Fabric MLflow-compatible
Stock-tick ingestion (10k events/sec)	Fabric RTI	Eventstream + KQL Database; sub-second query
T-SQL analyst layer	Fabric Warehouse	T-SQL over Delta; familiar SQL Server UX
Governance surface for SOC 2	Fabric + Purview	one workspace RBAC + Purview lineage across all workloads

Rule of thumb. Never pick Fabric because it's Microsoft. Pick it when the (Microsoft-shop reality × workload breadth × capacity math × Direct Lake need) product is high. Write the comparison table on a whiteboard first; the platform falls out of the constraints.

Worked example — what interviewers actually probe on Fabric

Detailed explanation. The senior data-engineering Fabric interview has a predictable structure: the interviewer opens with an ambiguous question ("walk me through Fabric"), then progressively narrows to test whether you understand OneLake, the workload split, capacity throttling, and Direct Lake. The candidates who name the pattern in sentence one score highest; the candidates who describe "Microsoft's Databricks competitor" score lowest. Walk through the interview grading rubric.

Ambiguous opener. "What's Microsoft Fabric?" — invites you to name the four workloads + OneLake + capacity in one sentence.
Follow-up 1. "What is OneLake and how does it differ from ADLS Gen2?" — probes storage understanding.
Follow-up 2. "Explain Direct Lake mode." — probes Power BI + Delta integration.
Follow-up 3. "How does F-SKU capacity throttling actually work?" — probes cost governance.
Follow-up 4. "When would you pick Databricks over Fabric?" — probes decision-making across peers.

Question. Draft a 5-minute senior Fabric answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Platform framed	"Microsoft's Databricks competitor"	"unified SaaS portal — one OneLake + four workloads + one capacity"
OneLake described	"Microsoft's data lake"	"tenant-wide logical layer on ADLS Gen2; Delta by default; shortcuts to external clouds"
Direct Lake described	"just DirectQuery for Delta"	"in-memory column loading straight from Delta files; no Import copy"
Capacity throttling	"you pay more"	"smoothing up to 24h → interactive throttling → rejection; per-second CU billing"
When Databricks wins	"for Python"	"multi-cloud + ML-heavy + Unity Catalog governance across clouds"

Code.

Senior Microsoft Fabric answer template (5 minutes)
====================================================

Minute 1 — frame the platform up front
  "Microsoft Fabric is a unified SaaS portal that ships one tenant-wide storage layer
   (OneLake, Delta by default, built on ADLS Gen2), four native compute workloads
   (Lakehouse with Spark, Warehouse with T-SQL, Real-Time Intelligence with KQL
   Database, Data Factory with pipelines), Power BI as the presentation layer, and
   one F-SKU capacity meter that backs all of them."

Minute 2 — OneLake specifics
  "OneLake is one logical namespace per Microsoft 365 tenant. Every workspace item —
   Lakehouse table, Warehouse table, KQL Database — lives in OneLake as Delta. You
   can create shortcuts to external ADLS, S3, or GCS locations without copying data.
   V-Order write optimisation makes Delta files ~15-20% smaller and faster to scan
   for Direct Lake and Power BI."

Minute 3 — Direct Lake
  "Direct Lake is the Fabric-exclusive Power BI mode that column-loads Delta files
   from OneLake into the VertiPaq engine on-demand, page by page. No Import refresh,
   no DirectQuery translation. Sub-second query latency at scale, with automatic
   memory eviction when the semantic model exceeds capacity. Falls back to
   DirectQuery when data doesn't fit."

Minute 4 — F-SKU capacity + throttling
  "You buy a capacity — F2, F8, F64, up to F2048 — measured in Capacity Units (CU).
   All workspaces attached to that capacity share the CU budget. Overage triggers
   smoothing (Fabric averages usage over up to 24 hours), then interactive
   throttling (queries queue), then rejection. Per-second billing, auto-pause
   supported. Copilot in Fabric is charged from the same capacity."

Minute 5 — Databricks / Snowflake trade-offs
  "Pick Databricks over Fabric when the shop is multi-cloud, ML-first, or already
   deep in Unity Catalog. Pick Snowflake when the workload is pure SQL warehouse
   with heavy JSON and cross-cloud governance. Pick Fabric when Microsoft 365 +
   Power BI + T-SQL analysts + Azure are already the reality — Direct Lake and
   one-capacity billing tip it decisively."

Step-by-step explanation.

Minute 1 is the crucial framing. Naming the pattern immediately — "unified SaaS portal, one OneLake + four workloads + one capacity" — signals you're a decision-maker, not a task-runner. Weak candidates say "Microsoft's Databricks competitor" and lose the room immediately.
Minute 2 addresses OneLake before the interviewer asks. The senior answer distinguishes OneLake (tenant-wide logical namespace, Delta by default, built on ADLS Gen2 but not equal to it) from ADLS Gen2 (Azure Data Lake Storage, raw blob layer). Naming shortcuts + V-Order in the same breath signals depth.
Minute 3 covers Direct Lake, the Fabric-exclusive differentiator. Framing it as "column-load Delta files into VertiPaq on-demand, page by page" — not "just DirectQuery" — is the correct mental model. Say the fallback-to-DirectQuery story.
Minute 4 covers capacity + throttling. The exact sequence — smoothing → interactive throttling → rejection — is the answer interviewers grade against. Copilot billing from the same capacity is a bonus senior signal.
Minute 5 covers when not to pick Fabric. Naming Databricks + Snowflake + specific fit criteria for each shows you evaluate platforms as a decision-maker, not as a fan.

Output.

Grading criterion	Weak score	Senior score
Names four workloads in minute 1	rare	mandatory
Names OneLake correctly	rare	required
Names Direct Lake mechanism	rare	senior signal
Names smoothing/throttling/rejection order	rare	senior signal
Names Databricks/Snowflake trade-offs	occasional	required

Rule of thumb. The senior Fabric answer is a 5-minute monologue that covers OneLake, workloads, Direct Lake, capacity, and peer trade-offs without waiting for follow-ups. Rehearse it once; deploy it every time.

Worked example — the "pick the Fabric workload" decision tree

Detailed explanation. Given a new data problem inside a Microsoft-shop, the senior architect runs a 5-question decision tree to route the workload to the right Fabric engine. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a scenario and you can walk the tree out loud. Walk through the tree with three canonical scenarios.

Q1. Is the primary output a Power BI report? → yes = data lands in Lakehouse gold layer with Direct Lake semantic model.
Q2. Does the pipeline need T-SQL DDL + multi-table transactions + Data Warehouse UX? → yes = Warehouse; no = Q3.
Q3. Is the workload streaming / sub-second telemetry / log analytics? → yes = Real-Time Intelligence (Eventstream + KQL Database); no = Q4.
Q4. Is the workload Spark / Python / ML feature engineering? → yes = Lakehouse notebooks; no = Q5.
Q5. Is it batch orchestration / low-code ETL / mirroring? → yes = Data Factory pipelines + Dataflows Gen2.

Question. Walk the decision tree for three scenarios and record which Fabric workload each ends up in.

Input.

Scenario	Q1 (Power BI?)	Q2 (T-SQL?)	Q3 (streaming?)	Q4 (Spark ML?)	Q5 (batch ETL?)
Nightly finance DW refresh	yes (final output)	yes	no	no	yes (source ingest)
Real-time IoT telemetry	yes (dashboard)	no	yes	no	no
ML feature store	no (offline)	no	no	yes	yes

Code.

# Decision-tree helper (illustrative)
def pick_fabric_workload(is_power_bi_report: bool,
                          needs_tsql_dw: bool,
                          is_streaming: bool,
                          is_spark_ml: bool,
                          is_batch_etl: bool) -> list[str]:
    """Return the primary Fabric workload(s) for a data problem."""
    workloads: list[str] = []

    if is_power_bi_report:
        workloads.append("Power BI (Direct Lake semantic model)")

    if needs_tsql_dw:
        workloads.append("Warehouse (T-SQL)")
    elif is_streaming:
        workloads.append("Real-Time Intelligence (Eventstream + KQL Database)")
    elif is_spark_ml:
        workloads.append("Lakehouse (Spark notebooks)")

    if is_batch_etl:
        workloads.append("Data Factory (pipelines + Dataflows Gen2)")

    if not workloads:
        raise ValueError("no viable Fabric workload; check requirements")

    return workloads


# Walk the three scenarios
print(pick_fabric_workload(True,  True,  False, False, True))
# → ['Power BI (Direct Lake ...)', 'Warehouse (T-SQL)', 'Data Factory (...)']

print(pick_fabric_workload(True,  False, True,  False, False))
# → ['Power BI (Direct Lake ...)', 'Real-Time Intelligence (...)']

print(pick_fabric_workload(False, False, False, True,  True))
# → ['Lakehouse (Spark notebooks)', 'Data Factory (...)']

Step-by-step explanation.

Scenario 1 — Nightly finance DW refresh with Power BI as the output. The tree routes to Warehouse for the T-SQL DDL + multi-table transactions, Data Factory for the batch ingest orchestration, and Power BI Direct Lake for the semantic model over the gold Warehouse tables. Three workloads, one OneLake, one capacity.
Scenario 2 — Real-time IoT telemetry with a Power BI dashboard. The tree routes to Real-Time Intelligence (Eventstream ingest + KQL Database for time-series analytics) and Power BI Direct Lake (or DirectQuery to KQL for freshest data). No Warehouse, no batch pipeline needed.
Scenario 3 — ML feature store, offline. The tree routes to Lakehouse (Spark notebooks for feature engineering) and Data Factory (batch orchestration for the daily refresh). No Power BI, no T-SQL layer — feature store is consumed by ML training jobs, not analysts.
The tree captures the primary workload; multiple Fabric workloads compose freely because they share OneLake. A Warehouse table can be queried by a Lakehouse Spark notebook (via shared metadata) and consumed by a Power BI Direct Lake model — no data movement.
If no branch matches, refuse to pick a Fabric workload until the requirement is clarified. Common trap: "we want Power BI" without a clear source workload — every Power BI report needs an upstream Lakehouse / Warehouse / KQL model.

Output.

Scenario	Primary Fabric workload(s)
Nightly finance DW refresh	Warehouse + Data Factory + Power BI Direct Lake
Real-time IoT telemetry	Real-Time Intelligence + Power BI
ML feature store	Lakehouse + Data Factory

Rule of thumb. The five-question decision tree is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any scenario and get a workload name in under 60 seconds. Fabric's power is that these workloads compose over one OneLake — but the interview only rewards clean routing, not "we could use everything."

Senior interview question on Fabric adoption

A senior interviewer often opens with: "Your Microsoft-heavy organisation currently runs Synapse dedicated pools ($350k/year), Power BI Premium P2, Data Factory, and ADLS Gen2 landing zones. The CDO wants to consolidate onto one platform, cut cost by 20%, and add real-time IoT telemetry. Walk me through the Fabric adoption plan, the capacity sizing, the workload mapping, and the migration path that avoids a big-bang cutover."

Solution Using Fabric F64 capacity + phased workload migration + OneLake shortcuts

# Step 1 — Fabric capacity + workspace layout
capacity:
  sku: F64                          # 128 CU; ~$8k/month PAYG, ~$5.5k reserved
  region: East US 2                 # match existing ADLS Gen2 region
  auto_pause: false                 # analyst layer runs 24/7

workspaces:
  - name: finance-dw
    capacity: F64
    items:
      - warehouse: dw_finance       # T-SQL warehouse for analysts
      - lakehouse: bronze_finance   # raw landing
      - data_factory:
          pipeline: ingest_erp_daily
      - power_bi: dashboards_finance
  - name: iot-telemetry
    capacity: F64
    items:
      - real_time:
          eventstream: sensor-feed
          kql_database: telemetry
      - power_bi: dashboards_iot
  - name: ml-features
    capacity: F64
    items:
      - lakehouse: gold_features
      - notebook: feature_engineering

# Step 2 — OneLake shortcut from existing ADLS Gen2 landing zone (no data copy)
# Run via Fabric REST API or Fabric UI; illustrative Python via requests
import requests

FABRIC_API   = "https://api.fabric.microsoft.com/v1"
WORKSPACE_ID = "<finance-dw-workspace-guid>"
LAKEHOUSE_ID = "<bronze-finance-lakehouse-guid>"

shortcut_payload = {
    "name": "erp_landing_shortcut",
    "path": "Files",
    "target": {
        "adlsGen2": {
            "location":  "https://retaillake.dfs.core.windows.net",
            "subpath":   "/raw/erp",
            "connectionId": "<adls-connection-guid>",
        }
    },
}

r = requests.post(
    f"{FABRIC_API}/workspaces/{WORKSPACE_ID}/items/{LAKEHOUSE_ID}/shortcuts",
    headers={"Authorization": "Bearer <entra-token>"},
    json=shortcut_payload,
)
r.raise_for_status()
print("Shortcut created:", r.json()["name"])

-- Step 3 — migrate Synapse dedicated SQL pool objects to Fabric Warehouse (T-SQL is compatible)
-- Warehouse supports most T-SQL surface area; DDL translates 1:1
CREATE SCHEMA finance;

CREATE TABLE finance.fact_sales (
    sale_id      BIGINT       NOT NULL,
    customer_id  BIGINT       NOT NULL,
    sale_date    DATE         NOT NULL,
    amount_cents BIGINT       NOT NULL,
    channel      VARCHAR(32)  NOT NULL
);

CREATE TABLE finance.dim_customer (
    customer_id  BIGINT       NOT NULL PRIMARY KEY NONCLUSTERED NOT ENFORCED,
    name         VARCHAR(256) NOT NULL,
    country      VARCHAR(64)  NOT NULL
);

-- CTAS from Lakehouse Delta table (cross-workload query, no data copy)
CREATE TABLE finance.dim_product AS
SELECT * FROM [bronze_finance].[dbo].[product];

Step-by-step trace.

Phase	Component	Old (Synapse)	New (Fabric)
Storage	landing zone	ADLS Gen2 raw containers	OneLake shortcut (no copy)
Warehouse	analyst layer	Synapse dedicated SQL pool	Fabric Warehouse (T-SQL)
ETL	orchestration	Azure Data Factory	Fabric Data Factory pipelines
Streaming	real-time IoT	not present	Fabric Real-Time Intelligence
BI	reports	Power BI Premium P2 (Import)	Power BI Direct Lake
Cost	monthly	~$29k/mo Synapse + Power BI	~$5.5k/mo Fabric F64 reserved
Governance	audit	Purview standalone	Purview integrated

After the migration, the finance analyst layer runs on Fabric Warehouse (T-SQL DDL translates 1:1 from Synapse), the ML team runs Spark notebooks in the Lakehouse workspace, the IoT team lands sensor telemetry into KQL Database via Eventstream, and every Power BI dashboard runs Direct Lake against OneLake Delta. One capacity, one bill, one governance surface.

Output:

Metric	Before (Synapse + PBI)	After (Fabric F64)
Monthly cost	~$29k	~$5.5k (reserved)
Power BI freshness	1-4 h (Import refresh)	seconds (Direct Lake)
Real-time IoT	none	sub-second (RTI)
Governance surfaces	3 (Synapse, PBI, ADF)	1 (Fabric + Purview)
Identity	3 IAM models	1 (Entra)

Why this works — concept by concept:

F64 capacity as the anchor — 128 CU covers a mid-sized Microsoft shop's warehouse + BI + light Spark + light RTI. Reserved pricing (~$5.5k/mo) is 30% off PAYG (~$8k/mo). Right-sizing before commit is the biggest saving.
OneLake shortcut — the existing ADLS Gen2 landing zone becomes visible inside Fabric Lakehouse without copying any data. Zero migration cost for the raw layer; the shortcut is a pointer, not a duplicate.
Warehouse T-SQL surface — Fabric Warehouse ships T-SQL that translates 1:1 from Synapse dedicated pool DDL. Analysts keep their queries; the DDL migration is largely search-and-replace.
Direct Lake semantic model — Power BI reports run in-memory over Delta files without an Import refresh. Freshness drops from hours to seconds; the "refresh failed" pager disappears.
Cost — one F64 replaces three legacy bills. Real cost is $5.5k/mo (reserved) vs $29k/mo split across Synapse + Power BI Premium + ADF. Break-even on the migration effort is ~2 months; senior architects call this the "consolidation dividend." The eliminated cost is not just dollars — it's three governance surfaces collapsed into one.

SQL
Topic — sql
SQL problems on data-warehouse migration

Practice →

Design Topic — design Design problems on unified data platforms

Practice →

2. OneLake — the "OneDrive for data"

One tenant, one lake, Delta by default — the storage layer that finally erases per-team ADLS silos

The mental model in one line: OneLake is Microsoft's tenant-wide logical storage layer, built on ADLS Gen2 under the hood, that Fabric provisions automatically the moment a tenant enables Fabric — every Lakehouse table, Warehouse table, KQL Database, and Dataflow Gen2 output lives in OneLake as Delta by default, addressable by a single hierarchical namespace onelake://tenant/workspace/item/Tables|Files/…, with shortcuts that let you mount external ADLS / S3 / GCS locations without ever copying data. Every senior Fabric interview probes OneLake first because it's the invariant every other workload assumes.

The four axes for OneLake.

Namespace. One logical namespace per Microsoft 365 tenant, addressed as onelake://<tenant>.onelake.dfs.fabric.microsoft.com/<workspace>/<item>.<type>/Tables|Files/…. Every workspace, every item, every table is discoverable via one URI shape. No per-team storage accounts, no per-project containers.
Storage format. Delta Lake by default for tables (Lakehouse, Warehouse, KQL Database mirrors write Delta). Parquet + JSON + CSV live in Files/. V-Order optimisation is applied on write for maximum Direct Lake / VertiPaq scan efficiency.
Shortcuts. Pointers, not copies. A shortcut in Lakehouse Tables/orders can point to adls://raw@retaillake.dfs.core.windows.net/orders/ or s3://acme-lake/orders/ or gs://acme-lake/orders/ — the data stays where it is; Fabric queries it in place. Shortcuts remove the "copy data to Fabric" migration tax.
Governance. OneLake integrates with Microsoft Purview for lineage + sensitivity labels + data-loss prevention. Domains group workspaces (e.g. retail, finance, hr) into logical business units with delegated admin.

The OneLake namespace shape.

Tenant. One per Microsoft 365 tenant. contoso.onelake.dfs.fabric.microsoft.com — the DFS-compatible endpoint every external tool talks to.
Workspace. The Fabric collaboration unit. Every workspace has a folder in OneLake. RBAC scoped at the workspace level.
Item. A Lakehouse, Warehouse, KQL Database, ML model, notebook — every item lives in a workspace and has a folder in OneLake. Item type determines the sub-structure (Lakehouse has Tables/ + Files/; Warehouse has managed Delta under the hood but exposed via T-SQL).
Table / File. Under Tables/<table_name>/ the files are Delta transaction log + parquet parts. Under Files/<path>/ anything goes — CSVs, JSONs, images.

Shortcuts — the "no copy" superpower.

What they are. A shortcut is a metadata object in OneLake that points to an external location. Reads flow through — the Delta / Parquet at the target is read as if it lived in OneLake. Writes are conditional (shortcuts to external ADLS Gen2 are read/write; shortcuts to S3 / GCS are read-only in most 2026 configurations).
Why they matter. They collapse the "migrate my raw layer to Fabric" cost to zero. Point Fabric at your existing lake; the workloads see the data immediately.
Where they show up. Under Tables/ (Delta tables become queryable by all workloads) or Files/ (raw files stay raw). Shortcut RBAC uses the target's IAM — Fabric passes the caller's identity through to the source.
The interview question. "Can I query my existing S3 lake from Fabric without copying?" — required answer: yes, via OneLake shortcut; reads flow through; the target's IAM governs access.

V-Order — the Delta write optimisation you didn't know you were using.

What it is. A Fabric-specific parquet write optimisation applied automatically to Delta tables written by Lakehouse, Warehouse, and Dataflow Gen2. Reorders row groups and applies sort + compression tuned for VertiPaq (Power BI's in-memory engine) and Direct Lake.
The cost. ~15% additional CPU on write; ~15-20% smaller files; 2-4× faster Direct Lake and Power BI scan.
The interview question. "What is V-Order?" — required answer: Fabric's Delta write optimisation for Direct Lake / VertiPaq; applied by default; ~15% write cost for 2-4× read speedup.

Domains + workspaces + items — the governance hierarchy.

Domain. The top-level business unit (e.g. retail, finance, hr). Configured by tenant admin; each domain has delegated admins.
Workspace. The collaboration unit inside a domain. Workspaces bind to a capacity; RBAC applies at workspace level.
Item. Lakehouse, Warehouse, KQL Database, notebook, Power BI report, etc. Each item can have per-item ACLs on top of workspace RBAC.
Purview integration. Lineage across items is tracked automatically; sensitivity labels propagate; DLP policies enforce.

Common interview probes on OneLake.

"OneLake vs ADLS Gen2 — are they the same?" — required answer: OneLake is a logical layer on ADLS Gen2; one namespace per tenant, Delta by default, plus shortcuts.
"How do shortcuts work?" — pointers, not copies; reads flow through; target IAM governs.
"What is V-Order?" — Fabric's Delta write optimisation for Direct Lake / VertiPaq.
"How is OneLake governed?" — domains → workspaces → items; Purview for lineage + sensitivity + DLP.
"Can I write to OneLake from outside Fabric?" — yes, via the DFS-compatible endpoint; Delta writes from Spark on Databricks or Azure Synapse work fine.

Worked example — provisioning a Lakehouse and inspecting the OneLake path

Detailed explanation. The canonical first Fabric task: create a workspace, provision a Lakehouse, load a Delta table, and inspect where the data actually lives in OneLake. Walk through the whole flow with the exact URIs.

Workspace. retail-analytics-prod, capacity F64, domain retail.
Lakehouse. bronze_landing.
Table. pos_transactions written via Spark from a notebook.
URI. Enumerate the OneLake path where the Delta files live.

Question. Provision the Lakehouse, write a Delta table, and list the OneLake path.

Input.

Component	Value
Tenant	contoso.onelake.dfs.fabric.microsoft.com
Workspace	retail-analytics-prod
Lakehouse item	bronze_landing.Lakehouse
Table	pos_transactions
Files subfolder	raw_ingest/2026/07/

Code.

# Fabric notebook — write a Delta table into the Lakehouse
# (Fabric attaches the Lakehouse; abfss:// endpoint is auto-configured)

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

df = spark.createDataFrame(
    [
        (1001, "SKU-A", 2, 1299),
        (1002, "SKU-B", 1,  799),
        (1003, "SKU-C", 5, 4999),
    ],
    schema="txn_id INT, sku STRING, qty INT, amount_cents INT",
)

# Fabric magic — writing to a Lakehouse table implicitly writes Delta with V-Order
(df.write
   .format("delta")
   .mode("overwrite")
   .option("optimizeWrite", "true")     # Fabric-managed OPTIMIZE
   .option("mergeSchema", "true")
   .saveAsTable("pos_transactions"))    # resolves to Lakehouse.Tables/pos_transactions

print("Wrote", df.count(), "rows to pos_transactions")

# Inspect the OneLake path from a Spark shell or the Fabric file browser
abfss://retail-analytics-prod@onelake.dfs.fabric.microsoft.com/bronze_landing.Lakehouse/Tables/pos_transactions/
├── _delta_log/
│   ├── 00000000000000000000.json
│   └── 00000000000000000001.json
└── part-00000-<uuid>-c000.snappy.parquet    # V-Order applied

-- Same table, queryable from Warehouse via cross-item query (shared metadata)
SELECT sku, SUM(amount_cents) / 100.0 AS revenue
FROM   [bronze_landing].[dbo].[pos_transactions]
GROUP  BY sku
ORDER  BY revenue DESC;

Step-by-step explanation.

The Fabric notebook auto-attaches the Lakehouse: spark.sql("SELECT ...") and df.write.saveAsTable(...) both resolve names against the Lakehouse's metadata. No path juggling. The write lands under Tables/pos_transactions/ in the Lakehouse's OneLake folder.
optimizeWrite = true triggers Fabric's V-Order optimisation on the parquet files. The transaction log entry references the V-Order-encoded parts; a subsequent Direct Lake read enjoys the 2-4× scan speedup.
The physical URI is abfss://<workspace>@onelake.dfs.fabric.microsoft.com/<lakehouse>.Lakehouse/Tables/<table>/. This is the exact same DFS-compatible endpoint ADLS Gen2 uses, so external Spark clusters (Databricks, HDInsight) can read/write OneLake as if it were plain ADLS Gen2.
The cross-workload query from Warehouse — SELECT ... FROM [bronze_landing].[dbo].[pos_transactions] — works because every workload sees the same Fabric metadata catalog. No data movement, no ETL job, no ODBC gateway. The Warehouse T-SQL engine reads the Delta files directly.
If you drop the Lakehouse, the OneLake folder is deleted (with retention grace). If you drop just the table, only Tables/pos_transactions/ is removed. Underlying storage lifecycle is Fabric-managed; you don't manage ADLS containers by hand.

Output.

Aspect	Value
Physical path	`abfss://retail-analytics-prod@onelake.dfs.fabric.microsoft.com/bronze_landing.Lakehouse/Tables/pos_transactions/`
Format	Delta (parquet parts + `_delta_log/`)
V-Order applied	yes (Fabric default on write)
Cross-workload visibility	Warehouse T-SQL, Spark SQL, Power BI Direct Lake all see the table
External access	Databricks / Synapse Spark can read via same abfss:// endpoint

Rule of thumb. Trust Fabric's V-Order and optimizeWrite defaults for any Delta write. The 15% CPU cost on write is bought back many times over on read. Never write "raw" Parquet without V-Order into Tables/ — Power BI Direct Lake performance suffers immediately.

Worked example — creating a shortcut to an external S3 lake

Detailed explanation. The single most-used OneLake feature after provisioning: creating a shortcut to an existing external lake so Fabric workloads can query it without copying. Walk through the S3-shortcut flow — the trickier of the shortcut targets because it involves cross-cloud IAM.

Target. s3://acme-lake/warehouse/orders/ — Iceberg or Delta tables written by an existing Databricks / Trino pipeline.
Fabric side. A Lakehouse in retail-analytics-prod gets a shortcut Tables/orders_s3 that points at the S3 path.
IAM. An S3 access key (or STS role via a Fabric connection) with s3:GetObject on the target prefix.

Question. Create the S3 shortcut and query the external orders table from Fabric Warehouse.

Input.

Component	Value
Source	s3://acme-lake/warehouse/orders/
Target	onelake://.../bronze_landing.Lakehouse/Tables/orders_s3
Format	Delta (readable)
Access	AWS access key stored in a Fabric connection

Code.

# Create the shortcut via Fabric REST API
import requests

FABRIC_API   = "https://api.fabric.microsoft.com/v1"
WORKSPACE_ID = "<retail-analytics-prod-workspace-guid>"
LAKEHOUSE_ID = "<bronze-landing-lakehouse-guid>"
S3_CONN_ID   = "<fabric-s3-connection-guid>"    # pre-created in Fabric UI

shortcut = {
    "name": "orders_s3",
    "path": "Tables",                             # under Tables so all workloads see it
    "target": {
        "amazonS3": {
            "location":     "https://s3.us-east-1.amazonaws.com/acme-lake",
            "subpath":      "/warehouse/orders",
            "connectionId": S3_CONN_ID,
        }
    },
}

r = requests.post(
    f"{FABRIC_API}/workspaces/{WORKSPACE_ID}/items/{LAKEHOUSE_ID}/shortcuts",
    headers={"Authorization": "Bearer <entra-token>"},
    json=shortcut,
)
r.raise_for_status()
print("S3 shortcut created:", r.json())

-- Query the S3-backed Delta table from Fabric Warehouse (T-SQL cross-item query)
SELECT customer_id, COUNT(*) AS order_count, SUM(total_cents) / 100.0 AS revenue_usd
FROM   [bronze_landing].[dbo].[orders_s3]
WHERE  order_ts >= DATEADD(DAY, -7, GETUTCDATE())
GROUP  BY customer_id
HAVING COUNT(*) > 5
ORDER  BY revenue_usd DESC;

# Same query from a Spark notebook against the shortcut
df = spark.sql("""
    SELECT customer_id, COUNT(*) AS order_count
    FROM   bronze_landing.orders_s3
    WHERE  order_ts >= current_date() - INTERVAL 7 DAY
    GROUP  BY customer_id
""")
df.show()

Step-by-step explanation.

The Fabric connection object holds the AWS credentials (access key or STS role assumption) and is created once per credential set. It's referenced by GUID from every shortcut that uses it — you never inline credentials in shortcut definitions.
The shortcut targets s3://acme-lake/warehouse/orders/. Placing it under Tables/ (not Files/) means the Delta transaction log at the target is discoverable by every Fabric workload's metadata layer — Warehouse T-SQL, Spark SQL, KQL, and Power BI all see orders_s3 as a table.
Reads flow through: when the T-SQL query runs, Fabric's engine fetches the Delta transaction log from S3, then reads the parquet parts from S3. No data lands in OneLake; the S3 bill for egress applies (mitigated by same-region colocation).
The HAVING COUNT(*) > 5 predicate demonstrates cross-item SQL richness — the shortcut is functionally identical to a native OneLake table from the query engine's perspective. Predicate pushdown works if the target supports it (Iceberg / Delta both do).
If the S3 target ever moves or the IAM credential rotates, updating the Fabric connection propagates to every shortcut using it. Deleting the shortcut leaves the S3 data untouched — shortcuts are pointers, not copies.

Output.

Aspect	Value
Shortcut path	`Tables/orders_s3` inside `bronze_landing.Lakehouse`
Source	`s3://acme-lake/warehouse/orders/`
Read semantics	Delta transaction log fetched from S3; parquet parts read from S3
Write semantics	S3 shortcuts are read-only in most 2026 configurations
Cross-workload	Warehouse, Spark, Power BI Direct Lake all see the table
Egress cost	applies unless the Fabric region and S3 region match

Rule of thumb. For any Fabric adoption in an already-multi-cloud shop, shortcut the existing S3 / GCS lakes first before deciding to migrate. Query in place; measure; migrate only what proves expensive. Shortcuts remove the "copy the world into Fabric" tax on day one.

Worked example — V-Order impact measured on Power BI Direct Lake

Detailed explanation. A team notices their Power BI Direct Lake report on a 500-million-row fact_sales table takes 8 seconds to render the top-level KPI page. Rewriting the table with V-Order enabled drops render time to under 2 seconds. Walk through the measurement, the rewrite, and the underlying mechanism.

Baseline. fact_sales Delta written by an external Spark cluster (no V-Order). 500M rows, 12 GB on disk. Power BI top-level page renders in 8 s.
Fix. Rewrite via Fabric Lakehouse with optimizeWrite=true (V-Order applied).
Result. Same rows, ~10 GB on disk (15% smaller). Power BI page renders in 1.8 s.

Question. Diagnose the pre-fix Delta layout and apply V-Order.

Input.

Metric	Before V-Order	After V-Order
Row count	500M	500M
File count	200 parts	200 parts
Total size	12 GB	~10 GB
Direct Lake page render	8 s	1.8 s
VertiPaq compression ratio	~4:1	~6:1

Code.

# Rewrite the fact_sales Delta table with V-Order
from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Read the existing table
src = spark.read.format("delta").load(
    "abfss://retail-analytics-prod@onelake.dfs.fabric.microsoft.com/"
    "silver_conformed.Lakehouse/Tables/fact_sales/"
)

# Rewrite with V-Order + optimizeWrite
(src.write
    .format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .option("optimizeWrite", "true")            # Fabric-managed OPTIMIZE
    .option("dataChange", "false")              # signal no logical change to Direct Lake
    .saveAsTable("fact_sales_vordered"))

# Swap tables via ALTER TABLE RENAME once verified
spark.sql("ALTER TABLE silver_conformed.fact_sales RENAME TO fact_sales_old")
spark.sql("ALTER TABLE silver_conformed.fact_sales_vordered RENAME TO fact_sales")

-- Verify V-Order applied via Delta table properties
DESCRIBE DETAIL silver_conformed.fact_sales;
-- Look for isVOrdered = true in properties

-- Force a OneLake OPTIMIZE if partial-V-Order (some parts old, some new)
OPTIMIZE silver_conformed.fact_sales;

Step-by-step explanation.

The pre-fix table was written by an external Spark cluster with vanilla Delta writer. Parquet files were sorted by write time, not by cardinality. VertiPaq's column dictionary compression didn't hit its optimal ratio.
Rewriting with optimizeWrite=true (Fabric-managed OPTIMIZE) applies V-Order: parquet row groups are reordered for high-cardinality-first sort, and Delta writer emits row-group statistics tuned for VertiPaq scan.
The 15% file-size reduction comes from better compression (higher-cardinality-first sort improves RLE / dictionary hit rate). The 4-5× Direct Lake speedup comes from VertiPaq being able to load fewer, more selective row groups per page render.
dataChange = false on the write tells Direct Lake the logical data hasn't changed — semantic-model refreshes see this as a physical reorg, not a data update, so cached page renders don't invalidate wholesale.
In production the swap is via ALTER TABLE ... RENAME after verifying the new table's row count and sample. Never DROP the old table until Direct Lake has served queries from the new one for at least one refresh cycle.

Output.

Metric	Before V-Order	After V-Order
Table size	12 GB	10.2 GB
Row-group count	5000	3800
VertiPaq compression	4.1×	5.9×
Direct Lake page render (p50)	8.0 s	1.8 s
Direct Lake page render (p99)	22 s	4.5 s
Cost per report render	3 CU-s	0.7 CU-s

Rule of thumb. Any Delta table destined for Power BI Direct Lake must be V-Ordered. Either write with Fabric's default optimizeWrite=true, or run OPTIMIZE post-hoc to convert an external-written table. The 15% write-CPU cost is negligible against the 4× Direct Lake speedup and 4× lower CU/render cost.

Senior interview question on OneLake

A senior interviewer might ask: "Your team has ~200 TB of existing Delta tables in ADLS Gen2 across 15 team-owned storage accounts, written by Databricks jobs. You're rolling out Fabric to unify the analyst BI layer on Power BI Direct Lake. Walk me through the OneLake shortcut strategy, the V-Order retrofit for the hottest tables, and the governance model that gives auditors one lineage graph across the old ADLS lake and the new OneLake."

Solution Using OneLake shortcuts + selective V-Order retrofit + domains + Purview lineage

# Step 1 — enumerate ADLS Gen2 lakes; create OneLake shortcuts (no data copy)
import requests

FABRIC_API   = "https://api.fabric.microsoft.com/v1"
DOMAIN_ID    = "<retail-domain-guid>"
CONN_ADLS    = "<adls-gen2-connection-guid>"    # per-storage-account credential

adls_lakes = [
    ("retail-sales-lake",     "silver/fact_sales/"),
    ("retail-inventory-lake", "silver/inventory/"),
    # ... 15 total
]

# One Fabric workspace per team; one Lakehouse acting as shortcut aggregator
for account, prefix in adls_lakes:
    workspace_id = create_workspace(f"retail-{account}-fabric", domain=DOMAIN_ID, capacity="F64")
    lakehouse_id = create_lakehouse(workspace_id, f"{account}_shortcut_hub")

    shortcut = {
        "name": prefix.replace("/", "_").strip("_"),
        "path": "Tables",
        "target": {
            "adlsGen2": {
                "location":     f"https://{account}.dfs.core.windows.net",
                "subpath":      f"/{prefix}",
                "connectionId": CONN_ADLS,
            }
        },
    }
    r = requests.post(
        f"{FABRIC_API}/workspaces/{workspace_id}/items/{lakehouse_id}/shortcuts",
        headers={"Authorization": "Bearer <entra-token>"},
        json=shortcut,
    )
    r.raise_for_status()

# Step 2 — retrofit V-Order on the hottest tables only (top 20 by report load)
HOT_TABLES = [
    "retail-sales-lake:silver/fact_sales",
    "retail-inventory-lake:silver/inventory_snapshot",
    # ...
]

for entry in HOT_TABLES:
    account, path = entry.split(":", 1)
    src = spark.read.format("delta").load(
        f"abfss://{account}@onelake.dfs.fabric.microsoft.com/{path}"
    )
    (src.write
        .format("delta")
        .mode("overwrite")
        .option("optimizeWrite", "true")
        .option("dataChange", "false")
        .save(f"abfss://{account}@onelake.dfs.fabric.microsoft.com/{path}"))
    spark.sql(f"OPTIMIZE delta.`abfss://{account}@onelake.dfs.fabric.microsoft.com/{path}` VORDER")

# Step 3 — Purview + domain governance
purview:
  scan_targets:
    - onelake://contoso/retail-*
    - adls://retail-*.dfs.core.windows.net
  lineage:
    unified: true          # cross-source lineage graph
  sensitivity_defaults:
    domain: retail
    label: internal
  dlp_rules:
    - name: pii_customer_ssn
      pattern: "ssn|social_security"
      action: block_direct_lake_publish

Step-by-step trace.

Component	Approach	Result
Existing ADLS lakes	shortcut per storage account	zero copy; visible in Fabric immediately
Hot tables (top 20)	V-Order retrofit via `OPTIMIZE ... VORDER`	4× Direct Lake speedup where it matters
Cold tables (rest)	leave in place	no migration cost, no read speedup
Governance	Purview unified scan on OneLake + ADLS	one lineage graph
Domain	`retail` domain groups all workspaces	delegated admin per business unit
Sensitivity	default `internal` per domain	propagates to every shortcut

After rollout, the analyst layer queries retail-sales-lake:silver/fact_sales via Fabric Warehouse T-SQL and Power BI Direct Lake without knowing (or caring) that the data physically lives in the old ADLS account. Purview shows lineage from Databricks pipelines → ADLS → Fabric shortcut → Direct Lake semantic model — one graph, one audit trail.

Output:

Concern	Answer	Value
Data copied to OneLake	0 TB	shortcuts are pointers
V-Order retrofit scope	top 20 hot tables	~5 TB rewritten
Direct Lake speedup (hot tables)	4× median	8s → 2s report render
Purview lineage graph nodes	~800	unified across ADLS + OneLake
Governance surfaces	1 (Fabric + Purview)	vs. 15 previously (per-account IAM)
Migration effort	~2 engineer-weeks	vs ~6 months for a copy-migration

Why this works — concept by concept:

OneLake shortcut — pointers, not copies. The 200 TB stays where it is; Fabric queries in place. Zero migration cost for the raw layer; V-Order retrofit is scoped to the hot tables only.
Selective V-Order retrofit — the 15% file-size + 4× read speedup only matters where reads dominate. Applying OPTIMIZE ... VORDER to the top 20 tables captures 90% of the Power BI Direct Lake load with 3% of the rewrite cost.
Domain + workspace hierarchy — one retail domain groups 15 team-owned workspaces; each team gets delegated admin; the tenant admin sees one governance surface. Avoids the "who owns which workspace" chaos of a flat model.
Purview unified lineage — scans both ADLS Gen2 (source of the Databricks writes) and OneLake (Fabric workspaces + shortcuts) into one graph. Auditors ask one question ("who touched customer_ssn?"); the graph answers.
Cost — ~2 engineer-weeks for shortcut provisioning + V-Order retrofit + Purview setup, vs ~6 months + significant storage duplication for a copy migration. F64 capacity handles the analyst BI load; storage stays in the original ADLS accounts. Compared to a Databricks-only future, adds Direct Lake at zero-copy cost.

SQL
Topic — sql
SQL problems on cross-lake governance

Practice →

Design Topic — design Design problems on unified data lakes

Practice →

3. Fabric workloads — Lakehouse, Warehouse, Real-Time Intelligence

One OneLake, four native compute engines — the workload split that determines every design decision

The mental model in one line: Fabric ships four native compute engines — Lakehouse (Spark + Delta + notebooks + SQL endpoint), Warehouse (T-SQL over Delta with a data-warehouse UX and multi-table transactions), Real-Time Intelligence (Eventstream + KQL Database, the Data Explorer successor), and Data Factory (pipelines + Copy activity + Dataflows Gen2) — plus Power BI as the presentation layer and Data Science as the ML surface, all sharing one OneLake storage and one metadata catalog so any workload can query any other workload's tables with zero data movement. Every senior Fabric interview probes this workload split because picking wrong is the most common architectural error.

The five workloads at a glance.

Lakehouse. Spark + Delta + notebooks. The Databricks-shaped experience inside Fabric. Best fit: Python ETL, Spark SQL, ML feature engineering, semi-structured JSON, unstructured file processing.
Warehouse. T-SQL over Delta with a full data-warehouse UX. Multi-statement transactions, primary keys (non-enforced), stored procedures, T-SQL surface. Best fit: analyst-driven SQL, star schemas, BI marts, procedural ETL from analysts who don't want to write Spark.
Real-Time Intelligence. Eventstream (managed stream ingest with no-code source/sink connectors) + KQL Database (the Azure Data Explorer engine, KQL query language, columnar time-series storage). Best fit: telemetry, log analytics, IoT, clickstream — anything sub-second and time-series-shaped.
Data Factory. Pipelines (control flow + orchestration + Copy activity across 100+ connectors) + Dataflows Gen2 (Power Query-based low-code ETL). Best fit: batch orchestration, mirroring, low-code ETL for citizen data engineers.
Data Science. MLflow-compatible notebooks + AutoML + model registry. Best fit: ML lifecycle inside the Fabric governance surface.

The Lakehouse — Spark + Delta + notebooks + SQL endpoint.

Compute. Spark, provisioned on demand from the Fabric capacity. Starter pools spin up in ~10s; custom pools for pinned versions / libraries.
Storage. Delta tables under Tables/; unstructured files under Files/.
SQL endpoint. Every Lakehouse ships a T-SQL SQL Endpoint automatically — read-only T-SQL over the same Delta tables, for BI tools that want SQL Server semantics.
Interviews. "Lakehouse vs Warehouse — which do I pick?" — required answer: Lakehouse for Spark / Python / ML; Warehouse for T-SQL DDL + multi-table transactions.

The Warehouse — T-SQL over Delta.

Compute. Distributed T-SQL engine (the Synapse Serverless heritage) with a data-warehouse UX. Not the same engine as the SQL Endpoint — Warehouse writes, SQL Endpoint reads.
Storage. Delta tables under the hood; hidden from the T-SQL user. Cross-warehouse queries work; cross-Lakehouse queries work via three-part naming.
Transactions. Multi-table, multi-statement transactions with COMMIT/ROLLBACK. This is the Warehouse's key differentiator vs Lakehouse — Delta's ACID is single-table; Warehouse orchestrates multi-table ACID on top.
Interviews. "Why not just use Lakehouse SQL endpoint everywhere?" — required answer: SQL endpoint is read-only; Warehouse is the write path with multi-table transactions.

Real-Time Intelligence — Eventstream + KQL Database.

Eventstream. No-code ingest. Sources: Azure Event Hubs, IoT Hub, Kafka, Change Data Capture from SQL, custom apps. Sinks: KQL Database, Lakehouse, Warehouse. Filtering + transformations in a visual editor.
KQL Database. Azure Data Explorer engine repackaged for Fabric. Columnar time-series storage; KQL query language; sub-second scan on billions of rows.
OneLake mirroring. KQL tables mirror to OneLake as Delta automatically, so Warehouse / Lakehouse / Power BI can read the same telemetry data as batch tables.
Interviews. "When do I use KQL Database vs Lakehouse for streaming?" — required answer: KQL for sub-second time-series analytics; Lakehouse for micro-batch ETL landing in Delta.

Data Factory — pipelines + Dataflows Gen2.

Pipelines. Control flow (For Each, If Condition), Copy activity (100+ connectors), Notebook activity, Dataflow activity, invoke pipeline, Web activity. The Azure Data Factory heritage adapted for Fabric.
Dataflows Gen2. Power Query M expressions in a low-code UI. Landing target is a Delta table or a Warehouse table.
Mirroring. A special DF surface that continuously replicates external DBs (Snowflake, Databricks, Cosmos DB, Azure SQL) into OneLake as Delta with minimal setup.
Interviews. "When do I use DF vs Lakehouse notebook for ETL?" — required answer: DF for low-code orchestration + citizen-DE workflows; Lakehouse notebook for Python / Spark / heavy transformation.

Cross-workload composition.

Shared metadata. One catalog across Lakehouse / Warehouse / SQL Endpoint / Power BI. A table created in Lakehouse is queryable from Warehouse via three-part naming ([lakehouse].[dbo].[table]).
Zero-copy read. Warehouse T-SQL can read a Lakehouse Delta table directly. Power BI Direct Lake can build a semantic model over a Warehouse table. No ETL required between workloads.
Governance. Workspace RBAC applies to all items uniformly. Purview lineage tracks reads across workloads.
The interview probe. "How do workloads share data?" — required answer: shared metadata over OneLake; zero-copy cross-workload queries via three-part naming.

Common interview probes on Fabric workloads.

"Lakehouse vs Warehouse — which do I pick when?" — Lakehouse for Spark / Python / ML; Warehouse for T-SQL + multi-table transactions.
"What's the SQL Endpoint?" — read-only T-SQL over Lakehouse Delta tables; ships automatically per Lakehouse.
"How does RTI differ from Lakehouse for streaming?" — RTI for sub-second time-series analytics; Lakehouse for micro-batch Delta landing.
"Can Warehouse and Lakehouse query each other?" — yes, three-part naming; shared metadata; zero-copy.
"When do I use Dataflow Gen2 vs a Lakehouse notebook?" — DF for low-code orchestration + citizen DEs; notebook for Python / Spark.

Worked example — cross-workload query from Warehouse to Lakehouse

Detailed explanation. The most-used cross-workload pattern: a Warehouse-based analyst layer joins a Lakehouse Delta table (written by a Spark ETL) with a Warehouse dim table (managed by a DBA). Walk through the SQL and the underlying mechanism.

Lakehouse. silver_conformed holds fact_sales (500M rows, written nightly by Spark).
Warehouse. dw_finance holds dim_customer, dim_product, dim_date (managed by analysts via T-SQL DDL).
Query. Join fact and dims; return monthly revenue by product category.

Question. Write the cross-workload T-SQL and explain the underlying execution.

Input.

Object	Workload	Type
silver_conformed.fact_sales	Lakehouse	Delta table (Spark-written)
dw_finance.finance.dim_customer	Warehouse	T-SQL managed table
dw_finance.finance.dim_product	Warehouse	T-SQL managed table
dw_finance.finance.dim_date	Warehouse	T-SQL managed table

Code.

-- From Fabric Warehouse dw_finance — cross-item query into silver_conformed Lakehouse
USE dw_finance;

WITH monthly_sales AS (
    SELECT d.year_month,
           p.category,
           SUM(f.amount_cents) / 100.0 AS revenue_usd,
           COUNT(*)                    AS txn_count
    FROM   [silver_conformed].[dbo].[fact_sales] f          -- Lakehouse Delta
    JOIN   finance.dim_customer  c ON f.customer_id = c.customer_id
    JOIN   finance.dim_product   p ON f.product_id  = p.product_id
    JOIN   finance.dim_date      d ON CAST(f.sale_ts AS DATE) = d.date_key
    WHERE  d.year_month >= '2026-01'
      AND  c.country     = 'US'
    GROUP  BY d.year_month, p.category
)
SELECT year_month, category, revenue_usd, txn_count
FROM   monthly_sales
ORDER  BY year_month, revenue_usd DESC;

# Same query from a Lakehouse Spark notebook (mirror image)
df = spark.sql("""
    SELECT d.year_month, p.category, SUM(f.amount_cents) / 100.0 AS revenue_usd
    FROM   silver_conformed.fact_sales f
    JOIN   dw_finance.finance.dim_customer c ON f.customer_id = c.customer_id
    JOIN   dw_finance.finance.dim_product  p ON f.product_id  = p.product_id
    JOIN   dw_finance.finance.dim_date     d ON to_date(f.sale_ts) = d.date_key
    WHERE  d.year_month >= '2026-01'
    GROUP  BY d.year_month, p.category
""")
df.show()

Step-by-step explanation.

The Warehouse T-SQL engine sees silver_conformed.dbo.fact_sales as a first-class table because the Fabric shared metadata layer exposes every Lakehouse table under the [lakehouse_name].[dbo].[table_name] three-part name. No linked server, no ODBC gateway — the metadata is in the same catalog.
Under the hood, the Warehouse engine reads the Delta transaction log at Tables/fact_sales/_delta_log/ in silver_conformed's OneLake folder, resolves the current snapshot's parquet parts, and streams them into the distributed query engine.
The join with dim_customer, dim_product, dim_date runs entirely in the Warehouse engine because those are Warehouse-managed tables. The optimiser broadcasts the small dim tables and streams the large fact_sales parquet parts through — the same shape as a Synapse dedicated-pool join.
Predicate pushdown works: WHERE c.country = 'US' is pushed into the customer scan; WHERE d.year_month >= '2026-01' is pushed into the date scan. Column pruning on fact_sales reads only customer_id, product_id, sale_ts, amount_cents — not the full row.
From the Spark side (second block) the exact same tables are queryable via Spark SQL. The metadata catalog is shared; the compute engine differs. Analysts write T-SQL; data scientists write Spark; both hit the same Delta files under OneLake.

Output.

year_month	category	revenue_usd	txn_count
2026-01	Electronics	4,289,750.25	24,158
2026-01	Apparel	2,145,880.10	18,092
2026-02	Electronics	4,510,220.75	25,391
...	...	...	...

Rule of thumb. For any cross-workload query, use three-part naming ([item].[dbo].[table]) from the workload that owns the primary compute. Warehouse is the T-SQL front door; Lakehouse is the Spark front door; both see the same OneLake tables through shared metadata. Never build ETL to copy data between workloads within the same OneLake — it's wasted CU.

Worked example — Real-Time Intelligence ingest and KQL query

Detailed explanation. The canonical Fabric RTI pattern: an Eventstream ingests IoT sensor data from Azure Event Hubs, lands it in a KQL Database, and analysts run sub-second KQL queries for operational dashboards. Walk through the full flow.

Source. Azure Event Hubs sensor-feed (10k events/sec).
Eventstream. Managed ingest, filters events with qc_status = 'valid'.
KQL Database. telemetry with table sensor_readings.
Query. 15-minute rolling average per sensor.

Question. Configure the Eventstream, define the KQL table, and write the analytic query.

Input.

Component	Value
Source	Event Hubs `sensor-feed`
Ingest rate	10k events/sec
Sink	KQL Database `telemetry.sensor_readings`
Retention	90 days hot; 1 year cold
Query pattern	15-min rolling avg per sensor

Code.

# Eventstream config (Fabric UI generates this JSON; illustrative)
eventstream:
  name: sensor-ingest
  sources:
    - name: event-hubs-sensors
      type: EventHubs
      namespace: acme-iot.servicebus.windows.net
      hub: sensor-feed
      consumer_group: $Default
  transformations:
    - name: filter-valid
      type: Filter
      expression: "qc_status == 'valid'"
    - name: enrich-region
      type: ManageFields
      add:
        - name: region_from_sensor
          expression: "substring(sensor_id, 0, 3)"
  destinations:
    - name: telemetry-kql
      type: KQLDatabase
      target_database: telemetry
      target_table: sensor_readings
      ingestion_mode: streaming        # sub-second ingest

// KQL Database table definition
.create table sensor_readings (
    ts:                datetime,
    sensor_id:         string,
    temperature_c:     real,
    humidity_pct:      real,
    region_from_sensor:string,
    qc_status:         string
)

// Retention policy — 90 days hot + 1 year cold
.alter-merge table sensor_readings policy retention @'{"SoftDeletePeriod": "365.00:00:00", "Recoverability": "Enabled"}'

// 15-minute rolling avg per sensor for the last 24 hours
sensor_readings
| where ts >= ago(24h)
| summarize
    avg_temp = avg(temperature_c),
    p95_temp = percentile(temperature_c, 95),
    reading_count = count()
    by sensor_id, bin(ts, 15m)
| where reading_count >= 50
| order by ts desc, sensor_id asc

-- Same KQL table exposed as Delta via OneLake mirroring; query from Warehouse
SELECT sensor_id,
       DATEADD(MINUTE, -DATEPART(MINUTE, ts) % 15, ts) AS window_15m,
       AVG(temperature_c) AS avg_temp
FROM   [telemetry].[dbo].[sensor_readings]
WHERE  ts >= DATEADD(HOUR, -24, GETUTCDATE())
GROUP  BY sensor_id, DATEADD(MINUTE, -DATEPART(MINUTE, ts) % 15, ts)
ORDER  BY window_15m DESC, sensor_id;

Step-by-step explanation.

Eventstream configures the Event Hubs source (namespace + hub + consumer group), applies a filter (qc_status == 'valid' drops the 3% of noisy readings), adds a derived column (region_from_sensor via string slice), and routes the stream into the KQL Database telemetry.sensor_readings table with ingestion_mode: streaming — sub-second end-to-end latency.
The KQL table definition uses first-class column types (datetime, string, real). Retention policy (SoftDeletePeriod = 365 days) tells KQL to keep data for a year; the hot / cold split is automatic via ADX's storage tiers.
The KQL 15-minute rolling avg query uses summarize ... by sensor_id, bin(ts, 15m) — the classic time-bucket idiom. percentile(..., 95) gives the p95 in the same pass. where reading_count >= 50 filters low-signal buckets.
Because OneLake mirroring is enabled on the KQL Database, the same sensor_readings table is queryable from Warehouse T-SQL. The Warehouse query uses DATEADD for the 15-minute bucket (T-SQL doesn't have bin). Same data, two engines, two languages, one storage.
Power BI Direct Lake can build a semantic model over either the KQL table (via KQL semantic model) or the mirrored Delta (via Direct Lake against OneLake). The KQL path gives freshest data; the mirrored Delta path gives better VertiPaq compression. Interview probe: "which do I pick?" — KQL when freshness beats compression; mirrored Delta when the report is 15+ minutes stale-tolerant.

Output.

sensor_id	window_15m	avg_temp	p95_temp	reading_count
ny01-sen-42	2026-07-27 12:15	22.4	24.1	892
ny01-sen-42	2026-07-27 12:00	22.1	23.9	900
tx03-sen-11	2026-07-27 12:15	31.7	34.2	887
...	...	...	...	...

Rule of thumb. For any sub-second telemetry workload, RTI (Eventstream → KQL Database) is the correct answer. For any 15-min+ batch analytics, a Lakehouse landing table via Structured Streaming is cheaper. When you need both, enable KQL OneLake mirroring so both engines see the same data — pay once, query twice.

Worked example — Warehouse multi-table transaction that Lakehouse can't do

Detailed explanation. A finance workflow requires an atomic update across fact_ledger, dim_account, and audit_log — three tables must either all commit or all roll back. Lakehouse Delta gives per-table ACID but not multi-table transactions; Warehouse gives multi-table ACID via T-SQL. Walk through the pattern.

Requirement. Atomically insert into fact_ledger, update balance in dim_account, insert audit row into audit_log. All three or none.
Lakehouse. Would require Delta MERGE per table without cross-table atomicity — a partial-failure hazard.
Warehouse. BEGIN TRAN ... COMMIT wraps all three DMLs; rollback on any error.

Question. Write the Warehouse T-SQL procedure and explain why Lakehouse can't do this cleanly.

Input.

Table	Workload	Operation
finance.fact_ledger	Warehouse	INSERT
finance.dim_account	Warehouse	UPDATE (balance)
finance.audit_log	Warehouse	INSERT
Atomicity	required	multi-table ACID

Code.

-- Fabric Warehouse — atomic multi-table transaction
CREATE PROCEDURE finance.post_ledger_entry
    @account_id  BIGINT,
    @amount      DECIMAL(18, 2),
    @description NVARCHAR(500)
AS
BEGIN
    SET XACT_ABORT ON;                    -- roll back on any error
    BEGIN TRAN;

    DECLARE @ledger_id BIGINT;

    -- 1. Insert into fact_ledger
    INSERT INTO finance.fact_ledger (account_id, amount, entry_ts, description)
    VALUES (@account_id, @amount, GETUTCDATE(), @description);
    SET @ledger_id = SCOPE_IDENTITY();

    -- 2. Update dim_account balance
    UPDATE finance.dim_account
    SET    balance    = balance + @amount,
           updated_at = GETUTCDATE()
    WHERE  account_id = @account_id;

    IF @@ROWCOUNT != 1
    BEGIN
        THROW 50001, 'Account not found or duplicate update', 1;
    END

    -- 3. Insert into audit_log
    INSERT INTO finance.audit_log (ledger_id, account_id, action, actor, action_ts)
    VALUES (@ledger_id, @account_id, 'LEDGER_POST', SYSTEM_USER, GETUTCDATE());

    COMMIT TRAN;
END;

# Contrast — attempting the same in Lakehouse Spark; NOT atomic across tables
from delta.tables import DeltaTable

def post_ledger_lakehouse(account_id, amount, description):
    # Each Delta op is atomic per-table, but there's no cross-table transaction
    ledger_df = spark.createDataFrame([(account_id, amount, description)])
    ledger_df.write.format("delta").mode("append").saveAsTable("fact_ledger")   # commit 1

    (DeltaTable.forName(spark, "dim_account")
        .update(
            condition="account_id = " + str(account_id),
            set={"balance": f"balance + {amount}"},
        ))                                                                       # commit 2

    audit_df = spark.createDataFrame([(account_id, "LEDGER_POST")])
    audit_df.write.format("delta").mode("append").saveAsTable("audit_log")       # commit 3

    # If commit 2 fails, commit 1 already happened — partial failure hazard.

Step-by-step explanation.

The Warehouse procedure wraps all three DMLs inside BEGIN TRAN ... COMMIT. SET XACT_ABORT ON ensures any statement error triggers a full rollback. The THROW on @@ROWCOUNT != 1 catches the "account not found" case explicitly.
Multi-table transactions in Fabric Warehouse work because the engine maintains a distributed transaction coordinator on top of the underlying Delta writes. When you COMMIT, all three Delta transaction logs are updated atomically (or all rolled back).
The Lakehouse contrast shows the hazard: three separate Delta writes, each atomic per-table. If commit 2 (the dim_account update) fails after commit 1 (the ledger insert), you now have a ledger row with no corresponding balance update — a silent correctness bug that only surfaces when someone reconciles ledger sum vs account balance.
There is a Delta cross-table pattern via one giant multi-statement command (MERGE INTO ... USING ... WHEN MATCHED ...) that can update multiple targets in one go, but it doesn't compose across arbitrary tables the way Warehouse BEGIN TRAN does. For any workflow with 3+ tables and business-invariant cross-table constraints, Warehouse is the correct choice.
Note that Warehouse tables still live in OneLake as Delta under the hood — the physical storage is the same. What differs is the transaction coordinator on top: Warehouse ships one; Lakehouse (as of 2026) does not.

Output.

Aspect	Warehouse (T-SQL)	Lakehouse (Delta)
Per-table ACID	yes	yes
Multi-table transaction	yes	no
Multi-statement `BEGIN TRAN`	yes	not supported
Rollback on any error	yes (SET XACT_ABORT ON)	manual per-table only
Best fit	financial ETL, DW loads	Spark/ML/single-table upserts

Rule of thumb. For any workflow that requires atomicity across two or more tables, use Fabric Warehouse and wrap in BEGIN TRAN. Lakehouse Delta is single-table ACID only; using it for multi-table workflows leaks correctness bugs into production. This is the load-bearing difference between the two workloads.

Senior interview question on Fabric workloads

A senior interviewer might ask: "You're designing an end-to-end retail analytics pipeline on Fabric. Data lands from POS (batch, nightly), from store IoT (real-time, 10k events/sec), and from an existing Snowflake warehouse (via mirroring). The output is a Power BI executive dashboard. Walk me through the workload split — which Fabric engine owns which slice, why, and how they compose."

Solution Using Lakehouse + Warehouse + RTI + DF + Power BI Direct Lake

# Workload map — one workspace, five item types, one OneLake
workspace: retail-e2e-analytics-prod
capacity: F128           # 256 CU; handles all four workloads + Direct Lake

items:
  # 1. POS batch ingest (nightly)
  data_factory:
    - pipeline: ingest_pos_daily
      steps:
        - Copy: POS SFTP → Lakehouse Files/raw/pos/
        - Notebook: bronze → silver Delta transform

  # 2. Bronze / silver / gold Lakehouse
  lakehouse:
    - name: retail_lakehouse
      tables:
        bronze:
          - raw_pos_transactions (Files → Delta)
        silver:
          - clean_transactions (Delta, deduplicated)
          - dim_store (SCD Type 2)
        gold:
          - fact_daily_sales (aggregated, V-Order)

  # 3. Warehouse for T-SQL analyst layer (multi-table txns)
  warehouse:
    - name: retail_dw
      schema: mart
      tables:
        - mart.fact_sales_summary
        - mart.dim_product
        - mart.dim_date
      procs:
        - mart.usp_load_daily_mart   # atomic multi-table load

  # 4. Real-Time Intelligence for store IoT
  real_time:
    eventstream:
      - name: store-telemetry
        source: EventHubs://acme-iot/store-feed
        sink: KQLDatabase://retail_rti/live_telemetry
    kql_database:
      - name: retail_rti
        tables:
          - live_telemetry (streaming ingest)

  # 5. Power BI Direct Lake semantic model
  power_bi:
    - semantic_model: retail_exec_dashboard
      mode: DirectLake
      sources:
        - retail_lakehouse.gold.fact_daily_sales
        - retail_dw.mart.fact_sales_summary
        - retail_rti.live_telemetry
      pages:
        - Executive KPI
        - Real-Time Store Ops
        - Product Performance

# Notebook — bronze → silver transformation inside Lakehouse
def build_silver(spark):
    bronze = spark.read.format("delta").table("retail_lakehouse.bronze.raw_pos_transactions")

    silver = (
        bronze
        .dropDuplicates(["txn_id"])
        .filter("amount_cents > 0")
        .withColumn("sale_date", to_date("sale_ts"))
    )

    (silver.write
        .format("delta")
        .mode("overwrite")
        .partitionBy("sale_date")
        .option("optimizeWrite", "true")
        .saveAsTable("retail_lakehouse.silver.clean_transactions"))

-- Warehouse stored procedure — atomic multi-table load into mart
CREATE PROCEDURE mart.usp_load_daily_mart
    @load_date DATE
AS
BEGIN
    SET XACT_ABORT ON;
    BEGIN TRAN;

    -- Refresh fact from Lakehouse silver
    DELETE FROM mart.fact_sales_summary WHERE sale_date = @load_date;

    INSERT INTO mart.fact_sales_summary (sale_date, product_id, revenue_usd, txn_count)
    SELECT   sale_date,
             product_id,
             SUM(amount_cents) / 100.0,
             COUNT(*)
    FROM     [retail_lakehouse].[dbo].[clean_transactions]
    WHERE    sale_date = @load_date
    GROUP BY sale_date, product_id;

    -- Refresh dim
    MERGE mart.dim_product AS tgt
    USING (SELECT DISTINCT product_id, product_name, category
           FROM [retail_lakehouse].[dbo].[clean_transactions]) AS src
    ON  tgt.product_id = src.product_id
    WHEN MATCHED     THEN UPDATE SET product_name = src.product_name, category = src.category
    WHEN NOT MATCHED THEN INSERT (product_id, product_name, category)
                          VALUES (src.product_id, src.product_name, src.category);

    COMMIT TRAN;
END;

Step-by-step trace.

Slice	Workload	Why this workload
POS batch ingest (nightly)	Data Factory pipeline	100+ connectors + control flow + orchestration
Bronze → silver → gold ETL	Lakehouse (Spark notebooks)	Python / Spark heavy transformation
Analyst mart layer	Warehouse (T-SQL)	multi-table atomic loads via BEGIN TRAN
Real-time store IoT	RTI (Eventstream + KQL)	sub-second time-series analytics
Executive dashboard	Power BI Direct Lake	in-memory scans over Delta / KQL mirror
Storage	OneLake	one namespace across all workloads
Capacity	F128 (256 CU)	shared across all workloads

After deployment, POS batch lands in Lakehouse bronze → silver → gold nightly; RTI ingests store IoT in real time into KQL; Warehouse runs the analyst mart with multi-table atomicity; Power BI Direct Lake composes gold + KQL live telemetry + Warehouse mart into one executive dashboard. Every workload sees every other workload's tables via shared metadata. One capacity handles all of it.

Output:

Downstream	Workload origin	Freshness	Consumer
gold.fact_daily_sales	Lakehouse (Spark)	daily	Warehouse + Power BI
mart.fact_sales_summary	Warehouse (T-SQL)	daily	Power BI
live_telemetry	KQL Database (RTI)	sub-second	Power BI + KQL dashboards
raw/pos/	Data Factory Copy	nightly SFTP	Lakehouse bronze
Executive dashboard	Power BI Direct Lake	seconds	executives

Why this works — concept by concept:

Workload split by trade-off, not by taste — Lakehouse for Spark / ML; Warehouse for T-SQL + multi-table ACID; RTI for sub-second time-series; DF for orchestration. Each workload does what it does best; none of them tries to do everything.
OneLake as the shared substrate — every workload writes Delta to OneLake. Cross-workload queries via shared metadata; zero data movement between workloads.
Direct Lake for Power BI — semantic model reads Delta files column-by-column into VertiPaq on demand. No Import refresh, no DirectQuery translation. Freshness follows the underlying tables.
Multi-table atomicity in Warehouse — the analyst mart load runs as BEGIN TRAN ... COMMIT. Half-baked loads never make it to Power BI, which is the load-bearing correctness guarantee for the exec dashboard.
Cost — F128 (256 CU) handles this whole stack for ~$16k/mo reserved. Compared to Databricks Lakehouse + Snowflake + Power BI Premium + separate KQL cluster, the Fabric bill collapses ~4 vendors into 1. O(1) per read across workloads; no cross-workload ETL. The eliminated cost is not just dollars — it's four separate governance surfaces collapsed to one Purview graph.

SQL
Topic — sql
SQL problems on data-warehouse mart loads

Practice →

Streaming Topic — streaming Streaming problems on real-time telemetry

Practice →

4. Compute + pricing — Capacities

F-SKU capacity is the single meter that backs every workload — smoothing, throttling, and Direct Lake all charge from the same pool

The mental model in one line: Fabric capacity is bought as an F-SKU (F2 through F2048), measured in Capacity Units (CU), and every workload — Lakehouse Spark, Warehouse T-SQL, KQL queries, DAX evaluations, Copilot invocations, Data Factory pipeline steps — draws CU-seconds from the same shared pool associated with the workspace; overage triggers smoothing (Fabric averages usage over up to 24 h), then interactive throttling (queries queue), then rejection, with per-second billing under PAYG and up to 40% reserved discounts for annual commits. Every senior Fabric interview probes the capacity math because right-sizing is the single most common Fabric cost mistake.

The F-SKU ladder — what you actually buy.

F2 → F2048. F2 is 4 CU; each rung doubles capacity. F64 is 128 CU (the sweet-spot for mid-market Microsoft shops). F2048 is 4096 CU (enterprise-scale). Ratio 2:1 between F-SKU number and CU.
PAYG. Per-second billing. F64 PAYG is roughly $8k/month if run 24/7.
Reserved. Annual commit for up to ~40% discount. F64 reserved is roughly $5.5k/month.
Trial. Every tenant gets a 60-day trial capacity for evaluation.
Pause. Non-production capacities can auto-pause; billing stops during pause; resume takes ~30s.

The Capacity Unit (CU).

Definition. The abstract billing unit. Every Fabric operation costs some CU-seconds. F64 = 128 CU means you can sustain 128 CU-seconds per second forever — beyond that, smoothing kicks in.
Examples. A Direct Lake page render: ~0.5-2 CU-s. A Spark notebook cell running for 1 minute on 4 cores: ~120 CU-s. A Warehouse T-SQL query scanning 10 GB: ~10-30 CU-s. A Copilot code-gen invocation: ~5-30 CU-s.
The interview probe. "What is a CU?" — required answer: Fabric's abstract billing unit; F64 = 128 CU sustained rate; overage smoothed then throttled.

Smoothing — the 24-hour rolling budget.

What it is. Fabric averages your CU consumption over a smoothing window (up to 24 hours). Short bursts above 128 CU on an F64 don't throttle immediately — they consume "future" CU-seconds from the smoothing window.
When it fires. Interactive workloads (Power BI, notebook cells) get up to 5 minutes of smoothing; background workloads (Spark jobs, Warehouse batch, Data Factory) get up to 24 hours.
The interview probe. "How does capacity handle a burst?" — required answer: smoothed over 5 min (interactive) or 24 h (background); throttled after budget exhausted.

Throttling — what actually happens when the budget runs out.

Interactive throttling. Queries queue with progressively longer delays (20 s, 60 s, 5 min). Users see spinners; work completes eventually.
Background rejection. Spark jobs fail-fast with a throttling error. Notebook cells throw; pipelines fail. This is the "your capacity is full" signal.
Auto-scale. F-SKUs support burst auto-scaling if configured; Microsoft charges scaled CU at PAYG rates.
The interview probe. "What does 'throttled capacity' mean?" — required answer: budget exhausted; interactive queries queue with backoff; background jobs fail; auto-scale kicks in only if enabled.

Copilot in Fabric — same capacity, extra CU drain.

What it does. Text-to-SQL in Warehouse, notebook code generation in Lakehouse, DAX-suggestion in Power BI, KQL-generation in RTI. Each invocation calls a hosted Azure OpenAI model.
The cost. Every Copilot invocation consumes CU-seconds from the same pool. F-SKU minimum F64 is required to enable Copilot in most tenants.
The interview probe. "How is Copilot billed?" — required answer: from the same F-SKU capacity; F64+ minimum; usage counts against your CU budget.

Direct Lake — the Power BI mode that changes capacity sizing.

What it is. Semantic model reads Delta files column-by-column into VertiPaq on demand; no Import refresh; no DirectQuery translation. Sub-second query latency at scale.
The capacity implication. Direct Lake in-memory footprint counts against capacity memory. F64 provides ~10 GB semantic model memory; F128 provides ~20 GB; F256 provides ~40 GB. Exceed the limit and Direct Lake falls back to DirectQuery (slow).
The interview probe. "How do I size capacity for a big semantic model?" — required answer: match F-SKU to model memory (F64 for ≤10 GB, F128 for ≤20 GB); over-limit falls back to DirectQuery.
Sizing rules of thumb. Warehouse: F64 handles ~200 concurrent analyst users. Lakehouse Spark: F64 gives roughly 32 Spark v-cores burst. KQL: F64 gives 2-3 typical KQL Database compute units. DAX (Direct Lake): F64 handles ~500 QPS for small semantic models.

Common interview probes on capacity.

"What's the smallest F-SKU that supports Copilot?" — F64 (in most 2026 tenants).
"What happens when I exceed capacity?" — smoothing → interactive throttling → background rejection.
"How do I size F-SKU for a workload?" — measure existing Spark v-cores, Warehouse concurrency, and Direct Lake model size; match to F-SKU CU + memory.
"What's per-second billing?" — Fabric charges per-second under PAYG; you can pause non-prod capacities.
"How does Direct Lake affect capacity choice?" — semantic model memory is capacity-bound; oversize forces DirectQuery fallback.

Worked example — CU-second budget for a mid-sized workload

Detailed explanation. A team wants to size Fabric capacity for a mid-sized workload: 20 analysts on Warehouse, 200 concurrent Power BI users on a 15 GB Direct Lake semantic model, 5 Spark ETL jobs running 30 min/day each, and 1M Copilot invocations/month. Walk through the CU math and pick the F-SKU.

Warehouse. 20 analysts × 200 queries/day × ~15 CU-s per query = 60k CU-s/day.
Power BI Direct Lake. 200 users × 100 renders/day × ~1 CU-s per render = 20k CU-s/day.
Spark ETL. 5 jobs × 30 min × 4 cores × 30 CU-s/core-min = 18k CU-s/day.
Copilot. 1M/month ÷ 30 = 33k/day × 10 CU-s each = 333k CU-s/day.
Total. ~430k CU-s/day = ~5 CU sustained. Peak concurrency drives sizing higher.

Question. Compute the sustained + peak CU load and pick the F-SKU.

Input.

Workload	Daily CU-s	Peak concurrency
Warehouse (20 analysts)	60,000	5-10 CU
Power BI Direct Lake (200 users)	20,000	40-80 CU
Spark ETL (5 jobs)	18,000	60-100 CU (during runs)
Copilot (1M/mo)	333,000	20-40 CU
Total daily	~430,000	peak burst ~180 CU

Code.

# CU-budget helper — right-size the F-SKU
def size_fabric_capacity(daily_cu_seconds: float,
                          peak_burst_cu:     float,
                          direct_lake_gb:    float) -> dict:
    """Recommend the smallest F-SKU that fits both sustained CU and Direct Lake GB."""

    # Sustained CU floor: daily_cu_s / 86400 seconds
    sustained = daily_cu_seconds / 86400.0

    # F-SKU CU pool: F<n> = 2n CU (F2=4, F64=128, ...)
    F_SKUS = [(f, 2 * f) for f in (2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, 2048)]

    # Direct Lake memory per F-SKU (approx): F<n>_mem_gb = n / 6.4
    def dl_mem(f: int) -> float:
        return f / 6.4

    # Find smallest F-SKU that satisfies both sustained CU and DL memory
    for f_num, cu in F_SKUS:
        if cu >= peak_burst_cu * 0.75 and dl_mem(f_num) >= direct_lake_gb:
            return {
                "f_sku":       f"F{f_num}",
                "cu_pool":     cu,
                "dl_memory":   dl_mem(f_num),
                "sustained":   sustained,
                "peak_burst":  peak_burst_cu,
                "monthly_paygo_est": f_num * 125,        # ~$125/F-SKU-unit/month PAYG
                "monthly_reserved_est": f_num * 88,       # ~30% off
            }
    raise ValueError("no F-SKU large enough; consider F2048 or split workspace")


rec = size_fabric_capacity(daily_cu_seconds=430_000, peak_burst_cu=180, direct_lake_gb=15)
print(rec)
# → {'f_sku': 'F128', 'cu_pool': 256, 'dl_memory': 20.0, 'sustained': 4.98,
#    'peak_burst': 180, 'monthly_paygo_est': 16000, 'monthly_reserved_est': 11264}

Step-by-step explanation.

Compute sustained CU: 430k CU-s/day ÷ 86400 s/day ≈ 5 CU sustained. This is trivial — any F-SKU above F4 handles it. Sustained CU is almost never the sizing driver.
Compute peak burst: ~180 CU during simultaneous ETL + heavy analyst window. Smoothing absorbs 24 h averages, but sustained peak >75% of pool for extended periods still causes throttling. Rule of thumb: pool should be ≥1.33× peak burst.
Compute Direct Lake memory: 15 GB semantic model requires ~F128 (20 GB budget) to avoid DirectQuery fallback. This is often the actual sizing driver — DL memory is far more restrictive than sustained CU.
Pick F128: 256 CU pool, 20 GB DL memory, ~$16k/mo PAYG or ~$11k/mo reserved. F64 would work for sustained CU but only gives ~10 GB DL — over-limit forces DirectQuery, which typically degrades user experience 5-10×.
If the Direct Lake model grew to 30 GB, upgrade to F256 (40 GB); if the workload stayed at 15 GB but ETL grew to 300 CU peak, upgrade to F256 (512 CU). Track both metrics; the tighter constraint wins.

Output.

Metric	Computed	Chosen F-SKU
Sustained CU	5	F4+ (trivial)
Peak burst CU	180	F128 (256 CU pool)
Direct Lake GB	15	F128 (20 GB DL memory)
Chosen F-SKU	F128	256 CU + 20 GB DL
Monthly cost (PAYG)	$16k
Monthly cost (reserved)	$11k

Rule of thumb. Size Fabric capacity to (max of peak burst CU and Direct Lake memory), not sustained. Direct Lake memory is often the tighter constraint. Use the CU-budget helper before committing to a reserved SKU — reserved discount evaporates if you have to upgrade mid-term.

Worked example — capacity throttling incident and runbook

Detailed explanation. A team runs an F64 capacity. On Tuesday afternoon the exec dashboard starts loading in 20-30 s instead of the usual 2 s. Slack lights up. The on-call inspects the Fabric Capacity Metrics App and sees a throttling event. Walk through the diagnosis and the fix.

Symptom. Power BI reports slow; Warehouse queries timing out; Spark jobs failing with "background throttling."
Root cause. A new mass-Copilot rollout consumed ~150 CU sustained across the smoothing window; combined with normal load pushed the smoothing budget past exhaustion.
Immediate fix. Bump capacity to F128 (temporary PAYG); investigate Copilot usage; scope-limit Copilot to a smaller user set.

Question. Walk through the incident runbook — detection, diagnosis, mitigation, prevention.

Input.

Phase	Signal / action
Detection	Capacity Metrics App shows "throttling" status
Diagnosis	Top consumers view lists Copilot as #1
Mitigation	Temporary F-SKU bump (F64 → F128)
Root cause	40 new users granted Copilot access same morning
Prevention	Scope Copilot policy; monitor CU per user

Code.

# Capacity Metrics App — enable and configure alerts
capacity_metrics:
  workspace: retail-analytics-prod
  alerts:
    - name: capacity_throttling
      condition: CU_smoothed_pct > 90
      duration: 10m
      channel: pagerduty:data-platform-oncall
    - name: throttling_active
      condition: throttling_state == 'interactive_delay'
      duration: 1m
      channel: pagerduty:data-platform-oncall
    - name: dl_memory_pressure
      condition: direct_lake_memory_pct > 85
      duration: 15m
      channel: slack:data-platform

# On-call — temporary capacity bump via Fabric PowerShell / CLI
# (Fabric portal action; illustrative)
Set-FabricCapacity `
  -CapacityId "<f64-capacity-guid>" `
  -Sku "F128" `
  -Reason "Copilot throttling incident 2026-07-28"

# Investigate top consumers
Get-FabricCapacityMetrics `
  -CapacityId "<f64-capacity-guid>" `
  -Window "24h" `
  -TopConsumers 10

-- Post-incident review — CU consumption by user via Capacity Metrics
SELECT user_upn,
       operation_type,
       SUM(cu_seconds) AS total_cu_s,
       COUNT(*)        AS operation_count
FROM   capacity_metrics.operations
WHERE  operation_ts >= '2026-07-28 00:00:00'
  AND  operation_ts <  '2026-07-29 00:00:00'
GROUP  BY user_upn, operation_type
ORDER  BY total_cu_s DESC
FETCH  FIRST 20 ROWS ONLY;

Step-by-step explanation.

Detection: Capacity Metrics App alerts fire when CU_smoothed_pct > 90 for 10 min. This is early warning — throttling hasn't started yet; you have ~30-60 min to react before user-visible impact.
Diagnosis: the "Top Consumers" view of the Capacity Metrics App ranks workloads and users by CU-s consumed. Copilot invocations show up as their own operation type — this incident had 40 new users each running ~500 Copilot calls in 4 h, ~10 CU-s each = 200k CU-s in 4 h = ~14 CU sustained added on top of ~110 CU baseline. Total 124 CU sustained on a 128 CU pool → smoothing budget consumed in <2 h.
Mitigation: temporarily bump to F128 via Fabric PowerShell / portal. This is a PAYG bill for the incident duration; typically a few hours costs ~$50-$100. Immediate user impact resolved.
Root cause: the org-wide Copilot rollout that morning turned on 40 new users simultaneously. Nobody sized the capacity for the CU impact. Prevention: gate Copilot behind a group policy that requires admin approval; monitor CU per user weekly.
Post-incident: build the CU-per-user query as a scheduled report. Set alerts for any user exceeding 5000 CU-s/day. If Copilot is the primary CU driver, consider a dedicated capacity for the Copilot workspace so throttling there doesn't affect analyst reporting.

Output.

Phase	Time	Action
T+0	Alert fires	Capacity Metrics smoothing budget 90%
T+30	On-call opens metrics app	Copilot identified as #1 consumer
T+45	Bump F64 → F128 (PAYG)	Users unblocked
T+2h	Copilot policy scoped	Access restricted to 10 approved users
T+24h	Revert to F64	PAYG bump cost ~$60
Post	Add CU-per-user report	Weekly monitoring

Rule of thumb. Capacity Metrics App is not optional — it's the only way to diagnose throttling. Every Fabric deployment must have alerts on smoothing % and Direct Lake memory %. The temporary F-SKU bump is a legitimate mitigation; the permanent fix is either right-sizing or workload isolation.

Worked example — Direct Lake vs Import vs DirectQuery decision

Detailed explanation. For every new Power BI semantic model built on Fabric, the choice between Direct Lake, Import, and DirectQuery affects capacity, freshness, and user experience. Walk through the decision for three real scenarios.

Scenario A. 10 GB semantic model, 500 users, sub-second latency required, data refresh every 15 min → Direct Lake.
Scenario B. 50 GB semantic model, 200 users, sub-second latency, freshness daily → Import (cache in memory).
Scenario C. 2 GB semantic model, 20 users, freshness real-time (seconds), data lives in RTI KQL Database → DirectQuery to KQL.

Question. Pick the mode per scenario and justify the trade-offs.

Input.

Scenario	Size	Users	Latency	Freshness	Mode
A: mid-market retail exec dashboard	10 GB	500	sub-second	15 min	Direct Lake
B: large-model financial reporting	50 GB	200	sub-second	daily	Import
C: real-time IoT ops	2 GB	20	sub-second	seconds	DirectQuery to KQL

Code.

Decision tree — Power BI mode selection on Fabric

Q1. Does data live in OneLake Delta (Lakehouse / Warehouse)?
    yes → Q2
    no  → Q4

Q2. Does the semantic model fit in F-SKU DL memory?
    yes → **Direct Lake** (best of both worlds — fresh + fast)
    no  → Q3

Q3. Is freshness > 1 hour tolerated?
    yes → **Import** (cache in memory; refresh on schedule)
    no  → **DirectQuery to Warehouse** (fresh but slower)

Q4. Is data in KQL Database (RTI)?
    yes → **DirectQuery to KQL** (fresh; KQL engine handles the load)
    no  → **DirectQuery to source** (Snowflake / SQL Server / ...)

# Semantic-model manifest — Direct Lake mode (scenario A)
model:
  name: retail_exec_dashboard
  mode: DirectLake
  storage:
    type: OneLake
    workspace: retail-analytics-prod
    lakehouse: retail_lakehouse
  tables:
    - name: fact_daily_sales
      source: retail_lakehouse.gold.fact_daily_sales
      partitions:
        - name: current
          source: "SELECT * FROM gold.fact_daily_sales WHERE sale_date >= DATEADD(YEAR, -1, GETDATE())"
    - name: dim_product
      source: retail_lakehouse.gold.dim_product
    - name: dim_store
      source: retail_lakehouse.gold.dim_store
  memory_target_gb: 10
  fallback_mode: DirectQuery   # if memory pressure, fall back rather than fail

Step-by-step explanation.

Scenario A (10 GB, 500 users, 15-min freshness) → Direct Lake wins hands down. Sub-second latency from VertiPaq in-memory scans; 15-min freshness matches OneLake write cadence; memory fits comfortably in F128 DL budget. No refresh job needed — Direct Lake reads current Delta snapshot on demand.
Scenario B (50 GB, 200 users, daily freshness) → Import wins. 50 GB exceeds even F256 DL memory (~40 GB) so Direct Lake would fall back to DirectQuery. Import copies the model into premium capacity memory (compressed VertiPaq typically 3-5×; 50 GB → ~12 GB in memory), refreshed nightly.
Scenario C (2 GB, 20 users, real-time) → DirectQuery to KQL. KQL Database handles the sub-second scan natively; Power BI just orchestrates the visuals. Direct Lake would work if the KQL table were mirrored to OneLake, but the mirror adds ~1-2 min freshness lag — unacceptable for real-time ops.
The fallback_mode: DirectQuery on the Direct Lake manifest is the safety net: if memory pressure grows (e.g. a new large table added), Direct Lake gracefully degrades to DirectQuery instead of failing. Users see slower queries, not errors. Always set this.
Never mix modes within the same semantic model unless you fully understand the "composite model" trade-offs (multi-source latency skew, RLS complexity, DAX behaviour). One mode per model is the safe default.

Output.

Scenario	Mode	Latency	Freshness	Memory
A: retail exec	Direct Lake	sub-second	15 min	F128 DL (fits in 20 GB)
B: financial 50 GB	Import	sub-second	daily	Premium cache (12 GB compressed)
C: real-time IoT	DirectQuery to KQL	sub-second	seconds	KQL engine handles

Rule of thumb. Default to Direct Lake for any OneLake-native model that fits in DL memory. Fall back to Import when the model exceeds DL memory but freshness tolerates a refresh window. Use DirectQuery only when data lives outside OneLake or freshness must be real-time. Always set fallback_mode: DirectQuery on Direct Lake models — it's free insurance.

Senior interview question on Fabric capacity

A senior interviewer might ask: "Your organisation is rolling out Fabric to 5 business domains — retail, finance, HR, marketing, and product. Each has different workloads, users, and SLAs. Walk me through the capacity strategy: one big shared capacity vs per-domain capacities, PAYG vs reserved, sizing methodology, and the throttling defense plan."

Solution Using per-domain capacities + reserved base + PAYG burst headroom + throttling alerts

# Per-domain capacity strategy
domains:
  retail:
    capacity_reserved: F128    # 256 CU; 200 analysts + Direct Lake + Copilot
    capacity_burst_paygo: F64  # scale to F192-equivalent for peak Tuesday afternoon
    monthly_reserved_cost: 11264
  finance:
    capacity_reserved: F64     # 128 CU; smaller analyst count; strict multi-table Warehouse
    capacity_burst_paygo: F32
    monthly_reserved_cost: 5632
  hr:
    capacity_reserved: F16     # 32 CU; small user base; batch-only
    monthly_reserved_cost: 1408
  marketing:
    capacity_reserved: F32     # 64 CU; Direct Lake + moderate Copilot
    monthly_reserved_cost: 2816
  product:
    capacity_reserved: F64     # 128 CU; Spark ML-heavy + RTI IoT
    monthly_reserved_cost: 5632

total_monthly_reserved: 26752   # ~$27k/mo baseline

# Sizing methodology per domain
def size_domain_capacity(analyst_count:      int,
                          pbi_dl_gb:          float,
                          spark_jobs_per_day: int,
                          copilot_users:      int) -> str:
    # Rough CU sustain from each source
    analyst_cu     = analyst_count * 0.02          # 0.02 CU per analyst sustained
    pbi_cu         = pbi_dl_gb     * 3.0           # 3 CU per GB DL sustained
    spark_cu       = spark_jobs_per_day * 0.5      # 0.5 CU per daily job sustained
    copilot_cu     = copilot_users * 0.8           # 0.8 CU per active copilot user sustained

    sustained = analyst_cu + pbi_cu + spark_cu + copilot_cu
    peak      = sustained * 3.0                    # 3× peak-to-mean ratio

    # DL memory constraint dominates for large models
    dl_constraint_f_sku = max(2, int(pbi_dl_gb * 6.4 / 2) * 2)   # round up

    # CU pool constraint
    cu_constraint_f_sku = max(2, int(peak / 2) * 2)              # F_num = CU_pool / 2

    f_num = max(dl_constraint_f_sku, cu_constraint_f_sku)
    return f"F{f_num}"


print(size_domain_capacity(analyst_count=200, pbi_dl_gb=15, spark_jobs_per_day=10, copilot_users=40))
# → F128

# Throttling defense — Capacity Metrics App alerts per domain
capacity_alerts:
  - domain: retail
    alerts:
      - metric: CU_smoothed_pct
        threshold: 85
        window: 10m
        action: pagerduty:retail-oncall
      - metric: dl_memory_pct
        threshold: 90
        window: 15m
        action: slack:retail-data
  # ... per-domain

burst_budget:
  policy: allow_paygo_burst
  max_burst_hours_per_month: 8
  budget_alert_threshold_usd: 500

Step-by-step trace.

Domain	F-SKU (reserved)	Monthly cost	Rationale
Retail	F128	$11.3k	200 analysts + 15 GB DL + Spark ETL + Copilot
Finance	F64	$5.6k	100 analysts + multi-table WH + 5 GB DL
HR	F16	$1.4k	20 users + batch only
Marketing	F32	$2.8k	50 users + Direct Lake + moderate Copilot
Product	F64	$5.6k	Spark ML-heavy + RTI IoT
Total		$26.7k	vs ~$85k for one giant F512

After rollout, each domain has isolated CU budget — retail throttling doesn't affect finance dashboards. Reserved commits save ~30% vs PAYG. PAYG burst headroom of 8h/month per domain covers Copilot rollouts and quarterly-close spikes without permanent oversize. Capacity Metrics App alerts every domain's on-call before throttling starts.

Output:

Concern	Answer	Value
Baseline cost	$26.7k/mo (reserved)	vs $85k for one F512
Isolation	per-domain capacity	retail spike doesn't affect finance
Burst headroom	8h PAYG/month/domain	covers Copilot rollouts, quarterly close
Throttling defense	Capacity Metrics alerts	pre-throttling warning
Right-sizing methodology	CU-per-analyst + DL-per-GB heuristics	reproducible sizing
Governance	one Purview + one identity	despite 5 capacities

Why this works — concept by concept:

Per-domain capacity — isolation is the load-bearing feature. Retail's Copilot rollout can't throttle finance's month-end close. One capacity per domain scales the blast radius, not the whole tenant.
Reserved + PAYG burst — reserved commits save 30% on baseline; PAYG burst covers spikes without permanent oversize. The 8h/month cap prevents burst from becoming the norm.
Sizing methodology — CU-per-analyst (0.02), CU-per-DL-GB (3.0), CU-per-Spark-job (0.5), CU-per-Copilot-user (0.8) heuristics give reproducible sizing. Peak-to-mean 3× multiplier accounts for concurrent load.
Capacity Metrics alerts — pre-throttling warning at 85% smoothed CU gives ops time to react (bump capacity, throttle Copilot, reschedule Spark). Alerts every domain independently; on-call rotations don't cross domains.
Cost — $26.7k/mo baseline vs ~$85k for one giant F512 that would hold the peak of every domain simultaneously. Per-domain saves ~$700k/year. Compared to Databricks + Snowflake per-workload billing, Fabric per-domain capacity is dramatically simpler to reason about — one meter per domain, not per workload.

Design
Topic — design
Design problems on capacity planning

Practice →

SQL Topic — sql SQL problems on cost governance

Practice →

5. Fabric vs Databricks vs Snowflake vs Synapse + interview signals

The four-way decision matrix — pick per workload; there is no single winner

The mental model in one line: Fabric, Databricks, Snowflake, and legacy Azure Synapse are the four platform choices senior data engineers evaluate for a modern lakehouse + warehouse + BI stack, and there is no single "winner" — Fabric wins for Microsoft-shop teams with Power BI Direct Lake and one-portal SaaS convenience, Databricks wins for cross-cloud + ML-heavy + Delta-origin depth, Snowflake wins for pure SQL warehouse + Cortex AI + cross-cloud governance, and Synapse persists only for legacy dedicated-SQL-pool commitments that haven't yet migrated to Fabric. Every senior interview probes this comparison because the decision compounds across storage, compute, governance, IAM, and cost — and getting it wrong locks the organisation into a re-platform two years later.

The four platforms at a glance.

Microsoft Fabric. SaaS portal, OneLake storage, four workloads (Lakehouse + Warehouse + RTI + DF), F-SKU capacity, Direct Lake for Power BI, Copilot. Azure-only. Best fit: Microsoft-shop, Power BI-heavy, T-SQL analysts.
Databricks. Lakehouse-native, Delta origin, Unity Catalog governance, cross-cloud (AWS + Azure + GCP), MLflow-native, Databricks SQL. Best fit: ML-heavy, cross-cloud, Delta-origin depth.
Snowflake. Pure SQL warehouse origin, evolved into Iceberg + Snowpark + Cortex AI, cross-cloud governance via Horizon. Best fit: SQL-warehouse-first, cross-cloud, heavy semi-structured JSON.
Azure Synapse Analytics. The Fabric predecessor. Dedicated SQL pool + Serverless SQL + Apache Spark + Data Explorer + Data Factory. Still exists; being subsumed by Fabric.

Fabric wins when …

Organisation is a Microsoft 365 shop with Entra IDs + Power BI Premium licensing sunk cost.
Power BI Direct Lake is a differentiator — sub-second dashboard latency over Delta without Import refresh is a game-changer for exec dashboards.
Users skew T-SQL analysts who prefer Warehouse over Spark notebooks.
One-portal SaaS convenience matters more than best-of-breed component depth.
Cloud is Azure-only for regulatory or existing-commitment reasons.

Databricks wins when …

Workload is multi-cloud or expected to move between clouds.
ML-heavy — MLflow, feature store, Mosaic AI, model serving — is central to the workload.
Delta Lake depth matters — you're doing OPTIMIZE ZORDER, deletion vectors, liquid clustering, CDF-heavy patterns.
Unity Catalog governance across clouds is a hard requirement.
Team culture is notebook-first and Spark-native.

Snowflake wins when …

Workload is 90%+ SQL warehouse with heavy semi-structured JSON.
Cortex AI (in-warehouse LLM functions, vector search) is a fit for the workload.
Cross-cloud governance via Horizon is a hard requirement.
Team culture is SQL-first — no Spark, no notebooks, no Python-in-warehouse ambition.
Iceberg-native (via Snowflake-managed Iceberg tables) is a strategic choice.

Legacy Synapse persists when …

A multi-year dedicated SQL pool commitment hasn't yet expired.
Serverless SQL pools are running production queries that haven't been migrated.
Migration to Fabric is planned but not yet executed — Fabric is the roadmap; Synapse is the current reality.

The interview probes senior engineers get.

OneLake shortcut semantics. "Can I query my existing S3 lake from Fabric?" — yes, shortcut; reads flow through.
Capacity throttling. "What happens when I exceed F-SKU capacity?" — smoothing → interactive throttling → background rejection.
Direct Lake vs Import. "When would you pick each?" — Direct Lake for freshest data + fits in memory; Import for large models + tolerated refresh window.
F-SKU sizing. "How do I pick between F64 and F128?" — measure peak burst CU and DL model memory; the tighter constraint wins.
Fabric vs Databricks migration. "When would I move off Fabric?" — when the organisation goes multi-cloud, or ML workloads outgrow Fabric Data Science's MLflow surface.

Common interview probes on the four-way comparison.

"Fabric vs Databricks — pick one for a Microsoft shop." — Fabric, unless multi-cloud + ML-heavy.
"Fabric vs Snowflake — pick one for a warehouse-first workload." — depends on Power BI use; Fabric with Direct Lake if Power BI is heavy, Snowflake otherwise.
"Is Synapse dead?" — subsumed by Fabric; legacy commitments run out; greenfield goes to Fabric.
"When would you go multi-vendor?" — when workload heterogeneity makes single-vendor a lossy fit; the operational tax is real.

Worked example — the four-vendor comparison table for a real workload

Detailed explanation. A senior architect for a mid-sized SaaS company evaluates a re-platform from Redshift + Tableau to something modern. The company runs AWS-primary, has ~200 analysts on Tableau, a small ML team on SageMaker, and a Snowflake trial that's proving popular. Walk through the four-way comparison and pick.

Current stack. AWS Redshift (2-year commit, $180k/year), Tableau Server ($80k/year), SageMaker ($60k/year), Airflow on EKS.
Constraints. AWS-primary; considering multi-cloud in 3 years; SOC 2; ~$400k/year data-platform budget.
Users. 200 SQL analysts on Tableau, 10 data scientists on SageMaker, 5 data engineers on Airflow.
Requirements. Sub-second Tableau dashboards; ML team wants MLflow; cost-neutral re-platform.

Question. Build the four-way comparison and pick the platform.

Input.

Axis	Fabric	Databricks	Snowflake	Synapse
Storage	OneLake (Delta)	Delta on cloud storage	Iceberg + native FDN	ADLS Gen2
Compute	Lakehouse/Warehouse/RTI/DF	Spark + SQL + ML	SQL + Snowpark	Dedicated SQL + Spark
BI story	Power BI Direct Lake	Databricks SQL + third-party BI	third-party BI	third-party BI
ML story	Fabric Data Science (MLflow-compat)	MLflow-native	Snowpark + Cortex	Spark MLlib
Governance	Purview + workspace RBAC	Unity Catalog	Horizon	Purview
Cross-cloud	Azure-only	AWS + Azure + GCP	AWS + Azure + GCP	Azure-only
Best fit	Microsoft-shop + Power BI	Multi-cloud + ML	SQL-warehouse + Cortex	Legacy

Code.

# Simple scoring rubric — weight axes by requirement priority
weights = {
    "cost":           0.25,
    "sub_second_bi":  0.20,
    "ml_depth":       0.15,
    "cross_cloud":    0.20,
    "sql_analyst_ux": 0.20,
}

scores = {   # 1-5 per axis per platform
    "Fabric": {
        "cost": 4, "sub_second_bi": 5, "ml_depth": 3, "cross_cloud": 1, "sql_analyst_ux": 4
    },
    "Databricks": {
        "cost": 3, "sub_second_bi": 3, "ml_depth": 5, "cross_cloud": 5, "sql_analyst_ux": 3
    },
    "Snowflake": {
        "cost": 3, "sub_second_bi": 4, "ml_depth": 3, "cross_cloud": 5, "sql_analyst_ux": 5
    },
    "Synapse": {
        "cost": 2, "sub_second_bi": 3, "ml_depth": 2, "cross_cloud": 1, "sql_analyst_ux": 4
    },
}

def weighted_score(platform: str) -> float:
    return sum(scores[platform][axis] * w for axis, w in weights.items())

for p in scores:
    print(f"{p}: {weighted_score(p):.2f}")

# → Fabric: 3.20
# → Databricks: 3.90
# → Snowflake: 4.05
# → Synapse: 2.20

Step-by-step explanation.

Weight axes by requirement priority. This SaaS company weighs cost (25%), cross-cloud future (20%), sub-second BI (20%), SQL analyst UX (20%), ML depth (15%). Weights sum to 1.0.
Score each platform 1-5 on each axis. Fabric loses on cross-cloud (Azure-only) but wins on sub-second BI (Direct Lake). Databricks wins ML + cross-cloud but middling on sub-second BI. Snowflake wins SQL analyst UX + cross-cloud + moderate ML. Synapse loses everywhere except cost-familiar-legacy.
Compute weighted scores: Snowflake 4.05, Databricks 3.90, Fabric 3.20, Synapse 2.20. For this SaaS company, Snowflake wins narrowly on the cross-cloud + SQL-analyst combo. If the same workload were Microsoft-shop, Fabric's cross-cloud score would matter less and its Direct Lake + Power BI score would dominate.
The scoring rubric is not automatic — it's a debate tool. Executives can adjust weights (e.g. "cross-cloud is a hard requirement" → weight to 40% → Databricks wins). The value is making trade-offs explicit rather than picking on brand.
For the Microsoft-shop scenario earlier, the same rubric with different weights would put Fabric at 4.5 and Databricks at 3.5. Same platforms; different fit. This is the whole point — vendor choice is workload-dependent.

Output.

Platform	Weighted score	Wins
Snowflake	4.05	cross-cloud + SQL analyst
Databricks	3.90	ML depth + cross-cloud
Fabric	3.20	sub-second BI (Direct Lake)
Synapse	2.20	legacy commitment only

Rule of thumb. Every re-platform decision needs a scoring rubric with explicit weights. Never pick a platform because "everyone's on it" or "Microsoft is our vendor" — pick because the weighted score falls out of the constraints. Rebuild the rubric per workload; the same organisation can rationally pick Fabric for BI and Databricks for ML.

Worked example — a Fabric answer that separates senior from staff

Detailed explanation. In a senior-vs-staff panel interview, the same question — "how would you compare Fabric to Databricks?" — gets very different answers. Walk through the pattern that separates the two levels.

Senior answer. Names the four workloads, OneLake, Direct Lake, capacity, and when each vendor wins. Roughly 5 minutes; declarative.
Staff answer. Adds a scoring rubric, weights per requirement, and gives a specific pick for the interviewer's imagined workload. Roughly 8 minutes; deeply analytical.

Question. Draft the staff-level answer.

Input.

Level	Signal
Senior	names workloads + Direct Lake + capacity trade-offs
Staff	senior + explicit scoring rubric + workload-specific pick + when to switch

Code.

Staff-level Fabric vs Databricks answer (8 minutes)
====================================================

Minute 1-2 — the framework
  "Vendor choice is workload-dependent, so let me name the axes I score against —
   cost, sub-second BI, ML depth, cross-cloud reach, SQL analyst UX, governance
   surface, and single-vendor risk — and then I'll walk through Fabric and
   Databricks per axis."

Minute 3 — Fabric strengths
  "Fabric wins on sub-second BI (Direct Lake in-memory over Delta), Microsoft-shop
   convenience (one Entra + one Power BI Premium bill), F-SKU capacity simplicity
   (one meter across workloads), and T-SQL analyst UX (Warehouse feels like SQL
   Server). Loses on cross-cloud (Azure-only) and ML depth (Data Science surface
   is thinner than MLflow-native Databricks)."

Minute 4 — Databricks strengths
  "Databricks wins on cross-cloud (AWS + Azure + GCP), ML depth (MLflow-native,
   Mosaic AI, feature store), Delta origin (deletion vectors, liquid clustering,
   Row Filters + Column Masks), and Unity Catalog governance across clouds. Loses
   on Power BI Direct Lake (Databricks SQL is good but not the same) and Microsoft-
   shop tax (extra IAM + BI licensing hoops)."

Minute 5-6 — the pick for the imagined workload
  "For an Azure-heavy shop with 200 Power BI analysts and moderate ML, Fabric wins
   decisively — Direct Lake alone is worth the switching cost. For a cross-cloud
   shop with 50 data scientists and moderate BI, Databricks wins — the ML depth
   and Unity Catalog compound. For 90% pure SQL warehouse, Snowflake is often the
   better pick than either."

Minute 7 — when to switch
  "You'd migrate off Fabric if you go multi-cloud, or ML grows beyond Fabric Data
   Science's MLflow-compat surface. You'd migrate off Databricks if the org
   consolidates on Microsoft 365 + Power BI Premium and Direct Lake becomes
   critical. Both migrations are painful; treat vendor commitment as a 3-year
   decision, not a 3-month one."

Minute 8 — governance closer
  "Regardless of pick, the governance surface (Purview, Unity, Horizon) is often
   the tighter constraint than the compute engine. Compute is fungible; governance
   is not. Score the governance model against your audit obligations first;
   compute follows."

Step-by-step explanation.

Framework first, per-vendor second. Senior candidates dive into vendor strengths; staff candidates name the scoring axes first, then apply them. This signals decision-making maturity.
Fabric strengths: sub-second BI (Direct Lake), Microsoft-shop tax alignment, one-portal SaaS, T-SQL analyst UX. Every strength named with a mechanism (Direct Lake, F-SKU, one Entra), not a slogan ("integrated experience").
Databricks strengths: cross-cloud, ML depth, Delta origin, Unity Catalog. Same pattern — strengths named with mechanisms (MLflow-native, deletion vectors, cross-cloud UC).
Pick for the imagined workload: give a specific answer, not "it depends." Staff candidates propose "Azure-heavy + Power BI + moderate ML → Fabric" as a firm recommendation with the reasoning attached.
When to switch: name the escape conditions up front. This is the staff-level tell — anticipating the "but what if..." follow-up before it comes.

Output.

Level	Names framework	Names mechanisms	Gives specific pick	Names switch conditions
Senior	sometimes	yes	sometimes	rare
Staff	mandatory	mandatory	mandatory	mandatory

Rule of thumb. For staff-level interviews, front-load the scoring framework, name mechanisms not slogans per vendor, give a specific workload-conditioned pick, and pre-empt the switch-conditions question. Governance-first closing is a bonus senior signal — compute is fungible, governance is not.

Worked example — the "Fabric is Microsoft-only" objection

Detailed explanation. A common interview trap: the interviewer probes whether you understand Fabric's cross-cloud story. The naive answer is "Fabric is Azure-only." The senior answer explains OneLake shortcuts to S3 / GCS, mirroring from Snowflake / Databricks, and the actual data-gravity constraints. Walk through the pattern.

Trap question. "Isn't Fabric just Azure-only? Doesn't that make it a dead end for us?"
Naive answer. "Yeah, Fabric is Azure-only."
Senior answer. "Compute is Azure-only; data can live anywhere via OneLake shortcuts + mirroring. Trade-off is cross-cloud egress + latency; not a dead end for many workloads."

Question. Draft the senior answer that addresses the trap head-on.

Input.

Aspect	Reality
Fabric compute region	Azure regions only
OneLake storage	Azure regions only
Shortcuts	can point to S3, GCS, ADLS Gen2 in any cloud
Mirroring	supports Snowflake, Databricks, Cosmos DB, Azure SQL sources
Egress cost	applies for cross-cloud shortcut reads
Latency	applies for cross-cloud shortcut reads (typically 20-200ms extra)

Code.

Senior answer to the "Fabric is Azure-only" objection
=====================================================

"Fabric's compute is Azure-only — that's true. But 'compute' and 'data' are separable
in Fabric.

Data can live in S3 or GCS via OneLake shortcuts; Fabric reads through without
copying. That means an existing AWS lake stays put; Fabric queries in place.
Egress cost applies for cross-cloud reads, and latency adds 20-200 ms per query
hop — but for BI dashboards and analyst SQL, that's often fine.

For live data in Snowflake, Databricks, Cosmos DB, or Azure SQL, Fabric mirroring
continuously replicates into OneLake with minimal setup — the source of truth stays
put; Fabric gets a Delta copy for Direct Lake and cross-workload queries.

So Fabric being 'Azure-only' matters mainly for two things:
  1. Compute must run in Azure — which is fine if you're a Microsoft shop.
  2. Highly egress-sensitive workloads (100 TB moved daily across clouds) get
     expensive fast — those stay on their native cloud.

For most enterprise workloads, Fabric's data-cross-cloud story is workable via
shortcuts + mirroring, and the Direct Lake + one-capacity + one-Purview wins
outweigh the compute-region constraint."

Step-by-step explanation.

Acknowledge the objection directly — "compute is Azure-only, that's true." Never dodge the question; the interviewer is testing whether you handle uncomfortable trade-offs honestly.
Draw the compute-vs-data distinction. This is the senior insight: Fabric's compute region is Azure, but OneLake shortcuts let data live in S3 / GCS with reads flowing through.
Name the specific mechanisms: OneLake shortcuts for external lakes; Fabric mirroring for external DBs. Both are named features, not hand-waves.
Quantify the trade-off: egress costs + 20-200ms latency for cross-cloud reads. Concrete numbers signal engineering depth.
Land the "when it matters" case: highly egress-sensitive workloads (100 TB/day cross-cloud) stay native. For BI + analyst SQL + moderate ETL, cross-cloud shortcuts are workable.

Output.

Aspect	Naive answer	Senior answer
Frames the objection	"yeah, Azure-only"	"compute is Azure; data is cross-cloud via shortcuts + mirroring"
Names mechanisms	none	OneLake shortcuts + Fabric mirroring
Quantifies trade-off	absent	egress + 20-200ms latency
Names when it matters	absent	high-egress workloads stay native

Rule of thumb. The best answer to a trap question separates the true constraint (compute region) from the perceived constraint (data location). For Fabric, that's compute-Azure but data-cross-cloud via shortcuts and mirroring. Named mechanisms + quantified trade-offs = senior signal.

Senior interview question on Fabric vs peers

A senior interviewer might ask: "Your organisation runs Databricks on AWS for the ML team and Snowflake for the SQL analysts. Leadership wants to consolidate to save cost and simplify governance. Walk me through the four-way comparison (Fabric, Databricks, Snowflake, staying multi-vendor), the decision framework, and the migration path if consolidation makes sense."

Solution Using a scored decision framework + phased consolidation plan

# Decision framework — scored comparison for this org's actual workload
scenario = {
    "org_type": "SaaS, AWS-primary, ~500 employees",
    "current_stack": {
        "databricks": {"monthly": 45000, "users_ml": 30, "users_sql": 50},
        "snowflake":  {"monthly": 38000, "users_sql": 150, "storage_tb": 80},
        "tableau":    {"monthly":  8000, "users": 200},
    },
    "requirements": {
        "cost_reduction_pct":    0.20,   # 20% cost cut goal
        "ml_workload_share":     0.30,
        "sql_workload_share":    0.60,
        "bi_workload_share":     0.10,
        "multi_cloud_future":    True,   # planning GCP in 3 years
    },
}

weights = {
    "cost_reduction": 0.30,
    "ml_depth":       0.20,
    "sql_analyst_ux": 0.25,
    "cross_cloud":    0.15,
    "consolidation":  0.10,
}

candidates = {
    "Fabric_all_in": {
        "cost_reduction": 3, "ml_depth": 3, "sql_analyst_ux": 4,
        "cross_cloud": 1, "consolidation": 5,
    },
    "Databricks_all_in": {
        "cost_reduction": 4, "ml_depth": 5, "sql_analyst_ux": 3,
        "cross_cloud": 5, "consolidation": 5,
    },
    "Snowflake_all_in": {
        "cost_reduction": 4, "ml_depth": 3, "sql_analyst_ux": 5,
        "cross_cloud": 5, "consolidation": 5,
    },
    "Multi_vendor_status_quo": {
        "cost_reduction": 1, "ml_depth": 5, "sql_analyst_ux": 5,
        "cross_cloud": 5, "consolidation": 1,
    },
}

def score(cand: str) -> float:
    return sum(candidates[cand][k] * w for k, w in weights.items())

for c in candidates:
    print(f"{c}: {score(c):.2f}")

# → Fabric_all_in: 3.05
# → Databricks_all_in: 4.35     ← winner
# → Snowflake_all_in: 4.05
# → Multi_vendor_status_quo: 3.30

# Phased migration plan (winner: Databricks all-in)
phase_1_months_0_3:
  goal: migrate Tableau to Databricks SQL + AI/BI Dashboards
  action:
    - stand up Unity Catalog federation over Snowflake tables
    - port top-20 Tableau dashboards to Databricks AI/BI
    - keep Snowflake queries running via UC federation

phase_2_months_3_9:
  goal: migrate Snowflake SQL analysts to Databricks SQL
  action:
    - migrate top 100 tables from Snowflake to Delta on S3
    - re-point analyst queries; keep Snowflake as read-only for backfill
    - measure query latency + cost per query

phase_3_months_9_18:
  goal: decommission Snowflake; complete Databricks all-in
  action:
    - migrate remaining tables + views
    - decommission Snowflake compute
    - budget: ~$400k for migration; ROI ~14 months

cost_target:
  before: 45000 + 38000 + 8000 = 91000/mo
  after: ~60000/mo (Databricks all-in on reserved DBUs)
  savings: 34% (exceeds 20% goal)

Step-by-step trace.

Decision	Rationale
Winner: Databricks all-in	4.35 weighted score; wins on ML + cross-cloud + consolidation
Runner-up: Snowflake all-in	4.05; loses on ML depth (Cortex is emerging, not yet MLflow-level)
Fabric ruled out	3.05; cross-cloud constraint (Azure-only) + AWS-primary reality
Multi-vendor rejected	3.30; consolidation goal + cost pressure make it non-viable
Migration timeline	18 months, 3 phases
Migration budget	$400k engineer time
ROI	~14 months (cost savings + governance simplification)

After phase 3, the org runs Databricks all-in on AWS: 30 ML engineers on MLflow + feature store, 200 SQL analysts on Databricks SQL + AI/BI Dashboards, one Unity Catalog governance surface, cross-cloud story preserved for the planned GCP expansion. Fabric was ruled out because Azure-only compute doesn't match the AWS-primary reality; Snowflake lost on ML depth for this workload; multi-vendor status quo lost on cost and consolidation.

Output:

Metric	Before (multi-vendor)	After (Databricks all-in)
Monthly cost	$91k	~$60k
Vendor count	3	1
Governance surfaces	3	1 (Unity Catalog)
ML platform depth	MLflow (Databricks)	MLflow (unchanged)
SQL analyst UX	Snowflake (best-in-class)	Databricks SQL (good; +10% learning curve)
Cross-cloud ready	mixed	yes (UC federation)

Why this works — concept by concept:

Scored decision framework — weights per requirement, scores per candidate, weighted sum picks winner. Removes vendor-preference bias; makes the decision auditable.
Databricks all-in — wins the framework because ML share (30%) + cross-cloud future + consolidation goal align. Fabric would have won if the org were Azure-primary and Microsoft-shop; Snowflake would have won if ML share were <10%.
Phased migration — 18 months across 3 phases with UC federation as the bridge. Never a big-bang; Snowflake stays live during phases 1-2 for read-only queries.
Cost savings — 34% vs 20% target. Consolidation drives the savings; single reserved DBU commit beats three separate vendor contracts.
Cost of choosing wrong — if Fabric had been picked instead, the org would have paid the cross-cloud egress + latency tax on every AWS-native workload. The framework prevents that mistake by making the cross-cloud constraint explicit before commit. Compared to a "let's just pick the vendor we like" approach, the framework saved a ~$2M / 3-year mistake.

Design
Topic — design
Design problems on data-platform consolidation

Practice →

SQL
Topic — sql
SQL problems on multi-platform migration

Practice →

Cheat sheet — Fabric recipes

Which workload when. Lakehouse for Spark / Python / ML feature engineering / semi-structured / unstructured files. Warehouse for T-SQL DDL + multi-table transactions + BI marts + procedural ETL. Real-Time Intelligence (Eventstream + KQL Database) for sub-second time-series telemetry / log analytics / IoT / clickstream. Data Factory pipelines + Dataflows Gen2 for orchestration + low-code ETL + mirroring from Snowflake / Databricks / Cosmos DB / SQL sources. Power BI Direct Lake for BI reports over OneLake Delta. Data Science for MLflow-compat ML lifecycle inside Fabric governance.
OneLake shortcut CLI template. POST /v1/workspaces/{ws}/items/{lakehouse}/shortcuts with body { "name": "orders_s3", "path": "Tables", "target": { "amazonS3": { "location": "https://s3.us-east-1.amazonaws.com/acme-lake", "subpath": "/warehouse/orders", "connectionId": "<conn-guid>" } } }. Place under Tables/ so every workload sees it; use Files/ for raw ADLS pass-through. S3 / GCS shortcuts are typically read-only; ADLS Gen2 shortcuts are read-write.
Delta + V-Order table DDL for a Lakehouse. df.write.format("delta").mode("overwrite").option("optimizeWrite","true").option("mergeSchema","true").saveAsTable("gold.fact_sales"). Always keep optimizeWrite=true on writes destined for Power BI Direct Lake — the 15% write CPU cost buys 4× read speedup. Retrofit V-Order on externally-written tables via OPTIMIZE table_name VORDER.
Warehouse cross-warehouse query template. USE dw_finance; SELECT ... FROM [silver_conformed].[dbo].[fact_sales] f JOIN finance.dim_customer c ON f.customer_id = c.customer_id; — three-part naming ([lakehouse].[dbo].[table]) is the cross-workload primitive. Metadata is shared; storage is OneLake; no linked-server plumbing needed.
Eventstream + KQL Database provisioning. Eventstream JSON: { "sources": [{"type":"EventHubs","namespace":"...","hub":"..."}], "transformations": [{"type":"Filter","expression":"qc_status=='valid'"}], "destinations": [{"type":"KQLDatabase","target_database":"telemetry","target_table":"sensor_readings","ingestion_mode":"streaming"}] }. KQL table via .create table sensor_readings (ts:datetime, sensor_id:string, temperature_c:real, ...). Enable OneLake mirroring on the KQL Database so Warehouse / Lakehouse / Direct Lake see the telemetry.
Direct Lake semantic model checklist. Model source = OneLake Lakehouse or Warehouse; mode = DirectLake; fallback_mode: DirectQuery set; memory target ≤ (F-SKU DL memory - 20% safety); no calculated tables that force materialisation; underlying Delta tables V-Ordered; source tables partitioned appropriately. Break any of these → Direct Lake falls back to DirectQuery silently and user perf tanks.
Capacity throttling / smoothing runbook. Alert at CU_smoothed_pct > 85 for 10 min; open Capacity Metrics App → Top Consumers view → identify the user + operation type driving usage. Mitigation: bump F-SKU (PAYG) → investigate → scope Copilot policy → revert. Prevention: per-domain capacities, weekly CU-per-user reports, Copilot access gated by admin approval.
F-SKU sizing rule of thumb. Sustained CU = daily CU-s / 86400; peak burst ≈ 3× sustained; DL memory = F-SKU-num ÷ 6.4 GB. Pick smallest F-SKU where pool ≥ 1.33 × peak burst AND DL memory ≥ semantic model size + 20%. Reserved commit = 30% cheaper than PAYG; keep 8h/month PAYG burst headroom for spikes.
OneLake namespace shape. abfss://<workspace>@onelake.dfs.fabric.microsoft.com/<item>.<type>/Tables|Files/… — same DFS-compatible endpoint as ADLS Gen2, so external Spark clusters (Databricks, HDInsight) can read/write OneLake as if it were plain ADLS. Every workspace has an OneLake folder; every item has one under that; every table under Tables/ is a Delta table.
Fabric vs peer decision matrix. Fabric: Microsoft-shop + Power BI Direct Lake + T-SQL analysts + Azure-only OK. Databricks: cross-cloud + ML-heavy + Delta-origin + Unity Catalog. Snowflake: pure SQL warehouse + Cortex + cross-cloud governance. Legacy Synapse: multi-year dedicated SQL pool commitment only. Score per axis with weights before committing; treat as a 3-year decision not a 3-month one.
Mirroring from external sources. Fabric Data Factory Mirroring supports Snowflake, Databricks Delta, Cosmos DB, Azure SQL, PostgreSQL, MongoDB. Landing is OneLake Delta; continuous replication with sub-minute latency in most cases. Great for keeping source-of-truth in the original system while giving Fabric analysts a Delta copy for Direct Lake and cross-workload queries.
Copilot in Fabric considerations. Requires F64+ capacity; every invocation charges CU-s from the same pool; watch out for mass rollouts that consume 100+ CU sustained and throttle everything else. Scope Copilot access via group policy; monitor per-user CU consumption weekly; consider a dedicated capacity for the Copilot workspace at scale.
Migration from Synapse. Dedicated SQL pool DDL translates 1:1 to Fabric Warehouse (T-SQL surface is compatible). Serverless SQL views become Fabric Warehouse or SQL Endpoint views. Synapse Spark notebooks port to Fabric Lakehouse notebooks (mostly compatible; check PySpark version + library differences). ADF pipelines port to Fabric DF pipelines (activity-for-activity). Budget ~2-3 engineer-months per 100 tables + ~10 pipelines.
Governance defaults. Domains group workspaces by business unit (retail, finance, hr); each has delegated admin. Workspace RBAC (admin, member, contributor, viewer) applies to all items uniformly; per-item ACLs on top for fine-grained control. Purview scan on OneLake gives lineage across workloads; sensitivity labels propagate on write. DLP policies can block Direct Lake publish for tables with sensitive labels.

Frequently asked questions

What is Microsoft Fabric in one sentence?

Microsoft Fabric is a unified SaaS data platform that combines Data Factory, Synapse (Warehouse + Spark + Real-Time), Power BI, and Purview into one portal, one storage layer (OneLake — Delta by default, built on ADLS Gen2), one billing surface (F-SKU capacity, measured in Capacity Units), and one identity model (Microsoft Entra), so a Microsoft-shop organisation can run its entire lakehouse + warehouse + BI + real-time telemetry + governance stack from a single management surface. It went GA in May 2023 and matured through 2024-2026 into Microsoft's default data platform, subsuming most of Azure Synapse Analytics' functionality along the way. Every senior data-engineering interview probes Fabric because it's the load-bearing platform decision for any Microsoft-heavy shop in 2026 — and the trade-offs against Databricks, Snowflake, and legacy Synapse are exactly what interviewers grade the answer against.

OneLake vs ADLS Gen2 — are they the same?

They are related but not the same. ADLS Gen2 is Azure Data Lake Storage Gen2 — the raw hierarchical-namespace object storage layer that lives beneath Fabric. OneLake is a logical storage layer built on ADLS Gen2, provisioned automatically per Microsoft 365 tenant, with a single hierarchical namespace (onelake://tenant/workspace/item/…), Delta Lake as the default table format, V-Order write optimisation, and shortcuts that let you mount external ADLS / S3 / GCS locations without copying data. Every Lakehouse table, Warehouse table, and KQL Database mirror lives in OneLake as Delta. From the outside, OneLake exposes a DFS-compatible endpoint (onelake.dfs.fabric.microsoft.com) that behaves exactly like ADLS Gen2, so external Spark clusters (Databricks, HDInsight, Azure Synapse Spark) can read and write OneLake as if it were plain ADLS. In short: ADLS Gen2 is the storage substrate; OneLake is the tenant-wide, Delta-first, shortcut-capable, governance-integrated layer Fabric wraps around it.

Direct Lake vs Import vs DirectQuery for Power BI — when do I pick each?

Direct Lake is the Fabric-exclusive Power BI mode that column-loads Delta files from OneLake into the VertiPaq engine on demand, page by page — no Import refresh, no DirectQuery translation. Sub-second query latency at scale, and freshness follows the underlying Delta tables (no scheduled refresh). Pick Direct Lake when the semantic model source is OneLake Delta and the compressed model fits in the F-SKU's Direct Lake memory budget (roughly F-SKU-number ÷ 6.4 GB). Import loads the model into premium capacity memory ahead of queries and refreshes on a schedule. Pick Import when the model is too big for Direct Lake memory (>20 GB on F128, >40 GB on F256) or when the source doesn't live in OneLake, and freshness of hours to daily is acceptable. DirectQuery translates each visual into a source-database query at render time. Pick DirectQuery only when data must be real-time-fresh and lives outside OneLake (e.g. against Snowflake, SQL Server, or KQL Database directly) — accept the latency and concurrency cost. Set fallback_mode: DirectQuery on every Direct Lake model so memory pressure degrades gracefully to DirectQuery instead of failing.

Fabric vs Databricks — when do I pick each?

Pick Fabric when your organisation is a Microsoft 365 shop with Entra IDs and Power BI Premium already paid for, when sub-second Power BI dashboards over Delta are a differentiator (Direct Lake is the Fabric-only feature that most Databricks stacks can't match), when your users skew T-SQL analysts who prefer a Warehouse UX over Spark notebooks, and when Azure-only compute is fine because your cloud strategy is Azure-primary. Pick Databricks when your workload is multi-cloud (AWS + Azure + GCP), when ML depth matters (MLflow-native, feature store, Mosaic AI, model serving), when Delta Lake advanced features (deletion vectors, liquid clustering, CDF, Row Filters + Column Masks) are load-bearing, when Unity Catalog governance across clouds is a hard requirement, and when the team culture is notebook-first and Spark-native. The two platforms overlap on ~70% of workloads; the choice hinges on the 30% where the differentiators bite — Direct Lake + Microsoft-shop convenience for Fabric, cross-cloud + ML depth + Delta advanced features for Databricks. Score per axis with explicit weights before committing; treat the choice as a 3-year decision, not a 3-month one, because the migration cost between them is real.

How does capacity throttling actually work in Fabric?

Fabric capacity is measured in Capacity Units (CU); an F64 provides 128 CU sustained. When usage exceeds the pool, Fabric applies smoothing first — averaging your CU consumption over a rolling window (up to 5 minutes for interactive workloads like Power BI and notebook cells, up to 24 hours for background workloads like Spark jobs, Warehouse batch, and Data Factory pipelines). Short bursts above the pool don't throttle immediately because the smoothing window absorbs them. Once the smoothing budget is exhausted, Fabric moves to interactive throttling — queries queue with progressively longer delays (20 s, then 60 s, then 5 min) so users see spinners; the work still completes eventually. Beyond that, background rejection kicks in — Spark jobs fail-fast with an explicit throttling error, notebook cells throw, and pipelines fail. If auto-scale is configured, Fabric bursts to a higher F-SKU at PAYG rates before throttling; otherwise the ceiling is the pool size. The Capacity Metrics App shows smoothing percentage, throttling state, top consumers, and Direct Lake memory pressure — every Fabric deployment should alert at CU_smoothed_pct > 85 for 10 min to catch overload before user-visible impact. On-call runbook: bump F-SKU (temporary PAYG) → identify top consumer via metrics app → scope-limit (e.g. gate Copilot access) → revert to base F-SKU.

Is Azure Synapse dead?

Not yet — but its greenfield adoption is essentially over, and Microsoft's clear roadmap is that Fabric subsumes it. Azure Synapse Analytics as a distinct product still exists in 2026 for organisations with multi-year dedicated-SQL-pool commitments, for Serverless SQL views that haven't been migrated, and for existing Synapse Spark or Data Explorer workloads that are running in production. Microsoft continues to support Synapse for these existing commitments through their contract lifetimes. Fabric ships every Synapse workload equivalent — Fabric Warehouse replaces dedicated SQL pool, Fabric SQL Endpoint replaces Serverless SQL, Fabric Lakehouse replaces Synapse Spark, Fabric Real-Time Intelligence (Eventstream + KQL Database) replaces Data Explorer, Fabric Data Factory replaces Azure Data Factory. Migration is usually straightforward — dedicated pool T-SQL translates 1:1 to Warehouse; ADF pipelines port activity-for-activity to Fabric DF; Synapse Spark notebooks port to Lakehouse notebooks with minor library version adjustments. Budget roughly 2-3 engineer-months per 100 tables + 10 pipelines for a Synapse-to-Fabric migration. New workloads in 2026 should start in Fabric; existing Synapse workloads should have a migration timeline attached. Interviewers ask "is Synapse dead?" to test whether you understand the roadmap — the correct answer is "subsumed by Fabric; legacy commitments still run; greenfield goes to Fabric."

Practice on PipeCode

Drill the SQL practice library → for the Warehouse T-SQL, cross-workload query, mart-load, and multi-table transaction problems senior Fabric interviewers love.
Rehearse system design against the design practice library → for the OneLake shortcut strategy, F-SKU capacity sizing, per-domain capacity isolation, and Fabric-vs-peer decision framework problems.
Sharpen the streaming axis with the streaming practice library → for the Real-Time Intelligence + Eventstream + KQL Database + OneLake mirroring scenarios.
Warm up the join axis with the joins practice library → for the cross-workload Lakehouse + Warehouse + KQL join patterns that Fabric interviewers probe.
Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the Fabric workload split, OneLake shortcut mechanics, and F-SKU capacity math against real graded inputs.

Lock in microsoft fabric muscle memory

Docs explain Fabric. PipeCode drills explain the decision — when Direct Lake beats Import, when OneLake shortcuts beat a copy migration, when F-SKU capacity smoothing becomes throttling, when Fabric wins over Databricks or Snowflake for a specific workload. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the platform trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →