Oracle Ingestion Creates Out-of-Filter Datasets Due to Unfiltered Usage Source and URN Mismatch – DataHub Customer Support Portal

Area: Ingestion Issues

Sub-Area: Oracle Connector — Usage Ingestion Filtering and URN Resolution

Issue

When using the Oracle ingestion source with schema_pattern, table_pattern, or view_pattern filters alongside include_query_usage: true, DataHub may create dataset entities for tables that fall outside the configured filter scope. These "orphaned" datasets appear as separate browse-path groups in the UI, have usage metadata attached, but lack a container aspect — meaning they are not nested under their expected database or schema container. This occurs because of two distinct root causes in the V$SQL usage path: (1) the SQL aggregator did not apply the user-defined schema and table filters, so usage, operations, and query subjects were emitted for every table referenced in query history; and (2) a URN mismatch caused by the add_database_name_to_urn setting meant that usage was recorded against database.schema.table URNs while the structural crawl emitted schema.table URNs, causing DataHub to treat them as entirely separate datasets. Both issues are resolved in CLI version 1.7.0.18.

Error Messages

Dataset has usage metadata but no container aspect
Orphaned datasets appear in browse path outside expected database container

You Might Be Asking

Why are datasets appearing in DataHub that are outside my configured schema_pattern and table_pattern filters?
Why do some Oracle datasets show usage statistics but have no container or database relationship?
Why are there duplicate browse-path groups (e.g., two folders with the same schema name) under the Oracle platform in DataHub?
Why does enabling include_query_usage cause extra datasets to appear that my filters should exclude?
How do I remove orphaned Oracle datasets created by earlier ingestion runs?

Solution

Understand the root causes.
The Oracle connector reads SQL statements from Oracle's V$SQL dynamic performance view, which is instance-wide. Prior to CLI 1.7.0.18, two bugs existed in this path:
- Missing filter application: The SQL aggregator built from V$SQL query history did not apply schema_pattern, table_pattern, or view_pattern. Every table referenced in any recent statement received usage, operation, and query metadata — including tables explicitly excluded by your recipe filters. These datasets were created without a container aspect because the structural crawl (which does respect filters) never ingested them.
- URN mismatch with add_database_name_to_urn: With the default setting add_database_name_to_urn: false, the structural crawl emitted dataset URNs of the form schema.table. However, the usage path resolved tables to database.schema.table, which DataHub treats as a different URN — and therefore a different dataset. This produced a second, container-less set of datasets appearing as a separate browse-path group named after the database.
Upgrade to CLI version 1.7.0.18 or later.
Both root causes are fixed in CLI 1.7.0.18. After upgrading:
- Usage, operations, and query metadata from V$SQL now respect your recipe's schema_pattern, table_pattern, and view_pattern. Tables excluded by your filters no longer have datasets created from query history.
- Filtering is applied per table, not per statement. If a SQL statement references both an in-scope and an out-of-scope table, the in-scope table still receives its usage. This avoids the downside of query_exclude_patterns where dropping an entire statement removes usage for all referenced tables.
- Usage is now recorded against the same URNs produced by the structural crawl, so usage data lands on the correct datasets inside their database containers.
To manually specify the CLI version in a DataHub Cloud managed ingestion recipe, open the Advanced section at the bottom of the ingestion configuration and set the CLI version field to 1.7.0.18.

For self-hosted or CLI-based ingestion, install the specific version:
```
pip install acryl-datahub==1.7.0.18
```
(Optional interim workaround — pre-1.7.0.18 only) Use query_exclude_patterns.
If you cannot upgrade immediately, you can add regex patterns to suppress statements referencing schemas or table name patterns you want excluded. Note that this drops the entire statement — all tables referenced in that statement lose the associated usage entry.
```
source:
  type: oracle
  config:
    # ... other config ...
    query_exclude_patterns:
      - 'FROM\s+<excluded_schema>\.'
      - 'JOIN\s+<excluded_schema>\.'
```
Replace <excluded_schema> with the schema name or table name pattern you want to suppress. This is a coarse workaround; upgrading to 1.7.0.18 is the recommended resolution.
Clean up orphaned datasets created by earlier ingestion runs.
Upgrading the CLI and re-running ingestion will prevent new orphaned datasets from being created, but datasets already present in DataHub from previous runs are not removed automatically. Use the DataHub CLI to soft-delete them. Soft-deletion hides assets from the UI and search immediately and schedules them for permanent deletion after 30 days. Any asset incorrectly soft-deleted will be re-ingested on the next successful run with all metadata intact.

Always perform a dry run first to review what will be deleted before committing:
```
# Dry run — review output before deleting
datahub delete \
  --platform oracle \
  --entity-type dataset \
  --query '<orphaned-browse-group-name>.*' \
  --dry-run
```
Once you have reviewed the dry-run output and confirmed the correct assets are targeted, remove the --dry-run flag to execute the soft-deletion:
```
# Execute soft-deletion
datahub delete \
  --platform oracle \
  --entity-type dataset \
  --query '<orphaned-browse-group-name>.*'
```
Replace <orphaned-browse-group-name> with the name of the separate browse-path group you want to remove (e.g., the database-named group that appeared due to the URN mismatch). Repeat for each orphaned group as needed.

For more options and flags, refer to the DataHub delete documentation linked below.

Additional Notes

Both root causes described here — the missing filter application in the V$SQL usage path and the URN mismatch caused by add_database_name_to_urn defaulting to false — were confirmed bugs and are fully resolved in CLI version 1.7.0.18. The fix applies per-table filtering rather than per-statement filtering, which preserves usage on in-scope tables even when a statement also references excluded tables. This is an improvement over using query_exclude_patterns as a workaround. The datahub delete command defaults to soft-deletion; assets soft-deleted in error will be restored on the next ingestion run. Review the changelog entry in docs/how/updating-datahub.md under "Next" for details on the URN change and cleanup guidance for prior runs. Temporary or staging tables with auto-generated numeric suffixes (commonly created by ETL tools) are particularly likely to generate orphaned datasets via V$SQL, since those tables may never be crawled by the structural path.

Issue

Error Messages

You Might Be Asking

Solution

Additional Notes

Related Documentation