Materialized views and dynamic tables: the right choice for your BI?
actu-en

MATERIALIZED VIEWS AND DYNAMIC TABLES: THE RIGHT CHOICE FOR YOUR BI?

By 11 October 2023 No Comments

In an environment where companies are looking for data solutions able to adapt to their growth and their constraints, Snowflake distinguishes itself with its flexibility and its simplicity. Indeed, this cloud data warehouse frees the company’s teams from the setup, the configuration or the management of hardware or software. In addition to these benefits, the solution has many features aimed at optimizing the data platform’s cost and performance.

Regarding pre-aggregation, Snowflake offers materialized views or dynamic tables. If materialized views are available in other cloud data warehouses, dynamic tables are a Snowflake-only feature.

So, why are these two options available to pre-aggregate data? What are each one’s characteristics and benefits? Our experts studied* materialized views (MV) and dynamic tables (DT) during several months, focusing on five points :

  • The boards’ display performance
  • The data’s consistency with source data
  • The automatic redirection
  • Cost control
  • Functional limitations

Let’s examine the results.

Blog post MV vs DT pour les non-techMaterialized views (MV)

Definition

A MV is simply a dataset from a precalculated table derived from a SQL query of which the result is physically stored, contrary to an ordinary “view” calculated on the spot.

Exemple de vue matérialisée (Credit: Microsoft)

Materialized view (Credit: Microsoft)

Benefits

Let’s examine display performance first, because that’s the first thing expected of MVs used for pre-aggregation. And according to the benchmark we created, display performance is enhanced by 9 in average compared to ordinary performance. If the boards display faster, the speed acceleration factor can go from 1 to 80 depending on the data model, the complexity of the usage and the granularity level.

The main benefit offered by Snowflake’s materialized views is their capacity to maintain data consistency between MV aggregation structures and source data, all of it in a manner that is transparent for the user.

Theoretically, Snowflake takes care of the automatic redirection towards materialized views, which simplifies the teams’ work because they don’t have to modify the boards.

Limits

Materialized views quickly show their limits when use cases become complex. For example, joins are not supported at the moment, which limits the amount of use cases where the use of MVs is relevant.

On the other hand, the redirection to the MVs isn’t that automatic ; indeed, we have discovered that in some cases the engine doesn’t do it, for no apparent reason.

Finally, the MVs’ cost is not predictable, mutualisable or dependent on warehouses, which complicates cost control regarding MVs on Snowflake.

Are dynamic tables the solution to the materialized views’ limits?

 

Dynamic tables (DT)

Definition

A dynamic table is not the result of a table, but of a flow, a stream which potentially includes transformations, which allows for a whole lot of possibilities!

Table dynamique (Credit: Snowflake)

Dynamic table (Credit: Snowflake)

Benefits

Regarding performance, dynamic tables offer a better gain of performance compared to MVs with an x13 average acceleration factor. Depending on the different use cases, complexity and granularity, the acceleration factor can go up to 500.

Dynamic tables, unlike materialized views, don’t have SQL functional limitations and thus support joins, which are very popular in BI and data visualization.

On the other hand, the data refresh rate is customizable during the creation of the DTs and is automatically done by Snowflake.

Lastly, DTs offer better control of the costs by allowing the user to choose the refresh rate and the warehouse which will store them.

Limits

Despite all of their undeniable benefits, dynamic tables aren’t perfect. Indeed, unlike MVs, DTs aren’t automatically redirected to by Snowflake even when they would speed up queries.

Moreover, since DTs’ refresh rate is predetermined, the user doesn’t isn’t guaranteed data that is updated in real time.

 

In conclusion, dynamic tables offer real flexibility for a BI and data viz use, as well as a better cost and use control compared to materialized views. We have some real dynamic tables fans in our team !

They are so in love with dynamic tables that our new solution, Autopilot, is built to offer constantly and effortlessly updated dynamic tables. Autopilot also allows for the automatic creation of DTs derived from boards uses and completes them by ensuring data consistency and automatic redirection. Performance, cost control and automatic self-service BI on Snowflake!!

To know more and test out the Autoptilot preview for free, contact us!

 

* Benchmark context:

12 sales, marketing, HR or finance dashboards from retail, energy, industry, and finance by daily run

1245 different SQL queries patterns

BI tools : PowerBI, Tableau, Microstrategy, BO, Cognos

Snowflake warehouse size: XS