Extensibility for Future Requirements

One of the key strengths of dimensional modeling is that Facts and Dimensions can evolve as business requirements change without requiring the entire warehouse or existing BI layer to be redesigned.

However, extending a dimensional model involves two separate considerations:

1. Schema Evolution - changing the structure by adding attributes or measures.
2. Historical Data Handling - determining how the new attribute or measure should be populated for existing records.

Extending Dimensions

When the business needs to track a new descriptive attribute - for example, Marketing wants to add customer_segment or loyalty_tier to the Customer dimension - you can generally add a new column to the existing dimension.

• Existing queries and BI reports that do not reference the new column continue to work unchanged.
• New queries can use the attribute for filtering, grouping, and analysis.
• The key design question is how the new attribute should be populated for existing historical records.

Handling Historical Dimension Data

Adding the column is straightforward; determining its historical meaning is often more important.

For example, suppose loyalty_tier is introduced in 2026:

Customer	Effective Period	Loyalty Tier
Alice	2024	?
Alice	2025	?
Alice	2026	Gold

You need to decide whether the new attribute should:

• Apply only from the point it was introduced, leaving historical values NULL or using an appropriate default such as Unknown.
• Be backfilled when reliable historical values can be derived from source data.
• Be historically tracked using SCD techniques if the business needs to know how the attribute changed over time.

For a Type 2 SCD, the new attribute may require updating or creating historical dimension versions so that facts continue to resolve to the correct version of the dimension.

NOTE

Adding a column does not automatically mean historical rows should be backfilled.

The decision depends on whether the new attribute has a meaningful historical value and whether the business requires as-was historical analysis.

Extending Facts

When a new measurement is introduced to an existing business process - for example, you start tracking shipping_cost for sales transactions - the approach depends primarily on grain and business process compatibility.

1. Add a Column to the Existing Fact

If the new measure belongs to the same business process and the exact same grain, it can generally be added as a new column.

For example, if the existing grain is:

One row per product sold per order line

then a new shipping_cost measure associated with that same order-line grain can be added to the existing Sales fact table.

• Existing queries that do not reference the column continue to work.
• New queries can use the new measure.
• Historical rows need to be evaluated to determine whether shipping_cost can be backfilled, should remain NULL, or should use an agreed default.

2. Create a New Fact Table

If the new measurement belongs to a different business process, timing, or grain, it should generally be modeled in a new fact table rather than added to the existing one.

For example:

• Sales Fact → one row per order line
• Shipment Fact → one row per shipment event
• Inventory Snapshot Fact → one row per product per store per day

Even though these fact tables are separate, they can still integrate through conformed dimensions such as Date, Product, Customer, and Store.

This avoids forcing measurements with incompatible grains into a single fact table.

Handling Schema Changes Safely

When extending an existing dimensional model, consider both the structural change and its impact on historical data.

Change	Typical Approach	Historical Data Consideration
Add descriptive dimension attribute	Add column	Backfill if reliable history exists; otherwise use NULL / Unknown as appropriate
Add new fact measure at same grain	Add column	Determine whether historical values can be derived
Attribute requires historical tracking	Use appropriate SCD strategy	Create/update historical dimension versions as required
New measure has different grain	Create new fact table	Do not force incompatible grains into the existing fact
New business process	Create new fact table	Reuse conformed dimensions where applicable

Design for Change

Adding a column is usually the easy part. The harder question is:

“What should this new attribute or measure mean for historical data?”

Before modifying a Fact or Dimension, determine the grain, effective date, historical requirements, backfill strategy, and default/unknown handling. This prevents a seemingly simple schema change from introducing inconsistent historical reporting.