What is a characteristic of materialized views in Snowflake?

A key characteristic of Snowflake materialized views is their ability to be defined by a query that joins multiple tables. This includes both inner and outer joins. This feature allows for the pre-computation of complex, frequently executed join operations, which significantly improves query performance by allowing the optimizer to use the pre-computed results instead of re-calculating the join for each query. The maintenance of the materialized view is handled automatically by Snowflake in the background.

A. Materialized views do not allow joins. This is incorrect. The Snowflake documentation explicitly states that the definition for a materialized view can include joins, including both inner and outer joins on multiple tables.
B. Clones of materialized views can be created directly by the user. This is incorrect. A materialized view is not a directly clonable object in Snowflake. While a materialized view will be cloned as part of a larger database or schema clone operation, a user cannot execute a CREATE MATERIALIZED VIEW ... CLONE command.
D. Aggregate functions can be used as window functions in materialized views. This is incorrect. Snowflake has specific limitations on the functions allowed in a materialized view's definition. Window functions, which use the OVER() clause, are explicitly not supported. This restriction includes aggregate functions used as window functions (e.g., SUM(c1) OVER (...)). ---

1. Snowflake Documentation: Working with Materialized Views > Limitations on Creating Materialized Views.

For Correct Answer C & Incorrect Answer A: The documentation states, "A materialized view can query only one table, or can perform an inner join or an outer join on multiple tables." This confirms that joins involving multiple tables are a supported characteristic.

For Incorrect Answer D: The same section lists unsupported query syntax, explicitly including: "Window functions (i.e. functions that use the OVER() clause), including aggregate functions used as window functions."

2. Snowflake Documentation: SQL Command Reference > CREATE