In this lesson, you will learn how to modify your database schema to support new business requirements without invalidating your historical analytical data.
As your business grows, your analytical models must evolve. You might need to add new metrics, change the granularity of a dimension, or track a new attribute. The challenge is doing this while keeping your existing reports and historical aggregates functional.
The Strategy: Additive Evolution
The safest approach in schema evolution is the Additive Principle. Rather than modifying or deleting existing columns—which can break your SELECT statements or downstream reporting tools—you should add new columns or create new versions of your tables.
Consider a fact table fact_sales that currently tracks total_amount. If the business now requires tracking tax_amount and discount_amount separately, do not rename the columns. Instead, add the new columns:
-- Adding new attributes without breaking historical queries
ALTER TABLE fact_sales
ADD COLUMN tax_amount DECIMAL(10,2),
ADD COLUMN discount_amount DECIMAL(10,2);
Managing History with SCDs
When a dimension changes—such as a customer moving from one region to another—you must preserve the historical truth. This is where your knowledge of Slowly Changing Dimensions (SCD) becomes vital. By using effective_date and expiration_date (Type 2 SCD), you evolve the schema to store history without breaking the "as-was" reporting requirements. The historical aggregates remain linked to the attribute values that were true at the time of the transaction.
Versioning Aggregation Tables
If you maintain aggregation tables for performance, schema evolution often requires a "side-by-side" deployment. If you change the logic for a monthly sales aggregate, create a new table (e.g., monthly_sales_v2) rather than altering the existing one. This allows your dashboarding tools to transition to the new schema at their own pace, ensuring that historical aggregates calculated under the old rules remain consistent and verifiable.
By favoring additive changes and keeping separate versions for breaking logic updates, you maintain the integrity of your data lineage and ensure that your analytical engine doesn't stumble when the business needs change.
Key Takeaway: Never modify or delete existing columns in a production schema; instead, utilize additive changes and versioning to ensure historical accuracy remains intact.