Hot Swappable Dimension
A hot swappable dimension is a pattern where different users analyze the same central fact table using their own interchangeable dimension data. The facts remain identical, but the dimension used to interpret them changes depending on the user.
"Swapping" means selecting which dimension data a query uses to interpret the shared facts. A reporting layer can expose a common interface such as dim_stock, with explicitly configured routing or filtering that selects the appropriate fund's data. "Hot" means the selection happens at query time.
You use this approach when the facts are identical for everyone, but the descriptions and categories belong privately to each user.
Consider a financial platform that provides daily stock market data to several investment funds. The stock prices and trading volumes are public and identical for everyone. However, each fund has its own private analysts who rate the stocks and assign custom risk levels. This pattern allows each fund to analyze the shared public data using their own private ratings without exposing their confidential strategies to competitors.
Shared Fact Table (one copy, used by all funds)
| date_key | ticker_key | closing_price | volume |
|---|---|---|---|
| 20260101 | AAPL | 150.00 | 48,200,000 |
| 20260101 | TSLA | 242.50 | 91,400,000 |
| 20260101 | JPM | 168.30 | 12,700,000 |
NOTE
Despite its name, the ticker_key is not a surrogate key. It identifies a specific stock, such as AAPL for Apple, and acts as the vital link connecting the shared price facts to each user's private, hot-swappable dimension table.
dim_stock_fund_a:
| ticker_key | rating | internal_sector | risk_band |
|---|---|---|---|
| AAPL | Buy | Consumer Tech | Low |
| TSLA | Hold | Consumer Tech | High |
| JPM | Buy | Financials | Low |
dim_stock_fund_b:
| ticker_key | rating | internal_sector | risk_band |
|---|---|---|---|
| AAPL | Sell | Hardware | Medium |
| TSLA | Buy | Clean Energy | Medium |
| JPM | Hold | Retail Banking | Low |
Both funds run the same report against shared price facts using their own classifications. Access controls protect each fund’s private classifications.
How to handle history
This example assumes the private attributes are slowly changing and that you only query their current values. The dimension table keeps exactly one row per stock and overwrites attributes in place when they change.
Hot swapping does not require this approach. If you need to track daily historical changes to these ratings, the historical classifications can be retained using effective-dated dimension versions. A periodic snapshot fact is another option for recording daily ratings.
Implementation Choices
You can implement this pattern in two ways depending on your scale and security requirements.
Separate tables: Each fund has its own dimension table, as shown in the example above. This provides strong physical isolation. A forgotten filter can't leak another fund's data, because those rows aren't in the table. However, the operational cost grows linearly because every new fund requires a new table to load, monitor and secure.
NOTE
While a bug in the application routing could still theoretically expose the wrong data, the separate table design strongly protects confidentiality through physical table isolation at the database level.
Shared table: One table holds all classifications, with one current row per (fund_id, ticker_key). This scales easily to hundreds of users because you do not need to create new tables. Instead of swapping physical tables, you rely on database row-level security to filter the active user's rows at query time. The trade-off is that a missing security filter could leak private classifications across funds.
Example (Single Dimension with all funds data):
| fund_id | ticker_key | rating | internal_sector | risk_band |
|---|---|---|---|---|
| FUND-A | AAPL | Buy | Consumer Tech | Low |
| FUND-B | AAPL | Sell | Hardware | Medium |
Avoid Fund-Specific Columns and Duplicate Facts
Putting everyone's data into a traditional wide dimension table with columns like fund_a_rating and fund_b_rating creates a massive confidentiality risk. Because all parties query the exact same columns, the design fails to isolate private data.
Copying the fact table for every fund is a much worse approach. The dimension table remains small because it holds only one row per stock. In contrast, the shared fact table holds one row per stock per trading day, making it significantly large. Duplicating this table simply to attach a few private categories wastes an enormous amount of space.
The data contract must stay consistent
Reusing an unchanged report requires a shared interface. Swapping only works if every dimension exposes the same key, column names and data types.
If a new fund needs a unique attribute, the new column will remain empty for all other funds. Furthermore, if one fund adds a unique column, reports referencing it will work for that fund but fail for everyone else.
Treat the core column set as a strict contract and version it centrally rather than per fund.