Reducing Snowflake Query Volume and Cost from DataHub Ingestion and Profiling – DataHub Customer Support Portal

Area: Ingestion Issues

Sub-Area: Snowflake Connector — Profiling, Lineage, and Assertions Cost Optimization

Issue

Organizations using DataHub's Snowflake connector may observe a significant increase in Snowflake query volume and compute costs attributed to the DataHub service account. This can result from one or more concurrent factors: column-level profiling generating expensive per-column statistics queries, lineage collection scanning large Snowflake account usage tables, schema cache building across broad database scopes, Smart Assertion or Volume Anomaly Detection monitors running frequent queries on large tables, a CLI version upgrade that changes the default profiling engine, or an expansion of the ingestion scope. Understanding which phase is responsible and applying targeted configuration changes can substantially reduce both query count and credit consumption.

Error Messages

No explicit error — symptom is elevated Snowflake credit usage and query count in QUERY_HISTORY for the DataHub service account.

You Might Be Asking

Why did Snowflake costs spike after enabling DataHub ingestion or upgrading the DataHub CLI?
What queries does DataHub run against Snowflake during ingestion, and which ones are the most expensive?
How do I reduce Snowflake query volume without disabling metadata collection entirely?
Are DataHub Smart Assertions or Volume Anomaly Detection monitors causing long-running Snowflake queries?
Did a CLI version upgrade change how profiling works and increase compute usage?

Solution

Step 1 — Identify Which Phase Is Driving the Cost

Ask your Snowflake administrator to pull ACCOUNT_USAGE.QUERY_HISTORY for the DataHub service account and examine:

Query text — to distinguish profiling queries (COUNT(*), APPROX_COUNT_DISTINCT, STDDEV_SAMP, MEDIAN) from lineage queries (QUERY_HISTORY, ACCESS_HISTORY scans) or assertion queries.
Query start time and duration — correlate with your ingestion schedule and assertion monitor schedules.
Warehouse used and its size — to estimate credit consumption per phase.

The most common cost drivers, in rough order of impact, are:

Column-level profiling — runs per-column statistics for every column in every profiled table; can run for hours on large tables.
CLI version changes — upgrading the DataHub CLI may change the default profiling engine, significantly increasing runtime for the same table set.
Lineage via Queries V2 — scans ACCOUNT_USAGE.QUERY_HISTORY and ACCESS_HISTORY; expensive when the lookback window is wide or no user/database filters are applied.
Schema cache building — scans INFORMATION_SCHEMA.COLUMNS across all in-scope databases before parsing query logs.
Smart Assertions / Volume Anomaly Detection monitors — can issue full-table scans (e.g., COUNT(*)) on an hourly cycle against very large tables.
Periodic full re-profiles — some configurations trigger a full catalog re-profile every several weeks, profiling many more tables than a typical incremental run.

Step 2 — Pin or Roll Back the Profiling Engine (if a CLI Upgrade Preceded the Spike)

If a CLI upgrade correlates with the cost increase, test whether reverting to the previous profiling engine restores the original runtime:

# In your Snowflake ingestion recipe (recipe.yml)
source:
  type: snowflake
  config:
    profiling:
      enabled: true
      method: ge   # Forces the Great Expectations profiler rather than the new default

Also add the corresponding extra to your pip install:

pip install "acryl-datahub[snowflake,profiling-ge]"

Run one ingestion cycle and compare runtime against a recent baseline run. If runtime drops significantly, the profiling engine change was the root cause.

Step 3 — Restrict the Ingestion Scope

Every additional database, schema, or table in scope adds profiling, schema scan, and lineage overhead. Restrict to only the assets you need:

source:
  type: snowflake
  config:
    database_pattern:
      allow:
        - "^MY_DATABASE$"
        - "^ANOTHER_DATABASE$"
    schema_pattern:
      allow:
        - "^MY_DATABASE\\.IMPORTANT_SCHEMA$"
    table_pattern:
      allow:
        - "^MY_DATABASE\\.IMPORTANT_SCHEMA\\..*"

