genie-space-patterns

name	genie-space-patterns
description	Patterns for setting up Databricks Genie Spaces with comprehensive agent instructions, data assets, SQL expressions, and benchmark questions. Use when creating Genie Spaces, configuring agent behavior, selecting data assets, defining SQL expressions (measures, filters, dimensions), or validating benchmark questions. Includes mandatory 8-section deliverable structure, General Instructions (≤20 lines), data asset organization (Metric Views → TVFs → Tables), SQL expressions (sql_snippets) for structured KPI/filter/dimension definitions, benchmark questions with exact SQL, Serverless warehouse mandate, table/column comment requirements for Genie SQL quality, pre-creation table inspection, Conversation API programmatic validation, follow-up vs new conversation patterns, deployment checklists, post-deployment configuration audit for drift detection, cross-consumer design considerations (Genie + dashboards), and benchmark regression testing patterns.
clients	["ide_cli","genie_code"]
bundle_resource	genie
deploy_verb	bundle_deploy
deploy_note	Design/config skill; the Genie Space JSON is deployed via 04-genie-space-export-import-api using the RULE_8 tier model. Serverless SQL Warehouse is mandatory; space title + table identifiers carry the per-user prefix.
coverage	full
metadata	{"author":"prashanth subrahmanyam","version":"2.6","domain":"semantic-layer","role":"worker","pipeline_stage":6,"pipeline_stage_name":"semantic-layer","called_by":["semantic-layer-setup"],"standalone":true,"last_verified":"2026-04-16","volatility":"medium","upstream_sources":[{"name":"databricks-agent-skills","repo":"databricks/databricks-agent-skills","paths":"[Truncated]","relationship":"extended","last_synced":"2026-08-30","sync_commit":"ca92a6c"}]}

Genie Space Patterns

Overview

This skill provides patterns for setting up production-ready Databricks Genie Spaces with natural language analytics capabilities. The quality of Genie responses directly correlates with the depth of business context provided in agent instructions.

Core Principle: Business context drives AI quality. Comprehensive agent instructions, properly selected data assets, and validated benchmark questions ensure reliable Genie performance.

---

When to Use This Skill

Use this skill when:

• Creating new Genie Spaces for natural language analytics
• Configuring agent behavior and instructions
• Selecting and organizing data assets (Metric Views, TVFs, Tables)
• Writing benchmark questions for validation
• Troubleshooting Genie query routing issues
• Optimizing Genie Space performance

🔀 Hand Off to genie-space-export-import-api Skill When:

User Says / Task Involves	Load Instead
"deploy Genie Space via API"	genie-space-export-import-api
"export Genie Space", "download Genie Space config"	genie-space-export-import-api
"import Genie Space", "restore Genie Space"	genie-space-export-import-api
"CI/CD for Genie Spaces"	genie-space-export-import-api
"migrate Genie Space to another workspace"	genie-space-export-import-api
"back up Genie Space configuration"	genie-space-export-import-api
"programmatically create Genie Space from JSON"	genie-space-export-import-api
"serialized_space", "REST API", "/api/2.0/genie/spaces"	genie-space-export-import-api

This skill covers what goes into a Genie Space (instructions, assets, benchmarks). The export/import API skill covers how to deploy it programmatically.

---

Upstream: Genie API Updates

The upstream databricks-genie skill provides these MCP tools:

