Generate Star Schema Data Warehouse Designs

This skill acts as an expert in dimensional modeling and star schema design for data warehouses, applying Kimball methodology - fact and dimension table design, slowly changing dimensions, and efficient analytical data structures. Its core principles: fact tables hold quantitative measurements and foreign keys to dimensions, dimension tables hold descriptive business-context attributes, grain defines the level of detail in a fact table, surrogate keys give stable joins independent of business keys, and conformed dimensions keep analysis consistent across multiple fact tables. Structurally it centers the fact table around dimension tables, keeps fact tables narrow (measures and FKs only), keeps dimension tables wide with rich descriptive attributes, avoids snowflaked (normalized) dimensions, and implements proper indexing.

For fact tables it distinguishes transaction fact tables (one row per event, with additive measures like sales amount, semi-additive measures like unit price, and derived non-additive measures like profit margin) from snapshot fact tables (periodic measurements like inventory on hand that are additive across dimensions but not across time). A transaction fact table example:

CREATE TABLE fact_sales (
    sales_key BIGINT IDENTITY(1,1) PRIMARY KEY,
    date_key INT NOT NULL,
    customer_key INT NOT NULL,
    product_key INT NOT NULL,
    store_key INT NOT NULL,
    salesperson_key INT NOT NULL,
    quantity_sold DECIMAL(10,2),
    gross_sales_amount DECIMAL(12,2),
    discount_amount DECIMAL(12,2),
    net_sales_amount DECIMAL(12,2),
    cost_amount DECIMAL(12,2),
    unit_price DECIMAL(8,2),
    profit_margin AS (net_sales_amount - cost_amount) / net_sales_amount,
    created_date DATETIME2 DEFAULT GETDATE(),
    batch_id INT
);

For dimensions it provides a customer dimension implementing Slowly Changing Dimension Type 2 (surrogate key, natural key, descriptive/address/demographic attributes, plus effective_date/expiration_date/current_flag to track history), and a standard date dimension with calendar and fiscal attributes. Advanced patterns cover junk dimensions for grouping low-cardinality flags together and bridge tables for many-to-many relationships (like customers sharing an account) with allocation percentages and effective dating.

Naming conventions are enforced (fact_[business_process], dim_[dimension_name], [table_name]_key for surrogate keys), along with indexing strategy (clustered on date, nonclustered on common join keys, filtered indexes for current-row lookups) and data quality constraints (foreign keys for referential integrity, check constraints for business rules like non-negative sales amounts and valid SCD date ranges).

Use this skill when designing or generating a star schema for a data warehouse - fact and dimension tables, SCD handling, indexing, and data quality constraints - always weighing the specific analytical requirements and query patterns rather than applying a generic template.

Output is SQL DDL for fact tables, dimension tables (including SCD Type 2), indexes, and constraints, following a validation checklist: defined grain, integer surrogate keys, primarily additive measures, rich descriptive dimensions, conformed shared dimensions, properly handled SCDs, enforced referential integrity, and appropriate indexing/partitioning.

Data engineers and analytics engineers designing dimensional models for data warehouses who need Kimball-methodology-compliant fact and dimension table structures rather than an ad hoc normalized schema.