Fabric Data Warehouse Interview Questions

A SaaS-based relational engine supporting full T-SQL (DDL, DML, ACID transactions) with compute completely separated from storage. Storage is open Delta Parquet in OneLake rather than SQL Server’s proprietary page-based format. There are no `.mdf`/`.ldf` files, no manual index management, and no DWU scaling — capacity is governed by the F-SKU.

Warehouse: full T-SQL read/write — INSERT, UPDATE, DELETE, DDL all work. Lakehouse SQL Analytics Endpoint: read-only. You query Spark-written Delta tables using T-SQL but cannot modify data or schema from that endpoint. Both run on the same SQL engine and share OneLake storage, enabling cross-item queries via three-part naming.

Synapse Dedicated Pools use a 60-distribution architecture with explicit HASH/ROUND_ROBIN/REPLICATE distribution keys, require manual DWU scaling, and store data in a proprietary format. Fabric Warehouse uses the Polaris engine on open Delta Parquet with no distribution key definitions (Polaris manages data placement automatically) and scales via F-SKU capacity rather than manual DWU provisioning.

Fabric Warehouse stores data as Delta Parquet in OneLake, with the Delta transaction log maintaining ACID guarantees. This enables: (1) Time Travel via T-SQL, (2) cross-engine read access (Spark, external tools with permissions), (3) Mirroring into Fabric from external databases that land data in this same format, (4) Direct Lake Power BI access to Warehouse tables without a separate copy.

Fabric Warehouse uses a distributed transaction log that writes to Delta Lake metadata (`_delta_log`) on commit. ACID compliance comes from Delta Lake’s optimistic concurrency control — each transaction reads a consistent snapshot, and conflicts are detected at commit time. There is no `.ldf` transaction log file to manage or back up; durability is handled by OneLake’s underlying storage redundancy (ZRS/GRS).

Because Warehouse tables are Delta Lake under the hood, you can query historical versions using OPTION (FOR TIMESTAMP AS OF ...) syntax. This enables point-in-time recovery without a full backup restore — query what the table looked like before an accidental DELETE, then re-insert those rows.

Database Hub in Fabric (early release, FabCon March 2026) is a unified governance surface for observing and managing SQL, CosmosDB, PostgreSQL, and Fabric Database workloads at scale. Rather than navigating separate admin consoles per database type, Database Hub provides a single place for cross-database monitoring, performance insights, and governance.

Fabric Data Warehouse (March 2026) integrates with Fabric Activator, allowing teams to define rules directly from SQL query outputs. A query that detects an SLA breach, process failure, or threshold breach can trigger an Activator rule that fires alerts and downstream actions in real time — without exporting data to a separate monitoring system.

Yes, as of November 2025 Preview. IDENTITY columns in Fabric Data Warehouse automatically generate unique values per row and maintain uniqueness across parallel ingestion jobs in the distributed engine. The original workaround of using ROW_NUMBER() or MAX(ID) + 1 is no longer required for new implementations.

Defined but not enforced. You can declare PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints in DDL for documentation and query optimizer hints — the optimizer uses them for join strategy decisions. But the engine does not validate uniqueness or referential integrity at write time. Data quality must be enforced in the ETL pipeline before data enters the Warehouse.

Yes — MERGE entered Preview in Fabric Data Warehouse in September 2025 and is now GA. It provides conditional DML: INSERT, UPDATE, and DELETE in a single statement based on a match condition. This is the standard T-SQL pattern for implementing SCD Type 1 and upsert logic without separate INSERT/UPDATE statements.

Yes — Stored Procedures, Views, and inline Table-Valued Functions are all supported. This allows substantial business logic to migrate from SQL Server or Synapse with relatively limited changes, provided the procedures don’t use unsupported T-SQL constructs (cursors, OPENQUERY, temp table loops).

COPY INTO loads data from external files (CSV, Parquet in ADLS, OneLake) in parallel across compute nodes — it’s the primary high-throughput ingestion path. INSERT INTO ... SELECT works but is slower for large file loads because it’s processed as a standard DML statement without the parallel file reader optimization.

Fabric Warehouse exposes a T-SQL endpoint compatible with the dbt-fabric adapter. Configure the profile with the Fabric Warehouse’s SQL connection string, run dbt run, and dbt executes models as SQL transformations inside the Warehouse. dbt materializations (table, view, incremental) all work against the Warehouse T-SQL surface.

