Data Engineering on Microsoft Azure — Question 9

You need to design an Azure Synapse Analytics dedicated SQL pool that meets the following requirements:
✑ Can return an employee record from a given point in time.
✑ Maintains the latest employee information.
✑ Minimizes query complexity.
How should you model the employee data?

Answer options

• A. as a temporal table
• B. as a SQL graph table
• C. as a degenerate dimension table
• D. as a Type 2 slowly changing dimension (SCD) table

Correct answer: D

Explanation

The best approach is to model the employee data as a Type 2 slowly changing dimension (SCD) table, as this method allows for tracking historical data while maintaining the latest records. Temporal tables are also useful for tracking history but may introduce additional complexity. SQL graph tables and degenerate dimension tables are not suitable for this specific requirement of capturing historical changes in employee records efficiently.