Update to astronomer-data

{
  "name": "warehouse-init",
  "description": "Initialize warehouse schema discovery. Generates .astro/warehouse.md with all table metadata for instant lookups. Run once per project, refresh when schema changes. Use when user says \"/astronomer-data:warehouse-init\" or asks to set up data discovery.",
  "included_files": [
    {
      "relative_path": "scripts/cache.py",
      "size_in_bytes": 9970
    },
    {
      "relative_path": "scripts/cli.py",
      "size_in_bytes": 14449
    },
    {
      "relative_path": "scripts/config.py",
      "size_in_bytes": 1961
    },
    {
      "relative_path": "scripts/connectors.py",
      "size_in_bytes": 30764
    },
    {
      "relative_path": "scripts/kernel.py",
      "size_in_bytes": 15731
    },
    {
      "relative_path": "scripts/templates.py",
      "size_in_bytes": 4269
    },
    {
      "relative_path": "scripts/warehouse.py",
      "size_in_bytes": 1575
    }
  ],
  "skill_md_contents": "---\nname: warehouse-init\ndescription: Initialize warehouse schema discovery. Generates .astro/warehouse.md with all table metadata for instant lookups. Run once per project, refresh when schema changes. Use when user says \"/astronomer-data:warehouse-init\" or asks to set up data discovery.\n---\n\n# Initialize Warehouse Schema\n\nGenerate a comprehensive, user-editable schema reference file for the data warehouse.\n\n**All CLI commands below are relative to this skill's directory.** Before running any `scripts/cli.py` command, `cd` to the directory containing this file.\n\n## What This Does\n\n1. Discovers all databases, schemas, tables, and columns from the warehouse\n2. **Enriches with codebase context** (dbt models, gusty SQL, schema docs)\n3. Records row counts and identifies large tables\n4. Generates `.astro/warehouse.md` - a version-controllable, team-shareable reference\n5. Enables instant concept→table lookups without warehouse queries\n\n## Process\n\n### Step 1: Read Warehouse Configuration\n\n```bash\ncat ~/.astro/agents/warehouse.yml\n```\n\nGet the list of databases to discover (e.g., `databases: [HQ, ANALYTICS, RAW]`).\n\n### Step 2: Search Codebase for Context (Parallel)\n\n**Launch a subagent to find business context in code:**\n\n```\nTask(\n    subagent_type=\"Explore\",\n    prompt=\"\"\"\n    Search for data model documentation in the codebase:\n\n    1. dbt models: **/models/**/*.yml, **/schema.yml\n       - Extract table descriptions, column descriptions\n       - Note primary keys and tests\n\n    2. Gusty/declarative SQL: **/dags/**/*.sql with YAML frontmatter\n       - Parse frontmatter for: description, primary_key, tests\n       - Note schema mappings\n\n    3. AGENTS.md or CLAUDE.md files with data layer documentation\n\n    Return a mapping of:\n      table_name -> {description, primary_key, important_columns, layer}\n    \"\"\"\n)\n```\n\n### Step 3: Parallel Warehouse Discovery\n\n**Launch one subagent per database** using the Task tool:\n\n```\nFor each database in configured_databases:\n    Task(\n        subagent_type=\"general-purpose\",\n        prompt=\"\"\"\n        Discover all metadata for database {DATABASE}.\n\n        Use the CLI to run SQL queries:\n        uv run scripts/cli.py exec \"df = run_sql('...')\"\n        uv run scripts/cli.py exec \"print(df)\"\n\n        1. Query schemas:\n           SELECT SCHEMA_NAME FROM {DATABASE}.INFORMATION_SCHEMA.SCHEMATA\n\n        2. Query tables with row counts:\n           SELECT TABLE_SCHEMA, TABLE_NAME, ROW_COUNT, COMMENT\n           FROM {DATABASE}.INFORMATION_SCHEMA.TABLES\n           ORDER BY TABLE_SCHEMA, TABLE_NAME\n\n        3. For important schemas (MODEL_*, METRICS_*, MART_*), query columns:\n           SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COMMENT\n           FROM {DATABASE}.INFORMATION_SCHEMA.COLUMNS\n           WHERE TABLE_SCHEMA = 'X'\n\n        Return a structured summary:\n        - Database name\n        - List of schemas with table counts\n        - For each table: name, row_count, key columns\n        - Flag any tables with >100M rows as \"large\"\n        \"\"\"\n    )\n```\n\n**Run all subagents in parallel** (single message with multiple Task calls).\n\n### Step 4: Discover Categorical Value Families\n\nFor key categorical columns (like OPERATOR, STATUS, TYPE, FEATURE), discover value families:\n\n```bash\nuv run scripts/cli.py exec \"df = run_sql('''\nSELECT DISTINCT column_name, COUNT(*) as occurrences\nFROM table\nWHERE column_name IS NOT NULL\nGROUP BY column_name\nORDER BY occurrences DESC\nLIMIT 50\n''')\"\nuv run scripts/cli.py exec \"print(df)\"\n```\n\nGroup related values into families by common prefix/suffix (e.g., `Export*` for ExportCSV, ExportJSON, ExportParquet).\n\n### Step 5: Merge Results\n\nCombine warehouse metadata + codebase context:\n\n1. **Quick Reference table** - concept → table mappings (pre-populated from code if found)\n2. **Categorical Columns** - value families for key filter columns\n3. **Database sections** - one per database\n4. **Schema subsections** - tables grouped by schema\n5. **Table details** - columns, row counts, **descriptions from code**, warnings\n\n### Step 6: Generate warehouse.md\n\nWrite the file to:\n- `.astro/warehouse.md` (default - project-specific, version-controllable)\n- `~/.astro/agents/warehouse.md` (if `--global` flag)\n\n## Output Format\n\n```markdown\n# Warehouse Schema\n\n> Generated by `/astronomer-data:warehouse-init` on {DATE}. Edit freely to add business context.\n\n## Quick Reference\n\n| Concept | Table | Key Column | Date Column |\n|---------|-------|------------|-------------|\n| customers | HQ.MODEL_ASTRO.ORGANIZATIONS | ORG_ID | CREATED_AT |\n<!-- Add your concept mappings here -->\n\n## Categorical Columns\n\nWhen filtering on these columns, explore value families first (values often have variants):\n\n| Table | Column | Value Families |\n|-------|--------|----------------|\n| {TABLE} | {COLUMN} | `{PREFIX}*` ({VALUE1}, {VALUE2}, ...) |\n<!-- Populated by /astronomer-data:warehouse-init from actual warehouse data -->\n\n## Data Layer Hierarchy\n\nQuery downstream first: `reporting` > `mart_*` > `metric_*` > `model_*` > `IN_*`\n\n| Layer | Prefix | Purpose |\n|-------|--------|---------|\n| Reporting | `reporting.*` | Dashboard-optimized |\n| Mart | `mart_*` | Combined analytics |\n| Metric | `metric_*` | KPIs at various grains |\n| Model | `model_*` | Cleansed sources of truth |\n| Raw | `IN_*` | Source data - avoid |\n\n## {DATABASE} Database\n\n### {SCHEMA} Schema\n\n#### {TABLE_NAME}\n{DESCRIPTION from code if found}\n\n| Column | Type | Description |\n|--------|------|-------------|\n| COL1 | VARCHAR | {from code or inferred} |\n\n- **Rows:** {ROW_COUNT}\n- **Key column:** {PRIMARY_KEY from code or inferred}\n{IF ROW_COUNT > 100M: - **⚠️ WARNING:** Large table - always add date filters}\n\n## Relationships\n\n```\n{Inferred relationships based on column names like *_ID}\n```\n```\n\n## Command Options\n\n| Option | Effect |\n|--------|--------|\n| `/astronomer-data:warehouse-init` | Generate .astro/warehouse.md |\n| `/astronomer-data:warehouse-init --refresh` | Regenerate, preserving user edits |\n| `/astronomer-data:warehouse-init --database HQ` | Only discover specific database |\n| `/astronomer-data:warehouse-init --global` | Write to ~/.astro/agents/ instead |\n\n### Step 7: Pre-populate Cache\n\nAfter generating warehouse.md, populate the concept cache:\n\n```bash\nuv run scripts/cli.py concept import -p .astro/warehouse.md\nuv run scripts/cli.py concept learn customers HQ.MART_CUST.CURRENT_ASTRO_CUSTS -k ACCT_ID\n```\n\n### Step 8: Offer CLAUDE.md Integration (Ask User)\n\n**Ask the user:**\n\n> Would you like to add the Quick Reference table to your CLAUDE.md file?\n>\n> This ensures the schema mappings are always in context for data queries, improving accuracy from ~25% to ~100% for complex queries.\n>\n> Options:\n> 1. **Yes, add to CLAUDE.md** (Recommended) - Append Quick Reference section\n> 2. **No, skip** - Use warehouse.md and cache only\n\n**If user chooses Yes:**\n\n1. Check if `.claude/CLAUDE.md` or `CLAUDE.md` exists\n2. If exists, append the Quick Reference section (avoid duplicates)\n3. If not exists, create `.claude/CLAUDE.md` with just the Quick Reference\n\n**Quick Reference section to add:**\n\n```markdown\n## Data Warehouse Quick Reference\n\nWhen querying the warehouse, use these table mappings:\n\n| Concept | Table | Key Column | Date Column |\n|---------|-------|------------|-------------|\n{rows from warehouse.md Quick Reference}\n\n**Large tables (always filter by date):** {list tables with >100M rows}\n\n> Auto-generated by `/astronomer-data:warehouse-init`. Run `/astronomer-data:warehouse-init --refresh` to update.\n```\n**If yes:** Append the Quick Reference section to `.claude/CLAUDE.md` or `CLAUDE.md`.\n\n## After Generation\n\nTell the user:\n\n```\nGenerated .astro/warehouse.md\n\nSummary:\n  - {N} databases, {N} schemas, {N} tables\n  - {N} tables enriched with code descriptions\n  - {N} concepts cached for instant lookup\n\nNext steps:\n  1. Edit .astro/warehouse.md to add business context\n  2. Commit to version control\n  3. Run /astronomer-data:warehouse-init --refresh when schema changes\n```\n\n## Refresh Behavior\n\nWhen `--refresh` is specified:\n\n1. Read existing warehouse.md\n2. Preserve all HTML comments (`<!-- ... -->`)\n3. Preserve Quick Reference table entries (user-added)\n4. Preserve user-added descriptions\n5. Update row counts and add new tables\n6. Mark removed tables with `<!-- REMOVED -->` comment\n\n## Cache Staleness & Schema Drift\n\nThe runtime cache has a **7-day TTL** by default. After 7 days, cached entries expire and will be re-discovered on next use.\n\n### When to Refresh\n\nRun `/astronomer-data:warehouse-init --refresh` when:\n- **Schema changes**: Tables added, renamed, or removed\n- **Column changes**: New columns added or types changed\n- **After deployments**: If your data pipeline deploys schema migrations\n- **Weekly**: As a good practice, even if no known changes\n\n### Signs of Stale Cache\n\nWatch for these indicators:\n- Queries fail with \"table not found\" errors\n- Results seem wrong or outdated\n- New tables aren't being discovered\n\n### Manual Cache Reset\n\nIf you suspect cache issues:\n\n```bash\nuv run scripts/cli.py cache status\nuv run scripts/cli.py cache clear --stale-only\nuv run scripts/cli.py cache clear\n```\n\n## Codebase Patterns Recognized\n\n| Pattern | Source | What We Extract |\n|---------|--------|-----------------|\n| `**/models/**/*.yml` | dbt | table/column descriptions, tests |\n| `**/dags/**/*.sql` | gusty | YAML frontmatter (description, primary_key) |\n| `AGENTS.md`, `CLAUDE.md` | docs | data layer hierarchy, conventions |\n| `**/docs/**/*.md` | docs | business context |\n\n## Example Session\n\n```\nUser: /astronomer-data:warehouse-init\n\nAgent:\n→ Reading warehouse configuration...\n→ Found 1 warehouse with databases: HQ, PRODUCT\n\n→ Searching codebase for data documentation...\n  Found: AGENTS.md with data layer hierarchy\n  Found: 45 SQL files with YAML frontmatter in dags/declarative/\n\n→ Launching parallel warehouse discovery...\n  [Database: HQ] Discovering schemas...\n  [Database: PRODUCT] Discovering schemas...\n\n→ HQ: Found 29 schemas, 401 tables\n→ PRODUCT: Found 1 schema, 0 tables\n\n→ Merging warehouse metadata with code context...\n  Enriched 45 tables with descriptions from code\n\n→ Generated .astro/warehouse.md\n\nSummary:\n  - 2 databases\n  - 30 schemas\n  - 401 tables\n  - 45 tables enriched with code descriptions\n  - 8 large tables flagged (>100M rows)\n\nNext steps:\n  1. Review .astro/warehouse.md\n  2. Add concept mappings to Quick Reference\n  3. Commit to version control\n  4. Run /astronomer-data:warehouse-init --refresh when schema changes\n```\n"
}