Snowflake Ingestion: Missing Schemas Due to Insufficient Role Grants (Secondary Roles Not Used) – DataHub Customer Support Portal

Area: Ingestion Issues

Sub-Area: Snowflake Connector — Permissions and Schema Discovery

Issue

When running DataHub's Snowflake ingestion connector, one or more schemas and their associated tables may be completely absent from the DataHub catalog — no schema containers, no datasets — even though the ingestion execution completes successfully with no reported errors, the affected schemas are not excluded by any schema_pattern.deny or table_pattern.deny rule, and the schemas appear accessible when validated interactively in Snowflake using the configured ingestion role. The root cause is typically a discrepancy between interactive Snowflake sessions (which may activate secondary roles and thereby inherit additional privileges) and the ingestion service account session (which uses only the explicitly configured primary role and never issues USE SECONDARY ROLES ALL). If the ingestion role lacks a direct USAGE grant on a schema, that schema is simply not returned by Snowflake's INFORMATION_SCHEMA.SCHEMATA metadata query and is silently skipped during enumeration.

Error Messages

No tables/views/semantic views/streams found in schema. If objects exist, please grant REFERENCES or SELECT permissions on them.

You Might Be Asking

Why are some Snowflake schemas missing from DataHub even though ingestion completes successfully and no patterns exclude them?
Why can I see the schemas when I query Snowflake interactively with the ingestion role, but DataHub still does not discover them?
How do I know whether a schema was skipped during enumeration or discovered but treated as empty?
Does the DataHub Snowflake connector use secondary roles?
What Snowflake privileges does the ingestion role need to discover schemas and tables?
Will running a targeted ingestion with stateful_ingestion.enabled: false interfere with my main production ingestion checkpoint?

Solution

Step 1 — Understand How the Connector Enumerates Schemas

The DataHub Snowflake connector builds its schema list by executing the following query against each in-scope database:

SELECT schema_name
FROM "<YOUR_DATABASE>".information_schema.schemata
WHERE schema_name != 'INFORMATION_SCHEMA';

Snowflake only returns rows from INFORMATION_SCHEMA.SCHEMATA for schemas on which the active session role has USAGE. The connector sets the role explicitly (via the role recipe parameter) and never issues USE SECONDARY ROLES ALL. Therefore, any privilege that your ingestion role inherits only through secondary roles in an interactive session will not be available during ingestion. Schemas invisible to the query are silently omitted — no warning, no error, and no container is created in DataHub.

Step 2 — Distinguish "Skipped at Enumeration" from "Discovered but Empty"

These two failure modes have different causes and require different fixes. Use the following test to tell them apart:

Schema container exists in DataHub (even with zero datasets): The schema was discovered by the schemata query but no tables were visible. This indicates a table-level privilege gap (REFERENCES or SELECT missing). The connector logs an INFO-level message: No tables/views/semantic views/streams found in schema.
No schema container exists in DataHub (not even soft-deleted): The schema was never returned by the schemata query. This confirms a schema-level USAGE gap — the focus of this article.

You can also check ACCOUNT_USAGE.QUERY_HISTORY in Snowflake to see the exact rows returned by the schemata query during the ingestion execution window, and ACCOUNT_USAGE.GRANTS_TO_ROLES to review when grants were applied to the ingestion role.

Step 3 — Reproduce the Connector's Exact Session Context

Interactive validation using your ingestion role from an admin login may silently activate secondary roles. To reproduce what the connector actually sees, run the diagnostic query as the ingestion service account with secondary roles explicitly disabled:

-- Connect as (or simulate) the ingestion service user
-- Ensure secondary roles are NOT active

USE ROLE <YOUR_INGESTION_ROLE>;
ALTER SESSION SET USE_SECONDARY_ROLES = NONE;

SELECT schema_name, created
FROM "<YOUR_DATABASE>".information_schema.schemata
WHERE schema_name != 'INFORMATION_SCHEMA'
ORDER BY schema_name;

Compare the row count from this query to what you see when secondary roles are enabled. Any schemas that appear only with secondary roles active are missing because the ingestion role lacks a direct USAGE grant.

Also confirm table-level visibility for any affected schema:

SHOW TABLES IN SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>";

If this returns zero rows when run without secondary roles, the ingestion role also lacks REFERENCES or SELECT on the tables within that schema.

Step 4 — Grant the Required Privileges to the Ingestion Role

