design-documentation

Design Documentation Skill

When to Use

Trigger phrases:

• "Update design docs", "Design aktualisieren"
• "Architecture diagram", "ER diagram"
• "Visualize vault", "Vault model diagram"
• "Staging dataflow"
• "Update Mermaid"

Recommended Folder Structure

design/
├── staging/
│   ├── _template.md              ← Staging template
│   └── <source_system>/          ← Staging docs per source
│       └── <entity>.md
├── raw-vault/
│   ├── _template_hub.md          ← Hub template
│   ├── _template_link.md         ← Link template
│   └── <source_system>/          ← Vault docs per source
│       ├── 01_analyse.md
│       └── vault-model.mmd
└── data-flow/
    └── end_to_end.md             ← Overall architecture

Templates

Staging Design

Create one document per staging entity with:

• Source system + file/table path
• Business Key + Hash Key definitions
• Mermaid Flowchart (dataflow)
• Column table (Name, Type, Category)

flowchart LR
    subgraph SOURCE["Source System"]
        P["<source_file_or_table>"]
    end
    subgraph EXT["External Table"]
        E["stg.ext_<entity>"]
    end
    subgraph STG["Staging View"]
        S["stg.<staging_model>"]
    end
    P --> E --> S
    S -->|"hk_<entity>"| HUB["hub_<entity>"]
    S -->|"hd_<entity>"| SAT["sat_<entity>"]

Hub Design

Create one document per Hub with:

• Business Key origin
• Mermaid ER diagram (Hub-Sat relationship)
• Column definition
• Dependencies

erDiagram
    HUB_ENTITY {
        CHAR64 hk_entity PK
        datatype BUSINESS_KEY BK
        DATETIME2 dss_load_date
        VARCHAR50 dss_record_source
    }
    SAT_ENTITY {
        CHAR64 hk_entity FK
        CHAR64 hashdiff
        datatype ATTRIBUTE_1
        datatype ATTRIBUTE_N
        DATETIME2 dss_load_date
        CHAR1 dss_is_current
    }
    HUB_ENTITY ||--o{ SAT_ENTITY : "has"

Vault Overall Model

Maintain an overall ER diagram of all vault objects:

erDiagram
    HUB_A ||--o{ SAT_A : "has"
    HUB_A ||--o{ LINK_A_B : "connects"
    HUB_B ||--o{ SAT_B : "has"
    HUB_B ||--o{ LINK_A_B : "connects"

Analysis Document (5 Steps)

Create design/raw-vault/<source_system>/01_analyse.md with:

1. Source System Analysis: Tables/files inventory
2. Business Key Identification: Natural keys per entity
3. Relationship Analysis: Which tables reference each other?
4. Satellite Splitting: Group by change frequency
5. Vault Model: Overall ER diagram

Mermaid Conventions

• Theme: base
• ER diagrams for Vault relationships
• Flowcharts for dataflows (LR orientation)
• Files: .mmd for standalone, embedded in .md for documentation
• Prefix all objects consistently with source system identifier