OBT Design Optimizer

---
name: obt-design-optimizer-01cab5524f57
description: Designs and optimizes One Big Table (OBT) patterns
version: 1.0.0
category: Data Modeling
skillId: SK-DEA-020
allowed-tools:
  - Read
  - Write
  - Edit
  - Glob
  - Grep
  - Bash
graph:
  domains: [domain:data-engineering]
  specializations: [specialization:data-engineering-analytics]
  skillAreas: [skill-area:data-warehouse-modeling, skill-area:columnar-storage]
  roles: [role:analytics-engineer, role:data-engineer]
  workflows: [workflow:data-pipeline-deployment]
  topics: [topic:database-sharding]
---

# OBT Design Optimizer

## Overview

Designs and optimizes One Big Table (OBT) patterns. This skill balances denormalization benefits with maintainability for analytical use cases.

## Capabilities

- Column selection optimization
- Denormalization strategy
- Nested/repeated field design (BigQuery)
- Clustering key selection
- Partition strategy
- Update frequency optimization
- Query pattern analysis
- Storage vs. performance tradeoffs

## Input Schema

```json
{
  "sourceModels": ["object"],
  "queryPatterns": ["object"],
  "platform": "snowflake|bigquery|redshift",
  "constraints": {
    "maxColumns": "number",
    "refreshFrequency": "string"
  }
}
```

## Output Schema

```json
{
  "obtDesign": {
    "columns": ["object"],
    "clustering": ["string"],
    "partitioning": "object"
  },
  "buildStrategy": "object",
  "refreshConfig": "object",
  "estimatedQueryImprovement": "percentage"
}
```

## Target Processes

- OBT Creation
- BI Dashboard Development
- Query Optimization

## Usage Guidelines

1. Analyze source models and relationships
2. Document common query patterns
3. Define platform and constraints
4. Balance column count with query needs

## Best Practices

- Include only columns needed for known query patterns
- Use appropriate clustering for common filter columns
- Partition by date for time-series analysis
- Schedule refreshes based on source update frequency
- Monitor query performance and adjust design