10.3 Trusted Assets & Parameterized Functions

10.3 Trusted Assets & Parameterized Functions

In enterprise analytics, certain calculations—such as official financial earnings, regulatory compliance metrics, or complex customer churn algorithms—cannot tolerate LLM generation variance. While dynamic SQL translation handles ad-hoc exploration effectively, business-critical KPIs demand deterministic execution. Databricks AI/BI Genie addresses this requirement through Trusted Assets: pre-verified, published SQL assets and parameterized Unity Catalog User-Defined Functions (UDFs) that Genie executes directly whenever relevant natural language prompts are detected.

Understanding Trusted Assets in AI/BI Genie

A Trusted Asset is a curated, analyst-approved data object linked to a Genie space. When an end user asks a question that matches the semantic scope of a Trusted Asset, Genie bypasses dynamic SQL generation and executes the trusted code path instead.

                  +-----------------------------------+
                  |      User Natural Language        |
                  |     "Calculate ARR for Q1 2026"   |
                  +-----------------------------------+
                                    |
                                    v
                     /-----------------------------\
                    /   Genie Intent Engine         \
                   \  Matches Trusted Asset Scope?  /
                    \-------------------------------/
                               /         \
                       YES    /           \   NO
                             v             v
          +------------------------+   +------------------------+
          | Execute Verified UDF   |   | Dynamic SQL Generation |
          | - 100% Deterministic   |   | - LLM Constructs SQL   |
          | - Zero Hallucinations  |   | - Validated on DBSQL   |
          +------------------------+   +------------------------+

Key Advantages of Trusted Assets

• Deterministic Precision: Guarantees identical, verified SQL logic every time a specific metric is requested.
• Governance & Compliance: Ensures executive dashboards and financial reports align strictly with audited corporate definitions.
• Complex Logic Encapsulation: Enables execution of sophisticated queries involving window functions, recursive CTEs, or procedural logic that LLMs might struggle to construct dynamically.

Authoring Parameterized Unity Catalog SQL UDFs

The primary vehicle for creating flexible Trusted Assets is the Parameterized SQL Table Function (UDTF) or Scalar Function registered in Unity Catalog. SQL UDFs accept input parameters (such as date ranges, region codes, or customer identifiers) and return filtered tables or scalar metrics.

-- Creating a parameterized Unity Catalog SQL UDTF for accurate customer LTV calculation
CREATE OR REPLACE FUNCTION main.finance_gold.get_customer_ltv_by_segment(
    start_date DATE,
    end_date DATE,
    target_segment STRING
)
RETURNS TABLE (
    customer_id INT,
    customer_name STRING,
    segment STRING,
    total_orders INT,
    lifetime_value NUMERIC(18, 2)
)
COMMENT 'Trusted Asset: Calculates lifetime value (LTV) and total orders for customers in a specific segment between start_date and end_date.'
RETURN 
    SELECT 
        c.customer_id,
        c.customer_name,
        c.customer_segment AS segment,
        COUNT(DISTINCT o.order_id) AS total_orders,
        SUM(o.net_amount) AS lifetime_value
    FROM main.sales_gold.dim_customers c
    JOIN main.sales_gold.fact_orders o ON c.customer_id = o.customer_id
    WHERE o.order_date BETWEEN start_date AND end_date
      AND c.customer_segment = target_segment
      AND o.order_status = 'COMPLETED'
    GROUP BY c.customer_id, c.customer_name, c.customer_segment;

When authoring functions for Genie:

1. Define explicit data types for all input parameters (DATE, TIMESTAMP, STRING, INT).
2. Provide comprehensive function comments explaining what the function calculates and what inputs it expects.
3. Ensure return table schema column names are clear and business-friendly.

Registering & Binding Trusted Assets to Genie Spaces

Once a SQL function is created in Unity Catalog, an analyst must explicitly bind it to the target Genie Space:

1. Open the Genie Space Settings and navigate to the Trusted Assets tab.
2. Click Add Trusted Asset and select Unity Catalog Function.
3. Browse the catalog hierarchy (catalog.schema.function_name) and select the target UDF.
4. Define sample natural language triggers that should route to this function (e.g., "What is the LTV for Enterprise customers in 2025?").
5. Verify parameter mapping rules and publish the updated space.

-- Granting EXECUTE permission on the SQL function to space users
GRANT EXECUTE ON FUNCTION main.finance_gold.get_customer_ltv_by_segment TO ROLE analytics_users;

Parameter Extraction & Execution Mechanics

When a user submits a natural language question matching a Trusted Asset, Genie's intent parser performs argument extraction:

1. Entity Recognition: Identifies entities in the prompt matching parameter types (e.g., parsing "between January 2025 and December 2025" as start_date = '2025-01-01' and end_date = '2025-12-31').
2. Value Binding: Maps extracted values to the corresponding UDF input arguments.
3. Execution Call: Invokes the UDF on the Databricks SQL Warehouse:

-- Generated query executing the Trusted Asset with bound parameters
SELECT * FROM main.finance_gold.get_customer_ltv_by_segment(
    CAST('2025-01-01' AS DATE),
    CAST('2025-12-31' AS DATE),
    'Enterprise'
);

If an optional parameter cannot be extracted from the user's prompt, Genie will either request clarification from the user or apply default values defined within the UDF signature.

Trusted Assets vs. Dynamic SQL Generation

Analysts must balance Trusted Assets and dynamic SQL generation when building enterprise Genie spaces: