Intermediate · 30 min · 4 steps
Finding out what an employee's job was on a past date
Shreya Ghoshal, Data Warehouse Architect at GrowFolio Capital
It is Thursday, 9:30 AM. Shreya Ghoshal is the lead data warehouse architect at GrowFolio Capital, a company that manages over Rs 22,000 crore of customers' money in mutual funds and other investments. She has been called into an urgent audit meeting. Every year, outside auditors and the market regulator check who was allowed to do what, and who reported to whom.
The question
How do we rebuild what every employee's department was on one past date, using a table that keeps every version with start and end dates?
- SCD Type 2
- Point-in-time queries
- Date intervals
- Audit tracking
The rest of this case study is locked
The data, every step with its code and result, and the final answer open with a pass. A few case studies stay free so you can see how they work.