Hey - Tom here.

If choosing between star and snowflake schemas feels overwhelming, you're not alone.

This fundamental decision has long-term implications for performance, cost, and maintainability.

Today, I'm sharing a simple breakdown from my experience at large companies (spoiler: there's no "one size fits all").

Let's get into it...

What you'll learn today:

• The Core Trade-off: Clarity vs. Efficiency

• Star Schema: The user-friendly, high-performance choice.

• Snowflake Schema: The organised, storage-efficient choice.

• A Simple Decision Framework: 4 key questions to ask.

• In a Nutshell: My practical advice.

But first... The Core Concept

• Normalised: Data is split into many tables to avoid repetition. This uses less storage but requires more JOINs.

• Denormalised: Data is combined into fewer tables for faster access. This uses more storage but needs fewer JOINs and is simpler to query.

A snowflake schema is normalised. A star schema is denormalised.

The Core Trade-off: Clarity vs. Efficiency

Star Schema (Denormalised)

• Design: A central fact table (e.g., sales) connected to dimension tables (e.g., customer, product).

• Pro: Query Performance & Clarity. Fewer joins mean faster execution and simpler SQL.

• Con: Data Management. Data redundancy increases storage. Handling updates adds complexity (using Slowly Changing Dimensions).

Snowflake Schema (Normalised)

• Design: A star schema where dimensions are broken into multiple tables (e.g., Customer -> City -> Country).

• Pro: Storage Efficiency & Integrity. Less redundancy, so values are consistent and stored in one place.

• Con: Query Complexity. More joins can slow down queries and confuse users.

A Simple Decision Framework

1. What is Your Query Engine? Modern cloud warehouses (BigQuery, Snowflake) are built for fast joins, but compute is expensive. Star schemas often use less compute.

2. Storage vs. Compute Cost?

   • Star: More storage, less compute.

   • Snowflake: Less storage, more compute.

   • Advice: In the cloud, optimising for compute (star) often saves more money.

3. How Large are Your Dimensions?

   • Default to Star for simplicity and performance.

   • Consider Snowflake only for:

     • Massive dimensions (100+ columns)

     • Shared reference data (e.g., country codes)

     • Complex hierarchies

4. Who is the End-User?

   • Business Users & BI Tools: They love star schemas for their simplicity. BI tools can handle snowflakes, but you’d just be making them do extra work to simulate a star schema.

In a nutshell...

Default to a star schema in your presentation layer.

It offers the best blend of performance and usability.

It’s standard practice to use normalised structures in your processing layers and then build a star schema for your users.

Only use a snowflake schema for your final presentation layer if you have a specific, measurable reason to do so.

TL;DR:

• Star schema = simpler, faster queries, business-user friendly. Start here.

• Snowflake schema = less storage, more complex queries. Use it for specific problems.

• Best practice: Process data in normalised layers, then present a star schema.

• In the cloud, saving compute costs is usually the priority, which favours star.

Where free readers stop — Premium members keep going.

This week, inside Premium you’ll get:

• Hands-on star vs snowflake schema comparison with real databases

• Progressive exercise series: from basic exploration to advanced performance analysis

• Business analysis challenge scenarios you’ll see in actual data roles

• Create your own visual schema diagrams (using the best online tool) + dimensional modelling best practices reference

Premium isn’t just more content — it’s tools, exercises, and real-world scenarios you can practice today.

Plus, you unlock £400+ in ready-to-use resources:

• Data Dictionary Builder (£99 value)

• Executive Data Storytelling Slide Deck (£149 value)

• Data Salary Negotiation Scripts (£199 value)

And full access to the complete archive of past editions.

Founding Member Offer

Join now and get 50% off for life — just £9.50/month.
Offer ends 26/09/2025, then the price doubles.

(Don't forget to use code: FOUNDERS at checkout to lock in your lifetime deal).