Star schema vs snowflake schema

The trade-off in one view

Consumer navigation: Fewer joins, direct path from fact to description; More joins, normalized paths need guidance. Shared hierarchies: Reused through conformed dimensions; Reused through shared subdimension tables. Attribute change: Update wide dimension rows; Update one normalized table. Storage: Repeats dimensional attributes; Removes some repetition. Query effort: Simpler for analysts and tools; Semantic view often needed. The rows describe tendencies, not laws. The storage and join cost of either pattern depends on the platform, the data volume, and the query mix. Decide on measured workload evidence, not on diagram aesthetics.

Use a star when consumer simplicity leads

A star often gives analysts and business-intelligence tools a direct path from a measure to its descriptive dimensions. It can reduce joins and make the model easier to explain. The tradeoff is repeated dimensional attributes and more care when shared hierarchies change. Use conformed dimensions when several facts need the same definition of customer, product, location, or date. Keep the dimension key and history rule consistent. A star becomes difficult to use when similar marts give the same field different meanings. Denormalization does not mean uncontrolled duplication. Generate dimensions through a governed transformation and test attribute consistency. Record which source and owner supply each important attribute. Most business-intelligence tools generate cleaner queries against a star, and their automatic join detection assumes one.

Use a snowflake when governed reuse leads

Normalizing a hierarchy can reduce repeated data and make some shared structures explicit. It can help when several models need the same governed subdimension. The tradeoff is more joins and a model that can be harder for consumers to navigate. Normalize a hierarchy when it has independent ownership, reuse, or change behavior. A product can connect to brand, category, and department tables when those structures are governed separately. Avoid splitting a dimension only to remove a small amount of repeated text. Expose a semantic view when consumers should not reconstruct the normalized joins themselves. The physical model and consumer model can differ when the contract remains clear. Watch update cost in both directions. A normalized hierarchy concentrates each change into one table, which simplifies correction. It also concentrates load dependencies on that table. A wide star dimension spreads changes across many rows and needs consistent update logic.

Define the fact before the dimensions

State the business process and fact grain first. Examples include one row per order line, store-item-day, shipment event, or account-month. The grain determines valid keys and measures. Declare facts that share a grain in the same table when they arrive together from the same process. Separate them when they arrive on different schedules or from different processes. Merging facts with different grains into one table creates the worst version of both patterns. Separate additive, semi-additive, and non-additive measures. Sales amount can often be added across products and time. An account balance may be additive across accounts but not across dates. A ratio should usually be recalculated from its components. Choose a key strategy for source changes and history. Surrogate keys can preserve dimensional history and isolate the warehouse from mutable source identifiers. Keep the natural source key for lineage and reconciliation.

Handle dimensional history

Decide what happens when an attribute changes. A type 1 update replaces the prior value. A type 2 design creates a new version with an effective period. Use the history required by the analytical question. Test late-arriving facts and dimensions. A fact can arrive before its dimension record or after the dimension has changed. Define the temporary key, update path, and effect on earlier reports. Avoid adding history to every attribute. Version the attributes whose past value matters to analysis or evidence. Document the choice for consumers. Record the correction path. When a wrong attribute reaches history, define whether to restate prior versions, add a correcting version, or annotate the record. Consumers of regulatory or financial evidence need the answer before the first correction arrives.

Compare the actual workload

Use these questions: Run representative queries through the selected business-intelligence and transformation tools. Measure execution time, bytes processed, concurrency, cache behavior, and the effort required to write a correct query. A performance result from a synthetic join does not represent the full workload. Review how the model supports row-level access, semantic metrics, incremental loads, and data-quality tests. The pattern must fit the operating controls as well as the query plan.

What is the fact grain?
Which dimensions and hierarchies do consumers use?
How often do attributes change?
Do tools and users handle the required joins well?
Which definitions must be shared across domains?
What performance and cost appear on representative queries?
Who owns changes and tests downstream compatibility?

Weigh the platform's economics

Modern columnar warehouses compress repeated text well, which shrinks the storage argument for normalization. Engines also price joins differently. Some handle star joins efficiently and charge little for a wide denormalized dimension. Others bill by bytes scanned, where repeated attributes cost money on every full scan. Run the comparison on the target platform with representative data. Measure the queries consumers actually write: dashboards, scheduled extracts, semantic-layer queries, and ad hoc analysis. Include the change workload: dimension updates, history inserts, and backfills. A model that queries well but costs hours to update can still fail the operating test.

Plan the build sequence

Start from the business processes, not from the source tables. List the processes that produce facts: orders, shipments, payments, inventory movements, support cases. Declare the grain of each. Then list the dimensions each process needs and mark the ones several processes share. That shared list becomes the conformed dimension set. Build one fact with its dimensions end to end before scaling. Prove the grain, keys, history behavior, late-arriving handling, and reconciliation on the first process. The next process then reuses the conformed dimensions and the proven patterns. Teams that build every dimension first often discover grain conflicts late.

Work through one example

Consider a retailer modeling order lines. The fact grain is one row per order line. Customer, product, store, and date describe each row. Several other facts, such as shipments and inventory movements, share the same product and store definitions, so those dimensions become conformed. The product hierarchy matters to merchandising, finance, and supply chain, and each team changes its part on a different schedule. Normalizing product into brand, category, and department tables gives each owning team a clear boundary. The model keeps customer, store, and date denormalized because they change rarely and consumers query them directly. The consumer-facing layer presents one flat product view that hides the normalized tables. Analysts see a star. The governed structure sits behind it. This mixed result is common when the decision follows ownership and workload instead of doctrine.

Migrate deliberately when the model must change

Moving between patterns is a data migration, not a rename. Build the new model beside the old one, load history, reconcile totals and known reports, and run both in parallel for an agreed period. Switch consumers by group with a rollback path. Retire the old model on a recorded date. Two models serving the same measures will drift apart, and the organization will keep whichever number suits the argument.

Common failure modes

A declared grain that the source cannot actually produce, discovered after the model is built.
The same attribute governed in two places with different values.
Type 2 history added everywhere, doubling dimension size without an analytical need.
A normalized hierarchy that no consumer can query without help.
Facts loaded against placeholder dimension rows that never get corrected.

Document the model for the next team

Dimensional models outlive their builders. Keep a short model guide beside the schema: the list of business processes and conformed dimensions, the grain statement for each fact, the history rule for each dimension, and the owner of each shared definition. Include worked examples. One correct query for a common question, with the expected result on sample data, prevents more misuse than a hundred column descriptions.

Keep the business contract stable

Both patterns fail when grain, keys, measures, or history rules are unclear. Document each measure and dimension. Test uniqueness, referential integrity, late-arriving dimensions, and slowly changing behavior. Version breaking semantic changes. Give consumers notice and a migration window. Keep compatibility views only for a defined period, then remove them through the same release control used for other semantic changes. Use automated tests for fact-key uniqueness, dimension-key uniqueness, orphan facts, accepted nulls, hierarchy relationships, measure reconciliation, and effective-date overlap.

A practical decision

Prefer the simplest model that gives consumers correct and governed answers. A star is a strong default for a bounded analytical mart. Normalize selected hierarchies when reuse, governance, or update behavior justifies it. Test the choice on real queries and tools. The decision can be mixed. Keep most dimensions denormalized and normalize one shared hierarchy. Publish a simple semantic surface even when the physical model uses more tables. Let the consumer contract and operating ownership decide.

Related BluePi resources

Where to read next on the same subject.