Seeking Guidance on Materialized Views Update Challenges

Hello Forum Members,

Our team relies on materialized views to facilitate joins between our main table and multiple dimension tables. Due to the significant size of the main table, we've implemented 12 materialized views, each corresponding to a different month. Following the update of dimension tables, it's imperative to refresh all 12 materialized views.

Over the past year, I've successfully updated these materialized views three times. However, I'm currently encountering multiple errors during the update process. The specific errors I encountered last night are as follows:

1. Admin node has changed


2. An admin command cannot be executed due to a failure in the distributed ingestion:
Details='External component has thrown an exception.'

3. An admin command cannot be executed due to a failure in the distributed ingestion:

 Details='Query execution lacks memory resources to complete (80DA0007):

 Partial query failure: Low memory condition (E_LOW_MEMORY_CONDITION).

 (message: 'shard: a0daa274-8072-4c75-8fb4-7caf2f663e8a, source: (hr: '2161770503'

 'Engine under memory pressure, context: field_datum decode range'): '

 , details: 'StgError { kind: Generic("shard: a0daa274-8072-4c75-8fb4-7caf2f663e8a")

 , source: Some(StgError { kind: HResult(2161770503, "Engine under memory pressure, context: field_datum decode range"), source: None }) }').

  (0th of 2 in an AggregateException with message: One or more errors occurred.)'

I'm reaching out to the forum to seek advice and explore alternative methods for optimizing this process. Are there better approaches or best practices that you could recommend for handling materialized views and updates, especially when dealing with large datasets?

Your insights and suggestions would be greatly appreciated.

Thank you,
Amir