0

I see there is an importance in having a row inserted and a row last updated fields in a fact table. But I could not find any standard data warehouse or a reference which says that this is a good thing to do. I am uncertain whether this is because it is a bad practice; if so why should it be so? If it is because of the data size, I see it is only 8bytes for a full date field.

Any help is greatly appreciated!!!

picmate 涅
  • 3,951
  • 5
  • 43
  • 52

2 Answers2

2

There's nothing talking about whether it's a good or bad practice because we include creation time and updated time only if we need them or ever will need them.

It's a "good thing to do" if you need to access those columns and a "bad thing to do" if your table will never require those columns.

skyuzo
  • 1,140
  • 7
  • 13
  • Hi @skyuzo, Thanks for your prompt answer. I will need them only during a problematic situation. I don't think the number of bytes is a problem in this! – picmate 涅 Oct 12 '11 at 11:38
  • Yes, the number of bytes is insignificant. However if you really want to, you could default to adding those timestamps to your tables in your development environment and then remove them in your production environment if you never use them. – skyuzo Oct 12 '11 at 11:40
1

The inclusion of insert and update timestamps in your data warehouse tables allow you to be able to report from the perspective of as was and/or as is with regards to the data warehouse. These timestamps would be in addition to any timestamps that may be captured from the source.

They also make troubleshooting easier and in a worse case scenario the ability to back out a set of data from a specific run of an ETL process.

At a previous client, the data model we implemented included upwards of 6 different timestamps to provide slowly changing history, as is/ as was reporting, and source related time stamps. It made for very flexible reporting but also increased the learning curve of how to get exactly what you wanted from the table(s).

Rob Paller
  • 7,736
  • 29
  • 26