Data Warehouse Concepts: Facts and Dimensions

A data warehouse is a centralized repository that stores integrated data from multiple sources for reporting and analysis. Unlike transactional databases (OLTP) optimized for inserts/updates, data warehouses are optimized for read-heavy analytical queries (OLAP). They use a schema-on-write approach where data is pre-processed, cleaned, and structured before loading. In Azure, the primary data warehouse service is Azure Synapse Analytics (formerly SQL Data Warehouse), which uses a massively parallel processing (MPP) architecture.

In a data warehouse, data is organized into fact tables and dimension tables. This is known as a dimensional model, popularized by Ralph Kimball. The fact table contains quantitative data (measures) that can be aggregated, such as sales amount, quantity sold, or profit. Dimension tables contain descriptive attributes (context) that provide meaning to the facts, such as customer name, product category, or date.

Fact Tables: - Store numeric, additive measures (e.g., sales_amount, quantity) - Contain foreign keys to dimension tables - Are often large (millions or billions of rows) - Can have different granularities: transaction-level (each row is a sale), periodic snapshot (daily balance), or accumulating snapshot (order lifecycle) - Example columns: SaleID, DateKey, CustomerKey, ProductKey, SalesAmount, Quantity

Dimension Tables: - Store descriptive attributes (text or discrete values) - Are typically smaller (thousands to millions of rows) - Provide context for facts (e.g., customer name, product name, date) - Often have a surrogate key (integer) as primary key, not the business key - Can be slowly changing (SCD types 1, 2, 3) to track historical changes - Example columns: CustomerKey, CustomerName, CustomerCity, CustomerSegment

The exam tests your ability to distinguish between star and snowflake schemas.

Star Schema: - A single fact table surrounded by dimension tables (like a star) - Dimension tables are denormalized (all attributes in one table, e.g., Product table includes category, subcategory, brand) - Advantages: simpler queries, fewer joins, better performance - Disadvantages: data redundancy, larger dimension tables - Example: FactSales joins directly to DimProduct (which contains CategoryName)

Snowflake Schema: - Dimension tables are normalized (split into multiple related tables) - For example, DimProduct references DimCategory, which references DimDepartment - Advantages: less redundancy, easier maintenance - Disadvantages: more joins, slower queries, complex queries - Example: FactSales joins to DimProduct, which joins to DimCategory

Exam tip: Azure Synapse Analytics and Power BI typically use star schemas for performance. Snowflake schemas are less common but may appear in data warehouse design questions.

The grain defines what a single row in the fact table represents. It is the most important design decision. Common grains:

Example: If you have a fact table with DateKey, CustomerKey, and SalesAmount, the grain is one row per customer per day (if there is only one sale per customer per day) or per transaction (if multiple rows per customer per day).

Exam questions may ask which measures are additive. Always look for numeric, ratio-based measures as non-additive.

Dimensions can change over time. SCD types describe how to handle changes:

Example: Customer moves to a new city. Type 1 updates the city; Type 2 adds a new row with a new surrogate key and effective dates; Type 3 adds a PreviousCity column.

A dimension table can be used multiple times in a fact table with different roles. For example, a Date dimension can be used for OrderDate, ShipDate, and DeliveryDate. In the fact table, each role has a foreign key (e.g., OrderDateKey, ShipDateKey).

A degenerate dimension is a dimension attribute stored directly in the fact table because it has no other attributes. Common for transaction numbers (e.g., InvoiceNumber). It is a key but belongs to no dimension table.

A fact table with no numeric measures, only foreign keys. Used to record events or coverages (e.g., student attendance: DateKey, StudentKey, CourseKey — no measure, just presence).

Dimensions that are shared across multiple fact tables or data marts. For example, a Date dimension used in Sales and Inventory fact tables ensures consistency. This is a key concept in Kimball's bus architecture.

In Azure Synapse, tables are created using CREATE TABLE statements. Fact tables are often distributed using hash distribution on a dimension key (e.g., CustomerKey) to collocate joins. Dimension tables can be replicated (small) or hash-distributed. Common distribution methods:

Example of creating a fact table:

CREATE TABLE dbo.FactSales (
    SaleID INT NOT NULL,
    DateKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    ProductKey INT NOT NULL,
    SalesAmount MONEY NOT NULL,
    Quantity INT NOT NULL
)
WITH (
    DISTRIBUTION = HASH(CustomerKey),
    CLUSTERED COLUMNSTORE INDEX
);

Example of creating a dimension table:

CREATE TABLE dbo.DimCustomer (
    CustomerKey INT NOT NULL,
    CustomerID INT NOT NULL,
    CustomerName NVARCHAR(100),
    City NVARCHAR(50),
    Segment NVARCHAR(20)
)
WITH (
    DISTRIBUTION = REPLICATE,
    CLUSTERED INDEX (CustomerKey)
);

Fact tables often use clustered columnstore indexes for compression and query performance. Dimension tables use clustered indexes on the surrogate key. In Synapse, columnstore indexes are the default for analytics workloads.

In Power BI, data models use star schemas. Relationships are created between fact and dimension tables (one-to-many). The fact table is on the many side, dimension on the one side. Cross-filter direction is usually single (from dimension to fact).

DP-900 expects you to:

Facts and dimensions are the heart of data warehousing. Facts store measurements; dimensions store context. Star schemas are preferred for analytics. Azure Synapse uses MPP with hash distribution and columnstore indexes. Understanding these concepts is essential for DP-900 and real-world data engineering.

In a retail company, the data warehouse is used to analyze sales performance across stores, products, and time. The fact table FactSales contains billions of rows, each representing a line item from a point-of-sale transaction. Dimensions include DimStore (store location, region), DimProduct (product name, category, brand), DimDate (date, month, quarter, year), and DimCustomer (customer demographics). The star schema allows the sales team to query total sales by region and product category with simple joins. The fact table is distributed by hash on StoreKey to collocate sales data with the store dimension, reducing data movement during joins. Clustered columnstore indexes compress the data and speed up aggregation queries. Common issues include data skew if a few stores have disproportionately large sales, causing uneven distribution. To mitigate, a different distribution key or round-robin distribution may be used. Another scenario is a financial services company tracking account balances. The fact table FactAccountBalance uses a periodic snapshot grain (daily balance per account). Dimensions include DimAccount, DimDate, and DimBranch. Balance is semi-additive: it can be summed across accounts but not across time (averaging is used for time). Misconfiguration occurs when the grain is not clearly defined, leading to duplicate rows or incorrect aggregations. In production, ETL processes load data daily, and the fact table is partitioned by date to allow partition switching for efficient loading and archiving. Azure Synapse supports partition elimination, improving query performance. When dimensions change (e.g., customer address), Type 2 SCD is used to track history, adding new rows with effective dates. This ensures historical sales data remains accurate. Performance tuning involves monitoring query execution plans, updating statistics, and rebuilding indexes periodically.