Slowly Changing Dimension AI Prompt

Implement a Type 2 Slowly Changing Dimension (SCD2) for the table {{dim_table}} in {{database_type}}.

Natural key: {{natural_key}}
Tracked attributes (trigger new version): {{tracked_columns}}
Non-tracked attributes (overwrite in place): {{non_tracked_columns}}

1. Table design:
   - Add columns: surrogate_key (BIGINT IDENTITY), valid_from (DATE), valid_to (DATE), is_current (BOOLEAN)
   - valid_to for current rows = '9999-12-31' (sentinel value)
   - is_current = TRUE for current rows (redundant but improves query performance)

2. Initial load: INSERT all rows with valid_from = first_seen_date, valid_to = '9999-12-31', is_current = TRUE

3. Incremental merge logic:
   For each incoming row:
   a. NEW RECORD (natural key not in dim): INSERT with valid_from = today, valid_to = '9999-12-31', is_current = TRUE
   b. CHANGED RECORD (tracked columns differ from current version):
      - UPDATE existing current row: valid_to = today - 1, is_current = FALSE
      - INSERT new row: valid_from = today, valid_to = '9999-12-31', is_current = TRUE
   c. UNCHANGED RECORD: no action
   d. DELETED RECORD (exists in dim but not in source): optionally set is_current = FALSE

4. Point-in-time query:
   SELECT * FROM {{dim_table}} WHERE {{natural_key}} = 'X' AND valid_from <= '{{as_of_date}}' AND valid_to > '{{as_of_date}}'

5. Current records query:
   SELECT * FROM {{dim_table}} WHERE is_current = TRUE
   (Always faster than the date range query — index on is_current)

6. Non-tracked attribute updates: UPDATE current row in-place, no new version needed

Return: CREATE TABLE DDL, MERGE statement, point-in-time query, and current records query.