Step 4 — Tune or Disable Column-Level Profiling

Column-level profiling is frequently the single largest cost driver. Apply the following options in increasing order of aggressiveness:

source:
  type: snowflake
  config:
    profiling:
      enabled: true

      # Option A: Skip column-level statistics; run only table-level row counts
      profile_table_level_only: true

      # Option B: Disable the most expensive per-column metrics individually
      turn_off_expensive_profiling_metrics: true

      # Option C: Re-enable the built-in size caps (these cap profiling to tables
      # under 5,000,000 rows and 5 GB by default; set to null removes the cap)
      profile_table_row_limit: 5000000
      profile_table_size_limit: 5

      # Option D: Limit profiling to specific tables only
      profile_pattern:
        allow:
          - "^MY_DATABASE\\.IMPORTANT_SCHEMA\\.CRITICAL_TABLE$"

      # Option E: Skip tables that have not been updated recently
      profile_if_updated_since_days: 7

Step 5 — Optimize Lineage Collection (Queries V2)

When use_queries_v2: true is set, DataHub scans Snowflake account usage tables. Reduce the cost of these scans by narrowing the lookback window and filtering out system or robot users at the Snowflake query level:

source:
  type: snowflake
  config:
    use_queries_v2: true

    # Narrow the lookback window (default may be much longer)
    start_time: "-7d"

    # Filter out non-human / service accounts to reduce rows processed
    # (applies a WHERE clause at the Snowflake side, not post-fetch)
    email_domain: "example.com"
    exclude_username_pattern: "^(SVC_|ROBOT_|SYSTEM_).*"

Step 6 — Enable Stateful Ingestion

Stateful ingestion ensures that after the first full run, subsequent runs only process the incremental window rather than the full history, substantially reducing repeated query volume:

source:
  type: snowflake
  config:
    stateful_ingestion:
      enabled: true

Step 7 — Review Smart Assertions and Volume Anomaly Detection Monitors

If Volume Anomaly Detection or other assertion monitors are active on very large tables, they can submit full-table scan queries on a scheduled (often hourly) cadence. Review active monitors in the DataHub UI:

Navigate to Observe → Monitors in the DataHub UI.
Identify assertion monitors targeting tables with very high row counts.
Reduce the evaluation frequency, increase the minimum row threshold, or disable the monitor for tables where the cost is not justified.
Use the Information Schema collection mechanism for assertions where available, as it is significantly more efficient than direct table scans.

Cross-reference assertion execution timestamps in DataHub with query timestamps in Snowflake's QUERY_HISTORY to confirm whether assertions are the source.

Step 8 — Isolate the DataHub Warehouse

Point the DataHub service account role at a small, dedicated Snowflake virtual warehouse with a short auto-suspend interval. This isolates DataHub's compute from production workloads and makes it easy to cap and monitor ingestion costs independently:

-- In Snowflake
CREATE WAREHOUSE DATAHUB_WH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

GRANT USAGE ON WAREHOUSE DATAHUB_WH TO ROLE ;

# In your recipe
source:
  type: snowflake
  config:
    warehouse: "DATAHUB_WH"

Additional Notes

Disabling assertion monitors or monitoring rules from the DataHub UI prevents future executions but does not terminate already-running Snowflake queries. Use Snowflake's QUERY_HISTORY UI or SHOW QUERIES to identify and abort any in-flight queries if immediate cost containment is needed.

The options profile_table_row_limit: null and profile_table_size_limit: null explicitly remove the built-in profiling caps. If these appear in your recipe, restoring them to their defaults (5,000,000 rows / 5 GB) will automatically skip the largest tables.

Starting with DataHub CLI version 1.6.0, the default profiler used for column statistics changed. If your ingestion runtime approximately doubled after a CLI upgrade without any recipe changes, testing with profiling.method: ge is the recommended first diagnostic step.

Periodic full re-profiles (where the entire catalog is reprofiled rather than only changed tables) are expected behavior on a multi-week cycle when profile_if_updated_since_days is not set. Setting this parameter prevents unbounded re-profiling sweeps.