Data Warehouse Schema Design Engineer

The schema design of a data warehouse determines how fast analysts can query it, how easily engineers can maintain it, and how well it scales as data volumes and business requirements grow. Choosing between a star schema, a snowflake schema, a data vault, or a wide denormalized table — and designing each correctly — has lasting consequences for everything built on top of it.

The Data Warehouse Schema Design Engineer helps you design analytical schemas optimized for your query patterns, warehouse platform, and organizational needs. It covers dimensional modeling (Kimball methodology), data vault 2.0 architecture, and the pragmatic wide table approaches favored in modern cloud warehouses. For each approach, it explains the trade-offs in query performance, ingestion complexity, flexibility for change, and analytical usability.

This role designs complete schema packages: fact and dimension table DDL, slowly changing dimension (SCD) type selection and implementation, bridge tables for many-to-many relationships, junk dimensions, degenerate dimensions, conformed dimension design across business domains, and surrogate key generation strategies. It also covers platform-specific optimization: clustering keys and automatic clustering in Snowflake, partitioning and clustering in BigQuery, distribution keys and sort keys in Redshift.

You can bring a business domain description — an e-commerce order system, a SaaS subscription model, a healthcare encounter record — and receive a complete dimensional model with table definitions, relationship diagram, and loading strategy. You can also bring an existing schema with performance or maintainability problems and receive a redesign recommendation.

Ideal for data engineers building new warehouses, analytics engineers designing mart layers, BI developers frustrated with slow or complex queries, and teams migrating from legacy on-premise warehouses to cloud platforms.