data-context

# Data Context Extractor

Extract tribal knowledge about a dataset or database and generate a reusable data context skill.

<EXTREMELY-IMPORTANT>
## The Iron Law of Data Context

**YOU MUST interview the user before generating ANY skill content. This is not negotiable.**

You MUST NOT:
- Generate a data context skill from schema alone
- Skip entity disambiguation
- Assume metric definitions without confirmation
- Output a skill without user review of each section

**If you're about to write a skill based on your assumptions, STOP. Interview first.**
</EXTREMELY-IMPORTANT>

## Two Modes

### 1. Bootstrap Mode
**Trigger:** No existing data context skill for this project/dataset.

Create a new data context skill from scratch by interviewing the user about their data.

### 2. Iteration Mode
**Trigger:** Existing data context skill exists, user wants to add a new domain or update.

Read existing skill, identify gaps, interview for the new domain, merge into existing skill.

## Integration with Existing Data Access Skills

Before interviewing, check for existing data access skills that already encode tribal knowledge:

```
1. READ existing skills: /wrds, /lseg-data, or any project-local data skills
2. IDENTIFY what they already cover (table names, filters, field mappings, gotchas)
3. DO NOT re-document what existing skills handle
4. FOCUS the interview on the project-specific layer:
   - Which specific tables/fields from WRDS or LSEG does THIS study use?
   - How are identifiers linked across sources? (permno ↔ gvkey, RIC ↔ ISIN ↔ cusip)
   - What sample filters define the study universe? (date range, exchange, firm type)
   - What derived variables or transformations are project-specific?
```

The generated data context skill should **reference** existing skills rather than duplicate them:

```markdown
## Data Sources

| Source | Skill | Tables/Fields Used |
|--------|-------|--------------------|
| WRDS | `/wrds` | comp.funda (at, lt, ceq), crsp.msf (ret, prc) |
| LSEG | `/lseg-data` | TR.F.TotRevenue, TR.GICSSector |
| Local | DuckDB | data/processed/merged_panel.parquet |

For connection details and critical filters, see the referenced skills.
```

## The Interview Process

```
1. DISCOVER data sources
   → What databases/files/APIs? Connection details?
   → For each: what dialect? (PostgreSQL, DuckDB, SQLite, Snowflake, etc.)
   → IMPORTANT: If user mentions WRDS or LSEG, read the corresponding skill first
     and ask only about project-specific usage, not general access patterns

2. MAP entities
   → What are the core entities? (users, transactions, products, etc.)
   → How do they relate? (foreign keys, join paths)
   → CRITICAL: Disambiguate entity names
     - "user" vs "account" vs "customer" — are these the same?
     - "order" vs "transaction" vs "purchase" — clarify overlaps
     - Document the canonical name and any aliases

3. DEFINE key metrics
   → What are the business-critical metrics?
   → For each metric:
     - Exact definition (SQL or formula)
     - Known edge cases
     - Common misinterpretations
     - Time grain (daily, monthly, etc.)

4. DOCUMENT data hygiene
   → Known data quality issues
   → Fields that lie (e.g., "created_at" that's actually "imported_at")
   → Nulls that mean something specific
   → Enums/codes that need translation
   → Date ranges with reliable data vs backfill periods

5. CAPTURE common gotchas
   → Joins that explode (many-to-many lurking as one-to-many)
   → Filters that are always needed (e.g., "WHERE is_deleted = false")
   → Time zones and their traps
   → Slowly changing dimensions
   → Tables that look useful but aren't (deprecated, partial, test data)

6. COLLECT common query patterns
   → Frequently needed aggregations
   → Standard date filters or cohort definitions
   → Boilerplate CTEs that everyone copies
```

## Interview Style

Ask questions in batches of 3-5. Don't overwhelm with everything at once.

**Round 1: Data Sources**
- What data sources does this project use? (WRDS, LSEG, local files, other databases?)
- For known sources (WRDS/LSEG): which specific tables and fields do you pull?
- For local data: what formats (parquet, CSV, SQLite, DuckDB)? Where stored?
- How do you link identifiers across sources? (permno, gvkey, cusip, ISIN, RIC)

**Round 2: Core Entities** (after Round 1 answers)
- What are the main entities in this data?
- Walk me through how [Entity A] relates to [Entity B]
- Are there any entity names that are ambiguous or have aliases?

