What this prompt does
This prompt makes the AI implement slowly changing dimension handling — Type 1, 2, and 3 — for a real dimension table. You name the [dimension_table], the [warehouse_platform], what it tracks ([entity_description]), its [column_count], the [update_frequency], and the [source_system], and the model builds SCD Type 2 for [scd2_columns] with surrogate_key, effective_from, effective_to, and is_current, plus a single atomic MERGE that expires old rows and inserts new versions.
The structure works because it separates columns by how much history they need and pins the integrity guarantees that make point-in-time queries trustworthy. Type 1 overwrites [scd1_columns] in place; Type 3 keeps current and previous for [scd3_columns]; a staging process compares against the current dimension using [comparison_method] and classifies each row as INSERT, UPDATE-SCD2, UPDATE-SCD1, or NO-CHANGE. It handles late-arriving updates that land after related facts, provides point-in-time and full-history example queries, and adds a check that no effective ranges overlap and exactly one is_current=true per [natural_key]. That overlap check is what keeps history honest.
When to use it
- You need to preserve history on a dimension so reports reflect attribute values as they were at the time.
- You have a mix of columns: some need full history, some can overwrite, some need just the previous value.
- You're loading from CDC or a daily batch and need clean change detection.
- You need trustworthy point-in-time queries joining facts to the correct dimension version.
- You have to handle dimension updates arriving after the related fact rows.
- You want an integrity check guaranteeing no overlapping date ranges and one current row per key.
Example output
Expect SQL and logic: the altered [dimension_table] with SCD2 columns, a single MERGE statement for the SCD2 columns, in-place UPDATE for SCD1, current/previous handling for SCD3, a staging-and-classify step using [comparison_method], a late-arriving-update strategy with its impact on historical queries explained, example queries (point-in-time as of [example_date], full history for an [entity_key], fact-to-version join), and a data quality check for overlaps and single is_current per [natural_key].
Pro tips
- Be deliberate about which columns go in
[scd2_columns]versus[scd1_columns]— tracking history on a volatile field like last_login_date explodes row counts for no analytical value. - Use a hash
[comparison_method]over the concatenated SCD2 columns so change detection is fast and doesn't compare column by column. - Treat the overlap and single-is_current check as non-negotiable; without it, a bad load silently produces double-counted point-in-time results.
- Plan
[scd3_columns]only where you genuinely need just the prior value (tier upgrade/downgrade analysis) — Type 3 can't reconstruct a full history. - Test the late-arriving path explicitly: back-date the SCD2 record and confirm existing fact rows still resolve to the correct version.
- Run the MERGE as a single atomic operation so a mid-load failure can't leave a dimension with expired-but-not-replaced rows.
