Fabric Data Warehouse

A Fabric Data Warehouse is a fully managed relational analytical store built on OneLake. It supports transactional T-SQL, ACID behaviour and open Delta storage.

Warehouse vs SQL Analytics Endpoint - The Warehouse offers full read-and-write T-SQL including INSERT, UPDATE, DELETE and MERGE, over native warehouse tables - The SQL analytics endpoint offers a read-only T-SQL experience over lakehouse tables surfaced through SQL

Connecting External Tools - Copy the warehouse SQL connection string from Fabric - Use Microsoft Entra ID authentication; traditional SQL authentication is not supported - Specify the warehouse database name when required by the client tool - Standard ODBC and OLE DB tools can connect when network access permits TCP port 1433

Core Dimensional Modelling Objects - Fact table: stores quantitative measurements such as sales amount or quantity - Dimension table: provides descriptive context such as customer, product, store or date - Surrogate key: warehouse-generated identifier with no business meaning - Business / alternate key: source-system identifier retained for traceability and lookups - Star schema: fact table connected directly to denormalised dimensions - Snowflake schema: dimensions further normalised into related tables

Slowly Changing Dimension Types - Type 0: attributes do not change - Type 1: overwrite the existing value; history is not retained - Type 2: insert a new row for each change; full history is retained - Type 3: store limited history in additional columns

Fact tables are generally loaded after dimensions because incoming business keys must be mapped to the correct dimension surrogate keys.

Loading Strategies - Full load: truncate and reload the complete target; simpler, but more resource intensive - Incremental load: process only new or changed records; more efficient, but requires change-detection logic - Staging: land source-shaped data in auxiliary tables before applying business rules - COPY statement: bulk load supported external files into warehouse tables - CTAS: create a new table from a SELECT result - INSERT...SELECT: append query results into an existing target table

Useful Analytical Functions - ROW_NUMBER() returns a unique sequential row number within a partition - RANK() gives equal values the same rank; subsequent ranks contain gaps - DENSE_RANK() gives equal values the same rank with no gaps - NTILE(n) divides rows into ranked groups - APPROX_COUNT_DISTINCT returns a fast estimate for large distinct-count workloads

Common Confusion - Data ingestion moves raw data into the analytical platform - Data loading places prepared data into final warehouse structures such as dimensions and facts