Workflow Query Failure Due to Memory Limit Exceeded

Overview

In some cases, a workflow may fail during execution because one or more queries consume more system memory than the allowed limit. When this happens, the workflow cannot complete successfully, and downstream processes (such as file generation or file delivery) may also be impacted.

This issue is not limited to a specific workflow and can occur in any workflow, depending on the query logic and the volume of data being processed.

Observed Error

During workflow execution, the following type of error may be observed:

Query `Replenishment` failed with While running this query the system ran out of memory. This error is safe to retry. If it continues to fail, try to optimize the query. If the query references the campaign_recipients table, please make sure it is filtered by 'delivery_date' to only the range required. If you expect this query should run without issue, please contact Amperity Support for help.

or

Dependent query did not complete successfully. qex-20250619-61314-3J6G7xk5WS8 
[EXCEEDED_LOCAL_MEMORY_LIMIT: Query exceeded per-node memory limit of 110GB 
[Allocated: 109.99GB, Delta: 15.35MB,
 Top Consumers: {HashBuilderOperator=108.32GB, HashAggregationOperator=1.05GB, TableScanOperator=304.91MB}]]

In some cases, the error details may also show the operators consuming the most memory, such as:

  • HashBuilderOperator

  • HashAggregationOperator

  • TableScanOperator

This indicates that the query execution is exceeding the available memory during processing.

What This Error Means

This error indicates that:

  • The query requires more memory to execute than what is available within the system limits.

  • To ensure platform stability, the system terminates the query execution.

Although the error may be marked as safe to retry, repeated failures generally indicate that the query needs to be optimized.

Possible Causes

Memory-related query failures commonly occur due to:

  • Large joins on high-volume datasets

  • Unfiltered or high-cardinality joins

  • Hash joins on large tables

  • Missing or insufficient filters (such as date or range filters)

  • Too much logic combined into a single query

These factors can significantly increase memory usage during query execution.

Recommended Actions

To resolve the issue and prevent it from recurring, the following actions are recommended:

  1. Optimize the Query

    • Apply filters as early as possible in the query

    • Review and simplify join conditions

  2. Use Date or Range-Based Filters

    • When querying event, campaign, or transactional tables, limit the data to the required date range only

  3. Enable CTAS

    • Use CTAS to materialize intermediate results

    • This can help reduce runtime memory consumption

  4. Break the Query into Smaller Steps

    • Split complex logic into multiple smaller queries or intermediate tables instead of a single large query

  5. Validate in a Sandbox or Test Environment

    • Test optimized queries in a sandbox or test environment before running them in production workflows

Additional Notes

  • This issue is related to query design and data volume, not to the workflow configuration or infrastructure.

  • Once the query is optimized and executes successfully, the workflow should complete normally and all downstream outputs should be generated as expected.

Support

If assistance is required with query optimization or validation, please contact Amperity Support. Our team will be happy to help.