At minimum, the ingestion role requires the following privileges for every schema it must discover:

USAGE on the database
USAGE on each schema
REFERENCES or SELECT on each table (required for column-level metadata extraction)

Apply the missing grants:

-- Grant schema-level visibility
GRANT USAGE ON SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>" TO ROLE <YOUR_INGESTION_ROLE>;

-- Grant table-level metadata access (repeat for each schema or use FUTURE GRANTS)
GRANT REFERENCES ON ALL TABLES IN SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>" TO ROLE <YOUR_INGESTION_ROLE>;

-- Optionally, apply future grants so new tables are automatically covered
GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>" TO ROLE <YOUR_INGESTION_ROLE>;

After applying grants, re-run the Step 3 diagnostic as the service account (with secondary roles disabled) to confirm all target schemas are now returned.

Step 5 — Re-run Ingestion to Pick Up the Missing Schemas

Once grants are confirmed, the simplest remediation is to trigger a manual run of your existing production ingestion source. The connector will re-enumerate schemas and discover the previously missing objects without any recipe changes.

If you need to ingest the missing schemas immediately without waiting for the next scheduled run, or if you want to avoid any risk to the main production checkpoint, you can run a separate targeted ingestion source scoped to only the affected database and schemas:

source:
  type: snowflake
  config:
    account_id: "<your-snowflake-account>"
    username: "<your-ingestion-service-user>"
    role: "<your-ingestion-role>"
    warehouse: "<your-warehouse>"

    database_pattern:
      allow:
        - "^<YOUR_DATABASE>$"

    schema_pattern:
      allow:
        - "^<YOUR_DATABASE>\\.<YOUR_MISSING_SCHEMA_1>$"
        - "^<YOUR_DATABASE>\\.<YOUR_MISSING_SCHEMA_2>$"

    match_fully_qualified_names: true
    include_tables: true
    include_views: true

    stateful_ingestion:
      enabled: false

pipeline_name: "snowflake-targeted-remediation-<unique-suffix>"

sink:
  type: datahub-rest
  config:
    server: "https://<your-instance>.acryl.io/api/gms"

Key points for the targeted recipe:

Set a unique pipeline_name that differs from your main production source. Checkpoint state is scoped to pipeline_name; using a unique name ensures the targeted run has no effect on the main source's checkpoint.
With stateful_ingestion.enabled: false, the targeted run does not read or write any checkpoint and does not soft-delete any existing metadata — it is purely additive.
Leave convert_urns_to_lowercase, platform_instance, and env unset (or match them exactly to your main recipe) so the targeted run writes URNs identical to those the main source would produce — no duplicate entities will be created.

Step 6 — Verify and Monitor

Confirm that schema containers and all expected datasets appear in DataHub after the remediation run.
Verify the next scheduled production ingestion run also discovers the schemas correctly, confirming that the grant fix is durable.
If your environment creates schemas dynamically (e.g., schemas with auto-generated or date-stamped names), implement a Snowflake FUTURE GRANTS policy so new schemas are automatically covered by the ingestion role without requiring manual intervention for each new schema.

Additional Notes

Secondary roles are never activated by the connector. This is by design. The DataHub Snowflake connector does not issue USE SECONDARY ROLES ALL at any point in the session. All metadata queries run under the single primary role specified in the recipe's role parameter. Interactive Snowflake sessions often activate secondary roles automatically depending on user or account settings, which can cause interactive tests to succeed while ingestion silently misses objects. Always validate ingestion-role privileges by explicitly disabling secondary roles in your test session, as shown in Step 3.

Schema name length is not a factor. The connector applies no name length limits or truncation during schema discovery. Long or dynamically generated schema names (up to Snowflake's 251-character identifier limit) are handled correctly.

Stateful ingestion and soft-deletions are independent of this issue. Soft-deleted datasets reported in a stateful ingestion run reflect objects that were previously in DataHub but were not seen in the current run — not objects that were never discovered in the first place. Missing schemas that were never ingested will not appear in soft-deletion counts.

convert_urns_to_lowercase defaults to true. If this key is omitted from your recipe, URNs are lowercased. Ensure any targeted or remediation recipe matches the convert_urns_to_lowercase setting of your main production recipe to avoid creating duplicate entities with differing URN casing.

Connector version note: The behavior described here has been confirmed on DataHub CLI v1.7.0.x. The schema enumeration query and secondary-roles behavior are consistent across recent connector versions.