Using Temporary Functions

[Marbricks]

Hi everyone,

I'm currently doing my professional internship and working on a reporting project that integrates with Databricks through Unity Catalog functions.

The reporting application is external to Databricks and uses Data Sources, Parameters, Result Sets, Grids, Charts, and Report Definitions to build dashboards. Existing reports consume data through functions referenced by a URI pattern similar to:

fn://catalog.schema.function_name@DATABRICKS.UNITYCATALOG
Show more lines

My mentor provided an existing report as a starting point and asked me to use it as a template for the new development. The report is already functional and contains the complete structure, including Data Sources, Parameters, Result Sets, Grids, Charts, and Report Definitions. My task is to understand how those pieces work together and then adapt or replace them to support a different dataset.

The challenge is that the new dataset does not currently have equivalent Unity Catalog functions available.

As guidance, my mentor suggested creating temporary functions first, using a pattern such as:

SQL
1
CREATE OR REPLACE TEMPORARY FUNCTION `catalog.schema.function_name`
2
RETURNS TABLE (...)
3
RETURN ...
4
 
Show more lines

The idea is to prototype the expected interface, validate the returned schema locally with the reporting application, and later create the permanent Unity Catalog functions if the design works correctly.

My mentor has already given me a clear direction, but as an intern I am still connecting the dots and trying to understand the complete flow from temporary functions, to production Unity Catalog functions, to the final report displayed in the application.

Has anyone worked on a similar integration between an external dashboard/reporting application and Databricks?

Any guidance, recommendations, or examples would be greatly appreciated.

I'm still pretty new to this area, so honestly I'm feeling a bit lost and trying to understand the correct path before I spend time going in the wrong direction 😅.

Thanks in advance! 👍

[ivanvyd]

@Marbricks your mentor’s approach is useful for validating the function contract, but the temporary function should use an unqualified name

Databricks defines a temporary function in CREATE FUNCTION as:

“When you specify TEMPORARY, the created function is valid and visible in the current session. No persistent entry is made in the catalog.”

A three-part reference such as catalog.schema.function_name() resolves to a persistent catalog function. Create and call the prototype without the catalog and schema:

CREATE OR REPLACE TEMPORARY FUNCTION function_name(p_id BIGINT)
RETURNS TABLE (id BIGINT, value STRING)
LANGUAGE SQL
RETURN
  SELECT id, value
  FROM catalog.schema.source_table
  WHERE id = p_id;

SELECT * FROM function_name(123);

The reporting application can use this temporary function only if it creates and invokes it through the same Databricks session. A new or pooled connection may use a different session and will not see it.

The fn://catalog.schema.function_name@DATABRICKS.UNITYCATALOG value is not Databricks SQL syntax; it appears to be your reporting connector’s resource URI. For the final integration, create the permanent Unity Catalog table function with the three-part name:

CREATE OR REPLACE FUNCTION catalog.schema.function_name(p_id BIGINT)
RETURNS TABLE (id BIGINT, value STRING)
LANGUAGE SQL
RETURN
  SELECT id, value
  FROM catalog.schema.source_table
  WHERE id = p_id;

Keep the parameter types and returned column names/types identical to the tested contract. Grant the application principal USE CATALOG, USE SCHEMA, and EXECUTE. The function owner must retain access to the source objects because SQL UDF bodies run with the owner’s privileges.

Databricks recommends:

“Following the principle of least privilege, Databricks recommends granting EXECUTE on individual functions.”

Test the permanent function in Databricks SQL, then confirm that the reporting connector supports Unity Catalog table functions and parameter binding through its fn:// URI.

[rkhand14_ltm]

@ivanvyd  

If I understand it correctly, fn://catalog.schema.function_name@DATABRICKS.UNITYCATALOG is a URI that uniquely identifies a Unity Catalog SQL or Python function. It is primarily used by Databricks AI Agents and MCP integrations to discover and invoke governed Unity Catalog functions as tools. The URI contains the catalog, schema, function name, and the Unity Catalog provider namespace, enabling secure, permission-controlled tool calling.

The agent call the function as a tool using a URI like fn://catalog.schema.function@DATABRICKS.UNITYCATALOG. This provides governance, auditing, RBAC, and abstraction of the underlying model.

