# Schema Evaluation > Use when evaluating or designing data warehouse schemas for analytical workloads. Covers star schemas, snowflake schemas, data vault, OBT patterns, grain definition, SCD strategies, normalization trade-offs, and data contracts between producers and consumers. Do not use for pipeline orchestration or ETL flow design (use pipeline-design). - Skill: `tomevault-io/schema-evaluation` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/schema-evaluation` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/schema-evaluation/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: DevOps & Infra - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/schema-evaluation --- # Schema Evaluation ## Purpose Evaluate and design data warehouse schemas for analytical workloads. Covers star schemas, snowflake schemas, data vault, and One Big Table (OBT) patterns. Assesses grain definition, normalization trade-offs, slowly changing dimension strategies, and data contracts between producers and consumers. ## Scope Constraints Reads schema definitions, DDL, ERDs, data dictionaries, and query patterns for analysis. Does not execute queries, modify databases, or manage pipeline orchestration. ## Inputs - Business domain and key entities (e.g., e-commerce: orders, products, customers) - Analytical queries the schema must support (e.g., "revenue by product category by month") - Data volume estimates (row counts, growth rate) - Source systems and their update patterns (CDC, full refresh, event stream) - Existing schema (if evaluating rather than designing from scratch) ## Input Sanitization No user-provided values are used in commands or file paths. All inputs are treated as read-only analysis targets. ## Procedure ### Progress Checklist - [ ] Step 1: Define the grain - [ ] Step 2: Identify facts and dimensions - [ ] Step 3: Choose a modeling approach - [ ] Step 4: Design SCD strategy - [ ] Step 5: Define data contracts - [ ] Step 6: Validate against query patterns - [ ] Step 7: Document the schema ### Step 1: Define the Grain Identify the grain of each fact table — what does one row represent? A single transaction? A daily snapshot? A session event? The grain determines everything downstream. Document the grain as a clear English sentence: "One row = one order line item" or "One row = one daily active user per product." ### Step 2: Identify Facts and Dimensions Separate measurable facts (revenue, quantity, duration, count) from descriptive dimensions (customer, product, date, geography). For each: - **Facts:** Data type, aggregation method (SUM, AVG, COUNT DISTINCT), nullability - **Dimensions:** Cardinality, hierarchy levels, whether it changes over time (SCD candidate) ### Step 3: Choose a Modeling Approach Evaluate which pattern fits the requirements: - **Star schema** — Simple, fast queries, denormalized dimensions. Best for straightforward BI with a known query pattern. - **Snowflake schema** — Normalized dimensions for storage efficiency and consistency. Best when dimensions are large or shared across many facts. - **Data vault** — Hub, link, satellite pattern for auditability and flexibility. Best when source systems change frequently or full history is required. - **One Big Table (OBT)** — Fully denormalized single table. Best for small teams, simple analytics, or when query simplicity outweighs storage concerns. Document the chosen approach and the reasoning behind it. ### Step 4: Design Slowly Changing Dimension Strategy For each dimension that changes over time, specify the SCD type: - **Type 1** — Overwrite. No history. Simple, but you lose the old value. - **Type 2** — Add new row with effective dates. Full history, but increases row count and query complexity. - **Type 3** — Add column for previous value. Limited history (only one prior value), but simple to query. - **Type 6** — Hybrid (1+2+3). Current value column plus history rows. Best of both but most complex. Document which SCD type applies to each changing attribute and why. ### Step 5: Define Data Contracts For each source-to-warehouse interface, specify the contract: - Schema expectations (required fields, data types, allowed values) - Freshness SLA (how soon after source update must the warehouse reflect it?) - Quality thresholds (max null rate, uniqueness constraints, referential integrity) - Breaking change policy (how are schema changes communicated and handled?) ### Step 6: Validate Against Query Patterns Test the proposed schema against the required analytical queries: - Can each query be answered with at most 2-3 joins? - Are the most common filters (date, category, status) on dimension keys? - Is the grain appropriate — not too fine (wasteful) or too coarse (lossy)? - Are aggregate tables or materialized views needed for high-frequency dashboards? ### Step 7: Document the Schema Produce a complete schema specification with DDL, relationships, and usage notes. > **Compaction resilience**: If context was lost during a long session, re-read the Inputs section to reconstruct what system is being analyzed, check the Progress Checklist for completed steps, then resume from the earliest incomplete step. ## Handoff - Hand off to pipeline-design if the evaluation reveals ETL/ELT orchestration or data flow architecture needs. - Hand off to guardian/compliance-review if schema design surfaces data governance, PII handling, or regulatory compliance concerns. ## Output Format ```markdown # Schema Evaluation: [Domain/Project Name] ## Grain Definitions | Fact Table | Grain (one row = ...) | Estimated Rows | Growth Rate | |------------|----------------------|----------------|-------------| | ... | ... | ... | ... | ## Entity Relationship Summary ``` [ASCII diagram showing fact and dimension relationships] ``` ## Modeling Approach **Chosen:** [Star / Snowflake / Data Vault / OBT] **Rationale:** [1-2 sentences] ## Fact Tables ### fct_[name] | Column | Type | Description | Aggregation | |--------|------|-------------|-------------| | ... | ... | ... | SUM/AVG/... | ## Dimension Tables ### dim_[name] | Column | Type | Description | SCD Type | |--------|------|-------------|----------| | ... | ... | ... | 1/2/3 | ## Slowly Changing Dimensions | Dimension | Attribute | SCD Type | Rationale | |-----------|-----------|----------|-----------| | ... | ... | ... | ... | ## Data Contracts | Source → Target | Freshness SLA | Quality Checks | Breaking Change Policy | |-----------------|---------------|----------------|----------------------| | ... | ... | ... | ... | ## Query Validation | Query Pattern | Tables Involved | Join Count | Performance Notes | |---------------|----------------|------------|-------------------| | ... | ... | ... | ... | ``` ## Quality Checks - [ ] Every fact table has a clearly stated grain in plain English - [ ] Facts and dimensions are properly separated — no business logic in fact tables - [ ] SCD strategy is specified for every dimension attribute that changes - [ ] Data contracts define freshness SLA, schema expectations, and quality thresholds - [ ] The schema supports all required analytical queries with minimal joins - [ ] Cardinality estimates are documented for key dimensions - [ ] Surrogate keys vs natural keys decision is documented and consistent - [ ] Indexing strategy is defined for common filter and join columns ## Evolution Notes --- > Converted and distributed by [TomeVault](https://tomevault.io/claim/dtsong) — claim your Tome and manage your conversions.