Fabric Warehouse enforces schema-on-write — a COPY INTO or pipeline write with an incompatible structure fails at load time, not silently. Handle schema drift with: (1) pre-load metadata check against INFORMATION_SCHEMA.COLUMNS, (2) dynamic ALTER TABLE ... ADD COLUMN for additive changes, (3) a staging table approach that always accepts raw data before applying schema checks.

Fabric Data Agents reached GA at FabCon March 2026. The Warehouse now has a direct “Create Data Agent” entry point in its ribbon — connecting a Warehouse to a Data Agent takes one click rather than manual configuration. Data Agents act as virtual analysts: they accept natural language questions and generate SQL against the Warehouse to answer them.

Result Set Caching (Preview, 2025) stores the output of a query in the Warehouse. Identical subsequent queries against unchanged underlying data return instantly from cache — consuming zero compute. Effective for repetitive dashboard queries that hit the same aggregations. Cache is invalidated when the underlying Delta table version changes.

Data Clustering (Preview, November 2025) organizes rows with similar column values together during ingestion, enabling aggressive Parquet file pruning when those columns appear in query predicates. Unlike Z-Order (which requires a separate OPTIMIZE ZORDER BY run on existing data), Data Clustering is applied at write time — no separate maintenance step.

Incremental Statistics Refresh (January 2026) re-samples only the newly added rows since the last statistics update, rather than re-scanning the full column. Applies to tables that experience mostly INSERT or APPEND operations. Reduces the time statistics maintenance contributes to SELECT query execution on long, append-heavy tables.

V-Order is primarily a VertiPaq (Power BI Direct Lake) optimization, but it benefits the Warehouse SQL engine indirectly. V-Ordered Parquet files have better dictionary encoding and column statistics that improve IO efficiency during large table scans in SQL. The improvement is secondary to its Direct Lake benefit — don’t enable V-Order solely for SQL performance if write speed on Gold tables matters.

The Polaris engine handles data placement — no manual HASH/ROUND_ROBIN hints needed. Optimize joins by: (1) building a proper star schema (dimension tables small, fact tables large), (2) ensuring statistics are current on join columns so the optimizer chooses the right join algorithm, (3) avoiding joins on VARCHAR(MAX) columns, (4) using Data Clustering on the join key column of large fact tables.

CTAS physically materializes a complex aggregation or join result into a new table. Use it when: (1) a complex query runs repeatedly and Result Set Caching doesn’t apply (parameter variation), (2) an aggregation is queried by multiple downstream consumers, (3) a view would be queried enough times that on-the-fly computation is expensive. CTAS is the Fabric equivalent of a Synapse Materialized View when needed.

Three surfaces: (1) Query Activity hub in the Fabric portal — shows active and recent queries with durations, (2) DMVs: sys.dm_exec_requests for active queries, sys.dm_pdw_exec_requests for history, (3) Capacity Metrics App for understanding CU consumption per query. For query plan analysis, use DAX Studio or connect SSMS via the SQL connection string and examine estimated execution plans.

In Synapse, you scaled manually by changing DWU (1 minute to 5 minutes per operation). In Fabric, the Warehouse draws from the shared F-SKU capacity pool automatically — no manual scaling action. For query concurrency issues, the resolution is either (1) upgrading the F-SKU, (2) enabling Autoscale Billing for burst workloads, or (3) scheduling heavy batch queries during off-peak hours.

Standard T-SQL RLS via Security Policy and an inline predicate function. Define the function to filter rows based on the executing user’s identity, attach it as a Security Policy on the table, and every query automatically applies the filter. This works for T-SQL queries via the SQL endpoint — it does NOT protect data accessed via Spark directly on the underlying Delta files.

Column-Level Security (CLS): DENY SELECT on specific columns to specific roles — unauthorized users get an error if they reference the column. Dynamic Data Masking (DDM): the column is readable but the value is obfuscated (partial mask, email mask, random number) — unauthorized users see the mask, not an error. CLS for strict access control; DDM for softer obfuscation where acknowledging the column exists is acceptable.

