数据仓库中Dimensional Model与De-normalized Model的区别及模型咨询

Great question—let’s unpack these terms step by step, since data warehouse modeling jargon often overlaps but isn’t interchangeable.

1. Are Dimensional Model and De-normalized Model the same? #

No, they’re not identical. Here’s why:

• A Dimensional Model is a framework for organizing data warehouse data around business processes (facts) and descriptive context (dimensions). It includes specific design principles like using fact tables, dimension tables, consistent conformed dimensions, and often (but not always) proxy keys.
• De-normalization is a design technique used to optimize query performance by reducing join operations. Dimensional models frequently use de-normalization (e.g., star schemas with wide, flat dimension tables), but the dimensional model itself is broader—it can also include normalized sub-types like snowflake schemas.

In short: De-normalization is a tool that dimensional models often leverage, but the two aren’t synonyms.

2. Snowflake vs. Flat Dimensions: Your Understanding Is Correct #

You’re right to connect these to dimensional modeling categories:

• Snowflake schemas are a normalized variant of dimensional models. Here, dimension tables are split into smaller, normalized sub-tables (e.g., a "Customer" dimension split into Customer, Address, and Contact tables) to reduce data redundancy. This is still a dimensional model—it just uses normalized dimensions instead of de-normalized ones.
• Flat (de-normalized) dimensions (like the wide dimension tables in star schemas) are the more common de-normalized form of dimensional models. They combine all related attributes into a single table to minimize joins and speed up queries.

So both fall under the umbrella of dimensional modeling—they’re just two different approaches to structuring dimension tables.

3. What’s the Model Called When Using OLTP Natural Keys Instead of Surrogate Keys? #

When you skip surrogate keys and use the OLTP system’s native primary keys (natural keys) to link fact and dimension tables, this is typically referred to as a natural key-based dimensional model.

A quick caveat: While this is a valid modeling approach, it’s generally not recommended for enterprise data warehouses. Natural keys can change over time (e.g., a business might reassign customer IDs, merge accounts, or update product codes), which breaks historical data consistency and makes it hard to track slowly changing dimensions (SCDs). Surrogate keys were invented specifically to solve these problems, so they’re the industry best practice.

内容的提问来源于stack exchange，提问作者Aditya