Snowflake | Using Materialized Views to Solve Multi Clustering Performance Problems

I rarely link to posts by vendors specifically about their own features. BUT: this is a really good one.

Snowflake recently released Materialized Views. MVs are a somewhat standard feature in the traditional database world—I started working with Oracle at 8i and they were standard even back then. But Snowflake is the first cloud data warehouse to support them, which has a bunch of people asking "what do I do with these things?"

This post is a brilliant example of an ideal use case: creating what Vertica calls projections. Projections are essentially copies of an underlying dataset that have different config properties: they're clustered differently, have a filter applied, or some other optimization. This helps dramatically when querying extremely large tables (1b rows or more), and MVs provide a super-easy way to keep one or more projections up-to-date with the underlying table.

Features like this are really important if we want the cloud data warehouse world to fully subsume the on-prem world. And, before you ask: no, dbt doesn't support Snowflake MVs out of the box, but you absolutely could make your own materialization to support them. I plan on playing with this over the coming week and will be sure to post in Slack if I come up with anything useful.


Want to receive more content like this in your inbox?