Table      ->     main.sales.customers

Function ->    fn://catalog.schema.function@DATABRICKS.UNITYCATALOG

Model     ->    models:/model_name/version_or_alias

@Marbricks  

Instead of using a temporary function, I would recommend creating a regular function in the Dev environment. Since temporary functions are session-scoped, they are only available within the current session. A regular function will be registered in Unity Catalog and should be sufficient for completing your POC.

Use a temporary function only when your report is session-specific and the function is both created and consumed within the same session.

Below terms seem specific to SSRS   - Data Sources, Parameters, Result Sets, Grids, Charts, and Report Definitions. you can check in SSRS documentation as well. 

[ivanvyd]

@rkhand14_ltm I agree that Unity Catalog functions can be exposed as governed tools for AI agents. My uncertainty concerns the specific fn://...@DATABRICKS.UNITYCATALOG notation from the original question. My earlier description of it as a possible connector resource URI was an interpretation.

The Databricks agent documentation shows fully qualified function names with UCFunctionToolkit and HTTPS endpoints for managed MCP. Could you share the documentation for this particular URI format? That would help establish how it relates to the reporting application.

Regarding the prototype, Databricks states:

“Temporary functions only exist within the session or query and must never be qualified.”

A temporary function can be sufficient for validating the parameters and returned schema within a Databricks session. Testing it through the reporting application depends on whether the connector can create and invoke it in that same session. If it requires catalog discovery or cannot preserve that session, a persistent development function becomes useful. Otherwise, I am not sure the additional efforts are required.

The reporting application’s name or connector documentation would help determine whether that additional step is needed.

[data_pulse]

@Marbricks 
Follow this approach: define the function’s interface first, then connect the real data. Use a temporary function for testing within the same session, and a persistent Unity Catalog function in a development schema for testing with the external reporting application.

1. Prototype the expected parameters and output columns

CREATE OR REPLACE TEMPORARY FUNCTION report_data(p_id BIGINT)
RETURNS TABLE (id BIGINT, label STRING, amount DOUBLE)
LANGUAGE SQL
RETURN
  SELECT p_id, 'Prototype', CAST(100 AS DOUBLE);

SELECT * FROM report_data(123);

Temporary functions exist only in the session that creates them. An external application using another connection will not see this function.

2. Test the application with an UC registered function

Create the same prototype using (With same definition from Above):

CREATE OR REPLACE FUNCTION dev_catalog.reporting.report_data(...)

Reuse the parameters, return schema, and body from step 1, removing TEMPORARY and using the fully qualified name.

Update the report’s data source using the same URI format as the working report:

fn://dev_catalog.reporting.report_data@DATABRICKS.UNITYCATALOG

Map the report parameters to the functions arguments and bind the returned columns to the grids and charts.

Potential Flow is: Report parameter → Function argument → Returned result set → Grid/chart

3. Replace the prototype body with real data

If the dataset is already in databricks, SQL function can query it as:

RETURN
  SELECT s.id, s.label, s.amount
  FROM dev_catalog.reporting.source_data AS s
  WHERE s.id = p_id;

Alternatively, If the data comes from an external API, expose a Python table function that fetches the data and returns rows with a defined schema.

CREATE OR REPLACE FUNCTION dev_catalog.reporting.api_data(p_id BIGINT)
RETURNS TABLE (id BIGINT, label STRING, amount DOUBLE)
LANGUAGE PYTHON
HANDLER 'ApiData'
AS $$
class ApiData:
    def eval(self, p_id):
        import requests

        response = requests.get(
            "https://api.example.com/data",
            params={"id": p_id},
            timeout=30,
        )
        response.raise_for_status()

        for row in response.json()["data"]:
            yield int(row["id"]), row["label"], float(row["amount"])
$$;

The reporting application consumes it like any other table function:

SELECT * FROM dev_catalog.reporting.api_data(123);

Before testing, grant the application identity USE_CATALOG, USE_SCHEMA, EXECUTE on the function, plus access to the compute endpoint. For the API example, confirm the compute (Databricks Run Time) supports UC Python table functions, configure API authentication, and ensure network access to the endpoint. Once validated, deploy the production function, update the report URI, and preserve the tested parameters and output schema.