Fixing Snowflake Lineage Gaps When Staging Tables Are Denied and OpenLineage Naming Is Malformed – DataHub Customer Support Portal

Area: Ingestion Issues

Sub-Area: Snowflake Lineage / OpenLineage Dataset Identity

Issue

A Snowflake dataset may show far fewer downstream (or upstream) lineage edges than expected — for example, 8 instead of 50–100 — when two compounding problems are present simultaneously. First, staging or buffer tables (such as __amend_*, __merge_*, or __rebuild_*) are excluded from the Snowflake catalog via table_pattern deny rules, which also prevents the connector from tracing lineage through those tables to real sources or consumers. Second, even when OpenLineage is enabled on the orchestrator side to compensate, the emitted dataset names use a slash-delimited format (e.g., schema/table with no database qualifier) instead of the dot-delimited fully-qualified format (DATABASE.SCHEMA.TABLE) that DataHub's Snowflake connector uses. As a result, DataHub creates disconnected stub entities for each OpenLineage-named dataset rather than linking job outputs to existing catalog entries, and the downstream lineage chain never forms.

Error Messages

No lineage record found for dataset URN urn:li:dataset:(urn:li:dataPlatform:snowflake,DATABASE.SCHEMA.TABLE,PROD)
OpenLineage dataset name "schema/table" does not match any existing catalog entity

You Might Be Asking

Why does my gold table show only a handful of downstream consumers when I know dozens of pipelines read from it?
I already denied staging tables in my Snowflake recipe — why are they still appearing in DataHub?
I enabled OpenLineage on my Airflow operators but lineage still isn't improving. What's wrong?
Will temporary_tables_pattern fix lineage when staging and merge steps run in separate Airflow connections?
Why does DataHub show slash-named Snowflake datasets that don't correspond to any real table?

Solution

Understand the two root causes independently.
- Cause A — Denied staging tables break lineage tracing: When your pipeline writes to a transient buffer table (e.g., SCHEMA.__amend_table_name_<id>) and then merges it into a final table, the Snowflake connector normally uses temporary_tables_pattern to trace through the buffer to real sources. However, if those buffer tables are also matched by a table_pattern deny rule, the connector classifies them as neither real tables nor temporary ones, discards their lineage, and the chain breaks.
- Cause B — Cross-session staging breaks same-session stitching: The connector's temp-table stitching is keyed on the Snowflake session_id from query_history. If your orchestrator (e.g., Airflow) opens a separate database connection for each statement via separate hook.run() calls, the staging write and the MERGE land in different sessions. temporary_tables_pattern alone cannot stitch lineage across sessions regardless of the table name pattern.
- Cause C — OpenLineage dataset names don't match Snowflake catalog URNs: DataHub resolves lineage by matching dataset URNs. The Snowflake connector produces URNs based on DATABASE.SCHEMA.TABLE (dot-delimited) under the snowflake://<account> namespace. If your OpenLineage emitter names output datasets as schema/table (slash-delimited, no database), DataHub creates a separate stub entity and the job output never attaches to the real catalog entry.

Fix Cause A: Add staging patterns to temporary_tables_pattern alongside your deny rules.

This setting coexists with table_pattern deny rules — staging tables can be excluded from the catalog while still being resolved for lineage. Note that this setting replaces the built-in defaults, so you must include all five default patterns plus your custom ones.

source:
  type: snowflake
  config:
    # ... other config ...
    table_pattern:
      deny:
        - "(?i).*__amend_.*"
        - "(?i).*__rebuild_.*"
        - "(?i).*__merge_.*"
        # add any other staging patterns specific to your environment

    temporary_tables_pattern:
      # Built-in defaults — keep all five
      - ".*\\.FIVETRAN_.*_STAGING\\..*"
      - ".*__DBT_TMP$"
      - ".*\\.SEGMENT_[a-f0-9]{8}[-_][a-f0-9]{4}[-_][a-f0-9]{4}[-_][a-f0-9]{4}[-_][a-f0-9]{12}"
      - ".*\\.STAGING_.*_[a-f0-9]{8}[-_][a-f0-9]{4}[-_][a-f0-9]{4}[-_][a-f0-9]{4}[-_][a-f0-9]{12}"
      - ".*\\.(GE_TMP_|GE_TEMP_|GX_TEMP_)[0-9A-F]{8}"
      # Custom patterns matching your staging table naming conventions
      - "(?i).*__amend_.*"
      - "(?i).*__rebuild_.*"
      - "(?i).*__merge_.*"
      - "(?i).*__pivot_.*"
      - "(?i).*__snapshot_.*"

