sidruud/agentic-etl-to-databricks-bot

1# Copilot System Instructions - ETL -> Databricks Agent Demo2 3You are assisting in a VS Code repository that implements an agentic ETL-to-Databricks conversion system.4 5## Mission6Maintain and extend a policy-driven, learning-enabled, multi-agent system that converts DataStage DSX jobs into Databricks Lakehouse notebooks.7 8## Architecture Summary9- Entry point: `data_engineering_agent.py`10- Core orchestration: `src/core/agent_loop.py`11- Agents:12  - DataStageAgent (DSX XML analysis)13  - OdsDsxAgent (DSX combined analysis: XML or text export)14  - S2TAgent (S2T Excel analysis)15  - PlannerAgent (policy + memory driven planning)16  - NotebookBuilderAgent (template-based generation, LLM-aware)17  - DataQualityAgent18  - UnitTestAgent19  - CodeReviewAgent20  - MetricsAgent21  - ProjectManagerAgent22  - LeadAgent (orchestration)23- Memory:24  - Persistent file: `agent_memory.json`25  - Learning loop: issues -> enforced_rules -> fixes on next run26  - Skill usage is tracked per agent27 28## LLM Integration29- LLM mode is optional and controlled by env vars.30- When LLM is enabled, templates are layout only (no copying SQL or values).31- ODS combined migrations synthesize SQL blocks from DSX/XML select statements + exemplar layout.32- Exemplar registry is stored in `artifacts/reports/exemplar_registry.json` and updated after successful runs.33- The LLM receives policy context and a skill subset scoped per agent.34- Skill pack is loaded from `docs/skills/` and merged with `.github/skill/SKILLS.md`.35- Skill pack docs are concise summaries to reduce prompt bloat.36- Skill selection defaults to `hybrid` (rules + LLM); set `SKILLS_PACK_MODE=relevant` or `SKILLS_PACK_MODE=full`.37 38## Skill Registry39- Implemented in `src/core/skills_registry.py`40- Defines per-agent skill subsets41- Skills are synced into memory on each run42 43## Supported CLI Commands (must remain working)44- `help`45- `--list-templates`, `list-templates`46- `talk <message>` (deterministic mock)47- `auto "<message>"` (LLM router)48- `lead <path>`49- `analyze <dsx_path>`50- `plan <dsx_path>`51- `migrate <dsx_path>`52- `migrate <path> --yes`53- `migrate <path> --layers bronze|silver|gold|bronze,silver,gold`54- `migrate <path> --template <template_key>` (only when exactly one layer selected)55- `migrate <path> --templates bronze=<k>,silver=<k>,gold=<k>`56- `migrate-batch <path>`57- `migrate-batch <path> --yes`58- `analyze-ods <dsx_path>`59- `plan-ods <dsx_path> --template <key>`60- `migrate-ods <dsx_path> --template <key>`61- `s2t <s2t_excel_path> --layers silver [--template <key>]`62- `convert <path>` (Databricks `.py` -> `.ipynb`)63- `clear-cache` (remove cached plan/LLM context)64- `cleanup-logs --keep <N>` (delete old logs, keep last N runs)65- `--llm`, `--mock`, or `--agent-mode llm|mock` (runtime override)66- `memory`, `history`, `shutdown`67 68## Templates69- Default reference template:70  - `references/databricks/approved_lakehouse_template.py`71- Optional registry:72  - `references/databricks/templates/*.py`73- Template key = filename without extension74- Missing keys must fall back to default75 76## Policies (must be loaded and influence planning)77- `.github/skill/SKILLS.md` - capability context78- `.github/skill/INSTRUCTIONS.md` - hard enforcement rules79- `docs/reference_notebook.md` - design standards80- `docs/skills/` - Skills Knowledge Pack81 82## Inputs83- DSX XML and DSX text exports under `inputs/dsx/`84- S2T Excel under `inputs/s2t/`85 86## Outputs (do NOT delete)87Artifacts under `artifacts/`:88- `bronze/`, `silver/`, `gold/` notebooks (`.py` and `.ipynb`)89- `tests/` (DQ checks + pytest stubs)90- `reports/` (code review, PM, batch, LLM learning)91- `metrics/` (latest + timestamped)92 93## Notebook Conversion94- `AUTO_CONVERT_IPYNB = True`95- When enabled, migrate generates `.ipynb` alongside `.py`96 97## LLM Context Defaults98- Repo context defaults to `paths` for speed.99- Output size defaults to `OPENAI_MAX_OUTPUT_TOKENS=8000`.100- Plan + LLM context caching is stored under `artifacts/cache/`.101- Cache keys include exemplar version and hashes.102 103## Guardrails (non-negotiable)104- Never delete user files or artifacts105- Ask before overwriting unless `--yes` is used106- Keep backward compatibility107- Add new behavior behind flags108- Update README / RUNBOOK if CLI behavior changes109 110## Verification Expectations111- `analyze -> plan -> migrate` works for a single DSX112- Re-running migrate demonstrates learning (fixes_applied)113- `migrate-batch` processes multiple DSX files correctly114