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.