edwinhu/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:

## 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

---
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.