Which is SCD type 2. What is its behavior?
💡 Model Answer
SCD type 2 is a Slowly Changing Dimension strategy that preserves the full history of changes to a dimension record. When a change occurs, instead of overwriting the existing row, a new row is inserted with a new surrogate key, an effective start date, an end date (often set to a future date or NULL for the current record), and a flag indicating whether the row is current. This allows queries to retrieve both the current state and the historical state of a dimension. For example, if a customer’s address changes, the old address row remains in the fact table with its original dates, while a new row with the updated address is added. This behavior is essential for time‑based reporting and auditing.
Sign in to unlock the rest of this answer
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