Dataset Warehouse Views

Sigma’s Dataset Warehouse Views feature empowers you to leverage the data you modeled in Sigma from the warehouse, or anywhere you can write SQL. 

Summary of Contents

Requirements
Using Dataset Warehouse Views
Limitations
Insufficient Permissions on Snowflake

Requirements

  • A Snowflake or BigQuery connection
  • Write access configured on your connection

Using Dataset Warehouse Views

Once write access is configured on your BigQuery or Snowflake connection, Sigma will automatically create SQL views in your write access destination, enabling you to query your Sigma Datasets directly from your warehouse.

For each Dataset, Sigma will create up to two views:

  1. A Dataset View to access your Dataset’s generated SQL
  2. A Materialized Dataset View to access your Sigma generated materialized tables; Materialization must be configured and scheduled on your dataset for a Materialization View to be generated

Querying the Dataset View will always return the live data from your warehouse, whereas querying the Materialized Dataset View will return data from the Materialized tables, which are recreated on a schedule. This means that data returned from the Materialized Dataset View may differ from the Dataset View and the sources that are referenced in the Dataset.

Both views stay in sync with the Dataset from which they are created. When changes are published to the dataset, Sigma will update each warehouse view to match the changes.

To use a Dataset view in your SQL queries, you can access the full view path from the dataset's header info icon. The view’s SQL path can be copied from the context menu next to the view’s name. You can also rename the view from this menu.

You can use that path to access the modeled Sigma data from any IDE or tool from which you can write SQL.

Limitations

  • If a parameter is used in your dataset, the warehouse view will only reflect the parameter’s default value.
  • Both Dataset Views and Materialized Dataset Views are incompatible with Row-Level Security. This is because they cannot support the use of System Functions, namely: CurrentUserEmail, CurrentUserInTeam and CurrentUserAttributeText. If these functions are called in the dataset, the view will not write and an error will display in the dataset's information modal. 
  • If a SQL dataset uses non-qualified SQL (where the SQL paths are not explicitly defined), the view will show an error for that Dataset and any Datasets that reference it.

Insufficient Permissions on Snowflake

If you have enabled write access on your connection but see an ‘Insufficient permissions’ warning in Sigma’s Warehouse Views display, please review the grants on your Sigma role.

Your Sigma role should have USAGE grants on your destination Database and USAGE, CREATE TABLE, CREATE VIEW and CREATE STAGE grants on your destination Schema.

You can follow the set up instructions and commands outlined in Sigma’s Set Up Write Access guide to ensure all appropriate grants have been set.

Was this page helpful?
Yes No