databricks-table-valued-functions

name	databricks-table-valued-functions
description	End-to-end guide for planning, creating, deploying, and validating Table-Valued Functions (TVFs) in Databricks optimized for Genie Space natural language queries. Use when creating TVFs for Genie Spaces, planning TVF requirements from business questions, troubleshooting TVF compilation errors, or ensuring Genie compatibility. Includes requirements gathering templates, schema validation patterns, SQL requirements (STRING parameters, parameter ordering, LIMIT workarounds), v3.0 bullet-point comment format, null safety, SCD2 handling, cartesian product prevention, 5 complete domain-adaptable examples, Asset Bundle deployment patterns, and post-deployment validation queries.
clients	["ide_cli","genie_code"]
bundle_resource	jobs
deploy_verb	bundle_deploy
deploy_note	TVFs are created by a notebook_task in the semantic-layer job; deploy via `bundle deploy --target dev` (runDatabricksCli on Genie Code). TVF params are STRING for Genie compatibility.
coverage	full
metadata	{"author":"prashanth subrahmanyam","version":"2.0","domain":"semantic-layer","role":"worker","pipeline_stage":6,"pipeline_stage_name":"semantic-layer","called_by":["semantic-layer-setup"],"standalone":true,"last_verified":"2026-02-07","volatility":"medium","upstream_sources":[]}

End-to-end semantic layer? If you are creating TVFs as part of a larger deployment that also includes Metric Views and Genie Spaces, read semantic-layer/00-semantic-layer-setup/SKILL.md first — it orchestrates this skill with the others and mandates Gold schema validation before artifact creation.

Databricks Table-Valued Functions (TVFs) for Genie

Overview

Table-Valued Functions (TVFs) provide pre-built, parameterized queries that Genie can invoke for natural language access to your data. This skill covers the entire TVF lifecycle: planning, creation, deployment, and validation.

Why TVFs Matter:

• Pre-built queries ensure consistent business logic across all consumers
• LLM-friendly metadata helps Genie understand when to use each function
• Proper SQL patterns prevent compile-time errors and data inflation bugs
• Parameterized access gives users instant answers to common questions

Key Capabilities:

• Plan TVFs from business questions using requirements gathering templates
• Create TVFs with Genie-compatible parameter types (STRING for dates)
• Validate schemas before writing SQL to prevent 100% of compilation errors
• Structure comments (v3.0 bullet-point format) for optimal Genie query matching
• Handle SCD2 dimensions with proper is_current filtering
• Prevent cartesian products in aggregation CTEs
• Use proper parameter ordering and LIMIT workarounds
• Deploy TVFs via Asset Bundle jobs
• Validate TVFs with post-deployment queries

What TVFs Provide:

