Redshift Ingestion: Correctly Formatting Deny Patterns for Fully Qualified Table Names – DataHub Customer Support Portal

Area: Ingestion Issues

Sub-Area: Redshift Source Configuration — Table Pattern Filtering

Issue

When configuring table_pattern deny entries in a Redshift ingestion recipe, users sometimes write patterns that match only the bare table name (e.g., ^my_table$). Because the Redshift source matches table_pattern against the fully qualified name in the form database.schema.table, a pattern that omits the database and schema prefixes will never match any table. The result is that the denied tables continue to be ingested, orphaned asset counts do not decrease as expected, or they increase when lineage and usage processing re-creates previously excluded datasets as bare entities.

Error Messages

No explicit error is raised — tables that should be denied continue to appear in DataHub after ingestion.

You Might Be Asking

Why are my Redshift deny patterns not filtering out the tables I specified?
Why did my orphaned asset count increase after I added more deny patterns?
How should I write table_pattern deny entries for Redshift ingestion?
Do Redshift table patterns need to include the database and schema name?

Solution

Understand what the pattern is matched against. In the Redshift ingestion source, table_pattern (both allow and deny lists) is evaluated against the fully qualified table name:
```
database.schema.table
```
The match is applied from the beginning of this fully qualified string, not just against the table name segment.
Identify incorrectly formatted deny patterns. Patterns written in any of the following forms will not match because the string being tested starts with the database name, not the table name:
```
# INCORRECT — these patterns will never match a fully qualified name
deny:
  - '^my_table$'
  - '^mv_tbl__.*__0$'
  - '^europa_demo$'
  - '^table_staleness_check$'
```

Rewrite deny patterns to include the fully qualified prefix. Use a leading .*\. wildcard to match any database and schema prefix, followed by the specific table name pattern:

# CORRECT — patterns that match the fully qualified database.schema.table name
deny:
  # Match any table named exactly 'my_table' in any database and schema
  - '.*\.my_table$'

  # Match any table whose name starts with 'mv_tbl__' and ends with '__0'
  # in any database and schema
  - '.*\.mv_tbl__.*__0$'

  # Match tables ending with '_swap' in any database and schema
  - '.*\._swap$'

  # Match any table containing 'mytemptable' in its name
  - '.*mytemptable.*'

  # Match a specific table in a specific database and schema
  - '^my_database\.my_schema\.europa_demo$'

Anchor patterns appropriately. Use ^ and $ anchors carefully:
- Using ^ at the start of a pattern requires the match to begin at the database name. If you anchor with ^, you must include the full database\.schema\.table path.
- Using a leading .*\. (without ^) is a convenient way to match a table name pattern across any database and schema.
```
# Anchored to a specific database and schema
deny:
  - '^prod_db\.staging_schema\.temp_.*$'

# Matches the table name pattern across ALL databases and schemas
deny:
  - '.*\.temp_.*$'
```
Verify patterns against actual asset names. In the DataHub UI, open a few of the assets that should have been filtered out and note their exact fully qualified names (database, schema, table). Then confirm that your deny pattern regex matches that full string. You can test regex patterns quickly with an online tool before updating your recipe.
Ensure stateful ingestion is enabled so that tables excluded by a deny pattern are removed from DataHub on the next run. Without stateful ingestion, previously ingested tables remain even after they are excluded from new runs.
```
source:
  type: redshift
  config:
    # ... other config ...
    stateful_ingestion:
      enabled: true
      remove_stale_metadata: true

pipeline_name: my_redshift_pipeline  # Must remain consistent across runs
```
Important: Do not change pipeline_name between runs. Changing it resets the ingestion state, which prevents stale entity removal from working correctly.

Clean up existing orphaned assets if tables that should have been excluded are already present in DataHub. Use the DataHub CLI to soft-delete them (always perform a dry run first):

# Dry run — review what would be deleted before committing
datahub delete --platform redshift --env PROD --query "<filter>" --soft --dry-run

# Execute the soft delete after confirming the dry run output
datahub delete --platform redshift --env PROD --query "<filter>" --soft

Additional Notes

The table_pattern filter is also applied to lineage targets in the Redshift lineage processing code. If a deny pattern does not correctly match the fully qualified name, the excluded tables may be re-created as bare datasets (with no schema or container metadata) during lineage, usage, or query ingestion. This can cause the orphaned asset count to increase rather than decrease, which is a common symptom of incorrectly formatted patterns. Additionally, if schema_pattern is configured alongside table_pattern, review the match_fully_qualified_names setting in your recipe, as this affects how patterns are evaluated. All pattern matching is case-insensitive by default.

Issue

Error Messages

You Might Be Asking

Solution

Additional Notes

Related Documentation