Materializing Views

👤 This documentation is intended for SQL Users. SQL Users may need to reach out to their Site Administrators to ensure underlying tables in Views are cached.

Note: Materializing views is only available with the Periscope Data Engine.

Views can be materialized on the Periscope Cache or Warehouse. When a materialized view is referenced in a query, Periscope Data pulls the "saved" materialized result set rather than running the contents of the view as a subquery. Leveraging materialized views in queries can contribute to significant performance gains when used strategically, and is especially recommended for queries experiencing long runtimes and timeout errors.

Materialized views must be written in Redshift-compatible or Snowflake-compatible syntax depending on the cache infrastructure being used. Once a view has been materialized for the first time, it can be queried almost immediately. 

Materialization

For a view to materialize, users must confirm with their Site Administrators that the underlying tables are cached. By default, the Materialize View switch is on, and views are materialized automatically. Caching views stores a copy of the view results in the Periscope Data Engine, however, if an immediate version of the view is preferred, the switch can be turned off and the view will run as a subquery when referenced in other queries.

After clicking "Save", hover over the information dot to check its status.

Materialized views are kept up-to-date in the background to make sure the data is always fresh. Views will only refresh every 1-3 hours provided that the underlying data has changed or the view uses any time sensitive functions such as getdate().

Once a view starts to materialize, the info icon turns into a grey dotted info icon:

Once it has successfully materialized, the info text turns green:

A red dotted info icon indicates a view failed to materialize on the first run due to an error:

A solid red info icon indicates that a view that was previously materialized failed to update its data during a subsequent refresh. The last materialized version is still stored on the Periscope Cache:

A solid grey icon indicates the underlying data has been updated and the view is awaiting its next refresh:

Keeping Track of Views

The "i" next to the view's name display information such as when the view was last refreshed, when the view was last modified, and how many charts and views depend on this View.

In the list of all views, the colored dots will give quick summary statuses. Green checks indicate successfully materialized views, grey clocks indicate views that are in the process of caching, red circles indicate cache failures, and no icons indicate views that are not to be materialized.

Materialization Criteria

In order for a view to successfully materialize without R or Python code attached, the following conditions must be met:

  • Site administrators must ensure that all underlying tables referenced in the view are cached
  • If on the Redshift Cache naming conventions and syntax must follow Redshift guidelines, outlined in Redshift's documentation page
  • If on the Snowflake Cache, naming conventions and syntax must follow Snowflake guidelines, outlined in Snowflake's documentation page
  • On the Snowflake Cache, every column must be uniquely and explicitly named/aliased. (e.g. count(1) as column_name rather than just count(1))
  • The view cannot refer to any filters
  • The view cannot contain any duplicate column names
  • The view must not contain comments at the end of the SQL code. Note that comments can be safely place anywhere else in the SQL (beginning or middle of the code)

A complete debugging guide can be found on the Periscope Data Community.

In order for a view to successfully materialize with R or Python code attached, the following conditions must be met:

  • SQL must be able to run on either the Periscope Data Engine (the Periscope Cache) or directly on any database connected to the site
  • The view cannot refer to any filters
  • The view cannot contain any duplicate column names
  • The view must not contain comments at the end of the SQL code. Note that comments can be safely placed anywhere else in the SQL (beginning or middle of the code)

More information on integrating R and Python code can be found on the documentation page here.

Note: R and Python Integration is an add-on feature. Site administrators can contact their Account Manager for additional information.

Timeout Mechanics

Unlike charts, Periscope views have a 30 minute timeout window, allowing additional capabilities to run and save results from computationally expensive operations. The preview in a view runs on a 4 minute timeout window; however, the view continues to run in the backend until 30 minutes have passed. If a view has exceeded the 30 minute materialization window or ran into any other errors, a red x will appear by the name of the view in the View menu. If R or Python code has been added to the view, the code block is also given 30 minutes to run.

Our support team is ready to help