Views

Overview

A view is simply a mechanism for querying data. When you create a query on your database you can store the query (i.e. the SQL commands that perform the query) as a view, and by giving the view a unique name you can make the same query available to use elsewhere in your database environment.

A view contains rows and columns of data, similar to a table; however, these can be drawn from multiple different tables and the view can be configured to filter the data retrieved from the underlying tables, and to perform some aggregation or calculation on the data (sum, average, etc.), making it a very powerful and flexible tool for accessing data.

The view can be thought of as a "virtual table", in that it presents data in the way that a table would, and many operations you would perform on a table can also be performed on a view. However, it is important to realise that it is not a table and should not be treated as such. The main difference is that while the table actually stores data in the database, the view is only a logical entitity that points to a subset of that data. Views take very little space to store, as the database stores only the definition of the view (i.e. the SQL commands that create it), not a copy of all the data that it presents.

A materialized view is a specific type of view that caches the results of a query to provide increased performance and efficiency. Queries that retrieve data from materialized views are generally faster and consume fewer resources than queries that retrieve the same data from the underlying tables. Matillion ETL supports materialized views on Amazon Redshift and Google BigQuery.

Using views in Matillion ETL

A Transformation Job in Matillion ETL will typically read data from a source table, perform some operation on that data (such as filter or aggregate), and write the resulting output to a new table. However, rather than writing to a new table, you could instead create a view. Creating a view as part of a job stores the definition of that view (i.e. the SQL commands generated by the job components), and allows you to examine the data that results from the Transformation Job without the resource overhead of creating a new table in your cloud data warehouse.

The following job components are used to work with views in Matillion ETL:

Create View — The Create View component lets you output a view definition to your cloud data warehouse.
Assert View — The Assert View component lets you verify that certain conditions are true of a view, and stop the query if they are not. This is an Enterprise Edition feature.
Refresh Materialized View (Redshift) — The Refresh Materialized View component identifies changes to an underlying table and applies those changes to the materialized view. This component is specific to Matillion ETL for Redshift only.
Refresh Materialized View (BigQuery) — The Refresh Materialized View component identifies changes to an underlying table and applies those changes to the materialized view. This component is specific to Matillion ETL for BigQuery only.

Converting views to tables

There are some operations in Matillion ETL where views are not supported. For example, unloading data via Cloud Storage Unload component. In these cases, you should convert your view to a table, which you can then use in the desired operation. To do this, you can create a new table with your view's metadata and copy the view data to it.

First, create a Transformation Job with a Table Input component connected to a Table Output component. Select your view in the Table Input's Table property. When this is done, you can use the Metadata tab in Text Mode to copy your view's metadata.

View metadata

Next, create an Orchestration Job and attach a Create Table component. Paste your metadata into the Table Metadata property, and then run the Orchestration Job to create the new table.

Now return to the Transformation Job and add a Table Output component to place data from your view into the newly created table and run the Transformation Job. You now have a new table that contains all of the data in your view and that can be used in situations such as unloading.