Engagement Rate Card and Staffing Hierarchy SCD2 Migration Script

Role: Lead Data Architect for Enterprise Advisory Platforms with expertise in dimensional modeling and Slowly Changing Dimensions.

Context

• Target cloud data warehouse: {{warehouse_platform}}
• Staging feed table: {{source_feed_table}}
• Slowly Changing Dimension target: {{dim_target_table}}
• Business natural keys: {{natural_key_columns}}
• Version-triggering attributes: {{tracked_attribute_columns}}
• Upstream change timestamp: {{effective_timestamp_column}}

Task

Author an enterprise-grade {{warehouse_platform}} SQL migration and load script implementing a Type 2 Slowly Changing Dimension (SCD2) on {{dim_target_table}} using records from {{source_feed_table}}, ensuring accurate point-in-time billing rate lookups across historic staffing matrices.

Method

1. Generate standard table DDL for {{dim_target_table}} featuring surrogate keys, natural keys {{natural_key_columns}}, tracked attributes {{tracked_attribute_columns}}, valid_from, valid_to, is_current_flag, and a SHA256 row hash.
2. Write a staging transformation query hashing {{tracked_attribute_columns}} to detect mutations deterministically.
3. Identify net-new natural keys from {{source_feed_table}} not present in {{dim_target_table}} for direct insertion.
4. Detect mutated records where natural keys match an active record (is_current_flag = TRUE) but the hash of {{tracked_attribute_columns}} differs.
5. Construct a dual-action atomic MERGE or transactional UPDATE/INSERT statement compatible with {{warehouse_platform}}.
6. Expire active records by setting valid_to to {{effective_timestamp_column}} and is_current_flag to FALSE.
7. Insert incoming altered rows with valid_from set to {{effective_timestamp_column}}, valid_to set to high-date ('9999-12-31'), and is_current_flag to TRUE.
8. Include comprehensive post-load validation assertions verifying no overlapping validity intervals exist per natural key.

Constraints

• MUST conform strictly to {{warehouse_platform}} dialect specifications.
• MUST maintain zero-gap, zero-overlap temporal continuity between valid_from and valid_to intervals.
• MUST NOT rely on non-deterministic hashing algorithms (use SHA2_256 or MD5 with null coalescing).
• MUST wrap transformation steps in an explicit transaction or idempotent MERGE block.

Output format

1. Target Dimension DDL: Complete CREATE TABLE statement with clustering/distribution keys.
2. SCD2 Transformation Engine: Fully executable SQL script containing hash generation and atomic merge/load logic.
3. Data Quality Validation Script: Post-execution diagnostic queries checking temporal consistency and duplicate current flags.

Self-review

• Confirm all columns in {{tracked_attribute_columns}} are cast to strings and null-coalesced in hash generation.
• Check that high-water mark logic correctly handles multiple updates within the same batch for {{natural_key_columns}}.
• Verify valid_to timestamps correspond exactly to the adjacent record's valid_from minus one discrete time unit.