226 ) How to design tables with SCDs: Applied to Columns or Tables?

 226 

226 ) How to design tables with SCDs: Applied to Columns or Tables?

Interview Answer:

SCD rules are handled by the pipeline script.

   SCD 1  : when it gets customer data and find a change in customer name ( SCD 1 ) so it wont add new row just corrects customer name

   SCD 2 :  when pipleline reads  a change in customer phone num ( SCD 2 ) then pipeline ..end dates the old row and it adds a new row with the updated phone num

"SCDs are fundamentally a table-level (row-level) design pattern, but in real-world data engineering, we apply different SCD rules to different columns within the same table. This is known as a Hybrid SCD model."

How to Design the Customer Table for Your Example

If a customer's name is just a typo correction (SCD Type 1) and their phone number needs a full history tracked (SCD Type 2), you don't build two separate tables. You design one dimension table using an SCD Type 2 base structure, and handle the columns differently:

1. The Table Structure (SCD Type 2 Baseline)

Your table uses row versioning with surrogate keys and validity flags:

• Customer_SK (Surrogate Key - Primary Key)
  
  
• CustomerID (Natural Key / Business Key - repeats for history)
  
  
• Customer_Name (SCD Type 1 attribute)
  
  
• Phone_Number (SCD Type 2 attribute)
  
  
• Effective_Date
  
  
• Expiration_Date
  
  
• Is_Current (Flag: 1 for active, 0 for historical)
  
  

2. How the ETL Pipeline Handles Each Column

• Handling the Phone Number (SCD Type 2 - Needs History):
  When an incoming file shows that a customer changed their phone number, the ETL pipeline detects a change on an SCD Type 2 attribute. It:
  
  
  1. Expires the current active row by setting Expiration_Date = CURRENT_DATE and Is_Current = 0.
     
     
  2. Inserts a brand new row with the new phone number, Effective_Date = CURRENT_DATE, and Is_Current = 1.
     
     
  • Result: Past invoices tied to this customer will correctly point to the old phone number using historical surrogate keys.
    
    
• Handling the Name Correction (SCD Type 1 - Just a Fix):
  If the name update is just fixing a typo (e.g., "Jon" corrected to "John"), you do not want to spin up a brand-new row, because that would unnecessarily fragment history. Instead:
  
  
  1. Your ETL runs an UPDATE statement targeting that customer.
     
     
  2. It updates Customer_Name = 'John' across the current active record (Is_Current = 1). Depending on data governance rules, you might even update it across all historical rows (Is_Current = 0) so historical reports show the correct spelling too.