Searching and Deleting Orphan Functions from Databricks Unity Catalog¶

What Are Orphan Functions?¶

In Databricks Unity Catalog, orphan functions are masking or row filter functions that exist in the catalog but are no longer attached to any table or column.

This situation typically occurs when:

• A table or view is deleted.
• A column using masking or row filtering is removed.
• Policies are removed without cleaning up the associated functions.

Orphan functions are not used by any active object and can be safely deleted after verification.

Where Functions Are Stored¶

Privacera stores masking and row filter functions in a dedicated security schema within your Databricks Unity Catalog. The functions are typically stored in the privacera_security schema (or the schema configured for Privacera security functions).

The full qualified path for these functions follows this pattern:

1

<catalog>.privacera_security.<function_name>

Common Naming Patterns¶

Masking and row filter functions created by Privacera follow these naming conventions:

Masking Functions¶

1

<catalog>.privacera_security.<table>_<column>_masking

Example:

1

my_catalog.privacera_security.sales_schema_person_details_dob_masking

Row Filter Functions¶

1

<catalog>.privacera_security.<table>_row_filter

Example:

1

my_catalog.privacera_security.sales_schema_employees_row_filter

Step 1: List Masking and Row Filter Functions¶

Use the following SQL command to list functions in the Privacera security schema:

1

SHOW FUNCTIONS IN <catalog>.privacera_security;

Filter the results for function names containing:

• _masking (for masking functions)
• _row_filter (for row filter functions)

Example:

1

SHOW FUNCTIONS IN my_catalog.privacera_security;

Filtering Results:

To filter and view only masking and row filter functions, you can use:

1
2

SHOW FUNCTIONS IN <catalog>.privacera_security LIKE '*_masking';
SHOW FUNCTIONS IN <catalog>.privacera_security LIKE '*_row_filter';

Example:

1
2

SHOW FUNCTIONS IN my_catalog.privacera_security LIKE '*_masking';
SHOW FUNCTIONS IN my_catalog.privacera_security LIKE '*_row_filter';

Example Output:

The output will show functions such as:

• sales_schema_person_details_dob_masking
• sales_schema_employees_row_filter

Step 2: Identify Orphan Functions¶

To verify whether a function is orphaned, extract the table and column names from the function name and check whether they still exist.

For Masking Functions¶

Masking functions follow the pattern: <schema>_<table>_<column>_masking

Steps to verify:¶

1. Extract schema, table, and column names from the function name:

• Example function name: sales_schema_person_details_dob_masking.
• The function name contains schema, table, and column separated by underscores.
• Schema: sales_schema (the first part before the table name).
• Table: person_details (the part before the column name).
• Column: dob (the part before _masking).

2. Verify the table exists in the schema:

1

SHOW TABLES IN <catalog>.<schema>;

Or check if the table exists:

1

SELECT * FROM <catalog>.<schema>.<table_name> LIMIT 1;

3. Verify the column exists in the table:

1

DESCRIBE TABLE <catalog>.<schema>.<table_name>;

Or check columns:

1
2
3
4
5

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = '<schema>' 
  AND table_name = '<table_name>' 
  AND column_name = '<column_name>';

Example:

For function sales_schema_person_details_dob_masking:

• Table: person_details
• Column: dob
• Schema: sales_schema (extracted from function name)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Check if table exists
SHOW TABLES IN my_catalog.sales_schema;

-- Or verify table and column
DESCRIBE TABLE my_catalog.sales_schema.person_details;

-- Check if column exists
SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'sales_schema' 
  AND table_name = 'person_details' 
  AND column_name = 'dob';

If the table or column does not exist, the function is orphaned.

For Row Filter Functions¶

Row filter functions follow the pattern: <schema>_<table>_row_filter

Steps to verify:¶

1. Extract schema and table name from the function name:

• Function name: sales_schema_employees_row_filter.
• The function name contains schema and table separated by underscores.
• Schema: sales_schema (the first part before the table name).
• Table: employees (the part before _row_filter).

2. Verify the table exists in the schema:

1

SHOW TABLES IN <catalog>.<schema>;

Or check if the table exists:

1

SELECT * FROM <catalog>.<schema>.<table_name> LIMIT 1;

Example:

For function sales_schema_employees_row_filter:

• Table: employees
• Schema: sales_schema (extracted from function name)

1
2
3
4
5

-- Check if table exists
SHOW TABLES IN my_catalog.sales_schema;

-- Or verify table exists
SELECT * FROM my_catalog.sales_schema.employees LIMIT 1;

If the table does not exist, the function is orphaned.

Step 3: Delete Orphan Functions¶

After confirming that the function is no longer in use, delete it using:

1

DROP FUNCTION <catalog>.privacera_security.<function_name>;

Example:

1

DROP FUNCTION my_catalog.privacera_security.sales_schema_person_details_dob_masking;