Tool	Purpose
list_genie	List all Genie Spaces accessible to you
create_or_update_genie	Create or update a Genie Space
get_genie	Get Genie Space details
delete_genie	Delete a Genie Space
find_genie_by_name	Look up a Genie Space by name (when you don't have the space_id)
ask_genie	Ask a question to a Genie Space, get SQL + results
ask_genie_followup	Ask follow-up question in existing conversation

IMPORTANT: There is NO system table for Genie spaces (e.g., system.ai.genie_spaces does NOT exist). To find a Genie space by name, use the find_genie_by_name tool.

---

Critical Rules

1. General Instructions Must Be ≤20 Lines

⚠️ CRITICAL: Genie processes General Instructions effectively only when ≤20 lines. Longer instructions get truncated or ignored.

✅ DO: Keep General Instructions concise and focused on essential routing rules.

❌ DON'T: Exceed 20 lines in General Instructions section.

2. Benchmark Questions Must Have Working SQL

Every benchmark question MUST include copy-paste-ready SQL that actually runs.

✅ DO: Include tested SQL with every benchmark question.

❌ DON'T: Provide questions without SQL or untested SQL.

⚠️ Temporal Expression Warning: Avoid CURRENT_DATE(), CURRENT_TIMESTAMP(), or DATE_TRUNC('month', CURRENT_DATE) in benchmark SQL. These produce different results each day, making automated regression testing unreliable.

❌ Fragile (non-deterministic):

WHERE transaction_date >= DATE_TRUNC('month', CURRENT_DATE)  -- Different results every day

✅ Stable for regression testing:

WHERE transaction_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31'  -- Pinned date range

Guidance: Use CURRENT_DATE - 30 for initial interactive testing, but pin to fixed date ranges (DATE '...') for CI/CD regression suites and benchmark validation scripts.

3. MEASURE() Uses Column Names, NOT Display Names

The MEASURE() function requires actual column name, NOT display_name.

❌ WRONG:

MEASURE(`Total Revenue`)  -- ❌ FAILS: "Total Revenue" is display_name

✅ CORRECT:

MEASURE(total_revenue)  -- ✅ Uses actual column name from YAML

⚠️ Dimension references in metric view queries must use bare names — NOT source/join table prefixes:

❌ WRONG:

SELECT dim_store.state_name, MEASURE(total_revenue)
FROM ${catalog}.${gold_schema}.revenue_metrics
WHERE dim_store.state_name = 'California'  -- ❌ UNRESOLVED_COLUMN
GROUP BY dim_store.state_name

✅ CORRECT:

SELECT state_name, MEASURE(total_revenue)
FROM ${catalog}.${gold_schema}.revenue_metrics
WHERE state_name = 'California'  -- ✅ Bare dimension name
GROUP BY state_name

Metric views flatten all dimensions into a single namespace. When writing benchmark SQL or General Instructions examples, always use bare dimension names without table prefixes.

4. Full UC 3-Part Namespace Required

All table and function references MUST use full Unity Catalog namespace.

❌ WRONG:

SELECT * FROM fact_sales;
SELECT * FROM get_revenue_by_period('2024-01-01', '2024-12-31', 'week');

✅ CORRECT:

SELECT * FROM ${catalog}.${gold_schema}.fact_sales;
SELECT * FROM ${catalog}.${gold_schema}.get_revenue_by_period('2024-01-01', '2024-12-31', 'week');

5. Data Asset Hierarchy: Metric Views → TVFs → Tables

Always add assets in this order:

1. Metric Views (Primary - use first)

   • Pre-aggregated, optimized, rich semantics
   • Best for broad analytical queries

2. TVFs (Secondary - use for specific patterns)

   • Parameterized queries, business logic
   • Date-bounded queries, top N rankings

3. Tables (Last resort - use sparingly)

   • Only when metric views/TVFs insufficient
   • Reference data, ad-hoc exploration

6. Avoid Contradictory Routing Rules

Issue: Contradictory rules cause Genie to randomly select wrong assets.

✅ DO: Group by question type, not asset

Revenue/booking questions:
  - By property → revenue_analytics_metrics
  - By host → get_host_performance TVF (not metric view!)

❌ DON'T: Create conflicting asset mappings

- host_analytics_metrics → for host data
- get_host_performance → for host data  # ❌ CONFLICT!

7. Define Ambiguous Terms Explicitly

Common ambiguous terms: "underperforming", "top performing", "valuable customers", "best hosts"

✅ DO: Add explicit definitions

## Term Definitions
"underperforming" = properties with revenue below median (use get_underperforming_properties TVF)
"top performing" = highest revenue unless "rated" specified

8. TVF Syntax Rules

Common errors to prevent:

❌ WRONG:

SELECT * FROM TABLE(get_customer_segments(...))  -- ❌ Don't wrap in TABLE()
SELECT * FROM get_customer_segments()            -- ❌ Missing parameters
SELECT * FROM get_customer_segments(...) GROUP BY segment  -- ❌ Unnecessary GROUP BY

✅ CORRECT:

SELECT * FROM get_customer_segments('2020-01-01', '2024-12-31')  -- ✅ Direct call with params

9. 🔴 MANDATORY: Serverless SQL Warehouse Only

ALWAYS assign a Serverless SQL Warehouse to Genie Spaces. NEVER use Classic or Pro warehouses.

Serverless provides auto-scaling, instant startup, and cost-efficient idle timedowns -- critical for interactive Genie sessions where users expect sub-10-second responses.

❌ WRONG: Classic SQL warehouse with manual cluster sizing.

✅ CORRECT: Serverless SQL warehouse (auto-detected or explicitly set).

10. Table/Column COMMENTs Are Genie Fuel

Genie uses Unity Catalog TABLE and COLUMN comments to understand data. Missing comments = degraded SQL generation quality.

🔴 MANDATORY: Before adding ANY table as a trusted asset, verify it has:

• COMMENT ON TABLE with a business-friendly description
• COMMENT ON COLUMN for every column, including dimension values and business context

See Table Documentation Skill for comment standards.

❌ WRONG:

CREATE TABLE fact_sales (sale_id BIGINT, amt DECIMAL(18,2));  -- No comments, cryptic names

✅ CORRECT:

CREATE TABLE fact_sales (
  sale_id BIGINT COMMENT 'Unique sale identifier from POS system',
  total_amount DECIMAL(18,2) COMMENT 'Net sale amount in USD after discounts'
) COMMENT 'Daily retail sales transactions at store-SKU grain';

11. Pre-Creation Table Inspection Is Mandatory

Before creating a Genie Space, ALWAYS inspect target table schemas. Do not rely on assumed schemas.

1. Run DESCRIBE TABLE EXTENDED or use get_table_details for each trusted asset
2. Verify all tables have TABLE and COLUMN comments
3. Verify descriptive column names (use customer_lifetime_value NOT clv)
4. Verify proper data types (DATE columns for time-based queries)

See Configuration Guide for the full inspection checklist.

12. Prompt User for Benchmark Questions Before Generating

Always ask the user for benchmark questions before generating synthetic ones. User-provided questions reflect real business needs and catch domain-specific edge cases that synthetic generation misses.

Three outcomes:

• User provides 10+: Validate each one. Report any that can't be answered (missing table, ambiguous terms). Proceed with valid set.
• User provides 1-9: Validate provided, report issues, augment with synthetic to reach 10-15 total. Show augmentation to user.
• User provides none: Generate 10-15 synthetic benchmarks from asset metadata. Show to user for review.

If a user question can't be answered, do NOT silently drop it. Inform the user with the specific reason:

• "Table X is not a trusted asset in this space"
• "No data available for churn analysis — available domains are: revenue, bookings, property performance"
• "Term 'underperforming' is ambiguous — how should it be defined?"

See Benchmark Intake Workflow for the full validation and generation pipeline.

13. Validate Programmatically via Conversation API

After deployment, test benchmark questions programmatically using the Conversation API -- not just the UI.

# ✅ Programmatic validation (reproducible, automated)
result = ask_genie(space_id="your_space_id", question="What were total sales last month?")
assert result["status"] == "COMPLETED"
assert result["row_count"] > 0

Key rules:

• Start a NEW conversation for each unrelated benchmark question
• Use ask_genie_followup ONLY for related follow-up questions within the same topic
• Set timeouts: simple queries (30s), complex joins (60-120s), large scans (120s+)

See Configuration Guide for full testing patterns.

14. Extended Instructions Must Follow the 13-Section Structure

⚠️ CRITICAL: For domains beyond simple use cases, structure Extended Instructions into these 13 mandatory sections:

PURPOSE → ASSET ROUTING → BUSINESS DEFINITIONS → DISAMBIGUATION → AGGREGATION RULES → FUNCTION ROUTING → JOIN GUIDANCE → QUERY RULES → QUERY PATTERNS → TEMPORAL FILTERS → DATA QUALITY NOTES → CONSTRAINTS → SQL EXPRESSIONS

Each section serves a distinct purpose for Genie's SQL generation. Missing sections (especially ASSET ROUTING, DISAMBIGUATION, and BUSINESS DEFINITIONS) are the top causes of misrouted queries. Section 13 (SQL EXPRESSIONS) documents which concepts to promote into structured sql_snippets.

✅ DO: Follow the 13-section template in Agent Instructions Guide.

❌ DON'T: Write instructions as an unstructured wall of text or a flat numbered list without section headers.

15. Column Configs Require Genie-Specific Flags

Every column_config entry should include enable_format_assistance and/or enable_entity_matching flags based on column type.

Column Type	enable_format_assistance	enable_entity_matching
Dimension text/name	✅	✅
Categorical flag (Y/N)	✅	✅
Date/timestamp	✅	❌
Numeric ID	✅	❌
Numeric measure	❌	❌

See Agent Instructions Guide for the full pattern.

16. Synonyms Go in Column Configs, Not Table COMMENTs

Synonyms belong in Genie Space column_configs[].synonyms or metric view YAML synonyms fields. Never embed synonyms in Unity Catalog TABLE or COLUMN COMMENT strings.

• UC COMMENTs → business definitions, grain, valid values
• column_configs synonyms → user-friendly alternative names for NLQ matching

17. Append Instructions When Optimizing — Never Replace

When optimizing an existing Genie Space, APPEND new rules to the existing instruction block. NEVER replace the entire block.

Existing rules were validated against benchmarks. Replacing them risks regression on questions that currently work. See Agent Instructions Guide.

18. Validate Programmatically via Conversation API

After deployment, test benchmark questions programmatically using the Conversation API — not just the UI.

result = ask_genie(space_id="your_space_id", question="What were total sales last month?")
assert result["status"] == "COMPLETED"
assert result["row_count"] > 0

Key rules:

• Start a NEW conversation for each unrelated benchmark question
• Use ask_genie_followup ONLY for related follow-up questions within the same topic
• Set timeouts: simple queries (30s), complex joins (60-120s), large scans (120s+)

See Configuration Guide for full testing patterns.

19. SQL Expressions Provide Structured Business Concept Definitions

SQL Expressions (sql_snippets in the API) give Genie structured, parseable definitions of measures, filters, and dimensions. Unlike text instructions (free-form) and example SQL queries (full query templates), SQL expressions define individual reusable concepts that Genie can match directly to user questions.

Type	JSON Key	SQL Requirement	Example
Measure	measures	Aggregation function	SUM(table.total_sales_usd)
Filter	filters	Boolean condition	table.country_code = 'US'
Dimension	expressions	Column reference or derivation (no aggregation)	table.zone_combination

When to use SQL Expressions:

• KPIs that users ask about frequently (promote from BUSINESS DEFINITIONS)
• Common WHERE clauses that appear in 3+ benchmark queries (promote from AGGREGATION RULES)
• Grouping attributes with synonyms (promote from DISAMBIGUATION section)

Every SQL Expression MUST include:

• display_name: User-friendly name shown in Genie UI
• sql: Working SQL fragment referencing trusted asset table.column