使用聚集列存储索引(CCI)时仍需维度表的场景有哪些？

Great question! Flattening dimension attributes directly into your fact table makes perfect sense with CCIs—they’re built for compression and large-scale analytics, so repeating strings can often be handled efficiently. But there are still several key scenarios where hanging onto a dimension table is the smarter move. Let’s walk through them:

• High-cardinality attributes with repeated values
  Even with CCI’s impressive compression, storing repeated high-length strings (like customer names, product descriptions, or full URLs) across millions of fact table rows adds up. A dimension table lets you store each unique value once, with a small integer ID in the fact table. This reduces overall storage footprint and can speed up queries, since joining on an integer is faster than comparing strings—plus, the dimension table can be indexed for quick lookups.

• Hierarchical data that needs easy traversal
  Think geographic hierarchies (country → region → city) or organizational structures (company → department → team). Flattening all these levels into the fact table means every query that needs to aggregate or filter by a higher level has to carry all those columns. A dimension table can predefine these relationships, making recursive queries, rollups, or drill-downs far simpler and more efficient. For example, summing sales by country is trivial when you can join the fact table to the geography dimension instead of grouping on a country column in the fact table.

• Attributes requiring centralized validation or business rules
  If you have attributes that follow strict business rules (like product categories, customer statuses, or order types), a dimension table acts as a single source of truth. You can enforce constraints (like foreign keys) to ensure only valid values make it into the fact table, avoiding messy data inconsistencies. Plus, when business rules change (e.g., adding a new product category), you only update the dimension table once—no need to modify millions of rows in your CCI fact table, even though CCIs handle updates better than traditional rowstore tables.

• Shared dimensions across multiple fact tables
  If you’ve got multiple fact tables (say, sales, inventory, and returns) that all reference the same set of attributes (like product details), a dimension table eliminates redundant data. Instead of storing product names, categories, and prices in every fact table, you store them once in the product dimension. This cuts down on storage and makes maintenance a breeze—update a product’s price in the dimension, and all fact tables immediately reflect the change when joined.

• Slowly Changing Dimensions (SCDs) beyond Type 1
  You already mentioned keeping dimensions for attributes that need historical consistency, but it’s worth expanding: SCD Type 2 (where you keep historical versions of attributes, like a customer’s old address tied to past orders) can’t be easily handled with a flattened fact table. A dimension table lets you track different versions with surrogate keys and date ranges, so you can accurately report on how attributes looked at the time of each fact event. Trying to store all historical attribute versions in the fact table would bloat it beyond reason.

• Ad-hoc query flexibility for common filters
  If your users frequently filter or group by specific attributes (e.g., “show me all sales for premium customers”), a dimension table lets them quickly get a list of valid values (without running a DISTINCT on the massive fact table). You can also add indexes to the dimension table to speed up these common filter queries—since dimension tables are usually smaller than fact tables, even rowstore indexes work great here.

• Point lookups or low-volume query performance
  CCIs excel at large-scale scans and aggregations, but they’re not the fastest for single-row or small-range lookups (e.g., “what was the customer’s email for order 12345?”). A rowstore dimension table with a non-clustered index on the surrogate key will handle these point queries far quicker than digging through a CCI fact table that’s optimized for bulk operations.

At the end of the day, it’s all about balancing compression efficiency, query performance, and maintainability. Flattening works for many cases, but dimension tables still shine when you need to manage relationships, consistency, history, or specific query patterns.

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