> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getcollate.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Metadata Ingestion - Incremental Extraction - Unity Catalog

> Configure incremental metadata extraction for Unity Catalog to sync only changed or deleted tables, reducing ingestion load.

# Metadata Ingestion - Incremental Extraction - Unity Catalog

## Approach

To implement incremental extraction for Unity Catalog, Collate queries two system tables at the start of incremental processing for each catalog, using a watermark derived from the previous successful run timestamp:

* **Changed tables**: `information_schema.tables` is filtered by `last_altered` to find tables modified since the last successful run. Each changed table is then fetched individually via the Databricks software development kit (SDK).
* **Deleted tables**: `system.access.audit` is queried for `deleteTable` events scoped to the configured catalog. If the `system.access` schema is unavailable, delete detection is skipped with a warning and only changed-table detection runs.

A table that appears in both the changed and deleted sets is kept, not deleted. This covers tables that were dropped and recreated within the window, since `information_schema` only lists currently existing tables.

<Note>
  Incremental extraction only runs for catalogs whose name matches `[A-Za-z0-9_]+`. Catalogs with names outside this pattern (for example, quoted names like `cat-a-log`) are skipped with a warning; no changed or deleted tables are returned and no exception is raised.
</Note>

## Requisites

Before enabling incremental extraction, review the following visibility and permission requirements for the Unity Catalog system tables used at runtime.

### Changed table detection

No explicit grant is required for `information_schema.tables` – Databricks automatically scopes its results to objects the querying principal can already see through existing Unity Catalog privileges. Ensure the ingestion service principal has standard read privileges (`USE CATALOG`, `USE SCHEMA`, `SELECT`) on the tables you want incrementally tracked.

### Deleted table detection (optional)

To detect dropped tables, you also need access to `system.access.audit`:

```sql theme={null}
GRANT USE CATALOG ON CATALOG system TO `<user>`;
GRANT USE SCHEMA ON SCHEMA system.access TO `<user>`;
GRANT SELECT ON system.access.audit TO `<user>`;
```

<Note>
  If these grants are absent, delete detection is skipped automatically and a warning is logged.
</Note>

## Used Queries

### Changed tables

Collate filters `information_schema.tables` by the `last_altered` column to identify tables that were created or modified within the ingestion window.

```sql theme={null}
SELECT
    table_schema,
    table_name
FROM `{catalog}`.information_schema.tables
WHERE last_altered >= timestamp_millis({start_timestamp})
```

### Deleted tables

Collate queries the Unity Catalog audit log for `deleteTable` events to identify tables that were dropped within the ingestion window.

```sql theme={null}
SELECT DISTINCT request_params.full_name_arg AS table_full_name
FROM system.access.audit
WHERE service_name = 'unityCatalog'
    AND action_name = 'deleteTable'
    AND event_date >= date(timestamp_millis({start_timestamp}))
    AND event_time >= timestamp_millis({start_timestamp})
    AND substring_index(request_params.full_name_arg, '.', 1) = '{catalog}'
```