Build and Optimize Data Marts

This skill applies expert-level data mart design across multidimensional modeling, star schema design, ETL/ELT processes, and performance optimization on platforms including SQL Server, PostgreSQL, Snowflake, and cloud data warehouses. Its modeling principles: design star schemas with clear fact and dimension tables, properly implement slowly changing dimensions (SCD), use surrogate keys on dimension tables, keep a consistent grain within fact tables, apply conformed dimensions across multiple fact tables, and separate transactional from analytical workloads. For architecture it follows the Kimball bottom-up methodology, implements proper staging, integration, and presentation layers, designs for query performance rather than storage optimization, plans incremental load strategies, and establishes clear data lineage and documentation.

It provides concrete schema patterns for a fact table with foreign keys, measures, and date-based partitioning:

-- Sales Fact Table Example
CREATE TABLE fact_sales (
    sales_key BIGINT IDENTITY(1,1) PRIMARY KEY,
    date_key INT NOT NULL,
    product_key INT NOT NULL,
    customer_key INT NOT NULL,
    store_key INT NOT NULL,
    
    -- Measures
    quantity_sold DECIMAL(10,2),
    unit_price DECIMAL(10,2),
    total_amount DECIMAL(12,2),
    discount_amount DECIMAL(10,2),
    
    -- Metadata
    created_date DATETIME2 DEFAULT GETDATE(),
    batch_id VARCHAR(50),
    
    -- Foreign Keys
    FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
    FOREIGN KEY (product_key) REFERENCES dim_product(product_key),
    FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key),
    FOREIGN KEY (store_key) REFERENCES dim_store(store_key)
);

-- Partitioning for performance
ALTER TABLE fact_sales
ADD CONSTRAINT pk_fact_sales_partitioned
PARTITION (date_key);

and a Type 2 slowly-changing dimension table (natural key, effective/expiry dates, is_current flag). ETL patterns cover incremental dimension loads that insert new records, close out changed current records, and insert new versions - and fact-table loads that run data-quality checks (rejecting negative amounts), resolve dimension keys, and log batch processing statistics.

For performance it covers indexing strategy - clustered columnstore indexes for analytical queries, supporting indexes with included columns, and filtered dimension indexes - plus efficient aggregation query patterns. For data quality it defines a rules-table framework (table, rule, SQL, threshold) that can encode checks like a revenue range validation.

Use it when designing or optimizing a data mart for analytical workloads - star schema design, SCD dimension handling, ETL load patterns, and query performance tuning. It is built around Kimball-style dimensional modeling for reporting and analytics, not for transactional/OLTP schema design, which it explicitly separates out as a different workload.

Output spans DDL for fact and dimension tables, stored procedures for incremental ETL loads, indexing scripts, aggregation query templates, and a data-quality rules framework - all grounded in named platforms (SQL Server, PostgreSQL, Snowflake, cloud data warehouses).

Patterns are written against SQL Server T-SQL syntax (IDENTITY, GETDATE, columnstore indexes) but the modeling principles apply across PostgreSQL, Snowflake, and other cloud data warehouse platforms named in scope.

Data engineers and analytics engineers designing or optimizing a data mart - star schema modeling, SCD dimension handling, ETL load logic, indexing, and data quality monitoring.