Designing a Scalable Data Warehouse Schema in Snowflake

Example answer

Start with business questions, source change semantics, expected growth and service targets. Declare each fact table's grain before choosing keys. For an order-line fact, store quantities and amounts at that grain, link appropriate customer/product/date dimensions, and decide whether reports need current attributes or historical versions. Validate duplicate handling, late facts, corrections and reconciliation to source totals.

Choose dimensional or more normalized structures according to those contracts. A star schema and a snowflaked dimension have different join and maintenance tradeoffs; neither guarantees faster queries. Keep reusable definitions of measures so that joins do not silently multiply revenue or average incompatible ratios.

For standard Snowflake tables, micro-partitioning is automatic. Do not describe manually creating regional/date partitions as a standard-table design step. Choose useful types and query predicates, inspect pruning, and consider a clustering key only when representative large-table queries justify its maintenance cost.

Plan warehouse isolation and concurrency separately from schema design. A multi-cluster warehouse can address concurrent demand; warehouse size and query shape affect individual-query work. Auto-suspend reduces idle compute, but billing and resume behavior do not mean compute is charged only for the exact seconds a query executes.

Test representative queries at expected volumes and skew, with explicit cache conditions and concurrent load. Measure result correctness, tail latency, bytes/partitions scanned, ingestion delay and total credits. Document the assumptions and revisit them as the workload changes instead of promising unchanged performance under unlimited growth.

Sources

Automatic micro-partitioning; warehouse considerations; multi-cluster warehouses.