Materialized views make Slate data easily accessible to external tools, such as Tableau, PowerBI, operational data stores, and data warehouses.

How they work

A materialized view is like a scheduled data export that stores its results in your database. They are a static, point-in-time snapshot of a result-set.

This snapshot can be refreshed on demand or within a delivery window. Compare this behavior to that of dynamic views, which update with any change in the table data.

If you were to create a materialized view of an entire Slate table, it would theoretically duplicate that table in your database (a huge computational undertaking). In reality, there is a 15-minute processing limit: if your materialized view takes more than 15 minutes to render, a time out error occurs.

When to use them

Materialized views are appropriate for queries that:

• Contain complex logic

• Are formula and calculation heavy

• Do not require real-time results

If your exported data are simpler and require results that are as recent as the last query update, make a dynamic view instead.

Advantages over regular queries

Materialized views are more efficient because they reference only those columns specific to your export.

Regular queries retrieve data from multiple tables across your Slate database. Each table referenced by the query likely contains many columns irrelevant to the external tool to which you are exporting Slate data.

Creating a materialized view

To create a materialized view:

1. From the main navigation, select Queries / Reports.

2. Click New Query. A pop-up appears.

3. Enter a name. The name must be:

   • Computer-friendly: No spaces, no special characters except for underscores.

   • Unique among other tables in your database: This includes standard table such as person, as well as any custom datasets or entities you may have in your instance.

     

4. Add exports and filters as desired.

   • A completely custom table is being constructed upon which queries may be run afterwards.

   • Export names must be unique within the same view. If two exports share the same name, the view will fail to build.

5. From the Edit Query page, select Schedule Export.

6. Configure the following settings:

   • Destination: View (Materialized)

   • View: Enter the name of your query following table://. The name entered here must match the name of your query.

   • Query Base (🏔️ Summit 2025 Feature): Creates a new query base from the view.

     • Queries on these materialized view bases can save considerable time when you want to return complexly-calculated query data.

     • 📖 Further reading: Query Base Views

   • Notification: Specify the conditions under which a notification email should be sent

   • Requested Delivery Window: The time frame during which the view should be refreshed

   • Requested Weekdays: The days of the week during which the view should be refreshed

     

6. Click Save.

The view is created as a table in the build schema and follows the naming convention build.[queryname].

For a materialized view to update automatically based on the selected windows, set the status to Active.

If left inactive, you can update the materialized view manually by clicking Refresh View.

Querying on the materialized view

Once the materialized view has been created, the snapshot data is stored on a table (as defined in the Schedule Export settings) in the database.

Query base views

If you created a query base from the view, you can query directly on the view as its own base.

📖 Further reading: Query base views

Direct SQL connections

You can also access the materialized view table with a direct SQL connection.

☑️ This requires the Direct SQL Access and Query permissions.

This table can be referenced via direct SQL access, or by Custom SQL snippet parts in a standard query in Slate:

select [Name], [Event Name] from build.[queryname]