npx skills add ...
npx skills add astronomer/agents --skill warehouse-init
npx skills add astronomer/agents --skill warehouse-init
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.
Generate a comprehensive, user-editable schema reference file for the data warehouse.
Scripts: ../analyzing-data/scripts/ — All CLI commands below are relative to the analyzing-data skill's directory. Before running any scripts/cli.py command, cd to ../analyzing-data/ relative to this file.
.astro/warehouse.md - a version-controllable, team-shareable referenceGet the list of databases to discover (e.g., databases: [HQ, ANALYTICS, RAW]).
Launch a subagent to find business context in code:
Launch one subagent per database using the Task tool:
Run all subagents in parallel (single message with multiple Task calls).
For key categorical columns (like OPERATOR, STATUS, TYPE, FEATURE), discover value families:
Group related values into families by common prefix/suffix (e.g., Export* for ExportCSV, ExportJSON, ExportParquet).
Combine warehouse metadata + codebase context:
Write the file to:
.astro/warehouse.md (default - project-specific, version-controllable)~/.astro/agents/warehouse.md (if --global flag){Inferred relationships based on column names like *_ID}
| Option | Effect |
|---|---|
/astronomer-data:warehouse-init | Generate .astro/warehouse.md |
/astronomer-data:warehouse-init --refresh | Regenerate, preserving user edits |
/astronomer-data:warehouse-init --database HQ | Only discover specific database |
/astronomer-data:warehouse-init --global | Write to ~/.astro/agents/ instead |
After generating warehouse.md, populate the concept cache:
Ask the user:
Would you like to add the Quick Reference table to your CLAUDE.md file?
This ensures the schema mappings are always in context for data queries, improving accuracy from ~25% to ~100% for complex queries.
Options:
- Yes, add to CLAUDE.md (Recommended) - Append Quick Reference section
- No, skip - Use warehouse.md and cache only
If user chooses Yes:
.claude/CLAUDE.md or CLAUDE.md exists.claude/CLAUDE.md with just the Quick ReferenceQuick Reference section to add:
If yes: Append the Quick Reference section to .claude/CLAUDE.md or CLAUDE.md.
Tell the user:
When --refresh is specified:
<!-- ... -->)<!-- REMOVED --> commentThe runtime cache has a 7-day TTL by default. After 7 days, cached entries expire and will be re-discovered on next use.
Run /astronomer-data:warehouse-init --refresh when:
Watch for these indicators:
If you suspect cache issues:
| Pattern | Source | What We Extract |
|---|---|---|
**/models/**/*.yml | dbt | table/column descriptions, tests |
**/dags/**/*.sql | gusty | YAML frontmatter (description, primary_key) |
AGENTS.md, CLAUDE.md | docs | data layer hierarchy, conventions |
**/docs/**/*.md | docs | business context |
For each database in configured_databases:
Task(
subagent_type="general-purpose",
prompt="""
Discover all metadata for database {DATABASE}.
Use the CLI to run SQL queries:
# Scripts are relative to ../analyzing-data/
uv run scripts/cli.py exec "df = run_sql('...')"
uv run scripts/cli.py exec "print(df)"
1. Query schemas:
SELECT SCHEMA_NAME FROM {DATABASE}.INFORMATION_SCHEMA.SCHEMATA
2. Query tables with row counts:
SELECT TABLE_SCHEMA, TABLE_NAME, ROW_COUNT, COMMENT
FROM {DATABASE}.INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_SCHEMA, TABLE_NAME
3. For important schemas (MODEL_*, METRICS_*, MART_*), query columns:
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COMMENT
FROM {DATABASE}.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'X'
Return a structured summary:
- Database name
- List of schemas with table counts
- For each table: name, row_count, key columns
- Flag any tables with >100M rows as "large"
"""
)uv run cli.py exec "df = run_sql('''
SELECT DISTINCT column_name, COUNT(*) as occurrences
FROM table
WHERE column_name IS NOT NULL
GROUP BY column_name
ORDER BY occurrences DESC
LIMIT 50
''')"
uv run cli.py exec "print(df)"# Warehouse Schema
> Generated by `/astronomer-data:warehouse-init` on {DATE}. Edit freely to add business context.
## Quick Reference
| Concept | Table | Key Column | Date Column |
|---------|-------|------------|-------------|
| customers | HQ.MODEL_ASTRO.ORGANIZATIONS | ORG_ID | CREATED_AT |
<!-- Add your concept mappings here -->
## Categorical Columns
When filtering on these columns, explore value families first (values often have variants):
| Table | Column | Value Families |
|-------|--------|----------------|
| {TABLE} | {COLUMN} | `{PREFIX}*` ({VALUE1}, {VALUE2}, ...) |
<!-- Populated by /astronomer-data:warehouse-init from actual warehouse data -->
## Data Layer Hierarchy
Query downstream first: `reporting` > `mart_*` > `metric_*` > `model_*` > `IN_*`
| Layer | Prefix | Purpose |
|-------|--------|---------|
| Reporting | `reporting.*` | Dashboard-optimized |
| Mart | `mart_*` | Combined analytics |
| Metric | `metric_*` | KPIs at various grains |
| Model | `model_*` | Cleansed sources of truth |
| Raw | `IN_*` | Source data - avoid |
## {DATABASE} Database
### {SCHEMA} Schema
#### {TABLE_NAME}
{DESCRIPTION from code if found}
| Column | Type | Description |
|--------|------|-------------|
| COL1 | VARCHAR | {from code or inferred} |
- **Rows:** {ROW_COUNT}
- **Key column:** {PRIMARY_KEY from code or inferred}
{IF ROW_COUNT > 100M: - **⚠️ WARNING:** Large table - always add date filters}
## Relationships