**Round 3: Metrics & Definitions** (after Round 2 answers)
- What metrics does your team track most?
- For [Metric]: what's the exact calculation? Any edge cases?
- Are there metrics people commonly get wrong?

**Round 4: Data Quality & Gotchas** (after Round 3 answers)
- What are the known data quality issues?
- Any fields that are misleading or have surprising behavior?
- What's the most common mistake new analysts make with this data?

**Round 5: Common Patterns** (after Round 4 answers)
- What queries do you find yourself writing repeatedly?
- Any standard filters or cohort definitions everyone uses?
- What boilerplate would save the most time?

## Output: Generated Skill

After the interview, generate a skill with this structure:

```
project-name/
├── .claude/
│   └── skills/
│       └── data-context/
│           ├── SKILL.md          # Main skill file
│           └── references/
│               ├── entities.md   # Entity definitions and relationships
│               ├── metrics.md    # Metric definitions with SQL/formulas
│               └── gotchas.md    # Data quality issues and common pitfalls
```

### SKILL.md Template

```markdown
---
name: [project]-data-context
description: "Data context for [project]. Entity definitions, metric calculations, data quality notes, and common patterns for [data domain]."
---

# [Project] Data Context

## Data Sources

| Source | Skill/Dialect | Tables/Fields Used |
|--------|---------------|--------------------|
| [WRDS] | `/wrds` | [specific tables and fields for this project] |
| [LSEG] | `/lseg-data` | [specific fields for this project] |
| [Local] | [DuckDB/CSV/Parquet] | [file paths or database] |

For connection details and critical filters, see the referenced skill. This context covers only project-specific usage.

## Entity Map

[Entity relationship summary — which entities exist, how they connect]

See `references/entities.md` for full definitions.

## Key Metrics

[Top 3-5 metrics with brief definitions]

See `references/metrics.md` for exact calculations and edge cases.

## Critical Gotchas

[Top 3-5 gotchas that catch analysts]

See `references/gotchas.md` for full list.

## Common Patterns

[Frequently used query snippets or data access patterns]
```

### Generating the References

For each reference file, include:
- **entities.md**: Entity name, aliases, primary key, important fields, relationships, common joins
- **metrics.md**: Metric name, definition (SQL/formula), edge cases, common misinterpretations, time grain
- **gotchas.md**: Issue description, why it happens, how to detect, how to handle

## Exit Gate

Before writing skill files, execute this gate:

1. **IDENTIFY**: All 5 interview rounds completed
2. **RUN**: Read back each generated section to the user
3. **READ**: Verify entities, metrics, gotchas sections all have substantive content (not placeholders)
4. **VERIFY**: User approved each section via explicit confirmation
5. **CLAIM**: Only generate skill files after ALL checks pass

**Skipping this gate produces a skill based on your assumptions, not the user's knowledge. That skill will mislead every future analysis.**

## Verification

Before finalizing the skill:

1. **Read back** each section to the user for confirmation
2. **Test** at least one metric definition against actual data if possible
3. **Check** entity relationships match what the user described
4. **Confirm** the gotchas list captures what the user considers most important

## Iteration Mode Details

When adding to an existing data context skill:

1. Read the existing skill files
2. Identify what's already documented
3. Interview for the NEW domain only (don't re-interview existing content)
4. Merge new content into existing files, maintaining consistent formatting
5. Flag any conflicts with existing definitions (e.g., metric redefinition)

## Where to Place the Output

- **Project-specific**: `.claude/skills/data-context/` in the project root
- The generated skill becomes available to all future Claude sessions in that project
- Subagents spawned by `ds-delegate` will have access to it automatically

## Data Context Facts

- Schema and column names carry structure, not semantics: `revenue` may be gross, net, or recognized; a `user_id` foreign key does not say whether users can hold multiple active accounts. A metric definition or relationship cardinality inferred from names is an unverified claim presented as fact — every downstream analysis that loads the generated skill inherits the error. The interview takes 10 minutes; weeks of wrong analysis is what it prevents.
- Gotchas are the most valuable section of the generated skill and the easiest to defer. "Fill in later" means never — capture them during the interview while context is fresh, or the skill ships as a hazard that misleads every future analysis.
- There is no "standard SQL" in practice. When the user says "standard SQL", get the exact dialect — definitions in metrics.md must be runnable as written.
- WRDS connection mechanics are covered by `/wrds`, and LSEG field prefixes by `/lseg-data`. Re-documenting them here duplicates and drifts; document only the project-specific tables, fields, and filters THIS project uses.