Design Efficient Snowflake Data Warehouse Schemas

This skill embodies a Snowflake schema design expert focused on building efficient, scalable, maintainable data warehouse schemas. It covers dimensional modeling fundamentals - fact tables storing measurable business events with foreign keys to dimensions, normalized dimension tables to reduce redundancy, slowly changing dimension (SCD) types 1, 2, and 3, surrogate keys for referential integrity, and bridge tables for many-to-many fact-dimension relationships. It contrasts star schema (denormalized dimensions) against snowflake schema (normalized dimensions split across related tables like dim_product, dim_category, and dim_brand), and shows a full SCD Type 2 implementation with effective_date/expiration_date/is_current columns, a row_hash for change detection, and a clustering key added via ALTER TABLE ... CLUSTER BY.

Performance guidance covers clustering keys for time-series fact tables (clustering by date and customer, or by date and store for multi-column cases), and materialized views that pre-aggregate frequently queried metrics - the example computes daily transaction counts, revenue, and average transaction value grouped by date and store. Data type guidance recommends VARIANT for semi-structured or flexible attributes and GEOGRAPHY for spatial data, alongside a fully constrained fact table example using IDENTITY surrogate keys, NOT NULL requirements, and CHECK constraints on order total and status values.

Advanced patterns include a hub-and-spoke (Data Vault-style) structure with a central hub table keyed on a business key plus satellite tables carrying descriptive attributes and change-detection hashes, and temporal history tables that log field-level old/new values with who changed them and why, for audit trails. Governance guidance covers role-based access control - creating functional roles like data_engineer, data_analyst, and business_user and granting schema-level permissions - plus row-level security via CREATE ROW ACCESS POLICY to restrict rows by region.

Use this when designing or evolving a Snowflake data warehouse schema and needing guidance on dimensional modeling choices, clustering strategy, materialized view design, data type selection, or governance patterns like RBAC and row-level security. The skill emphasizes weighing specific business requirements, query patterns, and data volume before committing to a design, rather than applying these patterns uniformly - a star schema and a hub-and-spoke Data Vault pattern solve different problems and shouldn't be treated as interchangeable defaults.

A representative clustering example from the performance guidance:

CREATE TABLE fact_transactions (
    transaction_id NUMBER,
    transaction_date DATE,
    customer_id NUMBER,
    amount DECIMAL(12,2),
    status VARCHAR(20)
) CLUSTER BY (transaction_date, customer_id);

Inputs are the business's fact and dimension requirements - what events need measuring, what descriptive attributes need tracking, and how they change over time. Naming conventions recommend consistent prefixes (fact_, dim_, bridge_, staging_), grain information in fact table names (fact_daily_sales), descriptive attribute names over abbreviations, and standard suffixes (_key for surrogate keys, _id for natural keys). Output is a set of DDL statements defining tables, clustering keys, materialized views, constraints, and access policies.

The skill leans on Snowflake-specific features throughout: columnar storage organization, automatic clustering and micro-partitioning, time-travel and fail-safe retention, SYSTEM$CLUSTERING_INFORMATION for monitoring clustering effectiveness, versioned views and SWAP WITH for zero-downtime schema evolution, and the query profiler for diagnosing schema-related performance issues.

Data engineers and architects designing or maintaining Snowflake-based data warehouses who need concrete SQL patterns for dimensional modeling, performance tuning via clustering and materialized views, and schema governance through RBAC and row-level security.