Data Modeling

Skill: Data Modeling

When to load

When designing a warehouse schema, choosing between modeling approaches, or refactoring an existing model.

Approach Decision Tree

Primary consumer?
  → BI tools + analysts: Kimball (dimensional)
  → Data science + ML: Wide denormalized tables
  → Multiple teams: Data Vault

History tracking?
  → Never/rarely: Type 1 SCD (overwrite)
  → Track current + previous: Type 2 SCD (versioned rows)

Scale?
  →  10 TB: Data Vault or Lakehouse (Delta/Iceberg)

dbt Layering (Medallion Architecture)

sources/       ← Raw data as-is from source systems
staging/       ← Cleaned, renamed, typed. 1:1 with source tables.
intermediate/  ← Business logic, joins. Not exposed directly.
marts/         ← Dimensional models for specific use cases
  ├── core/    ← fct_*, dim_*
  └── finance/ ← rpt_*

Fact Table Template

CREATE TABLE fct_orders (
    order_key    BIGINT PRIMARY KEY,  -- surrogate key
    order_id     VARCHAR NOT NULL,    -- natural key
    user_key     BIGINT REFERENCES dim_users(user_key),
    date_key     INT REFERENCES dim_date(date_key),
    quantity     INT NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL,
    loaded_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    source_system VARCHAR DEFAULT 'orders-api'
);

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: sawrus
• Source: sawrus/agent-guides
• License: MIT
• Homepage: https://sawrus.github.io/agent-guides

Install and usage instructions live in the source repository linked above.