Important: This fix only works when the staging write and MERGE run within the same Snowflake session. If your orchestrator opens a new connection per statement (see Cause B), proceed to the next step.

Fix Cause B (cross-session staging): Use OpenLineage for job-mediated lineage.
When your staging and merge steps execute in separate sessions, the Snowflake connector cannot stitch them. OpenLineage is the correct instrument because it records lineage at the Airflow task level and is session-agnostic. Enable OpenLineage on your Airflow operators and ensure events are forwarded to your DataHub instance. Once wired correctly, consumers will appear as: source_table → Airflow Job → destination_table (two hops). Expanding two hops in the lineage explorer will reveal the full consumer graph.
Fix Cause C: Correct the OpenLineage dataset naming convention.
OpenLineage output dataset names must use dot-delimited fully-qualified identifiers and the standard Snowflake namespace to match URNs created by the Snowflake connector.

Incorrect format (creates stub entities):
```
{
  "namespace": "snowflake://<your-account>",
  "name": "schema_name/table_name"
}
```
Correct format (matches Snowflake connector URNs):
```
{
  "namespace": "snowflake://<your-account>",
  "name": "DATABASE_NAME.SCHEMA_NAME.TABLE_NAME"
}
```
You can verify the exact URN format DataHub expects by inspecting an existing dataset in DataHub and noting its platform URN. Any existing slash-named stub entities will persist until the source that created them is corrected or those entities are manually removed.
Fix staging tables leaking in from a secondary ingestion source (e.g., Databricks federation).
If a second connector (such as a Databricks source federating a Snowflake catalog) ingests the same environment without equivalent deny rules, staging tables excluded by the Snowflake recipe will still appear in DataHub under a different platform identity. Apply the same deny patterns to the secondary recipe:
```
source:
  type: databricks
  config:
    # ... other config ...
    table_pattern:
      deny:
        - "(?i).*__amend_.*"
        - "(?i).*__rebuild_.*"
        - "(?i).*__merge_.*"
        # match the same patterns used in your Snowflake recipe
```
If stateful ingestion is already enabled on the secondary source, DataHub will soft-delete the previously ingested staging entity stubs on the next scheduled run after the deny rules are applied.
Run a historical backfill after applying recipe changes.
After updating your Snowflake recipe, trigger a one-time run with start_time set back up to 90 days (the maximum account_usage.access_history retention window) to reprocess historical query history and rebuild lineage for already-ingested tables:
```
source:
  type: snowflake
  config:
    # ... other config ...
    start_time: "2024-10-01T00:00:00Z"   # adjust to ~90 days before today
    end_time: "2025-01-01T00:00:00Z"     # adjust to current date
```
Note: The ignore_start_time_lineage: true option is only recognized by the legacy query extractor. If your Snowflake source uses the newer query extractor (default in recent versions), this setting is a no-op. Use explicit start_time / end_time values instead.
Verify the fix using the full Lineage Explorer.
After the corrected ingestion run completes, navigate to the dataset in DataHub and open the full lineage explorer (set is_lineage_mode=true in the URL). The embedded lineage panel fetches up to 100 edges; the full explorer fetches up to 1,000. Remember that OpenLineage-mediated consumers appear at the second hop (table → job → table), so expand two degrees to see the complete consumer set.

Additional Notes

Column-level lineage for MERGE statements is a known partial limitation in the Snowflake connector. Table-level lineage for MERGE should resolve correctly once the staging patterns are configured, but column-level lineage may remain incomplete for those operations. Additionally, temporary_tables_pattern classifies staging tables by name pattern — it does not require the Snowflake table type to be TRANSIENT or TEMPORARY. However, cross-session stitching is never supported regardless of pattern configuration. Finally, slash-named OpenLineage stub entities (where dataset names contain / characters) are invalid Snowflake identifiers and will never resolve to real catalog entries; these should be corrected at the emitter level and any accumulated stubs cleaned up separately.

Issue

Error Messages

You Might Be Asking

Solution

Additional Notes

Related Documentation