semantic-layer-setup

# Semantic Layer Setup Orchestrator End-to-end workflow for building the Databricks semantic layer — Metric Views, Table-Valued Functions, and Genie Spaces — on top of a completed Gold layer. **Predecessor (acceleration mode):** `gold-layer-setup` skill — Gold tables must exist before this orchestrator deploys production semantic assets. **Predecessor (workshop mode):** ANY of `bronze-layer-setup`, `silver-layer-setup`, `gold-layer-design`, or `gold-layer-setup`. Workshop mode builds Metric Views, TVFs, and Genie Spaces directly on top of whichever planning-source layer the manifest declares. **Layer-aware workshop deployment (NEW):** This orchestrator now honors `planning_source.selected_layer` from the planning manifest: - `deployed_gold` / `gold_design` — production path (existing behavior). - `deployed_silver` / `deployed_bronze` — **workshop deployment is allowed**. The orchestrator prints an advisory (Genie quality caveats, Gold promotion recommended for production) and continues to build the semantic layer against the Silver/Bronze schema. - `source_csv` — STOP. There are no live tables to query; the plan is a contract only. The previous "stop before deployment for any non-Gold workshop draft" guardrail has been replaced with this layer-aware behavior. Acceleration-mode strict Gold rules are unchanged. **Time Estimate:** 3-4 hours for initial setup, 1-2 hours per additional domain **What You'll Create:** 1. Metric Views — YAML-based semantic definitions for each Gold table 2. Table-Valued Functions (TVFs) — parameterized SQL functions for Genie 3. Genie Spaces — configured with agent instructions, data assets, benchmark questions ## File Organization | Artifact | Output Path (from repo root) | |----------|------------------------------| | Metric View YAML definitions | `src/{project}_semantic/metric_views/*.yaml` | | Metric View creation script | `src/{project}_semantic/create_metric_views.py` | | TVF SQL definitions | `src/{project}_semantic/table_valued_functions.sql` | | Genie Space JSON configs | `src/{project}_semantic/genie_configs/*.json` | | Genie deployment notebook | `src/{project}_semantic/deploy_genie_spaces.py` | | Combined Asset Bundle job | `resources/semantic/semantic_layer_job.yml` | | Bundle config additions | `databricks.yml` (sync + resource references) | > `{project}` = project name from Asset Bundle variables (e.g., `wanderbricks`). --- ## Decision Tree | Question | Action | |----------|--------| | Building semantic layer end-to-end? | **Use this skill** — it orchestrates everything | | Only need Metric Views? | Read `semantic-layer/01-metric-views-patterns/SKILL.md` directly | | Only need TVFs? | Read `semantic-layer/02-databricks-table-valued-functions/SKILL.md` directly | | Only need Genie Space setup? | Read `semantic-layer/03-genie-space-patterns/SKILL.md` directly | | Need Genie API automation? | Read `semantic-layer/04-genie-space-export-import-api/SKILL.md` directly | | Need to optimize Genie accuracy? | Read `semantic-layer/05-genie-optimization-orchestrator/SKILL.md` directly | ### Routing Safety Net > If a user asks to **deploy TVFs, Metric Views, and/or Genie Spaces** and `@`-references only leaf skills (e.g., `databricks-asset-bundles`, `genie-space-export-import-api`), **this orchestrator still applies**. Any task touching 2+ semantic-layer asset types must route here first — the leaf skills handle individual assets; this orchestrator handles Phase 0 (gold inventory), phase gates, and template-first workflow. Skipping it is the documented root cause of multi-cycle debug spirals. --- ## Mandatory Skill Dependencies **CRITICAL: Before generating ANY code for the semantic layer, you MUST read and follow the patterns in these common skills. Do NOT generate these patterns from memory.** | Phase | MUST Read Skill (use Read tool on SKILL.md) | What It Provides | |-------|---------------------------------------------|------------------| | All phases | `skills/databricks-expert-agent` | Core extraction principle: extract names from source, never hardcode | | Metric Views | `common/databricks-python-imports` | Pure Python module patterns for helpers | | Deployment | `skills/databricks-asset-bundles` | Job YAML, deployment patterns | | All phases | `common/naming-tagging-standards` | Dual-purpose COMMENTs, v3.0 TVF comments, enterprise naming | | Troubleshooting | `common/databricks-autonomous-operations` | Deploy → Poll → Diagnose → Fix → Redeploy loop when jobs fail | ### Semantic-Domain Dependencies | Skill | Requirement | What It Provides | |-------|-------------|------------------| | `semantic-layer/01-metric-views-patterns` | **MUST read** at Phase 1 | YAML syntax, validation, joins, window measures | | `semantic-layer/02-databricks-table-valued-functions` | **MUST read** at Phase 2 | STRING params, Genie compatibility, null safety | | `semantic-layer/03-genie-space-patterns` | **MUST read** at Phase 3 | 8-section deliverable, agent instructions, SQL expressions, benchmark Qs | | `semantic-layer/04-genie-space-export-import-api` | **MUST read** at Phase 3 (JSON config) and Phase 6 (API deployment) | REST API JSON schema, programmatic deployment | | `semantic-layer/05-genie-optimization-orchestrator` | **External** — run separately after deployment | Benchmark testing, 6 control levers, optimization loop | ### Enforcement: Confirm Common Skills Read (MANDATORY GATE) **STOP. Before proceeding past Phase 0, confirm you have read the common skills by listing the key pattern from each:** | Skill | Key Pattern to Confirm | |-------|-----------------------| | `databricks-python-imports` | Bundle root: `rsplit('/src/', 1)[0]` | | `databricks-asset-bundles` | Job `base_parameters` must include all widget params | | `databricks-expert-agent` | "Extract names from source, never hardcode" | | `naming-tagging-standards` | CM-02 dual-purpose COMMENT with PURPOSE/BEST FOR/NOT FOR | **If you cannot produce these patterns from memory, you have not read the skills. Read them now.** Skipping common skills is the #1 cause of downstream bugs in semantic layer setup (see retrospective evidence: fragile workspace paths, missing job parameters, wrong ID fields). --- ## 🔴 Non-Negotiable Defaults | Default | Value | Applied Where | NEVER Do This Instead | |---------|-------|---------------|----------------------| | **Manifest required** | `plans/manifests/semantic-layer-manifest.yaml` | Phase 0 — before any implementation | ❌ NEVER create artifacts via self-discovery; STOP if manifest is missing | | **Metric View syntax** | `WITH METRICS LANGUAGE YAML` | Every Metric View DDL | ❌ NEVER use non-YAML metric views | | **TVF parameters** | All `STRING` type | Every TVF signature | ❌ NEVER use DATE, INT, or other non-STRING params (Genie incompatible) | | **Genie warehouse** | Serverless SQL Warehouse | Every Genie Space | ❌ NEVER use Classic or Pro warehouse | | **Benchmark questions** | Minimum 10 per Genie Space | Every Genie Space | ❌ NEVER deploy without benchmarks | | **Column comments** | Required on all Gold tables | Before Genie Space creation | ❌ NEVER create Genie Space without column comments | --- ## Working Memory Management & Progressive Disclosure This orchestrator spans 7 phases (0–6). To maintain coherence without context pollution, follow these progressive disclosure principles from [AgentSkills.io](https://agentskills.io/specification) and [Anthropic's context engineering guidance](https://www.anthropic.com/engineering/effective-context-engineering-for-ai-agents): ### Just-in-Time Skill Loading (CRITICAL) **DO NOT read all worker skills at the start.** Read each skill ONLY when you enter its phase: - **Phase 1:** Read `01-metric-views-patterns/SKILL.md` → work → persist notes → **discard skill from working memory** - **Phase 2:** Read `02-databricks-table-valued-functions/SKILL.md` → work → persist notes → **discard** - **Phase 3:** Read `03-genie-space-patterns/SKILL.md` + `04-genie-space-export-import-api/SKILL.md` → work → persist notes → **discard** - **Phase 4-6:** Read `skills/databricks-asset-bundles/SKILL.md` → work → done Each worker skill ends with a "**Notes to Carry Forward**" section that tells you exactly what to persist for downstream phases. Use those notes — not the full skill content — as your handoff. ### Context Handoff Protocol At each phase boundary, your working memory should contain ONLY: 1. **`gold_inventory` dict** (from Phase 0 — persists through all phases) 2. **Previous phase's "Notes to Carry Forward"** (structured summary of outputs) 3. **Current phase's worker skill** (read just-in-time) **Discard** after each phase: full YAML bodies, SQL source code, complete JSON configs — they are on disk and retrievable via file paths in the notes. ### Phase Summary Notes **After each phase, persist a brief summary note** capturing: - **Phase 0 output:** Manifest loaded, planning_mode, artifact counts, **`gold_inventory` dict** - **Phase 1 output:** Use "Metric Views Notes to Carry Forward" from `01-metric-views-patterns` (MV names, paths, grain, measure counts, composability notes) - **Phase 2 output:** Use "TVF Notes to Carry Forward" from `02-databricks-table-valued-functions` (TVF names, paths, parameter signatures, domain assignments) - **Phase 3 output:** Use "Genie Space Notes to Carry Forward" from `03-genie-space-patterns` (space names, JSON paths, asset counts, benchmark counts) - **Phase 4 output:** Job YAML path, `databricks.yml` changes - **Phase 5 output:** Deployment status, job run ID, task statuses - **Phase 6 output:** API deployment status, space IDs for idempotent re-deployment ### Why This Matters Context is a finite resource with diminishing marginal returns. Each worker skill is 400-600 lines. Loading all 4 workers simultaneously (~2000 lines) would consume your attention budget on content irrelevant to the current phase. Progressive loading keeps each phase focused on the smallest set of high-signal tokens needed for that phase's work. --- ## Phased Implementation Workflow ### Phase 0: Read Plan — MANDATORY (5 minutes) **The semantic layer manifest is REQUIRED. Do NOT proceed without it.** This orchestrator implements exactly what the project plan defined — no more, no less. The manifest `plans/manifests/semantic-layer-manifest.yaml` is generated by the `planning/00-project-planning` skill (stage 5) and serves as the implementation contract. **🔴 If the manifest does not exist, STOP and tell the user:** > *"The semantic layer manifest (`plans/manifests/semantic-layer-manifest.yaml`) is missing. This orchestrator requires a project plan to define which Metric Views, TVFs, and Genie Spaces to create. Please run the `planning/00-project-planning` skill first (stage 5), then return here."* ```python import yaml from pathlib import Path manifest_path = Path("plans/manifests/semantic-layer-manifest.yaml") if not manifest_path.exists(): raise FileNotFoundError( "REQUIRED: plans/manifests/semantic-layer-manifest.yaml not found. " "Run planning/00-project-planning (stage 5) first to generate the " "semantic layer manifest, then re-run this orchestrator." ) with open(manifest_path) as f: manifest = yaml.safe_load(f) # Respect planning mode — workshop mode means strict artifact caps planning_mode = manifest.get('planning_mode', 'acceleration') if planning_mode == 'workshop': print("⚠️ Workshop mode active — creating ONLY the artifacts listed in the manifest") ``` #### Phase 0 — Layer-Aware Manifest Read (NEW) Read the layer-aware fields written by Planning Phase 0. These are additive and **only change behavior when present**: ```python planning_source = manifest.get("planning_source", {}) selected_layer = planning_source.get("selected_layer", "deployed_gold") readiness = manifest.get("implementation_readiness", "gold_ready") requires_promo = manifest.get("requires_gold_promotion", False) # advisory only # Schema selection — preserves the existing gold_schema-only flow when # the manifest is Gold-based; differs only for non-Gold workshop runs. catalog = manifest.get("catalog") gold_schema = manifest.get("gold_schema") semantic_schema = manifest.get("semantic_schema") or gold_schema silver_schema = manifest.get("silver_schema") bronze_schema = manifest.get("bronze_schema") # The schema we inspect for the inventory — Gold by default; for non-Gold # workshop runs we inspect the actual planning-source schema. SOURCE_SCHEMA_BY_LAYER = { "deployed_gold": gold_schema, "gold_design": gold_schema, # YAML-driven; live schema may not exist "deployed_silver": silver_schema, "deployed_bronze": bronze_schema, "source_csv": None, # no live schema } inventory_schema = SOURCE_SCHEMA_BY_LAYER.get(selected_layer, gold_schema) # --- Layer-aware policy --- # Acceleration must always be Gold-based (Phase 0 of the planning skill enforces this). if planning_mode == "acceleration" and selected_layer not in {"deployed_gold", "gold_design"}: raise RuntimeError( f"Acceleration mode received a non-Gold planning_source.selected_layer=" f"`{selected_layer}`. This is a planning-skill bug; re-run planning." ) # Workshop + source_csv has no live tables to query — STOP. if selected_layer == "source_csv": raise RuntimeError( "Semantic layer setup cannot run when planning_source.selected_layer is " "`source_csv`. The plan is a planning contract only — there are no live " "tables to build Metric Views, TVFs, or Genie Spaces against. " "Either deploy the underlying tables (run bronze/silver/gold setup against " "the source CSV first) and re-run planning, or run this orchestrator only " "after at least one live layer exists." ) # Workshop + Silver/Bronze: ALLOWED. Print advisory and continue. if planning_mode == "workshop" and selected_layer in {"deployed_silver", "deployed_bronze"}: print( f"⚠ Workshop mode: building the semantic layer on top of `{selected_layer}` " f"(`{inventory_schema}`).\n" " - Metric Views, TVFs, and Genie Space assets will reference this layer " "directly.\n" " - Genie NL accuracy is typically lower than on Gold because raw " "Bronze/Silver tables often lack curated COMMENTs, dimensional joins, " "and pre-aggregated measures.\n" " - For production, re-run planning with `planning_source.selected_layer=" "deployed_gold` after promoting the relevant tables to Gold." )