DLT pipelines failing out of memory (serverless)

[alejandro_jaram]

I have a Data Lake Transformation (DLT) pipeline that runs weekly. Normally, it takes 8 minutes to complete, but since last Friday (June 19), it has been running for hours until it encounters an out-of-memory error. This pipeline is responsible for creating materialized views from metric views. The only idea I have so far is that the issue might be related to reading materialized views of those metric views. When I perform a full refresh, the materializations of the metric views are refreshed, and the entire pipeline completes in 20 minutes. However, this option is not feasible for production environments due to its cost.

[bala_sai]

I think this is more like an incremental refresh issue than a generic serverless memory issue.

Since the pipeline completes in around 20 minutes with a full refresh, but the normal weekly run runs for hours and then fails with OOM, I would first recommend to check whether the materialized views are still taking an efficient incremental refresh path. It is possible that the incremental refresh plan became more expensive after June 19, or that the materialized view is falling back to a heavier recompute/refresh path.

The first thing you should check is the Lakeflow/DLT event log for the failing update. Specifically, check the refresh planning details and see whether the materialized view is planned as an incremental refresh or a complete recompute. If you see something like GROUP_AGGREGATE, that usually indicates an incremental aggregate refresh path. If you see COMPLETE_RECOMPUTE, then it means the MV is being fully recomputed.

list of things you can check is:

1) Did the metric view or materialized view definition change around June 19?
2) Did the source data volume increase suddenly?
3) Is there any data skew in the grouping/join keys?
4) Are the metric views referencing other metric views or materialized views?
5) Are there complex joins, window functions, non-deterministic expressions, or aggregations that may prevent efficient incremental refresh?
6) Is the incremental changeset now larger or more expensive than a clean full refresh?

As the full refresh completes faster than the normal run is a strong signal that the incremental path is the problem. Sometimes incremental refresh can become more expensive than full recompute depending on the data shape, query plan, or source changes. So my suggestion would be to check the event log, confirm whether the failing MV is using GROUP_AGGREGATE or COMPLETE_RECOMPUTE, compare the current MV/metric view definition with the last successful run, and then target only the problematic materialized view instead of doing a full refresh of everything.

[alejandro_jaram]

Hi @bala_sai, thanks for taking the time.
I changed the policy to auto and pipeline worked a few times but now is stuck again, even with full recompute is taking 2 hours reading 2GB of data.

Data haven't changed in dev or st enviroment so is not related to that, or DLT code got updated last week or metric views did.

[bala_sai · Accepted solution]

Hey @alejandro_jaram, You can check if the DLT/Lakeflow event log for the affected materialized view. Compare one fast successful run with one slow/stuck run and check the planning_information details. Specifically, look for whether the MV is using an incremental plan or full recompute and whether there is any reason listed for rejecting incremental refresh such as plan not incrementalizable/row tracking not enabled/the cost model rejecting the incremental plan etc

You can also try to isolate whether one specific materialized view is causing the issue. If possible, refresh or run only the affected MV instead of the whole pipeline. If one MV is taking most of the time, then the problem is likely in that MV’s query plan/source tables/joins/aggregations or metric view materialization path

Since full recompute itself is slow for only 2 GB you should even consider checking these:

1) skew on join or group by keys
2) small files or file churn in the source tables
3) an expensive join strategy
4) row filters, column masks, expectations, or non deterministic expressions
5) metric views referencing other metric views/materialized views
6) source table properties changing in a way that affects incremental refresh
7) row tracking or CDF not enabled on source Delta tables where incremental refresh depends on them

Another useful test is to run the underlying SQL for the problematic MV outside the DLT/Lakeflow pipeline if possible. If the same query runs quickly outside the pipeline but is slow inside the pipeline that points more towards the materialization/runtime path. If it is slow outside as well then the issue is likely in the query plan or source table layout.

[alejandro_jaram]

Hey, I found that another engineer added joins to the metric view definitions to a federated query table. I converted it to a streaming table to use CDF and now time reduced. I need to improve joins to reduce latency as much as possible, but I’m not getting the timeout error. Thanks