Query Workloads Issued Against Unity Catalog¶

This page documents every category of query and API call that the Privacera Databricks Unity Catalog connector issues against a Unity Catalog metastore and its SQL warehouse, together with what triggers each one and how often it runs.

Use it to understand the query footprint and the load the connector places on the warehouse, and to plan warehouse sizing, auto-stop settings, and sync intervals.

Where the load comes from¶

The connector runs four independent, scheduled loops plus an optional event-driven path. Each loop is what actually generates warehouse traffic.

Policy application (grants and revokes) is not on a timer — it is driven by policy changes in Privacera and runs as soon as a change is detected.

Resource loader¶

Native (Unity Catalog managed) catalogs¶

Native catalogs are read entirely from information_schema. All queries are plain SELECT statements — there is no per-table DESCRIBE for native catalogs.

-- Catalogs
select catalog_name as catalogName, catalog_owner as owner, created
  from `system`.`information_schema`.`catalogs`;

-- Schemas
select schema_name as schemaName, catalog_name as catalogName, created, schema_owner as owner
  from `{catalog}`.`information_schema`.`schemata`;

-- Tables and views
select table_name as tableName, table_schema as schemaName, table_type as tableType,
       table_owner as owner, created
  from `{catalog}`.`information_schema`.`tables`
 where table_schema not in ('information_schema')
 order by table_schema;

-- Columns
select column_name as columnName, table_schema as schemaName, table_name as tableName,
       data_type as dataType, full_data_type as fullDataType,
       (ordinal_position+1) as columnPosition
  from `{catalog}`.`information_schema`.`columns`
 where table_schema = '{schema}'
 order by table_name;

-- Functions
select specific_name as functionName, specific_schema as schemaName, routine_owner as owner, created
  from `{catalog}`.`information_schema`.`routines`;

-- Volumes
select volume_name as volumeName, volume_schema as schemaName, volume_owner as owner, created
  from `{catalog}`.`information_schema`.`volumes`;

-- Metastore
select metastore_name, metastore_owner as owner, created
  from `system`.`information_schema`.`metastores`;

-- External locations
select external_location_name as externalLocationName, external_location_owner as owner, created
  from `system`.`information_schema`.`external_locations`;

-- Storage credentials
select storage_credential_name as storageCredentialName, storage_credential_owner as owner, created
  from `system`.`information_schema`.`storage_credentials`;

-- Service credentials
select credential_name as serviceCredentialName, credential_owner as owner, created
  from `system`.`information_schema`.`credentials`
 where credential_purpose='SERVICE';

-- Connections
show connections;

-- Catalog type probe (detects foreign catalogs)
DESCRIBE CATALOG `{catalog}`;

How often each one runs:

Column loading dominates: it is the only per-schema query in the native path. Deployments with many schemas should consider selective resource loading or disabling column loading if column-level policies are not used.

Foreign (federated) catalogs¶

Foreign catalogs are not fully represented in information_schema, so the connector falls back to SHOW and DESCRIBE, which is significantly more expensive:

-- Schemas
show schemas in `{catalog}`;

-- Tables
show table extended in `{catalog}`.`{schema}` like '*';

-- Table types
select table_name, table_catalog, table_schema, table_type
  from `system`.`information_schema`.`tables`
 where table_catalog = '{catalog}' and table_schema = '{schema}';

-- Columns
describe table extended `{catalog}`.`{schema}`.`{table}`;

How often each one runs:

The per-table describe table extended calls are parallelized across a dedicated describe pool. Concurrency is capped by load.resources.load.table.thread.count (schemas in flight) and load.resources.load.foreign.table.describe.thread.count (tables per schema).

Permissions loader¶

Permission loading is perResource: the connector issues one SHOW GRANTS statement per managed resource on each permission-sync cycle.

SHOW GRANTS ON {resource_type} {resource_fqdn}

Examples of the rendered statements:

