none
How to deal with newly added columns on the data warehouse tables. RRS feed

  • Question

  • Hello, 

    I'm trying to build an enterprise data warehouse on which we can run the business reports like KPI's, KRI's, SLA's, etc.. 

    I'm wondering how the newly added columns in the tables are being handled in the real data warehouse world. Meaning, for example I've a table called "Customers" in my data warehouse which is a historical table started from 01/01/2010 and today 12/05/2019 I want to add a new column "CustomerFavoriteColor" to the "Customers" table and I can't pre-populate data into that new field for the previous historic data as I started capturing that from today onwards. 

    So, when I run backdated reports on "Customers" table and if I leave the historic rows with "NULL" in the new field, I don't know whether customer has chosen not to provide that info or infact we were not capturing that data back that time. I would like to know how this case is being handled in the real data warehouse world? 

    Appreciate your help! 


    • Edited by guest369 Thursday, December 5, 2019 9:16 PM
    Thursday, December 5, 2019 9:15 PM

All replies

  • Any advice would be really appreciated!!
    Friday, December 6, 2019 7:36 PM
  • Add the column with NOT NULL plus the default value, i.e., empty string. And then update that column to set NULL.

    A Fan of SSIS, SSRS and SSAS

    Friday, December 6, 2019 7:51 PM
  • But maintaining "NULL" value in the new column for the previous rows would create a confusion with if there is actually a "NULL" value came in today right ? 

    For the reporting purpose we should differentiate them. Correct ? 

    Tuesday, December 10, 2019 5:32 PM
  • If I create all the new columns as Varchar data type and update the existing rows new column column values to "N/A", wondering that will mess with indexing and performance, for example if the new column that has been added is to capture the data value and when trying to filter a date range on that varchar datatype column.  

    or 

    If  I create values for each data type. ie varchar = 'No History', date = '12/31/9999', or something on that line, thinking it would create a bit confusion for Integer and other data type columns. 


    • Edited by guest369 Tuesday, December 10, 2019 6:00 PM
    Tuesday, December 10, 2019 5:51 PM
  • I mean to add the column with NOT NULL and a default constraint of the empty string. So the existing rows will have the empty string. And then update that column to allow NULL from NOT NULL. So you can know which ones are from historic data and which ones customers do not choose it.


    A Fan of SSIS, SSRS and SSAS

    Tuesday, December 10, 2019 8:07 PM
  • what if the new column is "Integer/Money/Decimal/Date" datatypes   ? 
    • Edited by guest369 Tuesday, December 10, 2019 8:20 PM
    Tuesday, December 10, 2019 8:11 PM
  • You can set the default values (which will not be used in your applications) based on the data types. 

    A Fan of SSIS, SSRS and SSAS

    Tuesday, December 10, 2019 8:38 PM