Create View
    • Dark
      Light

    Create View

    • Dark
      Light

    Article Summary

    Create View Component

    The Create View component enables you to output a view definition to your cloud data warehouse (CDW). In some circumstances, this action may be preferable to writing the data to a physical table.

    If a view of the same name already exists, it will be replaced when this component runs.

    If a table of the same name already exists, this component will fail its run—the Create View component cannot replace an existing table, only an existing view.

    Views created by this component will not be dropped when the Transformation Job is revalidated. However, the views will be recreated at runtime (when the Transformation Job is executed).

    Note: This component cannot create views using external schemas.

    Properties

    Snowflake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    DatabaseSelectSelect a database. A database is a logical grouping of schemas. Each database belongs to a single Snowflake account.
    SchemaSelectSelect the schema. A schema is a logical grouping of database "objects" (tables, views, etc.). Each schema belongs to a single database. The special value, [Environment Default], will use the schema defined in the environment.
    View NameStringProvide a name for the view to be created.
    Secure ViewSelectWhen Yes, the view definition and details are only visible to authorised users, i.e. users who are granted the role that owns the view. Default is No.
    Snowflake advises that views should be defined as secure when they are specifically designated for data privacy. For more information about secure views, please read the Snowflake documentation.
    View TypeSelectSelect the view type.
    Materialized: A materialized view is a pre-computed data set derived from a query specification and stored for later use. Since the data is pre-computed, querying a materialized view is faster than executing the original query. Materialized views are advised when:
    • Query results contain a small number of rows and/or columns relative to the base table.
    • Query results contain results that require significant processing.
    Standard: (default setting) create standard views when:
    • The results of the view change often.
    • The results are not used often (relative to the rate at which the results change).
    • The query is not resource intensive, so it is not costly to re-run it.

    Redshift Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see this article.
    View NameStringProvide a name for the view to be created.
    Late BindingSelectSelecting "Yes", creates a late-binding view. Late-binding views do not check underlying database objects, such as tables and other views, until the view is queried. As a result, users can alter or drop the underlying objects without dropping and recreating the view. If the user drops underlying objects, queries to the late-binding view will fail. If the query to the late-binding view references columns in the underlying object that aren't present, the query will fail.
    The default setting is "No".
    View TypeSelectSelect the view type.
    Materialized: A materialized view is a pre-computed data set derived from a query specification and stored for later use. Since the data is pre-computed, querying a materialized view is faster than executing the original query. Materialized views are advised when:
    • Query results contain a small number of rows and/or columns relative to the base table.
    • Query results contain results that require significant processing.
    More information can be found here.
    Standard: create standard views when:
    • The results of the view change often.
    • The results are not used often (relative to the rate at which the results change).
    • The query is not resource intensive, so it is not costly to re-run it.
    More information can be found here.

    BigQuery Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    ProjectSelectSelect the Google BigQuery project. The special value, [Environment Default], will use the project defined in the environment.
    For more information, refer to the BigQuery documentation.
    DatasetSelectSelect the Google BigQuery dataset to load data into. The special value, [Environment Default], will use the dataset defined in the environment.
    For more information, refer to the BigQuery documentation.
    View NameStringProvide a name for the view to be created.
    View TypeSelectSelect the view type:
    Materialized: materialized views in Google BigQuery are pre-computed views that periodically cache the results of a query for increased performance and efficiency. Queries that use materialized views are typically faster and consume fewer resources compared to queries that retrieve the same data only from the base table. Materialized views can greatly improve the performance of workloads that bear the characteristics of common and repeated queries. For more information, read Introduction to materialized views.
    Standard: a view is a virtual table defined by an SQL query. You create a view by querying it in the same manner you query a table. When you query a view, the query results only contain data from the tables and fields specified in the query that defines the view. For more information, read Introduction to views.
    Enable RefreshSelectSelect whether to enable refreshing of the materialized view.
    No: the materialized view will only be refreshed manually. For example, when the Create View Transformation Job is re-run, or when triggered by the Refresh Materialized View component.
    Yes: the materialized view will be refreshed automatically every 30 minutes. Example changes would be row insertions or row deletions. The default setting is Yes.

    Synapse Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment.
    For more information on schemas, please see the Azure Synapse documentation.
    View NameStringProvide a name for the view to be created.

    Delta Lake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    CatalogSelectSelect a Databricks Unity Catalog. The special value, [Environment Default], will use the catalog specified in the Matillion ETL environment setup. Selecting a catalog will determine which databases are available in the next parameter.
    DatabaseSelectSelect the Delta Lake database. The special value, [Environment Default], will use the database specified in the Matillion ETL environment setup.
    View NameStringProvide a name for the view to be created.
    Table PropertiesKeyA metadata property within the table. These are expressed as key=value pairs.
    ValueThe value of the corresponding row's key.
    CommentStringA descriptive comment of the view.

    Strategy

    Generates a permanent, named view, containing the SQL generated by all the input tables up to the Create View component.

    Example

    This example creates a view over the Accounts table, applying a filter to select New Accounts.

    Only the View Name needs to be completed:

    Whenever "new_accounts_view" is read, it will reflect the current contents of the Accounts table with the filter applied.