Snowflake Ingestion - Handling Large View Counts (10,000+ Views)

When ingesting metadata from Snowflake databases that contain more than 10,000 views or tables, the `SHOW TABLES` command fails with an error indicating that the result set exceeds the maximum supported size. This is a Snowflake limitation where SHOW statements are capped at 10,000 rows and cannot be paginated without using the LIMIT clause. This causes ingestion failures and prevents DataHub from capturing metadata from large Snowflake databases, particularly those with extensive view definitions.

Common Error Messages:

• The result set size exceeded the max number of rows(10000) supported for SHOW statements. Use LIMIT option to limit result set to a smaller number.
• Snowflake ingestion errors when running SHOW TABLES command
• Incomplete ingestion from databases with many views

You Might Be Asking:

• Why is my Snowflake ingestion failing?
• How do I ingest from databases with more than 10,000 tables/views?
• What's the alternative to SHOW TABLES for large databases?
• Will this affect all my Snowflake databases?
• How do I know which databases have too many objects?

Primary Solution: Use INFORMATION_SCHEMA

Configure the Snowflake source to query from INFORMATION_SCHEMA instead of using SHOW statements:

Example for Snowflake. This can be adapted for other data warehouse platforms with similar limitations.

source:
  type: snowflake
  config:
    account_id: "YOUR_ACCOUNT"
    warehouse: "YOUR_WAREHOUSE"
    username: "YOUR_USERNAME"
    password: "YOUR_PASSWORD"

    # CRITICAL: Enable this flag to bypass SHOW TABLES limitation
    fetch_views_from_information_schema: true

    # Optional: Also fetch tables from information schema
    fetch_tables_from_information_schema: true

    # Database and schema patterns
    database_pattern:
      allow:
        - "PRODUCTION_DB"

    schema_pattern:
      allow:
        - "PUBLIC"
        - "ANALYTICS"

Understanding the Configuration Flags

• fetch_views_from_information_schema: true - Queries INFORMATION_SCHEMA.VIEWS instead of SHOW VIEWS
• fetch_tables_from_information_schema: true - Queries INFORMATION_SCHEMA.TABLES instead of SHOW TABLES
• These options avoid the 10,000 row limit imposed on SHOW statements

Alternative: Filter Problematic Databases

If you don't need all databases, exclude the large ones:

Example for Snowflake. This can be adapted for other data warehouse platforms.

source:
  type: snowflake
  config:
    # ... other config ...

    database_pattern:
      allow:
        - "PROD_DB"
        - "STAGING_DB"
      deny:
        - "LARGE_VIEW_DB"  # Exclude databases with 10k+ views
        - "TEST_DB"

Performance Considerations

Using INFORMATION_SCHEMA is generally slower than SHOW statements but more reliable for large datasets:

Example for Snowflake. This can be adapted for other data warehouse platforms.

# Enable profiling with caution for large databases
profiling:
  enabled: false  # Disable for initial ingestion of large DBs

# Consider using incremental ingestion
stateful_ingestion:
  enabled: true
  remove_stale_metadata: true

Checking View/Table Counts

Before ingestion, check object counts in Snowflake:

Example for Snowflake. This can be adapted for other data warehouse platforms.

-- Check view count per database
SELECT 
    table_catalog as database_name,
    COUNT(*) as view_count
FROM information_schema.views
GROUP BY table_catalog
HAVING COUNT(*) > 9000  -- Near the limit
ORDER BY view_count DESC;

-- Check table count per database
SELECT 
    table_catalog as database_name,
    COUNT(*) as table_count  
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
GROUP BY table_catalog
HAVING COUNT(*) > 9000
ORDER BY table_count DESC;

Required Snowflake Permissions

Ensure your service account has access to INFORMATION_SCHEMA:

Example for Snowflake. This can be adapted for other data warehouse platforms.

-- Grant necessary privileges
GRANT USAGE ON DATABASE YOUR_DATABASE TO ROLE YOUR_DATAHUB_ROLE;
GRANT USAGE ON ALL SCHEMAS IN DATABASE YOUR_DATABASE TO ROLE YOUR_DATAHUB_ROLE;

-- For information schema access
GRANT SELECT ON ALL TABLES IN SCHEMA INFORMATION_SCHEMA TO ROLE YOUR_DATAHUB_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA INFORMATION_SCHEMA TO ROLE YOUR_DATAHUB_ROLE;

• This is a Snowflake platform limitation, not a DataHub limitation
• The 10,000 limit applies per SHOW statement, not globally
• INFORMATION_SCHEMA queries may be slower but are more scalable
• Consider running ingestion during off-peak hours for large databases
• Monitor ingestion logs for timeout errors when using INFORMATION_SCHEMA
• This flag is available in recent versions of the DataHub CLI (v0.10.0+)
• Future Snowflake releases may increase or remove this limit

• Snowflake Source Configuration
• Snowflake Ingestion Guide

5024, 5050