home / skills / a5c-ai / babysitter / scd-implementation-generator

scd-implementation-generator skill

safe

/plugins/babysitter/skills/babysit/process/specializations/data-engineering-analytics/skills/scd-implementation-generator

This skill generates SCD implementations across platforms, enabling accurate historical tracking with automated patterns, surrogate keys, and platform-specific

npx playbooks add skill a5c-ai/babysitter --skill scd-implementation-generator

Review the files below or copy the command above to add this skill to your agents.

Files (2)

SKILL.md

1.7 KB

---
name: SCD Implementation Generator
description: Generates Slowly Changing Dimension implementations across platforms
version: 1.0.0
category: Data Modeling
skillId: SK-DEA-016
allowed-tools:
  - Read
  - Write
  - Edit
  - Glob
  - Grep
  - Bash
---

# SCD Implementation Generator

## Overview

Generates Slowly Changing Dimension implementations across platforms. This skill automates the creation of SCD patterns for proper historical tracking.

## Capabilities

- SCD Type 1/2/3/4/6 implementation
- MERGE statement generation
- dbt snapshot configuration
- Historical tracking optimization
- Surrogate key management
- Effective date handling
- Current flag management
- Mini-dimension design

## Input Schema

```json
{
  "dimension": {
    "name": "string",
    "columns": ["object"],
    "businessKey": ["string"]
  },
  "scdType": "1|2|3|4|6",
  "platform": "snowflake|bigquery|redshift|dbt",
  "trackingColumns": ["string"]
}
```

## Output Schema

```json
{
  "ddl": "string",
  "mergeStatement": "string",
  "dbtConfig": "object",
  "documentation": "string"
}
```

## Target Processes

- SCD Implementation
- Dimensional Model Design
- dbt Model Development

## Usage Guidelines

1. Define dimension structure with business keys
2. Select appropriate SCD type for business requirements
3. Specify target platform for syntax generation
4. Identify columns to track for historical changes

## Best Practices

- Use SCD Type 2 for attributes requiring full history
- Implement surrogate keys for dimension tables
- Use effective dates rather than just current flags
- Consider mini-dimensions for rapidly changing attributes
- Test SCD logic with representative change scenarios

Overview

This skill generates Slowly Changing Dimension (SCD) implementations across platforms to automate correct historical tracking in dimensional models. It produces DDL, platform-specific MERGE statements, dbt snapshot/config snippets, and concise documentation to accelerate implementation. Designed for Snowflake, BigQuery, Redshift, and dbt targets, it supports Type 1/2/3/4/6 patterns and surrogate key management.

How this skill works

Provide a dimension definition with columns, business key(s), desired SCD type, target platform, and tracking columns. The skill generates a ready-to-run DDL for the dimension table, a MERGE/upsert statement tailored to the platform, dbt configuration or snapshot settings when requested, and short documentation describing behavior. It also incorporates effective date logic, current-flag handling, and options for mini-dimensions or surrogate keys.

When to use it

When implementing dimension tables that must preserve historical attribute values
When migrating SCD logic between Snowflake, BigQuery, Redshift, or dbt
When you need consistent MERGE/upsert statements and testing scenarios
When introducing surrogate keys, effective dating, or mini-dimensions
When automating dbt snapshot configurations for change capture

Best practices

Choose SCD Type 2 for attributes that require full auditability and row-level history
Use surrogate keys to decouple business keys from technical identifiers
Prefer effective_from/effective_to dates plus a current flag for clear timeline queries
Design mini-dimensions for high-cardinality or rapidly changing attributes
Test with representative change scenarios (insert, update no-change, attribute change, delete)

Example use cases

Generate a Snowflake DDL and MERGE statement for a customer dimension with SCD Type 2
Produce dbt snapshot configuration and docs for product attribute tracking
Create a Type 1 script to overwrite attributes that should not retain history
Design a mini-dimension for marketing contact preferences and link it to the main dimension
Convert existing Redshift SCD logic into BigQuery-compatible MERGE statements

FAQ

Which SCD type should I pick for audit requirements?

Use Type 2 when you need row-level historical audit. Type 1 if history isn’t required, and Type 3 for limited historical attributes.

Can it generate dbt snapshots and model configs?

Yes. The skill outputs dbt snapshot configuration and model snippets when the platform is set to dbt.