SHOW GRANTS ON CATALOG `sales`;
SHOW GRANTS ON SCHEMA `sales`.`orders`;
SHOW GRANTS ON TABLE `sales`.`orders`.`line_items`;
SHOW GRANTS ON FUNCTION `sales`.`orders`.`mask_email`;
SHOW GRANTS ON VOLUME `sales`.`orders`.`raw_files`;
SHOW GRANTS ON EXTERNAL LOCATION `s3_landing`;
SHOW GRANTS ON STORAGE CREDENTIAL `s3_role`;
SHOW GRANTS ON SERVICE CREDENTIAL `svc_cred`;
SHOW GRANTS ON CONNECTION `pg_conn`;
SHOW GRANTS ON METASTORE;

Notes on how the statement is built:

• Views and materialized views are queried as TABLE; models are queried as FUNCTION.
• METASTORE is queried without a name — there is one metastore per region.
• Resource types disabled by the load.<type>.enable flags are skipped entirely, and so are resources excluded by the ignore lists.

This is the single largest contributor to permission-sync cost: a metastore with N managed resources produces N SHOW GRANTS statements per cycle. Loading is spread over load.permissions.thread.count threads (default 3) in batches of load.permissions.batch.size (default 1000).

Masking and row-filter loader¶

When masking or row-level filtering is enabled, each table additionally has its existing native policies read back:

-- Column masks
SELECT c.column_name, r.routine_definition
FROM `{catalog}`.`information_schema`.`routines` r
JOIN `{catalog}`.`information_schema`.`column_masks` c
  ON SUBSTRING_INDEX(c.mask_name, '.', -1) = r.routine_name
WHERE c.table_schema='{schema}' AND c.table_name='{table}';

-- Row filters
SELECT c.table_name, c.filter_name, r.routine_definition
FROM `{catalog}`.`information_schema`.`routines` r
JOIN `{catalog}`.`information_schema`.`row_filters` c
  ON SUBSTRING_INDEX(c.filter_name, '.', -1) = r.routine_name
WHERE c.table_schema='{schema}' AND c.table_name='{table}';

Foreign catalogs use the equivalent load_foreign_mask / load_foreign_rlf variants, which read the routine from the dedicated masking catalog and the mask/filter binding from system.information_schema.

Token privilege pre-checks¶

Before applying policies on a catalog, the connector verifies that its own service principal holds the privileges it needs:

SHOW GRANTS ON CATALOG `{catalog}`;
GRANT MANAGE ON CATALOG `{catalog}` TO `{api_token_user}`;

SHOW GRANTS ON CATALOG runs per catalog and the result is cached on the catalog resource for the cycle. The GRANT MANAGE statement runs only when the check shows the privilege is missing. Which privileges are required depends on avoid.token.user.allPrivileges (default true) and on whether the catalog is native or foreign.

Grants and revokes¶

Grants and revokes are applied when a Privacera policy changes, not on a schedule. Statements follow the standard Unity Catalog syntax and are generated from the connector's permission map, for example:

GRANT SELECT ON TABLE `sales`.`orders`.`line_items` TO `analyst@example.com`;
REVOKE MODIFY ON SCHEMA `sales`.`orders` FROM `priv_group_analysts`;
GRANT CREATE SCHEMA ON CATALOG `sales` TO `priv_role_dataeng`;
GRANT CREATE EXTERNAL LOCATION ON METASTORE TO `priv_group_admins`;

Statement merging¶

Actions are grouped by (resource, actor, actorType) and then by the ON clause, and each group is executed as one combined statement rather than one statement per privilege:

GRANT SELECT, MODIFY ON TABLE `sales`.`orders`.`line_items` TO `analyst@example.com`;

If any privilege in a group is ALL PRIVILEGES, the merged statement collapses to ALL PRIVILEGES. This keeps the number of round trips proportional to (resource x principal), not to (resource x principal x privilege).

Implicit grants¶

Granting access to a table or schema also requires traversal privileges on its parents. The connector issues the implicit USE CATALOG and USE SCHEMA grants alongside the explicit grant, and tracks them so they are revoked only when no remaining policy needs them:

GRANT USE CATALOG ON CATALOG `sales` TO `analyst@example.com`;
GRANT USE SCHEMA ON SCHEMA `sales`.`orders` TO `analyst@example.com`;

perform.grant.updates=false turns the apply path into a dry run: actions are marked SKIPPED and no SQL is sent to the warehouse.

Masking and row-filter DDL¶

Native masking and row filtering create UDFs in a policy schema and bind them to the column or table. A single masking policy change can therefore produce several statements:

CREATE SCHEMA IF NOT EXISTS {policy_schema};
CREATE OR REPLACE FUNCTION {policy_fqdn} ({column_parameters}) {returns_data_type} RETURN {condition};
ALTER TABLE {table_fqdn} ALTER COLUMN `{column}` SET MASK {policy_fqdn} {additional_columns_clause};
GRANT USE SCHEMA ON SCHEMA {policy_schema} TO `account users`;
GRANT EXECUTE ON SCHEMA {policy_schema} TO `account users`;

Removal is two statements:

ALTER TABLE {table_fqdn} ALTER COLUMN `{column}` DROP MASK;
DROP FUNCTION IF EXISTS {policy_fqdn};

Row filters follow the same shape, binding and dropping the filter on the table instead of the column:

ALTER TABLE {table_fqdn} SET ROW FILTER {policy_fqdn} ON ({column_names});
ALTER TABLE {table_fqdn} DROP ROW FILTER;

For foreign catalogs the create_foreign_mask and create_foreign_rlf variants additionally run CREATE CATALOG IF NOT EXISTS for the dedicated masking catalog.

Reusable master-UDF row-level security¶

When reusable UDF row-level security is enabled, per-policy UDFs are replaced by one master UDF plus rows in an entitlement table. The row filter is set once per table, and policy changes become DML on the entitlement table:

ALTER TABLE {table_fqdn} SET ROW FILTER {master_udf_invocation} ON ({column_names_with_table_literal});

MERGE INTO {entitlement_table} AS t
USING (SELECT ... ) AS s ON ...
WHEN MATCHED THEN UPDATE SET t.updated_at = current_timestamp()
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);

DELETE FROM {entitlement_table} WHERE principal_id = '...' AND principal_type = '...'
  AND table_fqn = '...' AND filter_name = '...';

SELECT principal_id, table_fqn, filter_name, principal_type FROM {entitlement_table};

Batch variants (insertEntitlementBatch / deleteEntitlementBatch) collapse many entitlement changes into a single MERGE or DELETE. The entitlement prefetch shown above runs once per apply cycle and is cached in memory.

This mode trades many CREATE FUNCTION / ALTER TABLE statements for a small number of DML statements, which materially reduces warehouse load on large policy sets.

Secure views¶

When view-based masking or row filtering is enabled, the connector reads and writes view DDL:

SELECT schema_name FROM `{catalog}`.`information_schema`.`schemata` WHERE schema_name = '{schema}';
SELECT view_definition FROM `{catalog}`.`information_schema`.`views` WHERE table_schema = '{schema}' AND table_name = '{view}';
SELECT table_name FROM `{catalog}`.`information_schema`.`tables` WHERE table_schema = '{view_schema}';

CREATE SCHEMA IF NOT EXISTS `{catalog}`.`{view_schema}`;
DROP VIEW IF EXISTS `{catalog}`.`{view_schema}`.`{view_name}`;
CREATE VIEW `{catalog}`.`{view_schema}`.`{view_name}` AS SELECT {columns} FROM `{catalog}`.`{table_schema}`.`{table_name}` {where_clause};
DROP SCHEMA IF EXISTS `{catalog}`.`{view_schema}`;

With secure.view.create.for.all=true (the default) a secure view is created for every managed table, so this scales with table count rather than with policy count.

UDF registry reconciliation¶

For the reusable-UDF registry the connector probes which routines already exist so it can skip redundant DDL, and drops UDFs that are no longer referenced:

SELECT routine_name, specific_name FROM `{catalog}`.`information_schema`.`routines` WHERE ...;
DROP FUNCTION IF EXISTS {function_fqdn};