• ✅ Parameterized queries with input parameters
• ✅ LLM-friendly metadata for Genie understanding
• ✅ Genie-compatible types (STRING for dates, not DATE)
• ✅ Top N patterns using WHERE rank <= param (not LIMIT param)
• ✅ Null safety with NULLIF for all divisions
• ❌ No DATE parameters (Genie doesn't support DATE type)
• ❌ No LIMIT with parameters (use ROW_NUMBER + WHERE instead)

When to Use This Skill

Use this skill when:

• Planning which TVFs to create from business questions
• Creating TVFs for Genie Spaces
• Troubleshooting TVF compilation errors
• Ensuring Genie compatibility
• Validating schemas before writing SQL
• Deploying TVFs via Asset Bundles
• Preventing common SQL errors (parameter types, LIMIT clauses, cartesian products)

Layer-aware manifest fields: TVF manifest entries carry one of two table lists:

• gold_tables_used — canonical for Gold-based runs (acceleration; workshop on deployed_gold / gold_design). TVF DDL references ${catalog}.${gold_schema}.*.
• source_assets_used — canonical for workshop deployments on deployed_silver / deployed_bronze. TVF DDL references ${catalog}.${silver_schema}.* or ${catalog}.${bronze_schema}.* directly. The orchestrator prints a quality advisory; STRING parameter rules and Genie compatibility are unchanged.

The orchestrator (semantic-layer/00-semantic-layer-setup) only stops before invoking this skill when planning_source.selected_layer = source_csv (no live tables exist). Production TVFs should ultimately reference Gold; promote workshop TVFs to Gold for production hardening.

Quick Start (2-3 hours)

Goal: Create 10-15 pre-built, parameterized SQL queries for common business questions.

What You'll Create:

1. table_valued_functions.sql — SQL file with 10-15 TVF definitions using ${catalog} and ${gold_schema} template variables
2. create_tvfs.py — Python notebook that reads the SQL file, substitutes ${catalog} / ${gold_schema} variables, splits into individual statements, and executes each via spark.sql(). Use the canonical template at assets/templates/create_tvfs.py — it imports the shared src/common/_notebook_paths.py helper (see common/databricks-python-imports/SKILL.md) and fails loud with RuntimeError on any DDL error. Never call sys.exit(0) to report failure (exit 0 = "success" to Databricks Jobs).
3. tvf_job.yml — Asset Bundle job using notebook_task (NOT sql_task — see warning below)

⚠️ Why notebook_task instead of sql_task? TVF DDL uses ${catalog}.${gold_schema} in identifiers (schema-qualified function names). sql_task.parameters are SQL bind parameters (:param) that cannot substitute identifiers — only values in WHERE clauses. A Python notebook performs string substitution before executing the SQL.

Fast Track:

CREATE OR REPLACE FUNCTION get_top_stores_by_revenue(
    start_date STRING COMMENT 'Start date (format: YYYY-MM-DD)',
    end_date STRING COMMENT 'End date (format: YYYY-MM-DD)',
    top_n INT DEFAULT 10 COMMENT 'Number of top stores to return'
)
RETURNS TABLE(
    rank INT COMMENT 'Store rank by revenue',
    store_name STRING COMMENT 'Store display name',
    total_revenue DECIMAL(18,2) COMMENT 'Total revenue for period'
)
COMMENT '
• PURPOSE: Returns top N stores ranked by revenue for a date range
• BEST FOR: "What are the top 10 stores by revenue?" | "Show me best performing stores"
• RETURNS: Individual store rows (rank, store_name, total_revenue)
• PARAMS: start_date, end_date, top_n (default: 10)
• SYNTAX: SELECT * FROM get_top_stores_by_revenue(''2024-01-01'', ''2024-12-31'', 10)
'
RETURN ...;

Common Business Naming Patterns:

Pattern	Template	Example
Top N	get_top_{entity}_by_{metric}(start_date, end_date, top_n)	get_top_stores_by_revenue(...)
Trending	get_{metric}_trend(start_date, end_date)	get_daily_sales_trend(...)
Comparison	get_{metric}_by_{dimension}(start_date, end_date)	get_sales_by_state(...)
Performance	get_{entity}_performance(entity_id, start_date, end_date)	get_store_performance(...)

Critical SQL Rules:

• STRING for date parameters (never DATE)
• Required parameters first, DEFAULT parameters last
• ROW_NUMBER + WHERE for Top N (never LIMIT with parameter)

Output: 10-15 TVFs callable by Genie and queryable via SQL

See references/tvf-planning-guide.md for question categorization and domain examples. See references/tvf-examples.md for 5 complete, production-ready TVF implementations. See assets/templates/tvf-requirements-template.md to plan your TVFs before coding.

Critical Rules

⚠️ CRITICAL: Schema Validation BEFORE Writing SQL

RULE #0: Always consult YAML schema definitions before writing any TVF SQL

100% of SQL compilation errors are caused by not consulting YAML schemas first.

Pre-Development Checklist:

1. Read YAML schema files (5 minutes)
2. Create SCHEMA_MAPPING.md (2 minutes)
3. Write TVF SQL using documented schema
4. Run validation script (30 sec)
5. Deploy

ROI: 71% time reduction (45 min → 13 min)
First-Time Success Rate: 0% → 95%+

See references/tvf-patterns.md for detailed schema validation workflow.

⚠️ Issue 1: Parameter Types for Genie Compatibility

RULE: Use STRING for date parameters, not DATE

Genie Spaces do not support DATE type parameters. Always use STRING with explicit format documentation.

❌ DON'T:

CREATE FUNCTION get_sales_by_date_range(
  start_date DATE COMMENT 'Start date',
  end_date DATE COMMENT 'End date'
)

✅ DO:

CREATE FUNCTION get_sales_by_date_range(
  start_date STRING COMMENT 'Start date (format: YYYY-MM-DD)',
  end_date STRING COMMENT 'End date (format: YYYY-MM-DD)'
)
...
WHERE transaction_date BETWEEN CAST(start_date AS DATE) AND CAST(end_date AS DATE)

⚠️ Issue 2: Parameter Ordering with DEFAULT Values

RULE: Parameters with DEFAULT must come AFTER parameters without DEFAULT

❌ DON'T:

CREATE FUNCTION get_top_stores(
  top_n INT DEFAULT 10,          -- ❌ DEFAULT parameter first
  start_date STRING,              -- ❌ Required parameter after DEFAULT
  end_date STRING
)

✅ DO:

CREATE FUNCTION get_top_stores(
  start_date STRING,              -- ✅ Required parameter first
  end_date STRING,                -- ✅ Required parameter
  top_n INT DEFAULT 10            -- ✅ Optional parameter last
)

⚠️ Issue 3: LIMIT Clauses Cannot Use Parameters

RULE: Use WHERE rank <= parameter instead of LIMIT parameter

LIMIT clauses require compile-time constants. Use WHERE with ROW_NUMBER() instead.

❌ DON'T:

SELECT * FROM store_metrics
ORDER BY total_revenue DESC
LIMIT top_n;  -- ❌ Cannot use parameter here

✅ DO:

WITH ranked_stores AS (
  SELECT ...,
    ROW_NUMBER() OVER (ORDER BY total_revenue DESC) as rank
  FROM store_metrics
)
SELECT * FROM ranked_stores
WHERE rank <= top_n  -- ✅ Can use parameter in WHERE
ORDER BY rank;

⚠️ CRITICAL: Cartesian Product Bug in Aggregation CTEs

Never re-join a table that's already been aggregated in a CTE.

❌ BUGGY PATTERN:

WITH period_data AS (
  SELECT SUM(revenue) as total_revenue
  FROM fact_table
  GROUP BY period
),
final AS (
  SELECT SUM(pd.total_revenue), SUM(ft.other_metric)  -- 🔥 CARTESIAN!
  FROM period_data pd
  LEFT JOIN fact_table ft ON ...  -- ❌ Re-joining source!
)

✅ CORRECT PATTERN:

SELECT 
  period,
  SUM(revenue) as total_revenue,
  SUM(other_metric) as other_metric
FROM fact_table
GROUP BY period;  -- ✅ Single aggregation pass

See references/tvf-patterns.md for detailed cartesian product prevention patterns.

Quick Reference

Standardized TVF Comment Format (v3.0)

Use bullet-point format for ALL TVF comments:

COMMENT '
• PURPOSE: [One-line description of what the TVF does]
• BEST FOR: [Example questions separated by |]
• NOT FOR: [What to avoid - redirect to correct TVF] (optional)
• RETURNS: [PRE-AGGREGATED rows or Individual rows] (exact column list)
• PARAMS: [Parameter names with defaults]
• SYNTAX: SELECT * FROM tvf_name(''param1'', ''param2'')
• NOTE: [Important caveats - DO NOT wrap in TABLE(), etc.] (optional)
'

Complete TVF Pattern

CREATE OR REPLACE FUNCTION get_top_stores_by_revenue(
  -- Required parameters first (no DEFAULT)
  start_date STRING COMMENT 'Start date (format: YYYY-MM-DD)',
  end_date STRING COMMENT 'End date (format: YYYY-MM-DD)',
  -- Optional parameters last (with DEFAULT)
  top_n INT DEFAULT 10 COMMENT 'Number of top stores to return'
)
RETURNS TABLE(
  rank INT COMMENT 'Store rank by revenue',
  store_number STRING COMMENT 'Store identifier',
  store_name STRING COMMENT 'Store name',
  total_revenue DECIMAL(18,2) COMMENT 'Total revenue for period',
  total_units BIGINT COMMENT 'Total units sold'
)
COMMENT '
• PURPOSE: Returns the top N stores ranked by revenue for a date range
• BEST FOR: "What are the top 10 stores by revenue?" | "Show me best performing stores"
• RETURNS: Individual store rows (rank, store_number, store_name, total_revenue, total_units)
• PARAMS: start_date, end_date, top_n (default: 10)
• SYNTAX: SELECT * FROM get_top_stores_by_revenue(''2024-01-01'', ''2024-12-31'', 10)
• NOTE: Returns user_id for individual store analysis | Sorted by total_revenue DESC
'
RETURN
  WITH store_metrics AS (
    SELECT 
      store_number,
      store_name,
      SUM(net_revenue) as total_revenue,
      SUM(net_units) as total_units
    FROM fact_sales_daily
    WHERE transaction_date BETWEEN CAST(start_date AS DATE) AND CAST(end_date AS DATE)
    GROUP BY store_number, store_name
  ),
  ranked_stores AS (
    SELECT 
      ROW_NUMBER() OVER (ORDER BY total_revenue DESC) as rank,
      store_number,
      store_name,
      total_revenue,
      total_units
    FROM store_metrics
  )
  SELECT * FROM ranked_stores
  WHERE rank <= top_n  -- ✅ Use WHERE instead of LIMIT
  ORDER BY rank;

Core Patterns

Null Safety

Always use NULLIF() for division to prevent divide-by-zero errors:

-- ✅ DO: Null-safe division
total_revenue / NULLIF(transaction_count, 0) as avg_transaction_value

SCD Type 2 Dimension Handling

Always filter for current records when joining SCD2 dimensions:

-- ✅ Correct: Filter for current version
LEFT JOIN dim_store ds 
  ON fsd.store_number = ds.store_number 
  AND ds.is_current = true

Aggregate vs Individual Row TVFs

Aggregate TVF (Returns Pre-Aggregated Rows):

• Returns fixed number of rows (e.g., 5 segment rows)
• Data is PRE-AGGREGATED - no GROUP BY needed on top
• Do NOT use in JOINs (no user_id or other keys to join on)

Individual Row TVF (Returns Detail Rows):

• Returns variable number of rows based on data
• Each row represents one entity (customer, property, host)
• CAN be used in JOINs (has identifier columns)

See references/genie-integration.md for detailed examples.

TVF Creation Checklist

SQL Compliance

• All date parameters are STRING type (not DATE)
• Required parameters come before optional parameters
• No parameters used in LIMIT clauses (use WHERE rank <= param)
• All divisions use NULLIF to prevent divide-by-zero
• SCD2 joins include is_current = true filter
• No cartesian products: CTEs don't re-join tables already aggregated
• Single aggregation pass: Each source table read and aggregated only once

Genie Optimization (Standardized Comment Format)

• Function COMMENT uses bullet-point format (• PURPOSE, • BEST FOR, etc.)
• PURPOSE: One-line description of what TVF does
• BEST FOR: 2+ example questions (pipe-separated)
• NOT FOR / PREFERRED OVER: Redirect to correct asset when applicable
• RETURNS: Specifies PRE-AGGREGATED or Individual rows + exact column list
• PARAMS: Parameter names with defaults
• SYNTAX: Exact copyable example with proper date format
• NOTE: Caveats (DO NOT wrap in TABLE(), DO NOT add GROUP BY, etc.)
• All parameters have descriptive COMMENT with format
• All returned columns have COMMENT
• Professional language (no "metric view is broken" phrases)

Testing

• Function compiles without errors
• Function executes with valid parameters
• Function handles edge cases (empty results, null values)
• Function tested in Genie Space (if applicable)
• Results validated against metric view (ratio ≈ 1.0, not 254x)

Implementation Workflow

Phase 1: Planning (30 min)

• Fill out requirements template (assets/templates/tvf-requirements-template.md)
• List 10-15 common business questions from stakeholders
• Categorize questions (revenue, product, entity, trend)
• Map questions to TVF names and parameters using naming patterns
• Identify required vs optional parameters

See references/tvf-planning-guide.md for question categories and domain examples.

Phase 2: SQL Development (1-2 hours)