MATERIALIZED_VIEW
The MATERIALIZED_VIEW entity type is for creating and managing materialized views in the target database.
See also:
Notes
Refer to target database specific documentation regarding its capabilities with materialized views.
Snowflake
STORAGE.SECURE_VIEW is supported.
Clustering is supported, see SORT_KEY.
Functions are required to be deterministic.
Aggregation and GROUP BY are allowed.
Window functions are not allowed.
See full reference in Snowflake documentation.
Amazon Redshift
Distribution is supported, see DISTRIBUTION_KEY.
Sorting is supported, see SORT_KEY and INTERLEAVED_SORT_KEY.
Vacuum execution can be toggled in entity re-create with STORAGE.SORTKEY_RECLUSTER_IN_ENTITY_RECREATE.
Materialized views require manual refresh, which is recommended to be added as a default POST load step for the entity.
See full reference in Amazon Redshift documentation.
Azure SQL Database
Materialized view is an indexed view in Azure SQL Database. It requires a unique clustered index to be defined, see SORT_KEY.
Attribute definition is required.
Functions are required to be deterministic.
Aggregation and GROUP BY are allowed.
Window functions are not allowed.
Secondary indexing is supported, see SECONDARY_INDEX.
See full reference in Microsoft documentation.
Azure Synapse SQL
Attribute definition is required.
Distribution is supported, see DISTRIBUTION_KEY.
Functions are required to be deterministic.
Either aggregation or GROUP BY (or both) are required.
Window functions are allowed, yet limited.
See full reference in Microsoft documentation.
Google BigQuery
Partitioning is supported, see DISTRIBUTION_KEY
Functions are required to be deterministic.
Aggregation and GROUP BY are allowed and recommended.
Window functions are not allowed.
Materialized view is automatically refreshed, the option for disabling the automatic refresh is not supported in Agile Data Engine.
See full reference in BigQuery documentation.