Star Schema vs Snowflake Schema

Star schema and snowflake schema are two approaches to organizing data in a data warehouse for analytical querying. Both use a central fact table that contains quantitative measures (e.g., sales amount, quantity) and foreign keys referencing dimension tables that provide context (e.g., time, product, customer). The difference lies in how dimension tables are structured.

In a star schema, each dimension is denormalized into a single table. For example, a 'Product' dimension table would include product ID, product name, category, subcategory, brand, and supplier all in one table. This eliminates the need for joins within the dimension itself. The fact table sits in the center, and dimension tables radiate out like the points of a star.

Key characteristics: - Simple structure: easy to understand and query. - Fewer joins: queries typically join the fact table with one or more dimension tables directly. - Fast query performance: because dimension tables are flat, queries require fewer joins. - More storage: denormalization leads to redundancy (e.g., category name repeated for every product in the same category). - Ideal for OLAP cubes and reporting tools like Power BI, which prefer star schemas for optimal performance.

In a snowflake schema, dimension tables are normalized into multiple related tables. Using the same example, the 'Product' dimension might be split into 'Product', 'Category', and 'Supplier' tables. The 'Product' table contains a foreign key to 'Category', which in turn may have a foreign key to 'Department'. This creates a snowflake-like pattern as dimensions branch out.

Key characteristics: - Normalized structure: reduces data redundancy. - More joins: queries require joining multiple tables to retrieve all dimension attributes. - Slower query performance: more joins increase query complexity and execution time. - Less storage: normalization saves space by eliminating duplicate values. - Preferred when storage is expensive or when dimensions are highly shared across multiple fact tables.

Consider a query: "Total sales by product category for 2024."

Star schema: - Fact table: Sales (ProductID, DateID, Amount) - Dimension table: Product (ProductID, ProductName, Category) - Query: SELECT Category, SUM(Amount) FROM Sales JOIN Product ON Sales.ProductID = Product.ProductID WHERE Year = 2024 GROUP BY Category. - Joins: one join between Sales and Product. - Execution: database optimizer scans Sales, joins with Product (usually via an index on ProductID), aggregates by Category.

Snowflake schema: - Fact table: Sales (ProductID, DateID, Amount) - Dimension tables: Product (ProductID, ProductName, CategoryID), Category (CategoryID, CategoryName) - Query: SELECT CategoryName, SUM(Amount) FROM Sales JOIN Product ON Sales.ProductID = Product.ProductID JOIN Category ON Product.CategoryID = Category.CategoryID WHERE Year = 2024 GROUP BY CategoryName. - Joins: two joins (Sales->Product and Product->Category). - Execution: more join operations, potentially requiring more memory and CPU. If indexes are missing, performance degrades significantly.

In Azure Synapse Analytics or SQL Server, you create these schemas using CREATE TABLE statements. For star schema:

CREATE TABLE DimProduct (
    ProductKey INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Category NVARCHAR(50)
);

CREATE TABLE FactSales (
    OrderID INT,
    ProductKey INT REFERENCES DimProduct(ProductKey),
    SalesAmount DECIMAL(10,2)
);

For snowflake schema:

CREATE TABLE DimCategory (
    CategoryKey INT PRIMARY KEY,
    CategoryName NVARCHAR(50)
);

CREATE TABLE DimProduct (
    ProductKey INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    CategoryKey INT REFERENCES DimCategory(CategoryKey)
);

CREATE TABLE FactSales (
    OrderID INT,
    ProductKey INT REFERENCES DimProduct(ProductKey),
    SalesAmount DECIMAL(10,2)
);

To verify schema, query system tables:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';

| Aspect | Star | Snowflake | |--------|------|-----------| | Storage | Higher (redundancy) | Lower (normalized) | | Query performance | Faster (fewer joins) | Slower (more joins) | | Maintenance | Simpler (one table per dimension) | Complex (multiple tables) | | Data integrity | Potential anomalies | Better (referential integrity) | | ETL complexity | Easier to load | Harder to load (multiple tables) | | Reporting tools | Preferred | Less preferred |

DP-900 tests your ability to identify which schema is used in a given scenario. Common questions: "Which schema is best for fast query performance?" (star). "Which schema reduces data redundancy?" (snowflake). "Which schema has more dimension tables?" (snowflake). Also, you may be asked to identify the fact table vs. dimension table in a diagram.

A large retail chain uses a star schema to analyze daily sales across 500 stores. The fact table FactSales contains one row per transaction line item, with measures like SalesAmount, Quantity, and Discount. Dimensions include DimDate, DimStore, DimProduct, and DimCustomer, each denormalized. For example, DimProduct includes ProductName, Category, SubCategory, Brand, and Supplier. This design allows analysts to run queries like 'total sales by brand and region for last quarter' with just three joins (FactSales -> DimProduct -> DimStore). The database is hosted on Azure Synapse with clustered columnstore indexes on the fact table, achieving sub-second response times for aggregated queries. Misconfiguration risk: if dimensions are not properly indexed on foreign keys, queries can become slow. Also, if the fact table grain is too coarse (e.g., daily totals instead of line items), drill-down analysis is impossible.

An insurance company uses a snowflake schema to manage claims data. The fact table FactClaims includes measures like ClaimAmount, SettlementAmount, and ProcessingTime. Dimensions are normalized: DimPolicy references DimPolicyType and DimCoverageLevel; DimCustomer references DimAddress and DimDemographics. This reduces storage because many policies share the same policy type and coverage level. However, queries that require policy type and customer demographics need four joins: FactClaims -> DimPolicy -> DimPolicyType, and FactClaims -> DimCustomer -> DimDemographics. This can lead to slower performance for ad-hoc queries. To mitigate, the company creates indexed views that denormalize the most common joins. The exam scenario: if the question mentions that storage space is limited and dimensions have many shared attributes, snowflake schema is the correct choice.

An e-commerce platform uses a star schema for order analytics. The fact table FactOrders has measures OrderTotal, ShippingCost, and Tax. Dimensions: DimDate, DimCustomer, DimProduct, DimShippingMethod. The DimProduct table includes ProductName, Category, Supplier, WarehouseLocation — all in one table. This enables the business intelligence team to build Power BI reports that load quickly. The star schema aligns with Power BI's recommendation for star schemas to optimize compression and query performance. A common mistake is to use snowflake schema in Power BI, leading to slow report rendering. In production, the ETL process loads data nightly using Azure Data Factory, truncating and reloading dimension tables (type 1 slowly changing dimension) and incrementally loading the fact table.