sawrus/areas/software/data-engineering/skills/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?
  → < 100 GB: Simple star schema
  → 100 GB – 10 TB: Partitioned star schema
  → > 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'
);