From dimensional-modeling
Design and implement Kimball-style star schemas in DuckDB for LLM agent state persistence. Use when user needs to track agent execution, model operational data, design fact/dimension tables, implement SCD Type 2, generate surrogate keys, or build analytical views. Also triggers on "star schema", "dimensional model", "DuckDB schema", "fact table", "dimension table", "SCD Type 2", "surrogate keys", "data warehouse for agents", "design a schema for", "help me track agent state", "store execution data in DuckDB", "I need to persist agent history", or "how do I model".
How this skill is triggered — by the user, by Claude, or both
Slash command
/dimensional-modeling:dimensional-modelingThis skill is limited to the following tools:
The summary Claude sees in its skill listing — used to decide when to auto-load this skill
Design and implement Kimball-style star schemas in DuckDB for tracking agent state, execution, and operational data.
Design and implement Kimball-style star schemas in DuckDB for tracking agent state, execution, and operational data.
Abstract the data, not the behavior. Frameworks that abstract interaction patterns (chains, agents, retrievers) break when research moves faster than the abstraction. Dimensional modeling abstracts what happened (facts) in what context (dimensions) -- patterns that are 30+ years old and model-agnostic.
What are you tracking? Every schema starts by naming the business process:
| Business Process | Example Grain | Example Facts |
|---|---|---|
| Agent task execution | One tool call | duration_ms, input_tokens, output_tokens, status |
| Skill quality tracking | One validation run | error_count, warning_count, is_valid |
| CDC change detection | One page check | content_hash, change_type, severity |
| Session cost tracking | One session | total_tokens, cost_usd, tool_call_count |
| Task decomposition | One routing decision | subtask_count, agent_assigned, success |
The grain is the most atomic level of data captured in the fact table. State it as a sentence:
"One row in fact_tool_call represents a single tool invocation by a single agent in a single session."
Rules:
When choosing between grain levels, use ultrathink to reason through the trade-offs before committing.
Dimensions answer who/what/where/when/why/how about each fact row.
Full dimension table when:
Degenerate dimension (carried in fact rows) when:
See references/schema_patterns.md for dimension table templates.
Fact tables are append-only event logs. Every fact table follows these rules:
inserted_at, record_source, session_id.See references/schema_patterns.md for fact table templates.
All surrogate keys use MD5 hash of natural key components:
import hashlib
def dimension_key(*natural_keys) -> str:
"""MD5 surrogate from natural key components."""
parts = [str(k) if k is not None else "-1" for k in natural_keys]
return hashlib.md5("|".join(parts).encode("utf-8")).hexdigest()
def hash_diff(**attributes) -> str:
"""MD5 of non-key attributes for SCD Type 2 change detection."""
parts = [f"{k}={v}" for k, v in sorted(attributes.items()) if v is not None]
return hashlib.md5("|".join(parts).encode("utf-8")).hexdigest()
See references/key_generation.md for details on key design.
When a dimension attribute changes:
effective_to = NOW() and is_current = FALSE on the old roweffective_from = NOW(), is_current = TRUEhash_diff from non-key attributesThis requires no PRIMARY KEY on dimension tables (hash_key appears in multiple rows).
Views compose dimensions and facts to answer analytical questions. Always filter is_current = TRUE when joining dimensions unless doing point-in-time analysis.
See references/query_patterns.md for view recipes.
Every database needs:
meta_schema_version -- tracks schema evolutionmeta_load_log -- tracks script execution for operational visibilityThe primary use case: model agent execution as a data pipeline DAG.
Goal / Objective (why -- spans days, sessions)
Task (what -- decomposed unit)
Branch / Attempt (how -- specific approach, may be pruned)
Session (where -- execution boundary)
Agent (delegation -- routed sub-problem)
Tool chain (sequence -- ordered operations)
Tool call(atomic -- read/write/search/execute)
The five invariant operations (decompose, route, prune, synthesize, verify) become fact table grains:
| Phase | Fact Table | What It Captures |
|---|---|---|
| Decompose | fact_task_decomposition | goal -> tasks (what was broken down and how) |
| Route | fact_routing_decision | task -> agent/tool (what was assigned where) |
| Execute | fact_execution_step | atomic tool call with timing, tokens, status |
| Prune | fact_pruning_event | what was killed/abandoned and why |
| Synthesize | fact_synthesis_result | merged output with quality signal |
| Verify | fact_verification | quality checks on final output |
See references/dag_execution.md for full schema and capture mechanisms.
See references/anti_patterns.md for mistakes to avoid:
Working proof: skill-maintainer/scripts/store.py -- the full Kimball schema (v0.6.0) with 3 dimensions, 6 fact tables, analytical views, and automatic schema migration.
Provides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Searches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.
npx claudepluginhub fblissjr/fb-claude-skills --plugin dimensional-modeling