Differentiate between SCD type 1 and type 2: when a new record comes, do we keep track of the old record?
💡 Model Answer
SCD (Slowly Changing Dimension) type 1 overwrites the old value with the new one. There is no historical record kept; the dimension table simply updates the existing row. This is suitable when the change is an error correction or when history is not required. In contrast, SCD type 2 preserves history by creating a new row for each change. The old row is marked inactive (often with an end date or flag), and the new row contains the updated values along with a start date. This allows queries to retrieve the state of the dimension at any point in time. Therefore, when a new record arrives, type 1 updates the existing record, while type 2 inserts a new record and retains the old one.
This answer was generated by AI for study purposes. Use it as a starting point — personalize it with your own experience.
🎤 Get questions like this answered in real-time
Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers on a discreet on-screen overlay.
Get Assisting AI — Starts at ₹500