Views are different from tables in a data warehouse in that a view is created with a SQL statement and will run that SQL statement every time it is called, whereas a table contains data in the format decided upon its creation. Thus, the key difference between a table and a view is: tables cost storage, views cost compute.

Generally, only create views where the data is used rarely (once a day within an ELT pipeline, for example).

A good rule of thumb:

  • If you are likely to query a table multiple times a day, then materialise the table as a table.
  • If you are only going to use the table as an input for your pipeline, then create the table as a view instead.

Views can also be used to apply an extra layer of security on top of data, where users can only have access to certain data in the data warehouse.

Views should be prefixed with vw_ (short for “view”) and reside in the schema that is most appropriate. If a view is created on top of a raw table and serves only as another way to query the raw data, then this view would reside in the <source_name>_raw schema.