topic Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED? in Data Engineering

Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

GregTyndall — Tue, 03 Dec 2024 13:43:50 GMT

I have a very basic view with 3 inner joins that will only do a full refresh. Is there a limit to the number of joins you can have and still get an incremental refresh?

"incrementalization_issues": [
{
"issue_type": "INCREMENTAL_PLAN_REJECTED_BY_COST_MODEL",
"prevent_incrementalization": true,
"cost_model_rejection_subtype": "NUM_JOINS_THRESHOLD_EXCEEDED"
}
]

Thanks!

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

MuthuLakshmi — Wed, 04 Dec 2024 13:50:44 GMT

@GregTyndall We have a limit of 2 joins

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

PotnuruSiva — Wed, 04 Dec 2024 14:07:17 GMT

@GregTyndall Yes, the current limit is 2 by default. But we can increase up to 5 with the below flag added to the pipeline settings.

pipelines.enzyme.numberOfJoinsThreshold 5

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

TheSmike — Thu, 17 Apr 2025 15:52:10 GMT

I have the same issue.

What do you mean exactly with "added to the pipeline settings"? How can I set it?

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

RiyazAliM — Mon, 21 Apr 2025 07:28:24 GMT

Hey @TheSmike

In the DLT Pipeline's top right cornor, you can click on settings and scroll down to Advanced and click on Add Configuration and give the key as `pipelines.enzyme.numberOfJoinsThreshold` and value as 5.

Hope this helps.

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

TheSmike — Thu, 24 Apr 2025 07:50:08 GMT

Thanks, it works.

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

_DatabricksUser — Wed, 14 May 2025 15:56:06 GMT

@GregTyndall- how did you get those level of details (incrementalization_issues) for the MV build?

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

TheSmike — Tue, 27 May 2025 15:29:43 GMT

To determine which refresh strategy is being used (incremental vs full), refer to the final section of the documentation: https://docs.databricks.com/aws/en/optimizations/incremental-refresh#determine-the-refresh-type-of-an-update.

According to the docs:

To determine the technique used, query the DLT event log where the "event_type" is "planning_information"...

Note:
There's a typo in the official documentation. To query the event log correctly, use FROM event_log_[NORMALIZED_DLT_ID]. Where [NORMALIZED_DLT_ID] is your pipeline ID with "_" instead of "-".

Look at the "details" column — if a **full refresh** is triggered, it often contains helpful insights into the reason.

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

maarko — Mon, 23 Jun 2025 12:54:53 GMT

@PotnuruSiva I set pipelines.enzyme.numberOfJoinsThreshold 5 for a MV with 4 joins. But still I am getting
"incrementalization_issues": [
{
"issue_type": "INCREMENTAL_PLAN_REJECTED_BY_COST_MODEL",
"prevent_incrementalization": true,
"cost_model_rejection_subtype": "NUM_JOINS_THRESHOLD_EXCEEDED"
}
]

Re: Materialized View Refresh - NUM_JOINS_THRESHOLD_EXCEEDED?

_DatabricksUser — Mon, 23 Jun 2025 13:21:04 GMT

@maarko- I'd create a separate thread for your issue. But I can tell you though that I had the same problem- the Enzyme number of joins threshold was seemingly not being respected. I reported to Databricks support and they transferred the ticket to the Databricks Spark team to investigate. Currently they do not have any answer for me.