Workspace roles (Admin/Member/Contributor/Viewer) grant broad access to all items in the workspace. Item-level sharing via the Warehouse “Share” button grants access to a specific Warehouse only — the user can connect via SQL without being in the workspace at all. Item sharing is the right model for external analysts or BI tools that need SQL access to one Warehouse but nothing else in the workspace.

Fabric integrates with Microsoft Purview for audit logging. Purview captures who ran which SQL query, from which application, at what time, and what data was accessed. This satisfies audit requirements for regulated industries where you need to prove who accessed sensitive data and when.

Warehouse security uses T-SQL GRANT/DENY/RLS/DDM at the SQL engine layer. Lakehouse security uses OneLake Data Access Roles at the storage layer plus SQL security on the SQL Endpoint. OneLake-layer security is stronger for multi-engine scenarios because it protects against all access paths (SQL, Spark, external tools), not just T-SQL queries.

No native backup/restore command like SQL Server’s BACKUP DATABASE. Data durability comes from OneLake’s ZRS/GRS storage redundancy. Point-in-time recovery is handled by Delta Time Travel (within the retention window). For schema-only backup: script out DDL via SSMS or Information Schema queries. For data recovery from accidental deletes: Time Travel.

Three-part naming: [LakehouseName].[schema].[table] within a T-SQL query on the Warehouse. Both items must be in the same workspace and the executing user must have read access to both. No ETL required — the SQL engine reads the Lakehouse’s Delta Parquet files directly.

Cross-workspace: create a Shortcut in the local Warehouse pointing to the remote Warehouse or Lakehouse item. The Shortcut appears as a local table and can be queried via three-part naming. Cross-tenant (delegated Shortcuts, coming soon as of March 2026): OneLake now supports creating Shortcuts to other tenants via delegated Entra identity — enabling cross-tenant data access without data duplication.

Key limitations: (1) Remote sources via three-part naming are read-only — no DML against a Lakehouse table from a Warehouse query, (2) both items should be in the same Fabric region (cross-region adds latency and potential cost), (3) the executing user needs permissions on both the local Warehouse AND the remote Lakehouse/Warehouse SQL Endpoint, (4) statistics on the remote table may be incomplete, affecting query plan quality.

A View encapsulates the three-part name inside the Warehouse: CREATE VIEW v_WebLogs AS SELECT * FROM [SalesLakehouse].[dbo].[WebLogs]. BI tools, dbt, and report developers query v_WebLogs without knowing the source is a Lakehouse. If the source moves to a different Lakehouse or the schema changes, you update the View — not every downstream query.

Performance is generally high because no data movement occurs — the SQL engine reads Parquet files from OneLake directly. But: (1) joining two massive tables from different items may trigger a cross-item shuffle with significant overhead, (2) filter pushdown to remote Lakehouse tables depends on statistics availability, (3) the V-Order status of the remote table affects read efficiency.

Both items in the same workspace. Write a T-SQL query in the Warehouse using three-part naming to reference the Lakehouse SQL Endpoint. Ensure the user has SQL read access to the Lakehouse endpoint. Optionally, create a View in the Warehouse to abstract the Lakehouse reference. No ETL pipeline, no data movement, no duplication.

Export data to Parquet in ADLS, load via COPY INTO. Rewrite DDL to remove Synapse-specific syntax. Use the Fabric Migration Assistant for SQL databases (FabCon March 2026) which imports schemas via DACPACs, identifies T-SQL compatibility issues with AI assistance, and guides assessment and data copy workflows — significantly reducing manual rewrite effort.

For Azure SQL DB, SQL Server, or Snowflake sources: enable Mirroring to replicate the source database into Fabric OneLake in near-real-time via CDC. Once the mirror is caught up, point BI tools and downstream consumers to the Fabric Warehouse SQL endpoint. Cutover is a connection string change, not a batch migration window.

Three checks in order: (1) Row counts per table — source vs Fabric, (2) Aggregation checksums — SUM(Amount), COUNT(OrderID) by key dimensions, (3) Sample record comparison — specific known records validated field by field. Automate in a Fabric Notebook or Data Pipeline that runs both source and target queries and compares results.

No direct DWU-to-F-SKU mapping formula exists. Run a pilot: migrate representative workloads (not just the simplest ones) to a trial F-SKU, run the Capacity Metrics App for 2–4 weeks of typical query patterns, and observe peak CU utilization and smoothing behavior. Size the production F-SKU with 20–30% headroom above observed peak.