Answer: as a Type 2 slowly changing dimension (SCD) table

## Analysis of Requirements The question specifies three key requirements for modeling employee data in an Azure Synapse Analytics dedicated SQL pool: 1. **Retrieve employee records from any specific point in time** - This requires maintaining historical versions of employee data 2. **Provide access to most current employee information** - Need to easily identify the latest version of each employee record 3. **Minimize query complexity** - The solution should not require complex joins or filtering logic ## Evaluation of Options ### Option A: Temporal Table - **Pros**: Temporal tables automatically maintain history and support point-in-time queries using simple SQL syntax - **Cons**: **Critical limitation** - Temporal tables are **not supported** in Azure Synapse Analytics dedicated SQL pools. This makes option A unsuitable despite its theoretical alignment with the requirements. ### Option B: SQL Graph Table - **Pros**: Useful for modeling complex relationships between entities - **Cons**: Does not inherently support historical versioning or point-in-time queries. Graph tables are designed for relationship traversal, not temporal data management. ### Option C: Degenerate Dimension Table - **Pros**: Typically used in dimensional modeling for transaction-level attributes - **Cons**: Not designed for maintaining historical versions of dimension data. Degenerate dimensions don't support the point-in-time query requirement. ### Option D: Type 2 Slowly Changing Dimension (SCD) Table - **Pros**: - **Perfectly addresses point-in-time queries**: Each record includes effective date ranges (start_date, end_date) allowing queries for any historical point - **Maintains latest information**: Includes an "is_current" flag or uses an end_date of NULL/MAX to identify the current version - **Minimizes query complexity**: Simple WHERE clauses can filter by date ranges or current flag without complex joins - **Supported in Synapse**: Fully compatible with Azure Synapse Analytics dedicated SQL pools ## Why Type 2 SCD is the Optimal Choice 1. **Historical Tracking**: Type 2 SCD creates new records for each change while preserving old versions, enabling accurate point-in-time queries 2. **Current Record Identification**: Using an "is_active" flag or NULL end_date makes retrieving current information straightforward 3. **Query Simplicity**: Basic SQL queries can filter by date ranges or current status without requiring complex temporal logic 4. **Synapse Compatibility**: Unlike temporal tables, Type 2 SCD is fully supported and commonly implemented in Azure Synapse Analytics 5. **Best Practice Alignment**: Type 2 SCD is the standard approach for maintaining dimension history in data warehousing scenarios The combination of historical preservation, current record accessibility, and query simplicity makes Type 2 SCD the most appropriate modeling approach for these requirements in an Azure Synapse Analytics environment.