I am fairly new to data-warehousing but have read my fair share of books and online tutorials to gain an introductory understanding of the basic components that build up a data warehouse.

My end goal is to be able to do a headcount of employees at the end of the month, therefore I designed my fact table as a monthly snapshot where each end-of-month I will populate my fact table (one row per employee per month).

My dimension table includes a DimEmployee table which is an SCD2 (any change made to an employee's information causes a new row to be introduced in the data source and I am keeping track of the most recent employee information via an is_current flag set to 'Y' or 'N').

My question is: do I need to update the dimensions daily and only insert whatever data I have at end-of-month into the fact table? Or do I update the dimensions end-of-month as well?

Thank you in advance!

1 Answer 1


You could go either in generally assuming you want to follow best practices of conformed dimensions you would want to update the customer daily so that other fact tables would have the updated information. what you don't want to do is have your previous snapshots updated to point to latest member as facts they are immutable and they should always point to the record as of the snapshot period

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.