Should I use materialized views for simple queries in snowflake? What is cost effective?

MYK 0
Viewed 26

I often work with big tables that change daily. A typical table might be:

|basket_id (int) | item_id (int) |is_current_basket (bool) | time_added (timestamp)| quantity (int) |

And be ~5b records long.

The way this table is “meant to be used” is something like

SELECT basket_id, SUM(price * quantity) AS basket_value
FROM baskets JOIN prices ON prices.product_id = baskets.product_id
GROUP BY basket_id
WHERE is_current_basket = 1

I want to understand how cheap/expensive it’d be to keep this data up to date using snowflake? Would I have to have one xs instance always running to query this data?

snowflake-cloud-data-platform
1 Answers

Materialized view maintenance generates a cost via cloud services. Since the table is only being updated once a day it shouldn't be too prohibitive.

The question then is if the query performance and potential decrease in cost due to the m/view would be enough to cover the cost of the maintenance

If your query on the view is say 10s and it is 30s on the table itself, if your warehouse suspend time is 5 minutes, it won't make a difference to cost and in fact will cost more to use the view due to maintenance

It is probably best to do some testing and benchmarking to see if there is any substantial cost gains on a query basis

Also, given the size of the table, you might get a performance increase by manually clustering it (if the table is >1TB in size). This also has a cost but it might be less than the view maintenance and could improve query performance on the table itself to the point where it could be cheaper than the view

Dean Flinter0
Related