Materialization allows you to write datasets back to your warehouse as tables. These tables act as simplified versions of the complex SQL queries you build in Sigma and can improve the speed and performance of your downstream reports.

Each table will be written and rewritten to your warehouse on the schedule that you set.

Summary of Content

Requirements
Materialization for Speed and Performance
View Materialized Tables in your Warehouse
View Materialized Tables in Sigma
Schedule Materialization
     Your Schedule Impacts your Results
     Set up a Materialization Schedule
     Edit a Materialization Schedule
     Run Now - How to Run your Scheduled Materialization at any Time
     Delete a Materialization Schedule (and Materialized Tables)
View your Organization's Full Materialization List
View your Organization's Materialization Logs and Statistics
Troubleshooting Q&A
Limitations

Requirements

  • Write access must be enabled on your dataset’s connection.
  • You must be an organization Admin or be assigned a custom account type with materialize permissions.

Materialization for Speed and Performance

Materialization makes it possible to convert datasets with complex or long-running queries (>2 minute timeout limit) into performant tables in your warehouse for interactive analysis in Sigma. They can be used as modeled layers of data building blocks for further analysis.

Materialized datasets and downstream documents will load faster by storing and refreshing data on a set schedule.

View Materialized Tables in your Warehouse

Scheduling materialization requires you to enable write access on your connection. All materialized tables will automatically be written to the schema you specify.

These tables are visible through your cloud data warehouse. However, they are not intended to be used as source tables for other applications. If you would like to access this data from other applications, check out Sigma’s Dataset Warehouse Views feature.

Please do not make changes to the materialized tables directly in your database. Doing so will result in unexpected results or query failures.

View Materialized Tables in Sigma

If materialization exists on a dataset, Materialization status will appear on the dataset header information popup. To view these details

  1. Click on the information icon in the dataset header. This will open the dataset's information popup.
  2. Hover over the materialization status, to view additional details.

Admins can also view the status and history of materializations from a dataset’s Materialization tab at any time.

Materialized tables will not show up in Sigma’s connection browser. If you want to use one of these tables for further analysis, build that analysis on top of the associated dataset.

Schedule Materialization

Your Schedule Impacts your Results

Your dataset and all downstream documents rely on your materialization schedule for data updates. This means, if your schedule hasn’t run in 2 days, the data on your dashboard will also be 2 days behind.

Materialization queries can run for up to 24 hours before being cancelled. Long running queries that do not display in Sigma can still be materialized.

Set up a Materialization Schedule

  1. Open the dataset you would like to materialize.
  2. Open to the dataset’s Materialization tab.
  3. Click Create Schedule to open the Materialization Schedule modal.
  4. Set the schedule for when you would like your dataset to materialize.
  5. Click Save.

Edit a Materialization Schedule

  1. Open the dataset.
  2. Navigate to your dataset’s Materialization Tab.
  3. Click the ••• icon next to the Run Now button in the schedule header.
  4. Click Edit Schedule to open the Materialization Schedule modal.
  5. Select a new schedule.
  6. Click Save.

Run Now - Run your Scheduled Materialization at Any Time

  1. Open the dataset.
  2. Navigate to your dataset’s Materialization tab.
  3. Click the Run Now button in the schedule header.

Delete a Materialization Schedule (and Materialized Tables)

When a materialization schedule is deleted, Sigma will automatically remove the materialized table from your warehouse. It can take up to 24 hours for the table to be deleted.

  1. Open the dataset.
  2. Click the Edit button in the top right corner of your screen.
  3. Navigate to your dataset’s Materialization tab.
  4. Click the ••• icon next to the Run Now button in the schedule header.
  5. Click Remove Schedule.

View your Organization's Full Materialization List

A complete list of your organization's materialization schedules can be found in your Admin Portal. 

  1. Open your user menu, and click Administration to open your Admin Portal.
  2. Click Materializations in the left panel to open the Materialization page.
  3. From here you can browse your materialization schedules and/or click a document's name to open it. 

View your Organization's Materialization Logs and Statistics

Logs and statistics for each of your organization's materialization runs can be found in the Usage Dashboard within the Admin Portal. 

  1. Open your user menu, and click Administration to open your Admin Portal.
  2. Click Usage in the left panel and then select the Materialization tab.
  3. From here you can browse statistics about all your organization's materialization runs as well as logs for each run in the table called Materialization Log.

Troubleshooting Q&A

Question: My materialized Dataset is suddenly showing a “materialization disabled” error. Why?
Answer: A dataset’s materialization schedule can become disabled if the dataset or any underlying Datasets have been altered such that they no longer support materialization.

To learn more, check out materialization limitations.
To explore upstream sources, visit your Dataset’s Lineage tab.
To explore joined sources, visit your Dataset’s Links tab.

Question: My scheduled materialization is failing. Why? How can I fix it?
Answer: Materialization can fail for a number of reasons. Admin users can view logs for each materialization run inside the Usage Dashboard. Explicit error messages will appear in there. 

We recommend checking the dataset for new column errors or materialization limitations. The source of failed materialization can also lie in errors within your dataset’s Lineage and Links.

Note: when a materialization fails, the dataset uses the last successful materialization.
 

Limitations

Materialization is not available for datasets that use parameters or system functions. These datasets are expected to return different values whenever their parameters change. Materialized tables, on the other hand, always return the same value - the fixed output of the dataset at the time the materialization was run. As a result, using the materialized versions of datasets that use parameters or system functions would produce unexpected results.

Datasets referencing other datasets, by means of duplication or joins, can typically be materialized. However, this is not true if any underlying dataset(s) cannot be materialized.