The probe runs on a short-lived connection so a long reconcile does not hold a pooled connection open.

Principals — no warehouse queries¶

User, group, service-principal, and role synchronization is done entirely through the Databricks SCIM APIs. These calls do not run on the SQL warehouse and do not contribute to warehouse cost, but they are subject to Databricks API rate limits.

Throttling controls: dbx.throttle.api.calls, dbx.api.consecutive.group.user.update.delay.interval.ms (default 100), dbx.retry.delay.min.seconds, and dbx.group.id.cache.timeout.minutes (default 15). See Prevent DBX API throttling.

API mode endpoints¶

When use.database.connection.api=true, resource and permission traffic moves off the warehouse and onto the Unity Catalog REST API:

API mode removes warehouse cost but is subject to per-second rate limits and is slower for bulk operations. It is not recommended for production — see JDBC vs API.

Audit collection¶

Audit collection is disabled by default (enable.audit=false). When enabled, the connector queries the Databricks system audit table every audit.interval.sec (default 4 hours):

SELECT * FROM system.access.audit
WHERE {workspace_filter}
  AND event_date IN ({date_list})
  AND event_time >= '{from_event_time}' AND event_time <= '{to_event_time}'
  AND service_name IN ('unityCatalog', 'clean-room')
  AND action_name IN ('createCatalog', 'createSchema', 'createTable', 'updatePermissions', ...);

Three modes are available:

• simple — Unity Catalog control-plane actions only (the default filter shown above).
• verbose — the simple filter plus commandSubmit and runCommand, which is substantially heavier because it scans data-plane command events.
• dynamic — a caller-supplied <condition> predicate.

Each mode also has an external-* variant that reads a customer-provided audit table instead of system.access.audit.

On-demand events¶

On-demand sync (Event Hub or REST-triggered) runs the same query templates as the scheduled loops, but with the resource-scoped sql_specific / sql_schema variants instead of the full-catalog ones. For an event naming catalog.schema.table, the connector issues roughly:

1. The scoped resource queries — the table lookup is narrowed to the named schema and tables, and the matching column query runs for that schema only:

   SQL

   select table_name as tableName, table_schema as schemaName, table_type as tableType,
          table_owner as owner, created
     from `{catalog}`.`information_schema`.`tables`
    where table_schema not in ('information_schema')
      and table_schema = '{schema}' and table_name in ({table})
    order by table_schema;
   

2. A SHOW GRANTS statement for each resource resolved by the event.

3. Any grant/revoke, masking, row-filter, or secure-view statements required to reconcile the resolved resources.

Two settings control how much an event pulls in:

• ondemand.skip.child.load.enabled (default false). When true, only the resources named in the event are loaded; unrequested children are skipped and already-stored children are retained. Use catalog.schema.* to refresh all tables under a schema. See Skip Child Resource Loading.
• load.resources.load.table.thread.min.count / .count (default 2). For foreign catalogs, each schema batch holds one worker until its per-table describe pool finishes, so this also caps how many foreign schemas are described concurrently in a single event.

Because on-demand events are scoped, their footprint is small relative to a full scheduled sync — a single-table event is a handful of statements. High event rates against foreign catalogs are the exception, since each one fans out into per-table describe table extended calls.

For configuration, see Event-Driven On-Demand Sync and API-Driven On-Demand Sync.

Summary — what runs, and when¶

Reducing the query footprint¶

• Restrict what is managed with the ignore lists and Manage Resources List.
• Turn off resource types you do not govern with the load.<type>.enable flags — Selective Resource Type Loading.
• Set load.columns=false if you do not use column-level policies; this removes the per-schema column query.
• Keep foreign catalogs out of the scheduled scan with scheduled.load.foreign.resources=false and refresh them on demand instead.
• Increase sync.interval.sec if 10-minute resource discovery is more frequent than you need, and align the warehouse auto-stop with it.
• Prefer reusable master-UDF RLS over per-policy UDFs on large policy sets.
• Leave enable.audit=false unless Unity Catalog audit collection through the connector is required, and prefer simple over verbose.