Volume Anomaly Detection Assertions Causing Long-Running Snowflake Queries – DataHub Customer Support Portal

Area: Observability Issues

Sub-Area: Assertion Monitors / Volume Anomaly Detection

Issue

DataHub's volume anomaly detection assertion monitors can generate expensive, long-running full-table-scan queries against Snowflake tables independently of dataset profiling or ingestion settings. When a monitored table is very large, these assertion-driven queries can run for two hours or more, triggering warehouse alerts. Because assertions and profiling are completely separate code paths, disabling Monitoring Rules and individual assertions does not immediately stop in-flight queries, and the SQL generated by assertion monitors (including COUNT, APPROX_COUNT_DISTINCT, MIN, MAX, AVG, STDDEV_SAMP, and MEDIAN) can be mistaken for column-level profiling output, leading to incorrect troubleshooting steps.

Error Messages

Long-running query alert: query executed by DataHub service account exceeded 2 hours on <your-table>

You Might Be Asking

Are these long-running Snowflake queries coming from DataHub column-level profiling or from assertion monitors?
Why do long-running queries continue after I disabled all assertions and Monitoring Rules?
Does disabling assertions immediately stop Snowflake queries from running?
How do I stop monitoring queries against a specific table without disabling all Snowflake ingestion?
Can I safely cancel already-running Snowflake queries that were generated by DataHub?
What is the difference between using profile_pattern and table_pattern to exclude a table?
Why does the volume anomaly detection assertion run a full table scan?

Solution

Identify whether the source is assertions or profiling.
The SQL functions COUNT, APPROX_COUNT_DISTINCT, MIN, MAX, AVG, STDDEV_SAMP, and MEDIAN appear in both assertion monitor queries and DataHub column-level profiling queries, making them easy to confuse. To determine the true source:
- Check whether your Snowflake ingestion recipe already has profile_table_level_only: true and turn_off_expensive_profiling_metrics: true set. If both are enabled, the profiler will not generate column-level statistics such as APPROX_COUNT_DISTINCT or MEDIAN — those queries must be coming from assertion monitors.
- In DataHub, navigate to the dataset's Quality tab and review active assertions, paying particular attention to any Volume Anomaly Detection assertions with a Query collection mechanism. These perform a full table scan on every execution cycle.
- Correlate the timestamps of the long-running Snowflake queries with assertion execution records in DataHub to confirm the match.
Understand why disabling assertions does not immediately stop running queries.
Disabling a Monitoring Rule or an individual assertion prevents future scheduled executions. It does not terminate queries that are already running in Snowflake. If an assertion execution was already submitted before you toggled it off, that Snowflake query will run to completion unless you cancel it manually in Snowflake.
Disable the Monitoring Rule and all related assertions.
In the DataHub UI:
1. Go to the dataset's Quality tab.
2. Locate the Volume Anomaly Detection assertion and its associated Monitoring Rule.
3. Toggle the Monitoring Rule to OFF.
4. Toggle each individual assertion to OFF.
After both are disabled, no new execution requests will be submitted. Verify in Snowflake's query history that no new queries from the DataHub service account appear against the affected table after the next expected execution window passes.
Optionally cancel in-flight Snowflake queries.
Assertion queries are read-only SELECT statements, so cancelling them in Snowflake will not corrupt any data. However, if DataHub's stateful checkpoint has already recorded the assertion as "in progress," cancelling mid-run may cause the next execution to re-evaluate or skip unexpectedly. The safest approach is to let the current queries finish and rely on the disabled assertion to prevent future runs. If warehouse impact is severe, cancel the queries in Snowflake using:
```
-- Find the running query
SELECT query_id, query_text, execution_status, total_elapsed_time
FROM TABLE(information_schema.query_history_by_user(
    USER_NAME => '<your-datahub-service-account>',
    RESULT_LIMIT => 100
))
WHERE execution_status = 'RUNNING'
ORDER BY start_time DESC;

-- Cancel a specific query
SELECT SYSTEM$CANCEL_QUERY('<query_id>');
```
Determine whether profiling exclusion is also needed.
If your profiling configuration does not have profile_table_level_only: true and turn_off_expensive_profiling_metrics: true, column-level profiling may also be contributing to long-running queries. In that case, exclude the specific table from profiling without removing it from ingestion entirely by using profile_pattern in your recipe:
```
source:
  type: snowflake
  config:
    # ... other config ...
    profiling:
      enabled: true
      profile_table_level_only: true          # disables column-level stats
      turn_off_expensive_profiling_metrics: true  # disables MEDIAN, STDDEV, etc.
      profile_pattern:
        deny:
          - "<DATABASE>\\.<SCHEMA>\\.<TABLE_NAME>$"
```
Important: Use profile_pattern (not table_pattern) to exclude only profiling while preserving the table's metadata, lineage, and usage statistics in DataHub. Using table_pattern would remove the table from DataHub entirely.
Consider switching the Volume Anomaly Detection collection mechanism.
If the assertion needs to remain active on a very large table, switching the collection mechanism from Query (full table scan) to Information Schema is significantly more efficient. The Information Schema approach reads Snowflake's precomputed row-count metadata instead of scanning the table directly. Navigate to the assertion's settings in the DataHub UI and update the Volume collection mechanism accordingly. Note that Information Schema counts may have a slight lag compared to real-time query counts.

Validate the fix.

After disabling the assertion monitor and/or switching the collection mechanism, monitor the Snowflake query history for the DataHub service account over the next 24–48 hours to confirm no additional long-running queries appear against the affected table.

-- Monitor for new DataHub queries on the affected table
SELECT query_id, query_text, start_time, total_elapsed_time, execution_status
FROM TABLE(information_schema.query_history_by_user(
    USER_NAME => '<your-datahub-service-account>',
    RESULT_LIMIT => 200
))
WHERE query_text ILIKE '%<YOUR_TABLE_NAME>%'
  AND start_time >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

Additional Notes

Assertions vs. Profiling are independent code paths. Disabling one has no effect on the other. Always verify which component is the true source before making recipe changes.

Volume Anomaly Detection with the Query mechanism performs a full table scan on every execution cycle (e.g., hourly training runs). On very large tables (hundreds of billions of rows), each scan can take several hours and is expected behavior — not a bug. The cost of this approach should be weighed against the benefit of the assertion.

Execution duration visibility is not currently surfaced in the DataHub UI per assertion run. If you need this feature, consider submitting a feature request through your support channel so the cost of each assertion execution can be evaluated directly in the platform.

Already-running queries are safe to cancel in Snowflake (they are read-only SELECT statements), but be aware that a mid-run cancellation may affect stateful checkpointing for that assertion cycle.

Profile settings reference: The options profile_table_level_only and turn_off_expensive_profiling_metrics are available in the Snowflake ingestion source configuration. When both are set to true, column-level statistics are not generated, and any remaining aggregate queries must be attributed to assertion monitors rather than the profiler.

Issue

Error Messages

You Might Be Asking

Solution

Additional Notes

Related Documentation