In a materialized view definition, are non-deterministic functions allowed?

• A. Only nondeterministic allowed
• B. Only deterministic allowed
• C. All functions allowed
• D. No functions allowed

Answer: (B)

Materialized views rely on reproducible results; to keep them accurate, the expressions in their definitions must be deterministic. Non-deterministic functions—such as current_timestamp() or random()—produce different outputs each time they are evaluated, even with the same inputs. If such functions were allowed inside a materialized view, the stored snapshot could become inconsistent or require complex and unreliable refresh behavior. Deterministic functions, which return the same result for the same inputs, let Snowflake compute and refresh the MV reliably. Examples of deterministic usage include standard arithmetic and string/date operations like length, lower, substr, and date_trunc. So, only deterministic functions are allowed in a materialized view definition; nondeterministic ones are not.