Snowflake Ingestion Performance Optimization: Reducing Ingestion Time with Recipe Configuration – DataHub Customer Support Portal

```html

Area: Ingestion Issues

Sub-Area: Snowflake Connector Performance Tuning

Issue

Snowflake ingestion in DataHub can take significantly longer than other connectors (such as AWS Glue or Teradata) — even when targeting a single dataset without lineage. Ingestion runs of 8 minutes or more for a single table are commonly reported. This is caused by several default connector behaviors: auto-detection of the Snowflake edition via a full-account SHOW TAGS scan, fetching metadata for all databases before applying Python-side filters, and initializing subsystems (such as usage and lineage pipelines) that are unnecessary for basic dataset creation. By adjusting the recipe configuration, ingestion time for a single dataset can typically be reduced from 8+ minutes to under 60 seconds.

Error Messages

Extra inputs are not permitted [type=extra_forbidden] — exclude_external_tables

You Might Be Asking

Why does Snowflake ingestion take so much longer than Glue or Teradata ingestion, even for a single table?
How can I speed up Snowflake ingestion when I only need to create a single physical dataset?
What does push_down_metadata_patterns do and when should I enable it?
Why is SHOW TAGS running at the start of every ingestion and how do I skip it?
Why is there a query to SNOWFLAKE.ACCOUNT_USAGE.USERS even when lineage is disabled?

Solution

Apply the following optimizations to your Snowflake ingestion recipe. Each setting targets a specific source of latency. Start with the high-impact settings and add additional ones based on your use case.

Step 1: Understand the Root Causes

By default, the DataHub Snowflake connector performs several expensive account-wide operations regardless of how narrow your target scope is:

SHOW TAGS: Runs at startup to auto-detect whether your Snowflake account is Standard or Enterprise edition. On large accounts, this query can take 2+ minutes.
SHOW DATABASES: Lists every database in the account. On accounts with hundreds of databases, this adds significant latency.
Python-side filtering: By default (push_down_metadata_patterns: false), all database/schema/table pattern matching happens in Python after fetching metadata for every object. The connector fetches everything and discards what it does not need.
ACCOUNT_USAGE.USERS query: Triggered by the use_queries_v2 pipeline even when lineage is not enabled. This query can return thousands of rows and add 5+ seconds of latency.
Primary and foreign key scans: SHOW PRIMARY KEYS and SHOW IMPORTED KEYS run by default and add several hundred milliseconds each.

Step 2: Apply High-Impact Recipe Settings

Add the following to the source.config section of your Snowflake recipe:

source:
  type: snowflake
  config:
    # --- Connection settings (fill in your values) ---
    account_id: "<your-snowflake-account-id>"
    username: "<your-username>"
    password: "<your-password>"
    warehouse: "<your-warehouse>"
    role: "<your-role>"

    # --- Performance optimizations ---

    # Skip SHOW TAGS by declaring your Snowflake edition explicitly.
    # Eliminates 2+ minute startup scan on large accounts.
    # Valid values: "STANDARD" or "ENTERPRISE" (or "Enterprise or above")
    known_snowflake_edition: ENTERPRISE

    # Push database/schema/table pattern filters into Snowflake SQL queries
    # instead of fetching all metadata and filtering in Python.
    # NOTE: Requires allow patterns to use Snowflake RLIKE syntax (full-string match).
    # Ensure patterns are anchored (e.g., use ".*" or "$") as needed.
    push_down_metadata_patterns: true

    # Limit metadata extraction to standard base tables only.
    # Avoids scanning external tables and dynamic tables.
    table_types:
      - BASE TABLE

    # Disable dynamic table scanning (not needed for standard table ingestion).
    exclude_dynamic_tables: true

    # Disable the use_queries_v2 pipeline to eliminate the
    # SNOWFLAKE.ACCOUNT_USAGE.USERS query (~5.5 seconds on large accounts).
    use_queries_v2: false

    # Disable primary and foreign key scanning (saves ~475ms combined).
    include_primary_keys: false
    include_foreign_keys: false

    # Disable features not needed for basic dataset creation.
    include_table_lineage: false
    include_view_lineage: false
    include_usage_stats: false
    include_view_definitions: false
    include_streams: false
    include_procedures: false

    # Remove deprecated settings — these are no longer valid and generate warnings.
    # Do NOT include the following lines in your recipe:
    #   include_view_column_lineage: false   # Removed December 2024
    #   include_view_lineage: false          # Removed December 2024

    # --- Pattern filters (example — replace with your target objects) ---
    match_fully_qualified_names: true
    database_pattern:
      allow:
        - "<YOUR_DATABASE>$"
    schema_pattern:
      allow:
        - "<YOUR_DATABASE>.<YOUR_SCHEMA>$"
    table_pattern:
      allow:
        - "<YOUR_DATABASE>.<YOUR_SCHEMA>.<YOUR_TABLE>$"

Step 3: Verify Pattern Syntax When Using push_down_metadata_patterns

When push_down_metadata_patterns: true is set, pattern filters are pushed into Snowflake SQL using the RLIKE operator, which performs full-string matching. Python's re.match() performs partial matching, so patterns that work without push-down may behave differently with it enabled.

Ensure allow patterns are anchored where needed — for example, use a trailing $ to match the end of the string.
Ensure match_fully_qualified_names: true is set so that patterns apply to fully qualified object names (e.g., DATABASE.SCHEMA.TABLE).
Test your patterns against a small known set of objects before running full production ingestion.

Step 4: Note on exclude_external_tables

The exclude_external_tables config key is not a valid field in SnowflakeV2Config and will cause a Pydantic validation error if included. Use table_types: ['BASE TABLE'] instead to restrict ingestion to standard tables and avoid scanning external or dynamic table types.

Step 5: Expected Performance After Optimization

Eliminating SHOW TAGS via known_snowflake_edition removes 2+ minutes of startup latency.
Enabling push_down_metadata_patterns reduces the number of objects fetched from Snowflake information schema views dramatically.
Disabling use_queries_v2 eliminates the ACCOUNT_USAGE.USERS query.
Disabling primary/foreign keys and unused features removes several additional queries.
With all optimizations applied, single-dataset ingestion typically completes in under 60 seconds, and often under 30 seconds.

Step 6: Known Remaining Latency (Architectural Limitation)

Even after all the above optimizations, a baseline of unavoidable latency remains:

SHOW DATABASES: Still runs once and lists all databases in the account (no config option to skip this). On accounts with hundreds of databases, this can take 30–60 seconds on its own.
Session setup queries (CURRENT_VERSION, CURRENT_ROLE, CURRENT_WAREHOUSE, etc.): Run at startup and cannot be disabled. Typically take about 1 second total.
Snowflake information schema latency: Some Snowflake system views (e.g., ACCOUNT_USAGE views) have inherent latency of up to 45 minutes to 3 hours. This is a Snowflake platform constraint and not specific to DataHub.

The Snowflake connector is architecturally different from connectors like AWS Glue (which accepts targeted API parameters like DatabaseName and TableName) or Teradata (which queries system tables with WHERE predicates). Snowflake metadata is stored in account-wide system views, so the connector enumerates broadly by design.

Additional Notes

The exclude_external_tables field does not exist in SnowflakeV2Config and will cause a Pydantic extra_forbidden validation error if included in your recipe — use table_types: ['BASE TABLE'] as the correct replacement. The deprecated settings include_view_column_lineage and include_view_lineage were removed in December 2024; remove them from any existing recipes to eliminate warnings. If you observe very long ingestion times after enabling include_views: false, this may trigger a separate issue involving the schema_resolver_provider loading all schema info — treat this as a separate problem and refer to DataHub support. The push_down_metadata_patterns and known_snowflake_edition configuration options have been available for several DataHub versions and are the primary documented performance levers for the Snowflake v2 connector. These optimizations apply to self-hosted (on-premise) and cloud deployments of DataHub.