Snowflake Ingestion OOM: Using push_down_database_pattern_access_history to Reduce Memory Usage – DataHub Customer Support Portal

```html

Area: Ingestion Issues

Sub-Area: Snowflake Connector — Out-of-Memory (OOM) During Query Log / Lineage Ingestion

Issue

Snowflake ingestion runs can be killed by an out-of-memory (OOM) signal (SIGKILL / SIGINT) when the remote executor pod exhausts its available memory. This most commonly occurs during the usage statistics and lineage phases of ingestion, where the SQL query log aggregator fetches large volumes of Snowflake ACCESS_HISTORY rows and accumulates them entirely in heap memory before emitting metadata. Environments with high query throughput — often exceeding one million preparsed queries per ingestion window — are especially susceptible, and the problem worsens as Snowflake workload grows over time without a corresponding increase in executor resources or a reduction in the query log scope.

Error Messages

TaskError: Failed to execute 'datahub ingest'
ERROR: The ingestion process was killed by signal SIGINT likely because it ran out of memory. You can resolve this issue by allocating more memory to the datahub-actions container.

You Might Be Asking

Why is my Snowflake ingestion running out of memory even though the database I am ingesting has relatively few tables?
Will simply increasing the remote executor memory limit fix the OOM problem permanently?
How can I reduce Snowflake query log volume without disabling lineage entirely?
What is the recommended long-term architecture for large Snowflake lineage ingestion?

Solution

The root cause is that the Snowflake connector's usage/lineage aggregator operates on an accumulate-everything-in-memory → emit-at-end model. Peak memory scales roughly linearly with the number of query log rows fetched, regardless of the number of tables in the target database. The following steps address the problem from least invasive to most comprehensive.

Upgrade to the latest DataHub CLI version.
Several OOM-related fixes have been shipped in recent CLI releases, including:
- Switching to pure-Python sqlglot to eliminate a memory leak in the C extension (sqlglot[c]).
- Clearing class-level caches on connector close to prevent cross-run accumulation.
- A FileBackedCounter-backed UsageAggregator that spills aggregation state to SQLite on disk instead of holding all counts in the heap, reducing peak RAM to a roughly flat ~90 MB regardless of query volume.
Verify the CLI version used by your executor and ensure it is current. Running an outdated CLI version is a common reason why OOM failures persist even after resource increases.
Enable use_file_backed_cache in your Snowflake recipe.
For environments with large schemas (more than ~10,000 objects), enable disk-backed caching to avoid holding all schema metadata in RAM:
```
source:
  type: snowflake
  config:
    use_file_backed_cache: true
```
Narrow the query log window.
The volume of rows fetched from ACCESS_HISTORY grows with the ingestion time window. Reducing the window from the default (often 7 days) to a shorter rolling window (1 day or 12 hours) dramatically reduces peak memory:
```
source:
  type: snowflake
  config:
    start_time: "-1d"   # or "-12h" for very high-traffic environments
```
Separate lineage ingestion into a dedicated snowflake-queries source.
This is the recommended long-term architecture for any Snowflake environment that regularly processes more than ~1 million queries per ingestion window. Disable lineage in the primary snowflake source and run a separate lightweight source on a short rolling window:

Primary Snowflake source (schema, tables, tags — no lineage):
```
source:
  type: snowflake
  config:
    # ... connection and pattern config ...
    include_table_lineage: false
    include_view_lineage: false
```
Separate query-log source (lineage only, short window):
```
source:
  type: snowflake-queries
  config:
    connection:
      account_id: "<your-snowflake-account-id>"
      authentication_type: KEY_PAIR_AUTHENTICATOR
      username: "<your-service-account-username>"
      private_key: "${SNOWFLAKE_PRIVATE_KEY}"
      role: "<your-snowflake-role>"
    window:
      start_time: "-1d"
    database_pattern:
      allow:
        - "<YOUR_DATABASE_NAME>"
```
Schedule the snowflake-queries source more frequently (e.g., every 12–24 hours) so that each run processes a manageable query slice. The aggregator never needs to hold more than the rows from that short window.
Increase and correctly configure remote executor memory (short-term stabilization).
As an immediate measure while the above configuration changes are applied, increase the executor pod's memory limits. Importantly, set requests equal to limits so the Kubernetes scheduler places the pod on a node that actually has the capacity available:
```
resources:
  requests:
    memory: "32Gi"   # match limits so the scheduler reserves full capacity
    cpu: "4"
  limits:
    memory: "32Gi"
    cpu: "4"
```
Note: increasing memory alone is not a permanent fix for environments with very high query volumes. The query log volume will continue to grow and will eventually exceed any static memory limit.
Reduce concurrent ingestion workers.
Running multiple Snowflake ingestions simultaneously multiplies memory pressure. Reduce max_workers in your executor configuration to limit concurrency:
```
executor:
  config:
    max_workers: 2   # reduce from the default (often 4) if Snowflake jobs overlap
```
Disable or schedule profiling separately.
Snowflake table profiling significantly increases memory usage. If profiling is enabled in the same recipe as lineage, move it to a separate ingestion source on a less frequent schedule (e.g., weekly):
```
source:
  type: snowflake
  config:
    profiling:
      enabled: false   # disable in the primary daily recipe; run separately
```

Additional Notes

Why a small database can still cause OOM: Memory pressure is driven by the total number of query log rows fetched from ACCESS_HISTORY across all queries touching the Snowflake account during the ingestion window — not by the number of tables in the target database. A source with very few tables can still trigger OOM if the Snowflake account has high overall query volume and the ingestion window is wide.

Memory scaling observed in practice: Approximately 4.5 GB peak RAM for ~1.1 million preparsed queries, ~8.6 GB for ~1.6 million, and OOM at ~2.8 million (with a 32 Gi limit). These figures vary with SQL complexity, temp-table stitching depth, and the number of unique query fingerprints.

File-backed aggregator (CLI >= recent releases): The new FileBackedCounter-backed UsageAggregator benchmarks at approximately 93 MB peak regardless of query volume (vs. 1,426 MB in-memory for a 10M event workload). Upgrading to a CLI version that includes this change is the most effective single fix.

Kubernetes scheduling note: When requests is set lower than limits, the Kubernetes scheduler may place the pod on a node that cannot satisfy the burst. Setting them equal ensures the node has the full required memory before the pod is scheduled.

Separate OOM-adjacent failure — oversized MCP payload: A distinct but related failure mode can occur when temp-table stitching concatenates many SQL statements into a single composite Query entity whose QueryProperties.statement field exceeds the GMS Jackson per-string limit of ~16 MB. This manifests as an HTTP 400 Cannot parse request entity error rather than an OOM signal, and is fixed in DataHub OSS pull request #14919. If you see 400 errors during Snowflake lineage ingestion, a CLI upgrade is the appropriate remedy.

Issue

Error Messages

You Might Be Asking

Solution

Additional Notes

Related Documentation